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中也有相关的索引缓存,在并发高的时候利用缓存就效果更好了。



展开阅读全文

没有更多推荐了,返回首页