MySQL：索引（Index）理论知识

关系型数据库中，索引（Index）是一种用于加快数据检索速度的数据结构。它通过存储某些列的值的指针，来提高查询的效率。

一般情况下，索引是在表中的一个或多个列上创建的，它们可以显著提高查询的效率。

当我们使用 SELECT 语句查询一个表时，如果没有索引，数据库需要扫描每一行数据，这会花费大量时间，特别是对于大型的数据集。如果有了索引，数据库就可以用更快的方式定位到需要的数据行，因为它可以使用索引中存储的值来快速查找匹配的行。

索引的优缺点

优点

• 索引大大减小了服务器需要扫描的数据量，从而大大加快数据的检索速度，这也是创建索引的最主要的原因。

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

• 索引可以将随机 IO 变成顺序 IO

• 索引对于 InnoDB（对索引支持行级锁）非常重要，因为它可以让查询锁更少的元组，提高了表访问并发性

• InnoDB 在二级索引上使用共享锁（读锁），但访问主键索引需要排他锁（写锁）

• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

• 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

• 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

• 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

缺点

虽然索引可以显著提高查询的效率，但也会增加写入操作的开销。因为每次写入操作都需要更新索引，这可能导致写入操作比没有索引的情况下慢一些。因此，在创建索引时需要权衡查询效率和写入效率之间的权衡。

• 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加

• 索引需要占用物理空间，除了数据表占用数据空间之外，每一个索引还要占用一定的物理空间，如果需要建立聚簇索引，那么需要占用的空间会更大

• 对表中的数据进行增、删、改的时候，索引也要动态的维护，这就降低了整数的维护速度

• 如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

• 对于非常小的表，大部分情况下简单的全表扫描更高效；

创建索引的准则

索引是建立在数据库表中的某些列的上面。因此，在创建索引的时候，应该仔细考虑在哪些列上可以创建索引，在哪些列上不能创建索引。

需要创建索引的列

• 在经常需要搜索的列上，可以加快搜索的速度

• 在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构

• 在经常用在连接（JOIN）的列上，这些列主要是一些外键，可以加快连接的速度

• 在经常需要根据范围（<，<=，=，>，>=，BETWEEN，IN）进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的

• 在经常需要排序（order by）的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；

• 在经常使用在 WHERE 子句中的列上面创建索引，加快条件的判断速度。

无需创建索引的列

• 对于那些在查询中很少使用或者参考的列不应该创建索引。

  若列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。

• 对于那些只有很少数据值或者重复值多的列也不应该增加索引。

  这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。

• 对于那些定义为 TEXT, IMAGE 和 BIT 数据类型的列不应该增加索引。这些列的数据量要么相当大，要么取值很少。

• 当该列修改性能要求远远高于检索性能时，不应该创建索引。（修改性能和检索性能是互相矛盾的）

索引分类

MySQL 的索引有两种分类方式：逻辑分类和物理分类。

逻辑分类

按逻辑分类有多种逻辑划分的方式，如按功能划分，按组成索引的列数划分等。

按列数划分

单例索引

单例索引：在数据库中为单个列创建的索引。一个索引只包含一个列，一个表可以有多个单例索引。

单例索引的特点和用途包括：

1. 提高查询性能：通过为单个列创建索引，可以加快根据该列进行的搜索和排序操作的速度。当查询条件涉及到单个列时，单例索引可以显著提高查询性能。

2. 索引大小较小：相比于多列索引，单例索引只涉及一个列，因此索引本身的大小往往较小。这可以减少索引的存储空间占用，并提高索引的缓存效率。

3. 精确匹配能力：单例索引适用于需要进行精确匹配的查询，例如等值查询。通过单例索引，可以快速定位到符合查询条件的记录。

4. 不适用范围查询：由于单例索引只包含一个列的索引信息，所以对于范围查询（如大于、小于等）的效果并不理想。范围查询可能需要扫描更多的索引页，导致查询性能下降。

使用单例索引时需要权衡索引的选择，因为过多地创建索引可能会造成存储空间的浪费和性能的下降。

<