原文鏈接 http://blog.csdn.net/kobejayandy/article/details/54799579
傳統(tǒng)的分庫(kù)分表
傳統(tǒng)的分庫(kù)分表都是通過(guò)應(yīng)用層邏輯實(shí)現(xiàn)的，對(duì)于數(shù)據(jù)庫(kù)層面來(lái)說(shuō)，都是普通的表和庫(kù)。
分庫(kù)
分庫(kù)的原因
首先，在單臺(tái)數(shù)據(jù)庫(kù)服務(wù)器性能足夠的情況下，分庫(kù)對(duì)于數(shù)據(jù)庫(kù)性能是沒(méi)有影響的。在數(shù)據(jù)庫(kù)存儲(chǔ)上，database只起到一個(gè)namespace的作用。database中的表文件存儲(chǔ)在一個(gè)以database名命名的文件夾中。
比如下面的employees數(shù)據(jù)庫(kù)：
在操作系統(tǒng)中看是這樣的：
database不是文件，只起到namespace
的作用，所以MySQL對(duì)database 大小當(dāng)然也是沒(méi)有限制的，而且對(duì)里面的表數(shù)量也沒(méi)有限制。
C.10.2 Limits on Number of Databases and Tables
MySQL has no limit on the number of databases. The underlying file system may have a limit on the number of directories.
MySQL has no limit on the number of tables. The underlying file system may have a limit on the number of files that represent tables. Individual storage engines may impose engine-specific constraints. InnoDB permits up to 4 billion tables.
所以，為什么要分庫(kù)呢？
答案是為了解決單臺(tái)服務(wù)器的性能問(wèn)題，當(dāng)單臺(tái)數(shù)據(jù)庫(kù)服務(wù)器無(wú)法支撐當(dāng)前的數(shù)據(jù)量時(shí)，就需要根據(jù)業(yè)務(wù)邏輯緊密程度把表分成幾撮，分別放在不同的數(shù)據(jù)庫(kù)服務(wù)器中以降低單臺(tái)服務(wù)器的負(fù)載。
分庫(kù)一般考慮的是垂直切分，除非在垂直切分后，數(shù)據(jù)量仍然多到單臺(tái)服務(wù)器無(wú)法負(fù)載，才繼續(xù)水平切分。
比如一個(gè)論壇系統(tǒng)的數(shù)據(jù)庫(kù)因當(dāng)前服務(wù)器性能無(wú)法滿足需要進(jìn)行分庫(kù)。先垂直切分，按業(yè)務(wù)邏輯把用戶相關(guān)數(shù)據(jù)表比如用戶信息、積分、用戶間私信等放入user數(shù)據(jù)庫(kù)；論壇相關(guān)數(shù)據(jù)表比如板塊，帖子，回復(fù)等放入forum數(shù)據(jù)庫(kù)，兩個(gè)數(shù)據(jù)庫(kù)放在不同服務(wù)器上。
拆分后表往往不可能完全無(wú)關(guān)聯(lián)，比如帖子中的發(fā)帖人、回復(fù)人這些信息都在user數(shù)據(jù)庫(kù)中。未拆分前可能一次聯(lián)表查詢就能獲取當(dāng)前帖子的回復(fù)、發(fā)帖人、回復(fù)人等所有信息，拆分后因?yàn)榭鐢?shù)據(jù)庫(kù)無(wú)法聯(lián)表查詢，只能多次查詢獲得最終數(shù)據(jù)。
所以總結(jié)起來(lái)，分庫(kù)的目的是降低單臺(tái)服務(wù)器負(fù)載，切分原則是根據(jù)業(yè)務(wù)緊密程度拆分，缺點(diǎn)是跨數(shù)據(jù)庫(kù)無(wú)法聯(lián)表查詢。
分表
分表的原因
當(dāng)數(shù)據(jù)量超大的時(shí)候，B-Tree索引就無(wú)法起作用了。除非是索引覆蓋查詢，否則數(shù)據(jù)庫(kù)服務(wù)器需要根據(jù)索引掃描的結(jié)果回表，查詢所有符合條件的記錄，如果數(shù)據(jù)量巨大，這將產(chǎn)生大量隨機(jī)I/O，隨之，數(shù)據(jù)庫(kù)的響應(yīng)時(shí)間將大到不可接受的程度。另外，索引維護(hù)（磁盤(pán)空間、I/O操作）的代價(jià)也非常高。
垂直分表
原因：
1.根據(jù)MySQL索引實(shí)現(xiàn)原理及相關(guān)優(yōu)化策略的內(nèi)容我們知道Innodb
主索引葉子節(jié)點(diǎn)存儲(chǔ)著當(dāng)前行的所有信息，所以減少字段可使內(nèi)存加載更多行數(shù)據(jù)，有利于查詢。
2.受限于操作系統(tǒng)中的文件大小限制。
切分原則： 把不常用或業(yè)務(wù)邏輯不緊密或存儲(chǔ)內(nèi)容比較多的字段分到新的表中可使表存儲(chǔ)更多數(shù)據(jù)。。
水平分表
原因：
1.隨著數(shù)據(jù)量的增大，table行數(shù)巨大，查詢的效率越來(lái)越低。
2.同樣受限于操作系統(tǒng)中的文件大小限制，數(shù)據(jù)量不能無(wú)限增加，當(dāng)?shù)竭_(dá)一定容量時(shí)，需要水平切分以降低單表（文件）的大小。
切分原則：
增量區(qū)間或散列或其他業(yè)務(wù)邏輯。
使用哪種切分方法要根據(jù)實(shí)際業(yè)務(wù)邏輯判斷。
比如對(duì)表的訪問(wèn)多是近期產(chǎn)生的新數(shù)據(jù)，歷史數(shù)據(jù)訪問(wèn)較少，可以考慮根據(jù)時(shí)間增量把數(shù)據(jù)按照一定時(shí)間段（比如每年）切分。
如果對(duì)表的訪問(wèn)較均勻，沒(méi)有明顯的熱點(diǎn)區(qū)域，則可以考慮用范圍（比如每500w一個(gè)表）或普通Hash或一致性Hash來(lái)切分。
全局主鍵問(wèn)題：
原本依賴數(shù)據(jù)庫(kù)生成主鍵（比如自增）的表在拆分后需要自己實(shí)現(xiàn)主鍵的生成，因?yàn)橐话悴鸱忠?guī)則是建立在主鍵上的，所以在插入新數(shù)據(jù)時(shí)需要確定主鍵后才能找到存儲(chǔ)的表。
實(shí)際應(yīng)用中也已經(jīng)有了比較成熟的方案。比如對(duì)于自增列做主鍵的表，flickr
的全局主鍵生成方案很好的解決了性能和單點(diǎn)問(wèn)題，具體實(shí)現(xiàn)原理可以參考這個(gè)帖子。除此之外，還有類似于uuid的全局主鍵生成方案，比如達(dá)達(dá)參考的Instagram的ID生成器。
一致性Hash：
使用一致性Hash切分比普通的Hash切分可擴(kuò)展性更強(qiáng)，可以實(shí)現(xiàn)拆分表的添加和刪除。一致性Hash的具體原理可以參考這個(gè)帖子，如果拆分后的表存儲(chǔ)在不同服務(wù)器節(jié)點(diǎn)上，可以跟帖子一樣對(duì)節(jié)點(diǎn)名或ip取Hash；如果拆分后的表存在一個(gè)服務(wù)器中則可對(duì)拆分后的表名取Hash。
MySQL的分區(qū)表
上面介紹的傳統(tǒng)的分庫(kù)分表都是在應(yīng)用層實(shí)現(xiàn)，拆分后都要對(duì)原有系統(tǒng)進(jìn)行很大的調(diào)整以適應(yīng)新拆分后的庫(kù)或表，比如實(shí)現(xiàn)一個(gè)SQL中間件、原本的聯(lián)表查詢改成兩次查詢、實(shí)現(xiàn)一個(gè)全局主鍵生成器等等。
而下面介紹的MySQL分區(qū)表是在數(shù)據(jù)庫(kù)層面，MySQL自己實(shí)現(xiàn)的分表功能，在很大程度上簡(jiǎn)化了分表的難度。
介紹
對(duì)用戶來(lái)說(shuō)，分區(qū)表是一個(gè)獨(dú)立的邏輯表，但是底層由多個(gè)物理子表實(shí)現(xiàn)。
也就是說(shuō)，對(duì)于原表分區(qū)后，對(duì)于應(yīng)用層來(lái)說(shuō)可以不做變化，我們無(wú)需改變?cè)械腟QL語(yǔ)句，相當(dāng)于MySQL幫我們實(shí)現(xiàn)了傳統(tǒng)分表后的SQL中間件，當(dāng)然，MySQL
的分區(qū)表的實(shí)現(xiàn)要復(fù)雜很多。
另外，在創(chuàng)建分區(qū)時(shí)可以指定分區(qū)的索引文件和數(shù)據(jù)文件的存儲(chǔ)位置，所以可以把數(shù)據(jù)表的數(shù)據(jù)分布在不同的物理設(shè)備上，從而高效地利用多個(gè)硬件設(shè)備。
一些限制：
1.在5.6.7之前的版本，一個(gè)表最多有1024個(gè)分區(qū)；從5.6.7開(kāi)始，一個(gè)表最多可以有8192個(gè)分區(qū)。
2.分區(qū)表中無(wú)法使用外鍵約束。
3.主表的所有唯一索引列（包括主鍵）都必須包含分區(qū)字段。MySQL
官方文檔中寫(xiě)的是：
All columns used in the partitioning expression for a partitioned table must be part of every unique key that the table may have.
這句話不是很好理解，需要通過(guò)例子才能明白，MySQL官方文檔也為此限制特意做了舉例和解釋。
分區(qū)表的類型
RANGE分區(qū)
根據(jù)范圍分區(qū)，范圍應(yīng)該連續(xù)但是不重疊，使用PARTITION BY RANGE, VALUES LESS THAN關(guān)鍵字。不使用COLUMNS關(guān)鍵字時(shí)RANGE括號(hào)內(nèi)必須為整數(shù)字段名或返回確定整數(shù)的函數(shù)。
根據(jù)數(shù)值范圍：
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT NOT NULL,
store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
PARTITION p0 VALUES LESS THAN (6),
PARTITION p1 VALUES LESS THAN (11),
PARTITION p2 VALUES LESS THAN (16),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
根據(jù)TIMESTAMP范圍：
CREATE TABLE quarterly_report_status (
report_id INT NOT NULL,
report_status VARCHAR(20) NOT NULL,
report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),
PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),
PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),
PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),
PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),
PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),
PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),
PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),
PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
PARTITION p9 VALUES LESS THAN (MAXVALUE)
);
添加COLUMNS
關(guān)鍵字可定義非integer范圍及多列范圍，不過(guò)需要注意COLUMNS括號(hào)內(nèi)只能是列名，不支持函數(shù)；多列范圍時(shí)，多列范圍必須呈遞增趨勢(shì)：
根據(jù)DATE、DATETIME范圍：
CREATE TABLE members (
firstname VARCHAR(25) NOT NULL,
lastname VARCHAR(25) NOT NULL,
username VARCHAR(16) NOT NULL,
email VARCHAR(35),
joined DATE NOT NULL
)
PARTITION BY RANGE COLUMNS(joined) (
PARTITION p0 VALUES LESS THAN ('1960-01-01'),
PARTITION p1 VALUES LESS THAN ('1970-01-01'),
PARTITION p2 VALUES LESS THAN ('1980-01-01'),
PARTITION p3 VALUES LESS THAN ('1990-01-01'),
PARTITION p4 VALUES LESS THAN MAXVALUE
);
根據(jù)多列范圍：
CREATE TABLE rc3 (
a INT,
b INT
)
PARTITION BY RANGE COLUMNS(a,b) (
PARTITION p0 VALUES LESS THAN (0,10),
PARTITION p1 VALUES LESS THAN (10,20),
PARTITION p2 VALUES LESS THAN (10,30),
PARTITION p3 VALUES LESS THAN (10,35),
PARTITION p4 VALUES LESS THAN (20,40),
PARTITION p5 VALUES LESS THAN (MAXVALUE,MAXVALUE)
);
List分區(qū)
根據(jù)具體數(shù)值分區(qū)，每個(gè)分區(qū)數(shù)值不重疊，使用PARTITION BY LIST、VALUES IN
關(guān)鍵字。跟Range分區(qū)類似，不使用COLUMNS關(guān)鍵字時(shí)List括號(hào)內(nèi)必須為整數(shù)字段名或返回確定整數(shù)的函數(shù)。
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
)
PARTITION BY LIST(store_id) (
PARTITION pNorth VALUES IN (3,5,6,9,17),
PARTITION pEast VALUES IN (1,2,10,11,19,20),
PARTITION pWest VALUES IN (4,12,13,14,18),
PARTITION pCentral VALUES IN (7,8,15,16)
);
數(shù)值必須被所有分區(qū)覆蓋，否則插入一個(gè)不屬于任何一個(gè)分區(qū)的數(shù)值會(huì)報(bào)錯(cuò)。
mysql> CREATE TABLE h2 (
-> c1 INT,
-> c2 INT
-> )
-> PARTITION BY LIST(c1) (
-> PARTITION p0 VALUES IN (1, 4, 7),
-> PARTITION p1 VALUES IN (2, 5, 8)
-> );
Query OK, 0 rows affected (0.11 sec)
mysql> INSERT INTO h2 VALUES (3, 5);
ERROR 1525 (HY000): Table has no partition for value 3
當(dāng)插入多條數(shù)據(jù)出錯(cuò)時(shí)，如果表的引擎支持事務(wù)（Innodb），則不會(huì)插入任何數(shù)據(jù)；如果不支持事務(wù)，則出錯(cuò)前的數(shù)據(jù)會(huì)插入，后面的不會(huì)執(zhí)行。
可以使用IGNORE關(guān)鍵字忽略出錯(cuò)的數(shù)據(jù)，這樣其他符合條件的數(shù)據(jù)會(huì)全部插入不受影響。
mysql> CREATE TABLE h2 (
-> c1 INT,
-> c2 INT
-> )
-> PARTITION BY LIST(c1) (
-> PARTITION p0 VALUES IN (1, 4, 7),
-> PARTITION p1 VALUES IN (2, 5, 8)
-> );
Query OK, 0 rows affected (0.11 sec)
mysql> INSERT INTO h2 VALUES (3, 5);
ERROR 1525 (HY000): Table has no partition for value 3
與Range分區(qū)相同，添加COLUMNS關(guān)鍵字可支持非整數(shù)和多列。
Hash分區(qū)
Hash分區(qū)主要用來(lái)確保數(shù)據(jù)在預(yù)先確定數(shù)目的分區(qū)中平均分布，Hash括號(hào)內(nèi)只能是整數(shù)列或返回確定整數(shù)的函數(shù)，實(shí)際上就是使用返回的整數(shù)對(duì)分區(qū)數(shù)取模。
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4;
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
)
PARTITION BY HASH( YEAR(hired) )
PARTITIONS 4;
Hash分區(qū)也存在與傳統(tǒng)Hash分表一樣的問(wèn)題，可擴(kuò)展性差。MySQL也提供了一個(gè)類似于一致Hash的分區(qū)方法－線性Hash分區(qū)，只需要在定義分區(qū)時(shí)添加LINEAR
關(guān)鍵字，如果對(duì)實(shí)現(xiàn)原理感興趣，可以查看官方文檔。
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
)
PARTITION BY LINEAR HASH( YEAR(hired) )
PARTITIONS 4;
Key分區(qū)
按照KEY進(jìn)行分區(qū)類似于按照HASH分區(qū)，除了HASH分區(qū)使用的用戶定義的表達(dá)式，而KEY分區(qū)的 哈希函數(shù)是由MySQL 服務(wù)器提供。MySQL 簇（Cluster）使用函數(shù)MD5()來(lái)實(shí)現(xiàn)KEY分區(qū)；對(duì)于使用其他存儲(chǔ)引擎的表，服務(wù)器使用其自己內(nèi)部的 哈希函數(shù)，這些函數(shù)是基于與PASSWORD()一樣的運(yùn)算法則。
Key分區(qū)與Hash分區(qū)很相似，只是Hash函數(shù)不同，定義時(shí)把Hash關(guān)鍵字替換成Key即可，同樣Key分區(qū)也有對(duì)應(yīng)與線性Hash的線性Key分區(qū)方法。
CREATE TABLE tk ( col1 INT NOT NULL, col2 CHAR(5), col3 DATE)PARTITION BY LINEAR KEY (col1)PARTITIONS 3;
另外，當(dāng)表存在主鍵或唯一索引時(shí)可省略Key括號(hào)內(nèi)的列名，Mysql將按照主鍵－唯一索引的順序選擇，當(dāng)找不到唯一索引時(shí)報(bào)錯(cuò)。
子分區(qū)
子分區(qū)是分區(qū)表中每個(gè)分區(qū)的再次分割。創(chuàng)建子分區(qū)方法：
CREATE TABLE ts (id INT, purchased DATE) PARTITION BY RANGE( YEAR(purchased) ) SUBPARTITION BY HASH( TO_DAYS(purchased) ) SUBPARTITIONS 2 ( PARTITION p0 VALUES LESS THAN (1990), PARTITION p1 VALUES LESS THAN (2000), PARTITION p2 VALUES LESS THAN MAXVALUE );
和
CREATE TABLE ts (id INT, purchased DATE) PARTITION BY RANGE( YEAR(purchased) ) SUBPARTITION BY HASH( TO_DAYS(purchased) ) ( PARTITION p0 VALUES LESS THAN (1990) ( SUBPARTITION s0 DATA DIRECTORY = '/disk0/data' INDEX DIRECTORY = '/disk0/idx', SUBPARTITION s1 DATA DIRECTORY = '/disk1/data' INDEX DIRECTORY = '/disk1/idx' ), PARTITION p1 VALUES LESS THAN (2000) ( SUBPARTITION s2 DATA DIRECTORY = '/disk2/data' INDEX DIRECTORY = '/disk2/idx', SUBPARTITION s3 DATA DIRECTORY = '/disk3/data' INDEX DIRECTORY = '/disk3/idx' ), PARTITION p2 VALUES LESS THAN MAXVALUE ( SUBPARTITION s4 DATA DIRECTORY = '/disk4/data' INDEX DIRECTORY = '/disk4/idx', SUBPARTITION s5 DATA DIRECTORY = '/disk5/data' INDEX DIRECTORY = '/disk5/idx' ) );
需要注意的是：每個(gè)分區(qū)的子分區(qū)數(shù)必須相同。如果在一個(gè)分區(qū)表上的任何分區(qū)上使用SUBPARTITION
來(lái)明確定義任何子分區(qū)，那么就必須定義所有的子分區(qū)，且必須指定一個(gè)全表唯一的名字。
分區(qū)表的使用及查詢優(yōu)化
根據(jù)實(shí)際情況選擇分區(qū)方法
對(duì)現(xiàn)有表分區(qū)的原則與傳統(tǒng)分表一樣。
傳統(tǒng)的按照增量區(qū)間分表對(duì)應(yīng)于分區(qū)的Range
分區(qū)，比如對(duì)表的訪問(wèn)多是近期產(chǎn)生的新數(shù)據(jù)，歷史數(shù)據(jù)訪問(wèn)較少，則可以按一定時(shí)間段（比如年或月）或一定數(shù)量（比如100萬(wàn)）對(duì)表分區(qū)，具體根據(jù)哪種取決于表索引結(jié)構(gòu)。分區(qū)后最后一個(gè)分區(qū)即為近期產(chǎn)生的數(shù)據(jù)，當(dāng)一段時(shí)間過(guò)后數(shù)據(jù)量再次變大，可對(duì)最后一個(gè)分區(qū)重新分區(qū)（REORGANIZE PARTITION
）把一段時(shí)間（一年或一月）或一定數(shù)量（比如100萬(wàn)）的數(shù)據(jù)分離出去。
傳統(tǒng)的散列方法分表對(duì)應(yīng)于分區(qū)的Hash／Key分區(qū)，具體方法上面已經(jīng)介紹過(guò)。
查詢優(yōu)化
分區(qū)的目的是為了提高查詢效率，如果查詢范圍是所有分區(qū)那么就說(shuō)明分區(qū)沒(méi)有起到作用，我們用explain partitions
命令來(lái)查看SQL
對(duì)于分區(qū)的使用情況。
一般來(lái)說(shuō)，就是在where
條件中加入分區(qū)列。
比如表salaries
結(jié)構(gòu)為：
mysql> show create table salaries\G;*************************** 1. row *************************** Table: salariesCreate Table: CREATE TABLE salaries ( emp_no int(11) NOT NULL, salary int(11) NOT NULL, from_date date NOT NULL, to_date date NOT NULL, PRIMARY KEY (emp_no,from_date)) ENGINE=InnoDB DEFAULT CHARSET=utf8/*!50100 PARTITION BY RANGE (year(from_date))(PARTITION p1 VALUES LESS THAN (1985) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (1986) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN (1987) ENGINE = InnoDB, PARTITION p4 VALUES LESS THAN (1988) ENGINE = InnoDB, PARTITION p5 VALUES LESS THAN (1989) ENGINE = InnoDB, PARTITION p6 VALUES LESS THAN (1990) ENGINE = InnoDB, PARTITION p7 VALUES LESS THAN (1991) ENGINE = InnoDB, PARTITION p8 VALUES LESS THAN (1992) ENGINE = InnoDB, PARTITION p9 VALUES LESS THAN (1993) ENGINE = InnoDB, PARTITION p10 VALUES LESS THAN (1994) ENGINE = InnoDB, PARTITION p11 VALUES LESS THAN (1995) ENGINE = InnoDB, PARTITION p12 VALUES LESS THAN (1996) ENGINE = InnoDB, PARTITION p13 VALUES LESS THAN (1997) ENGINE = InnoDB, PARTITION p14 VALUES LESS THAN (1998) ENGINE = InnoDB, PARTITION p15 VALUES LESS THAN (1999) ENGINE = InnoDB, PARTITION p16 VALUES LESS THAN (2000) ENGINE = InnoDB, PARTITION p17 VALUES LESS THAN (2001) ENGINE = InnoDB, PARTITION p18 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
則下面的查詢沒(méi)有利用分區(qū)，因?yàn)閜artitions
中包含了所有的分區(qū)：
mysql> explain partitions select * from salaries where salary > 100000\G;*************************** 1. row *************************** id: 1 select_type: SIMPLE table: salaries partitions: p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18 type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 2835486 Extra: Using where
只有在where
條件中加入分區(qū)列才能起到作用，過(guò)濾掉不需要的分區(qū)：
mysql> explain partitions select * from salaries where salary > 100000 and from_date > '1998-01-01'\G;*************************** 1. row *************************** id: 1 select_type: SIMPLE table: salaries partitions: p15,p16,p17,p18 type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1152556 Extra: Using where
與普通搜索一樣，在運(yùn)算符左側(cè)使用函數(shù)將使分區(qū)過(guò)濾失效，即使與分區(qū)函數(shù)想同也一樣：
mysql> explain partitions select * from salaries where salary > 100000 and year(from_date) > 1998\G;*************************** 1. row *************************** id: 1 select_type: SIMPLE table: salaries partitions: p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18 type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 2835486 Extra: Using where
分區(qū)和分表的比較
傳統(tǒng)分表后，count
、sum
等統(tǒng)計(jì)操作只能對(duì)所有切分表進(jìn)行操作后之后在應(yīng)用層再次計(jì)算得出最后統(tǒng)計(jì)數(shù)據(jù)。而分區(qū)表則不受影響，可直接統(tǒng)計(jì)。
Queries involving aggregate functions such as SUM() and COUNT() can easily be parallelized. A simple example of such a query might be SELECT salesperson_id, COUNT(orders) as order_total FROM sales GROUP BY salesperson_id;. By “parallelized,” we mean that the query can be run simultaneously on each partition, and the final result obtained merely by summing the results obtained for all partitions.
分區(qū)對(duì)原系統(tǒng)改動(dòng)最小，分區(qū)只涉及數(shù)據(jù)庫(kù)層面，應(yīng)用層不需要做出改動(dòng)。
分區(qū)有個(gè)限制是主表的所有唯一字段（包括主鍵）必須包含分區(qū)字段，而分表沒(méi)有這個(gè)限制。
分表包括垂直切分和水平切分，而分區(qū)只能起到水平切分的作用。
