数据库索引总结

在刚接触数据库时，我们经常听到使用索引可以提高查询性能；然而，我们也听到索引也会影响更新性能。在日常使用时，我们又会发现建立了索引，查询使用了索引反而并没有提升性能，甚至查询时并没有按照我们期望的使用索引。那么索引到底是什么？索引为什么会提升查询性能，反而又能影响更新性能呢？到底有没有必要建索引？索引使用过程需要注意哪些？带着这些问题，让我们开启一段数据库索引学习总结之旅吧！

索引是什么？

索引最经典的类比就是一本书籍的目录，通过该目录我们可以快速定位到特定章节。在Mysql数据库中，索引就是B+树(为什么选择B+树)，通过B+树我们可以快速定位到具体的记录。B+树是一颗多路平衡查找树，专为磁盘等外设而设计的。B+树的结构如下图所示：

B+树核心点：

• 非叶节点只存储键值信息，叶子结点存储键值信息和数据。
• 查询是自顶向下逐层查找，在每层非叶子节点中，根据范围找到目标节点所在下层节点指针，依次查找直至叶子节点。
• 每层查找中都是有序的，可以进行二分查找。

索引提升查询性能原理

索引提升查询性能的原理就是B+树的查询原理。B+树的查询是自顶而下逐层查找，直至找到叶子节点，具体流程如下：

比如查询19对应的数据，查询流程如下：
从根节点开始，因为19处于10～20之间，所以P2指向节点可能包含19
在1中P2指向节点中，因为19大于16，所以P3指向节点可能包含19
在2中P3指向节点中，二分查找可找到19及其对应的记录

在上面的例子中，使用索引过程中，需要3次即可；不使用索引，则需要进行扫表。B+树时间复杂度O(logmN)，扫表时间复杂度O(N)，所以使用索引可以提升系统的查询性能。
由于在记录更新过程中，可能也需要同步更新索引，从而影响系统的更新性能。

聚簇索引

在Mysql Innodb引擎中，索引可以分为聚簇索引和非聚簇索引(普通索引、二次索引)。浅谈聚簇索引和非聚簇索引的区别

• 聚簇索引将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据
• 非聚簇索引将数据与索引分开存储，索引结构的叶子节点存储主键值。在非聚簇索引上访问数据时，一般需要二次查询（聚合索引除外），先基于非聚簇索引找到主键，再根据聚簇索引找到具体的数据。非聚簇索引如下图所示：
  

由于聚簇索引将数据和索引存储在一起，所以聚簇索引具有唯一性。一张表只能有一个聚簇索引，而可以有多个非聚簇索引。聚簇索引一般都是基于主键，在没有主键的情况下，如果存在唯一索引，则聚簇索引就基于唯一索引创建，否则会生成一个隐式主键作为聚簇索引。

对于聚簇索引一般选择自增主键id，这样是为了避免频繁的页分裂。由于B+树每个节点的值个数是固定的，当节点中值个数超过固定个数就会导致页分裂，极端情况下甚至会导致大量级联页分裂。如果主键不是自增id，那么就可能导致不断地调整数据的物理地址、分页，从而影响系统性能和稳定性。基于自增id的主键，则插入数据时只会在最后一个叶子节点中追加数据，需要页分裂时也仅仅涉及极少的节点，索引结构相对紧凑，磁盘碎片少，效率也高。

索引分类

从索引包含的字段数角度来看，索引可以分为单列索引和多列索引（组合索引、复合索引）：

• 单列索引：索引只有一个字段，如字段a
• 多列索引：索引有多个字段，按顺序排列，如字段a、b、c组成索引idx

从索引功能角度来看，索引可以分为主键索引、唯一索引、普通索引：

• 主键索引：唯一且不能为空，只能有一个主键，主键是一种约束
• 唯一索引：唯一允许为空，可以有多个唯一索引
• 普通索引：可以有多个普通索引

从索引使用角度分为：

• 前缀索引：对于多列索引，使用前面几个字段来进行查询，比如索引idx包含a、b、c三个字段，则可以使用a和b字段来进行查询
• 覆盖索引：查询的数据都在索引中，比如根据a和b来查询c，那么就可以使用idx来进行查询，由于索引idx中已包含c字段，那么就不用进行二次查询。

另外一个在业务中用的比较少的索引-全文索引。全文索引一般是搜索引擎的核心技术，在Mysql中，innodb存储引擎并不支持全文索引，Myisam存储引擎支持全文索引。全文索引通常使用倒排索引(参见​什么是倒排索引？)来实现。倒排索引是一种索引结构，它通过关联数组的方式存储了单词与单词自身在一个或多个文档中所在的位置之间的映射。通俗的讲，倒排索引就是存储了单词到文档的映射关系，通过该映射可以很方便的找到相关文档。

Mysql索引使用

索引虽然可以提升系统的查询性能，但是索引并不是万能的。过多的索引不仅不能带来系统性能上的提升，反而还可能影响我们的系统性能。在我们提出创建索引请求之前，我们需要从以下几个角度评估到底需不需要创建索引。

• 业务上：比如业务上需要主键外的字段来防重，此时需要基于该字段创建唯一索引。
• 数据量：当数据量很小时，使用索引与否，都对性能影响不大。数据量的大小会影响索引选择
• 区分度（选择性）：一个字段在业务中的取值范围，取值范围越大，则区分度就越高，反之区分度就越低。索引的本质上还是为了减少扫描的纪录数，当字段的区分度越低时，代表着我们需要扫描的纪录就越多，此时基于该字段的索引对性能提升帮助不大。比如对于type字段，取值1、2、3，各个取值占比1/3，总共10w数据，那么基于type字段的索引都需要扫描3.3w多记录。

索引使用注意点

explain对于mysql语句分析时非常重要的工具，可以帮忙我们分析sql语句是否使用索引以及用的哪个索引。通过该工具，我们可以快速进行一些sql语句的优化。
索引并不是百无禁忌的，为了最有效的使用索引，在索引使用过程中必须注意以下问题：

1. 频繁进行数据操作的表，不要建立太多的索引
2. 删除无用的索引，避免对执行计划造成负面影响
3. 经常与其他表进行连接的表，在连接字段上应该建立索引
4. 表的主键、外键必须有索引
5. 索引列不能包含NULL，索引中包含NULL值就会使整个索引失效
6. 注意索引字段类型，不要使用类型转换。如果索引字段类型和查询值的类型不匹配，则将不会走索引，比如字段a类型为varchar，而查询语句是where a = 123，此时索引将失效
7. 注意多列索引字段顺序，字段区分度高的字段在前面，区分度低的字段在后面；在索引使用过程中，需要遵守最左前缀法则，即按索引字段顺序查询
8. 不使用NOT IN和<>，LIKE "xxx%"形式是可以使用索引，而LIKE "%xxx"形式无法使用索引
9. 索引列不应该作为表达式的一部分，即也不能在索引上使用函数
10. 避免多个范围条件，MySQL将不再使用范围列后面的其他索引列。
11. order by，尽量使用index方式排序(表示mysql扫描索引本身完成排序)，避免使用FileSort排序。order by满足以下情况会使用index方式排序：1.order by语句使用索引最左前列 2.使用where子句和order by子句条件组合满足索引最左前列规则
12. 慎用left join(left join会创建临时表)
13. 谨防where子句中的OR(where语句使用or，且没有使用覆盖索引，会进行全表扫描)，尽量使用UNION代替OR
14. 善用LIMIT和覆盖索引，减少select * from …的使用

附录

• 计算机科学中的常见树大全
  https://blog.csdn.net/weixin_43145361/article/details/89816016
• 为什么MySQL数据库索引选择使用B+树？https://segmentfault.com/a/1190000020545192
• 【深入学习MySQL】MySQL的索引结构为什么使用B+树？https://www.cnblogs.com/kismetv/p/11582214.html
• 浅谈聚簇索引和非聚簇索引的区别 https://my.oschina.net/xiaoyoung/blog/3046779
• 什么是倒排索引？https://blog.csdn.net/starzhou/article/details/87519973