数据库性能系列之索引（上）

前言

上一次，我们从优化子查询的角度，讲解了一些简单的数据库性能优化方面的知识。通过优化子查询的顺序，包括合理使用IN和EXISTS，可以起到部分查询的效率提升。

但对于其他大多数场景，如单表记录很大，或多表级联查询包含条件或排序时，子查询的优化往往起不到决定性的效果。所以从今天开始，我们要逐渐接触这一部分的主角：索引。这部分内容我会分为上中下三篇进行详细的讲解，上篇会着重于索引的概念，以及基础的使用方式；中篇会讲解索引背后的实现原理，便于理解索引如何发挥作用；下篇会结合一些复杂的SQL场景，来描述索引在实际工作中如何使用。

概述

数据库中的索引，就像一本书的目录，它可以帮我们快速定位和查找，从而加快数据查询的效率。

如果我们不使用索引，那么记录就必须从表中第一行开始进行逐行扫描，直到找出符合条件的记录为止，这样的查询效率，必然是很低的。那么，索引是不是建立得越多就越好呢？

答案是否定的，因为索引也是一种存储在数据库硬盘中的数据结构，会占用一定的空间。而且对于数据的新增、修改和删除时，数据库也需要对其相关的索引进行更新，进而降低了整体效率。

索引的类型

了解了索引的基本概念之后，我们还需要知道不同索引的用途。索引从功能逻辑上可以分为四类：普通索引、唯一索引、主键索引和全文索引。而在物理实现上可以分为两类：聚集索引和非聚集索引。下面我们来逐一讲解：

1、普通索引：最基础的索引，没有任何约束，直接建立即可，用于提升查找的效率。

2、唯一索引：在普通索引的基础上，加入了数据唯一性的约束，用于检查数据是否唯一，在同一张数据表中，可以有多个唯一索引。

3、主键索引：在唯一索引的基础上，加入了NOT NULL的特性，在我们给数据库表设置主键时，会自动创建主键索引，而根据主键的特性，一个表中只能有一个主键索引。

4、全文索引：一种特殊的基于标记的索引，由数据库引擎维护，用于快速查找某个字符出现的位置，较少使用。目前此种查找一般会使用搜索引擎实现，如ES(ElasticSearch)。

5、聚集索引：确定了数据存储的顺序，在物理上是连续存储的，因此聚集索引在每个表中只能有一个。在默认情况下，数据库会对主键约束自动创建聚集索引，这就是数据表中的行记录通常按照主键排列的原因。

6、非聚集索引：在数据库中有单独的空间进行存储，索引项本身是按照顺序存储的，但是索引指向的内容却是随机存储的。也就是说非聚集索引在工作时，系统会进行两次查找，第一次会找到索引本身，第二次则根据索引对应的位置找出数据行。非聚集索引不会把索引指向的内容像聚集索引一样直接放到索引后面，而是维护单独的索引表，为数据的检索提供方便。由于实现原理的不同，非聚集索引在每个表中可以有多个。

索引的应用

现在我们来看一下索引使用的实际效果，目前我有一张表T_ORG_USER，其中大约有3000条数据。这时我们需要查询一条记录如下：

我们可以使用主键Id和用户名分别查询同一条记录，然后查看查询记录的区别，首先我们使用主键Id进行查询：

SET STATISTICS TIME ON
SELECT * FROM T_ORG_USER WHERE F_USER_ID = 8400

查询结果如下图所示，执行时间为0毫秒：

此时，我们再使用用户名进行查询，用户名字段没有建立索引：

SET STATISTICS TIME ON
SELECT * FROM T_ORG_USER WHERE F_NAME = '24'

查询结果如下图所示，执行时间为196毫秒：

从执行时间上，我们可以看出二者有明显的效率差别，这时我们再对F_NAME字段建立索引：

CREATE INDEX IDX_USERNAME ON T_ORG_USER(F_NAME)

再次查询，结果如图所示，执行时间缩短到了5毫秒：

从上述三次的执行结果中，我们可以总结出以下两点：

1、对WHERE条件后的字段进行索引，可以大幅度提高查询的效率

2、采用聚集索引的查询效率，比采用非聚集索引的查询效率略高。在我们上述的例子中，第一次使用主键进行查询，系统会使用聚集索引进行查找，而第三次我们使用非聚集索引进行查找，效率会降低。因此在需要多次查询的场景下，我们的SQL语句应尽量使用主键索引进行查询。

除了我们之前描述的几种索引类型之外，索引还可以根据字段个数分为单一索引和联合索引。

单一索引是指使用单个列创建的索引，如我们上述的IDX_USERNAME所示，而把多个列组合在一起创建的索引，叫做联合索引。

创建联合索引时，我们需要注意字段的顺序问题，因为字段(a,b,c)和字段(b,a,c)创建出的联合索引，在使用时查询效率可能会不同。

导致这样的原因是由于联合索引存在最左匹配原则，也就是说如果我们创建了联合索引(a,b,c)时，WHERE条件如果为WHERE a = 1 AND b = 2 AND c = 3，则会匹配上联合索引，如果条件为WHERE b = 2，那么联合索引会失效，查询就会走全表扫描的方式去查找。还有一些范围查找操作也会导致联合索引失效，如果某一列使用了<，<=，>，>=，between等，那么此列后面的列就无法使用到索引。

总结

今天我们讲述了索引的基本概念，索引的类型以及索引的基本使用方式。合理使用索引可以帮助我们提升查询效率，但索引也存在一些缺点，如单独占用空间，降低数据库的写操作性能等，所以我们在使用索引时需要权衡索引的利与弊。

在实际工作中，我们也要根据查询的业务逻辑来决定如何建立索引，牢记最左匹配原则，适当对语句进行改写以便于索引生效，也可以大大提升数据查询的性能。

好了，今天我们要讲的内容到这里就结束了，如果有什么疑问或者启发，欢迎大家在评论区进行留言。下一期我们会从索引背后的原理：B树和B+树的算法实现进行讲解，敬请期待！

您的点赞和在看是我创作的最大动力，感谢支持

公众号：wacky的碎碎念

知乎：wacky