业务要根据时间范围查询交易记录,接口原始的SQL如下:
select * from trade_info where status = 0 and create_time >= '2020-10-01 00:00:00' and create_time <= '2020-10-07 23:59:59' order by id desc limit 102120, 20;
表trade_info上有索引idx_status_create_time(status,create_time),通过上面分析知道,等价于索引**(status,create_time,id)**,对于典型的分页limit m, n来说,越往后翻页越慢,也就是m越大会越慢,因为要定位m位置需要扫描的数据越来越多,导致IO开销比较大,这里可以利用辅助索引的覆盖扫描来进行优化,先获取id,这一步就是索引覆盖扫描,不需要回表,然后通过id跟原表trade_info进行关联,改写后的SQL如下:
SELECT
*
FROM
trade_info a,
( SELECT id FROM trade_info WHERE STATUS = 0 AND create_time >= '2020-10-01 00:00:00' AND create_time <= '2020-10-07 23:59:59' ORDER BY id DESC LIMIT 102120, 20 ) AS b
WHERE
a.id = b.id;