explain执行计划中包含的信息如下：

  • id: 查询序列号
  • select_type: 查询类型
  • table: 表名或者别名
  • partitions: 匹配的分区
  • type: 访问类型
  • possible_keys: 可能用到的索引
  • key: 实际用到的索引
  • key_len: 索引长度
  • ref: 与索引比较的列
  • rows: 估算的行数
  • filtered: 按表条件筛选的行百分比

  • Extra: 额外信息

    select_type 常见类型及其含义

  • SIMPLE：不包含子查询或者 UNION 操作的查询

  • PRIMARY：查询中如果包含任何子查询，那么最外层的查询则被标记为 PRIMARY
  • SUBQUERY：子查询中第一个 SELECT
  • DEPENDENT SUBQUERY：子查询中的第一个 SELECT，取决于外部查询
  • UNION：UNION 操作的第二个或者之后的查询
  • DEPENDENT UNION：UNION 操作的第二个或者之后的查询,取决于外部查询
  • UNION RESULT：UNION 产生的结果集

  • DERIVED：出现在 FROM 字句中的子查询

    type常见类型及其含义

  • system：这是 const 类型的一个特例，只会出现在待查询的表只有一行数据的情况下

  • consts：常出现在主键或唯一索引与常量值进行比较的场景下，此时查询性能是最优的
  • eq_ref：当连接使用的是完整的索引并且是 PRIMARY KEY 或 UNIQUE NOT NULL INDEX 时使用它
  • ref：当连接使用的是前缀索引或连接条件不是 PRIMARY KEY 或 UNIQUE INDEX 时则使用它
  • ref_or_null：类似于 ref 类型的查询，但是附加了对 NULL 值列的查询
  • index_merge：该联接类型表示使用了索引进行合并优化
  • range：使用索引进行范围扫描，常见于 between、> 、< 这样的查询条件
  • index：索引连接类型与 ALL 相同，只是扫描的是索引树，通常出现在索引是该查询的覆盖索引的情况

  • ALL：全表扫描，效率最差的查找方式

    Extra列

    Extra 列主要用于显示额外的信息，常见信息及其含义如下：

  • Using where ：MySQL 服务器会在存储引擎检索行后再进行过滤

  • Using filesort：通常出现在 GROUP BY 或 ORDER BY 语句中，且排序或分组没有基于索引，此时需要使用文件在内存中进行排序，因为使用索引排序的性能好于使用文件排序，所以出现这种情况可以考虑通过添加索引进行优化
  • Using index：使用了覆盖索引进行查询，此时不需要访问表，从索引中就可以获取到所需的全部数据
  • Using index condition：查找使用了索引，但是需要回表查询数据
  • Using temporary：表示需要使用临时表来处理查询，常出现在 GROUP BY 或 ORDER BY 语句中

当一条sql语句提交给mysql数据库进行查询的时候需要经历以下几步

1、先在where解析这一步把当前的查询语句中的查询条件分解成每一个独立的条件单元
2、mysql会自动将sql拆分重组
3、然后where条件会在B-tree index这部分进行索引匹配，如果命中索引，就会定位到指定的table records位置。如果没有命中，则只能采用全部扫描的方式
4、根据当前查询字段返回对应的数据值

1.where or，可能会导致索引失效

使用or并不是一定会使索引失效，你需要看or左右两边的查询列是否命中相同的索引。
假设USER表中的user_id列有索引，age列没有索引。下面这条语句其实是命中索引的（据说是新版本的MySQL才可以，如果你使用的是老版本的MySQL，可以使用explain验证下）。

  1. select * from `user` where user_id = 1 or user_id = 2;

但是这条语句是无法命中索引的。

select * from `user` where user_id = 1 or age = 20;

假设age列也有索引的话，依然是无法命中索引的。

select * from `user` where user_id = 1 or age = 20;

尽量避免使用or语句，可以根据情况尽量使用union all或者in来代替，这两个语句的执行效率也比or好些。

2. 索引列使用了负向查询，可能会导致索引失效

负向查询包括：NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等。
其实负向查询并不绝对会索引失效，这要看MySQL优化器的判断，全表扫描或者走索引哪个成本低。

3. 索引字段可以为null，使用is null或is not null时，可能会导致索引失效

  1. null的列使索引/索引统计/值比较都更加复杂，对MySQL来说更难优化,并且需要额外一个字节的存储空间。
  2. null 这种类型MySQL内部需要进行特殊处理，增加数据库处理记录的复杂性；同等条件下，表中有较多空字段的时候，数据库的处理性能会降低很多。
  3. null值需要更多的存储空，无论是表还是索引中每行中的null的列都需要额外的空间来标识。
  4. 对null 的处理时候，只能采用is null或is not null，而不能采用=、in、<、<>、!=、not in这些操作符号。如：where name!=’shenjian’，如果存在name为null值的记录，查询结果就不会包含name为null值的记录。

    4. 索引列使用内置函数，一定会导致索引失效

    尽量在应用程序中进行计算和转换。

    4.1 隐式类型转换导致的索引失效

    比如下面语句中索引列 user_id为varchar类型，不会命中索引：
    select * from `user` where user_id = 12;
    这是因为MySQL做了隐式类型转换，调用函数将user_id做了转换。
    select * from `user` where CAST(user_id AS signed int) = 12;

4.2 隐式字符编码转换导致的索引失效

当两个表之间做关联查询时，如果两个表中关联的字段字符编码不一致的话，MySQL可能会调用CONVERT函数，将不同的字符编码进行隐式转换从而达到统一。作用到关联的字段时，就会导致索引失效。

5. 对索引列进行运算，一定会导致索引失效

运算如+，-，*，/等，如下：

select * from `user` where age - 1 = 10;

优化的话，要把运算放在值上，或者在应用程序中直接算好，比如：

select * from `user` where age = 10 - 1;

6. like通配符可能会导致索引失效

like查询以%开头时，会导致索引失效。解决办法有两种：

  • 将%移到后面，如：

    select * from `user` where `name` like '李%';

  • 利用覆盖索引来命中索引。

    select name from `user` where `name` like '%李%';

7. 联合索引中，where中索引列违背最左匹配原则，一定会导致索引失效

8. MySQL优化器的最终选择，不走索引