理解MySQL解释输出 [英] understanding MySQL Explain output
问题描述
我有几个关于MySQL的问题解释。
- 在评估的第一步，它使用REF作为连接类型。但是，根据我对ref的研究，它说明如下：
从上表中的每个行的组合，从该表中读取具有匹配索引值的所有行。什么是上一个表？如果它的初始步骤，如何有一个前面的表？ - 我在SE上创建了一个索引，为什么它
使用where？在Extra列而不是使用索引？它特别声明它通过查看KEY列使用索引：SE - 就操作顺序而言，MySQL做这个顺序的一切吗？ S.E = 5使用索引，R.Rid = S.Rid使用上一步的记录，R.B = 5使用上一步的记录？
SE上的索引
mysql>在S（E）上创建索引SE;
查询OK，0行受影响（1.15秒）
记录：0重复：0警告：0
mysql>解释SELECT COUNT（R.RID）FROM R，S WHERE R.RID = S.RID AND R.B = 5 AND S.E = 5;
+ ---- + ------------- + ------- + -------- + --------- ------ + --------- + --------- + ---------------- + ------ + ------------- +
| id | select_type |表|类型| possible_keys |键| key_len | ref |行|额外|
+ ---- + ------------- + ------- + -------- + --------- ------ + --------- + --------- + ---------------- + ------ + ------------- +
| 1 | SIMPLE | S | ref | RID，SE | SE | 5 | const | 6 |使用where
| 1 | SIMPLE | R | eq_ref | PRIMARY | PRIMARY | 4 | project2.S.RID | 1 |使用where
+ ---- + ------------- + ------- + -------- + --------- ------ + --------- + --------- + ---------------- + ------ + ------------- +
DATASET
-
表R有100,000行和以下字段：
- RID（主键）：1到100,000之间的整数
- B：从1到10的随机均匀分布的整数
- C：a
-
表S有500,000行，其中包含以下字段：
- SID（主键）：1到500,000之间的整数
- RID从1到100,000的随机均匀分布的整数
- E：从1到100,000的随机均匀分布的整数
查询
SELECT COUNT（R.RID）FROM R，S WHERE R.RID = S.RID AND RB = 5 AND SE = 5;
解决方案此查询可以重写为：
SELECT COUNT（R.RID）FROM R，S WHERE R.RID = S.RID AND RB = 5 AND SE = 5;
to
SELECT COUNT（R.RID）
FROM R
内部加入S RID = S.RID
WHERE RB = 5 AND SE = 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的同一个示例时， / p>
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。使用
$ <$> explain 的输出。索引用于满足键其中... SE = 5，并且也用于查找表中的数据。
如果索引被覆盖并且表中的数据不需要查找，您可以看到
使用索引（如果没有使用条件）或使用索引;使用where（如果使用条件）
事件顺序
这是我的理解：
- MySQL首先查找SE索引的常量值
其中... SE = 5。 - 然后查找S.RID在表S中
- 然后将它到目前为止发现的记录与R.RID匹配
- 最合适的方法是到R的主键，这是RID（因此R具有eq_ref）
- 由于R.RID主键实际上是整行，RB = 5可以很容易地满足。因此，无需其他工作。
I have a couple of questions regarding MySQL explain.
- In the first step of the evaluation, it utilizes a REF, for join type. However, upon my research on ref it states the following:
All rows with matching index values are read from this table for each combination of rows from the previous tables.What is this Previous table? How can there be a previous table if its the initial step? - I created an index on S.E, why does it state
Using where?at the Extra column instead ofUsing Index? And it specifically states it uses the index by looking at theKEY column : SE - In terms of the order of operations, does MySQL do the process everything in this order? S.E = 5 using index, R.Rid = S.Rid using the records from the previous step, R.B = 5 using the records from the previous step?
INDEX on 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 | +----+-------------+-------+--------+---------------+---------+---------+----------------+------+-------------+DATASET
Table R has 100,000 rows and the following fields:
- RID (primary key): an integer from 1 to 100,000
- B: a random uniformly distributed integer from 1 to 10
- C: a random uniformly distributed integer from 1 to 1,000
Table S has 500,000 rows and the following fields:
- SID (primary key): an integer from 1 to 500,000
- RID (foreign key to R): a random uniformly distributed integer from 1 to 100,000
- D: a random uniformly distributed integer from 1 to 100
- E: a random uniformly distributed integer from 1 to 100,000
Query
SELECT COUNT(R.RID) FROM R, S WHERE R.RID=S.RID AND R.B=5 AND S.E=5;解决方案This query can be re-written like so also:
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. What is previous table
The verbiage on http://dev.mysql.com/doc/refman/5.7/en/explain-output.html for
refandeq_refcan be somewhat confusing. There are two tables here. Each one refers to the other as the previous table (this is my interpretation). The join here isinner join, so the database matches all R records with S and considers only those records suitable for review if RID matches.The confusion also arises when docs refer to the same example for both
eq_refandref:SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column;Overall, when explain mentions ref and eq_ref, I look at the respective tables to see what kind of join they are in.
eq_reflooks at PRIMARY or UNIQUE keys.refis likely to be using an index other than PRIMARY/UNIQUE.2. Using where
Using where is actually using index as indicated by
keycolumn ofexplain's output. Index is used to satisfywhere ... S.E = 5and is also then used to lookup data in the table.If the index was covering and data in the table didn't require to be looked up, you could see either
using index(if no where condition is used) orusing index; using where(if where condition is used)This information is identical to the link Martin Seitl provided in his comments
3. Order of events
It is my understanding that:
- MySQL is first looking up S.E index for a constant value
where ... S.E = 5. - It then looks up S.RID in table S
- It then matches up records it has so far discovered with R.RID
- The most suitable way to do that is to reach out to the primary key of R, which is RID (therefore R has eq_ref)
- Since R.RID primary key is actually the entire row, R.B = 5 can be satisfied easily. So no other work needs to be done
这篇关于理解MySQL解释输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
