我的查询运行缓慢,有些索引被选中而不是其他索引。我正试图找到一个工具或指南,用它我可以弄清楚为什么MySQL决定优先选择1个索引或1个表(在连接的情况下),这样我就可以微调索引或查询。
到目前为止,我还没有遇到一篇详细解释它的文章,也没有一个工具可以为我提供它的细节
任何投入都将不胜感激。提前感谢!
随着Optimizer变得越来越复杂,它越来越难理解它在做什么。最新的改进包括对可能的执行方法进行"基于成本"的分析。对于许多查询,一个索引显然比另一个要好。
目前有4种观点:
-
EXPLAIN
是相当有限的。它不能很好地处理LIMIT
,也不一定说明哪个步骤使用文件端口,甚至不一定说明是否有多个文件端口。通常,"行"列是有用的,但在某些情况下,它是无用的。简单的规则:一个大的数字是一个坏兆头。 -
EXPLAIN EXTENDED
+SHOW WARNINGS;
提供了查询的重写版本。这并没有多大作用。它确实为JOINs
中ON
和WHERE
的区别提供了线索。 -
EXPLAIN FORMAT=JSON
为基于成本的分析提供了更多细节,并阐述了包括文件端口在内的各种步骤。 -
"Optimizer trace"更进一步。(阅读起来相当乏味。)
至于"可视化",没有。无论如何,EXPLAIN
和它的朋友们只使用他们所拥有的。也就是说,它们没有给出"如果你添加了INDEX(a,b)会怎样"的线索。这才是真正需要的。它也没有有效地指出不应该"在函数调用中隐藏索引列"。示例:WHERE DATE(dt) = '2019-01-23'
。请注意,有些"运算符"实际上是函数调用。
我看过一些"图形解释",但它们似乎只不过是将EXPLAIN
的行装箱并在它们之间划线。
多年来,我一直在与这些问题作斗争,并写了一本部分答案——即"食谱"。它从另一个方向进行索引——解释为给定的SELECT
添加什么索引。不幸的是,它只适用于更简单的查询。http://mysql.rjweb.org/doc.php/index_cookbook_mysql
我在这个论坛上处理了很多性能问题,希望能更多地了解下一步该在食谱中添加什么。目前,您可以通过发布您的难题以及EXPLAIN SELECT
和SHOW CREATE TABLE(s)
来帮助我。
一些随机评论:
-
"索引合并交集"可能总是不如合成索引。
-
"索引合并联合"几乎从未使用过。您可能能够有效地将
OR
转换为UNION
。 -
更新的Optimizer为"派生表"(
JOIN ( SELECT ... )
)动态创建索引。但是,当返回大量行时,这很少像重写查询以避免这样的子查询那样有效。(同样,EXPLAINs
中没有一个会指向您。) -
一些经常被遗忘的东西(但确实显示为无法解释的大"行"):
COLLATIONs
必须匹配。 -
利用PK聚类的一个技巧:
PRIMARY KEY(foo, id), INDEX(id)
-
没有任何东西(除了经验)表明"前缀"索引是多么的无用(
INDEX(bar(10))
)。 -
FORCE INDEX
对实验来说很方便,但对生产来说几乎总是个坏主意。 -
在具有
JOIN
的SELECT
和仅提及其中一个表的WHERE
中,优化器将通常选择WHERE
中提及的表作为第一个表。然后,它将对其他表执行"嵌套循环联接"。
(我应该在我的食谱中添加一些。"敬请关注"。更新:完成。)