MySQL Key分割槽表建立方法介紹

按照KEY進行分割槽類似於按照HASH分割槽，除了HASH分割槽使用的使用者定義的表示式，而KEY分割槽的 雜湊函式是由MySQL 伺服器提供。MySQL 簇（Cluster）使用函式MD5()來實現KEY分割槽；
對於使用其他儲存引擎的表，伺服器使用其自己內部的 雜湊函式，這些函式是基於與PASSWORD()一樣的運演算法則。
Key分割槽與Hash分割槽很相似，只是Hash函式不同，定義時把Hash關鍵字替換成Key即可，同樣Key分割槽也有對應與線性Hash的線性Key分割槽方法。

語法為PARTITION BY LINEAR KEY(列名)

建立key分割槽表舉例如下:

mysql> CREATE TABLE `dsf_data` (
    ->   `id` bigint(20) NOT NULL AUTO_INCREMENT,
    ->   `SH` varchar(32) DEFAULT NULL COMMENT '稅號',
    ->   `KPJH` varchar(32) DEFAULT NULL COMMENT '開票機號',
    ->   `ZFJH` varchar(32) DEFAULT NULL COMMENT '主分機號',
    ->   `MONTH` varchar(10) DEFAULT NULL,
    ->   `STATUS` varchar(255) DEFAULT NULL COMMENT '解析狀態標識',
    ->   `CREATE_TIME` datetime DEFAULT NULL COMMENT '插入時間',
    ->   `UPDATE_TIME` datetime DEFAULT NULL COMMENT '更新時間',
    ->   `FP_DATA` mediumtext COMMENT '發票資料',
    ->   PRIMARY KEY (`id`,`SH`),
    ->   KEY `index_sh` (`SH`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=1173560 DEFAULT CHARSET=utf8 PARTITION BY LINEAR KEY (SH) PARTITIONS 8;
Query OK, 0 rows affected (0.11 sec)

備註：如果分割槽欄位中有主鍵或者唯一索引的列，那麼所有主鍵列和唯一索引列都必須包含進來，因此上一步必須有兩個主鍵PRIMARY KEY (`id`,`SH`)存在。

插入資料：
mysql> insert into dsf_data select * from test.fp_data;
Query OK, 202632 rows affected, 1 warning (18.96 sec)
Records: 202632  Duplicates: 0  Warnings: 1


mysql> explain partitions select sh from dsf_data;  --全表掃描共訪問了8個分割槽(p0--p7)
+----+-------------+----------+-------------------------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table    | partitions              | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+----+-------------+----------+-------------------------+-------+---------------+----------+---------+------+------+-------------+
|  1 | SIMPLE      | dsf_data | p0,p1,p2,p3,p4,p5,p6,p7 | index | NULL          | index_sh | 98      | NULL |    8 | Using index |
+----+-------------+----------+-------------------------+-------+---------------+----------+---------+------+------+-------------+
1 row in set (0.00 sec)



mysql> explain partitions select sh from dsf_data where sh='130202568907641';  --值被隨機分到了p0分割槽
+----+-------------+----------+------------+------+---------------+----------+---------+-------+------+--------------------------+
| id | select_type | table    | partitions | type | possible_keys | key      | key_len | ref   | rows | Extra                    |
+----+-------------+----------+------------+------+---------------+----------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | dsf_data | p0         | ref  | index_sh      | index_sh | 98      | const |    1 | Using where; Using index |
+----+-------------+----------+------------+------+---------------+----------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)


mysql> explain partitions select sh from dsf_data where sh='440300683797687';  --值被隨機分到了p4分割槽
+----+-------------+----------+------------+------+---------------+----------+---------+-------+------+--------------------------+
| id | select_type | table    | partitions | type | possible_keys | key      | key_len | ref   | rows | Extra                    |
+----+-------------+----------+------------+------+---------------+----------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | dsf_data | p4         | ref  | index_sh      | index_sh | 98      | const |    1 | Using where; Using index |
+----+-------------+----------+------------+------+---------------+----------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)




mysql> explain partitions select sh from dsf_data where sh='91500107784224861G';  --sh的值被隨機分到了p6分割槽
+----+-------------+----------+------------+------+---------------+----------+---------+-------+------+--------------------------+
| id | select_type | table    | partitions | type | possible_keys | key      | key_len | ref   | rows | Extra                    |
+----+-------------+----------+------------+------+---------------+----------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | dsf_data | p6         | ref  | index_sh      | index_sh | 98      | const |  452 | Using where; Using index |
+----+-------------+----------+------------+------+---------------+----------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

值被隨機分到各個分割槽，說明分割槽表建立成功。