百万级数据如何在原有表的基础上添加分区

作者: 梦昼初心 | 来源:发表于2020-05-02 09:24 被阅读0次

前言

对用户来说，分区表是一个独立的路基表，但是底层是由多个物理子表组成的。实现分区的代码实际上是对一致底层表的句柄对象（Handler Object）的封装。对分区表的请求，都会通过句柄对象转化成对存储引擎的接口调用。所以分区对与SQL层来说是一个完全封装底层实现的黑盒子，对应用是透明的，但是底层的文件系统来看就很容易发现，每一个分区表都有一个使用#分隔命名的表文件。

MySQL实现分区表的方式——对底层表的封装——意味着索引也是按照分区的子表定义的，而没有全局索引。

分区是指根据一定的规则，把数据库一个表分解成多个更小的表，更容易管理的部分。分区对应用来说是完全透明的，不影响应用的业务逻辑。

分区使用的场景

表非常大以至于无法全部都放在内存中，或者只在比偶的最后部分有热点数据，其他均是历史数据。
分区表的数据更容易维护。例如，想批量删除大量数据可以使用清楚整个分区的方式。另外，还可以对一个独立分区进行优化、检查、修复等操作。
分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备。
可以使用分区表来比年某些特殊的瓶颈，例如InnoDB的单个索引的互斥访问、ext3文件系统的inode锁竞争等。
如果需要，还可以备份和恢复独立的分区，这在非常大的数据集的场景下效果非常好。

分区表的限制

分区表本身也有一些限制，下面是其中比较重要的几点：

一个表最多智能有1024个分区；
在MySQL5.1中，分区表达式必须是整数，或者是返回整数的表达式。在MySQL5.5中，某些场景可以直接使用列来进行分区。
如uofenqu字段中有主键或者唯一索引的列，那么所有的主键列和唯一索引的列都必须包含进来。
分区表无法使用外键约束

mysql分区类型

RANGE 分区：
    基于属于一个给定连续区间的列值，把多行分配给分区。
LIST 分区：
    类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
HASH分区：
    基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式。
KEY分区：

    类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。
复合分区：
    基于RANGE/LIST 类型的分区表中每个分区的再次分割。子分区可以是 HASH/KEY 等类型。

分区需要注意的事项

以上已经说过分区使用的场景及限制，下面特别注意的情况：

需要删除添加分区表的所有外键，包含其他表与该表的外键；
创建分区时，当前分区的字段不能出现null数据库；
创建一个表并对其分区,被创建分区的字段必须为主键,或者创建分区时表中没有主键；
== 要对表的时间字段（类型：datetime）基于月进行分区，当分区表达式使用YEAR（）或TO-DAYS（）函数时，也可以对在DATE或DATETIME列上分区的表应用修剪。此外，在MySQL 5.7中，当分区表达式使用TO_SECONDS（）函数时，可以对这些表应用修剪,否则将进行全表，全分区查询==。
== 删除分区，指定分区的数据也会同步删除，谨慎操作==

创建分区语句

使用alter table的方式添加分区，分区按照消息时间，大体是每个月一个分区：

ALTER TABLE `tb_monitor`  PARTITION BY RANGE(YEAR(collecTime)*100+MONTH(collecTime)) (
         PARTITION p_201601 VALUES LESS THAN (201601) ENGINE = InnoDB,
        PARTITION p_201602 VALUES LESS THAN (201602) ENGINE = InnoDB,
        PARTITION p_201603 VALUES LESS THAN (201603) ENGINE = InnoDB,
        PARTITION p_201604 VALUES LESS THAN (201604) ENGINE = InnoDB,
        PARTITION p_201605 VALUES LESS THAN (201605) ENGINE = InnoDB,
        PARTITION p_201606 VALUES LESS THAN (201606) ENGINE = InnoDB,
        PARTITION p_201607 VALUES LESS THAN (201607) ENGINE = InnoDB,
        PARTITION p_201608 VALUES LESS THAN (201608) ENGINE = InnoDB,
        PARTITION p_201609 VALUES LESS THAN (201609) ENGINE = InnoDB,
        PARTITION p_201610 VALUES LESS THAN (201610) ENGINE = InnoDB,
        PARTITION p_201611 VALUES LESS THAN (201611) ENGINE = InnoDB,
        PARTITION p_201612 VALUES LESS THAN (201612) ENGINE = InnoDB,
        PARTITION p_201701 VALUES LESS THAN (201701) ENGINE = InnoDB,
        PARTITION p_201702 VALUES LESS THAN (201702) ENGINE = InnoDB,
        PARTITION p_201703 VALUES LESS THAN (201703) ENGINE = InnoDB,
        PARTITION p_201704 VALUES LESS THAN (201704) ENGINE = InnoDB,
        PARTITION p_201705 VALUES LESS THAN (201705) ENGINE = InnoDB,
        PARTITION p_201706 VALUES LESS THAN (201706) ENGINE = InnoDB,
        PARTITION p_201707 VALUES LESS THAN (201707) ENGINE = InnoDB,
        PARTITION p_201708 VALUES LESS THAN (201708) ENGINE = InnoDB,
        PARTITION p_201709 VALUES LESS THAN (201709) ENGINE = InnoDB,
        PARTITION p202XYZ VALUES LESS THAN (MAXVALUE)
);

刚开始使用的YEAR(collecTime)*100+MONTH(collecTime)运算进行分区，但是并不是我想要的结果，而是全表，全分区查询

explain select  id  from tb_monitor  where collecTime between  '2017-07-01 00:00:00' and  '2017-07-30 23:59:59';

在这里插入图片描述

经过一番查找，发现创建分区时不能使用运算，必须使用MySQL中的函数处理==（类型：datetime）基于月进行分区，必须使用函数处理 YEAR()、TO_DAYS()==

执行sql错误信息，时间格式不合法导致的，

ERROR 1566 (HY000): Not allowed to use NULL value in VALUES LESS THAN

优化sql语句

ALTER TABLE `tb_monitor`  PARTITION BY RANGE(TO_DAYS(collecTime)) (
  PARTITION p_201601 VALUES LESS THAN (TO_DAYS('2016-01-01')) ENGINE = InnoDB,
        PARTITION p_201602 VALUES LESS THAN (TO_DAYS('2016-02-01')) ENGINE = InnoDB,
        PARTITION p_201603 VALUES LESS THAN (TO_DAYS('2016-03-01')) ENGINE = InnoDB,
        PARTITION p_201604 VALUES LESS THAN (TO_DAYS('2016-04-01')) ENGINE = InnoDB,
        PARTITION p_201605 VALUES LESS THAN (TO_DAYS('2016-05-01')) ENGINE = InnoDB,
        PARTITION p_201606 VALUES LESS THAN (TO_DAYS('2016-06-01')) ENGINE = InnoDB,
        PARTITION p_201607 VALUES LESS THAN (TO_DAYS('2016-07-01')) ENGINE = InnoDB,
        PARTITION p_201608 VALUES LESS THAN (TO_DAYS('2016-08-01')) ENGINE = InnoDB,
        PARTITION p_201609 VALUES LESS THAN (TO_DAYS('2016-09-01')) ENGINE = InnoDB,
        PARTITION p_201610 VALUES LESS THAN (TO_DAYS('2016-10-01')) ENGINE = InnoDB,
        PARTITION p_201611 VALUES LESS THAN (TO_DAYS('2016-11-01')) ENGINE = InnoDB,
        PARTITION p_201612 VALUES LESS THAN (TO_DAYS('2016-12-01')) ENGINE = InnoDB,
        PARTITION p_201701 VALUES LESS THAN (TO_DAYS('2017-01-01')) ENGINE = InnoDB,
        PARTITION p_201702 VALUES LESS THAN (TO_DAYS('2017-02-01')) ENGINE = InnoDB,
        PARTITION p_201703 VALUES LESS THAN (TO_DAYS('2017-03-01')) ENGINE = InnoDB,
        PARTITION p_201704 VALUES LESS THAN (TO_DAYS('2017-04-01')) ENGINE = InnoDB,
        PARTITION p_201705 VALUES LESS THAN (TO_DAYS('2017-05-01')) ENGINE = InnoDB,
        PARTITION p202XYZ VALUES LESS THAN (MAXVALUE)
);

执行成功，执行速度大大提升

在这里插入图片描述

实现步骤

-- 删除tb_environment、tb_bug、tb_picture表中的monitorId外建
-- 1.删除外键

alter table `tb_environment` drop foreign key fk_envir_mon_monitorId;
alter table `tb_bug` drop foreign key fk_bug_mon_monitorId;
alter table `tb_picture` drop foreign key fk_pic_mon_monId;

alter table `tb_monitor` drop foreign key fk_mon_area_areaId;
alter table `tb_monitor` drop foreign key fk_mon_eq_eqId;

-- 2.删除1970年数据,2075年数据,0000-00-00 00:00:00数据

select * from `tb_monitor` where date_format(collecTime,'%Y')='1970';
DELETE  FROM  `tb_monitor` where date_format(collecTime,'%Y')='1970';

select * from `tb_monitor` where date_format(collecTime,'%Y')='2075';
DELETE  FROM  `tb_monitor` where date_format(collecTime,'%Y')='2075';

select * from `tb_monitor` where date_format(collecTime,'%Y-%m-%d %H:%i:%s')='0000-00-00 00:00:00';
DELETE  FROM  `tb_monitor` where date_format(collecTime,'%Y-%m-%d %H:%i:%s')='0000-00-00 00:00:00';

-- 3.删除主键

ALTER TABLE `tb_monitor` DROP PRIMARY KEY;

-- 4.添加ID与collecTime的联合主键

ALTER TABLE `tb_monitor` ADD PRIMARY KEY (`id`, `collecTime`);

-- 5.删除collecTime为null

select  *  from   `tb_monitor` WHERE  collecTime IS NULL;

DELETE FROM `tb_monitor` WHERE  collecTime IS NULL;

-- 6.删除所有索引

alter table `tb_monitor` drop index fk_mon_area_areaId ;
alter table `tb_monitor` drop index fk_mon_eq_eqId ;
alter table `tb_monitor` drop index index_collecTime ;
alter table `tb_monitor` drop index index_type ;
alter table `tb_monitor` drop index index_id ;
alter table `tb_monitor` drop index index_isDelete ;

-- 7.分区后将所有的索引删除后,数据查询仍然非常慢,尝试添加联合索引解决
-- 添加联合索引(mysql 分区会()
-- 如果定义的索引列和分区列不匹配，会导致查询无法进行分区过滤。假设在列a上定义了索引，而在列b上定义的分区。因为每个分区都有其独立的索引，所以扫描列b上的索引就需要扫描每个分区内对应的索引。要避免这个问题，应该避免建立和分区列不匹配的索引，除非查询中还同时包含了可以过滤分区的条件。

ALTER TABLE `tb_monitor` ADD INDEX index_union ( `id`,`collecTime` ) ;

-- 加入联合索引并没有解决查询慢的问题

alter table  `tb_monitor` drop  index  index_union;

-- 加入分区字段的索引,失败仍然全表搜索

alter table `tb_monitor` ADD index index_collecTime (`collecTime`) ;


alter table `tb_monitor` drop index index_collecTime ;

-- 7,尝试加入联合主键

ALTER TABLE `tb_monitor` ADD PRIMARY KEY (`id`, `collecTime`);

-- 8.尝试添加where 条件索引

alter table `tb_monitor` ADD index index_type (`type`) ;

alter table `tb_monitor` ADD index index_isDelete (`isDelete`) ;

-- 9.分区情况查询

SELECT  *  FROM

  INFORMATION_SCHEMA.partitions

WHERE

  TABLE_SCHEMA = schema()

  AND TABLE_NAME='tb_monitor';

10.分区全表，全分区查询
-- 当分区表达式包含一个等式或一个可以缩减为一组等式的范围时，
-- 或者当分区表达式表示一个递增或递减关系时，都可以应用这种类型的优化。
-- 当分区表达式使用YEAR（）或TO-DAYS（）函数时，
-- 也可以对在DATE或DATETIME列上分区的表应用修剪。此外，在MySQL 5.7中，当分区表达式使用TO_SECONDS（）函数时，可以对这些表应用修剪。

-- 删除分区，指定分区的数据也会同步删除，谨慎操作。
-- 创建分区

ALTER TABLE `tb_monitor`  PARTITION BY RANGE(TO_DAYS(collecTime)) (
  PARTITION p_201601 VALUES LESS THAN (TO_DAYS('2016-01-01')) ENGINE = InnoDB,
        PARTITION p_201602 VALUES LESS THAN (TO_DAYS('2016-02-01')) ENGINE = InnoDB,
        PARTITION p_201603 VALUES LESS THAN (TO_DAYS('2016-03-01')) ENGINE = InnoDB,
        PARTITION p_201604 VALUES LESS THAN (TO_DAYS('2016-04-01')) ENGINE = InnoDB,
        PARTITION p_201605 VALUES LESS THAN (TO_DAYS('2016-05-01')) ENGINE = InnoDB,
        PARTITION p_201606 VALUES LESS THAN (TO_DAYS('2016-06-01')) ENGINE = InnoDB,
        PARTITION p_201607 VALUES LESS THAN (TO_DAYS('2016-07-01')) ENGINE = InnoDB,
        PARTITION p_201608 VALUES LESS THAN (TO_DAYS('2016-08-01')) ENGINE = InnoDB,
        PARTITION p_201609 VALUES LESS THAN (TO_DAYS('2016-09-01')) ENGINE = InnoDB,
        PARTITION p_201610 VALUES LESS THAN (TO_DAYS('2016-10-01')) ENGINE = InnoDB,
        PARTITION p_201611 VALUES LESS THAN (TO_DAYS('2016-11-01')) ENGINE = InnoDB,
        PARTITION p_201612 VALUES LESS THAN (TO_DAYS('2016-12-01')) ENGINE = InnoDB,
        PARTITION p_201701 VALUES LESS THAN (TO_DAYS('2017-01-01')) ENGINE = InnoDB,
        PARTITION p_201702 VALUES LESS THAN (TO_DAYS('2017-02-01')) ENGINE = InnoDB,
        PARTITION p_201703 VALUES LESS THAN (TO_DAYS('2017-03-01')) ENGINE = InnoDB,
        PARTITION p_201704 VALUES LESS THAN (TO_DAYS('2017-04-01')) ENGINE = InnoDB,
        PARTITION p_201705 VALUES LESS THAN (TO_DAYS('2017-05-01')) ENGINE = InnoDB,
        PARTITION p202XYZ VALUES LESS THAN (MAXVALUE)
);

分区钱的查询速度为4.5秒左右，数据99w+条数据

在这里插入图片描述

分区后的查询速度为2秒左右，90w条数据

在这里插入图片描述

百万级数据如何在原有表的基础上添加分区

前言

分区使用的场景

分区表的限制

mysql分区类型

分区需要注意的事项

创建分区语句

实现步骤

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

简友广场