傳統(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ù)性能是沒有影響的建椰。在數(shù)據(jù)庫(kù)存儲(chǔ)上雕欺，database只起到一個(gè)namespace的作用。database中的表文件存儲(chǔ)在一個(gè)以database名命名的文件夾中棉姐。比如下面的employees數(shù)據(jù)庫(kù)：

mysql> show tables in employees;
+---------------------+
| Tables_in_employees |
+---------------------+
| departments         |
| dept_emp            |
| dept_manager        |
| employees           |
| salaries            |
| titles              |
+---------------------+

在操作系統(tǒng)中看是這樣的：

# haitian at haitian-coder.local in /usr/local/var/mysql/employees on git:master ● [21:19:47]
→ ls  
db.opt           dept_emp.frm     dept_manager.ibd salaries.frm     titles.ibd
departments.frm  dept_emp.ibd     employees.frm    salaries.ibd
departments.ibd  dept_manager.frm employees.ibd    titles.frm

database不是文件屠列，只起到namespace的作用，所以MySQL對(duì)database大小當(dāng)然也是沒有限制的伞矩，而且對(duì)里面的表數(shù)量也沒有限制笛洛。

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ù)（磁盤空間遏匆、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)較均勻，沒有明顯的熱點(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è)械?code>SQL語(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開始烛缔，一個(gè)表最多可以有8192個(gè)分區(qū)馏段。

2.分區(qū)表中無(wú)法使用外鍵約束。

3.主表的所有唯一索引列（包括主鍵）都必須包含分區(qū)字段践瓷。MySQL官方文檔中寫的是：

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> TRUNCATE h2;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM h2;
Empty set (0.00 sec)

mysql> INSERT IGNORE INTO h2 VALUES (2, 5), (6, 10), (7, 5), (3, 1), (1, 9);
Query OK, 3 rows affected (0.00 sec)
Records: 5  Duplicates: 2  Warnings: 0

mysql> SELECT * FROM h2;
+------+------+
| c1   | c2   |
+------+------+
|    7 |    5 |
|    1 |    9 |
|    2 |    5 |
+------+------+
3 rows in set (0.00 sec)

與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ū)沒有起到作用，我們用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: salaries
Create 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) */

則下面的查詢沒有利用分區(qū)镀裤，因?yàn)?code>partitions中包含了所有的分區(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: ALL
possible_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: ALL
possible_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: ALL
possible_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ū)字段蒸走，而分表沒有這個(gè)限制仇奶。

• 分表包括垂直切分和水平切分，而分區(qū)只能起到水平切分的作用比驻。

博客地址：http://haitian299.github.io/2016/05/26/mysql-partitioning/