每次提交一次SQL给MySQL，MySQL内核里的查询优化器，都会针对这个SQL语句的语义去生成一个执行计划，这个执行计划就代表了，它怎么去查各个表，用哪些索引，如何进行排序，如何进行分组，到底怎么能把你想要的东西查出来。

执行计划查表的访问方式

type

  1. system: 表中只有一行记录 的查表方式
  2. ------------------------------------------------------------------------------------------------
  3. const: 直接是[主键查询(聚簇索引)] 或者 [唯一索引+聚簇索引回源] 的查表方式
  4. ------------------------------------------------------------------------------------------------
  5. eq_ref: 唯一性索引扫描，表中只有一条记录于之匹配 的查表方式
  6. ------------------------------------------------------------------------------------------------
  7. ref: [普通二级索引] 或者 [满足最左侧列索引原则的联合索引]  或者 [唯一索引或主键索引限定了 is null/is not null] 的查表方式
  8. ------------------------------------------------------------------------------------------------
  9. ref_or_null: [普通二级索引，且限定了 index is null/ is not null] 的查表方式
  10. ------------------------------------------------------------------------------------------------
  11. range: [普通二级索引的范围条件查询 index => x and index <= x] 的查表方式
  12. ------------------------------------------------------------------------------------------------
  13. index: 只需要查询非聚簇索引树的叶子节点就能得到结果数据，不需要进行回表，且没有用到索引（需要遍历非聚簇索引树,但不需要进行回表操作）的 查表方式
  14. ------------------------------------------------------------------------------------------------
  15. all: 需要进行全表查询，至少得聚簇索引树的遍历 的查表方式
  16. ------------------------------------------------------------------------------------------------
  18. 整体type查表级别：
  19. system > const > eq_ref > ref > ref_or_null > range > index > all

多表关联方式

内连接

inner join
两个表里的数据必须完全能关联上，才能返回相关结果。

左外连接

left join
左侧表(驱动表)里的某条数据，如果在右侧表（被驱动表）里关联不到任何数据，也需要把左侧表的这个数据返回回来，右侧表字段的结果用 NULL 值表示。

右外连接

right join
在右侧表里如果关联不到左侧表里的任何数据，也需要把右侧表的数据返回回来，左侧表字段的结果用 NULL 值表示。

多表关联的SQL语句是如何查询的

比如针对两张表，商品表(goods)和商品明细表(goods_detail)

  1. good
  2. +--------------------------------+
  3. | id | name | price | cost | ... |
  4. +--------------------------------+
  6. good_detail
  7. +----------------------------------+
  8. | id | parent_id | capacity | type |
  9. +----------------------------------+
  11. 如果多表查询，
  12. select good.id, good.name,good.price, good.cost, detail.id as detailId, detail.capacity
  13. from goods good, goods_detail detail
  14. where good.name = '奶茶' and good.id = detail.parent_id and type = '瓶装';
  16. 此时，两张表就有一些别名定义
  17. goods 商品表 --> 驱动表
  18. goods_detail 商品明细表 --> 被驱动表
  20. 嵌套循环关联(nested-loop join)
  21. mysql内核在查询的时候，首先是先从goods表里面查到相关的结果数据，然后得到good.id的具体数值范围(比如212，245)，然后再将从goods表查到的结果数据跟goods_detail表的parent_id做关联，嵌套查询good_detail表。
  22. goods_detail表会在嵌套循环里面，遍历从goods表中得到的结果数据Id的值，然后再增加对type字段的条件筛选，最后筛选出结果数据。
  24. 嵌套循环关联：
  25. 简单来说，假设有两个表一起执行关联，此时会现在一个驱动表里根据他的where筛选条件找出一波数据，比如10条数据。
  27. 然后再从这10条数据里走一波循环，让每条数据都到另外一个被驱动表里去根据ON连接条件和WHERE里的被驱动表筛选条件去查找数据，找出来数据就进行关联。
  29. 所以，如果在驱动表里查到10条数据，那么就要到被驱动表里去查询10次。
  30. 如果是三个表进行关联，从表1查到10条数据，从表2查到3条数据，那查询表3就需要循环 10*3=30次。
  32. ### 多表查询可能存在的问题
  33. 如果驱动表和被驱动表没有合理的设置索引的话，可能会导致每个表都需要进行全表查询，又因为嵌套循环关联的存在，被驱动表就会有多次循环遍历全表的操作，这种做法会极大影响查询速度。
  34. 所以通常针对多表查询语句，应尽量给每个表都加上索引，索引要确保从驱动表里查询也是通过索引去查找的，接着被驱动表的查询也可以通过索引去查找，只有做到这点，多表的关联语句的性能才能提高。

MySQL是如何根据成本优化选择执行计划的

MySQL成本

  1. IO成本： 首先数据是存在磁盘里的，需要从磁盘里把数据读到内存当中，这个过程的成本就是IO成本。而且MySQL里都是一页一页读的，读一页的成本约定为1.0。
  2. CPU成本：拿到的数据，需要对数据进行一些运算，比如验证是否符合搜索条件，或者做分组排序的事情，这些事情会耗费CPU资源，属于CPU成本。一般约定读取和检测一条数据是否符合条件的成本是0.2。
  1. select * from table where x1=xx and x2=xx;
  3. 此时假设针对x1和x2建立了两个索引，此时SQL在查询就可能用到这两个索引，也就是possible keys;

单表—全表扫描成本

  1. 接着针对这个SQL计算一下全表扫描的成本，如果是全表扫描的话，需要先从磁盘IO把聚簇索引里的叶子节点上的数据页一页一页都读到内存里，有多少的数据页就得耗费多少IO成本，接着对内存里的每条数据都判断是否符合搜索条件，有多少条数据就要耗费多少的CPU成本。
  3. 可以通过命令计算成本信息
  4. show table status like "表名";
  6. 可以查到rows和data_length信息。对innodb来说，rows是估计值。
  7. rows就是表里的记录数，data_length就是表的聚簇索引的字节数大小。
  9. 计算表有多少数据页： data_length / 1024 / 16
  10. --(data_length/1024 就是以kb为单位，然后16kb为一个数据页的大小)
  12. 此时知道了数据页的数量 和 rows记录数，就可以计算全表扫描的成本了。
  14. IO成本 = 数据页数量 * 1.0 + 微调值
  15. CPU成本 = rows记录数 * 0.2 + 微调值
  16. 总成本 = IO成本 + CPU成本
  18. 假设数据页有100个，记录数有2万条。
  19. 则总成本 = 100 * 1.0 + 20000 * 0.2 = 4100

单表—普通索引扫描成本

  1. 使用普通索引访问数据，需要两步。一步是从二级索引查询一波数据，再根据这波数据的主键去聚簇索引回表查询。
  3. 二级索引的IO成本
  4. 所以计算成本当中，需要现在二级索引里面根据条件查询仪表工数据的IO成本，查询的一般为一个范围里面的数据，假设认为这个范围的数据就等同于一个数据页，此时对应的IO成本就可以预估为1*1.0 = 1，或者 n * 1.0 =n,基本就是个位数的级别。
  6. 二级索引的CPU成本
  7. 假设从二级索引里读取的符合条件的数据有100条，此时CPU成本就是 100 * 0.2 + 微调值 = 20。
  9. 聚簇索引的IO成本
  10. 接着因为在二级索引里搜索到的数据是100条，然后通过IO成本最多回表到聚簇索引访问的100个数据页，就可以拿到100条数据的完整值了。所以IO成本最多为 100 * 1.0 + 微调值 = 100。
  12. 聚簇索引的CPU成本
  13. 查到100条的数据，所以CPU成本为 100 * 0.2 + 微调值 = 20。
  15. 所以总的成本值 = 1 + 20 + 100 + 20 = 141。

多表关联—计算成本的案例分析

  1. select * from t1
  2. join t2 on t1.x1 = t2.x1
  3. where t1.x2=xxx and t1.x3=xxx and t2.x4=xxx and t2.x5=xxx;
  5.     一般来说，会先选择一个驱动表，比如t1作为驱动表，此时就需要根据t1.x2=xxx 和 t1.x3=xxx 这个条件去从表里查询一波符合条件的数据出来。如果x2和x3字段都建了索引，此时就会使用上面的扫描成本的方法计算成本。
  6.     mysql会计算针对表t1的全表扫描和不同索引的成本，选择一个针对表t1的最佳访问方式，用最低成本从表t1查到符合条件的数据。
  8.     接着再根据这波数据去t2表里查数据，按照连接条件t1.x1 = t2.x1去查，同时要符合t2.x4=xxx 和 t2.x5=xxx两个条件去查。
  9.   mysql会计算针对表t2的全表扫描以及基于x4,x5,x1几个字段不同索引访问的成本，挑选一个成本最低的方式，然后从表t2中把数据给查找出来。
  11.   以上的步骤就完成了多表关联。

MySQL基于各种规则优化执行计划

mysql在执行sql语句的时候，可能会对我们编写的sql语句进行优化。

  1. ##常量替换
  2. 例如 select * from t1 where i = 5 and j > i;
  3. 替换为 select * from t1 where i = 5 and j > 5; 
  5. 例如 select * from t1 where x= 5 and y = x and y = z;
  6. 替换为 select * from t1 where  x= 5 and y = 5 and y = 5; 
  8. 例如 select * from t1 join t2 on t1.x1=t2=x1 and t1.id = 1;
  9. 替换为 select t1表中id=1的那行数据的各个字段的常量值, t2.* from t1 join t2 on t1表里x1字段的常量值 = t2.x1;
  11. ##子查询优化
  12. select * from t1 where x1 = (select x1 from t2 where id=xxx);
  13. 在上面的sql语句执行的时候，会被拆分为两个步骤：
  14. （1）先执行子查询，也就是 select x1 from t2 where id =xxx;根据主键定位出一条数据的x1字段的值。
  15. （2）接着再执行 select * from t1 where x1=子查询的结果值 这个sql语句。
  17. select * from t1 where x1 in (select x2 from t2 where x3 = xxx);
  18. (1) 先执行子查询，也就是 select x2 from t2 where x3 =xxx, 然后把查出来的数据都写入一个临时表里，也可以叫做物化表（把中间的结果集进行物化）。
  19. （2）物化表可能会基于memory存储引擎来通过内存存放，如果结果集太大，则可能采用普通的b+树聚簇索引的方式放在磁盘里。但是无论如何，这个物化表都会建立索引。
  20. （3）此时会有两种方式查询最后的结果集。
  21. 第一种：全表扫描t1表，对每条数据的x1值都去物化表里根据索引快速查找一下是否在这个物化表里。如果是，就符合条件，返回结果。
  22. 第二种：假设t1表数据量10万条，而物化表的数据量只有500条，那么此时完全可以改成全表扫描物化表，对每个数据值都到t1表里根据x1这个字段的索引进行查找，查找物化表的这个值是否在t1表的x1的索引树里，如果在，就符合条件，返回结果。
  24. ## semi join 半连接
  25. 假设子查询语句： select * from t1 where x1 in (select x2 from t2 where x3=xxx);
  26. 转为半连接： select t1.* from t1 semi join t2 on t1.x1=t2.x2 and t2.x3=xxx;
  27. 此时对于t1来说，只要在t2表里查找到符合 t1=x1=t2.x2 和 t2.x3=xxx的两个条件的数据，就可以把t1表的数据筛选出来了。

在互联网公司，比较崇尚的是尽量写简单的SQL，复杂的逻辑用Java系统来实现就可以了。 SQL能单表查询就不要多表关联，能多表关联就尽量别写子查询，能写几十行的SQL就别写几百行的SQL。多考虑用Java代码在内存里实现一些数据的复杂计算逻辑，而不是放在SQL里做。

透彻研究通过explain命令得到的SQL执行计划

  1. |+-----------------------------------------------------------------------------------------------------------------------+|
  2. |    id    |    select_type    |    table    |    partitions    |    type    |    possible_keys    |    key     |    key_len    |    ref      |    rows    |    filtered    |    extra        |
  3. |    1        |        SIMPLE        |    NULL    |        NULL            |    NULL    |            NULL            |    NULL |    NULL      | NULL    |    NULL    |        NULL        |    NULL        |
  4. |+-----------------------------------------------------------------------------------------------------------------------+|
  6. id：    每一条执行计划都有一个唯一的id。
  7. -------------------------------------------------------------------------------------------------
  8. select_type:    这一条执行计划对应的查询是什么查询类型。
  9. -------------------------------------------------------------------------------------------------
  10. table：表名，意思是要查询哪个表。
  11. -------------------------------------------------------------------------------------------------
  12. partitions：表分区。
  13. -------------------------------------------------------------------------------------------------
  14. type：(关键) 针对当前表的访问方法：all>index>range>ref_or_null>ref>const
  15. (全表扫描>非聚簇索引遍历>普级索引（is null/is not null）回表 > 普通索引回表 > 主键或唯一索引回表)。
  16. -------------------------------------------------------------------------------------------------
  17. possible_keys: (关键)与type结合，确定有哪些索引是可以选择的，可以使用的。
  18. -------------------------------------------------------------------------------------------------
  19. key：在possible_keys里实际选择的索引。
  20. -------------------------------------------------------------------------------------------------
  21. key_len: 索引的长度。
  22. -------------------------------------------------------------------------------------------------
  23. ref: 就是使用某个字段的索引进行等值匹配搜索的时候，跟索引列进行等值匹配的目标值的一些信息。
  24. -------------------------------------------------------------------------------------------------
  25. rows：预估通过索引或者别的方式访问这个表的时候，大概可能会读取多少条数据。
  26. -------------------------------------------------------------------------------------------------
  27. filtered：经过搜索条件过滤之后的剩余数据的百分比。
  28. -------------------------------------------------------------------------------------------------
  29. extra：额外信息。
  30. -------------------------------------------------------------------------------------------------

各种查询执行计划分析

单表查询：explain select * from table;

  1. +-------------------------------------------------------------------------------------------------------------------+
  2. |    id    | select_type    |    table    |    partitions    |    type    |    possible_keys    |    key    |    key_len    |    ref    |    rows    |    filtered    |    extra    |
  3. |    1        |    SIMPLE            |    table    |    NULL                |    ALL        |    NULL                    |    NULL|    NULL        |    NULL|    3457    |    100.00        |    NULL    |
  4. +-------------------------------------------------------------------------------------------------------------------+
  1. id:    查询标识。
  2. select_type:    查询类型为普通查询。
  3. table：    查询的表名为table。
  4. type：    本次查表为全表扫描。
  5. possible_keys: 没有使用到索引查表，所以possible_keys为null。
  6. key：    没有使用到索引查表，所以key为null。
  7. key_len: 没有使用到索引查表，所以长度为null。
  8. rows：本次扫描表（全表扫描）扫描的结果数据有3457条。
  9. filtered：没有where过滤条件，所以筛选出来的结果是100%。
  10. extra：没有相关额外信息。

多表查询：explain select * from table1 join table2;

  1. +-------------------------------------------------------------------------------------------------------------------+
  2. |    id    | select_type    |    table    |    partitions    |    type    |    possible_keys    |    key    |    key_len    |    ref    |    rows    |    filtered    |    extra    |
  3. |    1        |    SIMPLE            |    table1|    NULL                |    ALL        |    NULL                    |    NULL|    NULL        |    NULL|    3457    |    100.00        |    NULL    |
  4. |    1        |    SIMPLE            |    table2|    NULL                |    ALL        |    NULL                    |    NULL|    NULL        |    NULL|    4568    |    100.00        |    Using join buffer(Block Nested Loop)    |
  5. +-------------------------------------------------------------------------------------------------------------------+
  1. id:    查询标识。两条执行计划对应同一条select，所以id是一样的。
  2. select_type:    查询类型为普通查询。
  3. table：    查询的表名为table1和table2。
  4. type：    本次查表为全表扫描。
  5. possible_keys: 没有使用到索引查表，所以possible_keys为null。
  6. key：    没有使用到索引查表，所以key为null。
  7. key_len: 没有使用到索引查表，所以长度为null。
  8. rows：本次扫描表（全表扫描）扫描的结果数据有3457条和4568条。
  9. filtered：没有where过滤条件，所以筛选出来的结果是100%。
  10. extra：对于table2的执行计划，为Nested Loop,表示为嵌套循环的访问方式。

多表查询： explain select * from table1 inner join table2 on table1.id=table2.id;

  1. +---------------------------------------------------------------------------------------------------------------------------------------------------------------+
  2. |   id  | select_type   |   table   |   partitions  |   type    |   possible_keys   |   key    |   key_len |   ref           | rows |   filtered    |   extra   |
  3. |   1   |   SIMPLE      |   table1  |   NULL        |   ALL     |   PRIMARY         |   NULL   |   NULL    |   NULL          | 3457 |   100.00      |   NULL    |
  4. |   1   |   SIMPLE      |   table2  |   NULL        |   eq_ref  |   PRIMARY         |   PRIMARY|   10      |test_db.table1.id| 1    |   100.00      |   NULL    |
  5. +---------------------------------------------------------------------------------------------------------------------------------------------------------------+
  1. id:    查询标识。两条执行计划对应同一条select，所以id是一样的。
  2. select_type:    查询类型为普通查询。
  3. table：    查询的表名为table1和table2。
  4. type：    ALL--对table1查表为全表扫描，eq_ref--对table2查表是根据table1的主键结果基于主键/唯一索引进行扫描。
  5. possible_keys: PRIMARY -- 主键。
  6. key：    table1没有用到索引，table2用到主键索引。
  7. key_len： table1没有用到索引，table2用到主键索引。
  8. rows：本次扫描表（全表扫描）扫描的结果数据有3457条和1条。
  9. filtered：没有where过滤条件，所以筛选出来的结果是100%。
  10. extra：---。

子查询： explain select * from table1 where x1 in (select x1 from table2) or x3 ='xxxx';

  1. +-------------------------------------------------------------------------------------------------------------------------------+
  2. |    id    | select_type    |    table    |    partitions    |    type      |    possible_keys    |    key        |    key_len    |    ref    |    rows    |    filtered    |    extra          |
  3. |    1        |    PRIMARY            |    table1|    NULL                |    ALL          |    index_x3            |    NULL    |    NULL        |    NULL|    3457    |    100.00        |    Using where    |
  4. |    2        |    SUBQUERY      |    table2|    NULL                |    INDEX        |    index_x1          |    index_x1|    507          |    NULL|    4687    |    100.00        |    Using index    |
  5. +-------------------------------------------------------------------------------------------------------------------------------+
  1. ### id = 1
  2. id:    查询标识。
  3. select_type:    查询类型为PRIMARY--主查询。
  4. table：    查询的表名为table1。
  5. type：    ALL -- 本次查表为全表扫描，虽然x3为索引字段，但是mysql通过成本分析后认为全表扫描的成本更低。
  6. possible_keys: 可能使用到的索引字段信息index_x3。
  7. key：    使用到的索引字段，这里为NULL，说明没用到索引。
  8. key_len: 没有使用到索引查表，所以长度为null。
  9. rows：本次扫描表（全表扫描）扫描的结果数据有3457条。
  10. filtered：没有where过滤条件，所以筛选出来的结果是100%。
  11. extra：Using where -- 有相关的条件筛选操作。
  13. ### id = 2
  14. id:    查询标识。
  15. select_type:    查询类型为SUBQUERY--子查询。
  16. table：    查询的表名为table2。
  17. type：    INDEX -- 说明只要遍历二级索引树的叶子节点就能得到查询结果。
  18. possible_keys: 可能使用到的索引字段信息 index_x1。
  19. key：    使用到的索引字段，这里为index_x1。
  20. key_len: 使用到的索引字段，计算出索引数据的最大长度信息。
  21. rows：本次扫描表的结果数据有4687条。
  22. filtered：没有where过滤条件，所以筛选出来的结果是100%。
  23. extra：Using index -- 使用二级索引的B+树可以得到结果数据。

UNION： explain select * from table1 union select * from table2;

  1. +-------------------------------------------------------------------------------------------------------------------------------+
  2. |    id    | select_type    |    table    |    partitions    |    type      |    possible_keys    |    key        |    key_len    |    ref    |    rows    |    filtered    |    extra          |
  3. |    1        |    PRIMARY            |    table1|    NULL                |    ALL          |    NULL                    |    NULL    |    NULL        |    NULL|    3457    |    100.00        |    NULL                |
  4. |    2        |    UNION              |    table2|    NULL                |    ALL            |    NULL                  |    NULL        |    NUL          |    NULL|    4687    |    100.00        |    NULL                |
  5. |    NULL|    UNION_RESULT|    <union1,2>|    NULL        |    ALL            |    NULL                  |    NULL        |    NUL          |    NULL|    NULL    |    NULL            | Using temporary|            
  6. +-------------------------------------------------------------------------------------------------------------------------------+
  1. ### id = NULL
  2. id: 查询标识。
  3. select_type:    查询类型为UNION_RESULT -- 针对union临时创建的临时表。
  4. table：  <union1,2> -- 临时的表名。
  5. type：   ALL -- 本次查表为全表扫描。
  6. possible_keys: ---。
  7. key：---。
  8. key_len: ---。
  9. rows：---。
  10. filtered：---。
  11. extra：Using temporary -- 使用临时表进行去重。

复杂查询：explain select * from t1 where x1 in (select x1 from t2 where x1 ='xxx' union select x1 from t1 where x1='xxx');

  1. +-----------------------------------------------------------------------------------------------------------------------------------------------------------+
  2. |    id    | select_type                |    table            |    partitions    |    type      |    possible_keys    |    key                |    key_len    |    ref     |    rows    |    filtered    |    extra                      |
  3. |    1        |    PRIMARY                        |    t1                |    NULL                |    ALL          |    NULL                    |    NULL            |    NULL        |    NULL |    3457    |    100.00        |    Using where                |
  4. |    2        |    DEPENDENT SUBQUERY|    t2                |    NULL                |    ref            |    index_x1            |    index_x1        |    899          |    const|    59        |    100.00        |    Using where; Using index|
  5. |    3     |    DEPENDENT UNION   |    t1                |    NULL                |    ref            |    index_x1            |    index_x1        |    899          |    const|    45        |    100.00        | Using where; Using index|    
  6. |    NULL|    UNION RESULT      |    <union2,3>|    NULL                |    ALL            |    NULL                    |    NULL                |    NULL      |    NULL |    NULL  |    NULL          | Using temporay    |    
  7. +-----------------------------------------------------------------------------------------------------------------------------------------------------------+
  1. ### id = 2
  2. id: 查询标识。
  3. select_type:    查询类型为DEPENDENT SUBQUERY -- 内嵌的子查询。
  4. table：  查询的表为t2表。
  5. type：   ref -- 普通二级索引 或 唯一索引(is null/is not null) -- 不需要回表。
  6. possible_keys: index_x1 -- 可能使用到的索引字段。
  7. key：index_x1 -- 使用到的索引字段。
  8. key_len: 使用到的索引字段长度。
  9. rows：本次扫描查到的结果数据条数。
  10. filtered：过滤百分比。
  11. extra：Using where -- where 条件过滤 Using index -- 二级索引树叶子节点遍历。
  13. ### id = 3
  14. id: 查询标识。
  15. select_type:    查询类型为DEPENDENT UNION -- 内嵌的子查询的union关联。
  16. table：  查询的表为t1表。
  17. type：   ref -- 普通二级索引 或 唯一索引(is null/is not null) -- 不需要回表。
  18. possible_keys: index_x1 -- 可能使用到的索引字段。
  19. key：index_x1 -- 使用到的索引字段。
  20. key_len: 使用到的索引字段长度。
  21. rows：本次扫描查到的结果数据条数。
  22. filtered：过滤百分比。
  23. extra：Using where -- where 条件过滤 Using index -- 二级索引树叶子节点遍历。
  25. ### id = NULL
  26. id: 查询标识。
  27. select_type:    查询类型为UNION RESULT -- 针对union临时创建的临时表。
  28. table：  <union1,2> -- 临时的表名。
  29. type：   ALL -- 本次查表为全表扫描。
  30. possible_keys: ---。
  31. key：---。
  32. key_len: ---。
  33. rows：---。
  34. filtered：---。
  35. extra：Using temporary -- 使用临时表进行去重。

复杂查询：explain select * from (select x1,count(*) as cnt from t1 group by x1) as_t1 where cnt > 10;

  1. +-----------------------------------------------------------------------------------------------------------------------------------+
  2. |    id    | select_type    |    table            |    partitions    |    type      |    possible_keys    |    key        |    key_len    |    ref    |    rows    |    filtered    |    extra          |
  3. |    1        |    PRIMARY            |    <derived2>|    NULL                |    ALL          |    NULL                    |    NULL    |    NULL        |    NULL|    3457    |    33.33          |    Using where    |
  4. |    2        |    DERIVED        |    t1        |    NULL                |    index        |    index_x1            |    index_x1|    899          |    NULL|    3568    |    100.00        |    Using index |
  5. +-----------------------------------------------------------------------------------------------------------------------------------+
  1. ### id = 1
  2. id: 查询标识。
  3. select_type:    查询类型为PRIMARY -- 主表。
  4. table：  <derived2> -- 派生出来的临时表。
  5. type：   ALL -- 全表扫描。
  6. possible_keys: 查询中没有索引可以使用。
  7. key：查询中没有索引可以使用。
  8. key_len: 查询中没有索引可以使用。
  9. rows：本次扫描查到的结果数据条数。
  10. filtered：过滤百分比。
  11. extra：Using where -- where 条件过滤 。
  13. ### id = 2
  14. id: 查询标识。
  15. select_type:    查询类型为DERIVED -- 针对子查询执行后的结果集会物化为一个内部临时表，然后外层查询根据这个临时的物化表进行查询。
  16. table：  查询的表为t1表。
  17. type：   index -- 只需要遍历二级索引树的叶子节点就能得到结果数据。
  18. possible_keys: index_x1 -- 可能使用到的索引字段。
  19. key：index_x1 -- 使用到的索引字段。
  20. key_len: 使用到的索引字段长度。
  21. rows：本次扫描查到的结果数据条数。
  22. filtered：过滤百分比。
  23. extra：Using index -- 二级索引树叶子节点遍历。

针对执行计划的各类字段信息的详细说明

select_type

  1. select_type 的 几种类型
  2. - SIMPLE: 单表 或者 多表关联
  3. - PRIMARY: union 查询的主表
  4. - SUBQUERY: 子查询的表
  5. - UNION: union 查询的次表
  6. - UNION RESULT: union 生成的临时表
  7. - DEPENDENT SUBQUERY: 子查询内部的union的主表
  8. -    DEPENTENT UNION: 子查询内部的union的次表
  9. - DERIVED: 派生的临时表

type

  1. type 的 几种类型
  2. - const: 主键查询，或者唯一索引查询
  3. - eq_ref: 针对主表的全表扫描的每条数据，都去关联表里基于主键或者唯一索引去进行等值匹配，此时在关联表的聚簇                    索引里根据主键快速查找。
  4.                     在连接查询时，针对被驱动表如果基于主键或者唯一索引进行等值匹配的话，它的查询方式就是eq_ref
  5. - ref： 普通二级索引查询 或者 唯一索引(is null / is not null) 的情况
  6. - ref_or_null : 普通二级索引(is null / is not null) 的情况
  7. - index_merge: 在一些特殊场景下针对单表查询可能基于多个索引提取数据后进行合并的情况
  8. - range： 普通二级索引的范围查询
  9. - index： 查询条件未用到索引，但是可以通过遍历二级索引树拿到结果数据。

possible_keys

  1. 顾名思义，其实就是在针对一个表进行查询的时候有哪些潜在可以使用的索引。
  3. mysql会通过成本优化，去估算使用possible_keys里面的索引的查询成本，选择成本最低的一种方式，最终选择索引字段，就是key这个字段的值。
  5. key_len: 其实就是当你在key里面选择使用某个索引之后，那个索引的最大值的长度是多少。

ref

  1. 当你的查询方式是索引等值匹配的时候，比如const/ref/eq_ref/ref_or_null 这些方式的时候，
  2. 此时执行计划的ref字段会告诉你：
  3. 你跟索引列等值匹配的是什么？ 是等值匹配一个常量值(const)、还是等值匹配另外一个字段的值(t1.id?)

rows 和 filtered

  1. rows: 使用你的查询方式，会查出来多少条数据。
  2. filtered：查询方式查出来的数据，再用上其他的不再索引范围里的查询条件，又会过滤出来百分之几的数据。
  4. 最终的结果数据值 = rows * filtered

extra

  1. - Using where： 针对一个表进行查询，但是却没有用到索引，或者是 
  2.                                 用到了索引查找，但除此之外还用到了非索引的查询条件。
  3. - Using index：本次查询只是涉及到二级索引的字段，不需要进行回表操作。
  4. - Using index condition: 通过二级索引查到的数据，不用回表，但还有其他的条件判断。
  5. - Using join buffer: 多表关联，如果关联的字段不是索引字段关联，
  6.                                          此时为了提升性能，会使用join buffer的内存技术提升关联性能。
  7. - Using filesort：排序没有用到索引进行排序，需要基于内存或者磁盘文件进行排序，影响性能，性能极低。
  8. - Using temprory：针对group by, union, distinct 等语句，没法直接利用索引进行分组聚合，此时就只能基于临时表来完成，此时会有大量的磁盘操作，性能也是极低的。