1. MySQL索引优化实战从原理到慢查询解决刚接手一个用户反馈越来越慢的系统时我遇到了这样一个SQLSELECT COUNT(*) FROM task WHERE status2 AND operator_id20839 AND operate_time1371169729 AND operate_time1371174603 AND type2;开发同学的建议简单粗暴给每个查询字段都加上索引。这让我意识到很多工程师对索引的理解还停留在表面。今天我就结合十年来处理的上百个慢查询案例系统梳理MySQL索引优化方法论。1.1 索引的本质与底层原理索引的核心价值在于将随机查询变为顺序查询。就像查字典时你不会从第一页开始逐页查找mysql这个词而是先定位M字母区域再找y、s、q、l。没有索引的数据库查询就相当于要从头遍历整本字典。MySQL采用B树作为索引数据结构这是经过多年验证的最佳选择。来看一个简化版的B树示例[磁盘块1] / | \ [磁盘块2] [磁盘块3] [磁盘块4] 17|35 P1|P2|P3当查询数据项29时加载磁盘块1到内存1次IO内存二分查找确定29在P2指针范围加载P2指向的磁盘块3第2次IO确定29在P2指向的磁盘块8加载磁盘块8找到29第3次IO三层B树可支撑百万级数据查询只需3次IO。而全表扫描需要百万次IO性能差异天壤之别。1.2 索引优化的黄金法则最左前缀原则索引(a,b,c)可以支持a、a,b、a,b,c组合查询但无法支持b,c或单独c查询。范围查询(,,like)会停止匹配如WHERE a1 AND b2 AND c3 AND d4建立(a,b,d,c)索引比(a,b,c,d)更优因为c是范围查询后d无法使用索引。区分度公式count(distinct col)/count(*)。性别字段区分度约0.5%而用户ID接近100%。一般建议join字段区分度10%。索引避坑指南避免在索引列使用函数FROM_UNIXTIME(create_time)2020-01-01会导致索引失效避免隐式类型转换varchar列用数字查询会失效不要过度索引每个额外索引都会降低写性能2. EXPLAIN执行计划深度解析拿到慢查询后我的第一反应总是EXPLAIN SQL。这是读懂MySQL思维的关键工具。重点关注的列列名关键解读优化方向typeALL全表扫描index索引扫描range范围扫描避免ALL至少达到range级别possible_keys可能使用的索引检查是否漏掉了合适索引key实际使用的索引强制使用更好的索引(force index)rows预估检查的行数超过1万就需要警惕ExtraUsing filesort/Using temporary最危险需要优化排序或分组2.1 真实案例从6秒到0.2秒的蜕变遇到一个951行数据查询需要6.22秒的案例EXPLAIN显示扫描361万行SELECT * FROM stage_poi WHERE accurate_result1 AND (sync_status0 OR sync_status2 OR sync_status4);分析发现两个问题accurate_result区分度低-1/0/1三值分布sync_status同样低区分度0和3两种状态通过与业务方沟通了解到这个查询是定时任务每次实际只需处理约1000条数据。于是创建复合索引ALTER TABLE stage_poi ADD INDEX idx_acc_status(accurate_result,sync_status);优化后查询仅需0.2秒提升30倍。这个案例告诉我们了解业务场景比技术本身更重要。3. 高级优化技巧与复杂查询处理3.1 联表查询优化策略处理一个13秒返回10条记录的复杂联表查询时我发现问题出在排序阶段SELECT c.* FROM contact c JOIN contact_branch cb ON c.idcb.contact_id JOIN branch_user bu ON cb.branch_idbu.branch_id JOIN org_emp_info oei ON oei.data_idbu.user_id ORDER BY c.created_time DESC LIMIT 10;优化方案先限制再关联。使用EXISTS改写SELECT c.* FROM contact c WHERE EXISTS ( SELECT 1 FROM contact_branch cb JOIN branch_user bu ON cb.branch_idbu.branch_id JOIN org_emp_info oei ON oei.data_idbu.user_id WHERE c.idcb.contact_id ) ORDER BY c.created_time DESC LIMIT 10;执行时间从13秒降到0.001秒。核心原理是MySQL先通过created_time索引获取10条主键再去关联查询详细信息避免处理77万条记录的排序。3.2 索引失效的隐蔽陷阱隐式类型转换-- user_id是varchar类型 SELECT * FROM users WHERE user_id 10086;这种写法会导致索引失效应该改为SELECT * FROM users WHERE user_id 10086;OR条件优化SELECT * FROM orders WHERE status1 OR amount100;建议改写为SELECT * FROM orders WHERE status1 UNION ALL SELECT * FROM orders WHERE amount100 AND status!1;4. 慢查询治理体系化方案4.1 全链路监控方案建立三层监控体系数据库层开启slow_query_log定期分析慢查询日志应用层通过APM工具监控DAO层耗时业务层关键业务操作添加耗时埋点推荐配置-- 记录超过1秒的查询 SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL log_queries_not_using_indexes ON;4.2 性能优化checklist索引检查所有WHERE条件列是否有合适索引复合索引字段顺序是否符合最左前缀原则索引区分度是否足够高SQL写法检查避免SELECT *只查询必要字段避免使用OR条件改用UNION ALL避免对索引列使用函数或运算分页优化-- 低效写法 SELECT * FROM table LIMIT 1000000, 10; -- 优化写法假设id是主键 SELECT * FROM table WHERE id 1000000 LIMIT 10;4.3 紧急止血方案当线上出现严重慢查询时通过SHOW PROCESSLIST定位问题查询使用KILL QUERY [id]终止问题会话临时增加索引缓解问题通过SQL改写或业务限流控制影响范围曾经处理过一个案例一个错误的全表扫描查询导致从库延迟通过pt-kill工具自动终止长时间运行的查询为优化争取了时间。5. 索引设计进阶实战5.1 覆盖索引优化Extra列出现Using index时说明使用了覆盖索引这是最佳情况-- 建立(status,operator_id)复合索引 EXPLAIN SELECT operator_id FROM task WHERE status1;5.2 索引下推技术MySQL 5.6引入的ICP特性可以在索引遍历时提前过滤数据-- 假设有索引(status,operate_time) SELECT * FROM task WHERE status1 AND operate_time2020-01-01;即使operate_time不在索引最左列ICP也能在存储引擎层提前过滤。5.3 索引合并优化当WHERE条件包含多个独立索引时MySQL可能使用Index Merge-- 假设status和type都有独立索引 EXPLAIN SELECT * FROM task WHERE status1 OR type2;Extra列会显示Using union(idx_status,idx_type)但通常效率不如复合索引。6. 特殊场景优化方案6.1 大数据量分页优化典型问题SQLSELECT * FROM huge_table ORDER BY id LIMIT 1000000, 10;优化方案游标分页记录上一页最后一条记录的IDSELECT * FROM huge_table WHERE id 1000000 ORDER BY id LIMIT 10;延迟关联SELECT t.* FROM huge_table t JOIN (SELECT id FROM huge_table ORDER BY id LIMIT 1000000, 10) tmp ON t.idtmp.id;6.2 JSON字段查询优化对于JSON类型字段MySQL 8.0支持函数索引ALTER TABLE products ADD INDEX idx_price((CAST(data-$.price AS DECIMAL(10,2))));6.3 分布式ID索引优化使用雪花ID等分布式ID时注意时间有序性。错误的索引设计ALTER TABLE orders ADD INDEX idx_user_id(user_id);更好的设计ALTER TABLE orders ADD INDEX idx_user_created(user_id, create_time);7. 性能优化误区与陷阱索引越多越好每个索引都会降低写入速度维护索引需要权衡所有字段都加索引低区分度字段加索引可能适得其反EXISTS一定比IN快当子查询数据量小时IN可能更高效UNION ALL不如UNION确实需要去重时才用UNION内存参数越大越好过大的buffer pool会导致swap反而更慢曾经遇到一个案例某表有20个索引导致INSERT速度从每秒2000条降到200条。通过索引合并和删除冗余索引性能提升5倍。8. 实战问题排查记录案例1定期出现的慢查询现象每天凌晨3点出现慢查询排查发现是备份任务导致IO瓶颈解决调整备份时间增加从库专门用于备份案例2突然的性能下降现象原本很快的查询突然变慢排查统计信息过期导致优化器选错索引解决执行ANALYZE TABLE更新统计信息案例3字段类型不匹配现象varchar字段用数字查询时索引失效排查发现是应用框架自动类型转换导致解决修改框架配置保持类型一致9. 工具链推荐性能分析pt-query-digest慢查询日志分析MySQL Enterprise Monitor全面监控压力测试sysbench基准测试工具tpcc-mysql事务处理测试Schema优化pt-index-usage索引使用情况分析pt-online-schema-change在线DDL变更可视化工具Percona PMM全栈监控平台VividCortex实时性能分析10. 持续优化体系建立性能优化闭环监控实时捕获慢查询分析定位根本原因解决实施优化方案验证确认优化效果沉淀形成知识库在某电商平台实践中这套体系将95%的查询优化到100ms内数据库整体QPS提升3倍。记住优化是持续过程不是一次性任务。 
长河君 · 2026/10/31 11:45:55 · 阅读 1.2k MySQL索引优化实战：从原理到慢查询解决
1. MySQL索引优化实战从原理到慢查询解决刚接手一个用户反馈越来越慢的系统时我遇到了这样一个SQLSELECT COUNT(*) FROM task WHERE status2 AND operator_id20839 AND operate_time1371169729 AND operate_time1371174603 AND type2;开发同学的建议简单粗暴给每个查询字段都加上索引。这让我意识到很多工程师对索引的理解还停留在表面。今天我就结合十年来处理的上百个慢查询案例系统梳理MySQL索引优化方法论。1.1 索引的本质与底层原理索引的核心价值在于将随机查询变为顺序查询。就像查字典时你不会从第一页开始逐页查找mysql这个词而是先定位M字母区域再找y、s、q、l。没有索引的数据库查询就相当于要从头遍历整本字典。MySQL采用B树作为索引数据结构这是经过多年验证的最佳选择。来看一个简化版的B树示例[磁盘块1] / | \ [磁盘块2] [磁盘块3] [磁盘块4] 17|35 P1|P2|P3当查询数据项29时加载磁盘块1到内存1次IO内存二分查找确定29在P2指针范围加载P2指向的磁盘块3第2次IO确定29在P2指向的磁盘块8加载磁盘块8找到29第3次IO三层B树可支撑百万级数据查询只需3次IO。而全表扫描需要百万次IO性能差异天壤之别。1.2 索引优化的黄金法则最左前缀原则索引(a,b,c)可以支持a、a,b、a,b,c组合查询但无法支持b,c或单独c查询。范围查询(,,like)会停止匹配如WHERE a1 AND b2 AND c3 AND d4建立(a,b,d,c)索引比(a,b,c,d)更优因为c是范围查询后d无法使用索引。区分度公式count(distinct col)/count(*)。性别字段区分度约0.5%而用户ID接近100%。一般建议join字段区分度10%。索引避坑指南避免在索引列使用函数FROM_UNIXTIME(create_time)2020-01-01会导致索引失效避免隐式类型转换varchar列用数字查询会失效不要过度索引每个额外索引都会降低写性能2. EXPLAIN执行计划深度解析拿到慢查询后我的第一反应总是EXPLAIN SQL。这是读懂MySQL思维的关键工具。重点关注的列列名关键解读优化方向typeALL全表扫描index索引扫描range范围扫描避免ALL至少达到range级别possible_keys可能使用的索引检查是否漏掉了合适索引key实际使用的索引强制使用更好的索引(force index)rows预估检查的行数超过1万就需要警惕ExtraUsing filesort/Using temporary最危险需要优化排序或分组2.1 真实案例从6秒到0.2秒的蜕变遇到一个951行数据查询需要6.22秒的案例EXPLAIN显示扫描361万行SELECT * FROM stage_poi WHERE accurate_result1 AND (sync_status0 OR sync_status2 OR sync_status4);分析发现两个问题accurate_result区分度低-1/0/1三值分布sync_status同样低区分度0和3两种状态通过与业务方沟通了解到这个查询是定时任务每次实际只需处理约1000条数据。于是创建复合索引ALTER TABLE stage_poi ADD INDEX idx_acc_status(accurate_result,sync_status);优化后查询仅需0.2秒提升30倍。这个案例告诉我们了解业务场景比技术本身更重要。3. 高级优化技巧与复杂查询处理3.1 联表查询优化策略处理一个13秒返回10条记录的复杂联表查询时我发现问题出在排序阶段SELECT c.* FROM contact c JOIN contact_branch cb ON c.idcb.contact_id JOIN branch_user bu ON cb.branch_idbu.branch_id JOIN org_emp_info oei ON oei.data_idbu.user_id ORDER BY c.created_time DESC LIMIT 10;优化方案先限制再关联。使用EXISTS改写SELECT c.* FROM contact c WHERE EXISTS ( SELECT 1 FROM contact_branch cb JOIN branch_user bu ON cb.branch_idbu.branch_id JOIN org_emp_info oei ON oei.data_idbu.user_id WHERE c.idcb.contact_id ) ORDER BY c.created_time DESC LIMIT 10;执行时间从13秒降到0.001秒。核心原理是MySQL先通过created_time索引获取10条主键再去关联查询详细信息避免处理77万条记录的排序。3.2 索引失效的隐蔽陷阱隐式类型转换-- user_id是varchar类型 SELECT * FROM users WHERE user_id 10086;这种写法会导致索引失效应该改为SELECT * FROM users WHERE user_id 10086;OR条件优化SELECT * FROM orders WHERE status1 OR amount100;建议改写为SELECT * FROM orders WHERE status1 UNION ALL SELECT * FROM orders WHERE amount100 AND status!1;4. 慢查询治理体系化方案4.1 全链路监控方案建立三层监控体系数据库层开启slow_query_log定期分析慢查询日志应用层通过APM工具监控DAO层耗时业务层关键业务操作添加耗时埋点推荐配置-- 记录超过1秒的查询 SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL log_queries_not_using_indexes ON;4.2 性能优化checklist索引检查所有WHERE条件列是否有合适索引复合索引字段顺序是否符合最左前缀原则索引区分度是否足够高SQL写法检查避免SELECT *只查询必要字段避免使用OR条件改用UNION ALL避免对索引列使用函数或运算分页优化-- 低效写法 SELECT * FROM table LIMIT 1000000, 10; -- 优化写法假设id是主键 SELECT * FROM table WHERE id 1000000 LIMIT 10;4.3 紧急止血方案当线上出现严重慢查询时通过SHOW PROCESSLIST定位问题查询使用KILL QUERY [id]终止问题会话临时增加索引缓解问题通过SQL改写或业务限流控制影响范围曾经处理过一个案例一个错误的全表扫描查询导致从库延迟通过pt-kill工具自动终止长时间运行的查询为优化争取了时间。5. 索引设计进阶实战5.1 覆盖索引优化Extra列出现Using index时说明使用了覆盖索引这是最佳情况-- 建立(status,operator_id)复合索引 EXPLAIN SELECT operator_id FROM task WHERE status1;5.2 索引下推技术MySQL 5.6引入的ICP特性可以在索引遍历时提前过滤数据-- 假设有索引(status,operate_time) SELECT * FROM task WHERE status1 AND operate_time2020-01-01;即使operate_time不在索引最左列ICP也能在存储引擎层提前过滤。5.3 索引合并优化当WHERE条件包含多个独立索引时MySQL可能使用Index Merge-- 假设status和type都有独立索引 EXPLAIN SELECT * FROM task WHERE status1 OR type2;Extra列会显示Using union(idx_status,idx_type)但通常效率不如复合索引。6. 特殊场景优化方案6.1 大数据量分页优化典型问题SQLSELECT * FROM huge_table ORDER BY id LIMIT 1000000, 10;优化方案游标分页记录上一页最后一条记录的IDSELECT * FROM huge_table WHERE id 1000000 ORDER BY id LIMIT 10;延迟关联SELECT t.* FROM huge_table t JOIN (SELECT id FROM huge_table ORDER BY id LIMIT 1000000, 10) tmp ON t.idtmp.id;6.2 JSON字段查询优化对于JSON类型字段MySQL 8.0支持函数索引ALTER TABLE products ADD INDEX idx_price((CAST(data-$.price AS DECIMAL(10,2))));6.3 分布式ID索引优化使用雪花ID等分布式ID时注意时间有序性。错误的索引设计ALTER TABLE orders ADD INDEX idx_user_id(user_id);更好的设计ALTER TABLE orders ADD INDEX idx_user_created(user_id, create_time);7. 性能优化误区与陷阱索引越多越好每个索引都会降低写入速度维护索引需要权衡所有字段都加索引低区分度字段加索引可能适得其反EXISTS一定比IN快当子查询数据量小时IN可能更高效UNION ALL不如UNION确实需要去重时才用UNION内存参数越大越好过大的buffer pool会导致swap反而更慢曾经遇到一个案例某表有20个索引导致INSERT速度从每秒2000条降到200条。通过索引合并和删除冗余索引性能提升5倍。8. 实战问题排查记录案例1定期出现的慢查询现象每天凌晨3点出现慢查询排查发现是备份任务导致IO瓶颈解决调整备份时间增加从库专门用于备份案例2突然的性能下降现象原本很快的查询突然变慢排查统计信息过期导致优化器选错索引解决执行ANALYZE TABLE更新统计信息案例3字段类型不匹配现象varchar字段用数字查询时索引失效排查发现是应用框架自动类型转换导致解决修改框架配置保持类型一致9. 工具链推荐性能分析pt-query-digest慢查询日志分析MySQL Enterprise Monitor全面监控压力测试sysbench基准测试工具tpcc-mysql事务处理测试Schema优化pt-index-usage索引使用情况分析pt-online-schema-change在线DDL变更可视化工具Percona PMM全栈监控平台VividCortex实时性能分析10. 持续优化体系建立性能优化闭环监控实时捕获慢查询分析定位根本原因解决实施优化方案验证确认优化效果沉淀形成知识库在某电商平台实践中这套体系将95%的查询优化到100ms内数据库整体QPS提升3倍。记住优化是持续过程不是一次性任务。