MySQL 调优指南：从慢如蜗牛到快如闪电

MySQL 调优指南从慢如蜗牛到快如闪电当你的查询慢到可以去泡杯咖啡回来还没结果时是时候看看这篇文档了。别担心我们都是从这个阶段过来的——那个以为自己写出了 SQL 之王的阶段。 目录慢查询你的数据库在摸鱼索引不是加了就有用EXPLAINSQL 的体检报告锁问题当数据库变成停车场配置调优默认配置是个坑实战案例集锦1. 慢查询你的数据库在摸鱼 问题现场-- 这个查询跑了 30 秒SELECT*FROMordersWHEREuser_id123;30 秒这时候用户已经刷新了 3 次页面怀疑网速有问题准备去竞争对手网站了 问题原因1.1 全表扫描Table Scan-- 没有索引的查询SELECT*FROMusersWHEREemailuserexample.com;数据库内心戏“好吧我把这 100 万行用户记录从头到尾看一遍找找看哪个邮箱匹配…”这就是全表扫描相当于让你在没有目录的 1000 页书中找一个句子。1.2 SELECT * 的陷阱SELECT*FROMorders;-- ❌ 别这么干问题读取不必要的列浪费 I/O增加网络传输开销可能导致索引失效覆盖索引用不上1.3 隐式类型转换-- user_id 是 VARCHAR但你用了数字SELECT*FROMusersWHEREuser_id123;-- ❌MySQL“这个 123 是什么字符串数字算了我先把所有 user_id 转成数字再比较…”结果索引失效全表扫描。️ 排查过程第一步开启慢查询日志-- 查看慢查询配置SHOWVARIABLESLIKEslow_query%;-- 开启慢查询日志SETGLOBALslow_query_logON;SETGLOBALlong_query_time1;-- 超过 1 秒的查询记录下来日志文件位置/var/log/mysql/slow-query.log第二步分析慢查询日志使用mysqldumpslow工具# 查看最慢的 10 条查询mysqldumpslow-st-t10/var/log/mysql/slow-query.log# 按平均执行时间排序mysqldumpslow-sat-t10/var/log/mysql/slow-query.log或者使用pt-query-digest更强大pt-query-digest /var/log/mysql/slow-query.log输出示例# Profile # Rank Query ID Response time Calls R/Call V/M Item # # 1 0x9A1B2C3D4E5F6A7B 45.2341 s 12342 0.003665 0.10 SELECT users?第三步定位具体查询找到慢查询后手动测试并分析-- 复制慢查询加上 EXPLAINEXPLAINSELECT*FROMordersWHEREuser_id123; 解决方法1. 添加合适的索引-- 为 user_id 创建索引CREATEINDEXidx_user_idONorders(user_id);-- 组合索引注意顺序CREATEINDEXidx_user_status_dateONorders(user_id,status,created_at);索引设计原则✅ 经常作为 WHERE 条件的列✅ JOIN 关联的列✅ ORDER BY / GROUP BY 的列❌ 频繁更新的列更新成本高❌ 区分度低的列如性别、状态2. 避免SELECT *-- ✅ 只查询需要的列SELECTid,user_id,amount,created_atFROMordersWHEREuser_id123;3. 修复类型转换问题-- ✅ 使用正确的类型SELECT*FROMusersWHEREuser_id123;-- 字符串用引号2. 索引不是加了就有用 常见误区开发者“我给所有字段都加了索引应该很快了吧”MySQL“你以为索引是免费的午餐每次 INSERT/UPDATE/DELETE 我都要更新这些索引累死我了…” 索引失效的 10 种情况情况示例为什么失效1. 使用函数WHERE YEAR(created_at) 2024函数破坏了索引树的有序性2. LIKE 前缀通配符WHERE name LIKE %张%无法使用索引范围扫描3. OR 连接无索引字段WHERE indexed_col 1 OR non_indexed 2优化器放弃索引4. 联合索引不满足最左前缀索引(a,b,c)查询WHERE b 1跳过了最左边的 a5. 隐式转换WHERE varchar_col 123类型转换导致无法用索引6. 不等于操作WHERE status ! 1范围太分散7. IS NULL可能WHERE col IS NULL取决于存储引擎和版本8. 负向查询WHERE NOT IN (...)优化器选择全表扫描9. 数据类型不匹配WHERE int_col 123隐式转换10. 小表全表扫描更快表只有 100 行优化器直接扫描比走索引快️ 索引优化实战案例联合索引的最左前缀原则-- 创建联合索引CREATEINDEXidx_abcONusers(age,city,name);-- ✅ 能用索引WHEREage25;-- 用到 ageWHEREage25ANDcity北京;-- 用到 age, cityWHEREage25ANDcity北京ANDname张三;-- 全用上-- ❌ 不能用索引或部分用WHEREcity北京;-- 跳过了 ageWHEREname张三;-- 跳过了 age, cityWHEREage25ANDname张三;-- city 断了记忆口诀联合索引就像爬山必须从第一阶梯开始不能跳级案例LIKE 优化-- ❌ 索引失效SELECT*FROMproductsWHEREnameLIKE%手机%;-- ✅ 索引生效前缀匹配SELECT*FROMproductsWHEREnameLIKEiPhone%;-- 替代方案全文索引ALTERTABLEproductsADDFULLTEXTINDEXft_name(name);SELECT*FROMproductsWHEREMATCH(name)AGAINST(手机);案例覆盖索引Covering Index-- 创建索引CREATEINDEXidx_user_dateONorders(user_id,created_at,amount);-- ✅ 这个查询只需要读索引不需要回表SELECTuser_id,created_at,amountFROMordersWHEREuser_id123;EXPLAIN 显示Extra: Using index完美3. EXPLAINSQL 的体检报告 读懂 EXPLAIN 输出EXPLAINSELECT*FROMorders oJOINusers uONo.user_idu.idWHEREo.status1;输出解读列说明理想值id查询序列号—select_type查询类型SIMPLE简单查询table访问的表—type访问类型ref range index ALLpossible_keys可能使用的索引—key实际使用的索引不为空key_len使用的索引长度越长越好联合索引ref索引比较的列—rows预估扫描行数越少越好Extra额外信息Using index覆盖索引 type 字段详解从好到坏-- 1. const主键/唯一索引等值查询最好EXPLAINSELECT*FROMusersWHEREid1;-- type: const-- 2. eq_refJOIN 时使用主键/唯一索引EXPLAINSELECT*FROMorders oJOINusers uONo.user_idu.id;-- type: eq_refu 表-- 3. ref非唯一索引等值查询EXPLAINSELECT*FROMordersWHEREuser_id123;-- type: ref-- 4. range范围查询EXPLAINSELECT*FROMordersWHEREcreated_at2024-01-01;-- type: range-- 5. index索引全扫描EXPLAINSELECTuser_idFROMorders;-- type: index-- 6. ALL全表扫描最差需要优化EXPLAINSELECT*FROMordersWHEREamount100;-- type: ALL Extra 字段解读Extra 值含义好坏Using index覆盖索引不需要回表✅ 完美Using whereWHERE 过滤✅ 正常Using index condition索引条件下推✅ 较好Using filesort需要文件排序内存或磁盘❌ 优化Using temporary使用临时表❌ 优化Using join bufferJOIN 缓冲⚠️ 可能需要优化️ 实战优化案例问题Using filesort-- 查询EXPLAINSELECT*FROMordersWHEREuser_id123ORDERBYcreated_atDESC;-- 输出-- Extra: Using filesort ❌原因索引不包含 ORDER BY 的列解决-- 创建包含排序字段的索引CREATEINDEXidx_user_dateONorders(user_id,created_at);-- 再次查询EXPLAINSELECT*FROMordersWHEREuser_id123ORDERBYcreated_atDESC;-- Extra: Using index condition ✅4. 锁问题当数据库变成停车场 锁的比喻共享锁Shared LockS 锁读锁多人可以同时读排他锁Exclusive LockX 锁写锁只有一个人能写其他人等意向锁表明我打算加锁提前声明 常见锁问题4.1 行锁升级为表锁-- 问题查询UPDATEusersSETname新名字WHERELOWER(name)old name;结果行锁 → 表锁整个表被锁定原因函数破坏了索引MySQL 不知道锁哪行解决-- ✅ 改写查询UPDATEusersSETname新名字WHEREnameold name;4.2 死锁Deadlock场景-- 事务 ASTARTTRANSACTION;UPDATEusersSETbalancebalance-100WHEREid1;-- 等待...-- 事务 BSTARTTRANSACTION;UPDATEusersSETbalancebalance100WHEREid2;UPDATEusersSETbalancebalance-100WHEREid1;-- 等待 A-- 此时 A 也尝试更新 id 2...死锁MySQL 内心戏“你们两个互相等着没完没了了是吧我只好杀一个…”解决方法统一加锁顺序最重要-- 所有事务都按相同顺序加锁-- 比如总是按 id 从小到大减少事务持有锁的时间-- ❌ 不好STARTTRANSACTION;UPDATEusersSETbalancebalance-100WHEREid1;-- 执行一些耗时的操作...sleep(5);COMMIT;-- ✅ 好-- 快速执行快速提交STARTTRANSACTION;UPDATEusersSETbalancebalance-100WHEREid1;COMMIT;设置死锁超时SETGLOBALinnodb_lock_wait_timeout10;-- 10 秒后放弃4.3 间隙锁Gap Lock的坑-- 当前数据id 1, 5, 10-- 事务 ABEGIN;SELECT*FROMusersWHEREid1ANDid10FORUPDATE;-- 锁定了 (1, 5) 和 (5, 10) 的间隙-- 事务 BBEGIN;INSERTINTOusersVALUES(3,new);-- 被阻塞解决-- 使用 READ COMMITTED 隔离级别减少间隙锁SETSESSIONTRANSACTIONISOLATIONLEVELREADCOMMITTED;5. 配置调优默认配置是个坑⚙️ 关键配置参数5.1 InnoDB 缓冲池大小-- 查看当前配置SHOWVARIABLESLIKEinnodb_buffer_pool_size;-- 默认128M太小了-- 设置为物理内存的 50-70%-- 例如 16GB 内存的服务器SETGLOBALinnodb_buffer_pool_size10737418240;-- 10GB为什么重要InnoDB 的数据和索引都缓存在这里太小会导致频繁磁盘 I/O。5.2 连接数-- 查看最大连接数SHOWVARIABLESLIKEmax_connections;-- 默认151-- 根据业务调整SETGLOBALmax_connections500;注意不是越大越好每个连接占用内存太多会 OOM。5.3 查询缓存Query Cache-- MySQL 8.0 已移除8.0 以下版本建议关闭SETGLOBALquery_cache_type0;SETGLOBALquery_cache_size0;为什么关闭并发高时锁竞争严重命中率通常很低表更新导致整个缓存失效5.4 临时表大小-- 内存临时表大小SHOWVARIABLESLIKEtmp_table_size;-- 默认 16M-- 增加SETGLOBALtmp_table_size67108864;-- 64MSETGLOBALmax_heap_table_size67108864;5.5 日志配置-- Redo Log 大小影响写入性能SHOWVARIABLESLIKEinnodb_log_file_size;-- 建议设置为 512M - 2G-- 刷盘策略1 最安全但最慢0/2 更快但可能丢数据SHOWVARIABLESLIKEinnodb_flush_log_at_trx_commit;-- 生产环境建议 1高并发可考虑 2 配置文件模板my.cnf[mysqld] # 基础配置 port 3306 datadir /var/lib/mysql socket /var/lib/mysql/mysql.sock # 连接配置 max_connections 500 max_connect_errors 1000 # InnoDB 配置最重要 innodb_buffer_pool_size 10G innodb_log_file_size 512M innodb_flush_log_at_trx_commit 1 innodb_flush_method O_DIRECT # 慢查询日志 slow_query_log ON long_query_time 1 slow_query_log_file /var/log/mysql/slow-query.log # 字符集 character-set-server utf8mb4 collation-server utf8mb4_unicode_ci6. 实战案例集锦案例 1分页查询优化问题深分页很慢-- 查询第 10000 页每页 10 条SELECT*FROMordersORDERBYidLIMIT100000,10;-- 执行时间8.5 秒 原因MySQL 需要扫描 100000 行然后抛弃解决方案方案 1使用子查询推荐SELECT*FROMordersWHEREid(SELECTidFROMordersORDERBYidLIMIT100000,1)ORDERBYidLIMIT10;-- 执行时间0.05 秒 ✅方案 2记录上次 ID游标分页-- 第一页SELECT*FROMordersORDERBYidLIMIT10;-- 假设最后一个 id 100-- 第二页SELECT*FROMordersWHEREid100ORDERBYidLIMIT10;-- 超级快✅方案 3使用覆盖索引-- 先查 ID很快SELECTidFROMordersORDERBYidLIMIT100000,10;-- 再根据 ID 查完整数据SELECT*FROMordersWHEREidIN(1,2,3,...);案例 2COUNT 查询优化问题COUNT(*) 很慢-- 大表统计SELECTCOUNT(*)FROMorders;-- 500 万行执行 3 秒解决方案方案 1使用近似值-- 使用 EXPLAIN 估算EXPLAINSELECT*FROMorders;-- rows 字段就是估算值-- 或使用信息架构SELECTtable_rowsFROMinformation_schema.tablesWHEREtable_schemayour_dbANDtable_nameorders;方案 2维护计数器表-- 创建计数器表CREATETABLEcounters(table_nameVARCHAR(64)PRIMARYKEY,row_countINT);-- 应用层维护计数-- 每次插入/删除时更新方案 3使用 COUNT(列) WHERE-- 如果只需要统计特定条件SELECTCOUNT(*)FROMordersWHEREstatus1ANDcreated_at2024-01-01;-- 利用索引案例 3JOIN 优化问题多表 JOIN 很慢SELECT*FROMorders oLEFTJOINusers uONo.user_idu.idLEFTJOINproducts pONo.product_idp.idWHEREo.status1;-- 执行时间5 秒优化步骤1. 检查 JOIN 字段索引-- 确保 JOIN 字段有索引SHOWINDEXFROMorders;SHOWINDEXFROMusers;SHOWINDEXFROMproducts;CREATEINDEXidx_user_idONorders(user_id);CREATEINDEXidx_product_idONorders(product_id);2. 使用 STRAIGHT_JOIN 强制顺序-- 如果你知道小表应该先 JOINSELECT*FROMusers u-- 小表STRAIGHT_JOIN orders oONu.ido.user_idWHEREo.status1;3. 分步查询替代大 JOIN-- 先查需要的 user_idSELECTuser_idFROMordersWHEREstatus1;-- 再批量查询用户信息SELECT*FROMusersWHEREidIN(1,2,3,...);-- 应用层组装案例 4子查询优化问题相关子查询很慢-- 慢查询SELECT*,(SELECTCOUNT(*)FROMorder_itemsWHEREorder_idorders.id)ASitem_countFROMorders;-- 执行时间12 秒优化-- 使用 JOINSELECTo.*,COUNT(oi.id)ASitem_countFROMorders oLEFTJOINorder_items oiONo.idoi.order_idGROUPBYo.id;-- 执行时间0.5 秒 ✅ 总结与最佳实践SQL 调优 Checklist检查项说明✅索引WHERE / JOIN / ORDER BY 字段是否有索引✅EXPLAIN是否扫过执行计划type 是否合理✅ **SELECT ***是否只查询需要的列✅慢查询日志是否开启并定期分析✅分页是否避免深度分页✅事务是否快速提交避免长事务✅配置缓冲池、连接数是否合理 调优的黄金法则先监控后优化不知道慢在哪里就别乱改索引第一80% 的性能问题都是索引问题小步快跑一次优化一个点测试验证数据驱动用 benchmark 数据说话别靠感觉留有余地别压榨到极限给流量增长留空间 推荐工具慢查询分析pt-query-digest,mysqldumpslow性能监控MySQL Enterprise Monitor,Percona PMM压测工具sysbench,mysqlslap可视化管理phpMyAdmin,DBeaver,Navicat 最后希望这篇文档能帮你把 MySQL 从蜗牛变成闪电记住调优是个持续的过程没有一劳永逸的方案。定期检查慢查询日志保持好奇心你的数据库会越来越快“Premature optimization is the root of all evil” — Donald Knuth但如果是生产环境慢查询那就不是过早优化了那是救火Happy Querying!