如何检查和优化 MySQL 索引：使用 SHOW INDEX、EXPLAIN 与性能调优的完整指南

1. 介绍

MySQL 是一种在众多 Web 应用和数据管理系统中广泛使用的关系型数据库。为了提升数据检索速度，存在一种称为“索引”的机制。然而，如果管理不当，索引实际上可能会降低性能。

为什么检查索引很重要？

数据库中的索引类似于书籍的索引章节。合理设计的索引可以提升查询的执行速度。但以下问题可能会出现：

• 索引未正确创建 → 导致搜索变慢
• 存在不必要的索引 → 使更新和插入变慢
• 不清楚哪些索引在被使用 → 难以判断是否需要删除未使用的索引

本文你将学到的内容

• MySQL 索引的基本原理
• 如何检查当前索引（使用 SQL 命令）
• 如何管理和优化索引
• 分析索引使用情况的技巧

现在，让我们系统地学习 MySQL 索引，并利用这些知识提升数据库性能。

2. 什么是 MySQL 索引？

索引是提升数据库性能的关键特性。本节将解释索引的基本概念、类型、优势与劣势。

索引的基本概念

数据库索引是一种机制，能够在特定列上快速搜索值。例如，在包含大量数据的表中查找特定记录时，如果没有索引，数据库必须扫描所有记录（全表扫描）。使用索引后，数据检索变得高效，处理速度显著提升。

MySQL 索引的类型

MySQL 支持多种索引类型，每种类型适用于特定的使用场景。

1. PRIMARY KEY（主键索引）

• 每个表只能设置一次
• 保证表中数据唯一
• 充当聚簇索引

2. UNIQUE 索引

• 确保指定列的值不重复
• 允许 NULL 值（多个 NULL 是被允许的）

3. INDEX（普通索引）

• 用于加速搜索
• 允许重复数据

4. FULLTEXT 索引（用于全文搜索）

• 优化文本搜索
• 与 MATCH ... AGAINST 语法配合使用

5. SPATIAL 索引（用于地理空间数据）

• 为空间（GIS）数据设计

索引的优势与劣势

优势

• 提升查询搜索速度
• 加强 JOIN 操作和 WHERE 子句的性能
• 使特定数据的检索更高效

劣势

• 更多的索引会减慢 INSERT、UPDATE、DELETE 操作
• 占用磁盘空间
• 设计不当的索引会导致性能下降

3. 如何检查 MySQL 索引

要正确管理 MySQL 索引，首先需要检查表上当前定义了哪些索引。本节将介绍如何使用 SHOW INDEX 命令、INFORMATION_SCHEMA.STATISTICS 以及 mysqlshow 命令 来检查索引。

SHOW INDEX 命令（基础方法）

在 MySQL 中，可以使用 SHOW INDEX 命令获取特定表上定义的索引列表。该命令可以让你查看 索引名称、索引覆盖的列以及是否存在唯一约束 等细节。

基本语法

SHOW INDEX FROM table_name;

示例

例如，要检查 users 表上定义的索引，可执行以下 SQL：

SHOW INDEX FROM users;

示例输出

使用 INFORMATION_SCHEMA.STATISTICS（获取详细信息）

通过 MySQL 的系统表 INFORMATION_SCHEMA.STATISTICS，可以在更灵活的情况下获取与 SHOW INDEX 相同的信息。

检查特定表的索引

SELECT TABLE_NAME, INDEX_NAME, COLUMN_NAME, NON_UNIQUE
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'users';

获取整个数据库的索引

SELECT TABLE_NAME, COLUMN_NAME, INDEX_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'your_database_name';

mysqlshow 命令（从 CLI 检查）

您也可以使用 MySQL 命令行工具检索索引信息。这在 通过 SSH 在 MySQL 服务器上工作 时尤其有用。

如何运行命令

mysqlshow -u username -p password database_name table_name

示例

mysqlshow -u root -p my_database users

如果不存在索引该怎么办

如果在运行 SHOW INDEX 或查询 INFORMATION_SCHEMA.STATISTICS 后未显示任何索引，则该表可能没有适当的索引。在这种情况下，您可以根据需要创建索引以提升查询性能。

创建新索引

CREATE INDEX idx_column ON users (email);

设置主键（PRIMARY KEY）

ALTER TABLE users ADD PRIMARY KEY (id);

删除不必要的索引

ALTER TABLE users DROP INDEX idx_column;

4. 如何检查索引使用情况

检查索引是否正常工作是 MySQL 性能优化的关键步骤。在本节中，我们将说明如何通过利用 EXPLAIN 命令和 Performance Schema 来确定查询使用了哪些索引。

使用 EXPLAIN 进行查询分析

EXPLAIN 命令用于可视化给定 SQL 查询的执行方式。它帮助您分析 使用的索引、访问方式和执行计划，这对于验证索引是否按预期工作非常有用。

基本语法

EXPLAIN SELECT * FROM table_name WHERE condition;

示例

例如，要使用 email 列作为条件搜索 users 表：

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

示例输出

关键点

• 如果 type = ALL，查询正在进行 全表扫描，因此可能需要索引。
• 如果在 key 中出现索引名称，则该索引正在被使用。
• 如果 rows 的值过大，可能需要进行查询优化。

使用 Performance Schema

使用 MySQL 的 performance_schema，您可以详细分析查询执行期间使用了哪些索引以及使用的频率。

获取查询执行统计信息

SELECT * FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT LIKE '%SELECT%';

检查特定表的索引使用情况

SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, COUNT_STAR, SUM_TIMER_WAIT
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'your_database_name' AND OBJECT_NAME = 'users';

如果索引未被使用该怎么办

1. 检查查询

如果索引未被使用，可能是查询结构的问题。例如，以下模式可能会阻止索引的使用。

❌ 错误示例（函数会禁用索引使用）

SELECT * FROM users WHERE LOWER(email) = 'test@example.com';

→ 由于 LOWER(email)，email 上的索引可能会被忽略。

✅ 正确示例（避免使用函数）

SELECT * FROM users WHERE email = 'test@example.com';

2. 重新创建索引

如果现有索引未正常工作，删除并重新创建它有时可以提升性能。

ALTER TABLE users DROP INDEX idx_email;
CREATE INDEX idx_email ON users(email);

3. 更新统计信息

如果表统计信息过时，MySQL 可能无法最佳地使用索引。您可以使用以下命令刷新统计信息：

ANALYZE TABLE users;

5. 索引管理

MySQL 索引对于提升数据检索性能至关重要。然而，如果管理不当，它们可能会降低整体数据库性能。在本节中，我们将详细说明如何创建、删除以及识别不必要的索引。

创建索引

通过创建适当的索引，您可以显著加快数据搜索速度。在 MySQL 中，您可以使用 CREATE INDEX 或 ALTER TABLE 来添加索引。

基本语法

CREATE INDEX index_name ON table_name(column_name);

示例

向 users 表的 email 列添加索引：

CREATE INDEX idx_email ON users(email);

多列索引（复合索引）

CREATE INDEX idx_name_email ON users(last_name, first_name, email);

唯一索引

CREATE UNIQUE INDEX idx_unique_email ON users(email);

设置主键（PRIMARY KEY）

ALTER TABLE users ADD PRIMARY KEY (id);

删除索引

删除不必要的索引有助于降低数据库开销。

基本语法

ALTER TABLE table_name DROP INDEX index_name;

示例

例如，删除名为 idx_email 的索引：

ALTER TABLE users DROP INDEX idx_email;

识别并删除不必要的索引

检查未使用的索引

SELECT * FROM sys.schema_unused_indexes;

检查表状态（索引影响）

SHOW TABLE STATUS LIKE 'users';

删除不必要的索引

ALTER TABLE users DROP INDEX idx_unused;

删除后，建议使用 ANALYZE TABLE 更新统计信息。

ANALYZE TABLE users;

6. 索引优化（性能提升）

适当的索引管理可以显著提升 MySQL 查询性能。然而，仅仅创建索引并不足够——需要正确的设计、管理和监控才能保持最佳性能。

正确的索引设计

精心设计的索引可以显著提升 MySQL 的搜索速度。

适用索引的情况

不适用索引的情况

使用慢查询日志

慢查询日志可以帮助您识别运行时间较长的查询，并分析哪些索引未被使用。

启用慢查询日志

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2; -- Log queries taking longer than 2 seconds

检查慢查询日志

SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;

分析慢查询

EXPLAIN SELECT * FROM users WHERE last_login > '2024-01-01';

应用索引的示例

CREATE INDEX idx_last_login ON users(last_login);

更新统计信息（ANALYZE 与 OPTIMIZE）

ANALYZE TABLE（更新统计信息）

ANALYZE TABLE users;

OPTIMIZE TABLE（碎片整理）

OPTIMIZE TABLE users;

7. 索引常见问题（FAQ）

MySQL 索引是提升数据库性能的重要机制。然而，如果管理不当，可能会适得其反。在本节中，我们汇总了与 MySQL 索引相关的常见问题（FAQ）及其答案。

添加更多索引会提升搜索速度吗？

答：不一定。

索引旨在提升查询性能，但添加过多索引实际上会降低数据库性能。

• 增加写入开销（INSERT、UPDATE、DELETE）
• 索引是否被使用取决于查询
• 不必要的索引会占用内存

哪些列应该建立索引？

答：对以下类型的列建立索引是有效的：

索引会自动创建吗？

答：某些索引会自动创建，但其他索引需要手动添加。

自动创建的索引

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY, -- PRIMARY KEY index
  email VARCHAR(255) UNIQUE -- Index automatically created by UNIQUE constraint
);

手动创建的索引

CREATE INDEX idx_email ON users(email);

应该如何在 B-Tree、Hash 和 FULLTEXT 索引之间进行选择？

如何检查索引的大小？

SHOW TABLE STATUS LIKE 'users';

如何检查索引是否被使用？

EXPLAIN SELECT * FROM users WHERE email = 'example@example.com';

何时应该删除索引？

删除不必要的索引

ALTER TABLE users DROP INDEX idx_unused;

8. 总结

在本文中，我们全面介绍了 MySQL 索引——从基础概念到验证方法、管理、优化以及常见问题。下面回顾各章节的要点，并总结 优化 MySQL 索引管理的最佳实践。

关键要点

什么是 MySQL 索引？

• 索引是一种加速数据检索的机制。
• 索引类型包括 B-Tree、Hash 和 FULLTEXT 索引。
• 在 WHERE 子句、JOIN 和 ORDER BY 中使用的列上应用索引最为有效。

如何检查 MySQL 索引

• 使用 SHOW INDEX 命令查看索引列表。
• 使用 INFORMATION_SCHEMA.STATISTICS 获取详细信息。

如何检查索引使用情况

• 使用 EXPLAIN 查看查询使用了哪些索引。
• 使用 Performance Schema 分析索引的使用频率。

索引管理

• 使用 CREATE INDEX 为适当的列创建索引。
• 使用 ALTER TABLE DROP INDEX 删除不必要的索引。
• 通过 SHOW TABLE STATUS 检查索引大小，并根据需要进行优化。

索引优化（性能提升）

• 在经常使用的 WHERE、JOIN 和 ORDER BY 列上创建索引。
• 使用慢查询日志（Slow Query Log）识别并优化慢查询。
• 使用 ANALYZE TABLE 和 OPTIMIZE TABLE 更新统计信息。

MySQL 索引管理最佳实践

1. 在创建索引之前先识别查询瓶颈。
2. 选择合适的索引。

• 正确使用单列索引和复合索引。
• 在需要唯一约束的场景下使用 UNIQUE INDEX。

3. 定期删除不必要的索引。

• 使用 SHOW INDEX 和 schema_unused_indexes 识别未使用的索引。

4. 定期更新统计信息。

• 使用 ANALYZE TABLE 更新统计信息。
• 运行 OPTIMIZE TABLE 解决因删除和更新导致的碎片问题。

后续步骤

✅ 实践清单
✅ 是否已使用 SHOW INDEX 检查当前索引？
✅ 是否已启用慢查询日志并识别慢查询？
✅ 是否已使用 EXPLAIN 分析查询执行计划？
✅ 是否已删除不必要的索引并创建合适的索引？
✅ 是否已使用 ANALYZE TABLE 更新统计信息？

最终总结

• 妥善管理 MySQL 索引可显著提升检索性能。
• 利用慢查询日志和 EXPLAIN 分析索引效果并进行相应优化。
• 通过定期更新统计信息和优化表来保持数据库性能。

本指南至此结束，完整覆盖了 MySQL 索引管理的全部内容。

运用这些知识构建更快、更高效的数据库系统。 💡🚀