索引的作用(不适合创建索引的是)

作者|惊奇10

编辑|屠敏

头部| CSDN下载自视觉中国

本文贡献给“业余码农”

索引的概念几乎每个人都遇到过，即使你没有了解过数据库中的索引，在生活中也难免会接触到。比如图书的目录，字典的查询页面，图书馆的主题检索等等。其实这些都是指数，起的作用差不多。

至于数据库，只是抽象了索引的概念，使得建立索引的过程更加灵活自由，从而优化了数据库在不同场景下的查询效率。

索引在数据库的实际应用场景中非常常见，数据库的优化离不开索引的优化。同时，指数相关知识也是面试频率较高的考点之一，是考生理论与实际相结合的最直接体现。

因此，本文将从基础理论出发，按照逻辑的观点介绍MySQL的索引分类和实现，并通过数据结构的实现原理说明不同结构在索引建立上的优缺点。同时，将根据物理存储的方式，分析索引的组织特点和应用场景。最后，根据不同的应用场景，尽力探索如何建立高性能的索引。文章的结构如下:

概念概念

什么是索引？

指数似乎没有一个非常明确的定义，而是一个定性的描述。简单地说，索引是一种数据结构，它以一种特殊的形式在数据库中存储记录。通过索引，可以显著提高数据查询的效率，从而提高服务器的性能。

从专业角度讲，索引是一个有序列表，其中存储了索引的值和包含该值的数据所在的行的物理地址。当数据库非常大时，索引可以大大加快查询速度。这是因为使用索引后，不需要扫描整个表来定位一行数据，而是通过索引表找到该行数据对应的物理地址，然后访问对应的数据。

说到索引，其实并不是MySQL数据库独有的机制。在关系数据库中也有类似的不同实现。这里我们只讨论MySQL数据库中的索引实现。

其实MySQL的索引并不准确。因为在MySQL中，索引是在存储引擎层而不是服务器层实现的。这意味着所讨论的索引是由InnoDB引擎或MyISAM引擎或其他存储引擎准确实现的。

所以即使在MySQL中，索引也没有统一的标准，不同存储引擎实现的索引工作方式也不一样。并非所有存储引擎都支持同一类型的索引，即使多个引擎支持同一类型的索引，它们的底层实现也可能不同。

为什么需要索引

说到这里，索引似乎给数据库增加了一个“目录页”，可以方便数据查询。但是指数的作用仅仅如此吗？为什么需要大费周章的建立和优化索引呢？

题外话，其实我从来不喜欢在字典里查目录页，不管是中文的还是英文的。因为慢，所以一个一个找索引效率很低。我习惯的方式是直接打开字典，根据打开的位置来回调整。比如我要找“酱姜”这个词，我就随便翻到一页。可能是“F”开头，在“J”前往回一点点就行了；如果你随意转到“L”，那就往前转一点。重复直到你找到它。

这大概是一种类似二分搜索法的方式，似乎摆脱了索引的束缚，也能达到更高的查询效率。但实际上，转念一想，在计算机的运算处理中，“一个一个找索引”的过程其实是很快的，跟人工比对部首的效率没法比。同时，为什么我可以直接打开字典，根据字母进行调整？其实是因为我脑子里有一个大概的“索引表”，我知道每个字母对应的是词典中的哪个位置。虽然很模糊，但却是真实的。(很难用武力解释一波...)

这样就可以看出，index的一个很大的优点就是，如其概念所述，使用index后，不需要扫描整个表来定位某一行的数据，而是先通过index表找到该行数据对应的物理地址，然后访问对应的数据。这种方式自然减少了服务器响应时扫描数据库所需的数据量。

不仅如此，在执行数据库的范围查询时，如果不使用索引，MySQL会先扫描数据库的所有行数据并筛选出目标范围内的行记录，对这些行记录进行排序并生成一个临时表，然后通过临时表返回用户查询的目标行记录。这个过程会涉及到临时表的建立和行记录的排序，在目标行记录较多的情况下，会极大地影响范围查询的效率。

因此，在添加索引时，由于索引本身的顺序性，在范围查询时已将所选行记录按顺序排列，从而避免了重新排序和需要建立临时表的问题。

同时，由于索引底层的顺序，可以避免数据查询时在磁盘不同扇区的随机寻址。索引使用后，磁盘上的数据访问可以通过磁盘预读粗略地顺序寻址。本质上，这是根据局部性原理实现的。

局部性原则:当一个数据被使用时，附近的数据通常会被立即使用。程序运行过程中需要的数据通常是集中的。由于顺序磁盘读取的高效率(没有寻道时间，只有一点旋转时间)，磁盘预读取可以提高本地程序的I/O效率。

磁盘预读要求每次预读的长度一般是页面的整数倍。而且数据库系统将一个节点的大小设置为等于一个页面，这样每个节点只需要一个I/O就可以满载，这里的页面是通过基于页面的内存管理来实现的，这里简单说一下概念。

分页机制是将内存地址空分成若干个固定大小的小页面，每个页面的大小由内存决定。这样做是为了从虚拟地址映射到物理地址，并提高内存和磁盘的利用率。

所以，总结一下。指数的存在有很大的好处，主要表现在以下三个方面:

索引大大减少了服务器需要扫描的数据量。

索引可以帮助服务器避免排序和临时表。

索引可以将随机I/O转换成顺序I/O。

以上三点可以大大提高数据库查询的效率，优化服务器的性能。因此，一般来说，为数据库添加高效的索引是优化数据库的重要任务之一。

然而，任何事物都有两面性。索引的存在可以提高性能，自然会在其他方面付出额外的代价。

索引本身是作为表存储的，所以会占用额外的存储空；

索引的创建和维护需要时间成本，时间成本随着数据的增加而增加。

建立索引会降低数据修改(删除、添加、修改)的效率，因为修改数据表的同时需要修改索引表；

所以对于非常小的表，使用索引的代价会比直接扫描整个表的代价更大，所以这个时候没有必要使用索引。没办法。成年人的世界总是那么趋利避害。

分类如果从逻辑上划分索引，可以分为单列索引、全文索引、组合索引和空索引。单列索引可以分为主键索引、唯一索引和普通索引。这里的逻辑可以从SQL语句或者数据库关系表的角度来理解。下面简单介绍一下这些索引的作用和用法，以及修改表格时如何添加索引。

主键索引

即主索引，根据主键进行索引。不允许重复值和空值。

主键:数据库表中一列或多列(字段)组合的值，可以唯一标识表中的每一行。

加速查询+唯一列值(无)+表中只有一个

ALTER TABLE 'table_name '添加主键PK _ index(' col ')；唯一索引用于索引的列的值必须是唯一的，并且允许空的值。唯一索引不允许表中的任何两行具有相同的索引值。例如，如果在employee表中为雇员的姓氏创建了唯一索引，这意味着没有两个雇员可以有相同的姓氏。

加速查询+唯一列值(可以拥有)

ALTER TABLE ' TABLE _ name ' ADD UNIQUE index _ name(' col ')；普通索引用表中的普通列构建的索引，没有任何限制。

仅加速查询

ALTER TABLE ' TABLE _ name ' ADD INDEX INDEX _ name(' col ')；全文索引用大文本对象的列构建的索引。

ALTER TABLE 'table_name '添加全文索引ft _ INDEX(' col ')；组合索引通过组合多个列建立的索引。这些列中的值不允许有空值。

多列值组成一个索引，专门用于组合搜索，效率大于索引合并。

ALTER TABLE ' TABLE _ name ' ADD INDEX INDEX _ name(' col 1 '，' col2 '，' col 3 ')；对多行组合进行索引时，遵循最左侧前缀原则。

最左前缀原则:顾名思义，就是最左优先的意思。在上面的例子中，我们创建了(col1，col2，col3)多列索引，相当于创建了(col1)单列索引，(col1，col2)组合索引和(col1，col2，col3)组合索引。

因此，当我们创建多列索引时，我们应该根据业务场景将最常用的列放在最左边的where子句中。

空间索引

数据类型在空之间的字段的索引可以通过底层的R-tree建立。只是用的比较少，可以理解。

实现原理我们知道索引本身的底层是通过数据结构来实现的。然后根据其底层结构，常见的索引类型可以分为hash索引、BTree索引、B+Tree索引等。这里主要介绍这三个指标背后的实现机制。

哈希索引

顾名思义，哈希索引是通过哈希表实现的。哈希表的特征在之前的文章《九种数据结构》中已经有详细描述。通过哈希表键值之间的对应关系，可以在查询时准确匹配索引的所有列。索引存储了根据索引中的索引列计算出的所有哈希码，同时保存了指向哈希表中每个数据行的指针。

上图是通过哈希索引查询行数据的示意图。可以发现哈希索引中也会出现哈希冲突，冲突是通过链地址法解决的。发送冲突时，需要遍历链表，找到最终结果。

在MySQL中，只有内存存储引擎显式支持哈希索引，而innodb隐式支持哈希索引。

这里的隐式支持是指innodb engine有一个特殊的功能“自适应哈希索引”。当innodb注意到某些索引值使用非常频繁，并且符合hash特性(比如每次查询的列都是相同的)时，它会基于B树索引在内存中创建另一个hash索引。这样BTree索引也有一些hash索引的点。这是一个完全自动的内部行为。

由于hash结构的特殊性，用于非常高的检索效率，通过hash函数映射可以一步完成。但是也因为特殊的结构，哈希索引只适用于一些特定的场合。哈希索引[1]的限制:

范围查询，如where a >:5；仅支持等效的比较查询，包括=，in，< = & gt

不能用来避免数据的排序操作；由于hash函数的映射过程，hash前后的大小关系丢失，所以无法按照索引值的顺序存储。

不支持某些索引列的匹配查找，因为哈希索引总是使用索引列的全部内容来计算哈希值。例如，散列索引建立在数据列(A，B)上，如果查询只有数据列A，则不能使用该索引。

无法避免表格扫描。因为当出现哈希冲突时，存储引擎必须遍历链表中的所有行指针(zipper方法)，逐行比较，直到找到所有符合条件的行。

当哈希冲突较多时，索引维护成本较高，性能不一定比BTree索引高。

BTree 索引

BTree实际上是一个多叉的平衡搜索树。从名字可以看出，BTree一开始是多分支搜索树，也就是说它是顺序的；其次，BTree是平衡的，即其左右子树的高度差小于等于1。

事实上，BTree需要满足以下条件:

每个叶节点的高度是相同的；

每个非叶节点由n-1个键和n个指针组成，其中d

叶指针都是；

非叶节点的键都是[key，data]的二元组，其中key表示作为索引的键，data是键值所在行的数据；

常见的BTree树如下所示。

这是一个三阶BTree，可以通过对键值进行排序来查询和检索数据。叶节点的指针是空，所以省略。从上图可以看出，BTree的树形比我们通常的二叉树结构更加扁平矮胖。

之所以这样设计，和读盘的特性有关。我们知道索引时也需要占用物理空空间。其实数据量比较大的时候，索引文件的大小也是很吓人的。考虑到一个表上可能有多个索引、组合索引以及较小的数据行，索引文件的大小可能会达到物理磁盘中数据的1/10甚至1/3。

这意味着索引不能全部加载到内存中。通过索引访问数据时，不可避免地要对磁盘进行读写操作。同时我们也知道，内存的读写速度是磁盘的几个数量级。所以在设计索引结构的时候，要尽可能的减少对磁盘的读写次数，也就是所谓的磁盘I/O次数。

这就是为什么索引会采用BTree的扁平树结构。树的层数越少，磁盘I/O数越少，不仅如此，我们还提到了磁盘预读的局部性原理。根据这个原理和页表机制，可以大大提高读盘时的性能。

与其他二叉树结构相比，BTree对磁盘的I/O次数非常少。但是在实际的数据库应用中仍然存在一些无法解决的问题。

第一，数据线找不到。BTree可以根据主键的顺序定位主键的位置，但是由于数据表的记录有多个字段，所以只定位主键是不够的，还要定位数据行。虽然这个问题可以通过在BTree的节点中存储数据行或者增加定位字段来解决，但是这种方法会大大增加BTree的深度，从而导致I/O次数的增加。

第二，不能处理范围查询。在实际应用中，数据库范围的查询频率非常高，而BTree只能位于一个索引位置。虽然可以通过连续查询范围的左右界来获得，但是这样的操作实际上并不能很好的利用磁盘预读的局部性原理，并且连续查询可能会通过预读造成物理地址的分散，使得I/O效率不高。

第三，当数据量较大时，BTree的高度仍然会变得很高，搜索效率仍然会明显下降。

问题永远是改善的前提。基于以上考虑，出现了BTree的优化版本，即B+Tree。

B+Tree索引

+BTree乍一看是在BTree基础上的改进，那么改变了什么呢？事不宜迟，就上图吧。

该图实际上是一个具有顺序索引的b+树。与最基本的b+树的区别在于叶子节点是否用指针连接。一般数据库常用的结构就是这种带顺序索引的b+树。下面的文章还讨论了具有顺序索引的B+树结构。

对比BTree和B+Tree，我们可以发现它们在以下三个方面有所不同:

非叶节点只存储关键信息，不再存储数据。

所有叶节点之间有一个链指针，指向下一个叶节点。

所有数据都存储在叶节点中。

再看b+树，是不是看起来像一棵树和一个有序链表的组合？事实上，B+Tree与树和链表相比有一些优势。树形结构使得有序检索更简单，I/O次数更少；有序链表结构使得按照键值排序的顺序遍历所有记录成为可能。

当用作索引结构时,+ b+树可以带来的好处是:

第一，I/O次数少。这是因为，如上所述，BTree的节点存储在内存页面中。那么在内存页大小相同(一般为4k)的情况下，B+Tree可以存储更多的键值，所以整个树结构的高度会更小，需要的I/O数量也会更少。

第二，数据遍历更方便。这个优势显然是有序链表带来的。通过叶节点的链接，所有数据的遍历只需要在一个线性链表上完成，非常适合区间检索和范围查询。

第三，查询性能更稳定。自然，因为数据只存储在叶节点，所以所有的数据查询都会到达叶节点，叶节点的高度是一样的，所以理论上所有数据的查询速度都是一样的。

由于B+树优秀的结构特性，经常被用作索引的实现结构。在MySQL中，存储引擎MyISAM和InnoDB分别用B+树实现了响应式索引设计。

物理存储

虽然在MyISAM和InnoDB中可以使用B+树结构，但这两者在物理存储级别实现索引的方式不同。InnoDB实现聚集索引，而MyISAM实现非聚集索引。在介绍聚集索引之前，我们需要知道以下几点:什么是Page，不，什么是“主键索引”和“二级索引”。

其实概念很简单。我们刚才在讲b+树的时候不是说过，树的非叶节点只存储键值吗？是的，这是关键值。当这个键值是数据表的主键时，就建立了主键索引。当这个键值是另一个字段时，它是一个二级索引。可以得出结论，主键索引只能有一个，但二级索引可以有多个。

聚集索引和非聚集索引的区别是根据其对应的主键索引和二级索引的不同特征来实现的。

聚簇索引

回到聚集索引。让我们失去一个定义。

聚簇索引的主键索引的叶节点存储键值本身对应的数据；索引的次叶节点存储对应于键值的数据的主键值。

这句话信息量挺大的。首先，分析第一句话。主键索引的叶节点存储与键值本身对应的数据。

我们知道存储在主键索引中的键值就是主键。也就是说，聚集索引的主键索引在叶节点中存储主键和主键对应的数据。和数据主键索引作为叶节点的一部分存储在一起。

然后分析第二句，二级索引的叶节点存储键值对应的数据的主键值。

我们还知道存储在辅助索引中的键值是非主键字段。也就是说，可以通过二级索引找到非主键字段对应的数据行中的主键。

重点来了。当然，主键索引和二级索引的组合也无能为力。当直接使用主键进行检索时，可以通过主键索引直接获取数据；使用非主键进行检索时，需要通过二级索引获取主键，然后通过这个主键在主键索引中找到对应的数据行。

举个例子吧。假设有这样一个数据表。

然后，使用聚集索引的存储方式，对应的主键索引为:(主键为ID)

对应的二级索引为:(键值为名称，大致意思):

因此，在使用ID = 7的条件查找主键时，可以根据B+树搜索算法找到对应的叶节点，进而得到行数据。姓名列的条件搜索需要两步:第一步是在二级索引B+树中搜索姓名，到达其叶节点获得对应的主键。第二步，使用主键在主键索引B+树种中再次执行B+树搜索操作，最后到达叶节点，获得整行数据。

最后，总结以上过程后，聚类索引的实际过程可以分为以下两个过程。现在这张图应该能看懂了。

非聚集索引学习了聚集索引之后，非聚集索引就简单多了。还是那句话，先定义一下。

除了主索引不允许重复和不允许空的值之外，非聚集索引的主键索引和辅助索引几乎相同。它们的叶节点都存储与键值对应的数据的物理地址。

与聚集索引相比，上面的定义能说明什么？首先，一级索引和二级索引的叶节点都存储了键值对应的数据的物理地址，这说明一级索引和二级索引都可以直接获取数据，而不必像聚簇索引一样在二级索引中四处寻找。

同时也说明了叶子节点存储物理地址的一个点，也就是说数据实际存在于另一个地方，没有存储在B+树的节点中。这说明非聚集索引的数据表和索引表是分开存储的。

同样，对非聚集索引的检索过程进行了总结。

无论是主键索引还是二级索引的检索过程，只需要通过对应的B+树进行搜索，得到数据对应的物理地址，然后就可以依次通过磁盘I/O访问数据了。

比较聚集索引和非聚集索引，可以发现两者的主要区别在于数据是否存储在B+树的节点中，即数据和索引是否存储在一起。这种差异造成的最大问题是聚集索引的索引顺序与数据本身的顺序相同，而非聚集索引的顺序与数据的顺序无关。

索引优化引入这么多索引，其实最终是为了建立一个高性能的索引策略，优化数据库中的索引。索引优化的角度很多，针对具体的业务场景可以采取不同的优化策略。这里考虑到文章篇幅，就不详细介绍了。下一次，我将发表一篇专门讨论索引优化的文章。简单列举几个优化时可以考虑的方向:

1独立列。列不能是表达式或函数参数的一部分。

2前缀索引和索引选择性。其实两者是相互制约的，制约条件在于前缀的长度。一般来说，前缀应该足够长以保证高选择性(保证查询效率)，但不能太长以至于不能保存空。

3尽量使用叠加索引。索引覆盖是指一个索引包含所有需要查询的字段的值，这样只需要扫描索引而不需要读取数据行，从而大大提高了性能。

4使用索引扫描进行排序。要知道，扫描索引本身速度就很快。设计索引时，您可以尽可能使用相同的索引来同时满足排序和查找行数据。

最后，在构建索引时给出一些提示:

1首先使用自增键作为主键。

记住最左边的前缀匹配原则。

3索引列不能参与计算。

4选择区分度高的列进行索引。

5如果可以扩展，就不要创建新的索引。

总结索引的概念和原理是我们在理解和掌握数据库的过程中逃不开的重点。事实上，建立高性能指数对实际应用场景也具有重要意义。本文的目的仍然是从简单到深入，从概念到实现到最终的优化策略来介绍索引。

学无止境。在掌握了基本的理论和概念之后，就需要在实际的服务器开发场景中，针对具体的问题和服务来设计和使用索引优化方案。本事不够，还需要努力。

参考MySQL，Baron Schwartz等，电子工业出版社

公众号海系列文章:

https://www.jianshu.com/p/9e9aca844c13

https://www.runoob.com/mysql/mysql-index.html

https://www.cnblogs.com/Aiapple/p/5693239.html

https://blog.csdn.net/tongdanping/article/details/79878302

https://www.cnblogs.com/igoodful/p/9361500.html