测试表分区查询效率:
创建分区表,按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倍。效果比较理想。