理解MySQL Explain输出

Question

我有几个关于MySQL explain的问题。

1. 在评估的第一步中，它使用了REF进行连接类型。然而，根据我对ref的研究，它指出：“对于来自前面的表的每一行组合，都将从此表中读取具有匹配索引值的所有行。”这个“前面的表”是什么？如果它是初始步骤，那么如何会有前面的表呢？
2. 我在S.E上创建了一个索引，为什么在Extra列中它显示为“Using where”而不是“Using Index”？它明确指出通过查看“KEY column:SE”来使用索引。
3. 在操作顺序方面，MySQL是否按照以下顺序处理所有过程？S.E = 5使用索引，R.Rid =使用前一步的记录中的S.Rid，R.B = 5使用前一步的记录？

S.E上的索引

mysql> Create index SE on S(E);
Query OK, 0 rows affected (1.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain SELECT COUNT(R.RID) FROM R, S WHERE R.RID=S.RID AND R.B=5 AND S.E=5;
+----+-------------+-------+--------+---------------+---------+---------+----------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref            | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+----------------+------+-------------+
|  1 | SIMPLE      | S     | ref    | RID,SE        | SE      | 5       | const          |    6 | Using where |
|  1 | SIMPLE      | R     | eq_ref | PRIMARY       | PRIMARY | 4       | project2.S.RID |    1 | Using where |
+----+-------------+-------+--------+---------------+---------+---------+----------------+------+-------------+

数据集

• 表R有100,000行，包含以下字段：

  • RID（主键）：一个从1到100,000的整数
  • B：一个从1到10的随机均匀分布的整数
  • C：一个从1到1,000的随机均匀分布的整数

• 表S有500,000行，包含以下字段：

  • SID（主键）：一个从1到500,000的整数
  • RID（外键关联R）：一个从1到100,000的随机均匀分布的整数
  • D：一个从1到100的随机均匀分布的整数
  • E：一个从1到100,000的随机均匀分布的整数

查询

SELECT COUNT(R.RID) FROM R, S WHERE R.RID=S.RID AND R.B=5 AND S.E=5;

- Sam

---

请将模式以文本格式返回。 - Drew

这里找到第二个问题的答案：https://dev59.com/Zmkw5IYBdhLWcg3w9vN-#9534935 - Martin Seitl

为什么这个问题被踩了？楼主已经很好地尝试了提问。 - zedfoxus

Answer 1

这个查询也可以这样重写：

 SELECT COUNT(R.RID) FROM R, S WHERE R.RID=S.RID AND R.B=5 AND S.E=5;

 to

 SELECT COUNT(R.RID) 
 FROM R
 INNER JOIN S ON R.RID=S.RID
 WHERE R.B=5 AND S.E=5;

1. 什么是前一个表

在http://dev.mysql.com/doc/refman/5.7/en/explain-output.html上，关于ref和eq_ref的措辞可能有些令人困惑。这里有两个表格，每个表格都将另一个表格称为前一个表格（这是我的理解）。这里的连接是inner join，所以数据库会将所有R记录与S匹配，并仅考虑那些RID匹配的记录进行审核。

当文档同时针对eq_ref和ref使用相同的示例时，也会引起混淆：

SELECT * FROM ref_table,other_table 
WHERE ref_table.key_column=other_table.column;

总的来说，当解释提到ref和eq_ref时，我会查看各自的表格以了解它们所在的连接类型。eq_ref查看PRIMARY或UNIQUE键。 ref可能使用除PRIMARY/UNIQUE之外的索引。

2. 使用where

使用where实际上是使用索引，如explain的输出中的key列所示。索引用于满足where ... S.E = 5，然后还用于在表中查找数据。

如果索引覆盖并且不需要查找表中的数据，则可以看到using index（如果没有使用where条件）或using index; using where（如果使用where条件）

此信息与Martin Seitl在评论中提供的链接相同

3. 事件顺序

我理解的是：

• MySQL首先查找S.E索引以获取常量值where ... S.E = 5。
• 然后在S表中查找S.RID
• 然后将其已发现的记录与R.RID匹配
• 最合适的方法是访问R的主键，即RID（因此R具有eq_ref）
• 由于R.RID主键实际上是整行，因此可以轻松满足R.B = 5。因此不需要进行其他工作

- zedfoxus