MySQL select 查询在 BOTH where 和 descending order 下变得很慢

Question

我有这个选择查询，ItemType 是 varchar 类型，ItemComments 是 int 类型:

select * from ItemInfo where ItemType="item_type" order by ItemComments desc limit 1 

可以看到这个查询有3个条件:

1. 其中“ItemType”等于特定值；
2. 按“ItemComments”排序
3. 降序

有趣的是，当我选择具有所有三个条件的行时，它变得非常慢。但是如果我放弃这三个中的任何一个(条件 2 除外)，查询运行得非常快。见:

select * from ItemInfo where ItemType="item_type" order by ItemComments desc limit 1;
/* Affected rows: 0  Found rows: 1  Warnings: 0  Duration for 1 query: 16.318 sec. */

select * from ItemInfo where ItemType="item_type" order by ItemComments limit 1;
/* Affected rows: 0  Found rows: 1  Warnings: 0  Duration for 1 query: 0.140 sec. */

select * from ItemInfo order by ItemComments desc limit 1;
/* Affected rows: 0  Found rows: 1  Warnings: 0  Duration for 1 query: 0.015 sec. */

另外，

1. 我正在使用带有 InnoDB 引擎的 MySQL 5.7。
2. 我已经在 ItemType 和 ItemComments 上创建了索引，表 ItemInfo 包含 200 万行。

我搜索了很多可能的解释，比如MySQL支持降序索引、复合索引等等。但是这些仍然不能解释为什么查询 #1 运行缓慢而查询 #2 和 #3 运行良好。

如果有人能帮助我，我将不胜感激。

更新:创建表和解释信息

创建代码:

CREATE TABLE `ItemInfo` (
`ItemID` VARCHAR(255) NOT NULL,
`ItemType` VARCHAR(255) NOT NULL,
`ItemPics` VARCHAR(255) NULL DEFAULT '0',
`ItemName` VARCHAR(255) NULL DEFAULT '0',
`ItemComments` INT(50) NULL DEFAULT '0',
`ItemScore` DECIMAL(10,1) NULL DEFAULT '0.0',
`ItemPrice` DECIMAL(20,2) NULL DEFAULT '0.00',
`ItemDate` DATETIME NULL DEFAULT '1971-01-01 00:00:00',
PRIMARY KEY (`ItemID`, `ItemType`),
INDEX `ItemDate` (`ItemDate`),
INDEX `ItemComments` (`ItemComments`),
INDEX `ItemType` (`ItemType`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

解释结果:

mysql> explain select * from ItemInfo where ItemType="item_type" order by ItemComments desc limit 1;
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | i     | NULL       | index | ItemType      | ItemComments | 5       | NULL |   83 |     1.20 | Using where |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+

mysql> explain select * from ItemInfo where ItemType="item_type" order by ItemComments limit 1;
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | i     | NULL       | index | ItemType      | ItemComments | 5       | NULL |   83 |     1.20 | Using where |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+

mysql> explain select * from ItemInfo order by ItemComments desc limit 1;
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-------+
|  1 | SIMPLE      | i     | NULL       | index | NULL          | ItemComments | 5       | NULL |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-------+

来自 O. Jones 的查询:

mysql> explain
    ->  SELECT a.*
    ->      FROM ItemInfo a
    ->      JOIN (
    ->             SELECT MAX(ItemComments) ItemComments, ItemType
    ->               FROM ItemInfo
    ->              GROUP BY ItemType
    ->           ) maxcomm ON a.ItemType = maxcomm.ItemType
    ->                    AND a.ItemComments = maxcomm.ItemComments
    ->     WHERE a.ItemType = 'item_type';
+----+-------------+------------+------------+-------+----------------------------------------+-------------+---------+---------------------------+---------+----------+--------------------------+
| id | select_type | table      | partitions | type  | possible_keys                          | key         | key_len | ref                       | rows    | filtered | Extra                    |
+----+-------------+------------+------------+-------+----------------------------------------+-------------+---------+---------------------------+---------+----------+--------------------------+
|  1 | PRIMARY     | a          | NULL       | ref   | ItemComments,ItemType                  | ItemType    | 767     | const                     |   27378 |   100.00 | Using where              |
|  1 | PRIMARY     | <derived2> | NULL       | ref   | <auto_key0>                            | <auto_key0> | 772     | mydb.a.ItemComments,const |      10 |   100.00 | Using where; Using index |
|  2 | DERIVED     | ItemInfo   | NULL       | index | PRIMARY,ItemDate,ItemComments,ItemType | ItemType    | 767     | NULL                      | 2289466 |   100.00 | NULL                     |
+----+-------------+------------+------------+-------+----------------------------------------+-------------+---------+---------------------------+---------+----------+--------------------------+

我不确定我是否正确执行了这个查询，但我在相当长的时间内无法获取记录。

来自 Vijay 的查询。但是我添加了 ItemType 连接条件，因为只有 max_comnt 从其他 ItemType 返回项目:

SELECT ifo.* FROM ItemInfo ifo 
JOIN (SELECT ItemType, MAX(ItemComments) AS max_comnt FROM ItemInfo WHERE ItemType="item_type") inn_ifo 
ON ifo.ItemComments = inn_ifo.max_comnt and ifo.ItemType = inn_ifo.ItemType
/* Affected rows: 0  Found rows: 1  Warnings: 0  Duration for 1 query: 7.441 sec. */

explain result:
+----+-------------+------------+------------+-------------+-----------------------+-----------------------+---------+-------+-------+----------+-----------------------------------------------------+
| id | select_type | table      | partitions | type        | possible_keys         | key                   | key_len | ref   | rows  | filtered | Extra                                               |
+----+-------------+------------+------------+-------------+-----------------------+-----------------------+---------+-------+-------+----------+-----------------------------------------------------+
|  1 | PRIMARY     | <derived2> | NULL       | system      | NULL                  | NULL                  | NULL    | NULL  |     1 |   100.00 | NULL                                                |
|  1 | PRIMARY     | ifo        | NULL       | index_merge | ItemComments,ItemType | ItemComments,ItemType | 5,767   | NULL  |    88 |   100.00 | Using intersect(ItemComments,ItemType); Using where |
|  2 | DERIVED     | ItemInfo   | NULL       | ref         | ItemType              | ItemType              | 767     | const | 27378 |   100.00 | NULL                                                |
+----+-------------+------------+------------+-------------+-----------------------+-----------------------+---------+-------+-------+----------+-----------------------------------------------------+

我想解释一下为什么我首先使用 order with limit:我计划以特定的概率从表中随机获取记录。从 python 生成的随机索引并作为变量发送到 MySQL。但后来我发现它花费了太多时间，所以我决定只使用我得到的第一条记录。

在O. Jones和Vijay的启发下，我尝试使用max函数，但效果不佳:

select max(ItemComments) from ItemInfo where ItemType='item_type'
/* Affected rows: 0  Found rows: 1  Warnings: 0  Duration for 1 query: 6.225 sec. */

explain result:
+----+-------------+------------+------------+------+---------------+----------+---------+-------+-------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key      | key_len | ref   | rows  | filtered | Extra |
+----+-------------+------------+------------+------+---------------+----------+---------+-------+-------+----------+-------+
|  1 | SIMPLE      | ItemInfo   | NULL       | ref  | ItemType      | ItemType | 767     | const | 27378 |   100.00 | NULL  |
+----+-------------+------------+------------+------+---------------+----------+---------+-------+-------+----------+-------+

感谢大家对这个问题的贡献。希望大家能根据以上信息提出更多的解决方案。

Answer 1

请提供当前的 SHOW CREATE TABLE ItemInfo。

对于大多数这些查询，您需要复合索引

INDEX(ItemType, ItemComments)

对于最后一个，你需要

INDEX(ItemComments)

对于特别慢的查询，请提供EXPLAIN SELECT ...。

讨论 - 为什么 INDEX(ItemType, ItemComments) 有助于where ItemType="item_type"order by ItemComments desc limit 1？

索引在 BTree 中构建(参见维基百科)，因此可以非常快速地搜索单个项目，并且可以非常快速地按特定顺序进行扫描。

ItemType="item_type" 表示要根据 ItemType 进行过滤，但索引中有很多这样的类型。在此索引中，它们按 ItemComments 排序(对于给定的 ItemType)。 desc方向建议从ItemContents的最大值开始；那是索引项的“结尾”。最后 limit 1 表示找到一项后停止。 (有点像在你的 Rolodex 中找到最后一个“S”。)

所以查询是将 BTree“向下钻取”到复合 INDEX(ItemType, ItemContents) 中 ItemType 条目的末尾并获取一个条目 - - 一项非常高效的任务。

实际上 SELECT * 意味着还有一个步骤，即获取该行的所有列。该信息不在索引中，而是在 ItemInfo 的 BTree 中——它包含所有行的所有列，按 PRIMARY KEY 排序。

“二级索引”(INDEX(ItemType, ItemComments)) 隐式包含相关 PRIMARY KEY 列的副本，所以我们现在有 的值>ItemID 和 ItemType。有了这些，我们可以向下钻取另一个 BTree 以找到所需的行并获取所有 (*) 列。