mysql分区表

如果有没理解的地方，点个关注，私信我就行哈~~，看到了会及时回复

分区表的优势和缺点看下图

下面讲一下怎么创建分区表，以及创建和不创建的查询速度对比。
三张表数据量是一样的，都是100w，数据时间都是从2021.04.01-2021.04.20每天基本上是均匀数据量分配的。
另外就是我这里是按照精确到日来分配做的日分区，当然你们自己可以做成月分区这样。当然除了日期还可以是其他维度的，我这里就简单放个参考示例，新版的mysql应该已经支持更多了，各位可以自行百度

首先第一张表，分区表，innodb引擎

CREATE TABLE `trail_event11`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `region_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `event_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `event_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `event_time` datetime(0) NOT NULL,
  `event_type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `error_code` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `error_message` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `request_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `request_parameters` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `user_agent` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `service_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `source_ip_address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`id`, `event_time`) USING BTREE,
  UNIQUE INDEX `uniq_event_id`(`event_id`, `event_time`) USING BTREE,
  INDEX `idx_region_id`(`region_id`) USING BTREE
) ENGINE = InnoDB 
PARTITION BY RANGE ((year(event_time)*100+month(event_time))*100+day(event_time)) 
(
PARTITION p0 VALUES LESS THAN (20210401), 
PARTITION p1 VALUES LESS THAN (20210403), 
PARTITION p2 VALUES LESS THAN (20210405), 
PARTITION p3 VALUES LESS THAN (20210407),
PARTITION p4 VALUES LESS THAN (20210409), 
PARTITION p5 VALUES LESS THAN (202104011), 
PARTITION p6 VALUES LESS THAN MAXVALUE
);

下面是第二张表，myisam引擎

CREATE TABLE `trail_event11`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `region_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `event_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `event_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `event_time` datetime(0) NOT NULL,
  `event_type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `error_code` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `error_message` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `request_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `request_parameters` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `user_agent` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `service_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `source_ip_address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`id`, `event_time`) USING BTREE,
  UNIQUE INDEX `uniq_event_id`(`event_id`, `event_time`) USING BTREE,
  INDEX `idx_region_id`(`region_id`) USING BTREE
) ENGINE = myisam
PARTITION BY RANGE ((year(event_time)*100+month(event_time))*100+day(event_time)) 
(
PARTITION p0 VALUES LESS THAN (20210401), 
PARTITION p1 VALUES LESS THAN (20210403), 
PARTITION p2 VALUES LESS THAN (20210405), 
PARTITION p3 VALUES LESS THAN (20210407),
PARTITION p4 VALUES LESS THAN (20210409), 
PARTITION p5 VALUES LESS THAN (202104011), 
PARTITION p6 VALUES LESS THAN MAXVALUE
);

最后是不做分区的表

CREATE TABLE `trail_event`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `region_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `event_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `event_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `event_time` datetime(0) NOT NULL,
  `event_type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `error_code` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `error_message` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `request_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `request_parameters` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `user_agent` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `service_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `source_ip_address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `uniq_event_id`(`event_id`) USING BTREE,
  INDEX `idx_region_id`(`region_id`) USING BTREE
) ENGINE = InnoDB 

最后是我的查询语句和查询速度结果

trail日志查询100w数据量

-- 测试环境信息：16核cpu  时钟频率2.3GHZ, mysql配置文件默认且未做调优，索引为单索引，未做级联索引 --
-- 测试查询结果如下：--
-- (1)不做分区单表查询（目前在用的表模式） 55.732s --
-- (2)做分区myisam引擎查询 3.883s --
-- (3)做分区innodb引擎查询 0.654s --

测试时所用语句：
SELECT
 region_id
FROM
 trail_event e
 LEFT JOIN trail_user u ON e.event_id = u.event_id 
WHERE
 u.account_id = "ce02a562240b4753b78fdc54ee212f90" 
 AND e.region_id = "cn-wuxi1" 
 AND e.event_time BETWEEN "2021-04-02 00:00:00" 
 AND "2021-04-17 00:00:00" 
ORDER BY
 e.event_time DESC 
 LIMIT 10