Mysql索引优化

目录

一、索引

二、底层数据结构B+树

三、Explain（extended）优化SQL

四、索引失效

五、主从同步不一致

六、SQL优化

---

一、索引

 参考：Mysql索引原理剖析与优化策略_mysql index_code_王者墨霜的博客-CSDN博客

1、聚簇索引

       一个表中只能有一个，聚集索引的顺序与数据真实的物理存储顺序一致。 

2、联合索引（由多列组成的的索引。遵循最左前缀规则。对where，order by，group by 都生效。）

        （a、b、c）三个字段，查询条件中abc字段无论顺序如何都会走索引，ab,ac,a会部分走索引，其他情况不走索引（参考图2）。

3、覆盖索引

        覆盖索引是联合索引的特殊情况，索引字段包含了查询语句中需要的所有列，不需要回表操作就能够获取到所需的数据。可以大大提高查询效率。

        例：select name,age from employees where age=1;

        可以给字段age,name建联合索引（ALTER employees use add INDEX idx_age_name (age,name);），age,name顺序不能颠倒，否则索引可能会失效。

二、底层数据结构B+树

        1、B+树：每个节点是一个页，通常为16KB大小。同一层级的节点通过双向链表来连接，不同层级的节点通过单向链表来连接。

        2、特点：非叶子节点不存储数据，相对B树增加每层存放的节点数，降低了B+树的高度，高度越低查询效率越高，MySQL高版本所有非叶子节点全部缓存在内存大大提高查询效率；叶子节点存储数据，叶子节点由双向链表关联，方便范围查询。

三、Explain（extended）优化SQL

        1、查看表的执行顺序（id：越大优先级越高，id相同从上往下）

        2、查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询（select_type）

        3、数据读取操作的扫描类型（type：system>const>eq_ref>ref>range>index>ALL，查询至少达到range级别，最好能达到ref）

        4、哪些索引可以使用（possible key）

        5、哪些索引被实际使用（key）

        6、索引字段的长度（key_len:联合索引ab两个字段，使用到的长度4+4=8）

        6、表之间的引用（ref）

        7、每张表有多少行被优化器查询（row）

        8、查看sql执行过程中使用了什么索引，using index走索引，using filesort不走 （extra）

四、索引失效

        1、有or;
        2、复合索引未用左列字段;
        3、like以%开头;like以%结尾不失效；
        4、需要类型转换;
        5、where中索引列有运算;
        6、where中索引列使用了函数;
        7、如果mysql觉得全表扫描更快时（效率低于30%）;

五、主从同步不一致

1、主从表使用的索引字段不一致；（update table set c=3 where a=1 or b=2,ab都是索引字段）

六、SQL优化

1、count(*)与count(1)区别，count(*)会统计值为null的行，count(1)不统计。

//name为索引字段，4条SQL执行效率差不多
//name不是索引字段，count(name)执行效率最差
select count(*) from user;
select count(1) from user;
select count(name) from user;   
select count(id) from user;

2、in与exist的使用。

select * from user where user_id in (select user_id from oder where status=1)

等价于：
for(select user_id from oder where status=1){
    select * from user where user_id;
}

select * from user where user_id exist (select 1 from oder where status=1)

等价于：
for(select * from user){
    select * from oder where status=1;
}