例如,如果有一个名为paper的表,我将使用[ select paper.user_id,paper.name,paper.score from paper where user_id in(201,205,209......)]执行sql
我观察到,在执行此语句时,只有当“in”的个数小于某个数字时才会使用索引,而这个数字是动态的。例如,当表的总行数为4000,基数为3939时,“in”的个数必须小于790,MySQL才会执行索引查询。(查看MySQL的解释,如果〈790,type=range; if〉790,type=all)当表的总行数为1300000,基数为1199166时,“in”的个数必须小于8500,MySQL将执行索引查询。
这个实验的结果对我来说很奇怪
我设想,如果我实现了这个“in”查询,我将首先找到in(max)和in(min),然后找到in(max)和in(min)所在的页,然后排除in(min)之前的页和in(max)之后的页。这肯定比执行全表扫描要快。
那么,我的测试数据可以总结如下:表1中的数据为1300000“中的数据”为900000为920000
我的问题是,在一个有1300000行数据的表中,为什么MySQL认为当“in”的数量超过8500时,它不需要执行索引查询呢?
mysql版本5.7.20
实际上,这个神奇的数字是8452。当表中的总行数是600000时,它是8452。当表中的总行数是1300000时,它仍然是8452。下面是我的测试屏幕截图
当in的个数为8452时,此查询只需要0.099s.
然后查看执行计划. range查询。
如果我将in的数目从8452增加到8453,即使我只添加一个重复的元素,这个查询也将花费5.066秒。
然后查看执行计划。
这真的很奇怪,这意味着如果我先执行带有“8452 in”的查询,然后再执行剩下的查询,总的时间要比直接执行带有“8453 in”的查询快得多。
谁可以调试MySQL源代码以查看此过程中发生了什么?
非常感谢。
2条答案
按热度按时间qvk1mo1f1#
很棒的问题和很好的发现!
查询计划器/优化器必须决定是搜索需要读取的页面,还是开始阅读更多页面并扫描需要的页面。搜索策略需要更多内存,尤其是CPU密集型内存,而扫描策略可能在I/O方面开销更大。
表越大,查找策略的吸引力就越小。对于大表,用于查找的非聚集索引的较大部分需要来自磁盘,查找所需的时间越长,内存压力就越大,顺序读取的可能性也就越小。因此,查找的行/结果比率的阈值随着表大小的增加而降低。
如果这是一个问题,您可以尝试调整一些东西。但是,如果这是一个问题,你在生产中,这可能是一个适当的时间考虑服务器升级,优化查询和软件涉及或只是调整预期。
e4yzc0pl2#
分两步完成一个查询通常是愚蠢的。那个框架似乎是在一步中获取id,然后在第二步中获取真实的的东西。
如果将两个查询合并为一个on(使用
JOIN
),则Optimizer通常被迫执行随机查找。“Range”可能总是
IN
查找的“type”。不要读它。无论IN
是否查看min和max来尝试最小化磁盘命中--我希望这是一个“最近”的优化。(我在Changelogs中没有它。)这些UUID是否去掉了破折号?它们不能很好地扩展到大型表。
“基数”只是一个估计。
ANALYZE TABLE
强制重新计算这些统计数据。看看这是否会改变边界等。