现实开发中，为了优化SQL，需要了解SQL语句的具体执行过程，可以使用explain+SQL语句来模拟优化器执行SQL查询语句，从而知道MySQL是如何处理SQL语句的。
mysql> explain select * from staffs;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | staffs | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
执行计划中包含的信息
| 列名 | 含义 |
|---|---|
| id | SELECT查询序列号 |
| select_type | 查询类型 |
| table | 表名 |
| partitions | 匹配分区 |
| type | 访问类型 |
| possible_keys | 可能的索引选择 |
| key | 实际使用的索引 |
| key_len | 索引中使用的字节数 |
| ref | 与索引做比较的列 |
| rows | 估算找出所需记录需要读取的行数 |
| filtered | 按表条件过滤的行百分比 |
| Extra | 附加信息 |
重点看下面5个属性：
1.type
type显示的是访问类型，访问类型表示我是以何种方式去访问我们的数据，最容易想的是全表扫描，直接暴力的遍历一张表去寻找需要的数据，效率非常低下，访问的类型有很多，效率从最好到最坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
常见情况排序：
const（表最多有一个匹配行） > ref（使用非唯一索引进行数据查找）> range（利用索引查询限制了范围） > index（全索引扫描）> ALL（全表扫描）
一般情况下，得保证查询至少达到range级别，最好能达到ref
2.key
实际使用的索引，如果为null，则没有使用索引，查询中若使用了覆盖索引，则该索引和查询的select字段重叠
3.key_len
表示索引中使用的字节数，可以通过key_len计算查询中使用的索引长度，在不损失精度的情况下长度越短越好
以上图为例介绍下key_len的计算方法：
- 列是否为空： NULL(+1) || NOT NULL(+0)
- 字符集： 如 utf8mb4=4 || utf8=3 || gbk=2 || latin1=1
- 列类型是否为变长字符：varchar(+2) || char(+0)
所以上面key_len = (24 + 20) * 3 + 4 + 2 * 2 = 140
4.rows
根据表的统计信息及索引使用情况，大致估算出找出所需记录需要读取的行数，此参数很重要，直接反应sql找了多少数据，在完成目的的情况下越少越好
5.Extra
列出几种Extra的可能情况：
mysql> show index from staffs;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| staffs | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
| staffs | 1 | idx_nap | 1 | name | A | 0 | NULL | NULL | | BTREE | | |
| staffs | 1 | idx_nap | 2 | age | A | 0 | NULL | NULL | | BTREE | | |
| staffs | 1 | idx_nap | 3 | pos | A | 0 | NULL | NULL | | BTREE | | |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+-
–Using filesort:说明mysql无法利用索引进行排序，只能利用排序算法进行排序，会消耗额外的位置
mysql> explain select * from staffs order by add_time;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | staffs | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using filesort |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------+
–Using temporary:建立临时表来保存中间结果，查询完成之后把临时表删除
–Using index condition:会先条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用 WHERe 子句中的其他条件去过滤这些数据行
mysql> explain select * from staffs where name = 'July' and pos > 25;
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | staffs | NULL | ref | idx_nap | idx_nap | 74 | const | 1 | 100.00 | Using index condition |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+
–Using index:这个表示当前的查询时覆盖索引的，直接从索引中读取数据，而不用访问数据表。如果同时出现using where 表名索引被用来执行索引键值的查找，如果没有，表面索引被用来读取数据，而不是真的查找
mysql> explain select name,age,pos from staffs where name = 'July';
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | staffs | NULL | ref | idx_nap | idx_nap | 74 | const | 1 | 100.00 | Using index |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
mysql> explain select name,age,pos from staffs where name = 'July' and age > 20 and pos = 'dev';
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+--------------------------+
| 1 | SIMPLE | staffs | NULL | ref | idx_nap | idx_nap | 74 | const | 1 | 100.00 | Using where; Using index |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+--------------------------+
–Using where:使用where进行条件过滤
mysql> explain select * from staffs where age > 20;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | staffs | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
–Using join buffer:使用连接缓存
–impossible where：where语句的结果总是false
mysql> explain select * from staffs where name = 'a' and name = 'b';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERe |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
