MySQL 执行计划
什么是执行计划呢？
简单来说，就是 SQL 在数据库中执行时的表现情况，通常用于 SQL 性能分析、优化等场景。
本文从 MySQL 的逻辑结构讲解，过渡到 MySQL 的查询过程，然后给出执行计划的例子并重点介绍执行计划的输出参数，从而理解执行计划。
MySQL 逻辑架构
MySQL 逻辑架构分为三层，如下图：
客户端：
如，连接处理、授权认证、安全等功能；
Server层：
包括连接器 、 查询缓存 、 分析器 、 优化器 、 执⾏器等 ， 涵盖MySQL的⼤多数核⼼服务功能 ， 以及所有的内置函数如 ⽇期 、 时间 、 数学和加密函数等), 所有跨存储引擎的功能都在这 ⼀层实现 ， ⽐如存储过程 、 触发器 、 视图等。
存储引擎
存储引擎层负责数据的存储和提取 。 其架构模式是插件式的 ， ⽀持 InnoDB 、 MyISAM 、 Memory 等多个存储引擎 。 现在最常⽤的存储引擎是 InnoDB ， 它从 MySQL 5 .5 .5 版本开始成为了默认存储引擎 。负责 MySQL 中的数据存储和读取中间的server层通过 API 与存储引擎通信，这些 API 屏蔽了不同存储引擎间的差异；
查询缓存：对于 select 语句，在解析查询之前，服务器会先检查查询缓存（Query Cache）。如果命中，服务器便不再执行查询解析、优化和执行的过程，而是直接返回缓存中的结果集。大体意思就是 MySQL 拿到 ⼀个查询请求后 ， 会先到查询缓存看看 ， 之前是不是执⾏过这条语句 。 之前执⾏过的语句及其结果可能会以 key -value 对的形式 ， 被直接缓存在内存中 。 key 是查询的语句 ， value 是查询的结果 。 如果你的查询能够直接在这个缓存中找到 key ， 那么这个 value 就会被直接返回给客户端 。如果语句不在查询缓存中 ， 就会继续后⾯的执⾏阶段 。 执⾏完成后 ， 执⾏结果会被存⼊查询缓存中 。 你可以看到 ， 如果查询命中缓存 ， MySQL 不需要执⾏后⾯的复杂操作 ， 就可以直接返回结果 ， 这个效率会很⾼ 。但是⼤多数情况下建议你不要使⽤查询缓存 ， 为什么呢？ 因为查询缓存往往弊⼤于利 。查询缓存的失效⾮常频繁 ， 只要有对 ⼀个表的更新 ， 这个表上所有的查询缓存都会被清空 。 因此很可能你费劲地把结果存起来 ， 还没使⽤呢 ， 就被 ⼀个更新全清空了 。 对于更新压⼒⼤的数据库来说 ， 查询缓存的命中率会⾮常低 。 除⾮你的业务就是有⼀张静态表 ， 很⻓时间才会更新 ⼀次 。 ⽐如 ⼀个系统配置表 ， 那这张表上的查询才适合使⽤查询缓存 。好在 MySQL 也提供了这种 “按需使⽤ ”的⽅式 。 你可以将参数 query_cache _type 设置成DEMAND ， 这样对于默认的SQL语句都不使⽤查询缓存 。 ⽽对于你确定要使⽤查询缓存的语句 ， 可以⽤ SQL _CACHE 显式指定 ， 像下⾯这个语句 ⼀样 ：
mysql>select SQL_CACHE * from T where ID=10；需要注意的是 ， MySQL8 .0 版本直接将查询缓存的整块功能删掉了 ， 也就是说 8 .0 开始彻底没有这个功能了 。
MySQL查询过程
如果能搞清楚 MySQL 是如何优化和执行查询的，对优化查询一定会有帮助。很多查询优化实际上就是遵循一些原则让优化器能够按期望的合理的方式运行。
下图是 MySQL 执行一个查询的过程。实际上每一步都比想象中的复杂，尤其优化器，更复杂也更难理解。本文只给予简单的介绍。
MySQL 查询过程如下：
1.客户端将查询发送到 MySQL 服务器；
2.服务器先检查查询缓存，如果命中，立即返回缓存中的结果；否则进入下一阶段；
3.服务器对 SQL 进行解析、预处理，再由优化器生成对象的执行计划；
4.MySQL 根据优化器生成的执行计划，调用存储引擎 API 来执行查询；
5.服务器将结果返回给客户端，同时缓存查询结果；
一、explian 执行计划
MySQL 会解析查询，并创建内部数据结构（解析树），并对其进行各种优化，包括重写查询、决定表的读取顺序、选择合适的索引等。
用户可通过关键字提示（hint）优化器，从而影响优化器的决策过程。也可以通过通过优化器解释（explain）优化过程的各个因素，使用户知道数据库是如何进行优化决策的，并提供一个参考基准，便于用户重构查询和数据库表的 schema、修改数据库配置等，使查询尽可能高效。
例子
这个执行计划给出的信息是，该查询通过一个简单的给定范围的扫描，共扫描 55183 行，使用 index condition 条件在 dt_user 表中筛选出，扫描过程中使用 PRIMARY 和 idx_city_name 索引。
使用 explain 或者 desc 关键字可以查看 MySQL 的 SQL 语句执行计划，根据执行计划分析你的查询语句或是表结构的性能瓶颈。
explain 和 desc 一样，执行计划包含的信息如下十列信息：
其中最重要的字段为：id、type、key、rows、Extra
以下是各个字段的详细解释：
1）id
select 查询的序列号，包含一组数字，表示查询中执行 select 子句或操作表的顺序
三种情况：
1、id相同：执行顺序由上至下
2、id不同：如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
3、id相同又不同（两种情况同时存在）：id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行
2）select_type
查询的类型，常见的类型如下表所示，主要是用于区分普通查询、联合查询、子查询等复杂的查询。
| 类型 | 说明 |
|---|---|
| SIMPLE | 简单SELECT(不使用UNION或子查询等) |
| PRIMARY | 主查询，即最外面的SELECT |
| UNION | UNION中的第二个或后面的SELECT语句 |
| DEPENDENT UNION | NION中的第二个或后面的SELECT语句，取决于外面的查询 |
| UNION RESULT | UNION的结果 |
| SUBQUERY | 子查询中的第一个SELECT |
| DEPENDENT SUBQUERY | 子查询中的第一个SELECT，取决于外面的查询 |
| SUBQUERY | 导出表的SELECT(FROM子句的子查询) |
1、SIMPLE：简单的select查询，查询中不包含子查询或者union
2、PRIMARY：查询中包含任何复杂的子部分，最外层查询则被标记为primary
3、SUBQUERY：在select 或 where列表中包含了子查询
4、DERIVED：在from列表中包含的子查询被标记为derived（衍生），mysql或递归执行这些子查询，把结果放在零时表里
5、UNION：若第二个select出现在union之后，则被标记为union；若union包含在from子句的子查询中，外层select将被标记为derived
6、UNION RESULT：从union表获取结果的select
3）type
表示 MySQL 在表中找到所需行的方式，或者叫访问类型，SQL 查询优化中一个很重要的指标，结果值从好到坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般来说，好的sql查询至少达到range级别，最好能达到ref
| 类型 | 值 | 示例说明 |
|---|---|---|
| ALL | 全表扫描 | 一般是没有where条件或者where条件没有使用索引的查询语句 |
| index | 索引全扫描 | 一般是查询的字段都有索引的查询语句 |
| range | 索引范围扫描 | 常用于<、<=、>、>=、between等操作 |
| ref | 非唯一索引扫描 | 使用非唯一索引或唯一索引的前缀扫描，返回匹配某个单独值的记录行 |
| eq_ref | 唯一索引扫描 | 类似ref，区别在于使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配 |
| const,system | 单表最多有一个匹配行 | onst/system出现在根据主键primary key或者 唯一索引 unique index 进行的查询 |
| NULL | 不用扫描表或索引 | select 1 from dual |
1、NULL：不用访问表或者索引，直接就能得到结果，例如：
explain select 3+2 from dual;
2、system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，可以忽略不计；const：表示通过索引一次就找到了，const用于比较primary key 或者 unique索引。因为只需匹配一行数据，所有很快。如果将主键置于where列表中，mysql就能将该查询转换为一个 const
3、eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录匹配。常见于多表连接中使用主键 （primary key）或 唯一索引扫描(unique index)作为关联条件。
注意：ALL全表扫描的表记录最少的表如t1表。
4、ref：非唯一性索引扫描或唯一索引的前缀扫描，返回匹配某个单独值的所有行。本质是也是一种索引访问，它返回所有匹配某个单独值的行，然而他可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体
5、range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了那个索引。一般就是在where语句中出现了bettween、<、>、in等的查询。这种索引列上的范围扫描比全索引扫描要好。只需要开始于某个点，结束于另一个点，不用扫描全部索引
6、index：Full Index Scan，index与ALL区别为index类型只遍历索引树。这通常为ALL快，因为索引文件通常比数据文件小。（Index与ALL虽然都是读全表，但index是从索引中读取，而ALL是从硬盘读取）
7、ALL：Full Table Scan，遍历全表以找到匹配的行
4）possible_keys
查询涉及到的字段上存在索引，则该索引将被列出，但不一定被查询实际使用
5）key
实际使用的索引，如果为NULL，则没有使用索引。
查询中如果使用了覆盖索引，则该索引仅出现在key列表中 。
6）key_len
表示索引中使用的字节数，查询中使用的索引的长度（最大可能长度），并非实际使用长度，理论上长度越短越好。key_len是根据表定义计算而得的，不是通过表内检索出的
7）ref
显示索引的那一列被使用了，如果可能，是一个常量const。
8）rows
根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数
9）Extra
不适合在其他字段中显示，但是十分重要的额外信息
| extra元素 | 说明 |
|---|---|
| Distinct | 一旦MYSQL找到了与行相联合匹配的行，就不再搜索了 |
| Not exists | MYSQL 优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了 |
| Range | checked for each |
| Record | 没有找到理想的索引，因此对于从前面表中来的每一 个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一 |
| Using filesort | 表示MySQL需额外排序操作, 不能通过索引顺序达到排序效果 |
| Using index | 表示索引覆盖，不会回表查询 |
| Using temporary | 通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上 |
| Using where | 表示进行了回表查询 |
什么是回表
简单来说就是数据库根据索引找到了指定的记录所在行后，还需要根据 rowid 再次到数据块里取数据的操作。比如这样的执行计划，先索引扫描，再通过 rowid 去取索引中未能提供的数据，即为回表。“回表”一般就是指执行计划里显示的“TABLE ACCESS BY INDEX ROWID”。再例如，虽然只查询索引里的列，但是需要回表过滤掉其他行。
怎么避免回表？
将需要的字段放在索引中去。查询的时候就能避免回表。但是不要刻意去避免回表，那样代价太了。也不是将所有的字段都放在所有中。回表和 MySQL 的索引实现有关系，有兴趣的朋友可以进一步了解 B+ 树相关原理。
1、Using filesort ：
mysql对数据使用一个外部的索引排序，而不是按照表内的索引进行排序读取。也就是说mysql无法利用索引完成的排序操作成为“文件排序”
由于索引是先按email排序、再按address排序，所以查询时如果直接按address排序，索引就不能满足要求了，mysql内部必须再实现一次“文件排序”
2、Using temporary：
使用临时表保存中间结果，也就是说mysql在对查询结果排序时使用了临时表，常见于order by 和 group by
3、Using index：
表示相应的select操作中使用了覆盖索引（Covering Index），避免了访问表的数据行，效率高
如果同时出现Using where，表明索引被用来执行索引键值的查找（参考上图）
如果没用同时出现Using where，表明索引用来读取数据而非执行查找动作
覆盖索引（Covering Index）：也叫索引覆盖。就是select列表中的字段，只用从索引中就能获取，不必根据索引再次读取数据文件，换句话说查询列要被所建的索引覆盖。
注意：
a、如需使用覆盖索引，select列表中的字段只取出需要的列，不要使用select *
b、如果将所有字段都建索引会导致索引文件过大，反而降低crud性能
4、Using where ：
使用了where过滤
5、Using join buffer ：
使用了链接缓存
6、Impossible WHERE：
where子句的值总是false，不能用来获取任何元组
7、select tables optimized away：
在没有group by子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT（*）操作，不必等到执行阶段在进行计算，查询执行计划生成的阶段即可完成优化
8、distinct：
优化distinct操作，在找到第一个匹配的元祖后即停止找同样值得动作
二、通过 show profile 分析SQL
--检查是否支持 profilemysql> select @@have_profiling;+------------------+| @@have_profiling |+------------------+| YES |+------------------+1 row in set, 1 warning (0.00 sec)
-- profile 为 0 表示关闭，可在会话级别设置为 1 打开。mysql> select @@profiling;+-------------+| @@profiling |+-------------+| 0 |+-------------+1 row in set, 1 warning (0.00 sec)
mysql> set profiling=1;Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> select count(*) from t_ci_user;+----------+| count(*) |+----------+| 768 |+----------+1 row in set (0.00 sec)
--show profiles 可查看当前执行的 SQL 的 Query_ID，本案例为 6mysql> show profiles;+----------+------------+----------------------------------------------------------------------------------------------------------+| Query_ID | Duration | Query |+----------+------------+----------------------------------------------------------------------------------------------------------+| 1 | 0.02545575 | select TABLE_SCHEMA,TABLE_NAME,TABLE_ROWS from information_schema.TABLES where TABLE_SCHEMA='cba_mps_ci' || 2 | 0.00214225 | select count(*) from t_ci_user || 3 | 0.00077300 | SELECT DATABASE() || 4 | 0.00244200 | show databases || 5 | 0.00949875 | show tables || 6 | 0.00180850 | select count(*) from t_ci_user |+----------+------------+----------------------------------------------------------------------------------------------------------+6 rows in set, 1 warning (0.00 sec)
-- 通过 show profile for query ID 语句能够看到执行过程中线程的每个状态和消耗的时间。mysql> show profile for query 6;+----------------------------+----------+| Status | Duration |+----------------------------+----------+| starting | 0.000212 || checking permissions | 0.000041 || Opening tables | 0.000109 || init | 0.000039 || System lock | 0.000100 || optimizing | 0.000037 || statistics | 0.000072 || preparing | 0.000061 || executing | 0.000030 || Sending data | 0.000746 || end | 0.000039 || query end | 0.000030 || waiting for handler commit | 0.000039 || closing tables | 0.000039 || freeing items | 0.000060 || cleaning up | 0.000157 |+----------------------------+----------+16 rows in set, 1 warning (0.00 sec)Sending data 状态表示 MySQL 线程开始访问数据行并把结果返回给客户端，而不仅仅是返回结果给客户端。由于在 Sending data 状态下，MySQL 线程往往需要做大量的磁盘读取操作，所以经常是整个查询中耗时最长的状态。
在获取到最消耗时间的线程状态后，MySQL 支持进一步选择 all、cpu、block io、context switch、page faults 等明细类型来查看 MySQL 在使用什么资源上耗费了过高的时间。
mysql> show profile cpu for query 6;+----------------------------+----------+----------+------------+| Status | Duration | CPU_user | CPU_system |+----------------------------+----------+----------+------------+| starting | 0.000212 | 0.000353 | 0.000015 || checking permissions | 0.000041 | 0.000063 | 0.000007 || Opening tables | 0.000109 | 0.000192 | 0.000008 || init | 0.000039 | 0.000075 | 0.000007 || System lock | 0.000100 | 0.000054 | 0.000000 || optimizing | 0.000037 | 0.000028 | 0.000008 || statistics | 0.000072 | 0.000074 | 0.000000 || preparing | 0.000061 | 0.000054 | 0.000007 || executing | 0.000030 | 0.000028 | 0.000000 || Sending data | 0.000746 | 0.001011 | 0.000059 || end | 0.000039 | 0.000058 | 0.000008 || query end | 0.000030 | 0.000060 | 0.000000 || waiting for handler commit | 0.000039 | 0.000071 | 0.000007 || closing tables | 0.000039 | 0.000076 | 0.000000 || freeing items | 0.000060 | 0.000117 | 0.000008 || cleaning up | 0.000157 | 0.000296 | 0.000022 |+----------------------------+----------+----------+------------+16 rows in set, 1 warning (0.00 sec)
mysql> show profile block io for query 6;+----------------------------+----------+--------------+---------------+| Status | Duration | Block_ops_in | Block_ops_out |+----------------------------+----------+--------------+---------------+| starting | 0.000212 | 0 | 0 || checking permissions | 0.000041 | 0 | 0 || Opening tables | 0.000109 | 0 | 0 || init | 0.000039 | 0 | 0 || System lock | 0.000100 | 0 | 0 || optimizing | 0.000037 | 0 | 0 || statistics | 0.000072 | 0 | 0 || preparing | 0.000061 | 0 | 0 || executing | 0.000030 | 0 | 0 || Sending data | 0.000746 | 0 | 0 || end | 0.000039 | 0 | 0 || query end | 0.000030 | 0 | 0 || waiting for handler commit | 0.000039 | 0 | 0 || closing tables | 0.000039 | 0 | 0 || freeing items | 0.000060 | 0 | 0 || cleaning up | 0.000157 | 0 | 0 |+----------------------------+----------+--------------+---------------+16 rows in set, 1 warning (0.00 sec)
三、通过 trace 分析优化器如何选择执行计划
首先打开 trace，设置格式为 JSON，设置 trace 最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整显示。
mysql> set optimizer_trace="enabled=on",end_markers_in_json=on;Query OK, 0 rows affected (0.00 sec)
mysql> set optimizer_trace_max_mem_size=1000000;Query OK, 0 rows affected (0.00 sec)mysql> select * from information_schema.optimizer_trace\G*************************** 1. row *************************** QUERY: select count(*) from rp_loginsummary_detail TRACE: { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select count(0) AS `count(*)` from `rp_loginsummary_detail`" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "table_dependencies": [ { "table": "`rp_loginsummary_detail`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "rows_estimation": [ { "table": "`rp_loginsummary_detail`", "table_scan": { "rows": 400044, "cost": 1826.5 } /* table_scan */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`rp_loginsummary_detail`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 400044, "access_type": "scan", "resulting_rows": 400044, "cost": 41831, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "condition_filtering_pct": 100, "rows_for_plan": 400044, "cost_for_plan": 41831, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`rp_loginsummary_detail`", "attached": null } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "optimizing_distinct_group_by_order_by": { } /* optimizing_distinct_group_by_order_by */ }, { "finalizing_table_conditions": [ ] /* finalizing_table_conditions */ }, { "refine_plan": [ { "table": "`rp_loginsummary_detail`" } ] /* refine_plan */ }, { "considering_tmp_tables": [ ] /* considering_tmp_tables */ } ] /* steps */ } /* join_optimization */ }, { "join_execution": { "select#": 1, "steps": [ ] /* steps */ } /* join_execution */ } ] /* steps */}MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0 INSUFFICIENT_PRIVILEGES: 01 row in set (0.00 sec)一个综合案例
通过 explain 说明执行计划的执行顺序。
执行顺序
1（id = 4）、【select id, name from t2】：select_type 为union，说明id=4的select是union里面的第二个select。
2（id = 3）、【select id, name from t1 where address = ‘11’】：因为是在from语句中包含的子查询所以被标记为DERIVED（衍生），where address = ‘11’ 通过复合索引idx_name_email_address就能检索到，所以type为index。
3（id = 2）、【select id from t3】：因为是在select中包含的子查询所以被标记为SUBQUERY。
4（id = 1）、【select d1.name, … d2 from … d1】：select_type为PRIMARY表示该查询为最外层查询，table列被标记为 “derived3”表示查询结果来自于一个衍生表（id = 3 的select结果）。
5（id = NULL）、【 … union … 】：代表从union的临时表中读取行的阶段，table列的 “union 1, 4”表示用id=1 和 id=4 的select结果进行union操作。
MySQL 如何获取 rownum
不同于 oracle 的 rownum，MySQL 不太直接提供这样的字段，但是可以用变量的方法添加伪自增序列，语法格式为:
SELECT @rownum:=@rownum+1 AS rownum, table_name.* FROM (SELECT @rownum:=0) r, table_name ;mysql> SELECT @rownum:=@rownum+1 AS rownum, user.id ,userName,passWord from (SELECT @rownum:=0) r, user;+--------+------+----------+----------------------------------+| rownum | id | userName | passWord |+--------+------+----------+----------------------------------+| 1 | 6 | faqrobot | 8B6909DDF65CC83386C478D3008AF4601 || 2 | 3 | forbid | 8B6909DDF65CC83386C478D3008AF4601 || 3 | 4 | forbiden | 8B6909DDF65CC83386C478D3008AF4601 || 4 | -1 | iyunwen | 8B6909DDF65CC83386C478D3008AF4601 || 5 | 5 | monitor | 8B6909DDF65CC83386C478D3008AF4601 || 6 | 1 | roboto | 8B6909DDF65CC83386C478D3008AF4601 || 7 | 2 | roboten | 8B6909DDF65CC83386C478D3008AF4601 || 8 | -100 | sys | 8B6909DDF65CC83386C478D3008AF4601 |+--------+------+----------+----------------------------------+8 rows in set, 2 warnings (0.00 sec)文章综合参考自以下链接：
https://blog.csdn.net/wuseyukui/article/details/71512793?spm=1001.2014.3001.5501
（版权归原作者所有，侵删）
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~