mysql - 主索引与索引的查询性能

Question

我在 mysql 上有一个表和两个性能完全不同的查询。我已经提取了查询计划，但我无法完全理解性能差异背后的原因。

表格:

+-------------+----------------------------------------------+------------------------------------+
|   TableA    |                                              |                                    |
+-------------+----------------------------------------------+------------------------------------+
| id          | int(10)   unsigned NOT NULL AUTO_INCREMENT   |                                    |
| userId      | int(10)                                      | unsigned DEFAULT NULL              |
| created     | timestamp                                    | NOT NULL DEFAULT CURRENT_TIMESTAMP |
| PRIMARY KEY | id                                           |                                    |
| KEY userId  | userId                                       |                                    |
| KEY created | created                                      |                                    |
+-------------+----------------------------------------------+------------------------------------+

Keys/Indices: id 字段的主键，userId 字段的键 ASC, created 字段 ASC 上的另一个键。

tableA 是一个非常大的表，它包含数百万行。

我在这个表上运行的查询是:

ID 为 1234 的用户在此表中有 150 万条记录。我想获取其最新的 100 行。为了实现这一点，我有 2 个不同的查询:

查询 1:

SELECT * FROM tableA USE INDEX (userId) 
WHERE userId=1234 ORDER BY created DESC LIMIT 100;

查询 2:

SELECT * FROM tableA 
WHERE userId=1234 ORDER BY id DESC LIMIT 100;

由于 tableA 的 id 字段是自增的，因此保留最新的条件。这 2 个查询返回相同的结果。但是，存在巨大的性能差异。

查询计划是:

+----------+-----------------------------------------------+-------------------------------+------+---------------------------------------+
| Query No |                   Operation                   |            Params             | Raws |               Raw desc                |
+----------+-----------------------------------------------+-------------------------------+------+---------------------------------------+
| Query 1  | Sort(using file sort) Unique index scan (ref) | table: tableA; index: userId; | 2.5M | Using index condition; Using filesort |
| Query 2  | Unique index scan (ref)                       | table: tableA; index: userId; | 2.5M | Using where                           |
+----------+-----------------------------------------------+-------------------------------+------+---------------------------------------+


+--------+-------------+
|        | Performance |
+--------+-------------+
| Query1 | 7,5 s       |
+--------+-------------+
| Query2 | 741 ms      |
+--------+-------------+

我了解到查询1有一个排序操作。在每个查询中，使用的索引是userId。但是为什么查询 2 中没有使用排序呢？主索引如何影响？

MySQL 5.7

编辑:表中还有更多列，我从上面的表定义中提取了它们。

Answer 1

Since id field of tableA is auto increment, the condition of being latest is preserved.

这通常是一个有效的陈述。

WHERE userId=1234 ORDER BY created DESC LIMIT 100

需要这个“复合”索引:(userId, created)。这样一来，无论表大小或该用户的行数如何，它都只会命中 100 行。

同理

WHERE userId=1234 ORDER BY id DESC LIMIT 100;

即它需要(userId, id)。但是，在 InnoDB 中，当您说 INDEX(x) 时，它会默默地添加到 PRIMARY KEY 列。所以你有效地得到了 INDEX(x,id)。这就是您的普通 INDEX(userId) 运行良好的原因。

EXPLAIN 很少(如果有的话)考虑 LIMIT。这就是为什么两个查询的“行数”都是“250 万”。

如果您去掉 USE INDEX 提示，第一个查询可能(或可能不会)使用 INDEX(userId)。选择取决于表中 userId = 1234 的百分比。如果它小于大约 20%，将使用该索引。但它会在二级索引和数据之间来回跳动——总共 150 万次。如果超过 20%，它将通过简单地读取所有“数百万”行并忽略那些不适用的行来避免弹跳。

注意:您为 Q1 所做的仍然会读取至少 150 万行，对它们进行排序(“使用文件排序”)，然后剥离所需的 100 行。但是使用 INDEX(userId, created) ，它可以跳过排序，只查看 100 行。

如果没有看到 SHOW CREATE TABLE 和未注释的 EXPLAIN，我无法解释“唯一索引扫描”。 (EXPLAIN FORMAT=JSON SELECT... 可能会提供更多见解。)