序
上一篇讲解了建表规范后，本章重点分析下创建索引的一些规范
由于索引是工作在存储引擎层，所以以下规约都是基于InnoDb引擎
关于索引
索引的优点
- 索引大大减少了服务器需要扫描的数据量
- 索引可以帮助服务器避免排序和临时表
- 索引可以将随机I/O变为顺序I/O
缺点
- 索引会带来额外维护的开销，减慢写入速度
索引规约
通用原则
- 代码先行，索引后上。开发时建立索引步骤：建表-开发完主体业务-建索引。
- 利用覆盖索引来进行查询操作，减少select *，避免回表
- 不允许存在重复索引（指完全相同的索引），大多数时候也应该避免冗余索引（指索引被其他联合索引包含）
- 不要在小基数字段上建立索引，注意列的区分度（例如大多数时候性别列不应该单独建立索引）
- is null，is not null 一般情况下也无法使用索引
- mysql在使用不等于(!=或者<>)，not in，not exists 的时候无法使用索引会导致全表扫描
- < 小于、 > 大于、 <=、>= 这些，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引
- 防止因字段类型不同造成的隐式转换，导致索引失效 ，例如：字符串不加单引号索引失效，数字型字段匹配字符串值等
- 少用or或in，用它查询时，mysql不一定使用索引，mysql内部优化器会根据检索比例、表大小等多个因素整体评 估是否使用索引，详见范围查询优化
- 不在索引列上做任何操作(计算、函数、(自动or手动)类型转换)，会导致索引失效
- 当要索引的列的值非常长时，可以考虑增加一个对应的hash列，自定义一个Hash算法（结果最好是整数）来同步更新对应的hash列，通过改为在hash列加索引查询将大大提高查询性能
- where和order by 冲突时，优先满足where条件，当过滤的数据集足够小时，哪怕走文件排序性能依然很高。
- 严禁左模糊或者全模糊，如果需要请走搜索引擎来解决
- 在较长的varchar上建立索引时，尽量指定索引长度。通常为前20个字符创建前缀索引能达到90%的区分度。可以使用 count(distinct left(列名, 索引长度))/count(*)的区分度来确定。
- 优化联合查询
- 尽量避免超过三个表的JOIN查询。
- 需要JOIN的字段，数据类型要保持绝对一直，包括字符集，关联字段尽量选择整数类型字段。
- 保证被关联表的字段一定有索引
- 小表驱动大表，写多表连接SQL时如果明确知道哪张表是小表可以用straight_join写法固定连接驱动方式，省去Mysql优化器自己判断的时间
注意：Mysql多表join很难优化，应尽量避免。如果一定要使用多表的大连接查询，那么请进行拆分，然后在应用中关联，好处如下：
- 减少锁竞争
- 增加缓存的可能性
- 可以减少冗余记录的查询。在应用层做关联，意味着对于某条记录应用只需要查询一次，而在数据库中做关联，则需要重复的访问一部分数据，这样可以减少网络和内存的消耗。
- Mysql的资源是非常宝贵，而且相对于应用服务器来说相对是难以扩容的，通过转移计算的压力到应用服务器，来降低Mysql的负载。
关于主键（聚簇索引）
- 一定要主动创建一个主键，减少mysql的工作，否则InnoDb会自动选择一个唯一的非空索引代替，若没有会隐式定义一个主键作为聚簇索引。
- 大多数时候建议使用自增的整型作为主键，若非顺序的主键会导致数据插入的时候可能产生较多的页分裂，降低插入速度，同时产生存储碎片，影响查询性能。当然可以根据业务特性需要聚集某些维度的数据，也可以使用其他数据列作为聚簇索引，来提高检索性能，但这需要综合评估其他操作。
Hash索引
- Hash索引只包含哈希值和行指针，不存储字段值，不能避免回表操作
- Hash索引只能执行=、IN查找，不支持索引前缀匹配，范围和排序
- 在区分度很低的列创建hash索引会导致大量hash冲突，性能很低，特别是更新/删除的时候
联合索引
- 创建联合索引时
- 大多数时候应该将区分度最高的列放在最左边
- 让联合索引尽量使用全部的列，减少通过索引筛选出来的结果集
- InnoDb不能使用索引中范围条件右边的列，尽量把需要范围查询的列放在最右侧（Mysql5.6引入了索引下推，可以在一定程度上优化该情况，减少回表次数，见下面的扩展阅读）
- 联合索引，第一个就走范围查询可能不会走索引，mysql内部可能觉得第一个字段就用范围，结果集应该很大，回表效率不高，还不如就全表扫描
- 控制单表索引数量，尽量避免使用单列索引，尽量使用3个左右联合索引覆盖80%业务查询场景，包括where，order by，group by，注意最左前缀原则
唯一索引还是普通索引
创建二级索引时选择 普通索引还是唯一索引？
- 结论
如果追求性能使用普通索引，如果追求数据唯一性使用唯一索引。
根据墨菲定律，长久来看，只要没有唯一索引，就一定会有脏数据产生，所以在大多数时候建议选择联合索引 - 从性能上分析
- 查询速度，唯一索引更快。唯一索引查找到记录就停止，普通索引需要查找到下一个不同的值出现，但mysql实际上是以页为单位读取数据，大多数时候下几条记录都通过一次I/O读取到了内存中，在内存中多做几次判断的耗时可以忽略不计
- 更新速度：普通索引快很多，唯一索引无法利用change buffer导致需要进行磁盘I/O读取数据到内存判断
唯一索引提升的查询速度非常有限，但是无法利用change buffer导致写操作速度下降很多。
索引合并
- 索引合并（index merge）指同时利用多个索引分别进行扫描，然后将扫描结果进行 合并 UNION（OR查询）或 相交 INTERSECTION （AND查询） 或者 两者皆有，在对多个索引做联合操作时，需要耗费大量CPU和内存资源在算法的缓存、排序和合并操作上，但优化器不会计算这些查询成本，导致成本被低估，有时候还不如全表扫描，应该创建联合索引避免该情况。
扩展阅读
索引下推
在5.6以后mysql引入索引下推机制，可以在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数，索引下推只运用于二级索引。 col1 like ‘??%’ AND col2 = ‘??’ 即可能使用索引下推。
系列文章
上一篇：【Mysql系列文章(四)】InnoDb 字段类型解析及建表规约
|