一篇文章学会Mysql分区表的管理与维护 « 邵珠庆の博客

邵珠庆の博客 生命只有一次,你可以用它来做些更多伟大的事情–Make the world a little better and easier

10十/160

一篇文章学会Mysql分区表的管理与维护

定义: 

       表的分区指根据可以设置为任意大小的规则,跨文件系统分配单个表的多个部分。实际上,表的不同部分在不同的位置被存储为单独的表。用户所选择的、实现数据分割的规则被称为分区函数,这在MySQL中它可以是模数,或者是简单的匹配一个连续的数值区间或数值列表,或者是一个内部HASH函数,或一个线性HASH函数。

使用场景:

     1.某张表的数据量非常大,通过索引已经不能很好的解决查询性能的问题

     2.表的数据可以按照某种条件进行分类,以致于在查询的时候性能得到很大的提升         

优点:

     1)、对于那些已经失去保存意义的数据,通常可以通过删除与那些数据有关的分区,很容易地删除那些数据。相反地,在某些情况下,添加新数据的过程又可以通过为那些新数据专门增加一个新的分区,来很方便地实现。

    2)、一些查询可以得到极大的优化,这主要是借助于满足一个给定WHERE语句的数据可以只保存在一个或多个分区内,这样在查找时就不用查找其他剩余的分区。因为分区可以在创建了分区表后进行修改,所以在第一次配置分区方案时还不曾这么做时,可以重新组织数据,来提高那些常用查询的效率。

    3)、涉及到例如SUM()和COUNT()这样聚合函数的查询,可以很容易地进行并行处理。这意味着查询可以在每个分区上同时进行,最终结果只需通过总计所有分区得到的结果。 

    4)、通过跨多个磁盘来分散数据查询,来获得更大的查询吞吐量。


     分类:


      

 1.检查你的Mysql是否支持分区

        mysql> SHOW VARIABLES LIKE '%partition%';

       若结果如下,表示你的Mysql支持表分区:

        +-----------------------+-------+

       | Variable_name         | Value |
       +-----------------------+-------+
       | have_partition_engine | YES   |
       +-----------------------+-------+
       1 row in set (0.00 sec)

               RANGE分区表创建方式:

  1. DROP TABLE IF EXISTS `my_orders`;  
  2. CREATE TABLE `my_orders` (  
  3.   `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '表主键',  
  4.   `pid` int(10) unsigned NOT NULL COMMENT '产品ID',  
  5.   `price` decimal(15,2) NOT NULL COMMENT '单价',  
  6.   `num` int(11) NOT NULL COMMENT '购买数量',  
  7.   `uid` int(10) unsigned NOT NULL COMMENT '客户ID',  
  8.   `atime` datetime NOT NULL COMMENT '下单时间',  
  9.   `utime` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '修改时间',  
  10.   `isdel` tinyint(4) NOT NULL DEFAULT '0' COMMENT '软删除标识',  
  11.   PRIMARY KEY (`id`,`atime`)  
  12. ) ENGINE=InnoDB DEFAULT CHARSET=utf8  
  13.   
  14. /*********分区信息**************/  
  15. PARTITION BY RANGE (YEAR(atime))  
  16. (  
  17.    PARTITION p0 VALUES LESS THAN (2016),  
  18.    PARTITION p1 VALUES LESS THAN (2017),  
  19.    PARTITION p2 VALUES LESS THAN MAXVALUE  
  20. );  

        以上是一个简单的订单表,分区字段是atime,根据RANGE分区,这样当你向该表中插入数据的时候,Mysql会根据YEAR(atime)的值进行分区存储。

        检查分区是否创建成功,执行查询语句:

         EXPLAIN PARTITIONS SELECT * FROM `my_orders`

         若成功,结果如下:

        

        性能分析:

        1).创建同样表结构,但没有进行分区的表     

  1. DROP TABLE IF EXISTS `my_order`;  
  2. CREATE TABLE `my_order` (  
  3.   `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '表主键',  
  4.   `pid` int(10) unsigned NOT NULL COMMENT '产品ID',  
  5.   `price` decimal(15,2) NOT NULL COMMENT '单价',  
  6.   `num` int(11) NOT NULL COMMENT '购买数量',  
  7.   `uid` int(10) unsigned NOT NULL COMMENT '客户ID',  
  8.   `atime` datetime NOT NULL COMMENT '下单时间',  
  9.   `utime` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '修改时间',  
  10.   `isdel` tinyint(4) NOT NULL DEFAULT '0' COMMENT '软删除标识',  
  11.   PRIMARY KEY (`id`,`atime`)  
  12. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;  

 

        2).向两张表中插入相同的数据

  1. /**************************向分区表插入数据****************************/  
  2. INSERT INTO my_orders(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,CURRENT_TIMESTAMP());  
  3. INSERT INTO my_orders(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,'2016-05-01 00:00:00');  
  4. INSERT INTO my_orders(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,'2017-05-01 00:00:00');  
  5. INSERT INTO my_orders(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,'2018-05-01 00:00:00');  
  6. INSERT INTO my_orders(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2015-05-01 00:00:00');  
  7. INSERT INTO my_orders(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2016-05-01 00:00:00');  
  8. INSERT INTO my_orders(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2017-05-01 00:00:00');  
  9. INSERT INTO my_orders(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2018-05-01 00:00:00');  
  10.   
  11. /**************************向未分区表插入数据****************************/  
  12. INSERT INTO my_order(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,CURRENT_TIMESTAMP());  
  13. INSERT INTO my_order(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,'2016-05-01 00:00:00');  
  14. INSERT INTO my_order(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,'2017-05-01 00:00:00');  
  15. INSERT INTO my_order(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,'2018-05-01 00:00:00');  
  16. INSERT INTO my_order(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2015-05-01 00:00:00');  
  17. INSERT INTO my_order(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2016-05-01 00:00:00');  
  18. INSERT INTO my_order(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2017-05-01 00:00:00');  
  19. INSERT INTO my_order(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2018-05-01 00:00:00');  

        3).主从复制,大约20万条左右(主从复制的数据和真实环境有差距,但是能体现出表分区查询的性能优劣)

 
  1. /**********************************主从复制大量数据******************************/  
  2. INSERT INTO `my_orders`(`pid`,`price`,`num`,`uid`,`atime`) SELECT `pid`,`price`,`num`,`uid`,`atime` FROM `my_orders`;  
  3. INSERT INTO `my_order`(`pid`,`price`,`num`,`uid`,`atime`) SELECT `pid`,`price`,`num`,`uid`,`atime` FROM `my_order`;   

       4).查询测试 

  1. /***************************查询性能分析**************************************/  
  2. SELECT * FROM `my_orders` WHERE `uid`=89757 AND `atime`< CURRENT_TIMESTAMP();  
  3. /****用时0.084s****/  
  4.   
  5. SELECT * FROM `my_order` WHERE `uid`=89757 AND `atime`< CURRENT_TIMESTAMP();  
  6. /****用时0.284s****/  

      通过以上查询可以明显看出进行表分区的查询性能更好,查询所花费的时间更短。

      分析查询过程:

      EXPLAIN PARTITIONS SELECT * FROM `my_orders` WHERE `uid`=89757 AND `atime`< CURRENT_TIMESTAMP();

            

 

      EXPLAIN PARTITIONS SELECT * FROM `my_order` WHERE `uid`=89757 AND `atime`< CURRENT_TIMESTAMP();

              

       

       通过以上结果可以看出,my_orders表查询直接经过p0分区,只扫描了49386行,而my_order表没有进行分区,扫描了196983行,这也是性能得到提升的关键所在。

       当然,表的分区并不是分的越多越好,当表的分区太多时找分区又是一个性能的瓶颈了,建议在200个分区以内。

      LIST分区表创建方式:

 
  1. /*****************创建分区表*********************/  
  2. CREATE TABLE `products` (  
  3. `id`  bigint UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '表主键' ,  
  4. `name`  varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '产品名称' ,  
  5. `metrial`  tinyint UNSIGNED NOT NULL COMMENT '材质' ,  
  6. `weight`  double UNSIGNED NOT NULL DEFAULT 0 COMMENT '重量' ,  
  7. `vol`  double UNSIGNED NOT NULL DEFAULT 0 COMMENT '容积' ,  
  8. `c_id`  tinyint UNSIGNED NOT NULL COMMENT '供货公司ID' ,  
  9. PRIMARY KEY (`id`,`c_id`)  
  10. )ENGINE=InnoDB DEFAULT CHARSET=utf8  
  11.   
  12. /*********分区信息**************/  
  13. PARTITION BY LIST(c_id)  
  14. (  
  15.     PARTITION pA VALUES IN (1,3,11,13),  
  16.     PARTITION pB VALUES IN (2,4,12,14),  
  17.     PARTITION pC VALUES IN (5,7,15,17),  
  18.     PARTITION pD VALUES IN (6,8,16,18),  
  19.     PARTITION pE VALUES IN (9,10,19,20)  
  20. );  

       可以看出,LIST分区和RANGE分区很类似,这里就不做性能分析了,和RANGE很类似。

  

       HASH分区表的创建方式:

 
  1. /*****************分区表*****************/  
  2. CREATE TABLE `msgs` (  
  3.   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '表主键',  
  4.   `sender` int(10) unsigned NOT NULL COMMENT '发送者ID',  
  5.   `reciver` int(10) unsigned NOT NULL COMMENT '接收者ID',  
  6.   `msg_type` tinyint(3) unsigned NOT NULL COMMENT '消息类型',  
  7.   `msg` varchar(225) NOT NULL COMMENT '消息内容',  
  8.   `atime` int(10) unsigned NOT NULL COMMENT '发送时间',  
  9.   `sub_id` tinyint(3) unsigned NOT NULL COMMENT '部门ID',  
  10.   PRIMARY KEY (`id`,`sub_id`)  
  11. ) ENGINE=InnoDB DEFAULT CHARSET=utf8  
  12. /*********分区信息**************/  
  13. PARTITION BY HASH(sub_id)  
  14. PARTITIONS 10;  

          以上语句代表,msgs表按照sub_id进行HASH分区,一共分了十个区。

 

       Key分区和HASH分区很类似,不再介绍,若想了解可以参考Mysql官方文档进行详细了解。

 

       子分区的创建方式:

 
  1. CREATE TABLE `msgss` (  
  2.   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '表主键',  
  3.   `sender` int(10) unsigned NOT NULL COMMENT '发送者ID',  
  4.   `reciver` int(10) unsigned NOT NULL COMMENT '接收者ID',  
  5.   `msg_type` tinyint(3) unsigned NOT NULL COMMENT '消息类型',  
  6.   `msg` varchar(225) NOT NULL COMMENT '消息内容',  
  7.   `atime` int(10) unsigned NOT NULL COMMENT '发送时间',  
  8.   `sub_id` tinyint(3) unsigned NOT NULL COMMENT '部门ID',  
  9.   PRIMARY KEY (`id`,`atime`,`sub_id`)  
  10. ) ENGINE=InnoDB DEFAULT CHARSET=utf8  
  11. /*********分区信息**************/  
  12. PARTITION BY RANGE (atime) SUBPARTITION BY HASH (sub_id)   
  13. (  
  14.         PARTITION t0 VALUES LESS THAN(1451577600)  
  15.         (  
  16.             SUBPARTITION s0,  
  17.             SUBPARTITION s1,  
  18.             SUBPARTITION s2,  
  19.             SUBPARTITION s3,  
  20.             SUBPARTITION s4,  
  21.             SUBPARTITION s5  
  22.         ),  
  23.         PARTITION t1 VALUES LESS THAN(1483200000)  
  24.         (  
  25.             SUBPARTITION s6,  
  26.             SUBPARTITION s7,  
  27.             SUBPARTITION s8,  
  28.             SUBPARTITION s9,  
  29.             SUBPARTITION s10,  
  30.             SUBPARTITION s11  
  31.         ),  
  32.         PARTITION t2 VALUES LESS THAN MAXVALUE  
  33.         (  
  34.             SUBPARTITION s12,  
  35.             SUBPARTITION s13,  
  36.             SUBPARTITION s14,  
  37.             SUBPARTITION s15,  
  38.             SUBPARTITION s16,  
  39.             SUBPARTITION s17  
  40.         )  
  41. );  

         检查子分区是否创建成功:

 

        EXPLAIN PARTITIONS SELECT * FROM msgss;

        结果如下图:

          

        

前面已经提过,Mysql支持4种表的分区,即RANGE与LIST、HASH与KEY,其中RANGE和LIST类似,按一种区间进行分区,HASH与KEY类似,是按照某种算法对字段进行分区。

 

       RANGE与LIST分区管理

       案例:有一个聊天记录表,用户几千左右,已经对表按照用户进行一定粒度的水平分割,现仍然有部分表存储的记录比较多,于是按照下列方式有对表进行了分区,分区的好处是,可以动态改变分区,删除分区后,数据也一同被删除,如聊天记录只保存两年,那么你就可以按照时间进行分区,定期删除两年前的分区,动态创建新的的分区就能做到很好的数据维护

   

       分区表创建的语句如下:

 
  1. DROP TABLE IF EXISTS `msgss`;  
  2. CREATE TABLE `msgss` (  
  3.   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '表主键',  
  4.   `sender` int(10) unsigned NOT NULL COMMENT '发送者ID',  
  5.   `reciver` int(10) unsigned NOT NULL COMMENT '接收者ID',  
  6.   `msg_type` tinyint(3) unsigned NOT NULL COMMENT '消息类型',  
  7.   `msg` varchar(225) NOT NULL COMMENT '消息内容',  
  8.   `atime` int(10) unsigned NOT NULL COMMENT '发送时间',  
  9.   `sub_id` tinyint(3) unsigned NOT NULL COMMENT '部门ID',  
  10.   PRIMARY KEY (`id`,`atime`,`sub_id`)  
  11. ) ENGINE=InnoDB DEFAULT CHARSET=utf8  
  12. /*********分区信息**************/  
  13. PARTITION BY RANGE (atime) SUBPARTITION BY HASH (sub_id)   
  14. (  
  15.         PARTITION t0 VALUES LESS THAN(1451577600)  
  16.         (  
  17.             SUBPARTITION s0,  
  18.             SUBPARTITION s1,  
  19.             SUBPARTITION s2,  
  20.             SUBPARTITION s3,  
  21.             SUBPARTITION s4,  
  22.             SUBPARTITION s5  
  23.         ),  
  24.         PARTITION t1 VALUES LESS THAN(1483200000)  
  25.         (  
  26.             SUBPARTITION s6,  
  27.             SUBPARTITION s7,  
  28.             SUBPARTITION s8,  
  29.             SUBPARTITION s9,  
  30.             SUBPARTITION s10,  
  31.             SUBPARTITION s11  
  32.         ),  
  33.         PARTITION t2 VALUES LESS THAN MAXVALUE  
  34.         (  
  35.             SUBPARTITION s12,  
  36.             SUBPARTITION s13,  
  37.             SUBPARTITION s14,  
  38.             SUBPARTITION s15,  
  39.             SUBPARTITION s16,  
  40.             SUBPARTITION s17  
  41.         )  
  42. );  

        上述语句创建了三个按照RANGE划分的主分区,每个主分区下面有六个按照HASH划分的子分区。

 

        插入测试数据:

 
  1. INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH',UNIX_TIMESTAMP(NOW()),1);  
  2. INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 2',UNIX_TIMESTAMP(NOW()),2);  
  3. INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 3',UNIX_TIMESTAMP(NOW()),3);  
  4. INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 10',UNIX_TIMESTAMP(NOW()),10);  
  5. INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 7',UNIX_TIMESTAMP(NOW()),7);  
  6. INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 5',UNIX_TIMESTAMP(NOW()),5);  
  7.   
  8. INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH',1451577607,1);  
  9. INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 2',1451577609,2);  
  10. INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 3',1451577623,3);  
  11. INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 10',1451577654,10);  
  12. INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 7',1451577687,7);  
  13. INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 5',1451577699,5);  
  14.   
  15. INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH',1514736056,1);  
  16. INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 2',1514736066,2);  
  17. INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 3',1514736076,3);  
  18. INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 10',1514736086,10);  
  19. INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 7',1514736089,7);  
  20. INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 5',1514736098,5);  

                  进行分区分析:

 

        EXPLAIN PARTITIONS SELECT * FROM msgss;

        可以检测到分区信息如下:

          

 

         检测分区数据分布:

 
  1. EXPLAIN PARTITIONS SELECT * FROM msgss WHERE `atime`<1451577600;  
  2.   
  3. EXPLAIN PARTITIONS SELECT * FROM msgss WHERE `atime`>1451577600 AND `atime`<1483200000;  
  4.   
  5. EXPLAIN PARTITIONS SELECT * FROM msgss WHERE `atime`>1483200000 AND `atime`<1514736000;  
  6.   
  7. EXPLAIN PARTITIONS SELECT * FROM msgss WHERE `atime`>1514736000;  

        结果:第一条语句只扫描了t0的所有子分区,第二条语句只扫描了t1的所有子分区,第三四条分别只扫描了t2的所有子分区,证明表的分区和数据分布成功。

         需求:目前已经是2017年,需要将2015年所有的聊天记录删除,但是保留2016年的聊天记录,并且2017年的数据也能正常按照分区进行存储。

        实现以上需求,需要两步,第一步删除t0分区,第二步按照新规则重建分区。

        删除分区语句:

        ALTER TABLE `msgss` DROP PARTITION t0;

        重建分区语句:

 
  1. ALTER TABLE `msgss` PARTITION BY RANGE (atime) SUBPARTITION BY HASH (sub_id)   
  2. (  
  3.         PARTITION t0 VALUES LESS THAN(1483200000)  
  4.         (  
  5.             SUBPARTITION s0,  
  6.             SUBPARTITION s1,  
  7.             SUBPARTITION s2,  
  8.             SUBPARTITION s3,  
  9.             SUBPARTITION s4,  
  10.             SUBPARTITION s5  
  11.         ),  
  12.         PARTITION t1 VALUES LESS THAN(1514736000)  
  13.         (  
  14.             SUBPARTITION s6,  
  15.             SUBPARTITION s7,  
  16.             SUBPARTITION s8,  
  17.             SUBPARTITION s9,  
  18.             SUBPARTITION s10,  
  19.             SUBPARTITION s11  
  20.         ),  
  21.         PARTITION t2 VALUES LESS THAN MAXVALUE  
  22.         (  
  23.             SUBPARTITION s12,  
  24.             SUBPARTITION s13,  
  25.             SUBPARTITION s14,  
  26.             SUBPARTITION s15,  
  27.             SUBPARTITION s16,  
  28.             SUBPARTITION s17  
  29.         )  
  30. );  

                    查询发现,15年的数据全部被删除,剩余的数据被重新分区并分布。

邵珠庆推荐文章

博文加载中...

anyShare分享到:

喜欢这个文章吗?

考虑订阅我们的RSS Feed吧!

发布在 邵珠庆

评论 (0) 引用 (0)

还没有评论.


Leave a comment

(required)


*

还没有引用.