Mysql 表分区实践

测试表分区查询效率：

创建分区表，按RANGE进行分区

CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `createtime` datetime NOT NULL,
  PRIMARY KEY (`id`,`createtime`)
) ENGINE=InnoDB AUTO_INCREMENT=1200594 DEFAULT CHARSET=utf8
 PARTITION BY RANGE (to_days(`createtime`))
(PARTITION p0 VALUES LESS THAN (738039) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (738040) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (738041) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (738042) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (738043) ENGINE = InnoDB,
 PARTITION p19 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) ;

创建普通表

CREATE TABLE `t2` (
  `id` int NOT NULL AUTO_INCREMENT,
  `createtime` datetime NOT NULL,
  PRIMARY KEY (`id`,`createtime`)
) ENGINE=InnoDB AUTO_INCREMENT=1200594 DEFAULT CHARSET=utf8

插入测试数据 5500592条数据。t1 和t1表 数据相同。

执行分区表测试SQL

 SELECT  * from t1 WHERE createtime 
BETWEEN '2020-09-09 00:12:00' and  '2020-09-09 23:00:00' 
ORDER BY createtime desc 
LIMIT 10,10;

首先解释执行，看执行计划，命中P4分区，检索数据量60万。

查询时间0.4S，效果还不错。

执行未分区T2表SQL

SELECT * from t2
  WHERE createtime BETWEEN '2020-09-09 00:12:00' and  '2020-09-09 23:00:00'
ORDER BY createtime desc
 LIMIT 10,10;

查看执行计划，需要扫描的行数大概在500万，远远大于分区表的扫描行数。

查询时间需要2.8S。

由此可见，在数据量比较大的情况下，使用分区表可以有效缩短查询时间，降低扫描行数，就8G内存，4核电脑来说，性能可以提升7倍。效果比较理想。