使用filesort & tempory而不是索引的简单选择组

Question

所以我在网上搜索了一下，似乎找不到答案。我有一张具有以下结构的桌子

Table structure for table `search_tags`
--

CREATE TABLE IF NOT EXISTS `search_tags` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `LOOK_UP_TO_CAT_ID` int(11) NOT NULL,
  `SEARCH_TAG` text COLLATE utf8_unicode_520_ci NOT NULL,
  `DATE` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `SOURCE` varchar(225) COLLATE utf8_unicode_520_ci NOT NULL,
  `SOURCE_ID` int(11) NOT NULL,
  `WEIGHT` int(11) NOT NULL DEFAULT '1000',
  PRIMARY KEY (`ID`),
  KEY `LOOK_UP_TO_CAT_ID` (`LOOK_UP_TO_CAT_ID`),
  KEY `WEIGHT` (`WEIGHT`),
  FULLTEXT KEY `SEARCH_TAG` (`SEARCH_TAG`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_520_ci AUTO_INCREMENT=1 ;

该表与800000+行放在一起，并且正在增长。

当我在LOOK_UP_TO_CAT_ID上运行带有组by的查询时，该查询需要1-2秒时间才能运行。我需要使用连接到其他表运行这个基础的多个版本，但这似乎是瓶颈所在，因为向它添加联接不会减慢它的速度。

    SELECT LOOK_UP_TO_CAT_ID, WEIGHT
FROM  `search_tags` 
WHERE  `SEARCH_TAG` LIKE  '%metallica%'
GROUP BY  `LOOK_UP_TO_CAT_ID`

删除GROUP BY会将查询时间降到0.1，这似乎更容易接受，但随后我使用了重复的查询时间。

在group中使用explain显示它正在创建一个临时表，而不是使用索引

+----+-------------+-------------+------+-------------------+------+---------+------+--------+----------------------------------------------+--+
| id | select_type |    table    | type |   possible_keys   | key  | key_len | ref  |  rows  |                    Extra                     |  |
+----+-------------+-------------+------+-------------------+------+---------+------+--------+----------------------------------------------+--+
|  1 | SIMPLE      | search_tags | ALL  | LOOK_UP_TO_CAT_ID | NULL | NULL    | NULL | 825087 | Using where; Using temporary; Using filesort |  |
+----+-------------+-------------+------+-------------------+------+---------+------+--------+----------------------------------------------+--+

所以我不确定mysql在这里做的是不是对的，但至少对我来说，不使用索引似乎是错误的。什么是最好的，为什么要加快这个查询？

编辑：

以下是我的数据示例：

+----+-------------------+----------------------------------+------------+---------------+-----------+--------+
| ID | LOOK_UP_TO_CAT_ID |            SEARCH_TAG            |    DATE    |    SOURCE     | SOURCE_ID | WEIGHT |
+----+-------------------+----------------------------------+------------+---------------+-----------+--------+
|  1 |               521 | METALLICA                        | 2017-02-18 | artist        |        15 |      1 |
|  2 |               521 | METALLICA - NOTHING ELSE MATTERS | 2017-02-18 | tracklisting  |        22 |      2 |
|  3 |               522 | METALLICA                        | 2017-02-18 | artist        |        15 |      1 |
|  4 |               522 | METALLICA - ST. Anger            | 2017-02-18 | product_title |       522 |      2 |
+----+-------------------+----------------------------------+------------+---------------+-----------+--------+

期望结果

+-------------------+--------+
| LOOK_UP_TO_CAT_ID | WEIGHT |
+-------------------+--------+
|               521 |      1 |
|               522 |      1 |
+-------------------+--------+

Answer 1

给你一些建议。

1. 在这个不幸的世界里，SEARCH_TAG LIKE '%metallica%'永远不会使用索引。模式haystack like '%needle' (前导%)要求MySQL检查列中的每个值是否匹配。haystack LIKE 'needle%' (跟踪%)没有这个问题。
2. 在您的索引列中有SEARCH_TAG，所以使用它！WHERE MATCH('metallica') AGAINST SEARCH_TAG是您需要的WHERE子句的形式。
3. 您的表上有很多单列索引。这些通常无助于使查询速度更快，除非它们恰好与您所要做的完全匹配。您最好使用为正在运行的查询设计的复合覆盖指数。

您的问题中的示例查询是

   SELECT LOOK_UP_TO_CAT_ID, WEIGHT
    FROM  search_tags 
   WHERE  SEARCH_TAG LIKE  '%metallica%'
GROUP BY  LOOK_UP_TO_CAT_ID

如果您将其更改为此，它将具有更多的SQL意义，并运行得更快。

   SELECT  LOOK_UP_TO_CAT_ID, MAX(WEIGHT)
     FROM  search_tags 
    WHERE  SEARCH_TAG LIKE  'metallica%'
 GROUP BY  LOOK_UP_TO_CAT_ID

(请注意，我去掉了领先的%。)

如果在(SEARCH_TAG, LOOK_UP_CAT_ID, WEIGHT)上添加复合覆盖索引，此查询将变得相当快。整个查询可以从索引中得到满足。MySQL随机访问索引以查找SEARCH_TAG，然后执行松散索引扫描以获得所需的结果。

(顺便提一下:当您在filesort或ORDER BY查询中看到EXPLAIN输出时，不要担心。这是MySQL满足查询方式的一部分。filesort中的文件不一定意味着硬盘上的慢速文件。)

Answer 2

在某种程度上，你的问题没有意义。您有一个全文索引，但使用的是LIKE，它执行表扫描.您需要使用MATCH()来使用全文索引。

我真正认为正在发生的是正在返回的数据量很大。当您在不使用order by或group by的情况下执行查询时，结果将在生成时返回。您会看到结果，因为某些行被提前扫描与您的条件相匹配。

group by/order by需要读取所有结果。

您可以通过执行count(*)而不是select来检查这一点。

SELECT COUNT(*)
FROM `search_tags` 
WHERE `SEARCH_TAG` LIKE '%metallica%';

我想这可能需要更长时间。

通过使用关联子查询，可以消除重复消除的性能影响：

SELECT st.LOOK_UP_TO_CAT_ID, st.WEIGHT
FROM `search_tags` st
WHERE `SEARCH_TAG` LIKE  '%metallica%' AND
      st.id = (SELECT MIN(st2.id) FROM search_tags st2 WHERE st2.LOOK_UP_TO_CAT_ID = st.LOOK_UP_TO_CAT_ID);

这需要在search_tags(LOOK_UP_TO_CAT_ID, ID)上建立一个索引来提高性能。

但是，您可能希望使用MATCH()来利用全文索引。