11.2.2 在已建立的数据表中创建索引-MySQL从入门到精通(四)

当前位置:　首页>> 技术小册>> MySQL从入门到精通(四)

### 11.2.2 在已建立的数据表中创建索引

在MySQL数据库中，索引是提高数据检索效率的重要手段。随着数据库应用的不断深入，数据量急剧增长，查询性能成为衡量数据库设计优劣的关键指标之一。索引能够显著减少数据库引擎需要扫描的数据量，从而加快查询速度。在MySQL中，索引可以在数据表创建时指定，也可以在数据表已经建立后通过ALTER TABLE或CREATE INDEX语句添加。本章节将详细探讨如何在已建立的数据表中创建索引，包括索引的基本概念、类型、创建方法以及最佳实践。

#### 1. 索引的基本概念

索引是数据库表中一列或多列的值组成的数据结构，用于加快表中数据的检索速度。MySQL中的索引类似于书籍的目录，通过索引可以迅速定位到数据表中的特定记录，而无需扫描整个表。索引不仅提高了查询效率，还可能在一定程度上影响数据的插入、删除和更新操作的性能，因为每次这些操作发生时，数据库都需要更新索引以保持其一致性。

#### 2. 索引的类型

MySQL支持多种类型的索引，每种索引都有其特定的使用场景和优缺点。以下是一些常见的索引类型：

- **B-Tree索引**：最常见的索引类型，适用于全键值、键值范围或键值前缀查找。MySQL中的InnoDB和MyISAM存储引擎都默认使用B-Tree索引。
- **哈希索引**：基于哈希表实现，只支持等值比较查询，速度快但不适用于范围查询。MEMORY存储引擎支持哈希索引。
- **全文索引**：用于搜索文本中的关键字，而不是直接比较索引中的值。InnoDB和MyISAM存储引擎都支持全文索引，但InnoDB从MySQL 5.6版本开始支持。
- **空间索引**：用于地理空间数据类型，如GIS数据。MySQL中的InnoDB和MyISAM存储引擎都支持空间索引。

#### 3. 在已建立的数据表中创建索引

在MySQL中，对已建立的数据表添加索引主要有两种方式：使用ALTER TABLE语句和使用CREATE INDEX语句。

##### 3.1 使用ALTER TABLE语句创建索引

ALTER TABLE语句不仅可以用来修改表的结构（如添加、删除列），还可以用来添加或删除索引。其基本语法如下：

```sql
ALTER TABLE table_name ADD INDEX index_name (column_name[, column_name] ...);
```

或者，如果是唯一索引：

```sql
ALTER TABLE table_name ADD UNIQUE (column_name[, column_name] ...);
```

示例：假设有一个名为`students`的表，包含`id`（主键）、`name`、`age`和`email`等字段，现在要为`email`字段创建索引：

```sql
ALTER TABLE students ADD INDEX idx_email (email);
```

如果要为`name`和`age`两个字段组合创建唯一索引：

```sql
ALTER TABLE students ADD UNIQUE (name, age);
```

##### 3.2 使用CREATE INDEX语句创建索引

CREATE INDEX语句专门用于创建索引，而不改变表的其他结构。其基本语法如下：

```sql
CREATE INDEX index_name ON table_name (column_name[, column_name] ...);
```

示例：同样以`students`表为例，为`email`字段创建索引：

```sql
CREATE INDEX idx_email ON students (email);
```

#### 4. 索引的最佳实践

虽然索引能够显著提高查询性能，但过多或不当的索引也可能成为系统性能的瓶颈。因此，在创建索引时，应遵循以下最佳实践：

- **选择合适的列创建索引**：通常选择经常作为查询条件的列（WHERE子句）、连接条件（JOIN子句）以及排序和分组操作（ORDER BY和GROUP BY子句）中的列作为索引列。
- **考虑索引的维护成本**：每次对表进行插入、删除或更新操作时，数据库都需要更新索引，这会增加额外的开销。因此，对于写操作频繁的表，应谨慎添加索引。
- **避免冗余索引**：如果两个索引的功能可以相互替代，那么其中一个索引就是冗余的。例如，如果已经有了`(A, B)`的复合索引，那么单独的`A`索引就是冗余的，因为查询时可以通过`(A, B)`索引来覆盖`A`索引的查询需求。
- **使用前缀索引**：对于字符串类型的列，如果列的值很长且查询时经常使用前缀进行匹配，可以考虑使用前缀索引来减少索引占用的空间并提高查询效率。
- **定期评估和调整索引**：随着数据库应用的不断发展，数据量和查询模式都会发生变化。因此，应定期评估现有索引的有效性，并根据需要添加、删除或修改索引。

#### 5. 索引的查询与优化

创建索引后，可以通过`EXPLAIN`语句来查看MySQL是如何执行查询的，从而评估索引的有效性。`EXPLAIN`语句会显示MySQL如何解析查询、选择表和索引以及执行查询的详细信息。

示例：使用`EXPLAIN`查看查询计划：

```sql
EXPLAIN SELECT * FROM students WHERE email = 'example@example.com';
```

执行上述查询后，MySQL会返回查询的执行计划，包括是否使用了索引、使用了哪个索引以及查询的预估成本等信息。根据这些信息，可以进一步优化查询语句或调整索引策略。

#### 结论

在MySQL中，索引是提高查询性能的重要手段。通过在已建立的数据表中创建合适的索引，可以显著提升数据库的查询效率。然而，索引的创建和管理也需要谨慎进行，以避免不必要的性能开销。通过遵循最佳实践并定期评估索引的有效性，可以确保数据库始终保持良好的查询性能。

该分类下的相关小册推荐：

细说MySQL(零基础到高级应用)

MySQL从入门到精通(三)

MySQL从入门到精通(二)

MySQL从入门到精通(一)

MySQL必会核心问题

MySQL从入门到精通(五)

MySQL8.0入门与实践

SQL零基础到熟练应用(增删改查)

MySQL 实战 45 讲