如何发现和处理MySQL中未使用的索引

理解未使用索引的概念

在 MySQL 数据库中，索引是一种数据结构，用于快速定位表中特定行的数据。它就像一本书的目录，通过索引，数据库可以快速找到满足查询条件的数据，从而提高查询性能。然而，并非所有创建的索引都会被实际使用。未使用的索引，简单来说，就是在数据库运行过程中，从没有被任何查询语句利用来优化查询性能的索引。

为什么会出现未使用索引

1. 查询模式与索引不匹配： 假设我们有一个 users 表，包含 id、name、email 等字段，并且我们在 email 字段上创建了索引。如果我们的查询语句总是通过 name 字段来检索用户，例如 SELECT * FROM users WHERE name = 'John';，那么 email 字段上的索引就不会被使用，因为查询没有基于 email 进行过滤。

-- 创建 users 表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    email VARCHAR(255),
    INDEX (email)
);
-- 查询语句
SELECT * FROM users WHERE name = 'John';

2. 索引冗余： 有时可能会创建了功能重叠的索引。比如在一个 orders 表中有 order_date 和 customer_id 字段，先创建了 INDEX idx_date (order_date)，之后又创建了 INDEX idx_date_customer (order_date, customer_id)。当查询只基于 order_date 时，idx_date 和 idx_date_customer 都可以满足，但 MySQL 可能会选择更窄的 idx_date，导致 idx_date_customer 中关于 customer_id 的部分未被使用。

-- 创建 orders 表
CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    order_date DATE,
    customer_id INT,
    INDEX idx_date (order_date),
    INDEX idx_date_customer (order_date, customer_id)
);
-- 查询语句
SELECT * FROM orders WHERE order_date = '2023 - 01 - 01';

3. 统计信息不准确： MySQL 的查询优化器依赖统计信息来决定是否使用索引。如果统计信息过时或不准确，优化器可能做出错误的决策，不使用本应使用的索引。例如，表数据发生了大量的插入、更新或删除操作后，没有及时更新统计信息，就可能出现这种情况。

发现未使用索引的方法

使用 SHOW STATUS 命令

1. 原理： SHOW STATUS 命令可以提供 MySQL 服务器运行时的各种状态信息。其中，Handler_read_* 系列变量与索引使用情况相关。Handler_read_rnd_next 表示从数据表中按照数据行的物理顺序读取下一行的请求数。如果这个值非常高，而 Handler_read_key（表示通过索引读取一行的请求数）相对较低，可能意味着索引未被充分利用。
2. 操作步骤：
   • 首先，记录当前的 Handler_read_* 值：

SHOW STATUS LIKE 'Handler_read%';

- 然后，运行一系列代表性的查询，这些查询应涵盖应用程序中常见的查询类型。
- 再次执行 `SHOW STATUS LIKE 'Handler_read%';`，比较两次的值。如果 `Handler_read_rnd_next` 增长明显，而 `Handler_read_key` 增长缓慢或没有增长，这可能暗示存在未使用的索引。

例如，假设第一次执行 SHOW STATUS LIKE 'Handler_read%'; 得到以下结果：

Variable_name	Value
Handler_read_first	10
Handler_read_key	50
Handler_read_next	100
Handler_read_prev	5
Handler_read_rnd	20
Handler_read_rnd_next	1000

运行一系列查询后，再次执行得到：

Variable_name	Value
Handler_read_first	15
Handler_read_key	55
Handler_read_next	120
Handler_read_prev	8
Handler_read_rnd	25
Handler_read_rnd_next	2000

可以看到 Handler_read_rnd_next 大幅增长，而 Handler_read_key 增长较少，这可能表明索引使用不太理想，存在未使用索引的可能性。

使用 EXPLAIN 命令

1. 原理： EXPLAIN 命令用于分析查询语句的执行计划。它会展示 MySQL 如何执行查询，包括是否使用索引、使用哪个索引等信息。通过查看 EXPLAIN 的输出，我们可以判断索引是否被有效利用。
2. 操作步骤：
   • 在查询语句前加上 EXPLAIN 关键字，例如：

EXPLAIN SELECT * FROM users WHERE name = 'John';

- `EXPLAIN` 的输出包含多个列，其中关键列有：
    - **id**：查询标识符，用于标识查询的执行顺序，如果有多个 `SELECT` 语句嵌套，会有不同的 `id` 值。
    - **select_type**：表示查询的类型，常见的有 `SIMPLE`（简单查询，不包含子查询或联合查询）、`PRIMARY`（最外层的查询）等。
    - **table**：显示这一行数据是关于哪张表的。
    - **partitions**：如果表是分区表，这里会显示查询涉及的分区。
    - **type**：表示连接类型，常见的有 `ALL`（全表扫描）、`index`（索引扫描）、`range`（范围扫描）等。如果 `type` 为 `ALL`，通常意味着没有使用索引，除非是非常小的表，否则这可能是性能问题的一个信号。
    - **possible_keys**：列出可能用于此查询的索引。
    - **key**：实际使用的索引，如果为空，表示没有使用索引。
    - **key_len**：表示使用的索引长度，通过这个值可以了解索引的使用情况是否最优。
    - **ref**：显示哪些列或常量被用于查找索引列上的值。
    - **rows**：估计为了找到所需的行需要读取的行数，这个值越低越好。
    - **filtered**：表示通过条件过滤后剩余记录的百分比估计值。

例如，对于上述 EXPLAIN SELECT * FROM users WHERE name = 'John'; 的查询，如果输出结果中 type 为 ALL，key 为空，而 possible_keys 中有索引 name_index，那么说明 name_index 索引未被使用。

使用慢查询日志

1. 原理： 慢查询日志记录了执行时间超过指定阈值（可以通过 long_query_time 变量设置）的查询。通过分析慢查询日志中的查询语句及其执行情况，可以发现哪些查询可能因为未使用索引而导致性能低下。
2. 操作步骤：
   • 首先，确保慢查询日志功能已开启。可以通过修改 MySQL 配置文件（通常是 my.cnf 或 my.ini），添加或修改以下配置：

slow_query_log = 1
long_query_time = 2 # 设置慢查询阈值为 2 秒
slow_query_log_file = /var/log/mysql/slow - query.log # 日志文件路径

- 重启 MySQL 服务使配置生效。
- 随着系统运行，慢查询日志会记录满足条件的查询。定期查看慢查询日志文件，使用 `EXPLAIN` 对日志中的查询进行分析，判断是否存在未使用索引的情况。

例如，在慢查询日志中发现一条查询语句：

SELECT * FROM products WHERE category = 'electronics' AND price > 100;

对其执行 EXPLAIN：

EXPLAIN SELECT * FROM products WHERE category = 'electronics' AND price > 100;

如果 EXPLAIN 输出显示没有使用索引，而在 products 表的 category 和 price 字段上创建有索引，那么就可以确定这些索引在该查询中未被使用。

使用 Performance Schema

1. 原理： Performance Schema 是 MySQL 提供的一个用于收集服务器性能信息的架构。它可以详细记录各种数据库操作的性能数据，包括索引的使用情况。通过查询 Performance Schema 中的相关表，可以获取未使用索引的信息。
2. 操作步骤：
   • 确保 Performance Schema 已启用，在 MySQL 配置文件中添加或确认以下配置：

performance_schema = ON

- 重启 MySQL 服务。
- 查询 `performance_schema.table_io_waits_summary_by_index_usage` 表，该表记录了每个索引的等待时间等信息。以下是一个查询示例，用于查找未使用的索引：

SELECT
    object_schema,
    object_name,
    index_name,
    count_star
FROM
    performance_schema.table_io_waits_summary_by_index_usage
WHERE
    index_name IS NOT NULL
    AND count_star = 0;

上述查询会返回在统计期间内没有被使用过的索引信息，包括所属的数据库（object_schema）、表名（object_name）和索引名（index_name）。

处理未使用索引的策略

评估索引的必要性

1. 业务需求分析： 在决定是否删除未使用的索引之前，需要深入分析业务需求。有时候，虽然当前的查询没有使用某个索引，但未来的业务发展可能会用到。例如，一个电商系统目前主要通过商品名称进行查询，但计划在未来添加按商品分类和价格区间筛选的功能，如果已经在商品分类和价格字段上创建了索引，即使当前未使用，也不应轻易删除。
2. 查询模式变化预测： 考虑应用程序未来可能的查询模式变化。如果预计会有新的查询基于某个未使用索引的字段组合进行，那么保留该索引可能是明智的。比如，一个用户管理系统当前只通过用户名查询用户，但计划增加按注册时间范围查询用户的功能，那么在注册时间字段上的索引虽然当前未使用，但可能在未来发挥作用。

删除未使用索引

1. 直接删除： 如果确定某个索引确实不会再被使用，可以使用 DROP INDEX 语句来删除它。例如，要删除 users 表上的 email_index 索引，可以执行以下语句：

DROP INDEX email_index ON users;

2. 注意事项：
   • 在生产环境中删除索引需要谨慎操作，因为删除索引可能会对现有查询产生影响，即使目前未使用，也可能在某些边缘情况下被用到。建议在测试环境中先进行删除操作，并对应用程序的所有功能进行全面测试，确保没有性能问题或功能异常。
   • 删除索引后，数据库的统计信息会发生变化，可能会影响查询优化器的决策。如果有必要，需要手动更新统计信息，例如使用 ANALYZE TABLE 语句：

ANALYZE TABLE users;

- 大量删除索引可能会导致表空间碎片化，在删除索引后，可以考虑对表进行优化操作，如 `OPTIMIZE TABLE`，以整理表空间，提高性能：

OPTIMIZE TABLE users;

优化索引结构

1. 合并索引： 对于冗余或部分重叠的索引，可以考虑合并它们。例如，有 INDEX idx1 (col1) 和 INDEX idx2 (col1, col2)，如果 idx1 未被使用，可以删除 idx1，并确保查询在使用 idx2 时能满足需求。这样可以减少索引数量，降低索引维护成本。
2. 调整索引字段顺序： 在复合索引中，字段顺序非常重要。如果发现某个复合索引未被使用，可以根据查询模式调整字段顺序。例如，对于查询 SELECT * FROM orders WHERE customer_id = 123 AND order_date > '2023 - 01 - 01';，如果在 (order_date, customer_id) 上创建的索引未被使用，而在 (customer_id, order_date) 上创建索引后查询性能提升，就可以调整索引结构。

-- 删除原索引
DROP INDEX idx_order_date_customer ON orders;
-- 创建新索引
CREATE INDEX idx_customer_id_order_date ON orders (customer_id, order_date);

更新统计信息

1. 原因： 如前文所述，不准确的统计信息会导致查询优化器做出错误决策，使索引未被使用。因此，及时更新统计信息可以帮助优化器更好地选择索引。
2. 方法：
   • ANALYZE TABLE： ANALYZE TABLE 语句用于更新表的统计信息，包括索引的统计信息。例如，对 products 表执行 ANALYZE TABLE：

ANALYZE TABLE products;

- **OPTIMIZE TABLE**：

OPTIMIZE TABLE 除了可以整理表空间碎片外，也会更新统计信息。同样对 products 表执行：

OPTIMIZE TABLE products;

这两种方法都能更新统计信息，但 OPTIMIZE TABLE 操作相对更耗时，因为它还涉及到表空间的整理操作。在数据量较大且表空间碎片化不严重的情况下，优先使用 ANALYZE TABLE 来更新统计信息。

重新设计查询

1. 调整查询逻辑： 有时候，查询本身的逻辑可以进行调整，以更好地利用索引。例如，对于查询 SELECT * FROM users WHERE NOT email ='specific_email@example.com';，这种否定条件的查询通常难以使用索引。可以将其改写为 SELECT * FROM users WHERE email <>'specific_email@example.com';，虽然逻辑上相同，但在某些情况下，后者可能更有利于索引的使用。
2. 使用覆盖索引： 覆盖索引是指一个索引包含了满足查询所需的所有列。例如，对于查询 SELECT name, email FROM users WHERE age > 30;，如果在 (age, name, email) 上创建索引，这个索引就可以覆盖查询，避免回表操作，提高查询性能。

-- 创建覆盖索引
CREATE INDEX idx_age_name_email ON users (age, name, email);

通过以上方法，可以有效地发现和处理 MySQL 中未使用的索引，从而提高数据库的性能和资源利用率。在实际操作中，需要综合考虑业务需求、查询模式以及数据库的整体性能，谨慎地做出决策。