在test表中有2000w条数据,表中有ID主键和created索引,表结构如下:

CREATE TABLE `test` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(15) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_created` (`created`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=21036801 DEFAULT CHARSET=utf8mb4;

执行SQL:

SELECT * 
FROM test 
WHERE created>='2022-09-09 00:00:00' AND created<='2022-09-09 23:59:59' 
ORDER BY created DESC 
LIMIT 200000,50;

image.png
执行耗时46秒。在test表中虽然created有索引,但是由于limit是从结果集中取出偏移量之后的记录数,在上面的SQL中,需要进行(20w+50)次回表才能取出50条数据,前面的20w次回表根本不需要,完全是浪费时间和性能,故优化就是减少前面的20w次回表,只要50次回表拿数据就可以了。
优化后SQL:

SELECT t2.* 
    FROM test t2 INNER JOIN ( 
        SELECT id 
        FROM test 
        WHERE created >= '2022-09-09 00:00:00' AND created <= '2022-09-09 23:59:59' 
        ORDER BY created DESC 
        LIMIT 200000, 50 
    ) t1 ON t1.id = t2.id;

image.png
执行耗时需要0.34秒,提升40多倍。以上代码关键是在子查询,因为ID是主键,created也是一个索引,所以查询时都有走索引,所以速度特别快,子查询返回50条数据再根据ID进行join获取其他字段。

除了以上办法还可以通过记录前一次查询的ID值(前提是确保ID是按时间递增的):

SELECT * from test WHERE id > 200000 ORDER BY id limit 50;

image.png

最后修改:2022 年 09 月 15 日
如果觉得我的文章对你有用,请随意赞赏