深入理解MySQL分区表

1. 分区表的原理

       MySQL从5.1开始引入了分区表的特性，对于用户来说，分区表是一个独立的逻辑表，但底层却是由多个物理子表组成。在物理层面，数据库中的数据是以文件形式存放在磁盘上，MySQL中每一个数据库都会在定义好（或者默认）的数据目录下存在一个以数据库名字命名的文件夹（可以使用：show variables like "%datadir%" 来查看数据存储目录 ），里面存放着数据文件以及与表相关的元文件（.frm）。不同的MySQL引擎存储的数据文件类型不同，如MyISAM用“.MYD” 作为扩展名，Innodb用“.ibd”作为扩展名。对于非分区表，一个表用一个数据文件存储，而对于分区表，每一个分区表都有一个使用#分隔命名的表文件。

2. 分区表的类型

         MySQL分区表有两种分区方式：水平分区 和 垂直分区

       水平分区：

       水平分区也叫范围分区，每个分区存储落在某个范围的记录。假如有1000W条数据，按日期分成十份，2010年之前的数据放到第一个分区，2011年之前的数据放到第二个分区，依此类推。也就是把表分成了十份。

        垂直分区：

        假设在设计用户信息表时，把所有用户信息都放到了一张表里面去，这样这张表里面就会有比较大的字段，如个人说明，而这个字段，可能不会有很多人去看，通常是有人要看的时候，才去查找。分表的时候，就可以把这样的大字段，分出来。也就是改变了表结构，把一张表拆分成了两张。

3. 创建分区表

3.1 新表创建分区       

       MySQL在创建表时使用PARTTITION BY子句实现分区表，举例：     

CREATE TABLE t_part ( id int not NULL, name varchar(30) not NULL, add_time date not NULL) engine=myisam   PARTITION BY RANGE (year(add_time)) (PARTITION p0 VALUES LESS THAN (2010),  PARTITION p1 VALUES LESS THAN (2011) , PARTITION p2 VALUES LESS THAN (2012) ,  PARTITION p3 VALUES LESS THAN (2013) , PARTITION p4 VALUES LESS THAN (2014) ,  PARTITION p5 VALUES LESS THAN (2015) , PARTITION p6 VALUES LESS THAN (2016) ,  PARTITION p7 VALUES LESS THAN (2017) , PARTITION p8 VALUES LESS THAN (2018) ,  PARTITION p9 VALUES LESS THAN (2019) ,  PARTITION p11 VALUES LESS THAN MAXVALUE ); 

         说明：PARTTITION BY分区子句可以使用各种函数，但有一个要求，表达式返回的值必须是一个确定的整数。本例中使用函数YEAR()，根据时间间隔进行分区，这也是一种很常见的分区方式。MySQL还支持键值、哈希和列表分区。

      执行上面的建表语句后，去到MySQL的数据存储目录，可以看到如下数据文件：

      

3.2 已有表创建分区

alter table p_no_part partition by RANGE (year(add_time))(PARTITION p0 VALUES LESS THAN (2010),  PARTITION p1 VALUES LESS THAN (2011) , PARTITION p2 VALUES LESS THAN (2012) ,  PARTITION p3 VALUES LESS THAN (2013) , PARTITION p4 VALUES LESS THAN (2014) ,  PARTITION p5 VALUES LESS THAN (2015) , PARTITION p6 VALUES LESS THAN (2016) ,  PARTITION p7 VALUES LESS THAN (2017) , PARTITION p8 VALUES LESS THAN (2018) ,  PARTITION p9 VALUES LESS THAN (2019) ,  PARTITION p11 VALUES LESS THAN MAXVALUE );

Query OK, 10000000 rows affected (15.20 sec)
Records: 10000000  Duplicates: 0  Warnings: 0

4. 性能测试

4.1 数据准备        

       创建完分区表后，为了更直观的看到分区表的优势，可以做一些测试。首先，写一个脚本，向分区表（t_part）里面插入1000万的数据，脚本如下：

       

<?php$conn = mysqli_connect($servername, $username, $password,$dbname);if (!$conn) {         die("Connection failed: " . mysqli_connect_error()); }for($i = 0; $i < 10000000 ; $i++){     $sql = "insert into t_part values (".$i.",'test',date('".(rand(2010,2020)."-01-01")."'))";     if ($conn->query($sql) != TRUE) {         echo "Error: " . $sql . "<br>" . $conn->error;     }}   $conn->close();

       再建一个与非分区表，t_no_part，复制t_part里面的数据：

insert into t_no_part select * from t_part;

4.2 SQL性能测试

4.2.1 SQL耗时对比

select count(*) from t_part where add_time > date '2010-01-01' and add_time < date '2011-01-01';

+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.32 sec)

select count(*) from t_no_part where add_time > date '2010-01-01' and add_time < date '2011-01-01';

+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (3.06 sec)

可以看到，同一查询，分区表比未分区表查询时间少了近90%。

4.2.2 EXPLAIN分析

使用EXPLAIN对以上两个查询语句进行分析：

explain select count(*) from t_part where add_time > date '2010-01-01' and add_time < date '2011-01-01' \G;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_part
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 992155
        Extra: Using where
1 row in set (0.00 sec)

explain select count(*) from t_no_part where add_time > date '2010-01-01' and add_time < date '2011-01-01' \G;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_no_part
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10000000
        Extra: Using where
1 row in set (0.00 sec)

通过EXPIAIN工具分析的结果可以得知，同一查询，分区表比非分区表查询rows要少很多，这也是我们使用分区表原因之一。

4.2.3 索引测试

在分区表和非分区表上分别创建索引：

create index idx_time on t_part (add_time);

create index idx_time on t_no_part (add_time);

创建索引之后，再次进行SQL查询测试，

select count(*) from t_part where add_time > date '2010-01-01' and add_time < date '2012-12-01';

+----------+
| count(*) |
+----------+
|  1983485 |
+----------+
1 row in set (1.19 sec)

select count(*) from t_no_part where add_time > date '2010-01-01' and add_time < date '2012-12-01';

+----------+
| count(*) |
+----------+
|  1983485 |
+----------+
1 row in set (1.28 sec)

可以看到，创建索引之后，分区表和未分区表的查询时间差不多，分区表略优于非分区表。

5.总结

        在数据量超大的时候，B-Tree索引就无法起作用了，除非是索引覆盖查询，否则数据库服务器需要根据索引扫描的结果回表，查询所有符合条件的记录，如果数据量巨大，这将产生大量随机I/O，严重影响服务器的性能。

       当数据量当数据量非常大的时候，不能再每次查询的时候都扫描全表，由于索引也无法使用，就可以考虑是否分区表的方案。理解分区时还可以将其当做索引的最初形态，以代价非常小的方式定位到需要的数据在那一片区域。在这片区域中，你可以做顺序扫描，可以建索引，还可以将数据都缓存到内存 等等。因为分区无需额外的数据结构记录每个分区有哪些数据——分区不需要精准定位每条数据的位置，也就无需额外的数据结构，所以代价非常低。