MySQL Partition 分区及实战分析

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))

发表评论

电子邮件地址不会被公开。 必填项已用*标注