最近有个需求，要修改现有存储结构，涉及查询条件和查询效率的考量，看了几篇索引和HBase相关的文章，回忆了相关知识，结合项目需求，说说自己的理解和总结。
前2篇介绍了索引的优点、索引结构的演化过程以及SQL的执行过程，重点分析了SQL的执行顺序和数据的定位过程，错过的朋友可以先回顾下：
这篇进入正题，介绍如何查看和分析SQL执行情况、排查SQL的性能问题，通过本篇介绍，你会了解到：
- explain命令概述
- select_type字段详细介绍
- type字段详细介绍
- extra字段详细介绍
部分内容摘录了几个博友的文章，最后会给出文章链接，感谢他们的精彩分析。
explain命令概述
工作中，MySQL会记录执行时间比较久的SQL语句，找出这些SQL语句是第一步，重要的是查看SQL语句的执行计划，对于MySQL执行计划的获取，可以通过explain方式来查看，这条命令的输出结果能够让我们了解MySQL优化器是如何执行SQL语句的。
MySQL优化器是基于开销来工作的，是在每条SQL语句执行的时候动态地计算出来的，命令用法十分简单, 在 SELECT 语句前加上 Explain 就可以了。
先来个示例
以基本的员工表为例，表的结构如下：
mysql> show create table employee \G;
*************************** 1. row ***************************
Table: mcc_employee
Create Table: CREATE TABLE `employee` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`userId` varchar(50) DEFAULT NULL COMMENT '员工编号',
`userName` varchar(50) DEFAULT NULL COMMENT '员工名称',
`nickName` varchar(50) DEFAULT NULL COMMENT '昵称',
`gender` varchar(10) DEFAULT NULL COMMENT '性别',
`mobilePhone` varchar(20) DEFAULT NULL COMMENT '手机号',
`miliao` varchar(100) DEFAULT NULL COMMENT '米聊号',
`email` varchar(100) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
一个简单的查询：
mysql> explain select * from employee where id =1 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: employee
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: const
rows: 1
Extra: NULL
1 row in set (0.00 sec)
select_type为simple说明是一个普通的查询，不包含子查询和union查询。
字段概述
id字段：select查询的标识符. 每个select都会自动分配一个唯一的标识符，id数值越大的优先执行，id相同的从上往下顺序执行。
select_type：select查询的类型，当没有子查询或union查询时为simple，有子查询或union查询时，有几种情况，后面会详细介绍。
table：标识查询的是哪个表，显示这一行的数据是关于哪张表的，有时不是真实的表名字,看到的是derived（n是个数字，为id字段）
mysql> explain select * from (select * from (select * from employee where id =76) table1 ) table2 ;
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | NULL |
| 2 | DERIVED | <derived3> | system | NULL | NULL | NULL | NULL | 1 | NULL |
| 3 | DERIVED | mcc_inform | const | PRIMARY | PRIMARY | 8 | const | 1 | NULL |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
type：数据访问、读取操作类型，对性能影响比较大，后面会详细介绍。
possible_keys：此次查询中可能选用的索引，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用。
key：此次查询中确切使用到的索引，如果没有选择索引，键是NULL。
key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。
ref: 哪个字段或常数与key一起被使用。
rows: 此查询一共扫描了多少行，这个是一个估计值。
filtered: 表示此查询条件所过滤的数据的百分比。
extra: 额外的信息，后面会详细介绍。
select_type字段详细介绍
select_type表示查询的类型，是简单查询或复杂查询，如果是复杂查询，包含SIMPLE、SIMPLE、UNION、UNION RESULT、SUBQUERY、DEPENDENT、DEPENDENT UNION、DEPENDENT SUBQUERY、DERIVED等，了解这些，可以识别在执行那部分。
SIMPLE
简单select，不使用union或子查询等:
mysql> explain select * from employee where id =1 ;
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | employee | const | PRIMARY | PRIMARY | 8 | const | 1 | NULL |
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
SIMPLE
如果是复杂查询，表示是最外层的select：
mysql> explain select * from (select * from employee where id =1) a ;
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | NULL |
| 2 | DERIVED | employee | const | PRIMARY | PRIMARY | 8 | const | 1 | NULL |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
UNION & UNION RESULT
UNION中的第二个或后面的SELECT语句，UNION RESULT为UNION的结果：
mysql> explain select * from employee where id =1 union all select * from employee where id=2;
+----+--------------+------------+-------+---------------+---------+---------+-------+------+-----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+------------+-------+---------------+---------+---------+-------+------+-----------------+
| 1 | PRIMARY | employee | const | PRIMARY | PRIMARY | 8 | const | 1 | NULL |
| 2 | UNION | employee | const | PRIMARY | PRIMARY | 8 | const | 1 | NULL |
| NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+------------+-------+---------------+---------+---------+-------+------+-----------------+
SUBQUERY
子查询中的第一个SELECT：
mysql> explain select * from employee where id = (select id from employee where id =1);
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------------+
| 1 | PRIMARY | employee | const | PRIMARY | PRIMARY | 8 | const | 1 | NULL |
| 2 | SUBQUERY | employee | const | PRIMARY | PRIMARY | 8 | const | 1 | Using index |
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------------+
DEPENDENT UNION & DEPENDENT SUBQUERY
DEPENDENT UNION，UNION中的第二个或后面的SELECT语句，但结果取决于外面的查询；
DEPENDENT SUBQUERY，子查询中的第一个SELECT，但结果取决于外面的查询：
mysql> explain select * from employee where id in (select id from employee where id =1 union all select id from employee where id=2);
+----+--------------------+------------+-------+---------------+---------+---------+-------+------+-----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+------------+-------+---------------+---------+---------+-------+------+-----------------+
| 1 | PRIMARY | employee | ALL | NULL | NULL | NULL | NULL | 26 | Using where |
| 2 | DEPENDENT SUBQUERY | employee | const | PRIMARY | PRIMARY | 8 | const | 1 | Using index |
| 3 | DEPENDENT UNION | employee | const | PRIMARY | PRIMARY | 8 | const | 1 | Using index |
| NULL | UNION RESULT | <union2,3> | ALL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------------+------------+-------+---------------+---------+---------+-------+------+-----------------+
DERIVED
派生表的SELECT，FROM子句的子查询：
mysql> explain select * from (select * from employee where id =1) a ;
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | NULL |
| 2 | DERIVED | employee | const | PRIMARY | PRIMARY | 8 | const | 1 | NULL |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
type字段详细介绍
type表示数据访问/读取的操作类型，显示了连接使用了哪种类别,有无使用索引，它提供了判断查询是否高效的重要依据依据。
常见的类型有常用有，性能从差到好排序：ALL, index, range, ref, eq_ref, const, system, NULL
NULL
不用访问表或者索引就可以直接得到结果：
mysql> explain select sysdate();
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
const、system
如将主键或者唯一索引置于where列表中，MySQL就能将该查询转换为一个常量，当表中只有一行记录时，类型为system。
mysql> explain select * from (select id from mcc_inform where id =1) a;
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------------+
| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | NULL |
| 2 | DERIVED | employee | const | PRIMARY | PRIMARY | 8 | const | 1 | Using index |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------------+
eq_ref
此类型通常出现在多表的 join 查询, 表示对于前表的每一个结果, 都只能匹配到后表的一行结果. 并且查询的比较操作通常是 =, 查询效率较高：
mysql> explain select * from t3,t4 where t3.id=t4.accountid;
+----+-------------+-------+--------+-------------------+-----------+---------+----------------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-------------------+-----------+---------+----------------------+------+-------+
| 1 | SIMPLE | t4 | ALL | NULL | NULL | NULL | NULL | 1000 | |
| 1 | SIMPLE | t3 | eq_ref | PRIMARY,idx_t3_id | idx_t3_id | 4 | dbatest.t4.accountid | 1 | |
+----+-------------+-------+--------+-------------------+-----------+---------+----------------------+------+-------+
ref
与eq_ref类似，不同的是ref不是唯一索引，此类型通常出现在多表的 join 查询, 针对于非唯一或非主键索引, 或者是使用了最左前缀规则索引的查询，可以用于使用=或<=>操作符的带索引的列：
index_merge
该联接类型表示使用了索引合并优化方法， where中可能有多个条件(或者join)涉及到多个字段，它们之间进行 AND 或者 OR，那么此时就有可能会使用到 index merge 技术。
index merge 技术如果简单的说，其实就是：对多个索引分别进行条件扫描，然后将它们各自的结果进行合并(intersect/union)。
range
表示使用索引范围查询, 通过索引字段范围获取表中部分数据记录， 这个类型通常出现在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中
index
表示全索引扫描(full index scan), 和 ALL 类型类似, 只不过 ALL 类型是全表扫描, 而 index 类型则仅仅扫描所有的索引, 而不扫描数据。
ALL
表示全表扫描, 这个类型的查询是性能最差的查询之一，一般不会出现。
extra字段详细介绍
EXplain中的很多额外的信息会在Extra字段显示，此字段能够给出让我们深入理解执行计划进一步的细节信息，介绍下常见的几个。
Using where
在查找使用索引的情况下，需要回表去查询所需的数据。
Using index
表示查询在索引树中就可查找所需数据, 不用扫描表数据文件, 说明性能不错。
Using filesort
当SQL中包含ORDER BY 操作，而且无法利用索引完成排序操作的时候，查询优化器不得不选择相应的排序算法来实现。
filesort主要用于查询数据结果集的排序操作，首先MySQL会使用sort_buffer_size大小的内存进行排序，如果结果集超过了sort_buffer_size大小，会把这一个排序后的chunk转移到file上，最后使用多路归并排序完成所有数据的排序操作。
filesort只能应用在单个表上，如果有多个表的数据需要排序，那么MySQL会先使用using temporary保存临时数据，然后再在临时表上使用filesort进行排序，最后输出结果。
Using temporary
查询有使用临时表, 一般出现于排序, 分组和多表join的情况, 查询效率不高, 建议优化.
下一篇文章会介绍索引优化原则以及案例分析。
参考文章：
欢迎扫描下方二维码，关注我的个人微信公众号，查看更多文章 ~
相关推荐
### MySQL 索引与执行计划 #### 一、索引与执行计划 ...通过以上详细介绍，我们可以看出索引和执行计划对于提高MySQL数据库的查询性能具有重要意义。合理设计索引和利用执行计划可以显著提升数据检索的速度和效率。
### MySQL执行计划及索引最佳...通过以上介绍，我们可以看出MySQL执行计划和索引优化是提高数据库性能的重要手段。合理地使用`EXPLAIN`和`PROFILE`工具，结合正确的索引策略，可以有效地提升查询效率，减少资源消耗。
`mysql_explain.pptx`可能包含更详细的PPT演示，涵盖MySQL执行计划的各个方面，包括如何分析执行计划，如何根据执行计划调整索引，以及如何解决常见的性能问题。建议查看这份资料以获取深入的实践指导和案例分析。 ...
在数据库查询优化领域，理解MySQL执行计划是至关重要的技能之一。执行计划，也称为查询计划或查询执行计划，是MySQL优化器为执行SQL查询所选择的一系列步骤。它揭示了数据库如何处理查询，包括数据访问方法、表的...
综上，《Oracle与MySQL数据库索引设计与优化》一书详细介绍了这两个数据库系统的索引特性、设计原则以及优化方法，对于数据库管理员和开发人员来说是一本极具价值的参考书。通过学习，读者可以更好地理解和应用索引...
7. 使用EXPLAIN分析查询：理解查询执行计划，检查是否使用了索引，是否存在全表扫描等问题。 8. 触发器和存储过程：虽然可以简化代码，但过度使用可能降低性能。谨慎使用，确保它们不会成为性能瓶颈。 9. 定期维护...
总结：数据库索引和事务是MySQL数据库管理中的关键概念。索引提高了数据查询速度，但需权衡存储和更新的开销；而事务则保证了数据操作的安全性，维护了数据的一致性。理解并合理使用这两者对于优化数据库性能和保证...
在工具方面，除了MySQL自带的EXPLAIN，还有一些第三方工具如MySQL Workbench、Percona Toolkit等，它们提供了更友好的界面和更详细的分析功能，可以帮助我们更深入地理解和优化执行计划。 总结来说，理解MySQL的...
MySQL执行计划是数据库查询优化中不可或缺的一部分，它能够详细展示SQL查询的执行方式，帮助数据库管理员和开发人员深入理解查询的内部细节，从而对查询进行优化。执行计划通过EXPLAIN关键字来获取，它会对SELECT...
《深入理解MySQL核心技术》这本书是MySQL数据库技术领域的一部经典之作，它旨在帮助读者深入了解MySQL的内部机制和工作原理，从而更好地优化和管理MySQL数据库系统。以下是对书中的主要知识点的详细阐述： 1. **...
详细讲解mysql优化过程。” 在MySQL数据库中，索引扮演着至关重要的角色，它不仅能够显著提升数据查询的速度，还能帮助执行某些约束（如唯一性和外键）。然而，不当的索引选择可能会导致生产环境中的大量问题。本文...
通过这次MySQL练习，你将更深入地理解索引的作用、创建方法以及查看索引的技巧，这对优化数据库性能至关重要。在实际应用中，根据业务需求和数据特性选择合适的索引策略，是数据库管理员和开发人员必备的技能之一。
但是，需要注意的是，Explain 语句输出的信息并不是查询的实际执行计划，而是 MySQL 预计的执行计划。因此，实际执行计划可能与 Explain 语句输出的信息不同。 Explain 语句是 MySQL 查询优化的重要工具，了解 ...
本篇内容会结合这些基础知识，详细解析MySQL执行计划中包含的关键要素。 首先，执行计划（execution plan）是数据库管理系统的组成部分，用以展示一条SQL语句执行的具体步骤。它有助于开发者和数据库管理员理解...
这可以帮助开发者理解查询的执行计划，并确定是否需要创建索引。例如： ```sql EXPLAIN SELECT * FROM employees WHERE last_name = 'Smith'; ``` 如果查询结果中显示全表扫描（`ALL`），则可能需要考虑为该查询...
### MySQL数据库及索引详解 #### 一、MySQL简介与数据库发展 MySQL是一款非常流行的开源关系型数据库管理...通过理解MySQL及其索引的基本概念，开发者可以更好地管理和优化数据库性能，确保高效地完成数据检索任务。
Extra 列是执行计划中的一个重要部分，用于描述执行计划的详细信息。在本文中，我们将深入探讨 MySQL 执行计划的 Extra 列，了解其含义和应用场景。 Using Index Using Index 也是 Extra 列中的一种状态，意味着 ...