mysql - MySQL中简单的重度索引表慢查询

Question

我遇到了特定查询速度缓慢的问题。尽管所有内容都有大量索引，一些类似的查询工作正常并且使用了索引，但查询仍然慢得要命。我不明白为什么，所以也许任何人都可以提供帮助。

仅针对先决条件:基础表的写入速度并不重要。该表包含大约 350 万个条目，但我想 MySQL 应该可以很好地处理这个问题。

缓慢的查询大约需要 2 秒

 SELECT DISTINCT t.`tag_3`  FROM `image_tags` t
     WHERE  t.`type` = 1 AND t.`category` LIKE "00%" AND tag_1 = "0"

--- DESCRIBE OUTPUT
--- The used index thirdtag is just an index defined as (type, category, tag_1, tag_3)
--- The actual result is 201 rows
+----+-------------+-------+- -----------------------+----------+---------+------+---------+-------------------------------------------+
| id | select_type | table | type  | possible_keys   | key      | key_len | ref  | rows    | Extra                                     |
+----+-------------+-------+-------+-----------------+----------+---------+------+---------+-------------------------------------------+
|  1 | SIMPLE      | t     | range | [... A LOT ...] | thirdtag | 31      | NULL | 1652861 | Using where; Using index; Using temporary |
+----+-------------+-------+-------+-----------------+----------+---------+------+---------+-------------------------------------------+

唯一突出的是涉及的大量行。如果你与我在这个问题末尾附加的两个快速查询进行比较，它实际上是唯一不同的地方(至少与第一个不同)。所以很可能这就是问题所在。但这就是数据提供给我的方式，所以我需要处理它。我想如果涉及到索引mysql就可以很好的处理数据了。

有人对如何优化查询有建议吗？如果我可以使用更适合查询的不同索引，有什么建议吗？

为了比较，这两个类似的查询运行速度非常快

 --- just a longer category string resulting in fewer results
 SELECT DISTINCT t.`tag_3`  FROM `image_tags` t
     WHERE  t.`type` = 1 AND t.`category` LIKE "0000%" AND tag_1 = "0"

 --- and additional where clause
 SELECT DISTINCT t.`tag_3`  FROM `image_tags` t
     WHERE  t.`type` = 1 AND t.`category` LIKE "00%" AND tag_1 = "0" and tag_2 = ""

表格(它有很多索引，可能太长而无法粘贴)。

+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| image    | char(8)          | NO   | MUL | NULL    |                |
| category | varchar(6)       | YES  | MUL | NULL    |                |
| type     | tinyint(1)       | NO   | MUL | NULL    |                |
| tag_1    | char(3)          | NO   | MUL | NULL    |                |
| tag_2    | char(3)          | NO   | MUL | NULL    |                |
| tag_3    | char(3)          | NO   | MUL | NULL    |                |
| tag_4    | char(3)          | NO   | MUL | NULL    |                |
| tag_5    | char(3)          | NO   | MUL | NULL    |                |
| tag_6    | char(3)          | NO   | MUL | NULL    |                |
+----------+------------------+------+-----+---------+----------------+

Answer 1

请提供SHOW CREATE TABLE，它比DESCRIBE更具描述性!特别是，我看不到你有哪些索引。

如My index cookbook解释说，以任何“=”字段开始索引，然后您就有机会添加“范围”比较。您的类别是一个范围，因此

WHERE  t.`type` = 1 AND t.`category` LIKE "00%" AND tag_1 = "0"

未超过

中的 类别

INDEX(type, category, tag_1, tag_3)

对于您的 3 个查询，以下是最佳索引:

INDEX(type, tag_1, category)
INDEX(type, tag_1, category)
INDEX(type, tag_1, tag_2, category)

category 应该放在最后；其他列可以按任何顺序排列。也许您的某个索引处理了第三种情况？

it has a lot of indexes probably too long to paste

可能大多数都未使用。请记住，如果您还有 INDEX(a,b)，则不需要 INDEX(a)。