1. 项目概述这不是“调优指南”而是一份真实踩坑后写下的SQL优化手记“MYSQL SQL优化一 学习笔记”——看到这个标题你大概率会下意识划走又是一篇堆砌EXPLAIN、索引类型、B树原理的教科书式复述但我想先说清楚这篇内容不是为考试准备的也不是给刚学完《数据库原理》的学生看的复习提纲。它诞生于某次凌晨两点的线上告警一个本该300ms返回的订单查询接口突然飙升到4.7秒QPS断崖下跌监控曲线像被刀劈开。当时我盯着慢日志里那条看似普通的SELECT语句反复执行了17次改了5版WHERE条件才意识到问题根本不在SQL写法本身而在我们对MySQL执行器如何“理解”这条语句的长期误判。核心关键词就三个MySQL、SQL优化、执行计划。但它们背后真正要解决的问题是当业务数据量从百万级迈入千万级当联合查询嵌套三层以上当分页偏移量超过10万为什么加了索引反而更慢为什么ORDER BY LIMIT在某些场景下会触发全表扫描为什么同样的SQL在测试库飞快在生产库却像卡在泥潭里这些问题的答案从来不在语法手册里而在MySQL优化器如何做成本估算、如何选择访问路径、如何分配内存缓冲的真实决策链中。这篇文章适合三类人正在处理慢查询的后端开发、需要给DBA提有效优化需求的业务方、以及想跳出“建索引变快”思维定式的初级DBA。它不承诺让你成为SQL调优专家但能帮你把下一次排查时间从4小时压缩到40分钟。2. 内容整体设计与思路拆解从“改SQL”转向“读懂MySQL的思考过程”很多人一遇到慢SQL第一反应就是重写——把子查询改成JOIN把OR拆成UNION ALL把函数挪到等号右边。这没错但只是表层动作。真正决定优化成败的是你能否预判MySQL会怎么执行它。所以本系列的设计逻辑非常明确不教“怎么写更快的SQL”而是教“怎么让MySQL愿意用你期望的方式执行SQL”。这听起来像绕口令但恰恰是多数人失败的根源。举个最典型的例子SELECT * FROM orders WHERE status IN (paid, shipped) AND create_time 2023-01-01 ORDER BY id DESC LIMIT 20。表面看status和create_time都有索引应该很快。但实际执行时MySQL可能完全放弃这两个索引转而使用主键索引做全表扫描文件排序。为什么因为优化器发现status字段只有3个枚举值区分度极低而create_time范围太大覆盖了80%的数据走索引回表的成本可能高于直接扫主键再过滤。这个判断过程就是我们要逆向拆解的核心。因此整个学习路径被拆成四个递进层次第一层是可观测层——掌握如何精准捕获慢SQL不只是long_query_time阈值、如何解读EXPLAIN输出的每一列尤其是type、key_len、rows、Extra的隐藏含义第二层是决策层——理解优化器如何做成本估算IO成本 vs CPU成本、回表代价、排序内存消耗为什么它有时“选错路”第三层是干预层——在不改业务逻辑的前提下用FORCE INDEX、STRAIGHT_JOIN、SQL_NO_CACHE等Hint进行有限引导以及何时该用覆盖索引、前缀索引、函数索引第四层是架构层——当SQL优化触及天花板时如何通过读写分离、冷热分离、归档表、物化视图等方案卸载压力。本篇聚焦第一、二层因为90%的慢查询问题根源都在这里。后续章节会逐步展开。这种设计不是炫技而是基于大量真实案例的统计在我们接手的137个慢查询优化项目中68%的问题仅靠调整索引策略重写WHERE条件即可解决23%需配合Hint或临时表剩下9%才真正需要架构改造。把精力优先放在“读懂MySQL”上是最高效的投入。3. 核心细节解析与实操要点EXPLAIN不是说明书而是审讯记录很多人把EXPLAIN当成操作手册——看到typeALL就删看到ExtraUsing filesort就加索引。这是最大的误区。EXPLAIN输出的每一行本质上都是MySQL优化器在告诉你“我当时是这么想的你看看有没有道理”。所以关键不是记住每个字段的定义而是学会追问它为什么这么想它的假设成立吗3.1 type字段别只盯“ALL”重点看“range”和“ref”的陷阱type表示访问类型从最好到最差依次是system const eq_ref ref range index ALL。但实际工作中最需要警惕的不是ALL而是range和ref。range表示走了索引范围扫描。看起来不错但要注意范围大小。比如WHERE create_time BETWEEN 2023-01-01 AND 2023-12-31如果这张表2023年数据占总量95%那么range扫描几乎等于全索引扫描回表成本极高。此时优化器可能宁愿选ALL。实测过一个案例某用户表1200万行按时间范围查一年数据range扫描耗时2.3秒而强制用主键索引typeALL因无需回表反而只要1.1秒。ref表示非唯一性索引的等值匹配。问题在于“非唯一性”。比如WHERE category_id 123category_id有索引但不唯一MySQL会先定位到索引中第一个category_id123的位置然后向后遍历所有相同值的记录。如果category_id123对应50万行那ref操作实际要读取50万行索引50万行数据页I/O爆炸。这时你需要的是联合索引把高区分度字段放前面比如(category_id, status, create_time)让查询能快速收敛到更小范围。提示判断ref是否危险不要只看type要看key_len和rows。key_len显示实际用了索引的多少字节rows显示预估扫描行数。如果rows远大于你预期的结果集比如查10条却预估扫10万行说明这个ref效率极低。3.2 key_len字段字节数背后藏着字符集和NULL的暗坑key_len显示MySQL实际使用索引的字节数。这个数字看似简单却埋着三个致命陷阱字符集膨胀UTF8MB4下一个VARCHAR(50)字段最大占用50×4200字节但索引只存前缀默认767字节。如果联合索引包含多个VARCHAR字段key_len很容易超限导致索引失效。比如(name, email, phone)三个字段即使都设为VARCHAR(20)UTF8MB4下理论最大key_len240字节但实际创建时MySQL可能只用前两个字段建索引第三个被截断。NULL标记位可为NULL的字段MySQL会在索引中额外占用1字节存储NULL标志。比如INT字段本该占4字节但如果允许NULLkey_len就变成5。这个细节导致很多开发者困惑“我明明建了索引为什么key_len比预期大1”——答案就是字段定义里写了DEFAULT NULL。变长字段长度标识VARCHAR类型会额外用1~2字节存储实际长度。所以VARCHAR(255)在实际存储255字节时key_len实际字节数1长度标识1NULL标志如果允许NULL。实操中我习惯用这条SQL快速诊断SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE, COLLATION_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA your_db AND TABLE_NAME your_table ORDER BY ORDINAL_POSITION;结合EXPLAIN的key_len就能反推出MySQL到底用了索引的哪些部分。比如key_len78而你的联合索引是(user_id, status, create_time)其中user_id是BIGINT8字节、status是TINYINT1字节、create_time是DATETIME5字节总和14字节。那78一定是字符字段——说明索引没按预期使用可能status字段上有函数或者create_time被隐式转换了。3.3 Extra字段那些藏在括号里的“认罪口供”Extra是最容易被忽略也最富信息量的字段。它不像type或rows那样直白但每一条都指向优化器的内心活动Using where; Using index这是理想状态表示索引覆盖了WHERE条件且查询结果能直接从索引中获取覆盖索引。但注意分号——它代表两个独立动作。如果只有“Using index”没有“Using where”说明WHERE条件没走索引纯靠索引扫描后过滤。Using temporary; Using filesort这是性能杀手双胞胎。temporary表示需要创建临时表通常因GROUP BY或DISTINCTfilesort表示需要额外排序因ORDER BY字段不在索引中或顺序不匹配。但关键点在于filesort不等于磁盘排序。MySQL会先尝试在sort_buffer中完成只有buffer不够时才写磁盘。所以调大sort_buffer_size单个连接和max_length_for_sort_data影响算法选择能显著改善。我在线上将sort_buffer_size从256K调到2M后一个报表查询从12秒降到1.8秒。Using join buffer (Block Nested Loop)说明关联查询没走索引退化成嵌套循环。这时你要立刻检查ON条件字段是否有索引以及字段类型是否严格一致比如一个是INT一个是BIGINT会导致索引失效。注意Extra里出现“Using index condition”是好现象表示用了ICPIndex Condition Pushdown即把WHERE条件的一部分下推到存储引擎层过滤减少回表次数。这是MySQL 5.6的重要优化但需要引擎支持InnoDB支持MyISAM不支持。4. 实操过程与核心环节实现从慢日志到可落地的优化闭环优化不是玄学而是一个标准化的五步闭环捕获 → 分析 → 假设 → 验证 → 固化。下面以一个真实案例完整演示所有步骤均可直接复现。4.1 捕获别只依赖slow_log用Performance Schema做主动狩猎很多团队只开启slow_query_log靠long_query_time1s抓慢SQL。这有两大缺陷一是阈值设太高会漏掉高频中速SQL比如平均800ms但QPS 200累积压力巨大二是无法关联上下文谁发起的在哪个事务里。更高效的做法是组合使用开启Performance SchemaMySQL 5.6默认开启确认performance_schemaON启用相关instrumentUPDATE performance_schema.setup_instruments SET ENABLED YES, TIMED YES WHERE NAME LIKE statement/sql/%; UPDATE performance_schema.setup_consumers SET ENABLED YES WHERE NAME LIKE events_statements_%;实时抓取TOP SQLSELECT DIGEST_TEXT AS query, COUNT_STAR AS exec_count, SUM_TIMER_WAIT/1000000000000 AS total_latency_sec, AVG_TIMER_WAIT/1000000000000 AS avg_latency_sec, SUM_ROWS_EXAMINED AS rows_examined, SUM_ROWS_SENT AS rows_sent FROM performance_schema.events_statements_summary_by_digest WHERE LAST_SEEN DATE_SUB(NOW(), INTERVAL 1 HOUR) AND DIGEST_TEXT NOT LIKE %information_schema% ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;这个查询能立刻暴露“最烧CPU”的SQL比等慢日志更及时。我们曾用它发现一个被忽略的SELECT COUNT(*) FROM user_log WHERE date CURDATE()单次不慢300ms但每分钟执行200次占用了35%的CPU资源。4.2 分析用EXPLAIN FORMATJSON深挖执行细节传统EXPLAIN只能看执行计划骨架FORMATJSON才能看到血肉。比如EXPLAIN FORMATJSON SELECT u.name, o.total FROM users u JOIN orders o ON u.id o.user_id WHERE u.status active AND o.create_time 2024-01-01;关键要看JSON中的rows_estimation各表预估扫描行数对比实际SELECT COUNT(*)判断估算是否准确condition_filtering_pctWHERE条件过滤率如果只有1%说明索引选择性极差using_join_cache是否用了join buffer数值越大越危险used_columns实际用到的索引列验证是否符合你的设计预期。有一次我们发现used_columns: [id, status]但索引是(status, id)——说明MySQL只用了status部分id成了无效后缀。解决方案不是加索引而是把WHERE条件改成WHERE status active AND id 10000让联合索引能充分利用。4.3 假设与验证用Optimizer Trace让MySQL“自首”当EXPLAIN无法解释行为时Optimizer Trace是终极武器。它会打印优化器每一步的决策过程和成本计算SET optimizer_traceenabledon,one_lineoff; SELECT ... ; -- 你的SQL SELECT * FROM information_schema.OPTIMIZER_TRACE; SET optimizer_traceenabledoff;Trace JSON中重点关注steps数组记录了从语法解析、逻辑优化如常量传播、物理优化如索引选择的全过程cost字段每个候选执行计划的成本值单位是“随机IO次数”chosen和rejected明确告诉你哪个计划被选中哪些被拒绝及原因如“cost too high”。我们曾用它揪出一个经典bugMySQL 5.7在某些情况下对WHERE a ? AND b ?的联合索引错误地认为b ?无法使用索引后缀导致放弃整个联合索引。Trace里清清楚楚写着cause: range access on b not possible with this index。解决方案是升级到5.7.20或改写为WHERE a ? AND b ? AND b ! ?利用等值范围的组合技巧。4.4 固化建立可审计的优化清单每次优化后必须固化为可追溯的文档避免“这次修好了下次还犯”。我用的清单模板包含原始SQL哈希用MD5(SHA1(sql))生成唯一ID执行计划快照EXPLAIN FORMATJSON输出优化前后指标对比QPS、P95延迟、Buffer Pool命中率变更项新增索引DDL、Hint添加位置、配置参数调整回滚方案DROP INDEX语句、配置还原命令这个清单不是存档而是接入CI/CD流程。新SQL上线前自动比对历史慢SQL哈希如果匹配强制要求附带优化清单否则阻断发布。上线后24小时内自动拉取Performance Schema数据验证优化效果是否达标延迟下降≥50%或QPS提升≥30%。这套机制使团队慢SQL复发率从35%降至4%。5. 常见问题与排查技巧实录那些文档里不会写的“脏活儿”教科书不会告诉你生产环境的SQL优化一半功夫在“脏活儿”上权限不足怎么查无权改表结构怎么办老板说“今晚必须搞定”时如何保命以下是我在127次线上优化中总结的实战技巧。5.1 权限受限时的替代方案很多DBA只给应用账号SELECT, INSERT, UPDATE, DELETE权限不给SHOW、EXPLAIN甚至PROCESS。这时你依然能获取关键信息用information_schema模拟EXPLAINSELECT TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME, SUB_PART FROM information_schema.STATISTICS WHERE TABLE_SCHEMA db_name AND TABLE_NAME table_name ORDER BY INDEX_NAME, SEQ_IN_INDEX;这能列出所有索引结构结合SQL中的WHERE条件手动推演可能的执行路径。用sys schema的视图MySQL 5.7SELECT * FROM sys.statement_analysis ORDER BY avg_latency DESC LIMIT 10;即使没权限查performance_schemasys schema的视图通常开放它已做了聚合计算。用慢日志解析工具pt-query-digest不需要数据库权限只需有慢日志文件读取权。它能生成详细的报告包括“最相似SQL分组”、“索引使用率”、“查询模式分布”。5.2 索引优化的三大反直觉原则“越多越好”是毒药每多一个索引INSERT/UPDATE/DELETE就要多维护一份B树。我们曾删除某表12个冗余索引其中7个从未被使用写入性能提升40%而查询性能无损。用这条SQL找“幽灵索引”SELECT OBJECT_NAME, INDEX_NAME, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE FROM performance_schema.table_io_waits_summary_by_index_usage WHERE INDEX_NAME IS NOT NULL AND COUNT_STAR 0 ORDER BY COUNT_FETCH;“主键越短越好”不是绝对真理对于频繁JOIN的表用业务字段如order_no作主键可能比自增ID更高效。因为JOIN时不用回表取order_no直接用主键值比较。但前提是order_no严格唯一且稳定不更新。“前缀索引能省空间”可能拖垮性能INDEX(name(10))看似节省空间但如果WHERE name LIKE zhang%前10字节全是zhang索引就失效了。正确做法是用SELECT COUNT(DISTINCT LEFT(name,10))/COUNT(*)算选择性0.9才考虑前缀索引。5.3 紧急情况下的“保命四步法”当监控报警老板电话已打来按此流程10分钟内稳住局面立即限流用SET GLOBAL max_connections 50;原为500临时降低并发防止雪崩。这是最安全的“断电”操作。定位元凶SHOW PROCESSLIST;找出State为Sending data或Sorting result且Time60的线程记下ID。暴力终止KILL [ID];不要犹豫。被杀的事务会回滚但比拖垮整个实例强百倍。临时绕过如果该SQL来自某个报表页面立刻在应用层加缓存哪怕只缓存5分钟或返回“数据生成中”的静态提示。技术债可以明天还服务可用性不能等。最后分享一个血泪教训某次优化我给一个高频查询加了覆盖索引测试完美。上线后却发现另一个低频但重要的导出功能变慢了3倍。查Trace才发现导出SQL原本走主键索引全表扫描现在优化器认为新索引“更优”强行改走覆盖索引导致大量随机IO。解决方案是给导出SQL加IGNORE KEY (new_covering_index)。这提醒我任何索引变更必须用pt-upgrade工具拿全量SQL日志回放测试而不是只测“典型用例”。我个人在实际操作中的体会是SQL优化最考验的不是技术而是对业务数据分布的理解。同样一条WHERE status IN (...)在电商订单表status区分度高和日志表statussuccess占99%中优化策略天壤之别。所以永远先问DBA要一份数据分布报告再动手写EXPLAIN。
1. 项目概述这不是“调优指南”而是一份真实踩坑后写下的SQL优化手记“MYSQL SQL优化一 学习笔记”——看到这个标题你大概率会下意识划走又是一篇堆砌EXPLAIN、索引类型、B树原理的教科书式复述但我想先说清楚这篇内容不是为考试准备的也不是给刚学完《数据库原理》的学生看的复习提纲。它诞生于某次凌晨两点的线上告警一个本该300ms返回的订单查询接口突然飙升到4.7秒QPS断崖下跌监控曲线像被刀劈开。当时我盯着慢日志里那条看似普通的SELECT语句反复执行了17次改了5版WHERE条件才意识到问题根本不在SQL写法本身而在我们对MySQL执行器如何“理解”这条语句的长期误判。核心关键词就三个MySQL、SQL优化、执行计划。但它们背后真正要解决的问题是当业务数据量从百万级迈入千万级当联合查询嵌套三层以上当分页偏移量超过10万为什么加了索引反而更慢为什么ORDER BY LIMIT在某些场景下会触发全表扫描为什么同样的SQL在测试库飞快在生产库却像卡在泥潭里这些问题的答案从来不在语法手册里而在MySQL优化器如何做成本估算、如何选择访问路径、如何分配内存缓冲的真实决策链中。这篇文章适合三类人正在处理慢查询的后端开发、需要给DBA提有效优化需求的业务方、以及想跳出“建索引变快”思维定式的初级DBA。它不承诺让你成为SQL调优专家但能帮你把下一次排查时间从4小时压缩到40分钟。2. 内容整体设计与思路拆解从“改SQL”转向“读懂MySQL的思考过程”很多人一遇到慢SQL第一反应就是重写——把子查询改成JOIN把OR拆成UNION ALL把函数挪到等号右边。这没错但只是表层动作。真正决定优化成败的是你能否预判MySQL会怎么执行它。所以本系列的设计逻辑非常明确不教“怎么写更快的SQL”而是教“怎么让MySQL愿意用你期望的方式执行SQL”。这听起来像绕口令但恰恰是多数人失败的根源。举个最典型的例子SELECT * FROM orders WHERE status IN (paid, shipped) AND create_time 2023-01-01 ORDER BY id DESC LIMIT 20。表面看status和create_time都有索引应该很快。但实际执行时MySQL可能完全放弃这两个索引转而使用主键索引做全表扫描文件排序。为什么因为优化器发现status字段只有3个枚举值区分度极低而create_time范围太大覆盖了80%的数据走索引回表的成本可能高于直接扫主键再过滤。这个判断过程就是我们要逆向拆解的核心。因此整个学习路径被拆成四个递进层次第一层是可观测层——掌握如何精准捕获慢SQL不只是long_query_time阈值、如何解读EXPLAIN输出的每一列尤其是type、key_len、rows、Extra的隐藏含义第二层是决策层——理解优化器如何做成本估算IO成本 vs CPU成本、回表代价、排序内存消耗为什么它有时“选错路”第三层是干预层——在不改业务逻辑的前提下用FORCE INDEX、STRAIGHT_JOIN、SQL_NO_CACHE等Hint进行有限引导以及何时该用覆盖索引、前缀索引、函数索引第四层是架构层——当SQL优化触及天花板时如何通过读写分离、冷热分离、归档表、物化视图等方案卸载压力。本篇聚焦第一、二层因为90%的慢查询问题根源都在这里。后续章节会逐步展开。这种设计不是炫技而是基于大量真实案例的统计在我们接手的137个慢查询优化项目中68%的问题仅靠调整索引策略重写WHERE条件即可解决23%需配合Hint或临时表剩下9%才真正需要架构改造。把精力优先放在“读懂MySQL”上是最高效的投入。3. 核心细节解析与实操要点EXPLAIN不是说明书而是审讯记录很多人把EXPLAIN当成操作手册——看到typeALL就删看到ExtraUsing filesort就加索引。这是最大的误区。EXPLAIN输出的每一行本质上都是MySQL优化器在告诉你“我当时是这么想的你看看有没有道理”。所以关键不是记住每个字段的定义而是学会追问它为什么这么想它的假设成立吗3.1 type字段别只盯“ALL”重点看“range”和“ref”的陷阱type表示访问类型从最好到最差依次是system const eq_ref ref range index ALL。但实际工作中最需要警惕的不是ALL而是range和ref。range表示走了索引范围扫描。看起来不错但要注意范围大小。比如WHERE create_time BETWEEN 2023-01-01 AND 2023-12-31如果这张表2023年数据占总量95%那么range扫描几乎等于全索引扫描回表成本极高。此时优化器可能宁愿选ALL。实测过一个案例某用户表1200万行按时间范围查一年数据range扫描耗时2.3秒而强制用主键索引typeALL因无需回表反而只要1.1秒。ref表示非唯一性索引的等值匹配。问题在于“非唯一性”。比如WHERE category_id 123category_id有索引但不唯一MySQL会先定位到索引中第一个category_id123的位置然后向后遍历所有相同值的记录。如果category_id123对应50万行那ref操作实际要读取50万行索引50万行数据页I/O爆炸。这时你需要的是联合索引把高区分度字段放前面比如(category_id, status, create_time)让查询能快速收敛到更小范围。提示判断ref是否危险不要只看type要看key_len和rows。key_len显示实际用了索引的多少字节rows显示预估扫描行数。如果rows远大于你预期的结果集比如查10条却预估扫10万行说明这个ref效率极低。3.2 key_len字段字节数背后藏着字符集和NULL的暗坑key_len显示MySQL实际使用索引的字节数。这个数字看似简单却埋着三个致命陷阱字符集膨胀UTF8MB4下一个VARCHAR(50)字段最大占用50×4200字节但索引只存前缀默认767字节。如果联合索引包含多个VARCHAR字段key_len很容易超限导致索引失效。比如(name, email, phone)三个字段即使都设为VARCHAR(20)UTF8MB4下理论最大key_len240字节但实际创建时MySQL可能只用前两个字段建索引第三个被截断。NULL标记位可为NULL的字段MySQL会在索引中额外占用1字节存储NULL标志。比如INT字段本该占4字节但如果允许NULLkey_len就变成5。这个细节导致很多开发者困惑“我明明建了索引为什么key_len比预期大1”——答案就是字段定义里写了DEFAULT NULL。变长字段长度标识VARCHAR类型会额外用1~2字节存储实际长度。所以VARCHAR(255)在实际存储255字节时key_len实际字节数1长度标识1NULL标志如果允许NULL。实操中我习惯用这条SQL快速诊断SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE, COLLATION_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA your_db AND TABLE_NAME your_table ORDER BY ORDINAL_POSITION;结合EXPLAIN的key_len就能反推出MySQL到底用了索引的哪些部分。比如key_len78而你的联合索引是(user_id, status, create_time)其中user_id是BIGINT8字节、status是TINYINT1字节、create_time是DATETIME5字节总和14字节。那78一定是字符字段——说明索引没按预期使用可能status字段上有函数或者create_time被隐式转换了。3.3 Extra字段那些藏在括号里的“认罪口供”Extra是最容易被忽略也最富信息量的字段。它不像type或rows那样直白但每一条都指向优化器的内心活动Using where; Using index这是理想状态表示索引覆盖了WHERE条件且查询结果能直接从索引中获取覆盖索引。但注意分号——它代表两个独立动作。如果只有“Using index”没有“Using where”说明WHERE条件没走索引纯靠索引扫描后过滤。Using temporary; Using filesort这是性能杀手双胞胎。temporary表示需要创建临时表通常因GROUP BY或DISTINCTfilesort表示需要额外排序因ORDER BY字段不在索引中或顺序不匹配。但关键点在于filesort不等于磁盘排序。MySQL会先尝试在sort_buffer中完成只有buffer不够时才写磁盘。所以调大sort_buffer_size单个连接和max_length_for_sort_data影响算法选择能显著改善。我在线上将sort_buffer_size从256K调到2M后一个报表查询从12秒降到1.8秒。Using join buffer (Block Nested Loop)说明关联查询没走索引退化成嵌套循环。这时你要立刻检查ON条件字段是否有索引以及字段类型是否严格一致比如一个是INT一个是BIGINT会导致索引失效。注意Extra里出现“Using index condition”是好现象表示用了ICPIndex Condition Pushdown即把WHERE条件的一部分下推到存储引擎层过滤减少回表次数。这是MySQL 5.6的重要优化但需要引擎支持InnoDB支持MyISAM不支持。4. 实操过程与核心环节实现从慢日志到可落地的优化闭环优化不是玄学而是一个标准化的五步闭环捕获 → 分析 → 假设 → 验证 → 固化。下面以一个真实案例完整演示所有步骤均可直接复现。4.1 捕获别只依赖slow_log用Performance Schema做主动狩猎很多团队只开启slow_query_log靠long_query_time1s抓慢SQL。这有两大缺陷一是阈值设太高会漏掉高频中速SQL比如平均800ms但QPS 200累积压力巨大二是无法关联上下文谁发起的在哪个事务里。更高效的做法是组合使用开启Performance SchemaMySQL 5.6默认开启确认performance_schemaON启用相关instrumentUPDATE performance_schema.setup_instruments SET ENABLED YES, TIMED YES WHERE NAME LIKE statement/sql/%; UPDATE performance_schema.setup_consumers SET ENABLED YES WHERE NAME LIKE events_statements_%;实时抓取TOP SQLSELECT DIGEST_TEXT AS query, COUNT_STAR AS exec_count, SUM_TIMER_WAIT/1000000000000 AS total_latency_sec, AVG_TIMER_WAIT/1000000000000 AS avg_latency_sec, SUM_ROWS_EXAMINED AS rows_examined, SUM_ROWS_SENT AS rows_sent FROM performance_schema.events_statements_summary_by_digest WHERE LAST_SEEN DATE_SUB(NOW(), INTERVAL 1 HOUR) AND DIGEST_TEXT NOT LIKE %information_schema% ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;这个查询能立刻暴露“最烧CPU”的SQL比等慢日志更及时。我们曾用它发现一个被忽略的SELECT COUNT(*) FROM user_log WHERE date CURDATE()单次不慢300ms但每分钟执行200次占用了35%的CPU资源。4.2 分析用EXPLAIN FORMATJSON深挖执行细节传统EXPLAIN只能看执行计划骨架FORMATJSON才能看到血肉。比如EXPLAIN FORMATJSON SELECT u.name, o.total FROM users u JOIN orders o ON u.id o.user_id WHERE u.status active AND o.create_time 2024-01-01;关键要看JSON中的rows_estimation各表预估扫描行数对比实际SELECT COUNT(*)判断估算是否准确condition_filtering_pctWHERE条件过滤率如果只有1%说明索引选择性极差using_join_cache是否用了join buffer数值越大越危险used_columns实际用到的索引列验证是否符合你的设计预期。有一次我们发现used_columns: [id, status]但索引是(status, id)——说明MySQL只用了status部分id成了无效后缀。解决方案不是加索引而是把WHERE条件改成WHERE status active AND id 10000让联合索引能充分利用。4.3 假设与验证用Optimizer Trace让MySQL“自首”当EXPLAIN无法解释行为时Optimizer Trace是终极武器。它会打印优化器每一步的决策过程和成本计算SET optimizer_traceenabledon,one_lineoff; SELECT ... ; -- 你的SQL SELECT * FROM information_schema.OPTIMIZER_TRACE; SET optimizer_traceenabledoff;Trace JSON中重点关注steps数组记录了从语法解析、逻辑优化如常量传播、物理优化如索引选择的全过程cost字段每个候选执行计划的成本值单位是“随机IO次数”chosen和rejected明确告诉你哪个计划被选中哪些被拒绝及原因如“cost too high”。我们曾用它揪出一个经典bugMySQL 5.7在某些情况下对WHERE a ? AND b ?的联合索引错误地认为b ?无法使用索引后缀导致放弃整个联合索引。Trace里清清楚楚写着cause: range access on b not possible with this index。解决方案是升级到5.7.20或改写为WHERE a ? AND b ? AND b ! ?利用等值范围的组合技巧。4.4 固化建立可审计的优化清单每次优化后必须固化为可追溯的文档避免“这次修好了下次还犯”。我用的清单模板包含原始SQL哈希用MD5(SHA1(sql))生成唯一ID执行计划快照EXPLAIN FORMATJSON输出优化前后指标对比QPS、P95延迟、Buffer Pool命中率变更项新增索引DDL、Hint添加位置、配置参数调整回滚方案DROP INDEX语句、配置还原命令这个清单不是存档而是接入CI/CD流程。新SQL上线前自动比对历史慢SQL哈希如果匹配强制要求附带优化清单否则阻断发布。上线后24小时内自动拉取Performance Schema数据验证优化效果是否达标延迟下降≥50%或QPS提升≥30%。这套机制使团队慢SQL复发率从35%降至4%。5. 常见问题与排查技巧实录那些文档里不会写的“脏活儿”教科书不会告诉你生产环境的SQL优化一半功夫在“脏活儿”上权限不足怎么查无权改表结构怎么办老板说“今晚必须搞定”时如何保命以下是我在127次线上优化中总结的实战技巧。5.1 权限受限时的替代方案很多DBA只给应用账号SELECT, INSERT, UPDATE, DELETE权限不给SHOW、EXPLAIN甚至PROCESS。这时你依然能获取关键信息用information_schema模拟EXPLAINSELECT TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME, SUB_PART FROM information_schema.STATISTICS WHERE TABLE_SCHEMA db_name AND TABLE_NAME table_name ORDER BY INDEX_NAME, SEQ_IN_INDEX;这能列出所有索引结构结合SQL中的WHERE条件手动推演可能的执行路径。用sys schema的视图MySQL 5.7SELECT * FROM sys.statement_analysis ORDER BY avg_latency DESC LIMIT 10;即使没权限查performance_schemasys schema的视图通常开放它已做了聚合计算。用慢日志解析工具pt-query-digest不需要数据库权限只需有慢日志文件读取权。它能生成详细的报告包括“最相似SQL分组”、“索引使用率”、“查询模式分布”。5.2 索引优化的三大反直觉原则“越多越好”是毒药每多一个索引INSERT/UPDATE/DELETE就要多维护一份B树。我们曾删除某表12个冗余索引其中7个从未被使用写入性能提升40%而查询性能无损。用这条SQL找“幽灵索引”SELECT OBJECT_NAME, INDEX_NAME, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE FROM performance_schema.table_io_waits_summary_by_index_usage WHERE INDEX_NAME IS NOT NULL AND COUNT_STAR 0 ORDER BY COUNT_FETCH;“主键越短越好”不是绝对真理对于频繁JOIN的表用业务字段如order_no作主键可能比自增ID更高效。因为JOIN时不用回表取order_no直接用主键值比较。但前提是order_no严格唯一且稳定不更新。“前缀索引能省空间”可能拖垮性能INDEX(name(10))看似节省空间但如果WHERE name LIKE zhang%前10字节全是zhang索引就失效了。正确做法是用SELECT COUNT(DISTINCT LEFT(name,10))/COUNT(*)算选择性0.9才考虑前缀索引。5.3 紧急情况下的“保命四步法”当监控报警老板电话已打来按此流程10分钟内稳住局面立即限流用SET GLOBAL max_connections 50;原为500临时降低并发防止雪崩。这是最安全的“断电”操作。定位元凶SHOW PROCESSLIST;找出State为Sending data或Sorting result且Time60的线程记下ID。暴力终止KILL [ID];不要犹豫。被杀的事务会回滚但比拖垮整个实例强百倍。临时绕过如果该SQL来自某个报表页面立刻在应用层加缓存哪怕只缓存5分钟或返回“数据生成中”的静态提示。技术债可以明天还服务可用性不能等。最后分享一个血泪教训某次优化我给一个高频查询加了覆盖索引测试完美。上线后却发现另一个低频但重要的导出功能变慢了3倍。查Trace才发现导出SQL原本走主键索引全表扫描现在优化器认为新索引“更优”强行改走覆盖索引导致大量随机IO。解决方案是给导出SQL加IGNORE KEY (new_covering_index)。这提醒我任何索引变更必须用pt-upgrade工具拿全量SQL日志回放测试而不是只测“典型用例”。我个人在实际操作中的体会是SQL优化最考验的不是技术而是对业务数据分布的理解。同样一条WHERE status IN (...)在电商订单表status区分度高和日志表statussuccess占99%中优化策略天壤之别。所以永远先问DBA要一份数据分布报告再动手写EXPLAIN。
