19. 什么是索引？为什么在数据库中使用索引？如何在MySQL中创建和删除索引？

PangHu大约 5 分钟

索引（Index） 是数据库中的一种数据结构，用于加速数据的查找。索引类似于书籍的目录，可以通过索引快速找到所需的数据，而无需扫描整个表。索引是基于表中的一个或多个列创建的，常见的索引结构包括B-Tree、Hash等。

在MySQL中，索引通常存储在B-Tree数据结构中，这使得MySQL能够高效地进行查找、插入、删除和范围扫描操作。

为什么在数据库中使用索引？

使用索引的主要目的是提高查询性能，尤其是在大数据集上，索引的作用非常明显。以下是使用索引的几个主要原因：

加快查询速度：索引大大减少了需要扫描的行数，从而加速查询。特别是在大表上，索引可以将全表扫描（Full Table Scan）转化为快速的索引查找。
提高排序性能：如果查询包含 ORDER BY 子句，使用索引可以减少排序的开销。索引可以直接返回排序后的数据。
优化组合查询：在 JOIN 操作中，索引可以帮助快速找到需要连接的行，从而优化连接查询。
提高唯一性检查效率：唯一索引（如主键、唯一键）确保了列值的唯一性，并加速了插入和更新操作中的唯一性检查。

在MySQL中创建索引

MySQL提供了多种方式来创建索引。你可以在创建表时创建索引，也可以在表创建之后添加索引。

1. 创建表时创建索引

在创建表的同时，可以通过 PRIMARY KEY、UNIQUE、INDEX 或 FULLTEXT 关键字来创建索引。

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,  -- 主键索引
    employee_name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10, 2),
    INDEX idx_department (department)  -- 普通索引
);

2. 在现有表上创建索引

可以使用 CREATE INDEX 语句或 ALTER TABLE 语句在现有表上创建索引。

使用 CREATE INDEX 语句创建索引：

CREATE INDEX idx_employee_name ON employees(employee_name);

使用 ALTER TABLE 语句创建索引：

ALTER TABLE employees ADD INDEX idx_department_salary (department, salary);

在以上示例中，idx_department_salary 是一个复合索引（Composite Index），它同时包含 department 和 salary 两列。复合索引在涉及多个列的查询中可以提供更好的性能。

在MySQL中删除索引

当索引不再使用或对性能不再有帮助时，可以使用 DROP INDEX 语句或 ALTER TABLE 语句删除索引。

1. 使用 `DROP INDEX` 删除索引

DROP INDEX idx_employee_name ON employees;

2. 使用 `ALTER TABLE` 删除索引

ALTER TABLE employees DROP INDEX idx_department_salary;

索引的类型

MySQL支持多种类型的索引，每种类型都有不同的用途和特点：

主键索引（Primary Key Index）：
- 唯一标识表中的每一行。主键列上的索引不允许重复值，也不允许NULL值。
- 在创建表时可以通过PRIMARY KEY约束自动创建。
唯一索引（Unique Index）：
- 保证索引列中的所有值是唯一的，允许一个NULL值。
- 使用UNIQUE关键字创建。
```
CREATE UNIQUE INDEX idx_unique_employee_name ON employees(employee_name);
```
普通索引（Normal/Non-Unique Index）：
- 允许重复值，用于加速查询。
- 使用INDEX或KEY关键字创建。
全文索引（Fulltext Index）：
- 用于加速基于文本的搜索，常用于搜索引擎中的自然语言处理。
- 使用FULLTEXT关键字创建，仅支持CHAR、VARCHAR和TEXT列。
```
CREATE FULLTEXT INDEX idx_fulltext_name ON employees(employee_name);
```
组合索引（Composite Index）：
- 包含多个列的索引，索引的顺序很重要，可以优化多列组合查询。
- 使用INDEX关键字创建。
```
CREATE INDEX idx_combined ON employees(department, salary);
```

索引的注意事项

索引的维护成本：虽然索引可以加速查询，但它也增加了插入、更新和删除操作的成本，因为每次数据变化都需要更新索引。因此，不要为每一列都创建索引，应根据查询频率和重要性来合理设置索引。
选择性：索引最适合选择性高的列，即列中不同值的比例较高。例如，对于布尔型列（true/false），索引的效果可能很差，因为选择性很低。
索引冗余：避免创建冗余索引。例如，如果已经有了(department, salary)的组合索引，就不需要再单独为department创建索引。
复合索引的顺序：在创建复合索引时，应该将选择性高的列放在前面，以获得最佳性能。

总结

索引：是用于加速数据库查找的结构，类似于书籍的目录。
使用索引的好处：包括加速查询、提高排序性能、优化连接查询等。
创建索引：可以在创建表时创建索引，也可以使用CREATE INDEX或ALTER TABLE在现有表上创建索引。
删除索引：可以使用DROP INDEX或ALTER TABLE语句删除不再需要的索引。
索引的类型：包括主键索引、唯一索引、普通索引、全文索引和组合索引等。

合理使用索引可以显著提高数据库的查询性能，但也需要平衡索引的维护成本。理解索引的工作原理和选择最佳的索引策略，对于优化数据库性能至关重要。