理解mysql说明 [英] understanding mysql explain
问题描述
所以，我从来没有理解MySQL的解释。我理解总概念，您应该在 possible_keys 列中至少有一个条目，以便使用索引，而简单的查询更好。但ref和eq_ref有什么区别？什么是优化查询的最佳方式。
例如，这是我最新的查询，我试图找出为什么它需要永远（从 django 模型）：
+ ---- + ------------- + -------------------- + -------- + -------------------- --------------------------------------- + ---------- ----------------------- + --------- + ---------------- ---------------------- + ------ + -------------------- ------------- +
| id | select_type |表|类型| possible_keys |键| key_len | ref |行|额外|
+ ---- + ------------- + --------------------- + ---- ---- + --------------------------------------------- -------------- + --------------------------------- + - -------- + -------------------------------------- + - ---- + --------------------------------- +
| 1 | SIMPLE | T6 | ref | yourock_achiever_alias_id | yourock_achiever_alias_id | 4 | const | 244 |使用临时;使用filesort |
| 1 | SIMPLE | T5 | eq_ref | PRIMARY | PRIMARY | 4 | paul.T6.achievement_id | 1 |使用索引|
| 1 | SIMPLE | T4 | ref | yourock_achiever_alias_id | yourock_achiever_achievement_id | 4 | paul.T6.achievement_id | 298 | |
| 1 | SIMPLE | yourock_alias | eq_ref | PRIMARY | PRIMARY | 4 | paul.T4.alias_id | 1 |使用索引|
| 1 | SIMPLE | yourock_achiever | ref | yourock_achiever_alias_id | yourock_achiever_alias_id | 4 | paul.T4.alias_id | 152 | |
| 1 | SIMPLE | yourock_achievement | eq_ref | PRIMARY | PRIMARY | 4 | paul.yourock_achiever.achievement_id | 1 | |
+ ---- + ------------- + --------------------- + ---- ---- + --------------------------------------------- -------------- + --------------------------------- + - -------- + -------------------------------------- + - ---- + --------------------------------- +
集合中的6行（0.00秒）
我希望足够了解mysql，解释不需要查询。唉，似乎你不能从explain语句获得足够的信息，你需要原始SQL。查询：
选择`yourock_achievement`.`id`，
`yourock_achievement`.`modified`，
`yourock_achievement`.`created`，
`yourock_achievement`.`string_id`，
`yourock_achievement`.`owner_id`，
`yourock_achievement`.`name`，
` yourock_achievement`.`description`，
`yourock_achievement`.`owner_points`，
`yourock_achievement`.`url`，
`yourock_achievement`.`remote_image`，
`yourock_achievement` .`image`，
`yourock_achievement`.`parent_achievement_id`，
`yourock_achievement`.`slug`，
`yourock_achievement`.`true_points`
从`yourock_achievement`
INNER JOIN
`yourock_achiever`
ON`yourock_achievement`.`id` =`yourock_achiever`.`achievement_id`
INNER JOIN
`yourock_alias`
ON` yourock_achiever`.`asilas_id` =`yourock_alias`.````
INNER JOIN
`yourock_achiever` T4
ON`yourock_alias`.`id` = T4.`alias_id`
INNER JOIN
`yourock_achievement` T5
ON T4.`achievement_id` = T5.`id`
INNER JOIN
`yourock_achiever` T6
ON T5.`id` = T6.`achievement_id`
WHERE
T6.`alias_id` = 6
ORDER BY
`yourock_achievement`.`modified` DESC
解决方案Paul：
eq_ref
从上表中的每个行组合，从此表中读取一行。 除了系统和const类型之外，这是最好的连接类型。当连接使用索引的所有部分并且索引是PRIMARY KEY或UNIQUE索引时使用。
eq_ref可用于使用=运算符比较的索引列。比较值可以是常量或使用在此表之前读取的表中的列的表达式。在以下示例中，MySQL可以使用eq_ref连接来处理ref_table：
SELECT * FROM ref_table，other_table
WHERE ref_table.key_column = other_table.column;
SELECT * FROM ref_table，other_table
WHERE ref_table.key_column_part1 = other_table.column
AND ref_table.key_column_part2 = 1;
ref
$ b对于上表中的每个行组合，从此表中读取具有匹配索引值的所有行。如果连接仅使用键的最左前缀，或者键不是PRIMARY KEY或UNIQUE索引（换句话说，如果连接不能根据键值选择单个行），则使用 ref <强>。如果使用的键只匹配几行，这是一个很好的连接类型。
ref可以用于使用=或< =>运算符。在以下示例中，MySQL可以使用ref连接来处理ref_table：
SELECT * FROM ref_table WHERE key_column = expr;
SELECT * FROM ref_table，other_table
WHERE ref_table.key_column = other_table.column;
SELECT * FROM ref_table，other_table
WHERE ref_table.key_column_part1 = other_table.column
AND ref_table.key_column_part2 = 1;
这些是从MySQL手册中逐字复制的： http://dev.mysql.com/doc/refman/5.0/en/using-explain.html
如果您可以发布永远采用 的查询，我可以帮助确定是什么使其减速。此外，请指定您对永远的定义。另外，如果你可以提供你的SHOW CREATE TABLE xxx;这些表的语句，我可以帮助尽可能优化您的查询。
立即跳出来的可能改进点是使用临时;使用filesort;。这意味着创建了一个临时表以满足查询（不一定是坏事），并且无法从索引检索您指定的GROUP BY / ORDER BY，从而产生一个 filesort 。
So, I've never understood the explain of MySQL. I understand the gross concepts that you should have at least one entry in the
possible_keyscolumn for it to use an index, and that simple queries are better. But what is the difference between ref and eq_ref? What is the best way to be optimizing queries.For example, this is my latest query that I'm trying to figure out why it takes forever (generated from
djangomodels) :+----+-------------+---------------------+--------+-----------------------------------------------------------+---------------------------------+---------+--------------------------------------+------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------------------+--------+-----------------------------------------------------------+---------------------------------+---------+--------------------------------------+------+---------------------------------+ | 1 | SIMPLE | T6 | ref | yourock_achiever_achievement_id,yourock_achiever_alias_id | yourock_achiever_alias_id | 4 | const | 244 | Using temporary; Using filesort | | 1 | SIMPLE | T5 | eq_ref | PRIMARY | PRIMARY | 4 | paul.T6.achievement_id | 1 | Using index | | 1 | SIMPLE | T4 | ref | yourock_achiever_achievement_id,yourock_achiever_alias_id | yourock_achiever_achievement_id | 4 | paul.T6.achievement_id | 298 | | | 1 | SIMPLE | yourock_alias | eq_ref | PRIMARY | PRIMARY | 4 | paul.T4.alias_id | 1 | Using index | | 1 | SIMPLE | yourock_achiever | ref | yourock_achiever_achievement_id,yourock_achiever_alias_id | yourock_achiever_alias_id | 4 | paul.T4.alias_id | 152 | | | 1 | SIMPLE | yourock_achievement | eq_ref | PRIMARY | PRIMARY | 4 | paul.yourock_achiever.achievement_id | 1 | | +----+-------------+---------------------+--------+-----------------------------------------------------------+---------------------------------+---------+--------------------------------------+------+---------------------------------+ 6 rows in set (0.00 sec)I had hoped to learn enough about mysql explain that the query wouldn't be needed. Alas, it seems that you can't get enough information from the explain statement and you need the raw SQL. Query :
SELECT `yourock_achievement`.`id`, `yourock_achievement`.`modified`, `yourock_achievement`.`created`, `yourock_achievement`.`string_id`, `yourock_achievement`.`owner_id`, `yourock_achievement`.`name`, `yourock_achievement`.`description`, `yourock_achievement`.`owner_points`, `yourock_achievement`.`url`, `yourock_achievement`.`remote_image`, `yourock_achievement`.`image`, `yourock_achievement`.`parent_achievement_id`, `yourock_achievement`.`slug`, `yourock_achievement`.`true_points` FROM `yourock_achievement` INNER JOIN `yourock_achiever` ON `yourock_achievement`.`id` = `yourock_achiever`.`achievement_id` INNER JOIN `yourock_alias` ON `yourock_achiever`.`alias_id` = `yourock_alias`.`id` INNER JOIN `yourock_achiever` T4 ON `yourock_alias`.`id` = T4.`alias_id` INNER JOIN `yourock_achievement` T5 ON T4.`achievement_id` = T5.`id` INNER JOIN `yourock_achiever` T6 ON T5.`id` = T6.`achievement_id` WHERE T6.`alias_id` = 6 ORDER BY `yourock_achievement`.`modified` DESC
解决方案Paul:
eq_ref
One row is read from this table for each combination of rows from the previous tables. Other than the system and const types, this is the best possible join type. It is used when all parts of an index are used by the join and the index is a PRIMARY KEY or UNIQUE index.
eq_ref can be used for indexed columns that are compared using the = operator. The comparison value can be a constant or an expression that uses columns from tables that are read before this table. In the following examples, MySQL can use an eq_ref join to process ref_table:
SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column; SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1=other_table.column AND ref_table.key_column_part2=1;
ref
All rows with matching index values are read from this table for each combination of rows from the previous tables. ref is used if the join uses only a leftmost prefix of the key or if the key is not a PRIMARY KEY or UNIQUE index (in other words, if the join cannot select a single row based on the key value). If the key that is used matches only a few rows, this is a good join type.
ref can be used for indexed columns that are compared using the = or <=> operator. In the following examples, MySQL can use a ref join to process ref_table:
SELECT * FROM ref_table WHERE key_column=expr; SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column; SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1=other_table.column AND ref_table.key_column_part2=1;These are copied verbatim from the MySQL manual: http://dev.mysql.com/doc/refman/5.0/en/using-explain.html
If you could post your query that is taking forever, I could help pinpoint what is slowing it down. Also, please specify what your definition of forever is. Also, if you could provide your "SHOW CREATE TABLE xxx;" statements for these tables, I could help in optimizing your query as much as possible.
What jumps out at me immediately as a possible point of improvement is the "Using temporary; Using filesort;". This means that a temporary table was created to satisfy the query (not necessarily a bad thing), and that the GROUP BY/ORDER BY you designated could not be retrieved from an index, thus resulting in a filesort.
这篇关于理解mysql说明的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
