理解mysql解释 [英] understanding mysql explain

问题描述

所以，我一直不明白 MySQL 的解释.我理解您应该在 possible_keys 列中至少有一个条目才能使用索引的粗略概念，并且简单查询更好.但是 ref 和 eq_ref 有什么区别呢?优化查询的最佳方法是什么.

例如，这是我最近的查询，我试图弄清楚为什么它需要永远(从 django 模型生成):

+----+-------------+------+--------+------------------------------------------------+---------------------------------+---------+--------------------------------------+-------+----------------------------------+|身份证 |选择类型 |表|类型 |可能的密钥|键 |key_len |参考 |行 |额外 |+----+-------------+------------+--------+-----------------------------------------------------------+----------------------------------+---------+--------------------------------------+------+---------------------------------+|1 |简单 |T6 |参考 |yourock_achiever_achievement_id,yourock_achiever_alias_id |yourock_achiever_alias_id |4 |常量 |第244话使用临时；使用文件排序 ||1 |简单 |T5 |eq_ref |主要 |主要 |4 |paul.T6.achievement_id |1 |使用索引 ||1 |简单 |T4 |参考 |yourock_achiever_achievement_id,yourock_achiever_alias_id |yourock_achiever_achievement_id |4 |paul.T6.achievement_id |第298话||1 |简单 |yourock_alias |eq_ref |主要 |主要 |4 |paul.T4.alias_id |1 |使用索引 ||1 |简单 |yourock_achiever |参考 |yourock_achiever_achievement_id,yourock_achiever_alias_id |yourock_achiever_alias_id |4 |paul.T4.alias_id |152 |||1 |简单 |yourock_成就|eq_ref |主要 |主要 |4 |paul.yourock_achiever.achievement_id |1 ||+----+-------------+------------+--------+-----------------------------------------------------------+----------------------------------+---------+--------------------------------------+------+---------------------------------+6 行(0.00 秒)

我曾希望对 mysql 有足够的了解，说明不需要查询.唉，看来您无法从解释语句中获得足够的信息，而您需要原始 SQL.查询:

SELECT `yourock_achievement`.`id`,`yourock_achievement`.`修改过的`，`yourock_achievement`.`created`,`yourock_achievement`.`string_id`,`yourock_achievement`.`owner_id`,`yourock_achievement`.`name`,`yourock_achievement`.`描述`，`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`内部联接`yourock_achiever`ON `yourock_achievement`.`id` = `yourock_achiever`.`achievement_id`内部联接`yourock_alias`ON `yourock_achiever`.`alias_id` = `yourock_alias`.`id`内部联接`yourock_achiever` T4ON `yourock_alias`.`id` = T4.`alias_id`内部联接`yourock_achievement` T5ON T4.`achievement_id` = T5.`id`内部联接`yourock_achiever` T6ON T5.`id` = T6.`achievement_id`在哪里T6.`alias_id` = 6订购者`yourock_achievement`.`修改过的`DESC

解决方案

Paul:

<块引用>

eq_ref

对于前面表中的每个行组合，从该表中读取一行.除了 system 和 const 类型之外，这是最好的连接类型.当连接使用索引的所有部分并且索引是 PRIMARY KEY 或 UNIQUE 索引时使用它.>

eq_ref 可用于使用 = 运算符进行比较的索引列.比较值可以是常量或表达式，该表达式使用在此表之前读取的表中的列.在以下示例中，MySQL 可以使用 eq_ref 连接来处理 ref_table:

SELECT * FROM ref_table,other_tableWHERE ref_table.key_column=other_table.column;SELECT * FROM ref_table,other_tableWHERE ref_table.key_column_part1=other_table.columnAND ref_table.key_column_part2=1;

<块引用>

参考

从该表中读取具有匹配索引值的所有行，以获取先前表中行的每个组合.如果联接仅使用键的最左前缀或者键不是 PRIMARY KEY 或 UNIQUE 索引(换句话说，如果联接无法根据键值选择单行)，则使用 ref 强>.如果使用的键只匹配几行，这是一个很好的连接类型.

ref 可用于使用 = 或 <=> 运算符进行比较的索引列.在以下示例中，MySQL 可以使用 ref join 来处理 ref_table:

SELECT * FROM ref_table WHERE key_column=expr;SELECT * FROM ref_table,other_tableWHERE ref_table.key_column=other_table.column;SELECT * FROM ref_table,other_tableWHERE ref_table.key_column_part1=other_table.columnAND ref_table.key_column_part2=1;

这些是从 MySQL 手册中逐字复制的:http://dev.mysql.com/doc/refman/5.0/en/using-explain.html

如果您可以发布您的永远的查询，我可以帮助查明是什么导致它变慢.另外，请说明您对 forever 的定义是什么.另外，如果你能提供你的SHOW CREATE TABLE xxx;"这些表的语句，我可以帮助尽可能地优化您的查询.

作为一个可能的改进点，我立即想到的是使用临时；使用文件排序；".这意味着创建了一个临时表来满足查询(不一定是坏事)，并且无法从索引中检索到您指定的 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_keys column 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 django models) :

+----+-------------+---------------------+--------+-----------------------------------------------------------+---------------------------------+---------+--------------------------------------+------+---------------------------------+
| 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屋！