MySQL索引常见问题(下)

MySQL索引常见问题(下)

1.什么时候需要/不需要索引

索引可以极大提高检索数据的效率，但是他的缺点也很明显

• 需要占用物理空间，建立一颗索引树是需要占用磁盘空间的，索引越多空间占用也就越多
• 需要维护索引，在增加、删除数据时，还需要维护索引树(B+树)的有序性

所以，我们需要根据具体的场景和业务来适当的建立索引

什么时候需要索引

• 字段具有唯一性的时候，例如身份证号，商品编码等等
• 经常需要使用where进行过滤的字段
• 经常用于group by 和 order by 的字段，因为建立索引后的数据在索引树中是有序的

什么时候不需要索引

• 字段中存在大量重复数据的时候，例如性别，只有男女(通常情况) 如果两个值在表中分布均匀，无论搜索哪个值都只能排除一半的数据，这种情况就没必要再建立索引。而且Mysql在执行阶段，还有一个查询优化器，当它发现某个值在表中出现比率很高时就会忽略索引，直接进行全表扫描
• where 、group by、order by 用不到的字段
• 频繁更新的字段，比如电商项目中用户余额字段就没有必要建立索引，因为这种字段更新很频繁，需要不断维护B+树的有序性，降低了数据库性能

2.索引优化的方法

有几种常见的优化索引的方法

• 前缀索引优化
• 覆盖索引优化
• 主键索引自增长
• 防止索引失效

前缀索引优化

就是根据一个字符串类型的字段的前几个字符建立索引

这样可以减少索引字段的大小，当字符串很长时既增加了一个数据页中的索引值，又提高了查询效率

覆盖索引优化

就是在一条查询语句使用二级索引检索数据时，他所要检索的字段，在二级索引树的叶子结点都能查询得到，就不需要再进行回表到主键索引树中查询了，提高了检索效率

主键索引自增长

在我们建表时，主键都会设为AUTO_INCREMENT，这样做的好处是什么？

原因就是InnoDB在存在主键的情况下，是以主键作为聚簇索引的索引键，也就是说数据是存储在B+树的叶子结点中的，而且由于B+树的特点，这些数据是以主键的顺序进行排列的，所以当我们每次新增一行数据，就直接追加到当前节点位置，即使当前数据页满了，也可以直接开辟一个新页，不需要重新移动数据

但是如果主键不设为自增长，就可能会将数据插入数据页中中间的某个位置，为了维护B+树，就不得不移动其他数据，如果当前数据页满了，还会造成页分裂

防止索引失效

这里简单举出几种常见情况

• 使用左或者左右模糊匹配 like %xx 或 like %xx%
• 在查询条件中对索引字段进行了函数计算操作
• 联合索引没有遵循最左匹配原则
• 在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列