mysql优化之索引

索引概念

        索引就像书的目录一样，能够让我们快速的找到想要的内容。索引是一个有序列表，每个索引包含这个索引的值和对应数据的物理地址，通过索引能快速定位对应数据，从而提高查询的效率。

索引类型

        mysql常用的索引类型包含：普通索引、唯一索引、主键索引、联合索引。

• 普通索引：最基本的索引，没有任何限制，一个索引值可能对应多条数据。
• 唯一索引：值不能重复的索引，且对应的字段不能有NULL值，如果是组合索引，则对应列值的组合必须唯一。
• 主键索引：可以理解为特殊的唯一索引，记录的唯一标识。
• 联合索引：由多个字段组合而建立的索引。

索引建立原则

         索引并非越多越好，要根据实际情况合理建立索引，否也会对数据库的性能造成一定的负面影响。

需要创建索引的情况：

• 主键会自动创建唯一索引 。
• 频繁作为查询条件的字段应该创建索引。
• 查询中要排序或分组的字段应该创建索引，如果排序或分组的字段有多个，可以考虑建联合索引。
• 查询冲要统计的字段应该创建索引。
• 作为逻辑外键（表与表之间用来关联的字段）的字段应该创建索引。

不创建索引的情况：

• 经常要增、删、改的字段不建议创建索引，因为有索引的字段在做增、删、改的时候要同时更新索引，会影响sql执行性能。
• 表记录很少的情况不需要创建索引，因为不创建查询速率也很快。
• 不用作查询条件的字段不需要创建索引 。
• 数据重复很平均或者数据数据不同值很少的字段不建议创建索引，比如性别，创建索引反而会降低效率。一般区分度在80%以上就可以建立索引。区分度可以使用count（distinct（列名））/count（*）来计算
• 经常做增、删、改的表也不建议建索引。

查询时索引失效场景 

• like左匹配或like左右匹配：因为索引 B+ 树是按照「索引值」有序排列存储的，只能根据前缀进行比较。比如：'%abc'、'%abc%'。
• 对索引列使用函数：因为索引保存的是索引字段的原始值，而不是经过函数计算后的值，自然就没办法走索引了。比如：length(name) = 3。
• 对索引列进行表达式计算：原因跟对索引使用函数一样。比如：age-1 = 9。
• 对索引列隐式类型转换：因为在对索引列进行类型转换是使用了转换函数，索引多导致索引失效。比如：phone字段是varcher类型，则（select * fromuser where phone=13800001234）会使索引失效。
• 联合索引违背最左匹配原则：比如（a，b，c）三个字段上建立联合索引，那么a、（a，b）、（a，b，c）三组的查询能够命中索引，（a、c）则不能命中索引。
• null值查询：因为mysql不会为null建立索引。
• 列与列的对比：例如：某个表中，有两列 id 和 c_id 都建了单独索引，where条件为 id=c_id，这种情况会被认为还不如走全表扫描。
• 联合索引前导列区分大：当联合索引前导列区分小的时候，我们有 INDEX SKIP SCAN，当前导列区分度大，且查后导列的时候，前导列的分裂会非常耗资源，导致执行计划全表扫描，然后就索引失效了。
• 非条件不会使用索引。

SQL优化补充 

•  like尽量使用后置模糊匹配。比如：'abc%'。
• union、or、in都可以命中索引，但建议使用in。
• 把非条件（！=、<>、not in、not exists、not like等）转化为in条件。
• 存在非等号和等号混合判断条件时，在建索引时，要把等号条件的列前置。
• 使用联合索引时，遵守最左前缀原则。
• 如果建立了（a，b）联合索引，就不必再单独建立a索引。
• 关联表查询时，区分度最高的字段在最左边。
• 范围列可以用到索引，但是范围列后面的列无法用到索引，索引最多用于一个范围列，如果查询条件中有两个范围列则无法全用到索引。范围条件有：<、<=、>、>=、between等。
• 把查询条件的计算放到业务层而不是数据库层。
• 查询条件要匹配查询字段的类型。
• 利用覆盖索引来进行查询操作，避免回表。
• 使用not null或默认值约束索引列。
• 业务上唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。
• 超过三个表最好不要用join，需要join的字段，数据类型必须一致，多表关联查询时，保证被关联的字段有索引。
• 如果排序字段没有用到索引，就尽量少排序。
• 使用合理的分页提高效率，比如：select id,name from product limit 100000, 20分页的时候，随着表数据量的增加，直接使用limit分页查询会越来越慢。可以取前一页的最大行数的id，然后根据这个最大的id来限制下一页的起点。 比如此列中，上一页最大的id是99999，然后select id,name from product where id> 866612 limit 20。
• 尽量用union all 代替 union，Union需要将集合合并后在进行唯一性过滤操作，这会涉及到排序，大量的cpu运算，加大资源消耗及延迟，当然，使用union all的前提条件是两个结果集没有重复数据。