深入理解MySQL分区表

标签: 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,严重影响服务器的性能。

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

版权声明:本文为u012285489原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/u012285489/article/details/78654288