MySQL Partition 分区及实战分析
MySQL分区
InnoDB 存储引擎使用分区需要开启 InnoDB_file_per_table
mysql> show variables like 'innodb_file_per_table'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_file_per_table | ON | +-----------------------+-------+
查看 MySQL 是否支持分区
mysql> show plugins; +----------------------------+----------+--------------------+---------+---------+ | Name | Status | Type | Library | License | +----------------------------+----------+--------------------+---------+---------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | | mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL | | sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL | | MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL | | PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL | | MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL | ...... | partition | ACTIVE | STORAGE ENGINE | NULL | GPL | | FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL | | ngram | ACTIVE | FTPARSER | NULL | GPL | +----------------------------+----------+--------------------+---------+---------+ 44 rows in set (0.00 sec) mysql> SELECT PLUGIN_NAME as Name, PLUGIN_VERSION as Version, PLUGIN_STATUS as Status FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_TYPE='STORAGE ENGINE'; +--------------------+---------+----------+ | Name | Version | Status | +--------------------+---------+----------+ | binlog | 1.0 | ACTIVE | | MyISAM | 1.0 | ACTIVE | | MEMORY | 1.0 | ACTIVE | | PERFORMANCE_SCHEMA | 0.1 | ACTIVE | | MRG_MYISAM | 1.0 | ACTIVE | | InnoDB | 5.7 | ACTIVE | | CSV | 1.0 | ACTIVE | | ARCHIVE | 3.0 | ACTIVE | | BLACKHOLE | 1.0 | ACTIVE | | partition | 1.0 | ACTIVE | | FEDERATED | 1.0 | DISABLED | +--------------------+---------+----------+ 11 rows in set (0.00 sec)
启用分区，从源码编译 MySQL5.6 必须配置 -DWITH_PARTITION_STORAGE_ENGINE
MySQL 分区类型
分区表类型
优点 缺点 特性
RANGE
适合日期类型、支持符合分区
有限的分区 一般针对某一列
LIST
适合于有固定取值的列，支持符合分区
有限的分区，插入记录在一列的值不在list中、则数据丢失
一般针对某一列
HASH
线性Hash 使得增加、删除和合并分区更快捷
线性 Hash 的数据分布不均匀，而一般 Hash 的数据分布均匀
一般针对某一列
KEY 列可以为字符型等其他非 int类型 效率较之前的低，因为函数为复杂的函数（如：MD5 或 SHA）
一般针对某一列
实战
创建一个 RANGE 分区表，字段id、pdate,插入1亿条数据，id重复且pdate日期不同的数据
CREATE TABLE `myisam_part` ( `id` int(11) DEFAULT NULL, `pdate` date NOT NULL, KEY `idx_pdate` (`pdate`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 PARTITION BY RANGE (YEAR(pdate)) (PARTITION p0 VALUES LESS THAN (2010) ENGINE = MyISAM, PARTITION p1 VALUES LESS THAN (2011) ENGINE = MyISAM, PARTITION p2 VALUES LESS THAN (2012) ENGINE = MyISAM, PARTITION p3 VALUES LESS THAN (2013) ENGINE = MyISAM, PARTITION p4 VALUES LESS THAN (2014) ENGINE = MyISAM, PARTITION p5 VALUES LESS THAN (2015) ENGINE = MyISAM, PARTITION p6 VALUES LESS THAN MAXVALUE ENGINE = MyISAM)
创建存储过程，为数据表插入数据
mysql>\d //
mysql>create procedure load_part_tab()
begin
declare v int default 0;
while v < 10000000 do insert myisam_part (id, pdate) values (v, adddate('2010-01-01', floor(rand()*36520))); set v = v + 1; end while; end // mysql>\d ;
执行存储过程，执行10次，插入1亿条数据
mysql>call load_part_tab();
mysql数据结构 mysql/data/dbname
[root@root par]# ls -lh total 1.8G -rw-r----- 1 mysql mysql 61 Dec 21 11:39 db.opt -rw-r----- 1 mysql mysql 8.4K Dec 22 10:05 myisam_part.frm -rw-r----- 1 mysql mysql 48 Dec 22 10:05 myisam_part.par -rw-r----- 1 mysql mysql 128M Dec 22 13:41 myisam_part#P#p0.MYD -rw-r----- 1 mysql mysql 175M Dec 22 13:41 myisam_part#P#p0.MYI -rw-r----- 1 mysql mysql 128M Dec 22 13:41 myisam_part#P#p1.MYD -rw-r----- 1 mysql mysql 175M Dec 22 13:41 myisam_part#P#p1.MYI -rw-r----- 1 mysql mysql 128M Dec 22 13:41 myisam_part#P#p2.MYD -rw-r----- 1 mysql mysql 176M Dec 22 13:41 myisam_part#P#p2.MYI -rw-r----- 1 mysql mysql 128M Dec 22 13:41 myisam_part#P#p3.MYD -rw-r----- 1 mysql mysql 175M Dec 22 13:41 myisam_part#P#p3.MYI -rw-r----- 1 mysql mysql 128M Dec 22 13:41 myisam_part#P#p4.MYD -rw-r----- 1 mysql mysql 175M Dec 22 13:41 myisam_part#P#p4.MYI -rw-r----- 1 mysql mysql 127M Dec 22 13:41 myisam_part#P#p5.MYD -rw-r----- 1 mysql mysql 175M Dec 22 13:41 myisam_part#P#p5.MYI -rw-r----- 1 mysql mysql 0 Dec 22 11:35 myisam_part#P#p6.MYD -rw-r----- 1 mysql mysql 1.0K Dec 22 11:35 myisam_part#P#p6.MYI
这里可以看出使用 MyISAM 存储引擎，存储1亿条数据产生 1.8G,由于测试日期是随机，这里分配还比较均匀，与实际项目肯定不相符。
查询测试
mysql> select count(*) from myisam_part; +-----------+ | count(*) | +-----------+ | 100000000 | +-----------+ 1 row in set (0.00 sec) mysql> explain select count(*) from myisam_part; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> select count(*) from myisam_part WHERE pdate between '2009-01-01' AND '2009-02-31'; +----------+ | count(*) | +----------+ | 2698820 | +----------+ 1 row in set (1.15 sec) mysql> explain select count(*) from myisam_part WHERE pdate between '2009-01-01' AND '2009-02-31'; +----+-------------+-------------+------------+-------+---------------+-----------+---------+------+---------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------+------------+-------+---------------+-----------+---------+------+---------+----------+--------------------------+ | 1 | SIMPLE | myisam_part | p0 | range | idx_pdate | idx_pdate | 3 | NULL | 2740314 | 100.00 | Using where; Using index | +----+-------------+-------------+------------+-------+---------------+-----------+---------+------+---------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> select count(*) from myisam_part WHERE pdate between '2009-01-01' AND '2009-12-31'; +----------+ | count(*) | +----------+ | 16668252 | +----------+ 1 row in set (5.88 sec) mysql> explain select count(*) from myisam_part WHERE pdate between '2009-01-01' AND '2009-12-31'; +----+-------------+-------------+------------+-------+---------------+-----------+---------+------+----------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------+------------+-------+---------------+-----------+---------+------+----------+----------+--------------------------+ | 1 | SIMPLE | myisam_part | p0 | index | idx_pdate | idx_pdate | 3 | NULL | 16668252 | 100.00 | Using where; Using index | +----+-------------+-------------+------------+-------+---------------+-----------+---------+------+----------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> select count(*) from myisam_part WHERE pdate between '2009-01-01' AND '2010-01-31'; +----------+ | count(*) | +----------+ | 18084820 | +----------+ 1 row in set (6.92 sec) mysql> explain select count(*) from myisam_part WHERE pdate between '2009-01-01' AND '2010-01-31'; +----+-------------+-------------+------------+-------+---------------+-----------+---------+------+----------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------+------------+-------+---------------+-----------+---------+------+----------+----------+--------------------------+ | 1 | SIMPLE | myisam_part | p0,p1 | range | idx_pdate | idx_pdate | 3 | NULL | 18196189 | 100.00 | Using where; Using index | +----+-------------+-------------+------------+-------+---------------+-----------+---------+------+----------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec)
测试比较 between 和 >=、<=
mysql> select count(*) from myisam_part WHERE pdate >= '2009-01-01' AND pdate <='2010-01-31'; +----------+ | count(*) | +----------+ | 18084820 | +----------+ 1 row in set (7.11 sec)
对比以上查询，between 速度较快，具体分析请查看《16- 剖析单条查询 show profile》
添加分区
>ALTER TABLE myisam_part ADD PARTITION (PARTITION pn VALUES LESS THAN (2016));
创建子分区
mysql> create table myisam_part_sub(id int , pdate date not null)ENGINE=MYISAM
-> PARTITION BY RANGE(year(pdate))
-> SUBPARTITION BY HASH(TO_DAYS(pdate))(
-> PARTITION p0 VALUES LESS THAN (2010) (SUBPARTITION s0, SUBPARTITION s1),
-> PARTITION p1 VALUES LESS THAN (2011) (SUBPARTITION s2, SUBPARTITION s3),
-> PARTITION p2 VALUES LESS THAN (2012) (SUBPARTITION s4, SUBPARTITION s5),
-> PARTITION p3 VALUES LESS THAN (2013) (SUBPARTITION s6, SUBPARTITION s7),
-> PARTITION p4 VALUES LESS THAN (2014) (SUBPARTITION s8, SUBPARTITION s9),
-> PARTITION p5 VALUES LESS THAN (2015) (SUBPARTITION s10, SUBPARTITION s11),
-> PARTITION p6 VALUES LESS THAN (2016) (SUBPARTITION s12, SUBPARTITION s13)
-> );
将 mysql_part 表数据导入到mysql_part_sub 表中
mysql> insert into myisam_part_sub select * from myisam_part;
在没有创建索引的情况下查询
mysql> show index from myisam_part_sub; Empty set (0.00 sec) mysql> select count(*) from myisam_part_sub; +-----------+ | count(*) | +-----------+ | 100000000 | +-----------+ 1 row in set (0.00 sec) mysql> select count(*) from myisam_part_sub WHERE pdate between '2009-01-01' AND '2009-02-31'; +----------+ | count(*) | +----------+ | 2698820 | +----------+ 1 row in set (3.25 sec) mysql> explain select count(*) from myisam_part_sub WHERE pdate between '2009-01-01' AND '2009-02-31'; +----+-------------+-----------------+-------------+------+---------------+------+---------+------+----------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------+-------------+------+---------------+------+---------+------+----------+----------+-------------+ | 1 | SIMPLE | myisam_part_sub | p0_s0,p0_s1 | ALL | NULL | NULL | NULL | NULL | 16668252 | 11.11 | Using where | +----+-------------+-----------------+-------------+------+---------------+------+---------+------+----------+----------+-------------+ 1 row in set, 1 warning (0.01 sec) mysql> select count(*) from myisam_part_sub WHERE pdate between '2009-01-01' AND '2009-12-31'; +----------+ | count(*) | +----------+ | 16668252 | +----------+ 1 row in set (3.51 sec) # 没有索引 子分区要比没有使用子分区快1 mysql> explain select count(*) from myisam_part_sub WHERE pdate between '2009-01-01' AND '2009-12-31'; +----+-------------+-----------------+-------------+------+---------------+------+---------+------+----------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------+-------------+------+---------------+------+---------+------+----------+----------+-------------+ | 1 | SIMPLE | myisam_part_sub | p0_s0,p0_s1 | ALL | NULL | NULL | NULL | NULL | 16668252 | 11.11 | Using where | +----+-------------+-----------------+-------------+------+---------------+------+---------+------+----------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> select count(*) from myisam_part_sub WHERE pdate between '2009-01-01' AND '2010-01-31'; +----------+ | count(*) | +----------+ | 18084820 | +----------+ 1 row in set (6.70 sec) # 跨分区速度也快 mysql> explain select count(*) from myisam_part_sub WHERE pdate between '2009-01-01' AND '2010-01-31'; +----+-------------+-----------------+-------------------------+------+---------------+------+---------+------+----------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------+-------------------------+------+---------------+------+---------+------+----------+----------+-------------+ | 1 | SIMPLE | myisam_part_sub | p0_s0,p0_s1,p1_s2,p1_s3 | ALL | NULL | NULL | NULL | NULL | 33330402 | 11.11 | Using where | +----+-------------+-----------------+-------------------------+------+---------------+------+---------+------+----------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
创建 pdate 索引查询
mysql> create index idx_pdate on myisam_part_sub(pdate); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show index from myisam_part_sub; +-----------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-----------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | myisam_part_sub | 1 | idx_pdate | 1 | pdate | A | NULL | NULL | NULL | | BTREE | | | +-----------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 1 row in set (0.00 sec) mysql> select count(*) from myisam_part_sub WHERE pdate between '2009-01-01' AND '2009-02-31'; +----------+ | count(*) | +----------+ | 2698820 | +----------+ 1 row in set (1.00 sec) # 比比较索引快 mysql> explain select count(*) from myisam_part_sub WHERE pdate between '2009-01-01' AND '2009-02-31'; +----+-------------+-----------------+-------------+-------+---------------+-----------+---------+------+---------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------+-------------+-------+---------------+-----------+---------+------+---------+----------+--------------------------+ | 1 | SIMPLE | myisam_part_sub | p0_s0,p0_s1 | range | idx_pdate | idx_pdate | 3 | NULL | 2559881 | 100.00 | Using where; Using index | +----+-------------+-----------------+-------------+-------+---------------+-----------+---------+------+---------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> select count(*) from myisam_part_sub WHERE pdate between '2009-01-01' AND '2009-12-31'; +----------+ | count(*) | +----------+ | 16668252 | +----------+ 1 row in set (5.76 sec) mysql> explain select count(*) from myisam_part_sub WHERE pdate between '2009-01-01' AND '2009-12-31'; +----+-------------+-----------------+-------------+-------+---------------+-----------+---------+------+----------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------+-------------+-------+---------------+-----------+---------+------+----------+----------+--------------------------+ | 1 | SIMPLE | myisam_part_sub | p0_s0,p0_s1 | index | idx_pdate | idx_pdate | 3 | NULL | 16668252 | 100.00 | Using where; Using index | +----+-------------+-----------------+-------------+-------+---------------+-----------+---------+------+----------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> select count(*) from myisam_part_sub WHERE pdate between '2009-01-01' AND '2010-01-31'; +----------+ | count(*) | +----------+ | 18084820 | +----------+ 1 row in set (6.59 sec) mysql> explain select count(*) from myisam_part_sub WHERE pdate between '2009-01-01' AND '2010-01-31'; +----+-------------+-----------------+-------------------------+-------+---------------+-----------+---------+------+----------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------+-------------------------+-------+---------------+-----------+---------+------+----------+----------+--------------------------+ | 1 | SIMPLE | myisam_part_sub | p0_s0,p0_s1,p1_s2,p1_s3 | range | idx_pdate | idx_pdate | 3 | NULL | 18011407 | 100.00 | Using where; Using index | +----+-------------+-----------------+-------------------------+-------+---------------+-----------+---------+------+----------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec)
总结：
查询近几个月数据（子分区有索引(1.00 sec), 子分区无索引(3.25 sec) , 没有使用子分区有索引(1.15 sec)）
查询整个一年数据（子分区有索引(5.76 sec), 子分区无索引(3.51 sec) , 没有使用子分区有索引(5.88 sec)）
查询夸年数据 （子分区有索引(6.59 sec), 子分区无索引(6.70 sec) , 没有使用子分区有索引(6.92 sec)）