mysql解析器对查询优化_通过Mysql解析工具Explain进行的索引优化

1.explain 分析语句

explain:通常应用于sql语句的性能分析

比如：

EXPLAIN SELECT * from t where sex='1'



id：select识别符

select_type:select语句类型

SIMPLE:表示最简单的查询

EXPLAIN SELECT * from t where id = (select id from t where age=20);



PRIMARY:主查询

SUBQUERY:子查询

explain select id from t where age = 20

union all

select id from t where age = 10



UNION:合并查询

table:与查询语句相关的表

partitions:表分区

type:表的连接类型all index range ref null const

all:全表匹配

index:只对索引树上的数据的访问查询

range:经过条件的过滤，并且只在索引树上定位

ref:表连接匹配的条件，在索引树上查到数据

null:mysql分解语句，不需要访问索引树，通过单独的索引来查找完成

const 主键转换条件

possible_keys:可能会使用到的索引

key：使用的索引

key_len:使用的索引长度(按照字节)

ref:使用哪个列一起进行的查询

EXPLAIN SELECT * from t where id='1'



const 条件为主键的查询

rows:查询sql语句扫描的数据量，不会很精确，属于约等于

Extra:一些额外的信息(性能从上倒下依次变差)

using index: 表示sql使用索引并且是覆盖索引的方式进行查询

using where: useing index:

单独出现 using where: 根据条件进行过滤

using index condition 使用了索引，但是需要回表进行数据查询

using filesort:在使用索引内进行排序时(order by 索引列)

using temporary:当sql中存在去重，排序，合并，分组(性能很低)

2.联合索引结构与最左匹配原则

二分查找，btree二分法

show index from t; idx_class_sex_ages

explain select * from t where class="starchy" and age=20 and sex=1;

explain select * from t where class="starchy" and sex=1 and age=20;

explain select * from t where sex=1 and age=20 and class="starchy";

explain select * from t where age=20 and sex=1 and class="starchy";

匹配最左边的列 idx_class_sex_ages => (class) (class,sex) (class,sex,age)

只要条件中存在上面的三种索引列，那么sql语句将就将就可以命中索引

总结：只要是按照了索引创建顺序来编写的where 条件，那么就可以使用到这个索引，并且大几率是覆盖索引，也符合最左匹配原则

而最左匹配原则与条件中的字段顺序无关，只要需要按照索引创建的顺序最左字段存在即可

3.mysql索引优先使用对象

alter table t add index idx_class(class);

alter table t add index idx_age(age);

alter table t add index idx_sex(sex);

explain select * from t where class="abc" group by sex order by id;



explain select * from t where class="abc" group by sex;



explain select * from t where class="abc" order by id;



explain select * from t group by sex order by id;



mysql优化器在选择索引时的策略 where > group by > order by

优化方案： 将条件字段以及分组和排序字段，都建立在索引中

4.在哪些情况下用索引

注意：索引字段的选择，一般会选择推荐重复比较少的字段

例如：

性别字段 0男 1女 2保密 则数据库中这个字段中三个值经常重复的数据，就不推荐使用

如果项目需求(可以考虑联合索引)

1.唯一字段可以建立单索引(字段不允许重复例如id)

2.非唯一字段可以考虑建立联合索引

3.索引个数： 最佳是不超出6个，勉强可以是10个=》如果不够还要加索引，建议垂直分表

4.索引的使用遵循最左匹配原则其次就是覆盖索引

5.索引的选择字段尽量要小一些 int varchar(10) char(5) 如果有文本，可以索引存储文本路径

6.避免like between 等范围查询

explain select * from t where class like 'abc%’;只有这个可以有索引命中

explain select * from t where class like '%abc%';

explain select * from t where class like '%abc';

后面两个都没有索引查询了

7.尽量多使用explain进行分析

8.优先考虑建立联合索引，索引的字段不要包含null 活着 ‘’