一、常见原因概览

即使使用了索引，查询仍可能很慢，主要原因包括：

1. 回表次数过多
2. 索引选择错误
3. 索引区分度不够
4. 数据量过大或深度分页
5. 未使用覆盖索引
6. 索引列有函数或计算
7. 隐式类型转换
8. 统计信息不准确
9. 锁等待或死锁
10. 磁盘IO瓶颈

二、详细原因分析

原因1：回表次数过多

问题描述

-- 表：1000万行，索引：INDEX(status)
-- status分布：active (900万), inactive (100万)

SELECT * FROM users WHERE status='active';

执行过程：

1. 扫描索引 idx_status，找到900万条记录的主键
2. 根据主键回表900万次，获取完整行数据
3. 返回结果

耗时分析：
- 索引扫描：0.1秒
- 回表操作：8秒（900万次随机IO）
- 总耗时：8.1秒

原因分析

回表成本 = 回表次数 × 单次回表成本

单次回表成本：
- 数据在内存：0.001ms
- 数据在磁盘：10ms（随机IO）

900万次回表：
- 全在内存：9秒
- 部分在磁盘：更长时间

解决方案

方案1：使用覆盖索引

-- 原查询
SELECT id, name, email FROM users WHERE status='active';

-- 创建覆盖索引
CREATE INDEX idx_status_id_name_email ON users(status, id, name, email);

-- 执行计划
EXPLAIN SELECT id, name, email FROM users WHERE status='active';
-- Extra: Using index  ← 无需回表
-- 耗时：0.5秒（减少94%）

方案2：延迟关联（分页场景）

-- 不好：深度分页需要回表10万次
SELECT * FROM users 
WHERE status='active' 
ORDER BY create_time 
LIMIT 100000, 20;

-- 好：先走覆盖索引，再延迟关联
SELECT u.* FROM users u
INNER JOIN (
    SELECT id FROM users
    WHERE status='active'
    ORDER BY create_time
    LIMIT 100000, 20
) t ON u.id = t.id;
-- 耗时减少80%

方案3：调整查询条件

-- 原查询：返回90%的数据
SELECT * FROM users WHERE status='active';

-- 优化：增加过滤条件
SELECT * FROM users 
WHERE status='active' AND create_time >= '2025-01-01';
-- 减少回表次数

原因2：索引选择错误

问题描述

-- 有两个索引
INDEX idx_status(status)
INDEX idx_create_time(create_time)

-- 查询
SELECT * FROM orders 
WHERE status='shipped' AND create_time >= '2025-11-01';

-- 优化器错误选择 idx_status
-- 实际应该选择 idx_create_time（过滤效果更好）

验证方法

-- 查看实际使用的索引
EXPLAIN SELECT * FROM orders 
WHERE status='shipped' AND create_time >= '2025-11-01';

-- 对比不同索引的效果
EXPLAIN SELECT * FROM orders FORCE INDEX(idx_status)
WHERE status='shipped' AND create_time >= '2025-11-01';
-- rows: 500000

EXPLAIN SELECT * FROM orders FORCE INDEX(idx_create_time)
WHERE status='shipped' AND create_time >= '2025-11-01';
-- rows: 10000  ← 更优

解决方案

方案1：强制使用正确索引

SELECT * FROM orders FORCE INDEX(idx_create_time)
WHERE status='shipped' AND create_time >= '2025-11-01';

方案2：创建联合索引

-- 根据查询模式创建最优索引
CREATE INDEX idx_status_time ON orders(status, create_time);

-- 或根据过滤效果
CREATE INDEX idx_time_status ON orders(create_time, status);

方案3：更新统计信息

-- 统计信息过时可能导致错误选择
ANALYZE TABLE orders;

-- 再次检查执行计划
EXPLAIN SELECT * FROM orders 
WHERE status='shipped' AND create_time >= '2025-11-01';

原因3：索引区分度不够

问题描述

-- 索引：INDEX(gender)
-- 数据分布：M (500万), F (500万)

SELECT * FROM users WHERE gender='M';

-- 虽然走了索引，但需要回表500万次
-- 效果不佳

判断标准

-- 计算索引选择性
SELECT 
    COUNT(DISTINCT gender) / COUNT(*) AS selectivity,
    COUNT(*) AS total_rows,
    COUNT(*) / COUNT(DISTINCT gender) AS avg_rows_per_value
FROM users;

-- 结果：
-- selectivity: 0.0000002（极低）
-- total_rows: 10000000
-- avg_rows_per_value: 5000000（平均每个值500万行）

解决方案

方案1：创建联合索引

-- 单独的 gender 索引效果差
-- 与其他列组合提升区分度
CREATE INDEX idx_gender_age_city ON users(gender, age, city);

-- 查询
SELECT * FROM users 
WHERE gender='M' AND age BETWEEN 20 AND 30 AND city='Beijing';
-- 过滤效果大幅提升

方案2：调整查询策略

-- 如果确实需要查询大量数据（如50%以上）
-- 考虑全表扫描 + 缓存

-- 全表扫描可能更快（顺序IO）
SELECT * FROM users WHERE gender='M';
-- 配合应用层缓存

原因4：数据量过大或深度分页

深度分页问题

-- 典型场景：翻到第5000页
SELECT * FROM posts 
ORDER BY create_time DESC 
LIMIT 100000, 20;

执行过程：

1. 从索引扫描100020行
2. 回表100020次
3. 返回最后20行
4. 前面100000行的回表全部浪费

耗时：
- 扫描100020行：0.5秒
- 回表100020次：3秒
- 总计：3.5秒

解决方案

方案1：延迟关联

SELECT p.* FROM posts p
INNER JOIN (
    SELECT id FROM posts
    ORDER BY create_time DESC
    LIMIT 100000, 20
) t ON p.id = t.id;

-- 子查询走覆盖索引，只回表20次
-- 耗时：0.6秒

方案2：游标分页

-- 不使用OFFSET，使用WHERE条件
-- 第一页
SELECT * FROM posts 
ORDER BY create_time DESC 
LIMIT 20;

-- 第二页（记录上一页最后一条的create_time）
SELECT * FROM posts 
WHERE create_time < '上一页最后一条的时间'
ORDER BY create_time DESC 
LIMIT 20;

-- 优势：
-- 1. 不需要扫描前N页
-- 2. 直接定位到目标位置
-- 3. 性能稳定，不随页数增加而下降

方案3：搜索引擎

-- 对于超深分页（10000页+）
-- 使用 Elasticsearch 等搜索引擎
-- MySQL 只存储近期数据

原因5：索引列有函数或计算

问题示例

-- 索引：INDEX(create_time)

-- ❌ 索引失效
SELECT * FROM orders 
WHERE DATE(create_time) = '2025-11-02';

-- ❌ 索引失效
SELECT * FROM orders 
WHERE YEAR(create_time) = 2025;

-- ❌ 索引失效
SELECT * FROM products 
WHERE price * discount > 100;

原因：函数或计算导致索引无法使用B+树的有序性。

解决方案

方案1：改写查询条件

-- 改写为范围查询
SELECT * FROM orders 
WHERE create_time >= '2025-11-02 00:00:00' 
  AND create_time < '2025-11-03 00:00:00';

-- 改写年份查询
SELECT * FROM orders 
WHERE create_time >= '2025-01-01' 
  AND create_time < '2026-01-01';

-- 改写计算条件
-- 假设 final_price = price * discount
CREATE INDEX idx_final_price ON products((price * discount));
-- MySQL 8.0+ 支持函数索引

方案2：添加冗余字段

-- 添加日期字段
ALTER TABLE orders ADD COLUMN create_date DATE;
UPDATE orders SET create_date = DATE(create_time);
CREATE INDEX idx_create_date ON orders(create_date);

-- 查询
SELECT * FROM orders WHERE create_date = '2025-11-02';

原因6：隐式类型转换

问题示例

-- 字段：phone VARCHAR(20), INDEX(phone)

-- ❌ 索引失效（隐式转换）
SELECT * FROM users WHERE phone = 13800138000;
-- phone 是字符串，传入数字，MySQL转换为：
-- WHERE CAST(phone AS UNSIGNED) = 13800138000

-- ✅ 正确写法
SELECT * FROM users WHERE phone = '13800138000';

类型转换规则

-- 字符串字段 vs 数字条件 → 索引失效
WHERE varchar_col = 123  -- ❌

-- 数字字段 vs 字符串条件 → 索引有效
WHERE int_col = '123'  -- ✅（MySQL自动转换条件）

-- 字符集不同 → 可能失效
WHERE utf8_col = utf8mb4_value

解决方案

-- 1. 确保类型匹配
SELECT * FROM users WHERE phone = '13800138000';

-- 2. 查看是否有隐式转换
EXPLAIN FORMAT=TREE SELECT * FROM users WHERE phone = 13800138000;
-- 查看是否有 CAST 或类型转换

-- 3. 修改字段类型
-- 如果phone总是数字，改为 BIGINT
ALTER TABLE users MODIFY phone BIGINT;

原因7：统计信息不准确

问题描述

-- 实际情况：status='active' 只有1000行
-- 统计信息：优化器认为有100万行（数据已删除但未更新统计）

EXPLAIN SELECT * FROM orders WHERE status='active';
-- rows: 1000000  ← 错误估计
-- 优化器选择全表扫描（认为成本更低）

-- 实际：走索引只需扫描1000行，但优化器不知道

解决方案

-- 1. 更新统计信息
ANALYZE TABLE orders;

-- 2. 验证统计信息
SHOW INDEX FROM orders;
-- 查看 Cardinality 列

-- 3. 强制使用索引（临时）
SELECT * FROM orders FORCE INDEX(idx_status) 
WHERE status='active';

-- 4. 配置自动更新（MySQL 8.0+）
SET GLOBAL innodb_stats_auto_recalc = ON;

原因8：锁等待

问题描述

-- 查询本身很快，但因为锁等待而慢
SELECT * FROM orders WHERE id=12345;

-- SHOW PROCESSLIST 显示：
-- State: Waiting for table metadata lock
-- 或：Waiting for row lock

排查方法

-- 1. 查看锁等待
SELECT * FROM sys.innodb_lock_waits;

-- 2. 查看当前事务
SELECT * FROM information_schema.innodb_trx;

-- 3. 查看锁信息
SHOW ENGINE INNODB STATUS\G

-- 4. 查看阻塞会话
SELECT 
    waiting_pid AS waiting_thread,
    waiting_query,
    blocking_pid AS blocking_thread,
    blocking_query
FROM sys.innodb_lock_waits;

解决方案

-- 1. 优化事务（减少持锁时间）
BEGIN;
SELECT ... FOR UPDATE;
-- 业务逻辑（尽量快）
UPDATE ...;
COMMIT;

-- 2. 调整隔离级别（如果业务允许）
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 3. 使用 NOWAIT 或 SKIP LOCKED
SELECT * FROM orders WHERE status='pending'
FOR UPDATE SKIP LOCKED
LIMIT 10;

-- 4. 杀死阻塞会话（谨慎）
KILL <blocking_thread_id>;

原因9：磁盘IO瓶颈

问题描述

-- Buffer Pool 太小，大量数据需要从磁盘读取
-- 机械硬盘随机IO慢（5-10ms/次）

排查方法

-- 1. 查看Buffer Pool命中率
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';

-- Innodb_buffer_pool_read_requests: 10000000（总请求）
-- Innodb_buffer_pool_reads: 1000000（磁盘读）
-- 命中率：90%（理想应 > 99%）

-- 2. 查看磁盘IO
-- Linux
iostat -x 1

-- 3. 查看慢查询的IO等待
SHOW PROFILE FOR QUERY 1;
-- 关注 Sending data 阶段的耗时

解决方案

-- 1. 增加 Buffer Pool
SET GLOBAL innodb_buffer_pool_size = 8G;

-- 2. 优化查询（减少数据量）
-- 使用覆盖索引、限制返回行数等

-- 3. 升级硬件
-- 机械硬盘 → SSD（随机IO提升100倍）

-- 4. 分离冷热数据
-- 热数据（近期）：高性能存储
-- 冷数据（历史）：归档存储

三、综合排查流程

Step 1：确认使用索引

EXPLAIN SELECT ...;

-- 检查关键字段：
-- type: ALL（全表扫描） < index（索引扫描） < range（范围） < ref（等值）
-- key: NULL（未使用索引）vs idx_xxx（使用了索引）
-- rows: 实际扫描行数
-- Extra: Using filesort, Using temporary（需要优化）

Step 2：分析慢查询日志

-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;  -- 1秒以上记录

-- 分析日志
-- Linux: pt-query-digest /var/log/mysql/slow.log

-- 关注字段：
-- Query_time: 总耗时
-- Lock_time: 锁等待时间
-- Rows_sent: 返回行数
-- Rows_examined: 扫描行数

Step 3：使用EXPLAIN ANALYZE

-- MySQL 8.0.18+
EXPLAIN ANALYZE SELECT ...;

-- 输出实际执行时间和行数
-- 对比预估（EXPLAIN）和实际（ANALYZE）的差异

Step 4：性能分析

-- 开启profiling
SET profiling = 1;

-- 执行查询
SELECT ...;

-- 查看详细耗时
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;

-- 分析各阶段耗时：
-- Sending data: 数据读取和传输
-- Sorting result: 排序
-- Creating sort index: 创建排序索引

Step 5：系统资源检查

# CPU使用率
top

# 内存使用
free -h

# 磁盘IO
iostat -x 1

# MySQL连接数
mysql> SHOW STATUS LIKE 'Threads_connected';
mysql> SHOW VARIABLES LIKE 'max_connections';

四、常见误区

误区1：认为有索引就一定快

-- 反例：索引选择性差
INDEX(gender)  -- 只有2个值
SELECT * FROM users WHERE gender='M';  -- 返回50%的数据
-- 可能比全表扫描还慢（随机IO）

误区2：忽略回表成本

-- 反例：覆盖不全的索引
INDEX(user_id)
SELECT * FROM orders WHERE user_id=?;  -- 需要回表获取所有列
-- 如果user_id的订单很多，回表成本高

误区3：过度依赖EXPLAIN

-- EXPLAIN 只是预估，不是实际执行
-- 需要结合 EXPLAIN ANALYZE 或实际执行时间

五、优化建议清单

1. 索引优化

• 创建覆盖索引，避免回表
• 使用联合索引，提升区分度
• 定期ANALYZE TABLE，更新统计信息
• 删除未使用的索引

2. 查询优化

• 避免SELECT *，只查询必要列
• 深度分页使用延迟关联或游标
• 不在索引列使用函数
• 确保类型匹配，避免隐式转换

3. 事务优化

• 缩短事务执行时间
• 避免长事务持有锁
• 合理使用隔离级别

4. 硬件优化

• 增加Buffer Pool大小
• 使用SSD替代机械硬盘
• 增加内存

六、面试总结

使用索引仍然很慢的常见原因：

数据层面：

1. 回表次数过多（过滤后数据量大）
2. 索引区分度不够（低选择性）
3. 数据量过大或深度分页

索引层面：

1. 索引选择错误（优化器判断失误）
2. 未使用覆盖索引（额外回表开销）
3. 统计信息不准确

查询层面：

1. 索引列有函数或计算
2. 隐式类型转换导致索引失效

系统层面：

1. 锁等待或死锁
2. 磁盘IO瓶颈（Buffer Pool命中率低）

排查方法：

1. EXPLAIN/EXPLAIN ANALYZE 分析执行计划
2. 慢查询日志分析
3. SHOW PROFILE 详细耗时
4. 系统资源监控

优化方向：

• 索引：覆盖索引、联合索引、更新统计
• 查询：避免函数、类型匹配、限制返回
• 分页：延迟关联、游标分页
• 系统：增加Buffer Pool、使用SSD

这道题考查对MySQL性能问题的全面排查和优化能力，是实战经验的体现。