Mysql索引失效导致查询慢的修复方案

一、索引为什么会失效

索引就像是图书馆的目录卡片，能帮我们快速找到想要的书。但有时候这个目录卡片会突然"失灵"，导致查询变得特别慢。这种情况我们称之为"索引失效"。

常见的索引失效场景包括：

使用了!=或<>操作符
对索引列使用了函数
使用了OR连接条件
使用了LIKE以通配符开头
数据类型不匹配

举个例子，假设我们有个用户表：

-- 技术栈：MySQL 8.0
-- 创建用户表
CREATE TABLE `users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `email` varchar(100) NOT NULL,
  `age` int DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_username` (`username`),
  KEY `idx_age` (`age`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB;

-- 插入测试数据
INSERT INTO `users` (`username`, `email`, `age`, `created_at`) VALUES
('user1', 'user1@example.com', 20, '2023-01-01 00:00:00'),
('user2', 'user2@example.com', 25, '2023-01-02 00:00:00'),
('user3', 'user3@example.com', 30, '2023-01-03 00:00:00'),
('user4', 'user4@example.com', 35, '2023-01-04 00:00:00'),
('user5', 'user5@example.com', 40, '2023-01-05 00:00:00');

现在我们来执行一些会导致索引失效的查询：

-- 使用了函数导致索引失效
SELECT * FROM users WHERE YEAR(created_at) = 2023;

-- 使用了LIKE以通配符开头
SELECT * FROM users WHERE username LIKE '%user%';

-- 使用了OR条件
SELECT * FROM users WHERE username = 'user1' OR age = 20;

二、如何诊断索引失效问题

当发现查询变慢时，第一步是要确认是不是索引失效导致的。MySQL提供了EXPLAIN命令来帮助我们分析查询执行计划。

-- 使用EXPLAIN分析查询
EXPLAIN SELECT * FROM users WHERE username LIKE '%user%';

-- 输出结果示例：
-- id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra
-- 1  | SIMPLE      | users | NULL       | ALL  | idx_username  | NULL| NULL    | NULL| 5    | 100.00   | Using where

从EXPLAIN的输出中，有几个关键字段需要注意：

type：显示ALL表示全表扫描，说明索引没起作用
key：显示NULL表示没有使用索引
rows：显示扫描的行数，数值越大性能越差

另一个有用的工具是开启慢查询日志：

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- 超过1秒的查询记录为慢查询
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';

三、修复索引失效的实用方案

1. 避免在索引列上使用函数

-- 错误的写法：使用了YEAR函数导致索引失效
SELECT * FROM users WHERE YEAR(created_at) = 2023;

-- 正确的写法：使用范围查询
SELECT * FROM users 
WHERE created_at >= '2023-01-01 00:00:00' 
AND created_at < '2024-01-01 00:00:00';

2. 优化LIKE查询

-- 错误的写法：通配符开头导致索引失效
SELECT * FROM users WHERE username LIKE '%user%';

-- 正确的写法1：如果可能，使用前缀匹配
SELECT * FROM users WHERE username LIKE 'user%';

-- 正确的写法2：使用全文索引
-- 首先添加全文索引
ALTER TABLE users ADD FULLTEXT INDEX ft_username (username);

-- 然后使用MATCH AGAINST语法
SELECT * FROM users WHERE MATCH(username) AGAINST('user');

3. 处理OR条件

-- 错误的写法：OR条件导致索引失效
SELECT * FROM users WHERE username = 'user1' OR age = 20;

-- 正确的写法1：使用UNION ALL
SELECT * FROM users WHERE username = 'user1'
UNION ALL
SELECT * FROM users WHERE age = 20 AND username != 'user1';

-- 正确的写法2：使用CASE WHEN优化
SELECT * FROM users 
WHERE (CASE WHEN username = 'user1' THEN 1 ELSE 0 END + 
       CASE WHEN age = 20 THEN 1 ELSE 0 END) > 0;

4. 数据类型匹配问题

-- 错误的写法：age是整数但用字符串比较
SELECT * FROM users WHERE age = '20';

-- 正确的写法：保持数据类型一致
SELECT * FROM users WHERE age = 20;

5. 复合索引的最左前缀原则

-- 添加复合索引
ALTER TABLE users ADD INDEX idx_username_age (username, age);

-- 错误的写法：没有使用索引的最左列
SELECT * FROM users WHERE age = 20;

-- 正确的写法：使用索引的最左列
SELECT * FROM users WHERE username = 'user1' AND age = 20;

四、高级优化技巧

1. 使用覆盖索引

-- 普通查询
SELECT username, age FROM users WHERE username = 'user1';

-- 添加覆盖索引
ALTER TABLE users ADD INDEX idx_cover_username_age (username, age);

-- 使用EXPLAIN验证
EXPLAIN SELECT username, age FROM users WHERE username = 'user1';
-- 在Extra列会显示"Using index"表示使用了覆盖索引

2. 使用索引提示

有时候MySQL优化器可能选择错误的索引，我们可以强制指定索引：

-- 强制使用特定索引
SELECT * FROM users FORCE INDEX (idx_username) WHERE username = 'user1';

3. 使用索引合并优化

-- 启用索引合并优化
SET optimizer_switch='index_merge=on';

-- 查询示例
SELECT * FROM users WHERE username = 'user1' OR id = 1;

4. 定期分析和优化表

-- 分析表
ANALYZE TABLE users;

-- 优化表
OPTIMIZE TABLE users;

五、实际案例分析

让我们看一个真实的电商场景案例。假设有一个订单表：

-- 创建订单表
CREATE TABLE `orders` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `order_no` varchar(32) NOT NULL,
  `user_id` int NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `status` tinyint NOT NULL COMMENT '0-未支付,1-已支付,2-已取消',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_order_no` (`order_no`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_status_created_at` (`status`, `created_at`)
) ENGINE=InnoDB;

-- 插入测试数据(假设有100万条订单数据)

现在有一个慢查询：查找某个用户最近3个月已支付的订单

-- 原始慢查询
SELECT * FROM orders 
WHERE user_id = 1001 
AND status = 1 
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 MONTH);

优化方案：

确保使用了复合索引(user_id, status, created_at)
避免在WHERE条件中对索引列使用函数
只查询需要的列

优化后的查询：

-- 添加复合索引
ALTER TABLE orders ADD INDEX idx_user_status_created (user_id, status, created_at);

-- 优化后的查询
SELECT id, order_no, amount, created_at 
FROM orders 
WHERE user_id = 1001 
AND status = 1 
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 MONTH);

六、预防索引失效的最佳实践

设计阶段：
- 根据查询模式设计合适的索引
- 避免过度索引，每个额外的索引都会降低写入性能
开发阶段：
- 编写SQL时考虑索引使用情况
- 使用EXPLAIN验证重要查询的执行计划
测试阶段：
- 使用真实数据量进行性能测试
- 监控慢查询日志
运维阶段：
- 定期检查索引使用情况
- 删除未使用的冗余索引
- 定期进行表优化

-- 检查未使用的索引
SELECT object_schema, object_name, index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
AND count_star = 0
ORDER BY object_schema, object_name;

七、总结与建议

索引是提高数据库查询性能的利器，但使用不当反而会成为性能瓶颈。通过本文的分析，我们了解到索引失效的常见原因及对应的解决方案。在实际工作中，建议：

养成使用EXPLAIN分析查询的习惯
遵循索引设计的最佳实践
定期审查和优化数据库索引
监控慢查询并及时处理
在业务增长过程中持续优化数据库性能

记住，没有放之四海而皆准的索引方案，最优的索引策略总是取决于你的具体业务场景和数据特点。通过持续的学习和实践，你一定能掌握MySQL索引优化的精髓。

敲码拾光专注于编程技术，涵盖编程语言、代码实战案例、软件开发技巧、IT前沿技术、编程开发工具，是您提升技术能力的优质网络平台。