MySql分区

项目场景：

订单表数据量极多 几千万数据

---

问题描述

查询速度效率低

---

解决方案：

1.首先数据降维，比如将订单表拆分成近三个月、近两天等表
2.其次采用mysql表分区来提高查询效率 用在给用户暴露的近三个月表当中 需要每天按时新建一个一天的分区 删除三个月前的最旧一天数据

分区测试表如下：

#创建分区表
CREATE TABLE `pay_order_consume_log_range` (
  `id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'ID',
  `date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`,`create_time`) USING BTREE,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC COMMENT='分区查询性能测试'
 PARTITION BY RANGE  COLUMNS(date)
(PARTITION p2023033015 VALUES LESS THAN ('2023-03-30 06:00:00') ENGINE = InnoDB,
 PARTITION p2023033016 VALUES LESS THAN ('2023-03-30 12:00:00') ENGINE = InnoDB,
 PARTITION p2023033017 VALUES LESS THAN ('2023-03-30 18:00:00') ENGINE = InnoDB);

INSERT INTO pay_order_consume_log_range(id,date)values(1,'2023-03-30 05:00:00');
INSERT INTO pay_order_consume_log_range(id,date)values(2,'2023-03-30 06:01:00');
INSERT INTO pay_order_consume_log_range(id,date)values(3,'2023-03-30 12:00:00');
INSERT INTO pay_order_consume_log_range(id,date)values(4,'2023-03-30 14:00:00');
INSERT INTO pay_order_consume_log_range(id,date)values(5,'2023-03-30 16:00:00');
INSERT INTO pay_order_consume_log_range(id,date)values(6,'2023-03-30 18:01:00');

#查询分区信息
SELECT * FROM information_schema.PARTITIONS
WHERE table_schema=database() AND table_name='cgc_pay_order_channel_consume_log_range';

#插入分区
alter table cgc_pay_order_channel_consume_log_range add PARTITION (partition p2023033115 values less than('2023-03-31 5:00:00'));

#删除指定分区
EXPLAIN alter table cgc_pay_order_channel_consume_log_range drop partition p2023033015;

测试结果如下：

分区表总结：
1.mysql 版本不同时，高版本mysql分区执行效率远超低版本mysql分区；
2.对于表进行小时/分钟分区以及不分区来说,筛选的时间越小 分区越小的表执行效率越高
出现的问题 在sum/count语句中十分钟分区模块高版本查询的速度低于低版本 可能原因：在MySQL 5.7及以下版本中，对于分区表的count查询语句，MySQL使用的是全表扫描的方式。这种方式可能会扫描更多的数据量，但是由于不需要进行分区统计的计算，因此查询效率可能会更高。在MySQL 8.0中，对于分区表的count查询语句，MySQL 8.0使用的是分区统计的方式，这样可以减少扫描的数据量，提高查询效率。但是，这种方式增加了统计的计算量，导致查询效率下降。
3.分区时间2023-03-30 06:00:00 之前时间的数据能全部插入成功（哪怕是2001-01-01 06:00:00） 但是18点之后的数据会插入失败，原因没该分区
4.时间限制：比如设置区间截至时间2011-01-01 12:00:00 那么入到该时间区域的表只能是小于2011-01-01 12:00:00
5.新建分区只能按时间顺序插入 不能从时间中插入分区
6.建表语句里需要把分区的字段 设置为 主键/唯一索引
7.查询时 时间范围越小的分区值 扫描的行数越少
8.分区数据时间一小时 或者 一小时六次 时 查询该小时 查询速度不变
9.一张表最多有1024个分区
10.查询分区信息 TABLE_ROWS 信息是预估值 不一定正确
11.750万数据新增分区以及删除旧分区时并未占用大量时间
12.删除分区的同时会将数据删除
13.分区表的索引会分发到每个分区：例如分区表里有其他索引【普通索引，组合索引等】那么每个分区都会有这个索引 分区越多索引长度就越大