当查询到的数据条数小于 limit 限制的条数时查询速度会很慢

[桃纭]

【 使用环境 】生产环境
ob数据库4.0.0
【问题描述】当查询到的数据条数小于 limit 限制的条数时查询速度会很慢
【复现路径】问题出现前后相关操作

使用的sql
SELECT id,first_time FROM hhit_asset assea WHERE (assea.ip_long BETWEEN 0 AND 4294967295) AND assea.ip like ‘%ip地址%’ ORDER BY assea.first_time DESC
SELECT id,first_time FROM (SELECT id,first_time FROM hhit_asset assea WHERE (assea.ip_long BETWEEN 0 AND 4294967295) AND assea.ip like ‘%ip地址%’ ORDER BY assea.first_time DESC ) assea LIMIT 9 ;
SELECT id,first_time FROM (SELECT id,first_time FROM hhit_asset assea WHERE (assea.ip_long BETWEEN 0 AND 4294967295) AND assea.ip like ‘%ip地址%’ ORDER BY assea.first_time DESC ) assea LIMIT 10 ;
有什么好办法解决吗？

[甘霖]

能否通过 explain extended 提供一下相关的执行计划？

[桃纭]

具体应该怎么做了？现在是当limit值如果大于子查询里面的值后，执行速度就会变慢很多。这个我们在4.2.2.0里面造数据测试测试过了，按照上面中图片操作一样会变慢

[桃纭]

explain extended.txt (2.4 KB)
执行的是速度慢的那条

[xuyu]

这种不是这么预期的，你造了这么一个场景limit值大于内层子查询，在计划优化阶段只能看到limit的值，根据这个提供的一点信息来生成计划，内层子查询的值是多少这时候是不知道的，因此这种场景没生成更优的执行计划是很好理解的，具体的性能问题要看具体的场景分析

[桃纭]

具体的情况有9条数据，通过limit限制为小于或者等于9时速度快。limit大于9时慢

但是不走子程序速度还是有区别
SELECT id,first_time FROM hhit_asset assea WHERE (assea.ip_long BETWEEN 0 AND 4294967295) AND assea.ip like ‘%192.168.60.4%’ ORDER BY assea.first_time DESC LIMIT 9；
SELECT id,first_time FROM hhit_asset assea WHERE (assea.ip_long BETWEEN 0 AND 4294967295) AND assea.ip like ‘%192.168.60.4%’ ORDER BY assea.first_time DESC LIMIT 10；
有没有办法优化一下

[桃纭]

还有没有大佬回复一下这个问题

[xuyu]

把Limit9的计划explain extended 计划也发下,另外assea上都有哪个索引呢

[桃纭]

explain extended.txt (2.5 KB)
hhit_asset索引

[xuyu]

计划没问题的，查询sql写得有问题，还是前面讲的，执行期并不知道你内层子查询有多少行，sql语义上limit 10的时候会尝试找10行，但并不知道你内层的子查询实际只有九行满足条件，所以会退化成扫描全表的情况，如果limit 9,在内层子查询找到九行就提前结束了

[桃纭]

SELECT id,first_time FROM hhit_asset assea WHERE (assea.ip_long BETWEEN 0 AND 4294967295) AND assea.ip like ‘%192.168.60.4%’ ORDER BY assea.first_time DESC LIMIT 9；
SELECT id,first_time FROM hhit_asset assea WHERE (assea.ip_long BETWEEN 0 AND 4294967295) AND assea.ip like ‘%192.168.60.4%’ ORDER BY assea.first_time DESC LIMIT 10；
这两条了？这里没有走子查询原因也是一样的嘛

[qiziyu]

你好，看一下这两张图，没有子查询也有这种情况

[qiziyu]

你好，可以看一下，上面那条是慢查询的

[昱旻]

索引 sortKey01 会导致两个 filter 需要回表计算, limit 9 很快就执行完了，limit 10 因为找不到第 10 行数据会全部扫描一遍。
可以添加 hint /*+index(assea key)*/指定索引 key，这样扫描会快一些

[qiziyu]

好的，

[qiziyu]

你好，目前指定索引去操作后查询速度有了成倍的提升，还有 ORDER BY assea.first_time,assea.id DESC 也就是图中第一种方式去优化速度也不相上下，经过多轮测试（查询不同语句，限制不同条数）去测试，相对而言添加索引的方式会快一些，快0.0几秒。感谢你的回复。