避免在UNION结果上使用"filesort“

Question

子查询1：

SELECT * from big_table
where category = 'fruits' and name = 'apple'
order by yyyymmdd desc

解释：

table       |   key           |   extra
big_table   |   name_yyyymmdd |   using where

真不错!

子查询2：

SELECT * from big_table
where category = 'fruits' and (taste = 'sweet' or wildcard = '*')
order by yyyymmdd desc

解释：

table       |   key               |   extra
big_table   |   category_yyyymmdd |   using where

真不错!

如果我把这些和UNION结合起来：

SELECT * from big_table
where category = 'fruits' and name = 'apple'

UNION

SELECT * from big_table
where category = 'fruits' and (taste = 'sweet' or wildcard = '*')

Order by yyyymmdd desc

解释：

table       |   key      |   extra
big_table   |   name     |   using index condition, using where
big_table   |   category |   using index condition
UNION RESULT|   NULL     |   using temporary; using filesort

不太好，它使用文件。

这是一个更复杂的查询的精简版本，下面是有关big_table的一些事实：

• big_table有10M +行
• 有五个独特的“类别”。
• 有五个独特的“口味”。
• 大约有10,000个独特的名字。
• 有大约10,000个独特的“yyyymmdd”
• 我已经在每个字段上创建了单个索引，另外还创建了复合索引(如yyyymmdd_category_taste_name )，但是Mysql没有使用它。

Answer 1

SELECT * FROM big_table
    WHERE category = 'fruits'
      AND (  name = 'apple'
          OR taste = 'sweet'
          OR wildcard = '*' )
    ORDER BY yyyymmdd DESC

并让INDEX(catgory)或一些以category开头的索引。但是，如果超过20%的表是category = 'fruits'，那么很可能会决定忽略索引，只做一次表扫描。(既然你说只有5类，我怀疑优化器会正确地避开索引。)

或者这可能是有益的：INDEX(category, yyyymmdd)，按这个顺序。

UNION必须进行排序(无论是在磁盘上的内存中，还是在不清楚的内存中)，因为它无法按所需的顺序获取行。

可以使用复合索引INDEX(yyyymmdd, ...)来避免“filesort”，但它不会在yyyymmdd之后使用任何列。

在构造复合索引时，从比较'=‘的任何WHERE列开始。之后，您可以添加一个范围或group by或order by。更多细节。

UNION通常是避免缓慢的OR的一个很好的选择，但在这种情况下，它需要三个索引

INDEX(category, name)
INDEX(category, taste)
INDEX(category, wildcard)

添加yyyymmdd将不会有帮助，除非您添加了一个LIMIT。

查询内容如下：

( SELECT * FROM big_table WHERE category = 'fruits' AND name = 'apple' )
UNION DISTINCT
( SELECT * FROM big_table WHERE category = 'fruits' AND taste = 'sweet' )
UNION DISTINCT
( SELECT * FROM big_table WHERE category = 'fruits' AND wildcard = '*' )
ORDER BY yyyymmdd DESC

增加一个限制会更加混乱。首先在三个复合索引的末尾插入yyyymmdd，然后

( SELECT ... ORDER BY yyyymmdd DESC LIMIT 10 )
UNION DISTINCT
( SELECT ... ORDER BY yyyymmdd DESC LIMIT 10 )
UNION DISTINCT
( SELECT ... ORDER BY yyyymmdd DESC LIMIT 10 )
ORDER BY yyyymmdd DESC  LIMIT 10

增加一个抵消会更糟。

另外两种技术--“覆盖”索引和“懒散查找”--可能会有所帮助，但我对此表示怀疑。

另一种技术是将所有单词放在同一列中，并使用FULLTEXT索引。但这可能是有问题的，原因有几个。

Answer 2

这也必须在没有工会的情况下进行

SELECT * from big_table
where 
    ( category = 'fruits' and name = 'apple' )
    OR
    ( category = 'fruits' and (taste = 'sweet' or wildcard = '*')
ORDER BY yyyymmdd desc;