MySQL數(shù)據(jù)庫(kù)分表分區(qū)
防偽碼：當(dāng)你終于沉默，成熟才剛剛開(kāi)始。
為什么要分表和分區(qū)？
我們的數(shù)據(jù)庫(kù)數(shù)據(jù)越來(lái)越大，隨之而來(lái)的是單個(gè)表中數(shù)據(jù)太多。以至于查詢(xún)書(shū)讀變慢，而且
由于表的鎖機(jī)制導(dǎo)致應(yīng)用操作也搜到嚴(yán)重影響，出現(xiàn)了數(shù)據(jù)庫(kù)性能瓶頸。
mysql 中有一種機(jī)制是表鎖定和行鎖定，是為了保證數(shù)據(jù)的完整性。表鎖定表示你們都不能
對(duì)這張表進(jìn)行操作，必須等我對(duì)表操作完才行。行鎖定也一樣，別的 sql 必須等我對(duì)這條數(shù)
據(jù)操作完了，才能對(duì)這條數(shù)據(jù)進(jìn)行操作。當(dāng)出現(xiàn)這種情況時(shí)，我們可以考慮分表或分區(qū)。
1、分表
什么是分表？
分表是將一個(gè)大表按照一定的規(guī)則分解成多張具有獨(dú)立存儲(chǔ)空間的實(shí)體表，每個(gè)表都對(duì)應(yīng)三
個(gè)文件，MYD 數(shù)據(jù)文件，.MYI 索引文件，.frm 表結(jié)構(gòu)文件。這些表可以分布在同一塊磁盤(pán)
上，也可以在不同的機(jī)器上。app 讀寫(xiě)的時(shí)候根據(jù)事先定義好的規(guī)則得到對(duì)應(yīng)的表名，然后
去操作它。
將單個(gè)數(shù)據(jù)庫(kù)表進(jìn)行拆分，拆分成多個(gè)數(shù)據(jù)表，然后用戶(hù)訪(fǎng)問(wèn)的時(shí)候，根據(jù)一定的算法（如
用 hash 的方式，也可以用求余（取模）的方式），讓用戶(hù)訪(fǎng)問(wèn)不同的表，這樣數(shù)據(jù)分散到多
個(gè)數(shù)據(jù)表中，減少了單個(gè)數(shù)據(jù)表的訪(fǎng)問(wèn)壓力。提升了數(shù)據(jù)庫(kù)訪(fǎng)問(wèn)性能。分表的目的就在于此，
減小數(shù)據(jù)庫(kù)的負(fù)擔(dān)，縮短查詢(xún)時(shí)間。
Mysql 分表分為垂直切分和水平切分
垂直切分是指數(shù)據(jù)表列的拆分，把一張列比較多的表拆分為多張表
通常我們按以下原則進(jìn)行垂直拆分:
把不常用的字段單獨(dú)放在一張表;
把 text，blob（binary large object，二進(jìn)制大對(duì)象）等大字段拆分出來(lái)放在附表中;
經(jīng)常組合查詢(xún)的列放在一張表中;
垂直拆分更多時(shí)候就應(yīng)該在數(shù)據(jù)表設(shè)計(jì)之初就執(zhí)行的步驟，然后查詢(xún)的時(shí)候用 jion 關(guān)鍵起來(lái)
即可。
水平拆分是指數(shù)據(jù)表行的拆分，把一張的表的數(shù)據(jù)拆成多張表來(lái)存放。
水平拆分原則
通常情況下，我們使用 hash、取模等方式來(lái)進(jìn)行表的拆分
比如一張有 400W 的用戶(hù)表 users，為提高其查詢(xún)效率我們把其分成 4 張表 users1，users2，
users3，users4
通過(guò)用 ID 取模的方法把數(shù)據(jù)分散到四張表內(nèi) Id%4= [0,1,2,3]
然后查詢(xún),更新,刪除也是通過(guò)取模的方法來(lái)查詢(xún)
部分業(yè)務(wù)邏輯也可以通過(guò)地區(qū)，年份等字段來(lái)進(jìn)行歸檔拆分;
進(jìn)行拆分后的表，這時(shí)我們就要約束用戶(hù)查詢(xún)行為。比如我們是按年來(lái)進(jìn)行拆分的,這個(gè)時(shí)
候在頁(yè)面設(shè)計(jì)上就約束用戶(hù)必須要先選擇年,然后才能進(jìn)行查詢(xún)。
分表的幾種方式：
1）mysql 集群
它并不是分表，但起到了和分表相同的作用。集群可分擔(dān)數(shù)據(jù)庫(kù)的操作次數(shù)，將任務(wù)分擔(dān)到
多臺(tái)數(shù)據(jù)庫(kù)上。集群可以讀寫(xiě)分離，減少讀寫(xiě)壓力。從而提升數(shù)據(jù)庫(kù)性能。
2）預(yù)先估計(jì)會(huì)出現(xiàn)大數(shù)據(jù)量并且訪(fǎng)問(wèn)頻繁的表，將其分為若干個(gè)表
根據(jù)一定的算法（如用 hash 的方式，也可以用求余（取模）的方式）讓用戶(hù)訪(fǎng)問(wèn)不同的表。
例如論壇里面發(fā)表帖子的表，時(shí)間長(zhǎng)了這張表肯定很大，幾十萬(wàn)，幾百萬(wàn)都有可能。聊天室
里面信息表，幾十個(gè)人在一起一聊一個(gè)晚上，時(shí)間長(zhǎng)了，這張表的數(shù)據(jù)肯定很大。像這樣的
情況很多。所以這種能預(yù)估出來(lái)的大數(shù)據(jù)量表，我們就事先分出個(gè) N 個(gè)表，這個(gè) N 是多少，
根據(jù)實(shí)際情況而定。以聊天信息表為例：我們事先建 100 個(gè)這樣的表，
message_00,message_01,message_02..........message_98,message_99.然后根據(jù)用戶(hù)的 ID 來(lái)判
斷這個(gè)用戶(hù)的聊天信息放到哪張表里面，可以用 hash 的方式來(lái)獲得，也可以用求余的方式
來(lái)獲得，方法很多。
或者可以設(shè)計(jì)每張表容納的數(shù)據(jù)量是 N 條，那么如何判斷某張表的數(shù)據(jù)是否容量已滿(mǎn)呢？
可以在程序段對(duì)于要新增數(shù)據(jù)的表，在插入前先做統(tǒng)計(jì)表記錄數(shù)量的操作，當(dāng)<N 條數(shù)據(jù)，
就直接插入，當(dāng)已經(jīng)到達(dá)閥值，可以在程序段新創(chuàng)建數(shù)據(jù)庫(kù)表（或者已經(jīng)事先創(chuàng)建好），再
執(zhí)行插入操作）。
3）利用 merge 存儲(chǔ)引擎來(lái)實(shí)現(xiàn)分表
如果要把已有的大數(shù)據(jù)量表分開(kāi)比較痛苦，最痛苦的事就是改代碼，因?yàn)槌绦蚶锩娴?sql 語(yǔ)
句已經(jīng)寫(xiě)好了，用 merge 存儲(chǔ)引擎來(lái)實(shí)現(xiàn)分表, 這種方法比較適合。
merge 分表，分為主表和子表，主表類(lèi)似于一個(gè)殼子，邏輯上封裝了子表，實(shí)際上數(shù)據(jù)都是
存儲(chǔ)在子表中的。
我們可以通過(guò)主表插入和查詢(xún)數(shù)據(jù)，如果清楚分表規(guī)律，也可以直接操作子表。
下面我們來(lái)實(shí)現(xiàn)一個(gè)簡(jiǎn)單的利用 merge 存儲(chǔ)引擎來(lái)實(shí)現(xiàn)分表的演示：
創(chuàng)建一個(gè)完整表存儲(chǔ)著所有的成員信息（表名為 member）
mysql> drop database IF EXISTS test;
mysql> use test;
create table member(
id big int auto_increment primary key,
name varchar(20),
sex tiny int not nulldefault '0'
)engine=myisamdefault charset=utf8 auto_increment=1;
加入點(diǎn)數(shù)據(jù)：
mysql> insert into member(name,sex) values('tom1',1);
mysql> insert into member(name,sex) select name,sex from member;
第二條語(yǔ)句多執(zhí)行幾次就有了很多數(shù)據(jù)
mysql> select * from member;
+----+------+-----+
| id | name | sex |
+----+------+-----+
| 1 | tom1 | 1 |
| 2 | tom1 | 1 |
| 3 | tom1 | 1 |
| 4 | tom1 | 1 |
| 5 | tom1 | 1 |
| 6 | tom1 | 1 |
| 7 | tom1 | 1 |
| 8 | tom1 | 1 |
| 9 | tom1 | 1 |
| 10 | tom1 | 1 |
| 11 | tom1 | 1 |
| 12 | tom1 | 1 |
| 13 | tom1 | 1 |
| 14 | tom1 | 1 |
| 15 | tom1 | 1 |
| 16 | tom1 | 1 |
+----+------+-----+
下面我們進(jìn)行分表，這里我們把 member 分兩個(gè)表 tb_member1,tb_member2
mysql> use test;
DROP table IF EXISTS tb_member1;
create table tb_member1(
id big int primary key ,
name varchar(20),
sex tiny int not null default '0'
)ENGINE=MyISAM DEFAULT CHARSET=utf8 ;
DROP table IF EXISTS tb_member2;
create table tb_member2(
idbigint primary key,
name varchar(20),
sextinyint not null default '0'
)ENGINE=MyISAM DEFAULT CHARSET=utf8;
//創(chuàng)建 tb_member2 也可以用下面的語(yǔ)句 create table tb_member2 like tb_member1;
創(chuàng)建主表 tb_member
DROP table IF EXISTS tb_member;
create table tb_member(
id bigint primary key ,
name varchar(20),
sex tinyint not null default '0'
) ENGINE=MERGEUNION=(tb_member1,tb_member2) INSERT_METHOD=LAST CHARSET=utf8 ;
注：INSERT_METHOD,此參數(shù)INSERT_METHOD = NO 表示該表不能做任何寫(xiě)入操作只作為查
詢(xún)使用,INSERT_METHOD = LAST 表示插入到最后的一張表里面。INSERT_METHOD = first 表示
插入到第一張表里面。
查看一下 tb_member 表的結(jié)構(gòu):
mysql>desc tb_member;
mysql>desc tb_member;
+-------+-------------+------+-----+---------+-----------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-----------------------------------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| sex | tinyint(4) | NO | | 0 | |
+-------+-------------+------+-----+---------+------------------------------------------+
3 rows in set (0.00 sec)
注：查看子表與主表的字段定義要一致
接下來(lái)，我們把數(shù)據(jù)分到兩個(gè)分表中去：
mysql> insert into tb_member1(id,name,sex) select id,name,sex from member where id%2=0;
mysql> insert into tb_member2(id,name,sex) select id,name,sex from member where id%2=1;
查看兩個(gè)子表的數(shù)據(jù)：
mysql> select * from tb_member1;
+----+------+-----+
| id | name | sex |
+----+------+-----+
| 16 | tom1 | 1 |
| 14 | tom1 | 1 |
| 12 | tom1 | 1 |
| 10 | tom1 | 1 |
| 8 | tom1 | 1 |
| 6 | tom1 | 1 |
| 4 | tom1 | 1 |
| 2 | tom1 | 1 |
+----+------+-----+
8 rows in set (0.00 sec)
mysql> select * from tb_member2;
+----+------+-----+
| id | name | sex |
+----+------+-----+
| 3 | tom1 | 1 |
| 1 | tom1 | 1 |
| 5 | tom1 | 1 |
| 7 | tom1 | 1 |
| 9 | tom1 | 1 |
| 11 | tom1 | 1 |
| 13 | tom1 | 1 |
| 15 | tom1 | 1 |
+----+------+-----+
8 rows in set (0.00 sec)
查看一下主表的數(shù)據(jù)：
mysql> select * from tb_member;
+----+------+-----+
| id | name | sex |
+----+------+-----+
| 16 | tom1 | 1 |
| 14 | tom1 | 1 |
| 12 | tom1 | 1 |
| 10 | tom1 | 1 |
| 8 | tom1 | 1 |
| 6 | tom1 | 1 |
| 4 | tom1 | 1 |
| 2 | tom1 | 1 |
| 15 | tom1 | 1 |
| 13 | tom1 | 1 |
| 11 | tom1 | 1 |
| 9 | tom1 | 1 |
| 7 | tom1 | 1 |
| 5 | tom1 | 1 |
| 3 | tom1 | 1 |
| 1 | tom1 | 1 |
+----+------+-----+
16 rows in set (0.00 sec)
mysql> select * from tb_member where id=3;
+----+------+-----+
| id | name | sex |
+----+------+-----+
| 3 | tom1 | 1 |
+----+------+-----+
1 row in set (0.00 sec)
注意：總表只是一個(gè)外殼，存取數(shù)據(jù)發(fā)生在一個(gè)一個(gè)的子表里面。
注意：每個(gè)子表都有自已獨(dú)立的相關(guān)表文件，而主表只是一個(gè)殼，并沒(méi)有完整的相關(guān)表文件
[root@localhost ~]# ls -l /usr/local/mysql/data/test/tb_member*
-rw-r-----. 1 mysqlmysql 8614 Sep 15 21:49 /usr/local/mysql/data/test/tb_member1.frm
-rw-r-----. 1 mysqlmysql 320 Sep 16 00:02 /usr/local/mysql/data/test/tb_member1.MYD
-rw-r-----. 1 mysqlmysql 2048 Sep 16 00:43 /usr/local/mysql/data/test/tb_member1.MYI
-rw-r-----. 1 mysqlmysql 8614 Sep 15 21:50 /usr/local/mysql/data/test/tb_member2.frm
-rw-r-----. 1 mysqlmysql 180 Sep 16 00:02 /usr/local/mysql/data/test/tb_member2.MYD
-rw-r-----. 1 mysqlmysql 2048 Sep 16 00:43 /usr/local/mysql/data/test/tb_member2.MYI
-rw-r-----. 1 mysqlmysql 8614 Sep 16 21:12 /usr/local/mysql/data/test/tb_member3.frm
-rw-r-----. 1 mysqlmysql 0 Sep 16 21:12 /usr/local/mysql/data/test/tb_member3.MYD
-rw-r-----. 1 mysqlmysql 1024 Sep 16 21:12 /usr/local/mysql/data/test/tb_member3.MYI
-rw-r-----. 1 mysqlmysql 8614 Sep 16 21:14 /usr/local/mysql/data/test/tb_member.frm
-rw-r-----. 1 mysqlmysql 53 Sep 16 21:14 /usr/local/mysql/data/test/tb_member.MRG
2、分區(qū)
什么是分區(qū)？
分區(qū)和分表相似，都是按照規(guī)則分解表。不同在于分表將大表分解為若干個(gè)獨(dú)立的實(shí)體表，
而分區(qū)是將數(shù)據(jù)分段劃分在多個(gè)位置存放，分區(qū)后，表還是一張表，但數(shù)據(jù)散列到多個(gè)位置
了。app 讀寫(xiě)的時(shí)候操作的還是表名字，db 自動(dòng)去組織分區(qū)的數(shù)據(jù)。
分區(qū)主要有兩種形式：
水平分區(qū)（Horizontal Partitioning）這種形式分區(qū)是對(duì)表的行進(jìn)行分區(qū)，所有在表中定義的
列在每個(gè)數(shù)據(jù)集中都能找到，所以表的特性依然得以保持。
舉個(gè)簡(jiǎn)單例子：一個(gè)包含十年發(fā)票記錄的表可以被分區(qū)為十個(gè)不同的分區(qū)，每個(gè)分區(qū)包含的
是其中一年的記錄。
垂直分區(qū)（Vertical Partitioning）這種分區(qū)方式一般來(lái)說(shuō)是通過(guò)對(duì)表的垂直劃分來(lái)減少目標(biāo)表
的寬度，使某些特定的列被劃分到特定的分區(qū)，每個(gè)分區(qū)都包含了其中的列所對(duì)應(yīng)的行。
舉個(gè)簡(jiǎn)單例子：一個(gè)包含了大 text 和 BLOB 列的表，這些 text 和 BLOB 列又不經(jīng)常被訪(fǎng)問(wèn)，
這時(shí)候就要把這些不經(jīng)常使用的 text 和 BLOB 了劃分到另一個(gè)分區(qū)，在保證它們數(shù)據(jù)相關(guān)性
的同時(shí)還能提高訪(fǎng)問(wèn)速度。
分區(qū)技術(shù)支持
在 5.6 之前，使用這個(gè)參數(shù)查看當(dāng)將配置是否支持分區(qū)
mysql> SHOW VARIABLES LIKE '%partition%';
+-----------------------+---------------+
|Variable_name | Value |
+-----------------------+---------------+
| have_partition_engine | YES |
+-----------------------+------------------+
如果是 yes 表示你當(dāng)前的配置支持分區(qū)
在 5.6 及以采用后，則采用如下方式進(jìn)行查看
mysql> show plugins;
在顯示結(jié)果中，可以看到 partition 是 ACTIVE 的，表示支持分區(qū)
下面我們先演示一個(gè)按照范圍(range)方式的表分區(qū)
創(chuàng)建 range 分區(qū)表
mysql> use test2;
mysql> create table if not exists user (
->idint not null auto_increment,
->name varchar(30) not null default '',
->sexint(1) not null default '0',
->primary key(id)
-> )default charset=utf8 auto_increment=1
->partition by range(id) (
->partition p0 values less than (3),
->partition p1 values less than (6),
->partition p2 values less than (9),
->partition p3 values less than (12),
->partition p4 values less than maxvalue
-> );
插入些數(shù)據(jù)
mysql> insert into test2.user(name,sex)values ('tom1','0');
mysql> insert into test2.user(name,sex)values ('tom2','1');
mysql> insert into test2.user(name,sex)values ('tom3','1');
mysql> insert into test2.user(name,sex)values ('tom4','0');
mysql> insert into test2.user(name,sex)values ('tom5','0');
mysql> insert into test2.user(name,sex)values ('tom6','1');
mysql> insert into test2.user(name,sex)values ('tom7','1');
mysql> insert into test2.user(name,sex)values ('tom8','1');
mysql> insert into test2.user(name,sex)values ('tom9','1');
mysql> insert into test2.user(name,sex)values ('tom10','1');
mysql> insert into test2.user(name,sex)values ('tom11','1');
mysql> insert into test2.user(name,sex)values ('tom12','1');
mysql> insert into test2.user(name,sex)values ('tom13','1');
mysql> insert into test2.user(name,sex)values ('tom14','1');
到存放數(shù)據(jù)庫(kù)表文件的地方看一下
[root@localhost ~]# ls -l /usr/local/mysql/data/test2/user*
-rw-r-----. 1 mysqlmysql 8614 Sep 16 21:46 /usr/local/mysql/data/test2/user.frm
-rw-r-----. 1 mysqlmysql 98304 Sep 16 21:48 /usr/local/mysql/data/test2/user#P#p0.ibd
-rw-r-----. 1 mysqlmysql 98304 Sep 16 21:48 /usr/local/mysql/data/test2/user#P#p1.ibd
-rw-r-----. 1 mysqlmysql 98304 Sep 16 21:49 /usr/local/mysql/data/test2/user#P#p2.ibd
-rw-r-----. 1 mysqlmysql 98304 Sep 16 21:49 /usr/local/mysql/data/test2/user#P#p3.ibd
-rw-r-----. 1 mysqlmysql 98304 Sep 16 21:49 /usr/local/mysql/data/test2/user#P#p4.ibd
mysql> select count(id) as count from user;
+-------+
| count |
+-------+
| 14 |
+-------+
1 row in set (0.00 sec)
從 information_schema 系統(tǒng)庫(kù)中的 partitions 表中查看分區(qū)信息
mysql> select * from information_schema.partitions where table_schema='test2' and
table_name='user'\G;
從某個(gè)分區(qū)中查詢(xún)數(shù)據(jù)
mysql> select * from test2.user partition(p0);
新增分區(qū)
mysql> alter table test2.user add partition (partition partionname values less than (n));
刪除分區(qū)
當(dāng)刪除了一個(gè)分區(qū)，也同時(shí)刪除了該分區(qū)中所有的數(shù)據(jù)。
ALTER TABLE test2.user DROP PARTITION p3;
分區(qū)的合并
下面的 SQL，將 p1 – p3 合并為 2 個(gè)分區(qū) p01– p02
mysql> alter table test2.user
-> reorganize partition p1,p2,p3 into
-> (partition p01 values less than (8),
->partition p02 values less than (12)
-> );
[root@localhost ~]# ls -l /usr/local/mysql/data/test2/user*
-rw-r-----. 1 mysqlmysql 8614 Sep 16 22:06 /usr/local/mysql/data/test2/user.frm
-rw-r-----. 1 mysqlmysql 98304 Sep 16 22:06 /usr/local/mysql/data/test2/user#P#p01.ibd
-rw-r-----. 1 mysqlmysql 98304 Sep 16 22:06 /usr/local/mysql/data/test2/user#P#p02.ibd
-rw-r-----. 1 mysqlmysql 98304 Sep 16 21:48 /usr/local/mysql/data/test2/user#P#p0.ibd
-rw-r-----. 1 mysqlmysql 98304 Sep 16 21:49 /usr/local/mysql/data/test2/user#P#p4.ibd
mysql> select * from test2.user partition(p01);
+----+------+-----+
| id | name | sex |
+----+------+-----+
| 3 | tom3 | 1 |
| 4 | tom4 | 0 |
| 5 | tom5 | 0 |
| 6 | tom6 | 1 |
| 7 | tom7 | 1 |
+----+------+-----+
5 rows in set (0.00 sec)
未分區(qū)表和分區(qū)表性能測(cè)試
創(chuàng)建一個(gè)未分區(qū)的表
mysql> create table test2.tab1(c1 int,c2 varchar(30),c3 date);
創(chuàng)建分區(qū)表,按日期的年份拆分
mysql> CREATE TABLE test2.tab2 ( c1 int, c2 varchar(30) , c3 date )
PARTITION BY RANGE (year(c3)) (PARTITION p0 VALUES LESS THAN (1995),
PARTITION p1 VALUES LESS THAN (1996) , PARTITION p2 VALUES LESS THAN (1997) ,
PARTITION p3 VALUES LESS THAN (1998) , PARTITION p4 VALUES LESS THAN (1999) ,
PARTITION p5 VALUES LESS THAN (2000) , PARTITION p6 VALUES LESS THAN (2001) ,
PARTITION p7 VALUES LESS THAN (2002) , PARTITION p8 VALUES LESS THAN (2003) ,
PARTITION p9 VALUES LESS THAN (2004) , PARTITION p10 VALUES LESS THAN (2010),
PARTITION p11 VALUES LESS THAN MAXVALUE );
注意:最后一行，考慮到可能的最大值
通過(guò)存儲(chǔ)過(guò)程插入 100 萬(wàn)條測(cè)試數(shù)據(jù)
創(chuàng)建存儲(chǔ)過(guò)程：
mysql> delimiter $$//指定存儲(chǔ)過(guò)程結(jié)束符
mysql>CREATE PROCEDURE load_part_tab()
begin
declare v int default 0;
while v < 1000000
do
insert into test2.tab1
values (v,'testing partitions',adddate('1995-01-01',(rand(v)*36520) mod 3652));
set v = v + 1;
end while;
end
$$
注：RAND()函數(shù)在 0 和 1 之間產(chǎn)生一個(gè)隨機(jī)數(shù)，如果一個(gè)整數(shù)參數(shù) N 被指定，它被用作種
子值。每個(gè)種子產(chǎn)生的隨機(jī)數(shù)序列是不同的。
執(zhí)行存儲(chǔ)過(guò)程 load_part_tab 向 test2.tab1 表插入數(shù)據(jù)
mysql> delimiter ;
mysql> call load_part_tab();
向 test2.tab2 表中插入數(shù)據(jù)
mysql> insert into test2.tab2 select * from test2.tab1;
測(cè)試 SQL 性能
mysql> select count(*) from test2.tab1 where c3 > '1995-01-01' and c3 < '1995-12-31';
+----------+
| count(*) |
+----------+
| 219642 |
+----------+
1 row in set (0.84 sec)
mysql> select count(*) from test2.tab2 where c3 > '1995-01-01' and c3 < '1995-12-31';
+----------+
| count(*) |
+----------+
| 219642 |
+----------+
1 row in set (0.09 sec)
結(jié)果表明分區(qū)表比未分區(qū)表的執(zhí)行時(shí)間少很多。
通過(guò) explain 語(yǔ)句來(lái)分析執(zhí)行情況
mysql> flush tables;
mysql> explain select count(*) from test2.tab1 where c3 > '1995-01-01' and c3 < '1995-12-31'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tab1
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2001552
filtered: 11.11
Extra: Using where
1 row in set, 1 warning (0.00 sec)
mysql> explain select count(*) from test2.tab2 where c3 > '1995-01-01' and c3 < '1995-12-31'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tab2
partitions: p1
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 220206
filtered: 11.11
Extra: Using where
1 row in set, 1 warning (0.00 sec)
explain 語(yǔ)句顯示了 SQL 查詢(xún)要處理的記錄數(shù)目可以看出分區(qū)表比未分區(qū)表的明顯掃描的記
錄要少很多。
創(chuàng)建索引后情況測(cè)試
mysql> create index idx_of_c3 on test2.tab1(c3);
Query OK, 0 rows affected (5.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> create index idx_of_c3 on test2.tab2(c3);
Query OK, 0 rows affected (4.87 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> flush tables;
mysql> select count(*) from test2.tab1 where c3 > '1996-01-01' and c3 < '1996-12-31';
+----------+
| count(*) |
+----------+
| 220264 |
+----------+
1 row in set (0.12 sec)
重啟 mysqld 服務(wù)
mysql> select count(*) from test2.tab2 where c3 > '1996-01-01' and c3 < '1996-12-31';
+----------+
| count(*) |
+----------+
| 220264 |
+----------+
1 row in set (0.11 sec)
創(chuàng)建索引后分區(qū)表比未分區(qū)表相差不大（數(shù)據(jù)量越大差別會(huì)明顯些）
mysql 分區(qū)的類(lèi)型
1.RANGE 分區(qū)
基于屬于一個(gè)給定連續(xù)區(qū)間的列值，把多行分配給分區(qū)。這些區(qū)間要連續(xù)且不能相互重疊，
使用 VALUES LESS THAN 操作符來(lái)進(jìn)行定義。以下是實(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 (21)
);
按照這種分區(qū)方案，在商店 1 到 5 工作的雇員相對(duì)應(yīng)的所有行被保存在分區(qū) P0 中，商店 6
到 10 的雇員保存在 P1 中，依次類(lèi)推。注意，每個(gè)分區(qū)都是按順序進(jìn)行定義，從最低到最高。
對(duì)于包含數(shù)據(jù)(72, 'Michael', 'Widenius', '1998-06-25', NULL, 13)的一個(gè)新行，可以很容易地確
定它將插入到 p2 分區(qū)中，但是如果增加了一個(gè)編號(hào)為第 21 的商店，將會(huì)發(fā)生什么呢？在這
種方案下，由于沒(méi)有規(guī)則把 store_id 大于 20 的商店包含在內(nèi)，服務(wù)器將不知道把該行保存
在何處，將會(huì)導(dǎo)致錯(cuò)誤。要避免這種錯(cuò)誤，可以創(chuàng)建 maxvalue 分區(qū)，所有不在指定范圍內(nèi)
的記錄都會(huì)被存儲(chǔ)到 maxvalue 所在的分區(qū)中。
mysql> alter table test2.user add partition (partition p4 values less than maxvalue);
2.LIST 分區(qū)
類(lèi)似于按 RANGE 分區(qū)，區(qū)別在于 LIST 分區(qū)是基于列值匹配一個(gè)離散值集合中的某個(gè)值來(lái)進(jìn)
行選擇。
LIST 分區(qū)通過(guò)使用“PARTITION BY LIST(expr)”來(lái)實(shí)現(xiàn)，其中“expr” 是某列值或一個(gè)基于某個(gè)列
值、并返回一個(gè)整數(shù)值的表達(dá)式，然后通過(guò)“VALUES IN (value_list)”的方式來(lái)定義每個(gè)分區(qū)，
其中“value_list”是一個(gè)通過(guò)逗號(hào)分隔的整數(shù)列表。
要按照屬于同一個(gè)地區(qū)商店的行保存在同一個(gè)分區(qū)中的方式來(lái)分割表，可以使用下面的
“CREATE TABLE”語(yǔ)句：
CREATE TABLE employees1 (
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)
)；
這使得在表中增加或刪除指定地區(qū)的雇員記錄變得容易起來(lái)。例如，假定西區(qū)的所有音像店
都賣(mài)給了其他公司。那么與在西區(qū)音像店工作雇員相關(guān)的所有記錄（行）可以使用查詢(xún)“ALTER
TABLE employees DROP PARTITION pWest；”來(lái)進(jìn)行刪除，它與具有同樣作用的 DELETE （刪
除）查詢(xún)“DELETE query DELETE FROM employees WHERE store_id IN (4,12,13,14,18)；”比起來(lái)，
要有效得多。
要點(diǎn)：如果試圖插入列值不在分區(qū)值列表中的一行時(shí)，那么“INSERT”查詢(xún)將失敗并報(bào)錯(cuò)。例
如，假定 LIST 分區(qū)的采用上面的方案，下面的插入將失?。?/span>
INSERT INTO employees VALUES(224, 'Linus', 'Torvalds', '2002-05-01', '2004-10-12', 42, 21);
這是因?yàn)椤皊tore_id”列值 21 不能在用于定義分區(qū) pNorth, pEast, pWest,或 pCentral 的值列表中
找到。要重點(diǎn)注意的是，LIST 分區(qū)沒(méi)有類(lèi)似如“VALUES LESS THAN MAXVALUE”這樣的包含其
他值在內(nèi)的定義。將要匹配的任何值都必須在值列表中找到。
3.HASH 分區(qū)
這種模式允許 DBA 通過(guò)對(duì)表的一個(gè)或多個(gè)列的 Hash Key 進(jìn)行計(jì)算，最后通過(guò)這個(gè) Hash 碼不
同數(shù)值對(duì)應(yīng)的數(shù)據(jù)區(qū)域進(jìn)行分區(qū)。
hash 分區(qū)的目的是將數(shù)據(jù)均勻的分布到預(yù)先定義的各個(gè)分區(qū)中，保證各分區(qū)的數(shù)據(jù)量大致
一致。在 RANGE 和 LIST 分區(qū)中，必須明確指定一個(gè)給定的列值或列值集合應(yīng)該保存在哪個(gè)
分區(qū)中；而在 HASH 分區(qū)中，MYSQL 自動(dòng)完成這些工作，用戶(hù)所要定一個(gè)列值或者表達(dá)式，
以及指定被分區(qū)的表將要被分割成的分區(qū)數(shù)量。
mysql> create table t_hash( a int(11), b datetime) partition by hash(year(b)) partitions 4;
hash 的分區(qū)函數(shù)頁(yè)需要返回一個(gè)整數(shù)值。partitions 子句中的值是一個(gè)非負(fù)整數(shù)，不加的
partitions 子句的話(huà)，默認(rèn)為分區(qū)數(shù)為 1。
mysql> insert into t_hash values(1,'2010-04-01');
該記錄會(huì)被放入分區(qū) p2 中。因?yàn)椴迦?2010-04-01 進(jìn)入表 t_hash,那么
MOD(YEAR('2010-04-01'),4)=2
mysql> select * from information_schema.partitions where table_schema='test2' and
table_name='t_hash'\G;
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test2
TABLE_NAME: t_hash
PARTITION_NAME: p0
SUBPARTITION_NAME: NULL
PARTITION_ORDINAL_POSITION: 1
SUBPARTITION_ORDINAL_POSITION: NULL
PARTITION_METHOD: HASH
SUBPARTITION_METHOD: NULL
PARTITION_EXPRESSION: year(b)
SUBPARTITION_EXPRESSION: NULL
PARTITION_DESCRIPTION: NULL
TABLE_ROWS: 0
AVG_ROW_LENGTH: 0
DATA_LENGTH: 16384
MAX_DATA_LENGTH: NULL
INDEX_LENGTH: 0
DATA_FREE: 0
CREATE_TIME: 2016-09-16 22:48:59
UPDATE_TIME: 2016-09-17 23:36:22
CHECK_TIME: NULL
CHECKSUM: NULL
PARTITION_COMMENT:
NODEGROUP: default
TABLESPACE_NAME: NULL
*************************** 2. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test2
TABLE_NAME: t_hash
PARTITION_NAME: p1
SUBPARTITION_NAME: NULL
PARTITION_ORDINAL_POSITION: 2
SUBPARTITION_ORDINAL_POSITION: NULL
PARTITION_METHOD: HASH
SUBPARTITION_METHOD: NULL
PARTITION_EXPRESSION: year(b)
SUBPARTITION_EXPRESSION: NULL
PARTITION_DESCRIPTION: NULL
TABLE_ROWS: 0
AVG_ROW_LENGTH: 0
DATA_LENGTH: 16384
MAX_DATA_LENGTH: NULL
INDEX_LENGTH: 0
DATA_FREE: 0
CREATE_TIME: 2016-09-16 22:48:59
UPDATE_TIME: 2016-09-17 23:36:22
CHECK_TIME: NULL
CHECKSUM: NULL
PARTITION_COMMENT:
NODEGROUP: default
TABLESPACE_NAME: NULL
*************************** 3. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test2
TABLE_NAME: t_hash
PARTITION_NAME: p2
SUBPARTITION_NAME: NULL
PARTITION_ORDINAL_POSITION: 3
SUBPARTITION_ORDINAL_POSITION: NULL
PARTITION_METHOD: HASH
SUBPARTITION_METHOD: NULL
PARTITION_EXPRESSION: year(b)
SUBPARTITION_EXPRESSION: NULL
PARTITION_DESCRIPTION: NULL
TABLE_ROWS: 1
AVG_ROW_LENGTH: 16384
DATA_LENGTH: 16384
MAX_DATA_LENGTH: NULL
INDEX_LENGTH: 0
DATA_FREE: 0
CREATE_TIME: 2016-09-16 22:48:59
UPDATE_TIME: 2016-09-17 23:23:26
CHECK_TIME: NULL
CHECKSUM: NULL
PARTITION_COMMENT:
NODEGROUP: default
TABLESPACE_NAME: NULL
*************************** 4. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test2
TABLE_NAME: t_hash
PARTITION_NAME: p3
SUBPARTITION_NAME: NULL
PARTITION_ORDINAL_POSITION: 4
SUBPARTITION_ORDINAL_POSITION: NULL
PARTITION_METHOD: HASH
SUBPARTITION_METHOD: NULL
PARTITION_EXPRESSION: year(b)
SUBPARTITION_EXPRESSION: NULL
PARTITION_DESCRIPTION: NULL
TABLE_ROWS: 0
AVG_ROW_LENGTH: 0
DATA_LENGTH: 16384
MAX_DATA_LENGTH: NULL
INDEX_LENGTH: 0
DATA_FREE: 0
CREATE_TIME: 2016-09-16 22:48:59
UPDATE_TIME: 2016-09-17 23:23:26
CHECK_TIME: NULL
CHECKSUM: NULL
PARTITION_COMMENT:
NODEGROUP: default
TABLESPACE_NAME: NULL
4 rows in set (0.00 sec)
可以看到 P2 分區(qū)有一條記錄。當(dāng)前這個(gè)例子并不能把數(shù)據(jù)均勻的分布到各個(gè)分區(qū)，因?yàn)榘?/span>
照 YEAR 函數(shù)進(jìn)行的，該值本身是離散的。如果對(duì)連續(xù)的值進(jìn)行 HASH分區(qū)，如自增長(zhǎng)的主
鍵，則可以較好地將數(shù)據(jù)平均分布。
請(qǐng)思考：
mysql> insert into t_hash values(1,'2012-04-01');
記錄會(huì)插入哪個(gè)分區(qū)？
4.key 分區(qū)
key 分區(qū)和 hash 分區(qū)相似，不同在于 hash 分區(qū)是用戶(hù)自定義函數(shù)進(jìn)行分區(qū)，key 分區(qū)使用
mysql 數(shù)據(jù)庫(kù)提供的函數(shù)進(jìn)行分區(qū)，NDB cluster 使用 MD5 函數(shù)來(lái)分區(qū)，對(duì)于其他存儲(chǔ)引擎
mysql 使用內(nèi)部的 hash 函數(shù)。
mysql> create table t_key( a int(11), b datetime) partition by key(b) partitions 4;
上面的 RANGE、LIST、HASH、KEY 四種分區(qū)中，分區(qū)的條件必須是整形，如果不是整形需要
通過(guò)函數(shù)將其轉(zhuǎn)換為整形。
5.columns 分區(qū)
mysql-5.5 開(kāi)始支持 COLUMNS 分區(qū)，可視為 RANGE 和 LIST 分區(qū)的進(jìn)化，COLUMNS 分區(qū)可以
直接使用非整形數(shù)據(jù)進(jìn)行分區(qū)。COLUMNS 分區(qū)支持以下數(shù)據(jù)類(lèi)型：
所有整形，如 INT SMALLINT TINYINT BIGINT。FLOAT 和 DECIMAL 則不支持。
日期類(lèi)型，如 DATE 和 DATETIME。其余日期類(lèi)型不支持。
字符串類(lèi)型，如 CHAR、VARCHAR、BINARY 和 VARBINARY。BLOB 和 TEXT 類(lèi)型不支持。
COLUMNS 可以使用多個(gè)列進(jìn)行分區(qū)。
mysql 分表和分區(qū)有什么區(qū)別呢
1、實(shí)現(xiàn)方式上
a） mysql 的分表是真正的分表，一張表分成很多表后，每一個(gè)小表都是完整的一張表，都
對(duì)應(yīng)三個(gè)文件，一個(gè).MYD 數(shù)據(jù)文件，.MYI索引文件，.frm 表結(jié)構(gòu)文件。
b） 分區(qū)不一樣，一張大表進(jìn)行分區(qū)后，他還是一張表，不會(huì)變成二張表，但是他存放數(shù)據(jù)
的區(qū)塊變多了
2、數(shù)據(jù)處理上
a）分表后，數(shù)據(jù)都是存放在分表里，總表只是一個(gè)外殼，存取數(shù)據(jù)發(fā)生在一個(gè)一個(gè)的分表
里面。
b）分區(qū)呢，不存在分表的概念，分區(qū)只不過(guò)把存放數(shù)據(jù)的文件分成了許多小塊，分區(qū)后的
表呢，還是一張表，數(shù)據(jù)處理還是由自己來(lái)完成。
3、提高性能上
a）分表后，單表的并發(fā)能力提高了，磁盤(pán) I/O 性能也提高了。并發(fā)能力為什么提高了呢，
因?yàn)椴閷ひ淮嗡ǖ臅r(shí)間變短了，如果出現(xiàn)高并發(fā)的話(huà)，總表可以根據(jù)不同的查詢(xún)，將并發(fā)
壓力分到不同的小表里面。
b）mysql 提出了分區(qū)的概念，主要是想突破磁盤(pán) I/O 瓶頸，想提高磁盤(pán)的讀寫(xiě)能力，來(lái)增加
mysql 性能。
在這一點(diǎn)上，分區(qū)和分表的測(cè)重點(diǎn)不同，分表重點(diǎn)是存取數(shù)據(jù)時(shí)，如何提高 mysql 并發(fā)能力
上；而分區(qū)呢，如何突破磁盤(pán)的讀寫(xiě)能力，從而達(dá)到提高 mysql 性能的目的。
4、實(shí)現(xiàn)的難易度上
a）分表的方法有很多，用 merge 來(lái)分表，是最簡(jiǎn)單的一種方式。這種方式跟分區(qū)難易度差
不多，并且對(duì)程序代碼來(lái)說(shuō)可以做到透明的。如果是用其他分表方式就比分區(qū)麻煩了。
b）分區(qū)實(shí)現(xiàn)是比較簡(jiǎn)單的，建立分區(qū)表，根建平常的表沒(méi)什么區(qū)別，并且對(duì)開(kāi)代碼端來(lái)說(shuō)
是透明的。
mysql 分表和分區(qū)有什么聯(lián)系？
1．都能提高 mysql 的性高，在高并發(fā)狀態(tài)下都有一個(gè)良好的表現(xiàn)。
2．分表和分區(qū)不矛盾，可以相互配合的，對(duì)于那些大訪(fǎng)問(wèn)量，并且表數(shù)據(jù)比較多的表，我
們可以采取分表和分區(qū)結(jié)合的方式，訪(fǎng)問(wèn)量不大，但是表數(shù)據(jù)很多的表，我們可以采取分區(qū)
的方式等。
3.分表技術(shù)是比較麻煩的，需要手動(dòng)去創(chuàng)建子表，app 服務(wù)端讀寫(xiě)時(shí)候需要計(jì)算子表名。采
用 merge 好一些，但也要?jiǎng)?chuàng)建子表和配置子表間的 union 關(guān)系。
4.表分區(qū)相對(duì)于分表，操作方便，不需要?jiǎng)?chuàng)建子表。
謝謝觀看，真心的希望能幫到您！
本文出自 “一盞燭光” 博客，謝絕轉(zhuǎn)載！
更多建議：