mysql通过表分区来增加数据查询效率

背景

我们mysql是单机, 其中有几个表体积会很大, 需要做分表来增加查询效率.
我们的数据是连续不断入库的,查询条件是一个批次一个批次的查,也可以看做是按时间段查询.

思路

1. mysql事件Event:到一定时间把原表改名, 加一个后缀,比如user表改为user_1
   RENAME TABLE division_stop_data_1 TO division_stop_data;
   然后再重新创建原表同名同结构的空表

2. mycat的 方式: 需要一个mycat中间件服务, 通过修改mycat的路由规则,拦截解析修改sql语句达到访问不同子表的效果.
   参考:https://blog.csdn.net/qq_33709508/article/details/120728498

3. mysql的表分区: 将表按照某个列(分区键)的值建立多个分区(子表),查询时, 如果查询条件涉及分区键,则会只查询对应分区内的数据,避免全表扫描.
   参考1:https://www.cnblogs.com/yeyuzhuanjia/p/16149744.html
   参考2:https://blog.csdn.net/wrh_csdn/article/details/80019171

对比可行性

1. 自动归档
   第一种方法看似简单但是有一个致命问题, 在创建新表和归档旧表时, 同一批次的数据可能会被分割到两个表中, 原查询语句无法满足这种查询. 并且在查询旧表数据时,表名变了, 也不能满足. 需要配合mycat,把旧表配置到mycat中作为子表
2. 使用mycat有几个问题:
   a. 需要单独引入一个mycat中间件, 增加了运维成本,
   b. 并且mycat的schema文件中,为逻辑表配置子表时,子表不能使用通配符,必须指定确定的子表名称.此时必须提前创建好所有子表.可扩展性差
   
   c. mycat配置完成后,原表的数据要分配到子表中需要自行按照分表策略把原表中的数据分别迁移到子表中.这个就很麻烦了
3. 表分区
   MySQL在5.1时添加了对水平分区的支持。分区是将一个表或索引分解成多个更小，更可管理的部分。每个区都是独立的，可以独立处理，也可以作为一个更大对象的一部分进行处理。这个是MySQL支持的功能，业务代码无需改动。
   a.可以对已存在数据的表进行分区,且分区后数据会自动按分区规则进入相应的子表.
   b.无需增加中间件或是第三方插件

解决问题过程

本着改动最小,最符合我们的需求(单库水平分表)决定选择对几张大表进行表分区.

1. 表分区前提
   mysql5.1后支持表分区

2. 表分区类型
   MySQL支持的分区类型一共有四种：RANGE，LIST，HASH，KEY。
   其中，
   RANGE又可分为原生RANGE和RANGE COLUMNS，
   LIST分为原生LIST和LIST COLUMNS，
   HASH分为原生HASH和LINEAR HASH，
   KEY包含原生KEY和LINEAR HASH。
   
   分析: 因为我们是按照批次号来查询数据,批次号是datetime + '_' + 随机数组成的字符串,差不多还是按照时间递增的.于是我们想以时间为依据,半年的数据分一个表, 所以我们应该选用range columns分区类型. range columns是基于range在mysql5.5以后新增的分区类型.
   区别:range类型分区的分区依据(分区键)必须是int类型的列或者表达式, 而range columns则可以是除了blob和text以外类型的列,但不能是表达式.
   注意:
   a. 分区键必须是查询时要用到的条件列,否则查询还是全表扫描,不会提高查询效率.
   b. 分区键必须要么是主键要么是联合主键的一部分
   ALTER TABLE continue_data ADD PRIMARY KEY (id, batch_no);
   c. range colums分区键不能是表达式
   d. range colums分区键可以接受多个字段

3. 分区语法
   首先确保分区键对应的列是主键或者是复合主键的一部分, 如果不是,需要删除旧主键,再创建复合主键,例如

#如果主键是自增的,需要先取消自增
ALTER TABLE 表名 CHANGE id id BIGINT;
#先去掉旧主键,并等待完成
ALTER TABLE 表名 DROP PRIMARY KEY;
#创建符合主键,并等待完成
ALTER TABLE 表名 ADD PRIMARY KEY (id, batch_no);
#再设置id为自增
ALTER TABLE 表名 CHANGE id id BIGINT AUTO_INCREMENT;

对于已有的表,按照半年一个分区,修改语法,如果表中已有数据, 在分区完成后,旧数据会自动按照分区规则分配至对应的分区.

ALTER TABLE continue_data_1 PARTITION BY RANGE COLUMNS(batch_no) (
  PARTITION p0 VALUES LESS THAN ('202006'),
	PARTITION p1 VALUES LESS THAN ('202012'),
  PARTITION p2 VALUES LESS THAN ('202106'),
	PARTITION p3 VALUES LESS THAN ('202112'),
  PARTITION p4 VALUES LESS THAN ('202206'),
	PARTITION p5 VALUES LESS THAN ('202212'),
  PARTITION p6 VALUES LESS THAN ('202306'),
	PARTITION p7 VALUES LESS THAN ('202312'),
  PARTITION p8 VALUES LESS THAN MAXVALUE
);

对于新创建的表,直接在后面加partition by即可

CREATE TABLE `continue_data_1` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'id',
  `cab_no` int DEFAULT NULL COMMENT '柜号',
  `cell_no` int DEFAULT NULL COMMENT '库号',
  `channel_no` int DEFAULT NULL COMMENT '通道号',
  `battery_no` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '电芯码',
  `tray_no` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '托盘码',
  `batch_no` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '批次号',
  `recipe_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '配方名',
  `func_code` int DEFAULT NULL COMMENT '功能码',
  `data` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '续接内容',
  `create_timestamp` datetime DEFAULT NULL,
  `update_timestamp` datetime DEFAULT NULL,
  PRIMARY KEY (`id`,`batch_no`)
) ENGINE=InnoDB AUTO_INCREMENT=101416 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
 PARTITION BY RANGE  COLUMNS(batch_no)
(PARTITION p0 VALUES LESS THAN ('202006') ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN ('202106') ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN ('202206') ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN ('202306') ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) 

测试

1. 插入数据

//插入数据
delimiter $$
drop procedure if exists addBatchData;
CREATE PROCEDURE addBatchData()
BEGIN
    DECLARE num INT;
		DECLARE loop_no INT;
    SET num = 20200101;
    
    WHILE num <= 20301231 DO
				set loop_no = 1;
				while loop_no <= 100 DO
					INSERT INTO continue_data (batch_no, create_timestamp, update_timestamp) 
					VALUES (CONCAT(num, '000000_111'), NOW(), NOW());
					SET loop_no = loop_no + 1;
				END WHILE;
				SET num = num + 1;
    END WHILE;
    
END$$;
delimiter;

call addBatchData();

2. 查询一个指定批次号的数据,看看是否会避免全表扫描而直接去查那条数据所在的分区

explain SELECT * FROM continue_data where batch_no = '20220102000000_111';

3.对于千万级数据, 可以看到使用分区键查询会比普通方式查询快很多

SELECT * FROM continue_data where batch_no = '20220702000000_111';

我只给以上那100条数据设置了cab_no=1值,所以直接查cab_no=1也能查到上述100条数据,只不过这次没有用分区键做条件, 来看看执行时长:

explain SELECT * FROM continue_data where cab_no = 1;