在数据库管理系统中，索引作为一种重要的数据结构，能够显著提升数据查询与检索的效率。本篇文章将详细阐述索引的定义、功能、优缺点、底层数据结构以及如何为表字段添加索引等知识，以帮助读者深入理解并熟练运用索引。

索引，顾名思义，是一种用于加速数据查询和检索的数据结构。常见的索引结构包括B树、B+树和Hash。

索引的作用犹如目录的功能。设想一下，我们在查阅字典时，若没有目录，可能需要逐一翻阅全书才能找到目标字词，效率较低。而若有目录，只需根据目录指引，便可迅速定位到目标页面，事半功倍。

索引的优缺点

优点：

1. 使用索引能极大地提升数据的检索速度（降低检索所需数据量），这也是创建索引的主要原因。

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

缺点：

1. 创建索引和维护索引需投入大量时间精力。在对表中的数据进行增删改时，若数据具有索引，则索引也需要相应地进行动态更新，这可能会降低SQL执行效率。

2. 索引需要占用物理文件存储空间，且在一定程度上会消耗存储资源。

然而，索引在使用上真的能带来查询性能的提升吗？

大部分情况下，索引查询相较于全表扫描确实更高效。但如果数据库数据量过大，即使有了索引，查询速度也未必优于全表扫描。因此，在实际应用中，我们需要权衡索引带来的查询性能提升与维护索引所付出的成本。

，这主要有以下几点原因：

1. 哈希表虽然具有较高的查找效率，但插入和删除操作的性能较差。在实际应用中，如数据库管理系统，需要频繁进行数据的增删改操作，而哈希表在这方面的性能表现不佳。

2. MySQL 的设计目标是支持 ACID（原子性、一致性、隔离性和持久性）事务。哈希表不支持事务，因此在数据库管理系统中，不适合作为主要的数据存储结构。

3. 在早期，哈希表的设计者并没有预见到未来的高并发场景。随着互联网的发展，数据库系统的并发访问量不断增大，对数据存储的高效性和稳定性的要求越来越高。相比之下，哈希表在高并发场景下的性能和稳定性无法满足现代数据库系统的要求。

4. MySQL 的开发者认为，对于大部分应用场景，B+树的性能已经足够优秀。B+树是一种平衡多路搜索树，相比哈希表，它具有更好的查询性能，同时支持事务处理。因此，在 MySQL 中采用了 B+树作为主要的索引数据结构。

总之，尽管哈希表在单机环境具有较高的查找效率，但在高并发、事务支持的场景下，B+树的性能更优。因此，MySQL 选择了 B+树作为索引数据结构。

为了索引的数据结构？

1.Hash冲突问题：我们之前也提到了Hash冲突的问题。然而，对于数据库而言，这还不足以成为最大的缺点。

2.Hash索引不支持顺序和范围查询（这是Hash索引最大的缺点：如果我们需要对表中的数据进行排序或进行范围查询，那么Hash索引就无能为力了。试想一种情况：SELECT * FROM tb1 WHERE id 500; Copy to clipboardErrorCopied 在这种范围查询中，优势非常大，只需遍历比500小的叶子节点即可。但是，Hash索引是基于hash算法来定位的，难道还需要对1-499的数据，每个都进行一次hash计算来定位吗？这正是Hash的最大缺点。

B树&B+树

B树又称B-树，全称为多路平衡查找树。B+树是B树的一种变体。B树和B+树中的B表示“平衡”（Balanced）。

目前，大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为索引结构。

B树&B+树两者有何异同？

•B树的所有节点既存放键（key），也存放数据值（value）。

•B+树的每个节点仅存放键（key）和指针（pointer）。

据数据显示，B+树只有叶子节点存放key和data，其他内部节点只存放key。•B树的叶子节点都是独立的；B+树的叶子节点有一条引用链指向与它相邻的叶子节点。•B树的检索过程相当于对范围内的每个节点的关键字进行二分查找，可能还未到达叶子节点，检索就已结束。而B+树的检索效率非常稳定，所有查找都是从根节点到叶子节点的顺序进行，叶子节点的顺序检索显然更为便捷。

在MySQL中，MyISAM引擎和InnoDB引擎都采用B+Tree作为索引结构，但它们的实现方式有所不同。（以上内容摘自《Java工程师修炼之道》）

在MyISAM引擎中，B+Tree的叶子节点的data域存放的是数据记录的地址。在索引检索时，首先按照B+Tree搜索算法搜索索引，若指定的Key存在，则取出其data域的值，接着以data域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。

而在InnoDB引擎中，其数据文件本身就是索引文件。相较于MyISAM，索引文件和数据文件是分离的。InnoDB的表数据文件本身就是按照B+Tree组织的一个索引结构，树的叶子节点data域存放的数据记录实际上是相互关联的。

域保存了完整的数据记录。这个索引的 key 是数据表的主键，因此 InnoDB 表数据文件本身就是主索引。这被称为“聚簇索引（或聚集索引）”，而其余的索引则作为辅助索引，辅助索引的 data 域存储相应记录主键的值而不是地址，这也是与 MyISAM 不同的地方。在根据主索引搜索时，可以直接找到 key 所在的节点并取出数据；而在根据辅助索引查找时，则需要先取出主键的值，再走一遍主索引。因此，在设计表时，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样可能会造成主索引频繁分裂。

索引类型

1. 主键索引（Primary Key）

2. 辅助索引（Secondary Index）

主键索引：数据表的主键列使用的就是主键索引。一张数据表只能有一个主键，且主键不能为 null，也不能重复。在 MySQL 的 InnoDB 中，如果没有显式地指定表的主键，InnoDB 会自动检查表中是否存在唯一索引的字段，如果有，则选择该字段作为默认的主键。否则，InnoDB 将自动创建一个长度为 6 字节的自增主键。

辅助索引：辅助索引也称为二级索引，因为其叶子节点的数据存储的是主键。也就是说，通过辅助索引，可以定位到主键所在的位置。

唯一索引（Unique Key）、普通索引（Index）和前缀索引（Prefix）都属于二级索引。这些索引类型可以帮助我们在数据库中更快地查找和访问数据。

1. 唯一索引（Unique Key）：这是一种约束，它的属性列不允许出现重复的数据，但允许数据为空值。一张表可以有多个唯一索引。建立唯一索引的主要目的是确保属性列数据的唯一性，而非提高查询效率。

2. 普通索引（Index）：它的主要功能是加速数据查询，一张表可以有多个普通索引，且允许数据为空值或重复。

3. 前缀索引（Prefix）：它只适用于字符串类型的数据。前缀索引是基于文本的前几个字符创建索引，因此相对于普通索引，建立的数据更小。

4. 全文索引（Full Text）：这是用于检索大型文本数据中的关键字的索引技术，目前被搜索引擎数据库广泛采用。在 MySQL 5.6 之前，只有 MYISAM 引擎支持全文索引，而在 5.6 及之后的版本，InnoDB 引擎也加入了这一功能。

关于二级索引的类型，我们有聚集索引和非聚集索引两种。

聚集索引（Aggregate Index）：这种索引将索引结构和数据一起存储。主键索引就是一种聚集索引。

在 MySQL 中：

在InnoDB引擎中,表的.ibd文件包含该表的索引和数据。对于InnoDB引擎表来说,索引(B+树)的每个非叶子节点存储索引,而叶子节点则存储索引和索引对应的数据。

聚集索引具有非常快的查询速度,因为整个B+树本身就是一颗多叉平衡树,叶子节点也都是有序的。因此,定位到索引的节点,就相当于定位到了数据。然而,聚集索引存在依赖有序数据的问题,因为在插入或查找数据时,如果索引的数据不是有序的,就需要在插入时进行排序,这会导致性能下降。此外,聚集索引的更新代价较大,因为当对索引列的数据进行修改时,对应的索引也需要进行修改,而且叶子节点还存放着数据,修改代价显然较大。因此,对于主键索引来说,主键通常是不可被修改的。

非聚集索引是指索引结构和数据分开存放的索引。其中,二级索引属于非聚集索引。与聚集索引不同,非聚集索引的索引信息存储在单独的文件中,而不像聚集索引一样直接存储在表的.ibd文件中。MYISAM引擎的表的.MYI文件包含表的索引,该索引采用B+树结构,每个叶子节点和叶子非叶子节点都存储索引和索引对应数据的指针,指向.MYD文件中的数据。

引用的叶子节点并不一定存放数据的指针，因为二级索引的叶子节点存放的是主键，可以根据主键在表中查找数据。

非聚集索引的优点在于其更新代价比聚集索引要小。非聚集索引的叶子节点不存放数据。

然而，非聚集索引也有一些缺点。它与聚集索引一样，依赖于有序的数据。它可能会进行二次查询（回表）。这应该是非聚集索引的最大缺点。当查询到索引对应的指针或主键后，可能还需要根据指针或主键到数据文件或表中查询。

以下是MySQL表的文件截图：

聚集索引和非聚集索引：

非聚集索引是否一定需要回表查询（覆盖索引）？

非聚集索引不一定需要回表查询。

假设有这样一个场景，用户准备使用SQL查询用户名，而用户名字段正好建立了索引。

```sql

SELECT name FROM table WHERE name='guang19';

```

在这个例子中，索引的key（索引字段）本身就是name，查询到对应的name直接返回就可以了，无需回表查询。即使是MyISAM，如果SQL查询的就是主键，也不需要回表查询，因为它的主键索引的叶子节点存放的是指针。

综上所述，非聚集索引在某些情况下可以避免回表查询，从而降低了查询成本。

FROM table WHERE id = 1;

主键索引本身的关键字就是主键，查到后即可返回结果。这种情况被称为覆盖索引。

覆盖索引

当一个索引包含了（或者说覆盖了）需要查询的所有字段的值时，我们称之为“覆盖索引”。在InnoDB存储引擎中，非主键索引的叶子节点存储的是主键加上列值。最后还需要通过主键进行二次查询，这样会比较慢。覆盖索引是将要查询的字段与索引对应，从而避免了回表查询的过程！

覆盖索引：

创建索引时的注意事项

1. 选择合适的关键字创建索引：

• 不为空的字段：索引字段的数据应尽量不空，因为对于为空的字段，数据库难以对其进行优化。如果某个字段频繁被查询，但无法避免其为空，建议使用0、1、true、false等固定值代替。

在实际应用中，创建合适的索引可以提高查询效率，减少不必要的数据读取。同时，确保索引字段的数据类型、长度和约束条件等与实际情况相符，以保证查询结果的正确性和完整性。

1. 使用意义明确的短语或短字符作为替代。•频繁查询的字段：我们创建索引的字段应该是查询操作非常频繁的字段。•用作条件的查询字段：被作为WHERE条件查询的字段，应考虑建立索引。•需要频繁排序的字段：索引已排序，这样查询可以利用索引的排序，加快排序查询时间。•常用于连接的字段：常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。

2. 对于被频繁更新的字段，建立索引需谨慎。虽然索引能带来查询上的效率，但维护索引的成本也不小。如果一个字段不被经常查询，反而被经常修改，那么就不应在这样的字段上建立索引。

3. 尽量考虑建立联合索引而非单列索引。索引需要占用磁盘空间，可以将其视为一棵B+树。如果一个表的字段过多，索引过多，当数据量达到一定程度时，索引占用的空间会很大，且修改索引时，所需时间较多。而联合索引则可以将多个字段放在一个索引上，节省磁盘空间，且在修改数据时，操作效率更高。

4.

注意避免冗余索引。冗余索引指的是索引的功能相同，能够命中索引(a, b)就肯定能命中索引(a)，那么索引(a)就是冗余索引。例如，（name, city）和（name）这两个索引就是冗余索引，能够命中前者的查询肯定是能够命中后者的。在大多数情况下，都应尽量扩展已有索引，而非创建新索引。

5. 考虑在字符串类型的字段上使用前缀索引代替普通索引。前缀索引仅限于字符串类型，相较于普通索引会占用更小的空间，因此可以考虑用前缀索引替代普通索引。

使用索引的一些建议：

• 对于中至大型表，索引是非常有效的；但对于特大型表，维护开销会很大，因此不适合建立索引。

• 避免在WHERE子句中对字段施加函数，否则会导致无法命中索引。

• 在使用InnoDB时，使用与业务无关的自增主键作为主键，即使用逻辑主键，而非业务主键。

• 删除长期未使用的索引，未使用的索引存在会造成不必要的性能损失。MySQL 5.7可以通过查询sys库的schema_unused_indexes视图来查询哪些索引从未被使用。

• 当使用limit offset查询较慢时，可以借助索引来提升性能。

1. 为表 `table_name` 添加主键索引。

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` );

2. 为表 `table_name` 添加唯一索引。

ALTER TABLE `table_name` ADD UNIQUE ( `column` );

3. 为表 `table_name` 添加普通索引。

ALTER TABLE `table_name` ADD INDEX index_name ( `column` );

4. 为表 `table_name` 添加全文索引。

ALTER TABLE `table_name` ADD FULLTEXT ( `column` );

5. 为表 `table_name` 添加多列索引。

ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` );