写在前面我们团队这几年从零到一搭建了一个日活千万级的集成自动化平台数据库层面踩过的坑数不胜数。MySQL 性能问题是最常遇到的——一个慢 SQL 能把整个服务拖垮连锁反应下游超时、上游重试、数据库连接池爆满最后全站不可用。这篇文章不打算写成MySQL 优化大全那种面面俱到的教材。我只讲我们真实遇到过的——有些坑看着简单但在生产环境里真真切切地引发过事故。希望这些经验能帮你在 Code Review 或者线上排查时多一些判断依据。一、索引基础先搞清楚 BTree 在干什么1.1 为什么是 BTreeMySQLInnoDB 引擎的索引底层结构是 BTree。很多人知道这个结论但不知道为什么。先来看一个对比数据结构查找时间复杂度磁盘IO次数适合场景哈希表O(1)1次等值查询二叉搜索树O(logN)O(logN)次内存操作B-TreeO(logN)O(logN)次但N很大磁盘存储BTreeO(logN)2-4次通常磁盘存储范围查询数据库面对的核心挑战是数据在磁盘上而磁盘的随机IO极慢。一块机械硬盘的随机读取延迟是 5-10msSSD 大概是 0.1ms。看起来 SSD 已经很快了但对比内存的 100ns0.0001ms仍然慢了 1000 倍。所以数据库索引的核心设计目标是尽量减少磁盘IO次数。BTree 怎么做到的每个节点存大量Key一个节点是一个磁盘页通常 16KB能存几百到上千个 Key。这意味着树的高度极低——千万级数据树高通常只有 3-4 层叶子节点有序链表范围查询时只需要找到起点然后顺着链表往后读就行不用回溯非叶子节点不存数据只存 Key 和指针能容纳更多分叉进一步降低树高一个直观的数字假设每行数据 1KB主键是 bigint8字节指针 6字节。一个 16KB 的内部节点能容纳约 16KB / (86) ≈1170个分叉。2层1170 × 16 ≈ 18,720 行3层1170 × 1170 × 16 ≈2190万行4层约 250 亿行也就是说两千万行数据只需要3次磁盘IO就能定位到任何一行。这就是 BTree 的威力。1.2 聚簇索引 vs 二级索引InnoDB 里有两种索引聚簇索引主键索引叶子节点直接存储完整的行数据每张表只能有一个数据物理上就是按主键顺序存储的二级索引非主键索引叶子节点存储的是索引列的值 主键值查到主键值后还需要回表——拿着主键去聚簇索引再查一次二级索引查找过程 idx_name(name) → 找到 name张三 → 得到主键 id1001 ↓ 聚簇索引 → 用 id1001 查聚簇索引 → 得到完整行数据这个回表操作是很多性能问题的根源——如果二级索引筛选出 1 万行就需要回表 1 万次。每次回表都可能是一次随机IO。1.3 覆盖索引干掉回表如果你的查询只需要索引列本身包含的字段MySQL 就不需要回表了——直接从索引里就能拿到所有数据。这叫覆盖索引。-- 假设有索引 idx_name_age(name, age)-- 需要回表要取address字段索引里没有SELECTname,age,addressFROMuserWHEREname张三;-- 覆盖索引只查索引里有的字段SELECTname,ageFROMuserWHEREname张三;-- EXPLAIN 的 Extra 列会显示 Using index1.4 联合索引与最左前缀联合索引(a, b, c)本质上是先按 a 排序a 相同再按 b 排序b 相同再按 c 排序。这决定了最左前缀原则-- 能用上索引从最左开始连续匹配WHEREa1WHEREa1ANDb2WHEREa1ANDb2ANDc3-- 用不上索引跳过了aWHEREb2WHEREb2ANDc3-- 部分用上用到ab的范围查询后c用不上WHEREa1ANDb5ANDc3-- 只用到 a, b二、慢 SQL 的物理层面原因在讲索引优化之前先聊一下不是索引问题的慢 SQL。因为很多时候第一反应是加索引但实际问题压根不在索引上。2.1 机械盘 → SSD一次立竿见影的提速我们早期的数据库跑在机械盘上。当时有一批复杂查询涉及多表 JOIN 排序响应时间在 800ms-2s 之间波动。排查了很久——索引都有执行计划看着也合理就是慢。最后发现瓶颈在磁盘随机IO。机械盘的随机 IOPS 大概在 150-200而我们那几个查询涉及大量回表操作二级索引 → 聚簇索引每次回表都是一次随机读。当并发上来后磁盘的 IO 队列堆积延迟直接飙升。换 SSD 后随机 IOPS 从 200 提升到 50000那批查询从 800ms 降到 50-80ms数据库服务器的 IO Wait 从 40% 降到 2%这不是优化是物理层面的代际差距。如果你的 MySQL 还跑在机械盘上其他优化都是细枝末节——先换盘。2.2 跨机房访问一个被忽视的延迟来源另一个真实案例我们有段时间应用服务器和数据库不在同一个物理机房。表面上看都在一个城市网络延迟应该很低。但实测下来同机房内网延迟0.1-0.3ms跨机房专线延迟1-3ms看起来差距不大算一笔账一个业务请求涉及 20 次数据库查询这在 OLTP 场景里很正常每次查询的 MySQL 执行时间只有 1ms。同机房总耗时20 × (0.2 1) 24ms跨机房总耗时20 × (2 1) 60ms仅网络延迟就多了 36ms。如果并发高一点、查询多一点差距会更加明显。更隐蔽的问题是——跨机房网络的抖动。专线偶尔会有 5-10ms 的毛刺在同机房场景下几乎不存在。一旦出现网络毛刺结合连接池等待、慢查询堆积很容易形成雪崩。解决方案把数据库和应用服务器迁到同一物理机房。迁完之后接口 P99 延迟直降 40%什么代码都没改。2.3 CPU 资源紧张还有一类慢 SQL 不是IO问题是CPU问题。常见场景大量的ORDER BY 文件排序filesort复杂的子查询、嵌套查询大字段的 JSON 解析MySQL 5.7 的 JSON 类型操作GROUP BY 产生临时表这类问题的特征是EXPLAIN看着没毛病索引也用了但就是慢。SHOW PROCESSLIST一看State 是Sorting result或Creating tmp table。遇到这种情况首先top看一下 MySQL 进程的 CPU 占用。如果已经跑满几个核了那瓶颈就是算力加索引救不了。三、慢 SQL 的逻辑层面原因真实踩坑下面是重头戏——我们在业务开发中真实遇到的逻辑层面慢 SQL 问题。3.1 索引失效函数转换杀手事故场景我们有一个商品处罚记录表punish_time字段有索引。业务需要查询某一天的处罚记录。开发同学写了这样的 SQL-- 错误写法 ❌SELECT*FROMproduct_punish_recordWHEREDATE_FORMAT(punish_time,%Y-%m-%d)2024-01-15;这条 SQL 在数据量小的时候毫无问题测试环境 100ms 跑完。但到了线上表有 800 万行——这条查询直接走了全表扫描耗时 8 秒。为什么索引失效了BTree 索引存储的是原始列值。当你对索引列套了函数如DATE_FORMATMySQL 必须对每一行数据都计算一次DATE_FORMAT(punish_time, %Y-%m-%d)然后跟2024-01-15比较。它没法利用索引的有序性来快速定位了——因为DATE_FORMAT的结果和原始值的排列顺序虽然一致但 MySQL 优化器不够聪明无法推导出这种等价关系。正确写法-- 正确写法 ✅ 用范围查询代替函数转换SELECT*FROMproduct_punish_recordWHEREpunish_time2024-01-15 00:00:00ANDpunish_time2024-01-16 00:00:00;这样punish_time列没有被函数包裹MySQL 可以直接用索引做范围扫描从 8 秒降到10ms。其他常见的索引杀手函数-- 全部会导致索引失效 ❌WHEREYEAR(create_time)2024WHERELEFT(name,3)张三丰WHERECAST(priceASCHAR)99.9WHEREIFNULL(status,0)1WHEREamount10100-- 列参与了运算也不行-- 正确的替代方案 ✅WHEREcreate_time2024-01-01ANDcreate_time2025-01-01WHEREnameLIKE张三丰%WHEREprice99.9WHEREstatus1-- 业务层保证不插NULL或用默认值WHEREamount90一条铁律索引列必须裸着出现在比较运算符的一边不能被任何函数或表达式包裹。3.2 N1 查询最常见的性能杀手事故场景业务需求查询某个商家下所有商品的处罚记录进行违规分析。第一版代码简化// 错误写法 ❌ 典型的 N1 查询ListProductproductsproductDao.listByMerchantId(merchantId);// 假设有 500 个商品for(Productproduct:products){// 每个商品查一次处罚记录 → 500 次SQLListPunishRecordrecordspunishDao.listByProductId(product.getId());analyze(product,records);}500 个商品 500 次 SQL 查询。即使每条 SQL 只要 5ms总耗时也是 2.5 秒。加上网络开销和连接池等待实际更慢。更致命的是——这 500 条 SQL 各自独立MySQL 的 Buffer Pool 缓存命中率很低因为每次查的范围不同磁盘 IO 被打散成大量小碎片。正确写法// 正确写法 ✅ 合并为一次批量查询ListProductproductsproductDao.listByMerchantId(merchantId);ListLongproductIdsproducts.stream().map(Product::getId).collect(Collectors.toList());// 一次性查出所有商品的处罚记录ListPunishRecordallRecordspunishDao.listByProductIds(productIds);// 在内存中按商品ID分组MapLong,ListPunishRecordrecordMapallRecords.stream().collect(Collectors.groupingBy(PunishRecord::getProductId));// 在内存中做关联分析for(Productproduct:products){ListPunishRecordrecordsrecordMap.getOrDefault(product.getId(),Collections.emptyList());analyze(product,records);}对应的 SQL-- 替代 500 次单条查询1次搞定SELECT*FROMproduct_punish_recordWHEREproduct_idIN(1001,1002,1003,...,1500);效果从 500 次 SQL → 1 次 SQL总耗时从 2.5s 降到50ms。进阶优化如果productIds列表特别大比如上万个IN 子句过长也会有问题。这时可以// 分批查询每批 500 个Lists.partition(productIds,500).forEach(batch-{ListPunishRecordbatchRecordspunishDao.listByProductIds(batch);allRecords.addAll(batchRecords);});思维方式的转变思维方式代码特征性能特征“逐条处理”循环里发SQLN1线性增长“批量思维”先收集ID再一次查内存分组O(1) 次SQL常量耗时这是后端开发者最容易犯、也最容易修的性能问题。Code Review 时只要看到循环里有数据库调用基本就是要改的。3.3 事务锁争用分布式锁化解热点行事故场景商品违规扣分功能——当检测到商品违规时需要给商家扣分。多个违规事件可能同时触发对同一个商家并发扣分。第一版实现// 错误写法 ❌ 数据库行锁 长事务TransactionalpublicvoiddeductScore(LongmerchantId,intpoints,Stringreason){// SELECT ... FOR UPDATE 锁住商家那一行MerchantScorescoremerchantScoreDao.selectForUpdate(merchantId);// 各种业务校验...validateDeduction(score,points);// 调用外部服务比如发通知—— 这里可能耗时 200msnotificationService.notifyMerchant(merchantId,reason);// 更新扣分score.setCurrentScore(score.getCurrentScore()-points);merchantScoreDao.update(score);// 记录扣分流水deductionLogDao.insert(buildLog(merchantId,points,reason));}问题在哪SELECT ... FOR UPDATE锁住了商家行事务里调了外部服务200ms锁持有时间太长并发场景下多个线程抢同一个商家的行锁 → 串行执行 → 接口超时当某个大商家有 50 个商品同时被检测到违规50 个请求串行等锁最后的那个要等 50 × 200ms 10秒。正确做法用分布式锁替代数据库行锁并缩短锁粒度// 正确写法 ✅ 分布式锁 最小化锁范围publicvoiddeductScore(LongmerchantId,intpoints,Stringreason){// 前置操作不需要加锁的validateBasicParams(merchantId,points);// 分布式锁只锁扣分这个动作StringlockKeymerchant:deduct:merchantId;booleanlockedredisLock.tryLock(lockKey,3,TimeUnit.SECONDS);if(!locked){thrownewBusinessException(操作太频繁请稍后再试);}try{// 最小化锁内操作只做读-改-写MerchantScorescoremerchantScoreDao.selectById(merchantId);validateDeduction(score,points);score.setCurrentScore(score.getCurrentScore()-points);merchantScoreDao.update(score);deductionLogDao.insert(buildLog(merchantId,points,reason));}finally{redisLock.unlock(lockKey);}// 通知等耗时操作放在锁外面异步更好asyncNotificationService.notifyMerchant(merchantId,reason);}改进点对比项改前DB行锁改后分布式锁锁粒度数据库行级业务逻辑级锁持有时间整个事务含外部调用 200ms仅读-改-写10-20ms并发表现串行等待阻塞连接池快速失败或短暂等待故障影响死锁可能导致事务超时最多等待 3s 超时关键原则锁的范围尽量小只锁必须互斥的操作锁内不要有 IO 调用RPC、消息发送、文件操作能异步的操作放到锁外面分布式锁比数据库锁更可控可以设超时、可以主动释放、不占数据库连接四、索引设计的实战原则基于上面这些坑总结几条我们实际在用的索引设计原则4.1 建索引前先看查询模式不要拿到表就开始建索引先搞清楚-- 查看哪些SQL最慢SELECT*FROMperformance_schema.events_statements_summary_by_digestORDERBYSUM_TIMER_WAITDESCLIMIT20;-- 或者直接看慢查询日志-- slow_query_log ON, long_query_time 1先知道最耗时的 SQL 是什么再针对性地建索引。盲目建索引只会拖慢写入。4.2 联合索引的列顺序联合索引的列顺序直接决定了它能服务哪些查询。设计原则等值条件的列放前面范围条件的列放后面区分度高的列放前面选择性 不重复值数量 / 总行数-- 典型查询查某商家某时间段的处罚记录SELECT*FROMproduct_punish_recordWHEREmerchant_id10086ANDpunish_time2024-01-01ANDpunish_time2024-02-01;-- 正确的联合索引CREATEINDEXidx_merchant_timeONproduct_punish_record(merchant_id,punish_time);-- merchant_id 是等值条件放前面punish_time 是范围条件放后面如果反过来建(punish_time, merchant_id)那punish_time做范围查询后merchant_id的索引就用不上了。4.3 避免过度索引每个索引都有代价写入变慢每次 INSERT/UPDATE 都要维护索引的 BTree占用空间索引本身也是数据优化器困惑索引太多MySQL 优化器可能选错索引我们的经验法则单表索引不超过 5-6 个联合索引的列不超过 4-5 个不对低区分度的列单独建索引比如status只有 0/1 两个值定期清理不用的索引-- 查看哪些索引从没被用过SELECT*FROMsys.schema_unused_indexesWHEREobject_schemayour_database;4.4 大表加索引的正确姿势线上千万级大表加索引直接ALTER TABLE ... ADD INDEX可能会锁表几分钟。我们的做法-- MySQL 5.6 支持 Online DDLALTERTABLEbig_tableADDINDEXidx_name(name),ALGORITHMINPLACE,LOCKNONE;如果是更早的版本或者更保守的策略用pt-online-schema-changept-online-schema-change\--alterADD INDEX idx_name(name)\--execute\Dyour_db,tbig_table五、EXPLAIN 实战读懂执行计划会看EXPLAIN是 SQL 优化的基本功。核心关注几个字段5.1 type 列访问类型从好到差排列system const eq_ref ref range index ALLtype含义是否可接受const主键/唯一索引等值查询最好eq_refJOIN 时使用主键/唯一索引很好ref使用非唯一索引等值查询好range索引范围扫描还行index全索引扫描一般ALL全表扫描大表必须优化5.2 Extra 列的关键信息Using index → 覆盖索引好 Using where → Server层过滤索引可能没完全匹配 Using temporary → 用了临时表GROUP BY/DISTINCT 需要关注 Using filesort → 文件排序ORDER BY 没用上索引 Using index condition → 索引下推5.6还行5.3 一个真实的 EXPLAIN 分析EXPLAINSELECTproduct_id,punish_time,reasonFROMproduct_punish_recordWHEREmerchant_id10086ANDDATE_FORMAT(punish_time,%Y-%m)2024-01ORDERBYpunish_timeDESCLIMIT20;------------------------------------------------------------------------------------- | id | type | key | rows | filtered | Extra | ------------------------------------------------------------------ | 1 | ALL | NULL | 823910 | 10.00 | Using where; Using filesort | ------------------------------------------------------------------type ALL全表扫描灾难key NULL没用上任何索引rows 823910扫描了 82 万行Extra Using filesort还做了文件排序修复去掉 DATE_FORMAT改用范围查询EXPLAINSELECTproduct_id,punish_time,reasonFROMproduct_punish_recordWHEREmerchant_id10086ANDpunish_time2024-01-01ANDpunish_time2024-02-01ORDERBYpunish_timeDESCLIMIT20;----------------------------------------------------------------------- | id | type | key | rows | filtered | Extra | ----------------------------------------------------------------------- | 1 | range | idx_merchant_time | 156 | 100.00 | Using index condition | -----------------------------------------------------------------------type range范围索引扫描rows 156只扫描了 156 行没有 filesort了因为联合索引(merchant_id, punish_time)天然有序从 82 万行 → 156 行性能提升5000 倍。六、我们的慢 SQL 治理体系解决单个慢 SQL 不难难的是系统性地防止慢 SQL 出现。我们建立了一套治理流程6.1 事前开发阶段拦截代码提交 → SQL审计自动检查 → Code Review人工确认自动检查规则禁止SELECT *强制列出字段禁止无 WHERE 的 UPDATE/DELETE索引列禁止使用函数正则匹配WHERE.*函数名(列名)IN 子句限制不超过 1000 个6.2 事中慢查询实时告警慢查询日志 → 采集到监控系统 → 超过阈值告警阈值设置单条 SQL 1s日报汇总标黄单条 SQL 3s实时告警当天处理单条 SQL 10s紧急告警立即处理6.3 事后定期巡检每周跑一次索引健康检查未使用的索引 → 评估后删除重复索引 → 合并全表扫描频次最高的 SQL → 分析加索引锁等待超时 TOP10 → 分析事务设计总结MySQL 慢 SQL 的治理说到底就是搞清楚三件事瓶颈在哪是磁盘IO网络延迟CPU还是锁等待→ 对应不同的解法索引为什么没生效函数包裹类型不匹配最左前缀断裂→ 让索引列裸出来设计模式对不对N1 查询长事务大表无分页→ 用批量思维、锁粒度最小化说实话90% 的慢 SQL 都不复杂——要么是索引列套了函数要么是循环里发SQL要么是事务范围太大。真正的难点不在解决在发现。建好监控、写好规范、Code Review 时多看一眼 SQL——能避免绝大多数的线上事故。