Mysql——索引优化

1、联合索引第一个字段用范围不会走索引 

2、强制走索引

3、覆盖索引优化

4、in和or在表数据量比较大的情况会走索引，在表记录不多的情况下会选择全表扫描

5、like KK%一般情况会走索引

索引下推

MySQL 5.6 引入了索引下推优化， 可以在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可 以有效的减少回表次数 。使用了索引下推优化后，上面那个查询在联合索引里匹配到名字是 'LiLei' 开头 的索引之后，同时还会在索引里过

滤 age 和 position 这两个字段，拿着过滤完剩下的索引对应的主键id再回表查整行数据。

索引下推会减少回表次数，对于innodb引擎的表索引下推只能用于二级索引，innodb的主键索引（聚簇索引）树叶子节点上保存的是全行数据，所以这个时候索引下推并不会起到减少查询全行数据的效果。

优化总结：

 1、MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index效率高，filesort效率低。

2、order by满足两种情况会使用Using index。   

        1) order by语句使用 索引最左前列 。

        2) 使用where子句与order by子句 条件列组合满足索引最左前 列。

3、尽量在 索引列 上完成排序，遵循 索引建立（索引创建的顺序） 时的最左前缀法则。

4、如果order by的条件不在索引列上，就会产生Using filesort。

5、能用覆盖索引尽量用覆盖索引。

6、group by与order by很类似，其实质是先 排序后分组 ，遵照 索引创建顺序 的最左前缀法则。对于group by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中的限定条件就不要去having限定了。  

索引设计原则

1、代码先行，索引后上

2、联合索引尽量覆盖条件

3、不要在小基数字段上建立索引

4、长字符串我们可以采用前缀索引

5、where与order by冲突时优先where

6、基于慢sql查询做优化

分页查询优化

常见的分页场景优化技巧：

1、根据自增且连续的主键排序的分页查询

2、根据非主键字段排序的分页查询

Join关联查询优化

mysql的表关联常见有两种算法：

• Nested-Loop Join算法
• Block Neste-Loop Join算法

1、嵌套循环连接Nested-Loop Join（NLJ）算法

2、基于块的嵌套循环连接Block Nested-Loop Join（BNL）算法

对于关联sql的优化：

• 关联字段加索引，让mysql做join操作时尽量选择NLJ算法
• 小表驱动大表，写多表连接sql时如果明确知道哪张表是小表可以用straight_join写法固定连接驱动方式，省去mysql优化器自己判断的时间

对于 小表定义的明确：

在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤， 过滤完成之后 ，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表” ，应该作为驱动表。

in和exsits优化

原则：小表驱动大表，即小的数据集驱动大的数据集

in：当B表的数据集小于A表的数据集时，in优于exists

exists：当A表的数据集小于B表的数据集时，exists优于in

count（*）查询优化

 这四个sql执行效率差不多。

字段有索引：count(*)≈count(1)>count(字段)>count(主键 id)

字段无索引：count(*)≈count(1)>count(主键 id)>count(字段)