MySQL 索引详解

一、什么是索引

索引是数据库中一种特殊的数据结构，它类似于现实生活中图书馆的图书目录系统。在图书馆中，如果没有目录，我们想要找一本书可能需要一本一本地翻找，这无疑会耗费大量的时间和精力。而有了目录，我们可以通过书名、作者、主题等多种方式快速定位到想要的书。同样地，在数据库中，索引为表中的数据提供了快速访问的途径。它以一种有序的结构存储了数据表中某些列的值，并且每个值都指向了对应的数据行，这样当我们需要查找数据时，就可以通过索引来快速定位，而不需要扫描整个表。

举个例子，假设我们有一个包含数百万条记录的用户表，其中有一列是用户的登录名。如果没有索引，当我们想要查找某个特定用户的信息时，数据库系统可能需要逐行扫描整个表，这会耗费大量的时间。但如果我们在登录名列上建立了索引，数据库就可以利用这个索引快速地定位到该用户所在的行，就像在图书馆中通过目录快速找到一本书一样。

二、索引的分类

1. 普通索引

普通索引是最基本的索引类型，它允许在表的任意列上创建。它可以提高列的检索速度，但不具有唯一性约束。例如，在一个学生信息表中，我们可以在“姓名”列上创建普通索引，这样在查询特定学生的信息时就可以利用索引快速定位。创建普通索引的语法为：

CREATE INDEX index_name ON table_name (column_name);

2. 唯一索引

唯一索引与普通索引类似，但它要求索引列中的值必须是唯一的，即不允许有重复的值。这在需要确保某一列数据唯一性的情况下非常有用，比如用户的登录名、身份证号码等。创建唯一索引的语法为：

CREATE UNIQUE INDEX index_name ON table_name (column_name);

3. 组合索引

组合索引包含多个列的索引，适用于查询条件中涉及多个列的组合查询。在创建组合索引时，需要注意列的顺序，因为索引的左前缀特性决定了只有查询条件中使用了组合索引的左前缀列时，才能利用该索引。例如，创建一个组合索引 (column1, column2)，那么在查询条件中使用 column1 或 column1 和 column2 的组合时都可以利用该索引，但如果只使用 column2 则无法利用该索引。

4. 主键索引

主键索引是一种特殊的唯一索引，它要求索引列的值唯一且不能为空。每个表只能有一个主键索引，通常在创建表时指定。主键索引不仅用于快速查询，还用于维护数据的完整性。创建主键索引的语法为：

CREATE TABLE table_name (column1 datatype PRIMARY KEY,column2 datatype,...
);

或者在已有表上添加主键索引：

ALTER TABLE table_name ADD PRIMARY KEY (column_name);

5. 聚簇索引与非聚簇索引

聚簇索引：在 InnoDB 存储引擎中，聚簇索引的叶子节点存储了完整的数据行。每个表只能有一个聚簇索引，通常主键索引就是聚簇索引。如果没有定义主键，InnoDB 会尝试使用第一个唯一且非空的索引作为聚簇索引。如果这些都没有，InnoDB 会自动生成一个隐藏的聚簇索引。
非聚簇索引：也称为二级索引或辅助索引，其叶子节点存储的是主键值而不是完整的数据行。当通过非聚簇索引查找数据时，需要先找到主键值，然后再通过主键索引找到实际的数据行。

各类索引比较表

索引类型	特点描述	适用场景	示例
普通索引	基本索引，无唯一性约束，可提高列检索速度	经常用于查询条件中的列	在学生表的“姓名”列上创建普通索引，用于快速查找学生信息
唯一索引	索引列的值必须唯一，允许 NULL 值	需要确保某一列数据唯一性的场景，如登录名、身份证号码等	在用户表的“登录名”列上创建唯一索引，确保每个用户的登录名唯一
组合索引	包含多个列的索引，适用于多列组合查询，需注意列顺序	查询条件中涉及多个列的组合查询	在用户表的“年龄”和“性别”列上创建组合索引，用于快速查询特定年龄和性别的用户组合
主键索引	特殊的唯一索引，要求值唯一且不能为空，每个表只能有一个主键索引	作为表的唯一标识，用于关联查询和维护数据完整性	在用户表的“用户 ID”列上设置为主键索引，作为用户的唯一标识
聚簇索引	叶子节点存储完整数据行，InnoDB 的主键索引通常为聚簇索引	InnoDB 存储引擎中，用于快速通过主键访问数据	InnoDB 表的主键索引，默认为聚簇索引
非聚簇索引	叶子节点存储主键值，需通过主键再查找实际数据行，也称为二级索引或辅助索引	当需要通过非主键列进行查询时，先找到主键值再定位数据行	在 InnoDB 表的非主键列上创建非聚簇索引，用于提高这些列的查询效率

三、索引的劣势

虽然索引可以提高查询效率，但它也存在一些劣势：

增加存储空间：索引本身需要占用一定的存储空间，这会增加数据库的存储开销。尤其是当表中有多个索引时，存储空间的占用会更加明显。
影响数据更新速度：当对表中的数据进行插入、更新或删除操作时，数据库系统不仅要更新数据本身，还需要同时更新相关的索引。这会增加数据更新的开销，导致数据更新操作变慢。
索引维护成本：索引的创建和维护需要一定的成本，包括时间成本和计算资源成本。在数据库设计和优化过程中，需要合理地选择和管理索引，以避免不必要的索引带来的维护负担。

四、什么时候需要创建索引

在以下情况下，通常需要考虑创建索引：

频繁查询的列：如果某一列经常出现在查询条件中（如 WHERE 子句），则在该列上创建索引可以显著提高查询速度。例如，用户表中的登录名列，经常用于登录验证，因此需要创建索引。
连接操作的列：在涉及多个表的连接查询中，如果连接条件中的列没有索引，连接操作可能会变得非常缓慢。因此，在连接列上创建索引可以提高连接查询的效率。
排序和分组的列：当查询中包含排序（ORDER BY）或分组（GROUP BY）操作时，如果这些操作所涉及的列上有索引，数据库可以利用索引的有序性来快速完成排序和分组，从而提高查询性能。
范围查询的列：对于经常进行范围查询的列，如日期列、数值范围列等，创建索引可以加速范围查询的执行。

五、哪些情况不要创建索引

在以下情况下，通常不建议创建索引：

数据更新频繁的列：如果某一列的数据更新非常频繁，如每秒都会进行多次更新操作，那么在该列上创建索引会导致大量的索引维护开销，从而影响数据库的整体性能。
低选择性的列：低选择性列是指该列中包含的值重复较多，区分度较低的列。例如，一个性别列只有“男”和“女”两个值，这样的列选择性较低，创建索引的意义不大，因为数据库在执行查询时可能不会使用该索引。
小表：对于数据量较小的表，即使没有索引，查询操作的速度也可能很快，因为全表扫描的开销相对较小。在这种情况下，创建索引可能并不会带来明显的性能提升，反而会增加存储空间和数据更新的开销。
频繁进行大规模数据删除或 truncating 的表：如果一个表经常需要进行大规模的数据删除或 truncating 操作，那么在该表上创建索引可能会导致索引的频繁重建和维护，增加不必要的开销。