mysql - 为什么这个 "Mysql"查询偶尔会挂起一次？

Question

我正在使用复杂的 MySQL 查询 来通过 Key,Value 表获取数据。为了实现这一目标，我们正在执行多重自连接。一开始我们认为问题出在 MySQL 中的按位操作，因为它们无法使用索引来完成。 但是如果不使用按位运算，问题仍然存在。即，如果将所有按位或更改为等于(==)

，也会发生同样的情况

查询

以下查询通常运行大约500毫秒。但是偶尔 MySQL 会在 10 秒内运行此查询!! 甚至更多......

SELECT DISTINCT sometable1_.id AS col_0_0_,
            sometable1_.hebrewName AS col_1_0_,
            sometable1_.urlBestBigPhoto AS col_2_0_
FROM sometable1 sometable1_
INNER JOIN sometable2 characters1_ ON sometable1_.id=characters1_.term_id
INNER JOIN sometable2 characters2_ ON sometable1_.id=characters2_.term_id
INNER JOIN sometable2 characters3_ ON sometable1_.id=characters3_.term_id
INNER JOIN sometable2 characters4_ ON sometable1_.id=characters4_.term_id
INNER JOIN sometable2 characters5_ ON sometable1_.id=characters5_.term_id
INNER JOIN sometable2 characters6_ ON sometable1_.id=characters6_.term_id
INNER JOIN sometable2 characters7_ ON sometable1_.id=characters7_.term_id
INNER JOIN sometable2 characters8_ ON sometable1_.id=characters8_.term_id
INNER JOIN sometable2 characters9_ ON sometable1_.id=characters9_.term_id
WHERE sometable1_.category="ABC"
AND (characters2_.value = 1<>0
   OR characters2_.value=0)
AND characters2_.name="somevalue2"
AND (characters3_.value & 2<>0
   OR characters3_.value=0)
AND characters3_.name="somevalue3"
AND (characters4_.value = 128<>0
   OR characters4_.value=0)
AND characters4_.name="somevalue4"
AND (characters5_.value = 1<>0
   OR characters5_.value=0)
AND characters5_.name="somevalue5"
AND (characters6_.value = 16392<>0
   OR characters6_.value=0)
AND characters6_.name="somevalue6"
AND (characters7_.value = 1<>0     OR characters7_.value=0)
AND characters7_.name="somevalue7"
AND (characters8_.value = 256<>0     OR characters8_.value=0)
AND characters8_.name="somevalue8"
AND (characters9_.value = 124<>0    OR characters9_.value=0)
AND characters9_.name="somevalue9"
AND (characters1_.name="somevalue10"  OR characters1_.name="somevalue11")
GROUP BY sometable1_.id,
     characters9_.term_id,
     characters8_.term_id,
     characters7_.term_id,
     characters6_.term_id,
     characters5_.term_id,
     characters4_.term_id,
     characters3_.term_id,
     characters2_.term_id
     ORDER BY sum(characters1_.value) DESC, sometable1_.text ASC LIMIT 10

Mysql解释

使用解释后我们得到

╔════╦═════════════╦═══════════════╦════════╦═════════════════════╦═════════╦═════════╦══════════════════════╦══════╦═════════════════════════════════════════════════════════════════════╗
║ id ║ select_type ║     table     ║  type  ║    possible_keys    ║   key   ║ key_len ║         ref          ║ rows ║                                Extra                                ║
╠════╬═════════════╬═══════════════╬════════╬═════════════════════╬═════════╬═════════╬══════════════════════╬══════╬═════════════════════════════════════════════════════════════════════╣
║  1 ║ SIMPLE      ║ characters1_  ║ range  ║ FK_47,name          ║ name    ║      77 ║ NULL                 ║ 2146 ║ Using index condition; Using where; Using temporary; Using filesort ║
║  1 ║ SIMPLE      ║ sometable1    ║ eq_ref ║ PRIMARY,category,id ║ PRIMARY ║       8 ║ characters1_.item_id ║    1 ║ Using where                                                         ║
║  1 ║ SIMPLE      ║ characters5_  ║ ref    ║ FK_47,name,value    ║ FK_47   ║       9 ║ characters1_.item_id ║    9 ║ Using where; Distinct                                               ║
║  1 ║ SIMPLE      ║ characters7_  ║ ref    ║ FK_47,name,value    ║ FK_47   ║       9 ║ characters1_.item_id ║    9 ║ Using where; Distinct                                               ║
║  1 ║ SIMPLE      ║ characters2_  ║ ref    ║ FK_47,name,value    ║ FK_47   ║       9 ║ characters1_.item_id ║    9 ║ Using where; Distinct                                               ║
║  1 ║ SIMPLE      ║ characters3_  ║ ref    ║ FK_47,name,value    ║ FK_47   ║       9 ║ characters1_.item_id ║    9 ║ Using where; Distinct                                               ║
║  1 ║ SIMPLE      ║ characters9_  ║ ref    ║ FK_47,name,value    ║ FK_47   ║       9 ║ characters1_.item_id ║    9 ║ Using where; Distinct                                               ║
║  1 ║ SIMPLE      ║ characters8_  ║ ref    ║ FK_47,name,value    ║ FK_47   ║       9 ║ characters1_.item_id ║    9 ║ Using where; Distinct                                               ║
║  1 ║ SIMPLE      ║ characters6_  ║ ref    ║ FK_47,name,value    ║ FK_47   ║       9 ║ characters1_.item_id ║    9 ║ Using where; Distinct                                               ║
║  1 ║ SIMPLE      ║ characters4_  ║ ref    ║ FK_47,name,value    ║ FK_47   ║       9 ║ characters1_.item_id ║    9 ║ Using where; Distinct                                               ║
╚════╩═════════════╩═══════════════╩════════╩═════════════════════╩═════════╩═════════╩══════════════════════╩══════╩═════════════════════════════════════════════════════════════════════╝

一些想法

我认为这个问题与group by和sort by有关。 但是与按位情况相同，当从查询中删除group by和sort by时运行时间仍然可以在6秒以上!

你知道造成蹩脚表现的原因是什么吗？

谢谢，橡木

附注

注意:这不可能是缓存问题，因为第一次运行可能非常快，只有几次运行后，查询的性能就会非常糟糕。

Answer 1

kv表中不需要id；您确实需要PRIMARY KEY(term_id, key)。

InnoDB 会是一个额外的好处；那么每个 term_id 的键值将“聚集”在一起。