MySQL优化之执行计划

1、执行计划概念

执行计划指的是使用explain关键字可以模拟优化器执行SQL查询语句，从而指导MySQL是如何处理你的SQL语句的。
作用：分析查询语句或是表结构的性能瓶颈
语法：Explain+SQL语句

2、执行计划中各字段详情

ID
- 概念：select插叙的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
- 规则
  - id相同，执行顺序由上至下
  - id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
  - id相同又有不相同的，序列号大的会先执行，然后相同的从上到下执行
Select_type
- 查询的类别，主要用于区别普通查询、联合查询、子查询等的复杂查询
  - simple：简单的select查询，不包含子查询或者union
  - primary：查询中包含任何复杂的子部分，最外层查询则被标记
  - subquery：在select或者where列表中包含了子查询
  - derived：在from列表中包含子查询被标记为derive的 MySQL会递归执行这些子查询，把结果放到零时表里
  - union：若在第二个 select 中出现 union之后，则被标记为 union 若union包含在 from 子句的子查询中，外层 select 将被标记为 derived。
table
- 显示这一行的数据是关于那个表的
type
- 显示的是访问类型，是较为重要的一个指标
- 结果值从最好到最坏的依次是：system > const > eq_ref > ref > range > index > ALL
- 至少要达到range级别，要求是ref级别，最好是const级别
  - Const：表示值通过一次索引就找到了
  - Ref：表示非唯一性索引扫描（二级索引），返回匹配某个单独值的所有行
  - Range：只检索给定范围的行，使用一个索引来选择行。key 列显示使用了哪个索引
  - index：当查询的结果全为索引列的时候，虽然也是全部扫描，但是只查询的索引库，而没有去查询数据。
possible_key和key
- possible_key：表示可能使用的key索引
- key：表示实际用到的索引
key_len
- 表示索引中使用的字节数，可通过该列计算查询中使用的字节长度，在不损失精确的情况下，长度越短越好。
- key_len 显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len 是根据表定义计算而得，不是通过表内检索出的
- key_len 表示索引使用的字节数
- 根据这个值，就可以判断索引使用情况，特别是在组合索引的时候，判断所有的索引字段是否都被查询用到。
- char 和 varchar 跟字符编码也有密切的联系。(latin1 占用一个字节，gbk 占用两个字节，utf-8 占用三个字节)
ref
- 显示索引的哪一列被使用了，如果可能的话，是一个常量。哪些列或常量被用于查找索引列上的值
rows
- 根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。
Extra
- 包含不适合在其他列中显示但十分重要的额外信息

3、优化策略

尽量全值匹配
最佳左前缀法则-最左匹配
- 如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
不在索引上做任何操作
- 不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描。
范围条件放最后
- 存储引擎不能使用索引中范围条件右边的列。
覆盖索引尽量用
- 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select *。
不等于要慎用
- mysql 在使用不等于(!= 或者<>)的时候有可能无法使用索引会导致全表扫描。
like中如果%在左边则会导致索引失效，走全表扫描
字符类型加引号
- 防止类型强制转换导致索引失效
or改union效率会提高