MySQL索引详解之索引的利弊以及高效使用
前言
很多人对数据库索引可能都是知其然却不知其所以然，对索引没有很深入的理解，在使用过程中也一知半解，导致没有办法准确高效地使用索引，甚至存在不少误用的情况，导致使用索引反而降低了系统的性能。下面就以MySQL索引为对象，通过几篇文章来带大家好好的学习下索引的知识。
索引利弊
在前面的文章，我们学习了索引的数据结构和存储方式，下面再来理解索引的利弊也就能更加容易和清晰了。
索引的好处
a、提高数据检索的效率，降低检索过程中必须要读取得数据量，降低数据库IO成本。
b、降低数据库的排序成本。因为索引就是对字段数据进行排序后存储的，如果待排序的字段与索引键字段一致，就在取出数据后不用再次排序了，因为通过索引取得的数据已满足排序要求。另外，分组操作是先排序后分组，所以索引同样可以省略分组的排序操作，降低内存与CPU资源的消耗。
c、唯一性索引可以在数据库层面保证表中数据的唯一性。
索引的弊端
a、索引会增加 增、删、改操作所带来的IO量与调整索引的计算量。
b、索引要占用空间，随着数据量的不断增大，索引还会带来存储空间的消耗。
接下来我们再来看下如何高效的使用B+索引
索引高效使用
如何正确建立索引列
适合建立索引的列有以下特点：
- 列的值区分度高，也就是没有太多相同的值，区分度的公式是count(distinct col)/count(*)。例如只有
男、女两个值，这样建立起来的索引树也只有两个节点，意义不大。 - 频繁查询的列，索引的目的就是为了加快查询效率，所以在频繁查询的列建立索引的收益最高。如果是不常搜索的列，建立索引了也没多少机会用上，反而因为需要维护索引，会降低系统的维护速度和增大了空间需求。
- 经常排序、分组的列，索引可以有效地加快排序和分组的效率
- 经常用于连接的列（主键、外键）
不应该创建索引的的列具有下列特点：
- 数据量很少的表，数据很少的表不需要建立索引，数据库一行一行遍历可能还更快
- 字段的值很大的列，例如text, image类型的字段等不应该增加索引。这是因为，这些列的数据量大，建立起来的索引会很大，导致降低了索引的效率。
- 频繁增、删、改的列不适合建索引，因为需要频繁维护索引，可能得不偿失
- 需要参与计算的列不适合作为索引，例如
where a+b=2或者where from_unixtime(created_at) = ‘xxx’
如何使用好索引
使用规则
- 独立的列：索引使用的时候需要是独立的列，不能使用表达式和函数
- 前缀索引：建立联合索引的时候把区分度高的索引放前面
- 遵循最左前缀原则：使用索引的时候，从左侧开始匹配索引。对于单列索引，例如索引 A，
where A like aa%可以使用到索引，而where A like %aa不会使用索引；对于组合索引，例如索引(A,B,C),where A=xx、where A=xx and B=xx、where A=xx and B=xx%会使用到索引，而where B=xx and C=xx用不到索引 - 优先使用组合索引：在需要使用多个列作为条件进行查询时，使用组合索引比使用多个单列索引性能更好。例如对于
where A=a and B=b，使用组合索引(A,B) 只需要查询一次索引树，而如果分开索引要去两棵树查询 - 使用短索引
- 尽量实现覆盖索引，也就是需要获取的数据在索引里就有了，这样就不需要去再查一次主键索引，例如
select A from table where A=a and B=b - 在使用InnoDB存储引擎时，如果没有特别的需要，永远使用一个与业务无关的自增字段作为主键。使用自增字段作为主键可以让索引树插入更加高效，每次插入都是近似顺序插入。因此每次插入的时候不需要移动已有数据，因此效率很高，而且会形成一个紧凑的索引树结构。而如果使用随机主键如身份证号的话，每次插入可能在索引树的任何一个地方，索引树需要频繁的移动，分页，最后形成一个不够紧凑的索引树结构，后续需要通过OPTIMIZE TABLE来重建表并优化填充页面。
索引失效
在下列情况下，索引会失效导致全表扫描，因此我们要尽量避免以下情况出现。
- 用or的条件，如果or其中一个条件列没有索引，则不会使用索引
- 使用索引的时候不符合最左侧原则
- 存在索引列的数据类型隐形转换，则用不上索引，例如列是字符串，而在where语句中用了数字，如
where A=1 - 列使用了表达式或者函数
- 在where子句中进行null值判断
- where 子句中使用 != 或 <> 操作符
- 不使用NOT IN 、<>、！=操作，但<,<=，=，>,>=,BETWEEN,IN是可以用到索引的
- 表的数据量较少，数据库判断不使用索引更快
参考资料
《高性能MySQL》
https://blog.csdn.net/apt1203JN/article/details/79587593
https://blog.csdn.net/zk3326312/java/article/details/79377680
https://www.cnblogs.com/shan1393/p/8999622.html
Enjoy it !
如果觉得文章对你有用，可以赞助我喝杯咖啡~
版权声明
转载请注明作者和文章出处
作者: X先生
首发于https://www.jianshu.com/p/e916076920ef
