分區(qū)表帶來(lái)的性能提升

我們先基于下面的SQL和存儲(chǔ)過(guò)程創(chuàng)建一張分區(qū)表，并插入1億條記錄：

DROP TABLE if exists employees_partition;
CREATE TABLE if not exists `employees_partition` (
  `id` int(11) NOT NULL ,
  `name` varchar(32) DEFAULT NULL COMMENT '員工姓名',
  `job_no` varchar(16) NOT NULL COMMENT '員工工號(hào)',
   UNIQUE key(job_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY KEY (job_no) PARTITIONS 32 ;

-- 創(chuàng)建存儲(chǔ)過(guò)程
DROP PROCEDURE IF EXISTS insertemployees;
DELIMITER $$
CREATE PROCEDURE insertemployees()
BEGIN
DECLARE i INT;
  SET i=1;
  WHILE(i<=100000000) DO
    insert into employees_partition values(i, CONCAT(i, '-NAME'), CONCAT('NO.', i));
    SET i=i+1; 
  END WHILE;
END;
$$
DELIMITER ;
-- 調(diào)用存儲(chǔ)過(guò)程
call insertemployees();

數(shù)據(jù)插入完成后，還給name列加上索引。

接下來(lái)分別嘗試有分片鍵查詢(xún)，二級(jí)索引（idx_name）查詢(xún)，無(wú)分片鍵查詢(xún)這三種非常典型查詢(xún)，并查看執(zhí)行計(jì)劃（并且為了防止查詢(xún)結(jié)果被緩存，每條SQL都加上SQL_NO_CACHE）：

• 有分片鍵查詢(xún)

由下圖可知，有分片鍵查詢(xún)的性能簡(jiǎn)直狂拽吊炸天。而且我們看查詢(xún)計(jì)劃，能夠選定特定分區(qū)p24，并且索引類(lèi)型也是最優(yōu)秀的const：

query by partition key

• 二級(jí)索引查詢(xún)

由下圖可知，二級(jí)索引查詢(xún)查詢(xún)性能也相當(dāng)不錯(cuò)，但是條件沒(méi)有分片鍵，所以無(wú)法選擇特定分區(qū)，其查詢(xún)計(jì)劃顯示的目標(biāo)分區(qū)是p0~p31所有32個(gè)分區(qū)：

query by secondary index

說(shuō)明：二級(jí)索引查詢(xún)具體查詢(xún)性能與索引列的可選性有很大的關(guān)系，由于筆者構(gòu)造的索引列的可選性為1，所以查詢(xún)性能很好。如果是一個(gè)狀態(tài)列，1億條數(shù)據(jù)不同的值只有不到10個(gè)，那查詢(xún)性能就要差很多了，不止是分區(qū)表，普通表也是如此。

• 無(wú)分片鍵查詢(xún)

由下圖可知，條件中既沒(méi)有分片鍵，也沒(méi)有普通索引，這時(shí)候查詢(xún)性能就很差了，查詢(xún)耗時(shí)近39秒，無(wú)法用到任何索引，而且目標(biāo)分區(qū)是所有32個(gè)分區(qū)：

query by nothing

說(shuō)明：事實(shí)上不止分區(qū)表，就是普通表，這種查詢(xún)性能也是極差的，因?yàn)樾枰頀呙琛?/p>

筆者基于另一張沒(méi)有分區(qū)，且數(shù)據(jù)總量也是1億的表，執(zhí)行條件不會(huì)走索引的SQL，耗時(shí)也是令人震驚的30s+：

mysql> select SQL_NO_CACHE * from employees_nopartition where `id`='8989898';
+---------+--------------+------------+
| id      | name         | job_no     |
+---------+--------------+------------+
| 8989898 | 8989898-NAME | NO.8989898 |
+---------+--------------+------------+
1 row in set, 1 warning (30.78 sec)

mysql> show create table employees_nopartition\G
*************************** 1. row ***************************
       Table: employees_nopartition
Create Table: CREATE TABLE `employees_nopartition` (
  `id` int(11) NOT NULL,
  `name` varchar(32) DEFAULT NULL COMMENT '員工姓名',
  `job_no` varchar(16) NOT NULL COMMENT '員工工號(hào)',
  UNIQUE KEY `job_no` (`job_no`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

• 總結(jié)

對(duì)于分區(qū)表，如果查詢(xún)條件能夠避免雷區(qū)，即不會(huì)有全表掃描查詢(xún)，或者低效索引查詢(xún)（這些條件在分庫(kù)分表上性能也很差）。所有SQL的條件要么有分片鍵，要么有高效的索引，那么都性能提升是很明顯的。

分區(qū)表對(duì)性能提升如此明顯，為什么還是有那么多拒絕分區(qū)表的聲音，或者說(shuō)一線(xiàn)互聯(lián)網(wǎng)公司還是以分庫(kù)分表為主？筆者在以前的文章《分庫(kù)分表技術(shù)演進(jìn)暨最佳實(shí)踐》中也列舉了若干知名互聯(lián)網(wǎng)公司的分庫(kù)分表中間件，例如阿里的tddl、cobar，美團(tuán)的zebra，360的atlas，開(kāi)源社區(qū)的sharding-sphere，mycat等。這是因?yàn)榉謪^(qū)表本身有諸多的限制，這些公司結(jié)合自己的業(yè)務(wù)特點(diǎn)，分區(qū)表完全不能滿(mǎn)足自己的需求！

分區(qū)表的限制

看上去帥氣的分區(qū)表，MySQL官方列舉了好多好多的限制，如下所示：

• 分區(qū)最大數(shù)

對(duì)于沒(méi)有使用NDB存儲(chǔ)引擎的表來(lái)說(shuō)，分區(qū)最大數(shù)限制為8192，這個(gè)數(shù)量包含了子分區(qū)數(shù)量。

• 不支持查詢(xún)緩存

對(duì)于分區(qū)表來(lái)說(shuō)，查詢(xún)緩存是不支持的，涉及分區(qū)表的查詢(xún)會(huì)自動(dòng)關(guān)閉查詢(xún)緩存，且不能開(kāi)啟。

• InnoDB分區(qū)表不支持外鍵

InnoDB存儲(chǔ)引擎的分區(qū)表不支持外鍵。

• 全文索引

即使分區(qū)表是InnoDB或者M(jìn)yISAM存儲(chǔ)引擎，全文索引也不被支持。例如執(zhí)行如下SQL會(huì)報(bào)錯(cuò): [Err] 1214 - The used table type doesn't support FULLTEXT indexes：

DROP TABLE if exists employees_partition;
CREATE TABLE if not exists `employees_partition` (
  `id` int(11) NOT NULL ,
  `uname` varchar(32) DEFAULT NULL COMMENT '員工姓名',
  `job_no` varchar(16) NOT NULL COMMENT '員工工號(hào)',
  FULLTEXT (`uname`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY KEY(uname) PARTITIONS 32 ;

但是去掉PARTITION BY KEY(uname) PARTITIONS 32 ;則OK。

• 空間列

一些POINT或者GEOMETRY這樣的空間數(shù)據(jù)類(lèi)型列，不能被用在分區(qū)表中。例如在分區(qū)表中定義一個(gè)名為geo的空間類(lèi)型列：geo GEOMETRY; 或者geo POINT; 會(huì)報(bào)錯(cuò)：[Err] 1178 - The storage engine for the table doesn't support GEOMETRY。如果不是分區(qū)表，則能成功創(chuàng)建該表。

• 臨時(shí)/日志表

臨時(shí)表和日志表都不能被分區(qū)。對(duì)日志表中執(zhí)行 ALTER TABLE ... PARTITION BY ...會(huì)報(bào)錯(cuò)。

• 算術(shù)&邏輯運(yùn)算符

分區(qū)表達(dá)式中可以使用+, -, * 這些運(yùn)算符。但是位運(yùn)算符例如|, &, ^, <<, >>, 和 ~ 是不支持的。例如PARTITION BY HASH(id+1) PARTITIONS 32 是支持的，但是PARTITION BY HASH(id<<1) PARTITIONS 32 則不支持。此外，分區(qū)表達(dá)式還有很多的內(nèi)置函數(shù)不支持，分區(qū)表達(dá)式支持的內(nèi)置函數(shù)可參考：https://dev.mysql.com/doc/refman/5.7/en/partitioning-limitations-functions.html

• 分區(qū)鍵數(shù)據(jù)類(lèi)型

分區(qū)鍵必須要么是整型列，要么是整型列表達(dá)式。ENUM枚舉類(lèi)型的列不能被作為分區(qū)表達(dá)式。但是，這個(gè)限制有兩個(gè)特殊情況：

1. [LINEAR] KEY分區(qū)方式，只要不是TEXT或者BLOB類(lèi)型，其他任何類(lèi)型列都可以作為分區(qū)鍵。因?yàn)镸ySQL內(nèi)部的hash算法能夠正確處理這些類(lèi)型。PARTITION BY KEY(uname) PARTITIONS 32 是可以的，PARTITION BY HASH(uname) PARTITIONS 32 則不行。
2. RANGE COLUMNS 或者 LIST COLUMNS 分區(qū)方式，可以使用string，DATE和DATETIME類(lèi)型作為分區(qū)列，例如下面的SQL什么是有效的:

CREATE TABLE rc (c1 INT, c2 DATE)
PARTITION BY RANGE COLUMNS(c2) (
    PARTITION p0 VALUES LESS THAN('1990-01-01'),
    PARTITION p1 VALUES LESS THAN('1995-01-01'),
    PARTITION p2 VALUES LESS THAN('2000-01-01'),
    PARTITION p3 VALUES LESS THAN('2005-01-01'),
    PARTITION p4 VALUES LESS THAN(MAXVALUE)
);

CREATE TABLE lc (c1 INT, c2 CHAR(1))
PARTITION BY LIST COLUMNS(c2) (
    PARTITION p0 VALUES IN('a', 'd', 'g', 'j', 'm', 'p', 's', 'v', 'y'),
    PARTITION p1 VALUES IN('b', 'e', 'h', 'k', 'n', 'q', 't', 'w', 'z'),
    PARTITION p2 VALUES IN('c', 'f', 'i', 'l', 'o', 'r', 'u', 'x', NULL)
);

• Window系統(tǒng)不支持DATA DIRECTORY和INDEX DIRECTORY

我們都知道創(chuàng)建分區(qū)表時(shí)，可以為每個(gè)分區(qū)指定 DATA DIRECTORY 和 INDEX DIRECTORY。但是Window系統(tǒng)或者M(jìn)yISAM的子分區(qū)是不支持該語(yǔ)法的。

• 單數(shù)據(jù)庫(kù)實(shí)例&服務(wù)器資源

分區(qū)表歸根結(jié)底是在一個(gè)數(shù)據(jù)庫(kù)實(shí)例上。那么它就會(huì)受到單數(shù)據(jù)庫(kù)實(shí)例的連接數(shù)限制、 IO瓶頸、 swap空間、 FD等諸多限制。

分區(qū)限制參考：22.6 Restrictions and Limitations on Partitioning: https://dev.mysql.com/doc/refman/5.7/en/partitioning-limitations.html#id3385325

分區(qū)PK.分庫(kù)分表

看到這么多的限制，不要慌張。畢竟任何一項(xiàng)都有優(yōu)缺點(diǎn)，沒(méi)有銀彈。我們先對(duì)分區(qū)表一些我認(rèn)為完全可以接受的限制做一個(gè)說(shuō)明。

• 分區(qū)最大數(shù)

8192個(gè)分區(qū)數(shù)限制，雖然不像分庫(kù)分表可以無(wú)限制擴(kuò)容下去，但是即使按照單表千萬(wàn)的行業(yè)標(biāo)準(zhǔn)，也能妥妥的容納幾百億的的數(shù)據(jù)。除了淘寶訂單，頭條評(píng)論這種海量數(shù)據(jù)，我相信99%的業(yè)務(wù)場(chǎng)景是遠(yuǎn)遠(yuǎn)達(dá)不到這個(gè)上限的。

• 全文索引&InnoDB分區(qū)表不支持外鍵

現(xiàn)在應(yīng)該沒(méi)有對(duì)大表加外鍵的操作了吧？也基本上沒(méi)有業(yè)務(wù)場(chǎng)景需要用到數(shù)據(jù)庫(kù)的全文索引吧？有也是瞎搞，不接受反駁。

• 空間列&臨時(shí)表&日志表

用這些功能的就更少了，不接受反駁。

• Window系統(tǒng)不支持DATA DIRECTORY和INDEX DIRECTORY

用Window作為生產(chǎn)環(huán)境服務(wù)器的也是極少數(shù)，不接受反駁。

• 不支持查詢(xún)緩存

這個(gè)好像也沒(méi)啥用，如果真的查詢(xún)頻率很高，為什么不用Redis或者memcache呢？

• 分區(qū)鍵限制

仔細(xì)看看分區(qū)鍵，以及分區(qū)鍵表達(dá)式限制，也就那么回事。一些常用的比如選擇整型列例如用戶(hù)ID作為分區(qū)鍵，選擇字符串類(lèi)型列例如訂單號(hào)作為分區(qū)鍵，選擇日期時(shí)間作為分區(qū)鍵也都是支持的。所以，那些限制只在極端業(yè)務(wù)場(chǎng)景才會(huì)碰到。

接下來(lái)是一些確實(shí)有影響的限制。我們?cè)诜謪^(qū)表、單庫(kù)分表和分庫(kù)分表三種方案之間進(jìn)行對(duì)比如下（需要說(shuō)明的是分庫(kù)分表包括單庫(kù)分表和分庫(kù)分表）：

通過(guò)分區(qū)表、單庫(kù)分表和分庫(kù)分表三種方案的對(duì)比我們發(fā)現(xiàn)，單庫(kù)分表相比分區(qū)表完全沒(méi)有任何優(yōu)勢(shì)，它們都會(huì)受到單個(gè)數(shù)據(jù)庫(kù)實(shí)例引發(fā)的連接數(shù)、存儲(chǔ)能力、并發(fā)能力等的限制。單庫(kù)分表相對(duì)于分區(qū)表甚至還會(huì)引入一些不必要的麻煩，例如跨分片鍵的操作，即使這種操作頻率很低，但是只要有需求就需要自研或者引入第三方中間件，從而大大增加開(kāi)發(fā)成本和維護(hù)成本。而分區(qū)表應(yīng)對(duì)這類(lèi)操作則不需要任何代價(jià)，甚至還可以通過(guò)引入一個(gè)從庫(kù)給這些系統(tǒng)使用從而防止對(duì)核心主庫(kù)的影響。

分區(qū)表和單庫(kù)分表的并發(fā)能力有限，很多寶貴的資源都受到單個(gè)實(shí)例和服務(wù)器的限制，這才是一線(xiàn)互聯(lián)網(wǎng)公司核心數(shù)據(jù)不使用分區(qū)表的主要原因。例如美團(tuán)外賣(mài)訂單表，淘寶訂單表等，這些業(yè)務(wù)都有相同的特點(diǎn)：高并發(fā)、海量數(shù)據(jù)，所以只能選分庫(kù)分表。所以那些高并發(fā)，海量數(shù)據(jù)場(chǎng)景下才會(huì)碰到的問(wèn)題，例如冷熱數(shù)據(jù)分離，數(shù)據(jù)歸檔，擴(kuò)容等，就不在PK范圍之內(nèi)了。

但是為什么我還是要為分區(qū)表正名呢？因?yàn)闈M(mǎn)足高并發(fā)、海量數(shù)據(jù)的大表畢竟是小數(shù)公司。很多公司的很多業(yè)務(wù)表，雖然整個(gè)生命周期內(nèi)也會(huì)有幾億，甚至上十億，但是并不會(huì)有高并發(fā)的可能，這種業(yè)務(wù)表就非常適合分區(qū)表！畢竟分區(qū)表能夠滿(mǎn)足我們需求的情況下，它的開(kāi)發(fā)成本和維護(hù)成本要比分庫(kù)分表小很多呀！

分區(qū)總結(jié)

MySQL的分區(qū)發(fā)展這么多年，從來(lái)沒(méi)見(jiàn)過(guò)官方有要將其拋棄的想法。這是因?yàn)?，在很多特定業(yè)務(wù)場(chǎng)景下，它的便捷性和對(duì)性能的提升是顯而易見(jiàn)的。廝大大說(shuō)過(guò)：沒(méi)有蹩腳的中間件，只有蹩腳的程序員！我對(duì)分區(qū)的評(píng)價(jià)則是：存在即合理！

如果你的業(yè)務(wù)滿(mǎn)足如下的特點(diǎn)，可以大膽嘗試使用分區(qū)表：

1. 可預(yù)估生命周期內(nèi)數(shù)據(jù)量在十億量級(jí)，而不是百億甚至千億的海量數(shù)據(jù)；
2. 不會(huì)有高并發(fā)的可能，即你的用戶(hù)是有一定局限性的，而不會(huì)成為全民爆款；

筆者就碰到很多業(yè)務(wù)非常適合使用分區(qū)表，這類(lèi)大表生命周期內(nèi)的上限是絕對(duì)可以預(yù)估在10億量級(jí)以下的，即使這些表將來(lái)超過(guò)10億，那起碼也是若干年以后的事情。一個(gè)方案能抗3~5年那絕對(duì)是一個(gè)優(yōu)秀的方案，如果能抗10年，那對(duì)于現(xiàn)階段來(lái)說(shuō)，絕對(duì)是一個(gè)完美的方案了。

筆者對(duì)一個(gè)重構(gòu)為分區(qū)表的業(yè)務(wù)表估算如下：

目前存量數(shù)據(jù)2kw，日增長(zhǎng)4w，即年增長(zhǎng)約1500w。
分區(qū)表設(shè)定128個(gè)分區(qū)，每個(gè)分區(qū)表約定上限1kw，那么總計(jì)可存儲(chǔ)128kw數(shù)據(jù)（12.8億數(shù)據(jù)）。

• 如果年復(fù)合增長(zhǎng)10%，可以確保業(yè)務(wù)運(yùn)行24年;
• 如果年復(fù)合增長(zhǎng)20%，可以確保業(yè)務(wù)運(yùn)行16年;
• 如果年復(fù)合增長(zhǎng)50%，可以確保業(yè)務(wù)運(yùn)行10年;
• 如果年復(fù)合增長(zhǎng)100%，可以確保業(yè)務(wù)運(yùn)行7年;
• 如果年復(fù)合增長(zhǎng)200%，可以確保業(yè)務(wù)運(yùn)行5年;

所以，分區(qū)表在特定業(yè)務(wù)場(chǎng)景下，絕對(duì)是一個(gè)既省時(shí)又省力，還能減少以后維護(hù)成本的絕佳方案。了解每個(gè)技術(shù)的優(yōu)缺點(diǎn)，然后以最小的代價(jià)，解決業(yè)務(wù)的痛點(diǎn)。而不是看著網(wǎng)上一些文章，自己沒(méi)有經(jīng)過(guò)任何求證，就否定一門(mén)技術(shù)。說(shuō)不定在你對(duì)她轉(zhuǎn)身離去的時(shí)候，你錯(cuò)過(guò)了很美麗的風(fēng)景！