目录
一、索引
参考: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;
}