MySql查询优化之索引跨列使用

  近期打算啃一啃Mysql，谈到数据库总是绕不过查询速度，Mysql 因其开源等的缘故，在各个关系型数据库中脱颖而出。这篇文章主要是对这段时间的学习mark 一下。如果还对看到这篇文章的你， 能有所帮助， 那就再好不过了。
  进行优化，需要先了解explain 使用。执行SQL 语句只需要在执行查询语句前，添加 “explain” 关键字

explain + SQL语句

简介参照以下描述

1，id

id值不同，越大越先查询。如果查询包含子查询，id值会不一样。（本质：嵌套查询时，先查内层再查外层），id值有相同有不同

2，select_type:查询类型

Primary key :包含子查询SQL中的主查询（最外层）；
SUBQUERY：包含子查询SQL的子查询（非最外层）；
simple：简单查询（不包含子查询、union）
derived：衍生查询（使用了临时表）

a. 在from 子查询中只有一张表select * from ( select * from course where tid in (1,2)) cr;
b. 在from子查询中，如果有table1 union tables2, 则table1 就是derived， table2 就是unionselect cr.cname from ( select * from course where tid =1 union select * from course where tid =2 ) cr

3，Type: 类型或者索引类型

System>const>eq_ref>ref>range>index>all
System,const 只是理想情况；实际能达到 ref 或者range
System:只有一条数据的系统表；或衍生表只有一条数据的主查询
const:仅仅能查到一条数据的SQL，用于Primary key或者unique
eq_ref: 唯一性索引键的查询，返回匹配唯一行数据ref ->非唯一性索引，对于每个索引键的查询，返回匹配的所有行
range:只检索给定范围的行，使用一个索引来选择行 > ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

4，key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）
不损失精确性的情况下，长度越短越好>

5，key
key列显示MySQL实际决定使用的键（索引）
6，Extra

Using filesort （文件内排序，“多了一次额外的查找/排序”）: 不要跨列使用（where 和order by）
explain select a1, a2, a3, a4 from test03 where a1 = 1 and a4 = 4 order by a2, a3;
Using where : 需要回原表Using index：不需要回原表
Using index：不需要回原表

explain 多用于分析索引创建的合理性，但是Mysql 内置的sql优化器也会进行一些它自认为好的优化。

以下是根据explian 参数展示索引跨列使用对性能影响。

1.创建a1,a2,a3,a4 的复合索引，进行简单的查询，分析计划如下. 根据extre 和key 的值，确认该SQL 语句使用了复合索引

2，修改SQL 语句–> 查询条件为a1,a2,a4，这个跨列查询，分析结果为key_len 为8 和 ref 中仅有四个const.
Point.1 中正常使用复合索引，key_len 为16（每个字段的长度为4字节） 和 ref 中包含四个const.
仅有a1,a2 索引有效，a4 因为跨列需要返回原表查询（根据extra 包含 using where）,导致索引失效。但是where和order by 拼起来为a1,a2,a3 并没有跨列，拼起来跨列的情况参照Point.3
3. 删除查询条件语句中的 a2，where 和 order by 拼起来 为a1、 a3，为跨列使用，Extra 出现了Using filesort。 出现了Using filesort， 表明多了一次额外的查询/排序，性能较差

总结：
1，如果（a,b,c,d）复合索引 和使用的顺序全部一致（且不跨列使用），则复合索引全部使用。如果部分一致，则部分使用。
2，索引一旦进行升级优化，需要将之前废弃的索引删掉，防止干扰。drop index idx_bta on book;
3，最佳左前缀，保持索引的定义和使用的顺序一致性
4，索引需要逐步优化

* 该文章参照了 相关 Mysql 视频学习