为什么MySQL (InnoDB)性能有很大的差异？
我开始研究为什么Django管理中的某些搜索非常慢(请参阅这里)。深入研究后，我发现MySQL (5.1，InnoDB表)的性能在不同查询之间有很大差异。例如：
Django生成的这个查询(在4个字段中查找'c‘、'd’和'e‘，两个相关字段)获取89 ms，并返回3093行：
SELECT DISTINCT `donnees_artiste`.`id`
FROM `donnees_artiste`
LEFT OUTER JOIN `donnees_artiste_evenements`
ON (`donnees_artiste`.`id` = `donnees_artiste_evenements`.`artiste_id`)
LEFT OUTER JOIN `donnees_evenement`
ON (`donnees_artiste_evenements`.`evenement_id` = `donnees_evenement`.`id`)
LEFT OUTER JOIN `donnees_artiste_evenements` T4
ON (`donnees_artiste`.`id` = T4.`artiste_id`)
LEFT OUTER JOIN `donnees_evenement` T5
ON (T4.`evenement_id` = T5.`id`)
LEFT OUTER JOIN `donnees_artiste_evenements` T6
ON (`donnees_artiste`.`id` = T6.`artiste_id`)
LEFT OUTER JOIN `donnees_evenement` T7
ON (T6.`evenement_id` = T7.`id`)
WHERE (
(`donnees_artiste`.`nom` LIKE '%c%'
OR `donnees_artiste`.`prenom` LIKE '%c%'
OR `donnees_evenement`.`cote` LIKE '%c%'
OR `donnees_evenement`.`titre` LIKE '%c%' )
AND (`donnees_artiste`.`nom` LIKE '%d%'
OR `donnees_artiste`.`prenom` LIKE '%d%'
OR T5.`cote` LIKE '%d%'
OR T5.`titre` LIKE '%d%' )
AND (`donnees_artiste`.`nom` LIKE '%e%'
OR `donnees_artiste`.`prenom` LIKE '%e%'
OR T7.`cote` LIKE '%e%'
OR T7.`titre` LIKE '%e%' )
);如果我用'k‘替换'e’，那么它基本上是相同的查询，它将花费8720 ms (增加100倍)并返回931行。
SELECT DISTINCT `donnees_artiste`.`id`
FROM `donnees_artiste`
LEFT OUTER JOIN `donnees_artiste_evenements`
ON (`donnees_artiste`.`id` = `donnees_artiste_evenements`.`artiste_id`)
LEFT OUTER JOIN `donnees_evenement`
ON (`donnees_artiste_evenements`.`evenement_id` = `donnees_evenement`.`id`)
LEFT OUTER JOIN `donnees_artiste_evenements` T4
ON (`donnees_artiste`.`id` = T4.`artiste_id`)
LEFT OUTER JOIN `donnees_evenement` T5
ON (T4.`evenement_id` = T5.`id`)
LEFT OUTER JOIN `donnees_artiste_evenements` T6
ON (`donnees_artiste`.`id` = T6.`artiste_id`)
LEFT OUTER JOIN `donnees_evenement` T7
ON (T6.`evenement_id` = T7.`id`)
WHERE (
(`donnees_artiste`.`nom` LIKE '%c%'
OR `donnees_artiste`.`prenom` LIKE '%c%'
OR `donnees_evenement`.`cote` LIKE '%c%'
OR `donnees_evenement`.`titre` LIKE '%c%' )
AND (`donnees_artiste`.`nom` LIKE '%d%'
OR `donnees_artiste`.`prenom` LIKE '%d%'
OR T5.`cote` LIKE '%d%'
OR T5.`titre` LIKE '%d%' )
AND (`donnees_artiste`.`nom` LIKE '%k%'
OR `donnees_artiste`.`prenom` LIKE '%k%'
OR T7.`cote` LIKE '%k%'
OR T7.`titre` LIKE '%k%' )
);这两个查询都提供了相同的EXPLAIN，因此没有任何线索。
ID SELECT_TYPE TABLE TYPE POSSIBLE_KEYS KEY KEY_LEN REF ROWS EXTRA
1 SIMPLE donnees_artiste ALL None None None None 4368 Using temporary; Using filesort
1 SIMPLE donnees_artiste_evenements ref artiste_id,donnees_artiste_evenements_eb99df11 artiste_id 4 mmac.donnees_artiste.id 1 Using index; Distinct
1 SIMPLE donnees_evenement eq_ref PRIMARY,donnees_evenements_id_index PRIMARY 4 mmac.donnees_artiste_evenements.evenement_id 1 Using where; Distinct
1 SIMPLE T4 ref artiste_id,donnees_artiste_evenements_eb99df11 artiste_id 4 mmac.donnees_artiste.id 1 Using index; Distinct
1 SIMPLE T5 eq_ref PRIMARY,donnees_evenements_id_index PRIMARY 4 mmac.T4.evenement_id 1 Using where; Distinct
1 SIMPLE T6 ref artiste_id,donnees_artiste_evenements_eb99df11 artiste_id 4 mmac.donnees_artiste.id 1 Using index; Distinct
1 SIMPLE T7 eq_ref PRIMARY,donnees_evenements_id_index PRIMARY 4 mmac.T6.evenement_id 1 Using where; Distinct另外，如果我对第一个查询执行COUNT，则需要11200 ms。
SELECT COUNT(DISTINCT `donnees_artiste`.`id`)
FROM `donnees_artiste`
LEFT OUTER JOIN `donnees_artiste_evenements`
ON (`donnees_artiste`.`id` = `donnees_artiste_evenements`.`artiste_id`)
LEFT OUTER JOIN `donnees_evenement`
ON (`donnees_artiste_evenements`.`evenement_id` = `donnees_evenement`.`id`)
LEFT OUTER JOIN `donnees_artiste_evenements` T4
ON (`donnees_artiste`.`id` = T4.`artiste_id`)
LEFT OUTER JOIN `donnees_evenement` T5
ON (T4.`evenement_id` = T5.`id`)
LEFT OUTER JOIN `donnees_artiste_evenements` T6
ON (`donnees_artiste`.`id` = T6.`artiste_id`)
LEFT OUTER JOIN `donnees_evenement` T7
ON (T6.`evenement_id` = T7.`id`)
WHERE (
(`donnees_artiste`.`nom` LIKE '%c%'
OR `donnees_artiste`.`prenom` LIKE '%c%'
OR `donnees_evenement`.`cote` LIKE '%c%'
OR `donnees_evenement`.`titre` LIKE '%c%' )
AND (`donnees_artiste`.`nom` LIKE '%d%'
OR `donnees_artiste`.`prenom` LIKE '%d%'
OR T5.`cote` LIKE '%d%'
OR T5.`titre` LIKE '%d%' )
AND (`donnees_artiste`.`nom` LIKE '%e%'
OR `donnees_artiste`.`prenom` LIKE '%e%'
OR T7.`cote` LIKE '%e%'
OR T7.`titre` LIKE '%e%' )
);我的innodb_buffer_pool_size设置得很高。我在所有相关字段和主键上都有索引，并且我已经优化了我的表。
那么，为什么第一个查询如此快，而其他两个查询却如此缓慢？这3个查询只是例子。很多时候，我只是从查询中更改或删除一个字符，这对查询时间产生了很大影响。但我看不见任何图案。
更新
性能问题肯定来自Django如何生成这些查询。所有这些冗余的LEFT OUTER JOIN链接在一起，扼杀了性能。目前，我还不完全清楚这是Django SQL生成器中的错误，是如何为搜索字段构建查询的bug，还是Django开发人员所期望的那样。我还在调查，但至少在Django的行为中有一件奇怪的事.
如果我运行这个查询(这不一定等同于第二个查询，但不算太远)，结果会非常快(161 ms，没有缓存)：
SELECT DISTINCT `donnees_artiste`.`id`
FROM `donnees_artiste`
LEFT OUTER JOIN `donnees_artiste_evenements`
ON (`donnees_artiste`.`id` = `donnees_artiste_evenements`.`artiste_id`)
LEFT OUTER JOIN `donnees_evenement`
ON (`donnees_artiste_evenements`.`evenement_id` = `donnees_evenement`.`id`)
WHERE (
(`donnees_artiste`.`nom` LIKE '%c%'
OR `donnees_artiste`.`prenom` LIKE '%c%'
OR `donnees_evenement`.`cote` LIKE '%c%'
OR `donnees_evenement`.`titre` LIKE '%c%' )
AND (`donnees_artiste`.`nom` LIKE '%d%'
OR `donnees_artiste`.`prenom` LIKE '%d%'
OR `donnees_evenement`.`cote` LIKE '%d%'
OR `donnees_evenement`.`titre` LIKE '%d%' )
AND (`donnees_artiste`.`nom` LIKE '%k%'
OR `donnees_artiste`.`prenom` LIKE '%k%'
OR `donnees_evenement`.`cote` LIKE '%k%'
OR `donnees_evenement`.`titre` LIKE '%k%' )
);第二次更新
最后，在Django中，这不是一个bug，我很确定这是想要的行为。其思想是，在多项搜索中，下一项的搜索是在上一项的子集返回上完成的，因此，对于相关字段，所有的项都不必在同一行中才能匹配。为此，DB必须创建包含每个子集的临时表并对其进行扫描。这就解释了为什么会有很多变化，因为如果第一个项只匹配几行，那么临时表就会很小，而对后续项的搜索将很快(因为它们将在一个小表上完成)。这两个查询之间的差别是微妙的，但是Django查询通常可以返回更多的匹配。
回答 3
Stack Overflow用户
发布于 2012-03-12 21:16:32
我认为，答案是，在大多数情况下，e位于扫描字符串的开头，在第一次搜索的字符串中，允许缩短OR条件，而对k的匹配发生在最后的条件和字符串的末尾。而且，由于k的行要少得多，所以应该在没有任何匹配的情况下完整扫描更多的字符串。
Stack Overflow用户
发布于 2012-03-12 22:02:10
如果在引导通配符中使用LIKE模式，则查询将不会使索引受益。以这种方式使用LIKE可能效率很低，其执行时间可能会有很大差异。为什么？
- LIKE语句后面的算法停止搜索一行，以防遇到匹配。
- 在这种情况下(不使用索引)，MySQL应用了一些其他增强算法，这些算法在某些情况下可能适用，也可能不适用。
为什么在您的第三个查询中使用COUNT会大大降低它的速度？
我看你在用innoDB。
innoDB不像MyISAM那样读取存储/缓存值中的行数(如果列不是空的话)，因为innoDB对“写”比“读取”(与MyISAM相反)更加优化。每次执行“计数innoDB表”时，都要执行全表扫描或全索引扫描。
您的查询不使用任何索引，这可能是最糟糕的情况，因此会发生全表扫描(是的，它听起来很慢)。
我想你可能会对：MySQL指数感兴趣
Stack Overflow用户
发布于 2012-03-12 21:06:02
类型的条件：
WHERE column LIKE '%c%'无法在column上使用索引。因此，必须对这些列进行全面扫描。
您有多个这样的条件，在它们之间使用OR (这可以确保所有这些表都会被扫描)。最后，您(更确切地说: Django是)在返回结果之前添加了可能需要一个最终文件的DISTINCT。
我找不到一个解释的巨大差异在性能(100倍)。也许第一个查询是缓存的。您能尝试在查询的末尾添加ORDER NY NULL并对它们进行计时吗？
生成的查询也不是很好的设计，因为它可能以Join结尾。您要将基表连接到多个表，多个表与基表有一对多的关系。这是性能不佳的原因之一，查询计划将有助于澄清这一点。
https://stackoverflow.com/questions/9674363
复制相似问题
腾讯云开发者
