MYSQL Explain执行计划优化

编写SQL SELECT语句时，MySQL Query Optimizer尝试为查询执行制定最佳计划。通过在查询前面加上EXPLAIN，可以查看有关计划的信息。EXPLAIN是您可以用来了解和优化麻烦的MySQL查询的最强大的工具之一，但是令人遗憾的是，许多开发人员很少使用它。在本文中，您将了解EXPLAIN的输出内容以及如何使用它来优化架构和查询。

Explain输出说明

SELECT site_options.domain, sites_users.user, site_taxes.monthly_statement_fee, site.name, AVG(price) AS average_product_price FROM sites_orders_products, site_taxes, site, sites_users, site_options WHERE site_options.site_id = site.id AND sites_users.id = site.user_id AND site_taxes.site_id = site.id AND sites_orders_products.site_id = site.id GROUP BY site.id ORDER BY site.date_modified desc LIMIT 5;

Explain:

+------+-------------+---------------------------------+--------+-----------------+---------------+---------+---------------------------------+------+-----------+
| id   | select_type | table                           | type   | possible_keys   | key           | key_len | ref                             | rows | Extra     |
+------+-------------+---------------------------------+--------+-----------------+---------------+---------+---------------------------------+------+-----------+
|    1 | SIMPLE      | sites                           | index  | PRIMARY,user_id | PRIMARY       | 4       | NULL                            |  858 | Using temporary; Using filesort |
|    1 | SIMPLE      | sites_options                   | ref    | site_id         | site_id       | 4       | service.sites.id                |    1 |           |
|    1 | SIMPLE      | sites_taxes                     | ref    | site_id         | site_id       | 4       | service.sites.id                |    1 |           |
|    1 | SIMPLE      | sites_users                     | eq_ref | PRIMARY         | PRIMARY       | 4       | service.sites.user_id           |    1 |           |
|    1 | SIMPLE      | sites_orders_products           | ref    | site_id         | site_id       | 4       | service.sites.id                | 4153 |           |//
+------+-------------+---------------------------------+--------+-----------------+---------------+---------+---------------------------------+------+-----------+
5 rows in set (0.00 sec)

行的含义：

ID（查询中每个SELECT的顺序标识符）
select_type（语句类型）
table（引用的表）
type（联接类型）
Possible_Keys（可以使用哪些键）
key（使用的索引key）
key_len（索引长度）
ref（依据的字段）
行（搜索的行数）
额外（其他信息）

重要字段说明

1. select_type（语句类型）

• SIMPLE –查询是一个简单的SELECT查询，没有任何子查询或UNION
• PRIMARY – SELECT在JOIN的最外层查询中
• DERIVED – SELECT是FROM子句中子查询的一部分
• SUBQUERY –子查询中的第一个SELECT
• DEPENDENT SUBQUERY –依赖于外部查询的子查询
• UNCACHEABLE SUBQUERY–不可缓存的子查询（在某些条件下，查询可缓存）
• UNION – SELECT是UNION的第二个或更高版本的语句
• DEPENDENT UNION – UNION的第二个或更高版本的SELECT依赖于外部查询
• UNION RESULT – SELECT是UNION的结果

2. type（联接类型）

MySQL如何连接使用的表。这是输出中最具洞察力的字段之一，因为它可以指示缺少索引或应该重新考虑查询的编写方式。可能的值为：

• system–表只有零行或一行
• const –该表只有一个匹配的行被索引。
  这是最快的连接类型，因为该表仅需读取一次，并且在连接其他表时可以将列的值视为常量。
• req_ref – 联接使用索引的所有部分，索引为PRIMARY KEY或UNIQUE NOT NULL。
  这是第二种可能的最佳联接类型。
• ref –从上一个表中为行的每种组合读取索引列的所有匹配行。
  对于使用=或<=>运算符进行比较的索引列，将出现这种类型的联接。
• fulltext – 联接使用表的FULLTEXT索引。
  ref_or_null –与ref相同，但也包含具有该列的null值的行。
• index_merge –联接使用索引列表来生成结果集。
  EXPLAIN输出的键列将包含使用的键。
• unique_subquery – IN子查询仅从表中返回一个结果，并使用主键。
• index_subquery –与unique_subquery相同，但返回多个结果行。
  范围–索引用于查找特定范围内的匹配行，通常是使用诸如BETWEEN，IN，>，> =等运算符将键列与常量进行比较时。
• index –扫描整个索引树以找到匹配的行。
• all–扫描整个表以查找联接的匹配行。这是最差的联接类型，通常表明表上缺少适当的索引。

通常，您将把多个表连接在一起，并使用复杂的WHERE子句。仅在几列上添加索引可能并不总是有帮助，然后是时候Explain一下SELECT SQL。