SQL大数据量分页性能优化

目前在进行web api只读接口的改造，在改造过程中，发现改在后响应时间和之前区别不是很大，通过测试结果显示在sql的分页功能处找到原因，并对其进行优化，优化方案如下。

测试内容

此次运行时间对比采用平台资金记录最多的用户 user_id 36062

测试次数未5次  为避免索引缓存每次测试前更改 limit 的起始值，查询条数10不变。

5次结果取平均值优化前平均查询时间为0.287s

         优化后 sql1平均查询时间为0.012s

         优化后 sql2 平均查询时间为0.016s

结果显示：优化后的sql1和sql2查询效率明显高于优化前。

通过先查找到所有满足条件的索引，然后通过索引检索到所需要的数据，效率提高很多。

附：sql

原sql

SELECT  * FROM  account_log WHERE user_id  =  36062ORDER BY id DESC LIMIT 3300, 10;

优化后的sql 1

SELECT * FROM account_log a JOIN ( SELECTid FROM rd_account_log WHERE user_id = 36062 ORDER BY id DESC LIMIT 3300, 10) bON b.id = a.id;

优化有sql 2

SELECT * FROM account_log WHERE user_id =36062 AND id <=(SELECT id FROM rd_account_log

WHERE user_id = 36062 ORDER BY id DESC LIMIT33416,1) LIMIT 10

总结

1、随着起始记录的增加，时间也随着增大， 这说明分页语句limit跟起始页码是有很大关系的；

1）limit语句的查询时间与起始记录的位置成正比
2）mysql的limit语句是很方便，但是对记录很多的表并不适合直接使用。

2、利用表的覆盖索引来加速分页查询
我们都知道，利用了索引查询的语句中如果只包含了那个索引列（覆盖索引），那么这种情况会查询很快。

因为利用索引查找有优化算法，且数据就在查询索引上面，不用再去找相关的数据地址了，这样节省了很多时间。另外Mysql中也有相关的索引缓存，在并发高的时候利用缓存就效果更好了。