MySQL5.1體驗--MySQL分區

一、概述

相信有很多人經常會問同樣的一個問題:當 MySQL

的總記錄數超過了100萬後,會出現性能的大幅度下降嗎?答案是肯定的,但是性能下降>的比率不一而同,要看系統的架構、應用程序、還有>包 括索引、服務器硬件等多種因素而定。當有網友問我這個問題的時候,我最常見的回答>就是:分表,可以根據id區間或者時間先後順序等多

種規則來分表。分表很容易,然而由此所帶來的應用程序甚至是架構方面的改動工作卻不>容小觑,還包括將來的擴展性等。

在以前,一種解決方案就是使用 MERGE

類型,這是一個非常方便的做飯。架構和程序基本上不用做改動,不過,它的缺點是顯見的:

只能在相同結構的 MyISAM 表上使用

無法享受到 MyISAM 的全部功能,例如無法在 MERGE 類型上執行 FULLTEXT 搜索

它需要使用更多的文件描述符

讀取索引更慢

這個時候,MySQL 5.1 中新增的分區(Partition)功能的優勢也就很明顯了:

與單個磁盤或文件系統分區相比,可以存儲更多的數據

很容易就能刪除不用或者過時的數據

一些查詢可以得到極大的優化

涉及到 SUM()/COUNT() 等聚合函數時,可以並行進行

IO吞吐量更大

分區允許可以設置爲任意大小的規則,跨文件系統分配單個表的多個部分。實際上,表的不同部分在不同的位置被存儲爲單獨的表。

二、分區的類型

RANGE 分區:基于屬于一個給定連續區間的列值,把多行分配給分區。參見18.2.1節,RANGE分區

LIST 分區:類似于按RANGE分區,區別在于LIST分區是基于列值匹配一個離散值集合中的某個值來進行選擇。參見18.2.2節,LIST分區

HASH分區:基于用戶定義的表達式的返回值來進行選擇的分區,該表達式使用將要插入到表中的這些行的列值進行計算。這個函數可以包>含MySQL中有效的、産生非負整數值的任何表達式。參見18.2.3節,HASH分區

KEY

分區:類似于按HASH分區,區別在于KEY分區只支持計算一列或多列,且MySQL服務器提供其自身的哈希函數。必須有一列或多列包含>整數值。

參見18.2.4節,KEY分區

三、分區例子:

RANGE 類型

CREATE TABLE users ( uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL DEFAULT '', email VARCHAR(30) NOT NULL DEFAULT '')PARTITION BY RANGE (uid) ( PARTITION p0 VALUES LESS THAN (3000000) DATA DIRECTORY = '/data0/data' INDEX DIRECTORY = '/data1/idx', PARTITION p1 VALUES LESS THAN (6000000) DATA DIRECTORY = '/data2/data' INDEX DIRECTORY = '/data3/idx', PARTITION p2 VALUES LESS THAN (9000000) DATA DIRECTORY = '/data4/data' INDEX DIRECTORY = '/data5/idx', PARTITION p3 VALUES LESS THAN MAXVALUE DATA DIRECTORY = '/data6/data' INDEX DIRECTORY = '/data7/idx');

在這裏,將用戶表分成4個分區,以每300萬條記錄爲界限,每個分區都有自己獨立的數據、索引文件的存放目錄,與此同時,這些目錄所在的>

物理磁盤分區可能也都是完全獨立的,可以多大提高了磁盤IO吞吐量。

LIST 類型

CREATE TABLE category ( cid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL DEFAULT '')PARTITION BY LIST (cid) ( PARTITION p0 VALUES IN (0,4,8,12) DATA DIRECTORY = '/data0/data' INDEX DIRECTORY = '/data1/idx', PARTITION p1 VALUES IN (1,5,9,13) DATA DIRECTORY = '/data2/data' INDEX DIRECTORY = '/data3/idx', PARTITION p2 VALUES IN (2,6,10,14) DATA DIRECTORY = '/data4/data' INDEX DIRECTORY = '/data5/idx', PARTITION p3 VALUES IN (3,7,11,15) DATA DIRECTORY = '/data6/data' INDEX DIRECTORY = '/data7/idx');

分成4個區,數據文件和索引文件單獨存放。

HASH 類型

CREATE TABLE users ( uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL DEFAULT '', email VARCHAR(30) NOT NULL DEFAULT '')PARTITION BY HASH (uid) PARTITIONS 4 ( PARTITION p0 DATA DIRECTORY = '/data0/data' INDEX DIRECTORY = '/data1/idx', PARTITION p1 DATA DIRECTORY = '/data2/data' INDEX DIRECTORY = '/data3/idx', PARTITION p2 DATA DIRECTORY = '/data4/data' INDEX DIRECTORY = '/data5/idx', PARTITION p3 DATA DIRECTORY = '/data6/data' INDEX DIRECTORY = '/data7/idx');

分成4個區,數據文件和索引文件單獨存放。

KEY 類型

REATE TABLE users ( uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL DEFAULT '', email VARCHAR(30) NOT NULL DEFAULT '')PARTITION BY KEY (uid) PARTITIONS 4 ( PARTITION p0 DATA DIRECTORY = '/data0/data' INDEX DIRECTORY = '/data1/idx', PARTITION p1 DATA DIRECTORY = '/data2/data' INDEX DIRECTORY = '/data3/idx', PARTITION p2 DATA DIRECTORY = '/data4/data' INDEX DIRECTORY = '/data5/idx', PARTITION p3 DATA DIRECTORY = '/data6/data' INDEX DIRECTORY = '/data7/idx');

分成4個區,數據文件和索引文件單獨存放。

子分區

子分區是針對 RANGE/LIST 類型的分區表中每個分區的再次分割。再次分割可以是 HASH/KEY 等類型。例如:

CREATE TABLE users ( uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL DEFAULT '', email VARCHAR(30) NOT NULL DEFAULT '')PARTITION BY RANGE (uid) SUBPARTITION BY HASH (uid % 4) SUBPARTITIONS 2( PARTITION p0 VALUES LESS THAN (3000000) DATA DIRECTORY = '/data0/data' INDEX DIRECTORY = '/data1/idx', PARTITION p1 VALUES LESS THAN (6000000) DATA DIRECTORY = '/data2/data' INDEX DIRECTORY = '/data3/idx');

對 RANGE 分區再次進行子分區劃分,子分區采用 HASH 類型。

或者

CREATE TABLE users ( uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL DEFAULT '', email VARCHAR(30) NOT NULL DEFAULT '')PARTITION BY RANGE (uid) SUBPARTITION BY KEY(uid) SUBPARTITIONS 2( PARTITION p0 VALUES LESS THAN (3000000) DATA DIRECTORY = '/data0/data' INDEX DIRECTORY = '/data1/idx', PARTITION p1 VALUES LESS THAN (6000000) DATA DIRECTORY = '/data2/data' INDEX DIRECTORY = '/data3/idx');

對 RANGE 分區再次進行子分區劃分,子分區采用 KEY 類型。

四、分區管理

刪除分區

ALERT TABLE users DROP PARTITION p0;

刪除分區 p0。

重建分區

RANGE 分區重建

ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES LESS THAN (6000000));

將原來的 p0,p1 分區合並起來,放到新的 p0 分區中。

LIST 分區重建

ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES IN(0,1,4,5,8,9,12,13));

將原來的 p0,p1 分區合並起來,放到新的 p0 分區中。

HASH/KEY 分區重建

ALTER TABLE users REORGANIZE PARTITION COALESCE PARTITION 2;

用 REORGANIZE 方式重建分區的數量變成2,在這裏數量只能減少不能增加。想要增加可以用 ADD PARTITION 方法。

新增分區

新增 RANGE 分區

ALTER TABLE category ADD PARTITION (PARTITION p4 VALUES IN (16,17,18,19)DATA DIRECTORY = '/data8/data'INDEX DIRECTORY = '/data9/idx');

新增一個RANGE分區。

新增 HASH/KEY 分區

ALTER TABLE users ADD PARTITION PARTITIONS 8;

將分區總數擴展到8個。

好了,本次體驗先到這裏,更多詳情請看 MySQL 手冊第18章。

MySQL的LIST分區體驗與總結
終于有點空閑時間了,測試一下LIST分區,因爲LIST在我們的開發中用到。他分區以後再補上。 版本: Server version: 5.1.23a-maria-alpha-log MySQL Community Server (GPL) 一、講在前面 注意: 1、ALTER T...查看完整版>>MySQL的LIST分區體驗與總結
 
MySQL5.1體驗--MySQL實例管理器mysqlmanager初試
  MySQL實例管理器(IM)是通過TCP/IP端口運行的後台程序,用來監視和管理MySQL數據庫服務器實例。MySQL實例管理器適合Unix-類操作系統和Windows。  可以在mysqld_safe腳本使用MySQL實例管理器來啓動和停止MySQL服...查看完整版>>MySQL5.1體驗--MySQL實例管理器mysqlmanager初試
 
MySQL全力准備商務升級近期將推出MySQL5.0
  據外電報道,爲了吸收新公司客戶,MySQL公司計劃在7到10天內推出MySQL數據庫5.0版。目前,該版數據庫正處于最後階段的查錯過程中,MySQL公司産品管理主管Robin Schumacher說。該公司計劃推出一種"公共"版本,通過...查看完整版>>MySQL全力准備商務升級近期將推出MySQL5.0
 
MySQL5權威指南(第3版)/數據庫系列/圖靈程序設計叢書(圖靈程序設計叢書)|報價¥59.20|圖書,計算機與互聯網,數據庫,MySQL,科夫勒
目錄:圖書,計算機與互聯網,數據庫,MySQL,品牌:科夫勒基本信息·出版社:人民郵電出版社·頁碼:662 頁碼·出版日:2006年·ISBN:711515337X·條碼:9787115153371·版次:2006年12月第3版·裝幀:平裝·開本:16開...查看完整版>>MySQL5權威指南(第3版)/數據庫系列/圖靈程序設計叢書(圖靈程序設計叢書)|報價¥59.20|圖書,計算機與互聯網,數據庫,MySQL,科夫勒
 
SQLserver數據庫導入Mysql數據庫的體驗
因工作需要,要將存放在sql server數據庫中的數據全部導入到mysql數據庫中,在網上搜集相關資料,找到兩種方法,現在分別談談對他們的看法。 第一種是安裝mysql ODBC,利用sql server的導出功能,選擇mysql數據源,進...查看完整版>>SQLserver數據庫導入Mysql數據庫的體驗
 
SQLServer數據庫導入MySQL數據庫的體驗
  因工作需要,要將存放在sql server數據庫中的數據全部導入到mysql數據庫中,在網上搜集相關資料,找到兩種方法,現在分別談談對他們的看法。  第一種是安裝mysql ODBC,利用sql server的導出功能,選擇mysql數...查看完整版>>SQLServer數據庫導入MySQL數據庫的體驗
 
分區和負載均衡讓MySQL更大更好-MySQL
  通常,當我們的MySQL數據庫逐漸變慢時,我們就希望通過一切努力使它變得更快、更強、更大、更好!那麽都有哪些方法呢?別著急,我會一個一個給大家介紹如何才能實現這些美好的願望。閱讀本系列文章將有助于擴大你...查看完整版>>分區和負載均衡讓MySQL更大更好-MySQL
 
通過分區(Partition)提升MySQL性能
通過分區(Partition)提升MySQL性能 通過分區(Partition)提升MySQL性能 通過分區(Partition)提升MySQL性能 ——MySQL5.1新特性翻譯系列 幾年前,俺寫過一篇題爲“The Fo...查看完整版>>通過分區(Partition)提升MySQL性能
 
通過分區(Partition)提升MySQL性能
通過分區(Partition)提升MySQL性能 ——MySQL5.1新特性翻譯系列 幾年前,俺寫過一篇題爲“The Foundation of Excellent Performance”的文章(現在仍然可以在看到),俺對SQL語句...查看完整版>>通過分區(Partition)提升MySQL性能
 
 
回到王朝網路移動版首頁