问为什么MySQL (InnoDB)性能有很大的差异？
EN

Stack Overflow用户

提问于 2012-03-12 12:31:20

回答 3查看 361关注 0票数 1

我开始研究为什么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查询通常可以返回更多的匹配。

mysql