MySQL 慢查询优化:索引失效的十大场景与解决方案
一、概述
1.1 背景介绍
MySQL慢查询是生产环境中最常见的性能瓶颈之一。虽然我们精心设计了索引,但在实际运行中却发现查询依然很慢,EXPLAIN分析显示索引并未生效。索引失效的原因复杂多样,理解这些常见场景并掌握优化方法,是每个后端工程师和DBA的必备技能。本文将系统性地剖析索引失效的十大典型场景,并提供可落地的解决方案。
1.2 技术特点
- • 特点一:索引失效往往不是索引本身的问题,而是SQL写法、数据类型转换、函数使用等导致优化器无法使用索引
- • 特点二:同一个查询在不同数据量、不同数据分布下,优化器可能做出完全不同的执行计划选择
- • 特点三:理解MySQL优化器的cost-based选择机制,是预判和解决索引失效问题的关键
1.3 适用场景
- • 场景一:生产环境出现慢查询告警,需要快速定位索引失效原因并优化
- • 场景二:代码审查阶段发现潜在的索引失效风险,需要提前重构SQL
- • 场景三:数据库架构设计阶段,需要规避常见的索引失效陷阱
1.4 环境要求
二、详细步骤
2.1 准备工作
◆ 2.1.1 开启慢查询日志
-- 查看慢查询配置
SHOW VARIABLES LIKE'%slow_query%';
SHOW VARIABLES LIKE'long_query_time';
-- 开启慢查询日志(动态设置)
SETGLOBAL slow_query_log ='ON';
SETGLOBAL slow_query_log_file =
'/var/log/mysql/slow.log';
SETGLOBAL long_query_time =1; -- 超过1秒记录
-- 记录未使用索引的查询
SETGLOBAL log_queries_not_using_indexes ='ON';
配置文件永久配置(my.cnf):
[mysqld]
# 慢查询日志配置
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
◆ 2.1.2 准备测试数据
-- 创建测试表
CREATE TABLE `users` (
`id` bigintNOT NULL AUTO_INCREMENT,
`username` varchar(50) NOT NULL,
`email` varchar(100) NOT NULL,
`age` intDEFAULTNULL,
`status` tinyint DEFAULT'1',
`created_at` datetime DEFAULTCURRENT_TIMESTAMP,
`updated_at` datetime DEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_username` (`username`),
KEY `idx_email` (`email`),
KEY `idx_age` (`age`),
KEY `idx_status_created` (`status`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 插入测试数据(100万条)
DELIMITER $$
CREATEPROCEDURE generate_test_data()
BEGIN
DECLARE i INTDEFAULT1;
WHILE i <=1000000 DO
INSERT INTO users (username, email, age, status, created_at)
VALUES (
CONCAT('user', i),
CONCAT('user', i, '@example.com'),
FLOOR(RAND() *60) +18,
FLOOR(RAND() *3),
DATE_ADD('2020-01-01', INTERVALFLOOR(RAND() *1460) DAY)
);
SET i = i +1;
END WHILE;
END$$
DELIMITER ;
CALL generate_test_data();
◆ 2.1.3 EXPLAIN基础知识
-- EXPLAIN基本用法
EXPLAIN SELECT*FROM users WHERE age =25;
-- EXPLAIN输出关键字段说明:
-- type: 连接类型 (ALL < index < range < ref < eq_ref < const)
-- possible_keys: 可能使用的索引
-- key: 实际使用的索引
-- key_len: 使用的索引长度
-- rows: 扫描的行数
-- Extra: 额外信息 (Using where, Using index, Using filesort等)
2.2 十大索引失效场景
◆ 2.2.1 场景一:在索引列上使用函数
问题示例:
-- 索引失效:在索引列上使用函数
EXPLAIN SELECT*FROM users WHEREYEAR(created_at) =2023;
EXPLAIN结果:
type: ALL
key: NULL
rows: 1000000
Extra: Using where
原因分析: 当对索引列使用函数时,MySQL无法使用索引,因为索引存储的是原始值,而不是函数计算后的值。
解决方案:
-- 方案1:改写查询条件,避免函数
EXPLAIN SELECT*FROM users
WHERE created_at >='2023-01-01'AND created_at <'2024-01-01';
-- 方案2:创建函数索引(MySQL 8.0+)
ALTER TABLE users ADD INDEX idx_created_year ((YEAR(created_at)));
EXPLAIN SELECT*FROM users WHEREYEAR(created_at) =2023;
其他常见函数导致索引失效的例子:
-- ❌ 索引失效
SELECT*FROM users WHERE DATE_FORMAT(created_at, '%Y-%m') ='2023-01';
SELECT*FROM users WHERESUBSTRING(username, 1, 3) ='adm';
SELECT*FROM users WHERE CONCAT(username, email) ='adminadmin@test.com';
-- ✅ 优化后
SELECT*FROM users WHERE created_at >='2023-01-01'AND created_at <'2023-02-01';
SELECT*FROM users WHERE username LIKE'adm%';
-- 对于CONCAT,考虑分别查询或创建虚拟列索引
◆ 2.2.2 场景二:隐式类型转换
问题示例:
-- 表结构:username varchar(50), 有索引
-- 索引失效:字符串类型的字段使用数字查询
EXPLAIN SELECT*FROM users WHERE username =123456;
-- type: ALL, key: NULL, 全表扫描
原因分析: MySQL会将字符串转换为数字进行比较,相当于 CAST(username AS SIGNED) = 123456,触发了函数计算导致索引失效。
解决方案:
-- 正确写法:使用字符串类型
EXPLAIN SELECT*FROM users WHERE username ='123456';
-- type: ref, key: idx_username, 使用索引
其他类型转换场景:
-- ❌ 数字字段使用字符串(通常能用索引,但不推荐)
SELECT*FROM users WHERE age ='25'; -- age是int类型
-- ❌ 日期类型使用字符串
SELECT
*FROM users WHERE created_at =20230101; -- created_at是datetime
-- ✅ 正确写法
SELECT*FROM users WHERE age =25;
SELECT*FROM users WHERE created_at ='2023-01-01';
◆ 2.2.3 场景三:LIKE查询以通配符开头
问题示例:
-- 索引失效:前缀通配符
EXPLAIN SELECT*FROM users WHERE username LIKE'%admin%';
EXPLAIN SELECT*FROM users WHERE username LIKE'%admin';
-- type: ALL, key: NULL
原因分析: B+树索引是按照索引列的值从左到右顺序存储的,前缀通配符无法利用索引的有序性进行快速定位。
解决方案:
-- ✅ 后缀通配符可以使用索引
EXPLAIN SELECT*FROM users WHERE username LIKE'admin%';
-- type: range, key: idx_username
-- 对于必须使用前缀模糊查询的场景:
-- 方案1:使用全文索引
ALTER TABLE users ADD FULLTEXT INDEX ft_username (username);
SELECT*FROM users WHEREMATCH(username) AGAINST('admin'INNATURALLANGUAGE MODE);
-- 方案2:使用ElasticSearch等专业全文检索引擎
-- 方案3:反向索引(将字符串反转存储)
ALTER TABLE users ADDCOLUMN username_reverse VARCHAR(50);
UPDATE users SET username_reverse = REVERSE(username);
ALTER TABLE users ADD INDEX idx_username_reverse (username_reverse);
-- 查询时反转搜索词
SELECT*FROM users WHERE username_reverse LIKE REVERSE('admin') ||'%';
◆ 2.2.4 场景四:OR条件中有未建索引的列
问题示例:
-- status有索引,age有索引,但OR连接
EXPLAIN SELECT*FROM users WHERE status =1OR age =25;
-- 可能使用index_merge,也可能全表扫描
-- status有索引,但created_at无独立索引
EXPLAIN SELECT*FROM users WHERE status =1OR created_at >'2023-01-01';
-- type: ALL, 全表扫描
原因分析: OR条件要求两侧的条件都能使用索引,MySQL才会考虑使用index_merge。如果任一条件无法使用索引,则可能放弃所有索引。
解决方案:
-- 方案1:改写为UNION(推荐)
EXPLAIN
SELECT*FROM users WHERE status =1
UNION
SELECT*FROM users WHERE age =25;
-- 方案2:确保OR两侧都有索引
ALTER TABLE users ADD INDEX idx_created_at (created_at);
EXPLAIN SELECT*FROM users WHERE status =
1OR created_at >'2023-01-01';
-- 方案3:根据业务逻辑改写为IN(适用于枚举值)
EXPLAIN SELECT*FROM users WHERE status IN (0, 1) AND age =25;
◆ 2.2.5 场景五:联合索引不满足最左前缀原则
问题示例:
-- 联合索引: KEY idx_status_created (status, created_at)
-- ✅ 使用索引:满足最左前缀
EXPLAIN SELECT*FROM users WHERE status =1;
EXPLAIN SELECT*FROM users WHERE status =1AND created_at >'2023-01-01';
-- ❌ 不使用索引:跳过status直接使用created_at
EXPLAIN SELECT*FROM users WHERE created_at >'2023-01-01';
-- type: ALL, key: NULL
原因分析: 联合索引是按照索引列的顺序构建B+树,必须从最左侧的列开始才能有效利用索引结构。
解决方案:
-- 方案1:调整WHERE条件顺序(如果业务允许)
EXPLAIN SELECT*FROM users WHERE status =1AND created_at >'2023-01-01';
-- 方案2:创建单独的索引
ALTER TABLE users ADD INDEX idx_created_at (created_at);
-- 方案3:调整联合索引顺序(根据查询模式决定)
ALTER TABLE users DROP INDEX idx_status_created;
ALTER TABLE users ADD INDEX idx_created_status (created_at, status);
联合索引使用规则总结:
-- 假设联合索引: INDEX idx_abc (a, b, c)
-- ✅ 使用索引
WHERE a =1
WHERE a =1AND b =2
WHERE a =1AND b =2AND c =3
WHERE a =1AND c =3-- 只使用a部分
-- ❌ 不使用索引
WHERE b =2
WHERE c =3
WHERE b =2AND c =3
-- ⚠️ 部分使用索引
WHERE a =1AND b >2AND c =3-- 使用a,b,但c无法使用(范围查询后的列无法使用)
◆ 2.2.6 场景六:不等于操作符(!=、<>)
问题示例:
-- 可能索引失效
EXPLAIN SELECT*FROM users WHERE status !=1;
EXPLAIN SELECT*FROM users WHERE status <>1;
-- type: ALL 或 range,取决于数据分布
原因分析: 不等于操作需要扫描大量数据,MySQL优化器会评估成本,如果不等于条件过滤后的数据量过大(如超过30%),优化器可能认为全表扫描更高效。
解决方案:
-- 方案1:改写为IN或OR(适用于已知的取值范围)
-- 假设status只有0,1,2三个值
EXPLAIN SELECT*FROM users WHERE status IN (0, 2);
-- 方案2:使用覆盖索引减少回表
ALTER TABLE users ADD INDEX idx_status_id (status, id);
EXPLAIN SELECT id FROM users WHERE status !=1;
-- type: range, Extra: Using where; Using index
-- 方案3:如果!=1的数据很少,考虑单独建表或分区存储
验证优化器选择:
-- 强制使用索引
EXPLAIN SELECT*FROM users FORCE INDEX(idx_status) WHERE status !=1;
-- 查看优化器成本估算
EXPLAIN FORMAT=JSON SELECT*FROM users WHERE status !=1\G
◆ 2.2.7 场景七:IS NULL 和 IS NOT NULL
问题示例:
-- 可能索引失效(取决于NULL值的比例)
EXPLAIN SELECT*FROM users WHERE age ISNULL;
EXPLAIN SELECT*FROM users WHERE age ISNOT NULL;
原因分析:
- • IS NULL通常能使用索引(如果NULL值较少)
- • IS NOT NULL可能不使用索引(如果NULL值较少,意味着NOT NULL数据很多)
解决方案:
-- 方案1:设计表时避免NULL
ALTER TABLE users MODIFY age INTNOT NULLDEFAULT0;
-- 方案2:使用特殊值代替NULL
UPDATE users SET age =-1WHERE age ISNULL;
SELECT*FROM users WHERE age =-1;
-- 方案3:使用覆盖索引
CREATE INDEX idx_age_id ON users(age, id);
SELECT id, age FROM users WHERE age ISNOT NULL;
-- Extra: Using where; Using index
NULL值处理建议:
-- 表设计时的最佳实践
CREATE TABLE users_optimized (
id BIGINTNOT NULL AUTO_INCREMENT,
username VARCHAR(50) NOT NULLDEFAULT'',
age INTNOT NULLDEFAULT0,
status TINYINT NOT NULLDEFAULT1,
PRIMARY KEY (id)
) ENGINE=InnoDB;
◆ 2.2.8 场景八:范围查询后的列无法使用索引
问题示例:
-- 联合索引: KEY idx_status_age_created (status, age, created_at)
EXPLAIN SELECT*FROM users
WHERE status =1AND age >25AND created_at >'2023-01-01';
-- key: idx_status_age_created
-- 但只使用了status和age,created_at未使用索引
原因分析: 在联合索引中,一旦出现范围查询(>、
解决方案:
-- 方案1:调整索引顺序,将范围查询的列放在最后
ALTER TABLE users DROP INDEX idx_status_age_created;
ALTER TABLE users ADD INDEX idx_status_created_age (status, created_at, age);
-- 此时查询改为:
SELECT*FROM users
WHERE status =1AND created_at >'2023-01-01'AND age >25;
-- 方案2:分别创建索引,让优化器选择
CREATE INDEX idx_status_age ON users(status, age);
CREATE INDEX idx_status_created ON users(status, created_at);
-- 方案3:改写为精确匹配
SELECT*FROM users
WHERE status =1AND age IN (26, 27, 28, 29, 30) AND created_at >'2023-01-01';
◆ 2.2.9 场景九:数据分布导致优化器放弃索引
问题示例:
-- 假设表中99%的用户status都是1
EXPLAIN SELECT*FROM users WHERE status =1;
-- type: ALL, key: NULL, 优化器选择全表扫描
原因分析: MySQL优化器是基于成本(Cost-Based)的,如果条件过滤后的数据占比过高(通常>20-30%),优化器会认为全表扫描比走索引再回表更快。
解决方案:
-- 方案1:使用覆盖索引避免回表
CREATE INDEX idx_status_cover ON users(status, id, username, email);
SELECT id, username, email FROM users WHERE status =1;
-- Extra: Using where; Using index
-- 方案2:强制使用索引(谨慎使用)
SELECT*FROM users FORCE INDEX(idx_status) WHERE status =1;
-- 方案3:分区表设计
ALTER TABLE users PARTITIONBY LIST(status) (
PARTITION p0 VALUESIN (0),
PARTITION p1 VALUESIN (1),
PARTITION p2 VALUESIN (2)
);
-- 方案4:调整数据模型,增加过滤条件
SELECT*FROM users
WHERE status =1AND created_at >'2024-01-01'; -- 增加时间过滤
分析优化器成本:
-- 查看索引统计信息
SHOW INDEX FROM users;
-- 更新统计信息
ANALYZE TABLE users;
-- 查看优化器trace
SET optimizer_trace='enabled=on';
SELECT*FROM users WHERE status =1;
SELECT*FROM information_schema.OPTIMIZER_TRACE\G
SET optimizer_trace='enabled=off';
◆ 2.2.10 场景十:字符集和排序规则不一致
问题示例:
-- 表字符集: utf8mb4, 排序规则: utf8mb4_general_ci
-- 查询使用不同的排序规则
EXPLAIN SELECT*FROM users
WHERE username ='test'COLLATE utf8mb4_bin;
-- 可能导致索引失效或无法使用索引优化排序
原因分析: 当查询条件或ORDER BY指定的排序规则与索引的排序规则不一致时,MySQL可能无法有效使用索引。
解决方案:
-- 方案1:统一字符集和排序规则
ALTER TABLE users CONVERTTOCHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 方案2:创建特定排序规则的索引
ALTER TABLE users ADD INDEX idx_username_bin (username) COLLATE utf8mb4_bin;
-- 方案3:查询时不指定COLLATE
SELECT*FROM users WHERE username ='test';
三、示例代码和配置
3.1 慢查询分析脚本
◆ 3.1.1 自动化分析脚本
#!/bin/bash
# 慢查询分析脚本: slow_query_analyzer.sh
SLOW_LOG="/var/log/mysql/slow.log"
REPORT_FILE="/tmp/slow_query_report_$(date +%Y%m%d).txt"
echo"========== MySQL慢查询分析报告 ==========" > "$REPORT_FILE"
echo"生成时间: $(date '+%Y-%m-%d %H:%M:%S')" >> "$REPORT_FILE"
echo"" >> "$REPORT_FILE"
# 使用pt-query-digest分析慢查询
ifcommand -v pt-query-digest &> /dev/null; then
echo"【TOP 10 慢查询】" >> "$REPORT_FILE"
pt-query-digest "$SLOW_LOG" --limit 10 >> "$REPORT_FILE"
else
echo"pt-query-digest未安装,使用mysqldumpslow分析" >> "$REPORT_FILE"
mysqldumpslow -s t -t 10 "$SLOW_LOG" >> "$REPORT_FILE"
fi
# 检查未使用索引的查询
echo -e "\n【未使用索引的查询】" >> "$REPORT_FILE"
grep "Query_time""$SLOW_LOG" | head -20 >> "$REPORT_FILE"
echo"分析完成,报告保存至: $REPORT_FILE"
◆ 3.1.2 索引使用情况监控
-- 查看索引使用情况
SELECT
object_schema AS database_name,
object_name AS table_name,
index_name,
COUNT_STAR AS index_usage_count
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name ISNOT NULL
ORDERBY COUNT_STAR DESC
LIMIT 20;
-- 查找未使用的索引
SELECT
object_schema AS database_name,
object_name AS table_name,
index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name ISNOT NULL
AND index_name !='PRIMARY'
AND COUNT_STAR =0
ORDERBY object_schema, object_name;
3.2 SQL优化实战案例
◆ 案例一:订单查询优化
场景描述:电商订单表,需要查询指定用户在某个时间范围内的订单,并按创建时间倒序排列。
原始SQL(慢):
-- 表结构
CREATE TABLE orders (
id BIGINTPRIMARY KEY AUTO_INCREMENT,
user_id BIGINTNOT NULL,
order_no VARCHAR(32) NOT NULL,
amount DECIMAL(10,2) NOT NULL,
status TINYINT NOT NULL,
created_at DATETIME NOT NULL,
INDEX idx_user_id (user_id),
INDEX idx_created_at (created_at)
) ENGINE=InnoDB;
-- 慢查询SQL
SELECT*FROM orders
WHERE user_id =123456
AND created_at >='2024-01-01'
AND created_at <'2024-02-01'
ORDERBY created_at DESC
LIMIT 20;
-- EXPLAIN结果:
-- type: ref, key: idx_user_id, rows: 50000, Extra: Using where; Using filesort
优化方案:
-- 创建联合索引,覆盖查询条件和排序
ALTER TABLE orders ADD INDEX idx_user_created (user_id, created_at DESC);
-- 优化后的查询
SELECT*FROM orders
WHERE user_id =123456
AND created_at >='2024-01-01'
AND created_at <'2024-02-01'
ORDERBY created_at DESC
LIMIT 20;
-- EXPLAIN结果:
-- type: range, key: idx_user_created, rows: 100, Extra: Using where
性能对比:
-- 测试脚本
SET profiling =1;
-- 原始查询
SELECT*FROM orders WHERE user_id =123456AND created_at >='2024-01-01'AND created_at <'2024-02-01'ORDERBY created_at DESC LIMIT 20;
-- 优化后查询
SELECT*FROM orders WHERE user_id =123456AND created_at >=
'2024-01-01'AND created_at <'2024-02-01'ORDERBY created_at DESC LIMIT 20;
SHOW PROFILES;
-- 优化前: 2.5s -> 优化后: 0.02s, 性能提升125倍
◆ 案例二:分页查询深度翻页优化
场景描述:用户列表分页查询,当翻页到很后面时(如第10000页),查询变得非常慢。
原始SQL(慢):
-- 深度翻页查询
SELECT*FROM users
WHERE status =1
ORDERBY id
LIMIT 200000, 20;
-- 执行时间: 5.2s
-- rows examined: 200020
优化方案:
-- 方案1:使用子查询,先定位ID再JOIN
SELECT u.*FROM users u
INNERJOIN (
SELECT id FROM users
WHERE status =1
ORDERBY id
LIMIT 200000, 20
) AS t ON u.id = t.id;
-- 执行时间: 0.8s
-- 方案2:记录上次查询的最大ID(推荐)
SELECT*FROM users
WHERE status =1AND id >1234567-- 上一页的最大ID
ORDERBY id
LIMIT 20;
-- 执行时间: 0.01s
-- 方案3:使用游标(适用于后台任务)
DECLARE done INTDEFAULTFALSE;
DECLARE cur CURSORFORSELECT*FROM users WHERE status =1ORDERBY id;
DECLARE CONTINUE HANDLER FORNOT FOUND SET done =TRUE;
OPEN cur;
read_loop: LOOP
FETCH cur INTO ...;
IF done THEN
LEAVE read_loop;
END IF;
-- 处理数据
END LOOP;
CLOSE cur;
四、最佳实践和注意事项
4.1 最佳实践
◆ 4.1.1 索引设计原则
- • 原则一:选择性高的列优先建索引
-- 计算列的选择性(唯一值比例)
SELECT
COUNT(DISTINCT username) /COUNT(*) AS username_selectivity,
COUNT(DISTINCT status) /COUNT(*) AS status_selectivity,
COUNT(DISTINCT age) /COUNT(*) AS age_selectivity
FROM users;
-- username: 0.98 (高选择性,适合索引)
-- status: 0.03 (低选择性,不适合单独索引)
-- age: 0.05 (低选择性)
- • 原则二:联合索引顺序: 等值 > 范围 > 排序
-- 查询: WHERE status = 1 AND created_at > xxx ORDER BY id
-- 推荐索引: INDEX idx_opt (status, created_at, id)
-- 查询: WHERE age > 25 AND status = 1 ORDER BY created_at
-- 推荐索引: INDEX idx_opt (status, age, created_at) -- status等值在前
- • 原则三:避免冗余索引
-- ❌ 冗余索引
INDEX idx_a (a)
INDEX idx_ab (a, b) -- idx_a是冗余的
-- ✅ 优化后
INDEX idx_ab (a, b) -- 删除idx_a
-- 检查冗余索引
SELECT*FROM sys.schema_redundant_indexes;
◆ 4.1.2 SQL编写规范
- • **规范一:SELECT指定列名,避免SELECT ***
-- ❌ 不推荐
SELECT*FROM users WHERE id =1;
-- ✅ 推荐(便于使用覆盖索引)
SELECT id, username, email FROM users WHERE id =1;
- • 规范二:避免在WHERE子句中对字段进行计算
-- ❌ 不推荐
WHEREYEAR(created_at) =2024
WHERE amount *0.8>100
-- ✅ 推荐
WHERE created_at >='2024-01-01'AND created_at <'2025-01-01'
WHERE amount >125-- 100 / 0.8
- • 规范三:尽量使用UNION ALL代替UNION
-- ❌ UNION会去重,性能较差
SELECT id FROM orders WHERE status =1
UNION
SELECT id FROM orders WHERE status =2;
-- ✅ 如果确定无重复,使用UNION ALL
SELECT id FROM orders WHERE status =1
UNIONALL
SELECT id FROM orders WHERE status =2;
◆ 4.1.3 定期维护
- • 维护任务一:更新统计信息
-- 手动更新统计信息
ANALYZE TABLE users;
-- 批量更新所有表
SELECT CONCAT('ANALYZE TABLE ', table_schema, '.', table_name, ';')
FROM information_schema.tables
WHERE table_schema ='your_database';
- • 维护任务二:清理碎片
-- 查看表碎片
SELECT
table_name,
ROUND((data_length + index_length) /1024/1024, 2) AS total_mb,
ROUND(data_free /1024/1024, 2) AS data_free_mb,
ROUND(data_free / (data_length + index_length) *100, 2) AS fragment_pct
FROM information_schema.tables
WHERE table_schema ='your_database'
AND data_free >0
ORDERBY fragment_pct DESC;
-- 优化表(重建索引)
OPTIMIZE TABLE users;
- • 维护任务三:监控慢查询趋势
-- 使用Prometheus监控慢查询数
SELECT
VARIABLE_VALUE AS slow_queries
FROM performance_schema.global_status
WHERE VARIABLE_NAME ='Slow_queries';
4.2 注意事项
◆ 4.2.1 索引使用误区
⚠️ 警告:索引不是越多越好,过多索引会严重影响写入性能和占用存储空间
- • 索引维护开销:INSERT/UPDATE/DELETE都需要更新索引
◆ 4.2.2 常见错误
◆ 4.2.3 性能测试注意事项
- • 注意事项二:清理缓存影响
-- 清理查询缓存(MySQL 5.7及以下)
RESET QUERY CACHE;
-- 清理InnoDB缓冲池(重启MySQL或刷新缓冲池)
SETGLOBAL innodb_buffer_pool_dump_at_shutdown =1;
SETGLOBAL innodb_buffer_pool_load_at_startup =1;
- • 注意事项三:使用性能分析工具
-- 启用profiling
SET profiling =1;
-- 执行查询
SELECT*FROM users WHERE username ='test';
-- 查看详细耗时
SHOW PROFILE FOR QUERY 1;
五、故障排查和监控
5.1 故障排查
◆ 5.1.1 慢查询诊断流程
#!/bin/bash
# 慢查询诊断流程脚本
# 1. 确认慢查询是否开启
mysql -e "SHOW VARIABLES LIKE '%slow_query%';"
# 2. 查看当前正在执行的慢查询
mysql -e "SELECT * FROM information_schema.processlist WHERE time > 5 ORDER BY time DESC;"
# 3. 分析EXPLAIN执行计划
mysql -e "EXPLAIN SELECT * FROM users WHERE username = 'test'\G"
# 4. 查看表索引情况
mysql -e "SHOW INDEX FROM users;"
# 5. 检查表统计信息
mysql -e "SHOW TABLE STATUS LIKE 'users'\G"
◆ 5.1.2 常见问题排查
问题一:为什么EXPLAIN显示使用了索引,但查询仍然很慢?
-- 可能原因1:回表次数过多
EXPLAIN SELECT*FROM users WHERE age =25;
-- type: ref, rows: 50000, Extra: Using where
-- 虽然使用了索引,但需要回表50000次
-- 解决方案:使用覆盖索引
CREATE INDEX idx_age_cover ON users(age, id, username, email);
SELECT id, username, email FROM users WHERE age =25;
-- Extra: Using where; Using index (无需回表)
-- 可能原因2:索引选择性太低
SELECTCOUNT(DISTINCT age) /COUNT(*) FROM users;
-- 0.05, 选择性很低
-- 解决方案:增加联合索引提高选择性
CREATE INDEX idx_age_status ON users(age, status);
SELECT*FROM users WHERE age =25AND status =1;
问题二:同样的SQL,在测试环境走索引,在生产环境不走索引
-- 原因:数据分布不同,导致优化器选择不同
-- 诊断步骤1:对比统计信息
SHOW INDEX FROM users; -- 查看Cardinality
-- 诊断步骤2:更新生产环境统计信息
ANALYZE TABLE users;
-- 诊断步骤3:查看优化器成本估算
EXPLAIN FORMAT
=JSON SELECT*FROM users WHERE status =1\G
-- 临时解决:强制使用索引(不推荐长期使用)
SELECT*FROM users FORCE INDEX(idx_status) WHERE status =1;
问题三:加了索引后写入变慢
-- 诊断:查看索引数量和大小
SELECT
table_name,
COUNT(*) AS index_count,
ROUND(SUM(stat_value) * @@innodb_page_size/1024/1024, 2) AS index_size_mb
FROM mysql.innodb_index_stats
WHERE database_name ='your_database'
GROUPBY table_name;
-- 解决方案1:合并冗余索引
-- 删除使用频率低的索引
ALTER TABLE users DROP INDEX idx_rarely_used;
-- 解决方案2:延迟索引创建(插入时无索引,批量插入后再创建)
ALTER TABLE users DROP INDEX idx_created_at;
-- 批量插入数据...
ALTER TABLE users ADD INDEX idx_created_at (created_at);
◆ 5.1.3 实时监控慢查询
-- 创建慢查询监控视图
CREATEOR REPLACE VIEW v_slow_queries AS
SELECT
DIGEST_TEXT,
COUNT_STAR AS exec_count,
ROUND(AVG_TIMER_WAIT /1000000000, 2) AS avg_time_ms,
ROUND(MAX_TIMER_WAIT /1000000000, 2) AS max_time_ms,
ROUND(SUM_ROWS_EXAMINED / COUNT_STAR, 0) AS avg_rows_examined
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME ='your_database'
AND AVG_TIMER_WAIT >1000000000-- 超过1秒
ORDERBY AVG_TIMER_WAIT DESC
LIMIT 20;
-- 查询慢查询
SELECT*FROM v_slow_queries;
5.2 性能监控
◆ 5.2.1 关键指标监控
-- 1. 查询吞吐量
SHOWGLOBAL STATUS LIKE'Questions';
SHOWGLOBAL STATUS LIKE'Queries';
-- 2. 慢查询数量
SHOWGLOBAL STATUS LIKE'Slow_queries';
-- 3. 索引使用率
SELECT
ROUND((SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME ='Handler_read_rnd_next') /
(SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME ='Handler_read_key') *100, 2) AS index_usage_pct;
-- 4. 表锁等待
SHOWGLOBAL STATUS LIKE'Table_locks_waited';
-- 5. InnoDB行锁等待
SHOWGLOBAL STATUS LIKE'Innodb_row_lock%';
◆ 5.2.2 Prometheus监控配置
# prometheus.yml - MySQL exporter配置
scrape_configs:
-job_name:'mysql'
static_configs:
-targets: ['localhost:9104']
relabel_configs:
-source_labels: [__address__]
target_label:instance
Grafana告警规则:
# mysql_alerts.yml
groups:
-name:mysql_performance
interval:30s
rules:
# 慢查询告警
-alert:HighSlowQueries
expr:rate(mysql_global_status_slow_queries[5m])>10
for:5m
labels:
severity:warning
annotations:
summary:"MySQL慢查询过多"
description:"实例 {{ $labels.instance }} 慢查询速率: {{ $value }}/s"
# 索引未命中率高
-alert:HighTableScanRate
expr:rate(mysql_global_status_handler_read_rnd_next[5m])/rate(mysql_global_status_handler_read_key[5m])>100
for:10m
labels:
severity:warning
annotations:
summary:"全表扫描比例过高"
description:"实例 {{ $labels.instance }} 可能存在索引失效"
◆ 5.2.3 监控指标说明
5.3 性能测试
◆ 5.3.1 基准测试脚本
#!/bin/bash
# MySQL性能基准测试:benchmark_test.sh
# 使用sysbench进行压力测试
sysbench /usr/share/sysbench/oltp_read_write.lua \
--mysql-host=127.0.0.1 \
--mysql-port=3306 \
--mysql-user=root \
--mysql-password=password \
--mysql-db=testdb \
--tables=10 \
--table-size=1000000 \
prepare
# 运行测试
sysbench /usr/share/sysbench/oltp_read_write.lua \
--mysql-host=127.0.0.1 \
--mysql-port=3306 \
--mysql-user=root \
--mysql-password=password \
--mysql-db=testdb \
--tables=10 \
--table-size=1000000 \
--threads=16 \
--time=300 \
--report-interval=10 \
run
# 清理
sysbench /usr/share/sysbench/oltp_read_write.lua \
--mysql-host=127.0.0.1 \
--mysql-port=3306 \
--mysql-user=root \
--mysql-password=password \
--mysql-db=testdb \
--tables=10 \
cleanup
◆ 5.3.2 SQL性能对比测试
-- 对比测试框架
DELIMITER $$
CREATEPROCEDURE compare_query_performance(
IN sql_original TEXT,
IN sql_optimized TEXT,
IN iterations INT
)
BEGIN
DECLARE i INTDEFAULT0;
DECLARE start_time BIGINT;
DECLARE end_time BIGINT;
DECLARE original_time BIGINTDEFAULT0;
DECLARE optimized_time BIGINTDEFAULT0;
-- 清理缓存
SET@dummy= (SELECT SLEEP(0.1));
-- 测试原始SQL
WHILE i < iterations DO
SET start_time = UNIX_TIMESTAMP(NOW(6));
SET@sql= sql_original;
PREPARE stmt FROM@sql;
EXECUTE stmt;
DEALLOCATEPREPARE stmt;
SET end_time = UNIX_TIMESTAMP(NOW(6));
SET original_time = original_time + (end_time - start_time);
SET i = i +1;
END WHILE;
SET i =0;
-- 测试优化后SQL
WHILE i < iterations DO
SET start_time = UNIX_TIMESTAMP(NOW(6));
SET@sql= sql_optimized;
PREPARE stmt FROM@sql;
EXECUTE stmt;
DEALLOCATEPREPARE stmt;
SET end_time = UNIX_TIMESTAMP(NOW(6));
SET optimized_time = optimized_time + (end_time - start_time);
SET i = i +1;
END WHILE;
-- 输出结果
SELECT
ROUND(original_time / iterations, 4) AS avg_original_time_sec,
ROUND(optimized_time / iterations, 4) AS avg_optimized_time_sec,
ROUND((original_time - optimized_time) / original_time *100, 2) AS improvement_pct;
END$$
DELIMITER ;
-- 使用示例
CALL compare_query_performance(
'SELECT * FROM users WHERE username LIKE "%test%"',
'SELECT * FROM users WHERE username LIKE "test%"',
100
);
六、总结
6.1 技术要点回顾
- • ✅ 要点一:索引失效的本质是MySQL优化器无法利用索引结构进行快速定位,常见原因包括函数计算、类型转换、不合理的查询条件等
- • ✅ 要点二:联合索引的最左前缀原则和索引列顺序设计,直接决定了索引的使用效率,需要根据查询模式合理设计
- • ✅ 要点三:MySQL优化器是基于成本的,数据分布、统计信息、硬件配置都会影响执行计划,需要定期维护统计信息
- • ✅ 要点四:索引不是万能的,需要在查询性能和写入性能之间权衡,避免过度索引
6.2 进阶学习方向
- • 学习资源:《MySQL技术内幕:InnoDB存储引擎》《高性能MySQL》
- • 实践建议:阅读MySQL源码,深入理解B+树索引结构和优化器cost模型
- • 学习资源:ShardingSphere、Vitess、ProxySQL官方文档
- • 实践建议:在单表优化到极限后,考虑水平拆分和垂直拆分方案
- • 学习资源:TiDB、CockroachDB、YugabyteDB
- • 实践建议:了解分布式数据库的索引设计差异和查询优化策略
6.3 参考资料
- • MySQL官方文档 - Optimization - MySQL优化指南
- • 《高性能MySQL》 - MySQL性能优化经典书籍
- • MySQL索引背后的数据结构及算法原理 - 深入理解B+树
- • Percona Blog - MySQL性能优化博客
- • pt-query-digest - 慢查询分析工具
附录
A. 索引优化决策树
查询慢?
├─ 是否走索引?
│ ├─ 否
│ │ ├─ WHERE条件有索引? → 否 → 创建索引
│ │ ├─ 使用函数/计算? → 是 → 改写SQL或创建函数索引
│ │ ├─ 类型转换? → 是 → 统一数据类型
│ │ ├─ LIKE '%xx%'? → 是 → 使用全文索引或搜索引擎
│ │ └─ 数据分布问题? → 是 → 增加过滤条件或分区
│ └─ 是
│ ├─ 回表次数多? → 创建覆盖索引
│ ├─ Using filesort? → 调整索引列顺序,包含排序列
│ ├─ 扫描行数多? → 提高索引选择性
│ └─ JOIN慢? → 确保关联列都有索引
└─ 否 → 检查业务逻辑或硬件资源
B. EXPLAIN输出字段详解
| |
|---|
| |
| 查询类型:SIMPLE、PRIMARY、UNION、SUBQUERY等 |
| |
| 连接类型(性能从差到好):ALL < index < range < ref < eq_ref < const < system |
|
|
| |
| |
| |
| |
| |
| 额外信息:Using index(覆盖索引)、Using where、Using filesort(需优化)、Using temporary(需优化) |
C. 常用SQL优化命令速查
-- 查看执行计划
EXPLAIN SELECT*FROM users WHERE username ='test';
EXPLAIN FORMAT=JSON SELECT*FROM users WHERE username ='test'\G
-- 查看索引使用情况
SHOW INDEX FROM users;
SHOWTABLE STATUS LIKE'users'\G
-- 更新统计信息
ANALYZE TABLE users;
-- 查看表结构
SHOWCREATE TABLE users\G
-- 查看当前正在执行的查询
SHOWFULL PROCESSLIST;
-- 杀死慢查询
KILL QUERY <process_id>;
-- 查看慢查询配置
SHOW VARIABLES LIKE'%slow_query%';
-- 查看索引统计信息
SELECT*FROM mysql.innodb_index_stats WHERE database_name ='your_db'AND table_name ='users';
-- 查看未使用的索引
SELECT*FROM sys.schema_unused_indexes;
-- 查看冗余索引
SELECT*FROM sys.schema_redundant_indexes;
-- 启用profiling
SET profiling =1;
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;