EXPLAIN

执行计划：
image.png

id

含有子查询的时候，表明各语句执行的先后顺序，如果数字相同，则按照先后顺序执行，如果为null，则代表是结果集，不需要查询。

select_type

分为 simple（简单查询）、subquery（子查询）、drived（衍生表，from 列表中有子查询），union（联合查询）等。

table

通常是表名，或者表的别名，或者一个为查询产生临时表的标示符（如派生表、子查询、集合）。

type

扫描类型（性能从高到低）：

  • system：表中只有一行数据或者该表为空表，这个方式通常出现再 myisam 和 memory 的引擎中，innodb 一般会展示为 all 或 index。
  • const：使用唯一索引或者主键，返回记录一定是 1 行记录的等值 where 条件时。
  • eq_ref：出现在要连接过个表的查询计划中，驱动表只返回一行数据，且这行数据是第二个表的主键或者唯一索引，且必须为 not null，唯一索引和主键是多列时，只有所有的列都用作比较时才会出现 eq_ref。
  • ref：不像eq_ref那样要求连接顺序，也没有主键和唯一索引的要求，只要使用相等条件检索时就可能出现。常见于辅助索引的等值查找；多列主键、唯一索引中，使用第一个列之外的列作为等值查找也会出现，总之，返回数据不唯一的等值查找就可能出现。
  • ref_or_null：与ref方法类似，只是增加了null值的比较。实际用的不多。
  • range：以范围的形式扫描数据，常见于使用 >, <, is null, between, in, like 等运算符的查询中。
  • index_merge：表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取所有索引，性能可能大部分时间都不如range
  • index：索引全表扫描，把索引从头到尾扫一遍，常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。
  • all：这个就是全表扫描数据文件，然后再在 server 层进行过滤返回符合要求的记录。
  • null：不需要访问索引或表就可以得到结果。

range、index、all 需要添加合适的索引。
image.png
由左至右，性能由最差到最好：
ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行
index：Full Index Scan，index与ALL区别为index类型只遍历索引树
range：索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行，常见于between、<、>等的查询
ref：非唯一性索引扫描，返回匹配某个单独值的所有行。常见于使用非唯一索引即唯一索引的非唯一前缀进行的查找
eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
const、system：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量
NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引。

possible_keys

本次查询可能会用到的索引

key

实际使用到的索引。

key_len

键长

ref

使用的索引列用的查找方式：

  • const：使用常数等值进行查询。
  • func：使用了表达式或函数。

    rows

    预估需要扫描的行数，其中如果行数到达表总行数一定的比例的时候，就会不使用索引。

    filtered

    通过过滤条件之后对比总数的百分比。
    给出了一个百分比的值，这个百分比值和rows列的值一起使用，可以估计出那些将要和执行计划中的前一个表（前一个表就是指id列的值比当前表的id小的表）进行连接的行的数目。

    Extra

    using index

    本次查询使用了覆盖索引，直接通过索引就可以返回结果，无需进行回表。
    The column information is retrieved from the table using only information in the index tree without having to do an additional seek to read the actual row. This strategy can be used when the query uses only columns that are part of a single index.
    For InnoDB tables that have a user-defined clustered index, that index can be used even when Using index is absent from the Extra column. This is the case if type is index and key is PRIMARY.
    从表中仅使用索引树中的信息就能获取查询语句的列的信息, 而不必进行其他额外查找（seek）去读取实际的行记录。当查询的列是单个索引的部分的列时, 可以使用此策略。（简单的翻译就是：使用索引来直接获取列的数据，而不需回表）。对于具有用户定义的聚集索引的 InnoDB 表, 即使从Extra列中没有使用索引, 也可以使用该索引。如果type是index并且Key是主键, 则会出现这种情况。

    using where

    A WHERE clause is used to restrict which rows to match against the next table or send to the client. Unless you specifically intend to fetch or examine all rows from the table, you may have something wrong in your query if the Extra value is not Using where and the table join type is ALL or index.
    Using where has no direct counterpart in JSON-formatted output; the attached_condition property contains any WHERE condition used.
    where 子句用于限制与下一个表匹配的行记录或发送到客户端的行记录。除非您特意打算从表中提取或检查所有行,否则如果Extra值不是Using where并且表连接类型为ALL或index，则查询可能会出错。
    表示MySQL服务器在存储引擎收到记录后进行“后过滤”（Post-filter）,如果查询未能使用索引，Using where的作用只是提醒我们MySQL将用where子句来过滤结果集。这个一般发生在MySQL服务器，而不是存储引擎层。一般发生在不能走索引扫描的情况下或者走索引扫描，但是有些查询条件不在索引当中的情况下。
    后过滤，即先读取整行数据，再判断是否符合条件，符合保留，不符合丢弃。

    using index condition

    Tables are read by accessing index tuples and testing them first to determine whether to read full table rows. In this way, index information is used to defer (“push down”) reading full table rows unless it is necessary. See Section 8.2.1.5, “Index Condition Pushdown Optimization”.
    mysql 5.6时出现的新特性，基于ICP（Index Condition Pushdown），即如果你的查询条件里有部分可以走索引，那么则会先将条件推到底层的存储引擎层去做一部分过滤，以此减少查询的条数。因此基于 icp 的概念，在我们使用组合索引的场景不是很明确时，最好可以分别建立索引。
    在MySQL 5.6版本后加入的新特性（Index Condition Pushdown）;会先条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用 WHERE 子句中的其他条件去过滤这些数据行；

    using filesort

    当需要的排序和使用索引的排序不一致时，即无法通过索引排序，再获取结果之后，还需要对结果进行再一次的排序。
    MySQL中无法利用索引完成的排序操作称为“文件排序”。
    当我们试图对一个没有索引的字段进行排序时，就是filesoft。它跟文件没有任何关系，实际上是内部的一个快速排序。
    Using index condition : where condition contains indexed and non-indexed column and the optimizer will first resolve the indexed column and will look for the rows in the table for the other condition (index push down)
    Using where; Using index : ‘Using index’ meaning not doing the scan of entire table. ‘Using where’ may still do the table scan on non-indexed column but it will use if there is any indexed column in the where condition first more like using index condition
    Which is better? ‘Using where; Using index’ would be better then ‘Using index condition’ if query has index all covering.
    When ‘Column Extra’ says Using Index Condition, all columns in where condition are using index. If there are any columns out of index, then Column Extra say Using Where; Using Index (in this case, Mysql need look for in data row to apply where clause). It’s better ‘Using Index Condition’. https://dev.mysql.com/doc/refman/5.7/en/index-extensions.html
    参考：https://www.cnblogs.com/kerrycode/p/9909093.html

    优化经验

  1. 要对经常进行搜索，排序，分组的列创建索引。
  2. 考虑列基数（同一个列中的不重复的值的数量），列基数越大，效果越好，即区分度越高。
  3. 索引的数据类型尽可能的短，如果tinyint可以实现，就不要用Int
  4. 使用最左前缀。
  5. 不要建立过多的索引。
  6. insert的时候可以考虑使用批量插入。

  7. like的时候不要在初始位置使用通配符。

    Extra列

    出现以下情况时，考虑优化：
    1、using filesort 使用外部排序，而不是按照索引顺序排序，数据量少时通过内存排序，否则需要通过磁盘排序（需要添加合适的索引）
    2、Using temporary 创建一个临时表来存储数据，一般出现在对非索引的列集进行 group by 时 （需要添加合适的索引）
    3、Using where 通常是对全表/全索引进行扫描之后，再用 where 条件进行筛选查询出来的，通常 type 列为 all 或者 index.（需要添加合适的索引）
    4、Using index 表示当前的查询条件都能够从索引树当中获取，不需要进行回表查询，即（索引覆盖）说明性能还可以，需要和type列当中的 index 进行区分。如果同时出现了 using where 表明进行了索引被用来执行键值的查询，如果没有using where表明索引用来读取数据，而非查找，以上两种情况都是从 mysql 服务层完成的，无需再回表查询记录。
    特殊的 Using Index Condition 即 Index Condition Pushdown（ICP）。

    ICP

    在没有 ICP 之前，存储引擎根据索引去基表查找，然后将数据返回给 mysql server，mysql server 再根据 where 条件进行过滤。
    ICP 是在取出索引的同时，判断是否可以根据索引当中的列进行 where 条件过滤，将 where 条件的过滤放在了存储引擎。
    ICP 的执行步骤是：
    1、在存储引擎获取一条索引基础数据。
    2、存储引擎根据上面的数据，结合where条件，判断是否满足where条件，如果没有满足条件，回到第一步，筛选下一条数据，否则的话，进行下面的判断。
    3、对于满足下推条件的数据，存储引擎根据 B+ 树的 key，定位基表的行数据，并返回整行数据至 server 层。
    4、在 server 层筛选没有被下推到存储引擎层 where 条件，满足则使用，否则丢弃。
    image.png

    对比 Using Index

    如果extra当中出现Using Index Condition说明我们的查询结果返回集当中，既包含了索引列，也包含了非索引列，需要我们进行一次回表查询。
    如果extra当中出现了using where, using index 说明我们的查询条件通过索引限定和 where 过滤已经能够查询到结果，不需要进行回表查询。
    所以使用 using where,using index 要比使用 using index condition 好。
    举例，同一索引 idx_c1_c2：
    select * from t1 where c1 = 1 and c2 > 4; 使用 using index condition。
    select c1,c2 from t1 where c1 = 1 and c2 >4; 使用的是 using where, using index。

    优化案例

    GROUP BY

    优化GROUP BY语句
    默认情况下，MySQL 对所有 GROUP BY col1，col2… 的字段进行排序。这与在查询中指定 ORDER BY col1,col2… 类似。因此，如果显式包括一个包含相同的列的 ORDER BY 子句，则对MySQL的实际执行性能没有什么影响。 如果查询包括 GROUP BY 但用户想要避免排序结果的消耗，则可以指定 ORDER By NULL 禁止排序，例如：

  8. explain select id, sum(moneys) from sales2 group by id \G

  9. explain select id, sum(moneys) from sales2 group by id order by null \G

你可以通过比较发现第一条语句会比第二句在Extra:里面多了Using filesort.而恰恰filesort是最耗时的。