每次接手一个“MySQL慢得像蜗牛”的排查任务我第一反应不是去看服务器配置也不是去骂产品经理又乱写需求而是先打开慢查询日志和EXPLAIN。干这行十年经手的MySQL优化案例没有一千也有八百不管是单表几十万数据的初创项目还是分库分表后单表仍上亿的成熟业务系统最终的性能瓶颈往往都收束到三个方向索引是否设计合理、SQL是否写得到位、数据量大到一定程度后架构是否扛得住。这篇文章不会跟你聊那些“高大上”的底层原理名词堆砌就实打实地把索引、SQL调优、分库分表这三块硬骨头拆开揉碎讲清楚每个方案背后的取舍逻辑和我在项目里验证过的真实做法。这篇攻略适合谁看如果你刚把业务代码写完发现线上数据库CPU经常飙到100%或者查询接口随着数据量增长从200ms一路滑到2秒开外又或者你正在为“要不要分库分表”这个问题纠结到失眠——那这篇文章就是写给你的。我尽量把每个决策点都还原成“当时我遇到的是什么场景、为什么选这个方案、踩了哪些坑”而不是给你一堆PPT式的理论框架。1. 先定位慢在哪90%的MySQL性能问题都出在同一个地方我见过太多团队一上来就讨论要不要换PostgreSQL、要不要上分布式数据库结果我上去一查单表才三千万数据连接数才一两百根本还没到换数据库的地步纯粹是几条核心SQL没写好索引走了全表扫描。定位慢查询这件事是后续所有优化的地基地基不稳后面所有动作都是瞎忙活。1.1 慢查询日志与EXPLAIN的正确打开方式排查的第一步永远是打开慢查询日志。很多开发者说“我开了啊”结果一查long_query_time设置的是默认的10秒等于没开。这个阈值我一般建议从0.5秒开始设先抓出当前消耗最高的SQL——晚上用pt-query-digest这类工具一汇总top 10的慢SQL基本就是你要优化的大头。拿到慢SQL之后先别急着看业务逻辑直接EXPLAIN看执行计划。这里有个新手特别容易忽略的点EXPLAIN看的是预估执行计划它告诉你的是优化器“打算怎么干”不是“实际什么表现”。真正要看你得开EXPLAIN ANALYZEMySQL 8.0.18支持它能给出实际执行时间和扫描行数做完索引改动之后也必须用这个命令确认优化效果而不是只看type是不是从ALL变成了ref就以为万事大吉。看执行计划时我有一套自己的快速判断顺序type字段从好到差依次是 system const eq_ref ref range index ALL。只要看到ALL基本可以确定这条SQL要全表扫除非表就几百行否则一定要优化。key字段看看实际用了哪个索引。经常出现的坑是possible_keys里有候选索引但key是NULL——就是优化器压根没选你的索引。rows字段预估扫描行数。这个数值和实际行数相差太大通常意味着统计信息过期或者WHERE条件的写法让优化器无法做准确估算。Extra字段看到Using filesort和Using temporary这是两个危险信号一个说明排序没有走索引一个说明临时表被用到了后面第六节详细讲。1.2 从执行计划到根因判断一张排查对照表为了让你排查起来更有方向感我把日常最常遇到的执行计划特点和对应根因整理成一张表。这个话单是多年实战积累下来的浓缩版排查时直接对照着看就行执行计划特征可能根因优先处理建议typeALL, rows 很大没建索引或索引失效检查WHERE和JOIN条件字段建立合适的单列或联合索引keyNULL, possible_keys 有值优化器放弃索引可能是数据分布问题或函数运算导致重写SQL去掉函数包裹如果实在无法避免考虑强制索引Using filesort排序字段和WHERE条件不在同一个联合索引里调整联合索引字段顺序让排序走索引而不是再开一道排序Using temporaryGROUP BY 或 DISTINCT 触发了临时表重写查询或者调整索引让分组字段有序rows 误差超过10倍统计信息严重过期执行 ANALYZE TABLE 刷新统计信息索引选择性极低重复率过高建了索引但区分度不够优化器不想用换区分度更高的字段或者用覆盖索引绕开回表还有一种坑比较隐蔽——索引选择性误判。比如性别字段你建了索引结果优化器发现全表一半是男一半是女它觉得用这个索引还不如直接扫全表划算于是直接放弃索引。遇到这种情况别硬刚优化器要么用覆盖索引带上查询需要的其他列让扫描成本降低要么直接改业务查询逻辑去掉这个条件。2. 索引为什么失效回表、最左前缀与函数运算的连锁反应索引优化是MySQL性能优化的第一颗子弹。但很多人建完索引就以为完事了结果上线后该慢还是慢。索引失效的那几类典型场景我闭着眼都能背出来——不是因为我记忆力好而是每个坑我都亲自掉进去过又亲自爬出来。2.1 函数包裹、隐式转换和区分度问题一个联合索引的设计案例先看一个我最近处理的真实案例。一张订单表查询条件是WHERE pay_time BETWEEN ... AND ...我建议开发在上面建索引他说建了但你猜怎么着他是这么写的WHERE DATE(pay_time) BETWEEN 2024-01-01 AND 2024-01-31。索引是建在pay_time上的但DATE()函数一包索引直接失效——因为MySQL要先算出DATE(pay_time)的结果才能去和区间比较这等于把全部行先计算一遍那建索引还有什么意义正确的写法应该是WHERE pay_time 2024-01-01 AND pay_time 2024-02-01既覆盖了整个一月份的区间又没做任何函数运算索引能完美走range扫描。这条规则几乎适用于所有日期范围查询也是我审查SQL时一眼就能扫出来的问题。同样的道理字段类型不匹配的隐式转换也是索引杀手。比如user_id字段是varchar(32)但你在代码里传入的是整数类型的IDMySQL会自动把字符串转成数字再比对。这本来不算什么大问题坏就坏在当你拿一个数字去和varchar字段比较的时候MySQL会先对所有行的user_id做一次类型转换于是索引全部失效。这类问题很难排查因为它不报错执行计划也不崩但rows行数就是下不来。真正设计联合索引的时候我养成了三个习惯分享出来供你参考把等值条件放最前面然后才是范围条件。比如查询条件是WHERE status1 AND create_time 2024-01-01联合索引就应该设计为(status, create_time)。因为等值条件可以精确定位范围条件只需要在等值定位后的范围里扫一小段。能用覆盖索引解决的尽量用覆盖索引。比如查询只需要id和status建一个(status, id)的索引MySQL就能直接从索引里拿数据连回表都省了这是最快的一种查询路径。控制索引数量。这块我必须多说两句。我见过一个项目为了一张表建了12个索引写入性能惨不忍睹。索引不是越多越好每多一个索引INSERT和UPDATE就要多维护一个B树。通常单表索引控制在5个以内能复用联合索引的左前缀就绝不重复建索引。2.2 复合索引的最左前缀原则一个字段顺序引发的性能灾难复合索引联合索引最核心的规则就是最左前缀原则——MySQL 8.0之前优化器只能从左往右匹配索引列你不能跳过第一列直接用到第二列。这个我建议你牢记在脑子里因为实际开发中被它坑过的案例我至少亲手处理过十次。具体来说假设你建了复合索引(a, b, c)WHERE a1能走索引WHERE a1 AND b2能走索引WHERE a1 AND c3只能用到索引的a列c列用不上WHERE b2完全不走这个索引最经典的反例是业务需求经常按name和create_time查开发就建了(name, create_time)的索引。但后来新增了一个需求只按create_time范围查询发现这条新SQL慢得离谱查看执行计划居然走了全表扫描。原因就是这个复合索引根本帮不上忙——因为create_time不是第一列只按它查的时候索引直接报废。这时候正确的做法有三种。第一种是再建一个(create_time)的单列索引第二种是如果你频繁需要这个组合直接正面刚改查询让name条件不缺席比如业务上允许默认带一个空串或通配但要注意通配符走不了索引这里不做推荐第三种最优雅——把复合索引设计成(create_time, name)既支持按时间查又支持按时间姓名查。我强烈推荐第三种因为create_time通常是范围条件放在左边之后右边接等值条件大多数情况下都能满足新老需求。索引失效的场景还有很多比如WHERE name LIKE %张这种前置通配符会导致索引失效这个好理解B树是按前缀有序的你硬要在中间挖一段它没法定位还有OR连接的非索引列场景以及SQL里出现!或IS NOT NULL的情况。但归根结底你要理解B树的排序存储特性就能自己推导出索引什么时候能用只要是破坏了有序性或者需要全量计算的场景索引大概率就废了。3. SQL层面最容易忽略的性能杀手分页、JOIN与排序的隐形代价索引设计得再合理SQL写得不靠谱照样卡死。这一节我从最常见的三个业务场景入手分页、多表关联和排序分组把隐藏在业务代码背后的性能杀手挨个揪出来。3.1 分页翻得越深越慢limit深翻页问题的三种解法做后台管理系统的同学应该有切身体会——导数据列表的时候越往后面翻页越慢。比如运营人员在列表页一页页翻到第200页每页50条这页只显示第9951到10000条但MySQL干的事是把从101到10000行的所有行先扫出来然后全部丢掉只把最后50条返回给你。数据量小的时候感觉不出来数据量上到9000万这种写法的延迟你根本没法接受。排查的时候往往发现开发同学的SQL是这种形态SELECT * FROM orders ORDER BY create_time DESC LIMIT 10000, 20;这条基本就是深翻页臭名昭著的写法。优化方案有三种按优先级推荐方案一延迟关联所有版本通用。先只查id覆盖索引能快速定位再用这些id去关联查询完整行SELECT o.* FROM orders o INNER JOIN (SELECT id FROM orders ORDER BY create_time DESC LIMIT 10000, 20) t ON o.id t.id;方案二基于游标分页推荐用在Web端。不是翻页跳转而是通过上一页最后一条记录的ID来进行下一页定位SELECT * FROM orders WHERE create_time 2024-01-15 10:00:00 ORDER BY create_time DESC LIMIT 20;用where条件过滤掉上一页已知的最后一条走索引只在数据里顺序取20条不丢数据不重排。缺点是用户不能直接跳到任意页只能在相邻页之间点击“加载更多”这种交互形式移动端和多数信息流都这么玩因为它压测下来性能最稳。方案三如果产品必须跳页提前算好ID集合一次性查询。用前端的countoffset换算成已知的主键集合然后WHERE id IN (...) LIMIT 20。根据我的实测经验方案二在没有深翻页诉求时性能最佳方案一在必须保留跳页时性价比最高。极限情况下同样的深翻页SQL从800ms优化到80ms以内的都是常规操作。3.2 JOIN不是不能用但ON条件没索引等于灾难再来说JOIN。很多规范文档都会告诉你“尽量少用JOIN”这句话让不少新手走入了另一个极端——把原本一条JOIN拆成三五条SQL在代码里挨个查结果网络来回比以前更慢。我的观点是JOIN本身不是洪水猛兽关键在JOIN字段是否走索引以及小表驱动大表的原则是否被遵守。MySQL的JOIN执行逻辑是这样的外层驱动表每一行都需要去匹配内层表。如果内层表的连接字段没有索引那就是一次全表扫描乘以驱动表的行数——这个代价是几何级增长的。以一个业务系统为例用户表几十万条订单表上千万条要查某时间段内有订单的用户信息。如果JOIN条件是u.id o.user_id但订单表的user_id没建索引每次匹配都要去扫订单表的一千多万行这条查询基本就是灾难现场。建上user_id索引一切迎刃而解。所以我审查SQL的第一原则不是“见了JOIN就皱眉”而是看EXPLAIN里的type只要被驱动表的连接字段是ref或eq_ref级别JOIN完全可以放心用。另一个重点是小表驱动大表。MySQL的优化器一般会自动选择小表做驱动表但如果你用什么子查询包裹的方式去打乱它的统计幻觉优化器也有可能做出错误选择。这时候STRAIGHT_JOIN可以直接指定驱动顺序把大表放到被驱动位置。这个技巧不常用但当你在EXPLAIN里看到驱动表选错的时候它比硬改SQL简单得多。3.3 filesort和临时表ORDER BY与GROUP BY为什么会拖垮查询ORDER BY最怕的不是排序本身而是排序字段没法走索引迫使MySQL把所有结果集捞出来再额外执行一次内存排序或者磁盘排序。EXPLAIN里的Using filesort就是它。在这个地方联合索引的字段顺序又体现出关键作用。比如联合索引是(status, create_time)当查询条件是WHERE status1 ORDER BY create_time DESCMySQL可以直接按索引顺序来读不需要额外排序。但如果你把ORDER BY create_time中的create_time放在索引非末尾位置或者排序条件和查询条件的顺序不匹配filesort就出现了。同样GROUP BY 之所以效率低是因为它通常伴随着临时表的创建MySQL 8.0之前还会专门建个内部临时表来存分组中间结果量大还可能落到磁盘。遇到大表上的GROUP BY我的优化顺序是先看能否通过联合索引设计让分组字段有序这样MySQL就可以直接顺序扫索引然后顺便分组不需要建临时表。如果分组字段的基数很小比如就几个枚举状态改用COUNT(DISTINCT ...)配合业务侧的Map汇总有时反而更利落。非要在SQL里GROUP BY把返回字段做得尽量窄只取需要分组的字段和聚合函数字段减少临时表的内存压力。4. 写入性能与并发控制的隐形瓶颈不只是慢查询才需要关注慢查询折磨人写入抖动同样是事故隐患。很多后台系统的MySQL优化文章通篇讲SELECT但一到每天零点定时任务跑批、或者业务高峰导入数据的场景INSERT和UPDATE的性能问题能把线上打挂。这一节聊聊我在处理写入瓶颈和并发控制时常用的两招批量写的正确姿势和锁竞争的有效规避。4.1 批量提交与事务大小的黄金分割点刚工作那会儿我写过一个批量导入脚本循环一万条数据一条一条INSERT跑了一个多小时还没跑完。后来改成一次事务批量INSERT一千条十分钟跑完。差异就在于INSERT本身一条条执行时每次都有网络往返、事务提交刷盘的开销而批量提交可以把这些固定开销摊薄。但也不是批量越大越好。我有过一次性INSERT十万条的“翻车”经历——事务过大导致undo日志膨胀MVCC机制要保留旧版本数据binlog同步延迟飙升主库事务把从库备库拖得追不上差点引发主从切换事故。后来总结出的经验是单个事务控制在1000~5000条范围或者事务执行时间控制在1秒以内。这个数字不是拍脑袋定的而是权衡了网络往返、锁持有时长、binlog体积和回滚成本之后的可接受区间你可以根据自己业务的写入量做微调。预处理语句绑定参数值得强烈推荐INSERT INTO orders (order_id, user_id, amount, status) VALUES (?, ?, ?, ?)使用prepared statement批量绑定参数能避免每条INSERT都做一次SQL解析。在大批量导数据场景这个优化效果非常明显一千万行的导数任务从小时级缩到十几分钟。4.2 行锁、间隙锁与死锁为什么长事务比慢SQL更可怕慢SQL至少还能在慢查询日志里显式看到长事务才是真正的隐形杀手。事务不提交它持有的锁就一直不释放后续所有要更新同一条记录、甚至插入相邻记录的事务都会排队堵住。更隐蔽的是MVCC下的undo日志膨胀长事务意味着大量旧版本数据不能被purge线程清理这会导致表空间无限膨胀查询还要在版本链上往前找正确的版本性能越来越差。这类长事务的来源十有八九是业务代码在同一个事务里调用了外部接口。例如一个“创建订单并扣库存”的接口你在Spring的Transactional事务里调用支付回调接口等外部响应等了3秒。这三秒里整个订单表相关范围的锁都捏在你手里所有同类请求全部排队阻塞线上就炸锅了。死锁同样值得反复排查。我处理过最典型的死锁场景是两个事务都执行“先更新主表再更新明细表”但顺序恰好反过来两边互相等锁MySQL检测到死锁后就随机选一个事务回滚。这种死锁在高并发写入下特别常见。规避方法不复杂所有事务按照固定顺序访问资源比如先主表后明细表或者把相关行一次性用SELECT ... FOR UPDATE锁住。经验之谈线上环境一定要监控长事务和锁等待事件。information_schema.innodb_trx这张表可以直接查出来哪些事务超过3秒还没提交配套sys.innodb_lock_waits能定位锁等待的根因。趁事务还没膨胀到酿成大祸之前把它杀在萌芽阶段是DBA和资深后端必须建立的夜间值班基本功。5. 分库分表的前提判断数据量大不等于必须要分讲到分库分表我先泼一盆冷水分库分表是最后的手段不是第一选择。很多人看数据量到了几千万就喊着要分库分表结果分完以后跨库JOIN、分布式事务、分布式ID、扩容数据迁移样样都是新坑问题没解决反而更多了。我的经验是在动手分库分表之前你先把下面这些事做扎实了再说。5.1 什么数据量级才需要分库分表三大前置判断标准判断要不要分库分表不是一个数字阈值就拍板的我通常会看三个维度同时亮红灯才启动方案单表数据量超过MySQL的舒适区。InnoDB的B树在单表数据量一亿以内理论上都能扛住但实际运维中发现当表超过2000万行且索引深度超过3层时查询延迟开始明显可见地退化。当然这和数据宽度、磁盘IO性能都有关系不是绝对判断标准。单库的并发写入能力成为瓶颈。比如数据库连接数打满每秒写入行数受制于磁盘IOPS即使拆索引、优化SQL也提不上去。数据增长趋势是持续且陡峭的。如果只是短期活动带来的一波数据量活动结束就平稳了那没必要为了峰值去做永久性的分库分表改造。我见过一个日志表三个月时间从2亿涨到15亿这种就属于必须分另一种是用户表全年就涨个2000万说实话单表再撑几年问题不大。如果只占了第一点单表数据量大但读多写少优先考虑冷热分离归档。比如订单表把一年前的订单迁移到一个独立的归档库或者只读历史表主表数据量直接砍掉70%查询性能秒回。占有便宜又见效快完全不需要分库分表。如果第二、第三点同时出现比如订单系统日订单量突破500万单、流水表半年就过10亿行那才是分库分表的启动时机可以进入下一节。5.2 分片键选错是最大的灾难订单表案例的教训分库分表的第一步不是选中间件而是选分片键。这里有一条血的教训分片键必须满足80%以上的核心查询能带着它走否则就是给自己埋雷。以电商订单系统为例主流分片键是user_id或order_id。选user_id的好处是用户能查到自己的全部订单天然落在同一个分片上坏处是通过order_id直接查询详情就麻烦了——订单号不知道属于哪个用户得路由到所有分片去查一次点开详情页所有分片都得扫一遍。选order_id作为分片键则刚好反过来通过订单号查详情很快但查用户某个时间段的订单列表就麻烦了。工程上的常见解法是维护一张映射表或者在生成order_id时内嵌用户ID的分片信息让order_id自身就携带分片路由信息。举个例子生成订单号的时候把用户ID的后4位嵌入到订单号中这样拿到order_id算一下就知道去哪个分片查不需要额外建立映射关系。分布式ID这块我强烈建议不要再用数据库自增ID作为分片表的全局主键。因为分片之后每个实例的自增ID会重复必须要引入全局唯一ID生成方案。常见的方案有雪花算法Snowflake、各个中间件自带的ID生成器或者用Redis的原子INCR。雪花算法是目前使用最广泛的方案它的核心是时间戳机器ID序列号每秒能生成几百万个不重复ID而且趋势递增对数据库索引友好。5.3 分库分表之后这些SQL全都不能用了分库分表带来的功能损失如果你没有足够的心理和技术准备上线后会让业务方骂娘。我先列一份“黑名单”给你做好准备全局表JOIN消失了。不能做跨分片的JOIN查询。以前一条SQL搞定的事现在得代码里先在各个分片上分别查完然后在内存里做map合并和关联。数据量小时还能接受数据量大了这种聚合层逻辑也是性能瓶颈。全局排序分页非常痛苦。跨分片做ORDER BY create_time LIMIT 0, 20你得把所有分片的top N都拉出来然后在代码层重新排序截取。更恶心的深翻页——你根本没法像单表那样直接跳到第100页因为每个分片都不知道全局的第990到第1000行是哪几条。这也是为什么分库分表之后的产品设计大都改成“下拉加载更多”而不是跳页。分布式事务成本高。分片之后跨分片的事务已经超越了MySQL本地事务的能力你需要引入分布式事务方案两阶段提交、最终一致性的消息事务、或者TCC这套复杂度不是小团队能轻易驾驭的。所以分库分表后第一原则是尽可能把需要事务的数据放到同一个分片也就是通过合理选分片键来避免跨分片事务。这些问题没办法完全规避只能通过合理的表结构设计来缓解。我见过的高可用设计是把订单主表按user_id分片订单明细表也按user_id分片这样同一个用户的主表记录和明细表记录落在同一个物理库还是可以走本地事务这是把跨分片事务扼杀在摇篮里的标准做法。5.4 中间件选型ShardingSphere、MyCat还是自研路由分库分表的落地中间件选型决定了你后续半年的运维成本。当前主流有三个方向ShardingSphere推荐首选Apache顶级项目支持分片、读写分离、数据加密等多种能力。它的JDBC模式是一个轻量级jar包嵌入到应用里代码侵入小、性能损耗低适合Java技术栈是我们团队目前的主力方案。MyCat适合做数据库代理层独立部署的中间件服务对应用透明应用不需要改连接方式但它需要额外部署和运维一套集群链路更长性能损耗也更大。如果非Java技术栈或者想引入数据库中间件这个方向可以评估。自研路由适合极简场景只在代码层做一个简单的分片规则算法比如通过用户ID取模路由到不同的库。这个方案性能最好、最可控但只适合分片规则简单、分片数量固定、不要求弹性扩缩容的场景。再复杂一点你就得从头自己解决分布式事务、平滑扩容这些问题代价极高。我在这块的取舍原则是没有完美的中间件适合当前团队规模和技术栈的才是最好的。如果团队只有两三个人我建议直接用ShardingSphere-JDBC学习成本低出了问题社区答案也多如果公司数据团队规范成熟、对数据库有强管控需求MyCat的代理模式会让DBA更安心。5.5 数据迁移与扩容从停机迁移到平滑扩容的演进分库分表之后迟早要面对扩容的问题。假设初始分成了16个分片两年后数据量涨到需要32个分片怎么办这时候最忌讳的是用hash(user_id) % 32这种规则因为一旦把取模基数从16改成32几乎所有数据都要重新分布迁移成本让人崩溃。我建议在设计分片规则时就直接避免这个问题。两个常用方案一致性哈希。它把哈希值空间组织成环数据落在哪个分片由它沿环顺时针找到的第一个节点决定。扩容的时候只需要迁移部分分片上的数据到新节点不用全部重排。运维成本要低很多。按时间分片历史数据冷处理。对于日志、流水类数据按月份或季度分片。过了当前月份的数据不会再有写入完全不需要迁移。查询的时候带上月份条件就能定位到对应分片读取性能和扩容便利性都有保障。这两个方案选哪个取决于业务特性核心还是要前置考虑别等数据量大了再改路由规则那真是所有DBA的噩梦。6. 优化效果的一次完整复盘一个日订单量百万级系统的改造路径理论讲了这么多还是给你看一个我印象比较深的完整改造案例。这套系统我接手时已经线上运行了两年每天新增订单量在100万行左右数据库单表已经突破1.8亿行高峰期核心查询接口平均响应时间从最初的300ms恶化到3秒左右时不时还会因为慢查询拖垮连接池。6.1 从200ms到20ms索引重建与SQL重写的组合拳接手第一阶段我根本没考虑分库分表而是先针对现有单表做极限优化目标是确认在不动架构的前提下能压榨出多少性能。先抓了top慢SQL发现80%的请求落在两个查询上一是订单列表查询按user_id和order_status过滤按create_time倒序分页二是订单数统计按seller_id和日期范围聚合。订单列表查询的问题是原来的单列索引(user_id)和(create_time)单独存在导致WHERE条件走了user_id索引但ORDER BY create_time依然要filesort。我用覆盖索引方案把联合索引直接设计为(user_id, order_status, create_time, id)查询条件直接从索引里全部取出连回表都省了。这个索引上线后列表查询从450ms降到25ms。订单数统计的问题在GROUP BY。原来的SQL是SELECT seller_id, COUNT(*) FROM orders WHERE create_time BETWEEN ? AND ? GROUP BY seller_id没有合适的联合索引触发了临时表和全表扫描。我调整成了(create_time, seller_id)联合索引让时间过滤和分组字段都能走索引。日期范围检索后的分组从全表扫描变成了索引顺序扫描加分组统计耗时从2.8秒降到180ms。这轮改造做完线上整体慢查询数量直接下降了一个数量级因为90%的核心查询都通过新索引跑进了几十毫秒的区间。6.2 什么触发了最终的分库分表以及落地后的效果对比索引和SQL优化到极限之后系统稳定运行了相当长一段时间。但随着业务继续扩张单日订单量从100万翻到500万单表数据量爆炸式增长到7亿行以上索引深度已经达到4层即使覆盖索引也开始出现明显延迟数据库磁盘占用率长期接近90%。更致命的是核心表的写入并发量大增单库的写入性能开始成为瓶颈连续两次因为读写互锁导致高峰期订单写入延迟超过10秒。到了这个节点单表优化的路已经走到头了我们才正式启动了分库分表方案。按顺序挑了ShardingSphere-JDBC作为中间件分片键选了user_id分成16个物理库每个库内按月份做子表这样既能根据用户很快定位又能利用时间维度做冷热分离这个方案兼顾了业务查询规律和后续数据归档的便利性。因为分片键选对了大部分按用户视角的查询只需要路由到唯一一个分片响应时间从优化前的平均500ms进一步降到30ms以内。以前那些跨月查询订单列表的SQL现在通过ShardingSphere的绑定表功能自动路由到各个月表再合并结果整体体验也没差太多。这轮改造落地的关键其实还是前面的铺垫因为索引和SQL优化已经把单表性能压到了极致我们才敢在数据量确实起来以后有条不紊地分库分表而不是在性能问题一出现就仓促上马。很多团队把顺序搞反了单表索引还没优化好就慌忙分库分表后果基本都是雪上加霜。7. 全局优化思路的更高层面索引优化与架构演进的金线在哪里最后想聊聊我在每一次优化项目中都会问自己的一个问题优化的金线到底画在哪里这个话题可能有点“务虚”但它决定了你在一个项目里投入多少精力和资源值得单独拿一小节来说。7.1 优化决策的两条铁律不要过度设计也不要放弃治疗第一条铁律是先拿数据说话再谈方案取舍。每个优化的起点都是监控数据而不是直觉。慢查询日志、监控面板上的CPU、IOPS、连接数曲线能告诉你系统真实的压力点在哪个维度。数据还没确认瓶颈先优化了一堆业务代码那是白费劲。第二条铁律是不在错误层次上优化。有些团队遇到慢查询第一反应是换数据库结果换完发现还是慢——因为慢的根本原因是查询要返回几百兆数据给应用层。反过来有些团队一遇到写入慢就硬着头皮分析SQL结果根因是磁盘IOPS不够加一块SSD或者把RAID级别换一下就好了。怎么判断正确的优化层次我的经验是如果CPU高通常优先考虑SQL和索引优化如果磁盘IO高往往要考虑减少回表冷读量、增加内存缓冲池或者升级硬件如果连接数满一般查慢查询和锁等待如果以上都排除了还是扛不住才轮到架构层——缓存、读写分离、分库分表逐层升级。7.2 当分库分表也不够用的时候冷热分离与数据归档的最后一公里有一种情况即使做了分库分表你仍然会发现热数据被冷数据拖累。因为分片是按用户ID或者订单号分某个分片上的用户可能恰好一年了还有大量历史订单查询需求和另一个活跃度低的分片负载极不均衡。这时候冷热分离就非常重要了。以订单系统为例我们上线了数据归档任务把超过一年的订单从在线订单库定期迁移到历史归档库。在线库里只保留近一年的热数据一年前的订单需要查询时走独立的归档查询接口。由于在线库的数据量直接砍掉一半以上索引深度降低查询和写入都变轻了。这一步和分库分表是能叠加的。很多团队分库分表后就停在原地其实还可以再往前走一步在分片内部再按时间滚动切割表或者定期对冷数据做压缩归档。这一套组合拳打下来系统的横向扩展能力和纵向数据治理能力才算是真正建立起来了。7.3 缓存系统与MySQL的分工协作别把缓存当成万能药最后想提醒一个特别容易被误用的点很多人把Redis缓存当成MySQL问题的万能药有什么性能问题先加一层缓存。缓存确实能分担读压力但如果你把缓存当成覆盖一切查询的手段等到缓存穿透、缓存雪崩和缓存一致性这些问题一起来的时候你才会意识到缓存治理的复杂度完全不亚于数据库优化。更合理的方式是先让MySQL本身健健康康地跑再用缓存去解决MySQL“不该承担”的读压力。什么算不该承担热点数据读多写少、一致性要求不高的场景比如商品详情、配置信息这些完全可以扔到Redis里。但强一致性的库存扣减、订单状态流转这些数据最好留在MySQL中用数据库事务能力来保证而不是把一致性逻辑搬到Redis的Lua脚本里去任何一个环节失误都会造成资损。我见过最“吓人”的架构是把订单都放到Redis里MySQL只做持久化备份结果Redis宕机一次就永久丢了一部分未落库的订单。这种灾难完全可以避免——缓存回归缓存数据库承担它该承担的一致性保障角色。这里不是否认缓存的价值而是强调分层清楚、职责分明。做了这么多年MySQL优化我最深的体会是优化不是一锤子买卖也不是一招鲜吃遍天。索引、SQL、分库分表乃至缓存与冷热分离是一个层层递进、互相配合的体系。每个阶段都有它该做的功课和不该越过的红线而判断那条金线在哪里才是区分一个普通增删改查工程师和资深性能优化专家的根本差异。如果你现在正被MySQL性能问题困扰我建议你按这篇文章的层次逐一排查先打开慢查询日志抓问题SQL再用EXPLAIN分析执行计划然后用索引设计和SQL重写去解决85%以上的性能问题当单表优化到头且数据增长趋势确实不可逆时再理性评估分库分表并且一定要记住分片键大于一切路由规则要预留扩展空间。顺着这个思路走你至少能少踩我当年踩过的那一连串大坑。 MySQL性能优化实战：从慢查询定位到索引与分库分表
每次接手一个“MySQL慢得像蜗牛”的排查任务我第一反应不是去看服务器配置也不是去骂产品经理又乱写需求而是先打开慢查询日志和EXPLAIN。干这行十年经手的MySQL优化案例没有一千也有八百不管是单表几十万数据的初创项目还是分库分表后单表仍上亿的成熟业务系统最终的性能瓶颈往往都收束到三个方向索引是否设计合理、SQL是否写得到位、数据量大到一定程度后架构是否扛得住。这篇文章不会跟你聊那些“高大上”的底层原理名词堆砌就实打实地把索引、SQL调优、分库分表这三块硬骨头拆开揉碎讲清楚每个方案背后的取舍逻辑和我在项目里验证过的真实做法。这篇攻略适合谁看如果你刚把业务代码写完发现线上数据库CPU经常飙到100%或者查询接口随着数据量增长从200ms一路滑到2秒开外又或者你正在为“要不要分库分表”这个问题纠结到失眠——那这篇文章就是写给你的。我尽量把每个决策点都还原成“当时我遇到的是什么场景、为什么选这个方案、踩了哪些坑”而不是给你一堆PPT式的理论框架。1. 先定位慢在哪90%的MySQL性能问题都出在同一个地方我见过太多团队一上来就讨论要不要换PostgreSQL、要不要上分布式数据库结果我上去一查单表才三千万数据连接数才一两百根本还没到换数据库的地步纯粹是几条核心SQL没写好索引走了全表扫描。定位慢查询这件事是后续所有优化的地基地基不稳后面所有动作都是瞎忙活。1.1 慢查询日志与EXPLAIN的正确打开方式排查的第一步永远是打开慢查询日志。很多开发者说“我开了啊”结果一查long_query_time设置的是默认的10秒等于没开。这个阈值我一般建议从0.5秒开始设先抓出当前消耗最高的SQL——晚上用pt-query-digest这类工具一汇总top 10的慢SQL基本就是你要优化的大头。拿到慢SQL之后先别急着看业务逻辑直接EXPLAIN看执行计划。这里有个新手特别容易忽略的点EXPLAIN看的是预估执行计划它告诉你的是优化器“打算怎么干”不是“实际什么表现”。真正要看你得开EXPLAIN ANALYZEMySQL 8.0.18支持它能给出实际执行时间和扫描行数做完索引改动之后也必须用这个命令确认优化效果而不是只看type是不是从ALL变成了ref就以为万事大吉。看执行计划时我有一套自己的快速判断顺序type字段从好到差依次是 system const eq_ref ref range index ALL。只要看到ALL基本可以确定这条SQL要全表扫除非表就几百行否则一定要优化。key字段看看实际用了哪个索引。经常出现的坑是possible_keys里有候选索引但key是NULL——就是优化器压根没选你的索引。rows字段预估扫描行数。这个数值和实际行数相差太大通常意味着统计信息过期或者WHERE条件的写法让优化器无法做准确估算。Extra字段看到Using filesort和Using temporary这是两个危险信号一个说明排序没有走索引一个说明临时表被用到了后面第六节详细讲。1.2 从执行计划到根因判断一张排查对照表为了让你排查起来更有方向感我把日常最常遇到的执行计划特点和对应根因整理成一张表。这个话单是多年实战积累下来的浓缩版排查时直接对照着看就行执行计划特征可能根因优先处理建议typeALL, rows 很大没建索引或索引失效检查WHERE和JOIN条件字段建立合适的单列或联合索引keyNULL, possible_keys 有值优化器放弃索引可能是数据分布问题或函数运算导致重写SQL去掉函数包裹如果实在无法避免考虑强制索引Using filesort排序字段和WHERE条件不在同一个联合索引里调整联合索引字段顺序让排序走索引而不是再开一道排序Using temporaryGROUP BY 或 DISTINCT 触发了临时表重写查询或者调整索引让分组字段有序rows 误差超过10倍统计信息严重过期执行 ANALYZE TABLE 刷新统计信息索引选择性极低重复率过高建了索引但区分度不够优化器不想用换区分度更高的字段或者用覆盖索引绕开回表还有一种坑比较隐蔽——索引选择性误判。比如性别字段你建了索引结果优化器发现全表一半是男一半是女它觉得用这个索引还不如直接扫全表划算于是直接放弃索引。遇到这种情况别硬刚优化器要么用覆盖索引带上查询需要的其他列让扫描成本降低要么直接改业务查询逻辑去掉这个条件。2. 索引为什么失效回表、最左前缀与函数运算的连锁反应索引优化是MySQL性能优化的第一颗子弹。但很多人建完索引就以为完事了结果上线后该慢还是慢。索引失效的那几类典型场景我闭着眼都能背出来——不是因为我记忆力好而是每个坑我都亲自掉进去过又亲自爬出来。2.1 函数包裹、隐式转换和区分度问题一个联合索引的设计案例先看一个我最近处理的真实案例。一张订单表查询条件是WHERE pay_time BETWEEN ... AND ...我建议开发在上面建索引他说建了但你猜怎么着他是这么写的WHERE DATE(pay_time) BETWEEN 2024-01-01 AND 2024-01-31。索引是建在pay_time上的但DATE()函数一包索引直接失效——因为MySQL要先算出DATE(pay_time)的结果才能去和区间比较这等于把全部行先计算一遍那建索引还有什么意义正确的写法应该是WHERE pay_time 2024-01-01 AND pay_time 2024-02-01既覆盖了整个一月份的区间又没做任何函数运算索引能完美走range扫描。这条规则几乎适用于所有日期范围查询也是我审查SQL时一眼就能扫出来的问题。同样的道理字段类型不匹配的隐式转换也是索引杀手。比如user_id字段是varchar(32)但你在代码里传入的是整数类型的IDMySQL会自动把字符串转成数字再比对。这本来不算什么大问题坏就坏在当你拿一个数字去和varchar字段比较的时候MySQL会先对所有行的user_id做一次类型转换于是索引全部失效。这类问题很难排查因为它不报错执行计划也不崩但rows行数就是下不来。真正设计联合索引的时候我养成了三个习惯分享出来供你参考把等值条件放最前面然后才是范围条件。比如查询条件是WHERE status1 AND create_time 2024-01-01联合索引就应该设计为(status, create_time)。因为等值条件可以精确定位范围条件只需要在等值定位后的范围里扫一小段。能用覆盖索引解决的尽量用覆盖索引。比如查询只需要id和status建一个(status, id)的索引MySQL就能直接从索引里拿数据连回表都省了这是最快的一种查询路径。控制索引数量。这块我必须多说两句。我见过一个项目为了一张表建了12个索引写入性能惨不忍睹。索引不是越多越好每多一个索引INSERT和UPDATE就要多维护一个B树。通常单表索引控制在5个以内能复用联合索引的左前缀就绝不重复建索引。2.2 复合索引的最左前缀原则一个字段顺序引发的性能灾难复合索引联合索引最核心的规则就是最左前缀原则——MySQL 8.0之前优化器只能从左往右匹配索引列你不能跳过第一列直接用到第二列。这个我建议你牢记在脑子里因为实际开发中被它坑过的案例我至少亲手处理过十次。具体来说假设你建了复合索引(a, b, c)WHERE a1能走索引WHERE a1 AND b2能走索引WHERE a1 AND c3只能用到索引的a列c列用不上WHERE b2完全不走这个索引最经典的反例是业务需求经常按name和create_time查开发就建了(name, create_time)的索引。但后来新增了一个需求只按create_time范围查询发现这条新SQL慢得离谱查看执行计划居然走了全表扫描。原因就是这个复合索引根本帮不上忙——因为create_time不是第一列只按它查的时候索引直接报废。这时候正确的做法有三种。第一种是再建一个(create_time)的单列索引第二种是如果你频繁需要这个组合直接正面刚改查询让name条件不缺席比如业务上允许默认带一个空串或通配但要注意通配符走不了索引这里不做推荐第三种最优雅——把复合索引设计成(create_time, name)既支持按时间查又支持按时间姓名查。我强烈推荐第三种因为create_time通常是范围条件放在左边之后右边接等值条件大多数情况下都能满足新老需求。索引失效的场景还有很多比如WHERE name LIKE %张这种前置通配符会导致索引失效这个好理解B树是按前缀有序的你硬要在中间挖一段它没法定位还有OR连接的非索引列场景以及SQL里出现!或IS NOT NULL的情况。但归根结底你要理解B树的排序存储特性就能自己推导出索引什么时候能用只要是破坏了有序性或者需要全量计算的场景索引大概率就废了。3. SQL层面最容易忽略的性能杀手分页、JOIN与排序的隐形代价索引设计得再合理SQL写得不靠谱照样卡死。这一节我从最常见的三个业务场景入手分页、多表关联和排序分组把隐藏在业务代码背后的性能杀手挨个揪出来。3.1 分页翻得越深越慢limit深翻页问题的三种解法做后台管理系统的同学应该有切身体会——导数据列表的时候越往后面翻页越慢。比如运营人员在列表页一页页翻到第200页每页50条这页只显示第9951到10000条但MySQL干的事是把从101到10000行的所有行先扫出来然后全部丢掉只把最后50条返回给你。数据量小的时候感觉不出来数据量上到9000万这种写法的延迟你根本没法接受。排查的时候往往发现开发同学的SQL是这种形态SELECT * FROM orders ORDER BY create_time DESC LIMIT 10000, 20;这条基本就是深翻页臭名昭著的写法。优化方案有三种按优先级推荐方案一延迟关联所有版本通用。先只查id覆盖索引能快速定位再用这些id去关联查询完整行SELECT o.* FROM orders o INNER JOIN (SELECT id FROM orders ORDER BY create_time DESC LIMIT 10000, 20) t ON o.id t.id;方案二基于游标分页推荐用在Web端。不是翻页跳转而是通过上一页最后一条记录的ID来进行下一页定位SELECT * FROM orders WHERE create_time 2024-01-15 10:00:00 ORDER BY create_time DESC LIMIT 20;用where条件过滤掉上一页已知的最后一条走索引只在数据里顺序取20条不丢数据不重排。缺点是用户不能直接跳到任意页只能在相邻页之间点击“加载更多”这种交互形式移动端和多数信息流都这么玩因为它压测下来性能最稳。方案三如果产品必须跳页提前算好ID集合一次性查询。用前端的countoffset换算成已知的主键集合然后WHERE id IN (...) LIMIT 20。根据我的实测经验方案二在没有深翻页诉求时性能最佳方案一在必须保留跳页时性价比最高。极限情况下同样的深翻页SQL从800ms优化到80ms以内的都是常规操作。3.2 JOIN不是不能用但ON条件没索引等于灾难再来说JOIN。很多规范文档都会告诉你“尽量少用JOIN”这句话让不少新手走入了另一个极端——把原本一条JOIN拆成三五条SQL在代码里挨个查结果网络来回比以前更慢。我的观点是JOIN本身不是洪水猛兽关键在JOIN字段是否走索引以及小表驱动大表的原则是否被遵守。MySQL的JOIN执行逻辑是这样的外层驱动表每一行都需要去匹配内层表。如果内层表的连接字段没有索引那就是一次全表扫描乘以驱动表的行数——这个代价是几何级增长的。以一个业务系统为例用户表几十万条订单表上千万条要查某时间段内有订单的用户信息。如果JOIN条件是u.id o.user_id但订单表的user_id没建索引每次匹配都要去扫订单表的一千多万行这条查询基本就是灾难现场。建上user_id索引一切迎刃而解。所以我审查SQL的第一原则不是“见了JOIN就皱眉”而是看EXPLAIN里的type只要被驱动表的连接字段是ref或eq_ref级别JOIN完全可以放心用。另一个重点是小表驱动大表。MySQL的优化器一般会自动选择小表做驱动表但如果你用什么子查询包裹的方式去打乱它的统计幻觉优化器也有可能做出错误选择。这时候STRAIGHT_JOIN可以直接指定驱动顺序把大表放到被驱动位置。这个技巧不常用但当你在EXPLAIN里看到驱动表选错的时候它比硬改SQL简单得多。3.3 filesort和临时表ORDER BY与GROUP BY为什么会拖垮查询ORDER BY最怕的不是排序本身而是排序字段没法走索引迫使MySQL把所有结果集捞出来再额外执行一次内存排序或者磁盘排序。EXPLAIN里的Using filesort就是它。在这个地方联合索引的字段顺序又体现出关键作用。比如联合索引是(status, create_time)当查询条件是WHERE status1 ORDER BY create_time DESCMySQL可以直接按索引顺序来读不需要额外排序。但如果你把ORDER BY create_time中的create_time放在索引非末尾位置或者排序条件和查询条件的顺序不匹配filesort就出现了。同样GROUP BY 之所以效率低是因为它通常伴随着临时表的创建MySQL 8.0之前还会专门建个内部临时表来存分组中间结果量大还可能落到磁盘。遇到大表上的GROUP BY我的优化顺序是先看能否通过联合索引设计让分组字段有序这样MySQL就可以直接顺序扫索引然后顺便分组不需要建临时表。如果分组字段的基数很小比如就几个枚举状态改用COUNT(DISTINCT ...)配合业务侧的Map汇总有时反而更利落。非要在SQL里GROUP BY把返回字段做得尽量窄只取需要分组的字段和聚合函数字段减少临时表的内存压力。4. 写入性能与并发控制的隐形瓶颈不只是慢查询才需要关注慢查询折磨人写入抖动同样是事故隐患。很多后台系统的MySQL优化文章通篇讲SELECT但一到每天零点定时任务跑批、或者业务高峰导入数据的场景INSERT和UPDATE的性能问题能把线上打挂。这一节聊聊我在处理写入瓶颈和并发控制时常用的两招批量写的正确姿势和锁竞争的有效规避。4.1 批量提交与事务大小的黄金分割点刚工作那会儿我写过一个批量导入脚本循环一万条数据一条一条INSERT跑了一个多小时还没跑完。后来改成一次事务批量INSERT一千条十分钟跑完。差异就在于INSERT本身一条条执行时每次都有网络往返、事务提交刷盘的开销而批量提交可以把这些固定开销摊薄。但也不是批量越大越好。我有过一次性INSERT十万条的“翻车”经历——事务过大导致undo日志膨胀MVCC机制要保留旧版本数据binlog同步延迟飙升主库事务把从库备库拖得追不上差点引发主从切换事故。后来总结出的经验是单个事务控制在1000~5000条范围或者事务执行时间控制在1秒以内。这个数字不是拍脑袋定的而是权衡了网络往返、锁持有时长、binlog体积和回滚成本之后的可接受区间你可以根据自己业务的写入量做微调。预处理语句绑定参数值得强烈推荐INSERT INTO orders (order_id, user_id, amount, status) VALUES (?, ?, ?, ?)使用prepared statement批量绑定参数能避免每条INSERT都做一次SQL解析。在大批量导数据场景这个优化效果非常明显一千万行的导数任务从小时级缩到十几分钟。4.2 行锁、间隙锁与死锁为什么长事务比慢SQL更可怕慢SQL至少还能在慢查询日志里显式看到长事务才是真正的隐形杀手。事务不提交它持有的锁就一直不释放后续所有要更新同一条记录、甚至插入相邻记录的事务都会排队堵住。更隐蔽的是MVCC下的undo日志膨胀长事务意味着大量旧版本数据不能被purge线程清理这会导致表空间无限膨胀查询还要在版本链上往前找正确的版本性能越来越差。这类长事务的来源十有八九是业务代码在同一个事务里调用了外部接口。例如一个“创建订单并扣库存”的接口你在Spring的Transactional事务里调用支付回调接口等外部响应等了3秒。这三秒里整个订单表相关范围的锁都捏在你手里所有同类请求全部排队阻塞线上就炸锅了。死锁同样值得反复排查。我处理过最典型的死锁场景是两个事务都执行“先更新主表再更新明细表”但顺序恰好反过来两边互相等锁MySQL检测到死锁后就随机选一个事务回滚。这种死锁在高并发写入下特别常见。规避方法不复杂所有事务按照固定顺序访问资源比如先主表后明细表或者把相关行一次性用SELECT ... FOR UPDATE锁住。经验之谈线上环境一定要监控长事务和锁等待事件。information_schema.innodb_trx这张表可以直接查出来哪些事务超过3秒还没提交配套sys.innodb_lock_waits能定位锁等待的根因。趁事务还没膨胀到酿成大祸之前把它杀在萌芽阶段是DBA和资深后端必须建立的夜间值班基本功。5. 分库分表的前提判断数据量大不等于必须要分讲到分库分表我先泼一盆冷水分库分表是最后的手段不是第一选择。很多人看数据量到了几千万就喊着要分库分表结果分完以后跨库JOIN、分布式事务、分布式ID、扩容数据迁移样样都是新坑问题没解决反而更多了。我的经验是在动手分库分表之前你先把下面这些事做扎实了再说。5.1 什么数据量级才需要分库分表三大前置判断标准判断要不要分库分表不是一个数字阈值就拍板的我通常会看三个维度同时亮红灯才启动方案单表数据量超过MySQL的舒适区。InnoDB的B树在单表数据量一亿以内理论上都能扛住但实际运维中发现当表超过2000万行且索引深度超过3层时查询延迟开始明显可见地退化。当然这和数据宽度、磁盘IO性能都有关系不是绝对判断标准。单库的并发写入能力成为瓶颈。比如数据库连接数打满每秒写入行数受制于磁盘IOPS即使拆索引、优化SQL也提不上去。数据增长趋势是持续且陡峭的。如果只是短期活动带来的一波数据量活动结束就平稳了那没必要为了峰值去做永久性的分库分表改造。我见过一个日志表三个月时间从2亿涨到15亿这种就属于必须分另一种是用户表全年就涨个2000万说实话单表再撑几年问题不大。如果只占了第一点单表数据量大但读多写少优先考虑冷热分离归档。比如订单表把一年前的订单迁移到一个独立的归档库或者只读历史表主表数据量直接砍掉70%查询性能秒回。占有便宜又见效快完全不需要分库分表。如果第二、第三点同时出现比如订单系统日订单量突破500万单、流水表半年就过10亿行那才是分库分表的启动时机可以进入下一节。5.2 分片键选错是最大的灾难订单表案例的教训分库分表的第一步不是选中间件而是选分片键。这里有一条血的教训分片键必须满足80%以上的核心查询能带着它走否则就是给自己埋雷。以电商订单系统为例主流分片键是user_id或order_id。选user_id的好处是用户能查到自己的全部订单天然落在同一个分片上坏处是通过order_id直接查询详情就麻烦了——订单号不知道属于哪个用户得路由到所有分片去查一次点开详情页所有分片都得扫一遍。选order_id作为分片键则刚好反过来通过订单号查详情很快但查用户某个时间段的订单列表就麻烦了。工程上的常见解法是维护一张映射表或者在生成order_id时内嵌用户ID的分片信息让order_id自身就携带分片路由信息。举个例子生成订单号的时候把用户ID的后4位嵌入到订单号中这样拿到order_id算一下就知道去哪个分片查不需要额外建立映射关系。分布式ID这块我强烈建议不要再用数据库自增ID作为分片表的全局主键。因为分片之后每个实例的自增ID会重复必须要引入全局唯一ID生成方案。常见的方案有雪花算法Snowflake、各个中间件自带的ID生成器或者用Redis的原子INCR。雪花算法是目前使用最广泛的方案它的核心是时间戳机器ID序列号每秒能生成几百万个不重复ID而且趋势递增对数据库索引友好。5.3 分库分表之后这些SQL全都不能用了分库分表带来的功能损失如果你没有足够的心理和技术准备上线后会让业务方骂娘。我先列一份“黑名单”给你做好准备全局表JOIN消失了。不能做跨分片的JOIN查询。以前一条SQL搞定的事现在得代码里先在各个分片上分别查完然后在内存里做map合并和关联。数据量小时还能接受数据量大了这种聚合层逻辑也是性能瓶颈。全局排序分页非常痛苦。跨分片做ORDER BY create_time LIMIT 0, 20你得把所有分片的top N都拉出来然后在代码层重新排序截取。更恶心的深翻页——你根本没法像单表那样直接跳到第100页因为每个分片都不知道全局的第990到第1000行是哪几条。这也是为什么分库分表之后的产品设计大都改成“下拉加载更多”而不是跳页。分布式事务成本高。分片之后跨分片的事务已经超越了MySQL本地事务的能力你需要引入分布式事务方案两阶段提交、最终一致性的消息事务、或者TCC这套复杂度不是小团队能轻易驾驭的。所以分库分表后第一原则是尽可能把需要事务的数据放到同一个分片也就是通过合理选分片键来避免跨分片事务。这些问题没办法完全规避只能通过合理的表结构设计来缓解。我见过的高可用设计是把订单主表按user_id分片订单明细表也按user_id分片这样同一个用户的主表记录和明细表记录落在同一个物理库还是可以走本地事务这是把跨分片事务扼杀在摇篮里的标准做法。5.4 中间件选型ShardingSphere、MyCat还是自研路由分库分表的落地中间件选型决定了你后续半年的运维成本。当前主流有三个方向ShardingSphere推荐首选Apache顶级项目支持分片、读写分离、数据加密等多种能力。它的JDBC模式是一个轻量级jar包嵌入到应用里代码侵入小、性能损耗低适合Java技术栈是我们团队目前的主力方案。MyCat适合做数据库代理层独立部署的中间件服务对应用透明应用不需要改连接方式但它需要额外部署和运维一套集群链路更长性能损耗也更大。如果非Java技术栈或者想引入数据库中间件这个方向可以评估。自研路由适合极简场景只在代码层做一个简单的分片规则算法比如通过用户ID取模路由到不同的库。这个方案性能最好、最可控但只适合分片规则简单、分片数量固定、不要求弹性扩缩容的场景。再复杂一点你就得从头自己解决分布式事务、平滑扩容这些问题代价极高。我在这块的取舍原则是没有完美的中间件适合当前团队规模和技术栈的才是最好的。如果团队只有两三个人我建议直接用ShardingSphere-JDBC学习成本低出了问题社区答案也多如果公司数据团队规范成熟、对数据库有强管控需求MyCat的代理模式会让DBA更安心。5.5 数据迁移与扩容从停机迁移到平滑扩容的演进分库分表之后迟早要面对扩容的问题。假设初始分成了16个分片两年后数据量涨到需要32个分片怎么办这时候最忌讳的是用hash(user_id) % 32这种规则因为一旦把取模基数从16改成32几乎所有数据都要重新分布迁移成本让人崩溃。我建议在设计分片规则时就直接避免这个问题。两个常用方案一致性哈希。它把哈希值空间组织成环数据落在哪个分片由它沿环顺时针找到的第一个节点决定。扩容的时候只需要迁移部分分片上的数据到新节点不用全部重排。运维成本要低很多。按时间分片历史数据冷处理。对于日志、流水类数据按月份或季度分片。过了当前月份的数据不会再有写入完全不需要迁移。查询的时候带上月份条件就能定位到对应分片读取性能和扩容便利性都有保障。这两个方案选哪个取决于业务特性核心还是要前置考虑别等数据量大了再改路由规则那真是所有DBA的噩梦。6. 优化效果的一次完整复盘一个日订单量百万级系统的改造路径理论讲了这么多还是给你看一个我印象比较深的完整改造案例。这套系统我接手时已经线上运行了两年每天新增订单量在100万行左右数据库单表已经突破1.8亿行高峰期核心查询接口平均响应时间从最初的300ms恶化到3秒左右时不时还会因为慢查询拖垮连接池。6.1 从200ms到20ms索引重建与SQL重写的组合拳接手第一阶段我根本没考虑分库分表而是先针对现有单表做极限优化目标是确认在不动架构的前提下能压榨出多少性能。先抓了top慢SQL发现80%的请求落在两个查询上一是订单列表查询按user_id和order_status过滤按create_time倒序分页二是订单数统计按seller_id和日期范围聚合。订单列表查询的问题是原来的单列索引(user_id)和(create_time)单独存在导致WHERE条件走了user_id索引但ORDER BY create_time依然要filesort。我用覆盖索引方案把联合索引直接设计为(user_id, order_status, create_time, id)查询条件直接从索引里全部取出连回表都省了。这个索引上线后列表查询从450ms降到25ms。订单数统计的问题在GROUP BY。原来的SQL是SELECT seller_id, COUNT(*) FROM orders WHERE create_time BETWEEN ? AND ? GROUP BY seller_id没有合适的联合索引触发了临时表和全表扫描。我调整成了(create_time, seller_id)联合索引让时间过滤和分组字段都能走索引。日期范围检索后的分组从全表扫描变成了索引顺序扫描加分组统计耗时从2.8秒降到180ms。这轮改造做完线上整体慢查询数量直接下降了一个数量级因为90%的核心查询都通过新索引跑进了几十毫秒的区间。6.2 什么触发了最终的分库分表以及落地后的效果对比索引和SQL优化到极限之后系统稳定运行了相当长一段时间。但随着业务继续扩张单日订单量从100万翻到500万单表数据量爆炸式增长到7亿行以上索引深度已经达到4层即使覆盖索引也开始出现明显延迟数据库磁盘占用率长期接近90%。更致命的是核心表的写入并发量大增单库的写入性能开始成为瓶颈连续两次因为读写互锁导致高峰期订单写入延迟超过10秒。到了这个节点单表优化的路已经走到头了我们才正式启动了分库分表方案。按顺序挑了ShardingSphere-JDBC作为中间件分片键选了user_id分成16个物理库每个库内按月份做子表这样既能根据用户很快定位又能利用时间维度做冷热分离这个方案兼顾了业务查询规律和后续数据归档的便利性。因为分片键选对了大部分按用户视角的查询只需要路由到唯一一个分片响应时间从优化前的平均500ms进一步降到30ms以内。以前那些跨月查询订单列表的SQL现在通过ShardingSphere的绑定表功能自动路由到各个月表再合并结果整体体验也没差太多。这轮改造落地的关键其实还是前面的铺垫因为索引和SQL优化已经把单表性能压到了极致我们才敢在数据量确实起来以后有条不紊地分库分表而不是在性能问题一出现就仓促上马。很多团队把顺序搞反了单表索引还没优化好就慌忙分库分表后果基本都是雪上加霜。7. 全局优化思路的更高层面索引优化与架构演进的金线在哪里最后想聊聊我在每一次优化项目中都会问自己的一个问题优化的金线到底画在哪里这个话题可能有点“务虚”但它决定了你在一个项目里投入多少精力和资源值得单独拿一小节来说。7.1 优化决策的两条铁律不要过度设计也不要放弃治疗第一条铁律是先拿数据说话再谈方案取舍。每个优化的起点都是监控数据而不是直觉。慢查询日志、监控面板上的CPU、IOPS、连接数曲线能告诉你系统真实的压力点在哪个维度。数据还没确认瓶颈先优化了一堆业务代码那是白费劲。第二条铁律是不在错误层次上优化。有些团队遇到慢查询第一反应是换数据库结果换完发现还是慢——因为慢的根本原因是查询要返回几百兆数据给应用层。反过来有些团队一遇到写入慢就硬着头皮分析SQL结果根因是磁盘IOPS不够加一块SSD或者把RAID级别换一下就好了。怎么判断正确的优化层次我的经验是如果CPU高通常优先考虑SQL和索引优化如果磁盘IO高往往要考虑减少回表冷读量、增加内存缓冲池或者升级硬件如果连接数满一般查慢查询和锁等待如果以上都排除了还是扛不住才轮到架构层——缓存、读写分离、分库分表逐层升级。7.2 当分库分表也不够用的时候冷热分离与数据归档的最后一公里有一种情况即使做了分库分表你仍然会发现热数据被冷数据拖累。因为分片是按用户ID或者订单号分某个分片上的用户可能恰好一年了还有大量历史订单查询需求和另一个活跃度低的分片负载极不均衡。这时候冷热分离就非常重要了。以订单系统为例我们上线了数据归档任务把超过一年的订单从在线订单库定期迁移到历史归档库。在线库里只保留近一年的热数据一年前的订单需要查询时走独立的归档查询接口。由于在线库的数据量直接砍掉一半以上索引深度降低查询和写入都变轻了。这一步和分库分表是能叠加的。很多团队分库分表后就停在原地其实还可以再往前走一步在分片内部再按时间滚动切割表或者定期对冷数据做压缩归档。这一套组合拳打下来系统的横向扩展能力和纵向数据治理能力才算是真正建立起来了。7.3 缓存系统与MySQL的分工协作别把缓存当成万能药最后想提醒一个特别容易被误用的点很多人把Redis缓存当成MySQL问题的万能药有什么性能问题先加一层缓存。缓存确实能分担读压力但如果你把缓存当成覆盖一切查询的手段等到缓存穿透、缓存雪崩和缓存一致性这些问题一起来的时候你才会意识到缓存治理的复杂度完全不亚于数据库优化。更合理的方式是先让MySQL本身健健康康地跑再用缓存去解决MySQL“不该承担”的读压力。什么算不该承担热点数据读多写少、一致性要求不高的场景比如商品详情、配置信息这些完全可以扔到Redis里。但强一致性的库存扣减、订单状态流转这些数据最好留在MySQL中用数据库事务能力来保证而不是把一致性逻辑搬到Redis的Lua脚本里去任何一个环节失误都会造成资损。我见过最“吓人”的架构是把订单都放到Redis里MySQL只做持久化备份结果Redis宕机一次就永久丢了一部分未落库的订单。这种灾难完全可以避免——缓存回归缓存数据库承担它该承担的一致性保障角色。这里不是否认缓存的价值而是强调分层清楚、职责分明。做了这么多年MySQL优化我最深的体会是优化不是一锤子买卖也不是一招鲜吃遍天。索引、SQL、分库分表乃至缓存与冷热分离是一个层层递进、互相配合的体系。每个阶段都有它该做的功课和不该越过的红线而判断那条金线在哪里才是区分一个普通增删改查工程师和资深性能优化专家的根本差异。如果你现在正被MySQL性能问题困扰我建议你按这篇文章的层次逐一排查先打开慢查询日志抓问题SQL再用EXPLAIN分析执行计划然后用索引设计和SQL重写去解决85%以上的性能问题当单表优化到头且数据增长趋势确实不可逆时再理性评估分库分表并且一定要记住分片键大于一切路由规则要预留扩展空间。顺着这个思路走你至少能少踩我当年踩过的那一连串大坑。