04-26MYSQL索引优化一

索引优化例子

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

联合索引第一个字段就用范围查找不会走索引，mysql内部可能觉得第一个字段就用范围，结果集应该很大，回表效率不高，还不如就全表扫描

EXPLAIN SELECT * FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manager';

这种情况下可以使用 force index(idx_name_age_position) 去强制走索引，但是 强制走索引的最终查找效率不一定比全表扫描高。
所以这种情况，可以使用覆盖索引去优化：

EXPLAIN SELECT name,age,position FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manager';

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

3.like KK% 一般情况都会走索引

EXPLAIN SELECT * FROM employees WHERE name like 'LiLei%' AND age = 22 AND position ='manager';

这里的key_len是140，也就是把辅助索引的3个字段都用上了。按照最左前缀原则，like ‘LiLei%’只会走name字段索引。因为根据name字段过滤完，得到的索引行里的age和position是无序的，也就无法再利用索引。
like KK%其实就是用到了索引下推优化。
什么是索引下推：

• 在5.6以前的版本，这个查询只能匹配到名字是’LiLei’开头的索引，然后把这些索引的主键回表去找出对应的记录，然后再筛选出age和position符合的记录。
• 在5.6之后引入了索引下推，就是在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数。也就是，在找出名字开始是’LiLei’的索引后，还会在索引里过滤age和position，最后才用筛选后的主键回表去查询。
• 索引下推会减少回表次数，对于innodb引擎的表索引下推只能用于二级索引（非聚集索引）。因为innodb的聚簇索引树叶子节点上保存的是全行数据，所以这个时候索引下推并不会起到减少查询全行数据的效果
  • 为什么范围查找Mysql没有用索引下推优化
• 可能是认为范围查找过滤的结果集过大，like KK% 在绝大多数情况来看，过滤后的结果集比较小，所以这里Mysql选择给 like KK% 用了索引下推优化

Order by与Group by优化

Case1:

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei'  AND position ='dev' ORDER BY age

从这里key_len=74可以看出走了name的索引，因为中间age断了，所以position没走索引，age索引列用在排序过程中，因为Extra字段里没有using filesort。

Case2:

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei'  ORDER BY position

key_len=74走了name的索引，由于用了position进行排序，跳过了age，出现了Using filesort。

Case3:

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei'  ORDER BY age,position;

走了name的索引,age和position用于排序，无Using filesort

Case4:

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei'  ORDER BY position,age;

这里把排序的age和position调换了下，出现了filesort。因为索引的创建顺序为name,age,position，不符合最左匹配原则。

Case5:

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' and age=10 ORDER BY position,age;

这里的Extra中没有出现Using filesort，因为age为常量，在排序中被优化，所以索引未颠倒，不会出现Using filesort

优化总结：

• MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index效率高，filesort效率低。
• order by满足两种情况会使用Using index
  • order by语句使用索引最左前列
  • where子句与order by子句条件列组合满足索引最左前列
• 尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则
• 如果order by的条件不在索引列上，就会产生Using filesort
• 能用覆盖索引尽量用覆盖索引
• group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则
• group by如果不需要排序可以加上order by null禁止排序

Using filesort文件排序原理

filesort文件排序方式

• 单路排序，是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序。
• 双路排序，是先根据相应的条件取出相应的排序字段（例如ORDER BY age,position,那就取出age和position字段）和可以直接定位行数据的行 ID（主键），然后在sort buffer中进行排序，排序完后再用id会表取查询其它需要的字段

MySQL 通过比较系统变量 max_length_for_sort_data(默认1024字节) 的大小和需要查询的字段总大小来判断使用哪种排序模式

• 如果 字段的总长度小于max_length_for_sort_data ，那么使用 单路排序模式
• 如果 字段的总长度大于max_length_for_sort_data ，那么使用 双路排序模式

索引设计原则

• 代码先行，索引后上
  • 不是建完表马上就建立索引的，应该等到主体业务功能开发完毕，把涉及到该表相关sql都要拿出来分析之后再建立索引
• 联合索引尽量覆盖条件
  • 可以设计一个或者两三个联合索引(尽量少建单值索引，因为每个索引都会构建一颗索引树，多个单值不如一个联合索引好)，让每一个联合索引都尽量去包含sql语句里的where、order by、group by的字段，还要确保这些联合索引的字段顺序尽量满足sql查询的最左前缀原则。
• 不要在小基数字段上建立索引
  • 尽量使用那些基数比较大的字段，就是值比较多的字段，那么才能发挥出B+树快速二分查找的优势来。如果是小基数的列，如果性别，不是男就是女，没法进行快速的二分查找，这种建立索引不如全表扫描。
• 长字符串我们可以采用前缀索引
  • 对于这种varchar(255)的大字段可能会比较占用磁盘空间，可以针对这个字段的前20个字符建立索引，就是说，对这个字段里的每个值的前20个字符放在索引树里，类似于 KEY index(name(20),age,position)。
  • 如果你在where条件是根据name字段来搜索，会先到索引树里根据name字段的前20个字符去搜索，定位到前20个字符的前缀匹配的部分数据之后，再回到聚集索引提取出来完整的name字段值进行比对。
  • 如果你是order by name，因为name在索引树里仅仅包含了前20个字符，所以这个排序是没法用上索引的。
• where与order by冲突时优先where
• 基于慢sql查询做优化