MySQL优化实战：先索引、再SQL、最后分库分表

一说MySQL优化大部分人脑子里跳出来的就是三个词索引、SQL、分库分表。这样理解不算错但很容易把顺序搞反。我见过不少团队一遇到性能问题就嚷嚷着要分库分表结果把系统搞得无比复杂最后发现是几条SQL没写对。这篇文章不是教科书是我在实际项目里处理慢查询、压测、以及一次真实拆分后沉淀下来的操作路径先索引、再SQL、最后才是架构层拆库拆表。内容适合正在写业务代码的开发和运维同学哪怕你只是第一次接触优化也能按着里面的步骤查到问题在哪。1. MySQL优化的整体思路先定位瓶颈再谈方案1.1 慢SQL背后最常见的四种瓶颈先说一个我反复遇到的场景线上某业务突然变慢监控面板上CPU或IO飙升用户开始投诉。这时如果直接去看SQL语句往往发现并不是语句写错了而是它在执行时触发了某种瓶颈。结合个人排障经验慢SQL背后基本逃不出以下四类问题CPU瓶颈大量排序、聚合、关联查询以及非常深的子查询都会消耗CPU。典型特征就是数据库所在主机的CPU使用率居高不下。IO瓶颈最常见的是走了全表扫描或者索引没有覆盖查询列导致大量随机读磁盘。特征是磁盘IO等待时间明显增加。锁等待瓶颈行锁竞争、间隙锁冲突、元数据锁等待都可能让一条本来很快的SQL被卡住。这种问题从执行时间上看很慢但CPU和IO都很平静。连接数瓶颈慢查询增多后连接堆积线程池被打满新的请求排队。这种已经属于“并发症”了但根因还是慢SQL。真正接手过一个订单类系统之后我最大的感受是大多数慢SQL并不是单一原因而是“索引没建对”和“SQL写得贪心”叠加在一起数据量一上来就彻底暴露。所以优化的第一步不是动方案而是确认瓶颈到底属于哪一类再决定动手顺序。1.2 为什么优先做索引和SQL而不是一上来就分库分表很多同学对“分库分表”有执念总觉得拆完库、拆完表性能问题就自动解决了。这个想法非常危险。分库分表会引入分布式事务、跨分片查询、全局唯一ID、数据迁移等一系列问题每一件都够喝一壶的。如果你只是因为单表数据量到了一千万就想拆分大概率会把自己绕进去。我习惯把优化比作整理房间索引和SQL优化是“把常用的东西放到顺手的位置”分库分表则是“换个大房子重新装修”。房间还没乱到住不了人为了找一双鞋就砸墙改造明显不合理。实际操作中有个经验法则单表数据量在500万到1000万左右只要索引设计合理读写压力通常还能扛住如果单表超过2000万而且字段冗余、慢SQL频发那才是需要认真评估分库分表的时刻。所以顺序永远是这样先看索引和SQL能不能解决80%的问题再讨论架构调整。这不仅是技术理性也是成本考量——改写SQL是小时级的工作分库分表是周月级的工作。1.3 优化前先定好量化指标没有指标就谈不上优化。我每次动手前至少先盯住三个数字扫描行数、响应时间、锁等待时间。其中扫描行数最直观优化前后最好能下降一个数量级。EXPLAIN输出的rows字段就是估算扫描行数type字段给出了访问类型。常见的访问类型从好到差依次是type值含义说明system系统表几乎不访问极少出现const主键或唯一索引等值匹配性能最好ref普通索引等值匹配典型的最优状态range索引范围扫描可接受index扫描了整棵索引树比ALL好但仍需小心ALL全表扫描最需要警惕后面实战部分会详细演示。这里只需要记住一个原则优化不是靠感觉而是靠前后对比这些数值才能真正验证方案有没有生效。2. 索引优化让MySQL少做无用功2.1 B树加速与回表那些事索引为什么快因为MySQL的InnoDB存储引擎使用B树组织索引查找一条记录的时间从逐行扫全表变成了沿着树形结构做很少几次比较。就好比你在一本按拼音排序的字典里查某个字翻开目录直接定位而不是从第一页往后翻。但索引也不是免费的。每个索引都是一棵独立的B树写入数据时除了更新主键索引还要同步更新每一个二级索引。所以索引越多写放大越明显。有些表一建就是七八个索引查询倒是快了写入慢到怀疑人生。另外二级索引保存的是主键值查询字段如果不在索引里还得拿着主键去主键索引里再捞一次这个动作叫“回表”。回表次数多了性能和全表扫描也没多大差别。明白这两个概念之后索引设计的核心就清晰了让查询尽量在索引树上就能拿到所有需要的字段也就是“覆盖索引”同时避免索引数量失控。2.2 索引失效的七种典型场景很多人建了索引但查询还是慢就是因为SQL写法让索引“失效”了。以下七种场景我全都踩过每一种都配了反面示例。前置模糊匹配LIKE %abc无法使用索引因为B树只能按前缀匹配如果必须这样查考虑全文索引或换查询方案。对索引列做函数运算WHERE YEAR(create_time)2025会让索引失效。应该改成范围条件WHERE create_time 2025-01-01 AND create_time 2026-01-01。隐式类型转换WHERE phone13800138000如果phone列是varchar类型MySQL会把字符串隐式转成数字比较导致索引失效。改成字符串字面量带上引号就好。联合索引不满足最左前缀建立了(a,b,c)联合索引却只查询b和c那索引根本用不上。范围查询影响右侧列WHERE a1 AND b100 AND c2b用了范围后c无法继续命中联合索引。这时c的过滤只能靠回表。OR条件中有一侧无索引WHERE a1 OR c2如果c没有索引MySQL可能放弃索引扫描转成全表扫描。区分度太低性别字段建索引每个值对应的数据行数太多优化器认为还不如全表扫。遇到这些情况先不要急着怪MySQL而是检查SQL写法是否破坏了索引条件。破坏索引不是说索引不存在而是优化器算下来用索引导航的成本更高所以放弃它。2.3 联合索引怎么设计才算合理联合索引设计是整个索引优化的核心。以订单表为例最常见的查询是SELECT order_id, amount, status FROM orders WHERE buyer_id 123456 AND status 1 ORDER BY create_time DESC LIMIT 20;候选索引有很多种建法但最有价值的方案是围绕高频查询设计一个联合索引ALTER TABLE orders ADD INDEX idx_buyer_status_time (buyer_id, status, create_time);设计逻辑有三点买家查询是最核心路径所以buyer_id放在第一列满足最左前缀。status作为第二列过滤掉大量无效数据同时让等值条件尽可能多命中。create_time放在最后因为ORDER BY create_time可以利用联合索引完成排序避免额外的filesort。不要盲目在每一列上都建独立索引。普通二级索引之间无法自动合并虽然MySQL有Index Merge优化但场景有限而且独立索引越多写放大越严重。好的索引设计往往三到五个联合索引就足够覆盖一个核心表的主要查询路径。2.4 用EXPLAIN验证索引是否生效设计完索引必须用EXPLAIN验证。上面那条查询的验证结果应该是------------------------------------------------------------------------------------------------------------ | id | select_type | table | type| key | rows | filtered| Extra | ------------------------------------------------------------------------------------------------------ | 1 | SIMPLE | orders | ref | idx_buyer_status_time | 200 | 1.00 | Using index condition | ------------------------------------------------------------------------------------------------------重点关注三个地方type是ref而不是ALLkey显示实际用了哪个索引rows是否从几十万降到几百。Extra里如果出现Using filesort说明排序没有走索引需要微调联合索引的字段顺序或者改变查询条件。有一个小技巧如果发现优化器没有用你设计的索引可以先试试FORCE INDEXSELECT order_id, amount FROM orders FORCE INDEX (idx_buyer_status_time) WHERE buyer_id 123456 AND status 1;但这只能作为临时验证手段。线上如果经常需要FORCE INDEX说明索引本身的设计和真实数据分布不匹配必须重新审视。3. SQL优化把查询写得更聪明3.1 常见的几种“贪心”SQL写法索引解决了访问路径问题SQL写法解决的是“让数据库做的事越少越好”。以下三种写法我在代码评审里经常见到每次都要花很长时间讲道理。第一种是SELECT *。看起来方便实际上会让覆盖索引失效并加大网络传输压力。以订单表为例明明只需要order_id和amount结果把几十个字段全部查出来索引覆盖不到时就会回表。更好的做法是只选需要的列。第二种深分页问题典型症状是页面越翻越慢SELECT * FROM orders WHERE buyer_id 123456 ORDER BY create_time DESC LIMIT 100000, 20;MySQL要扫描到第100000行才开始返回前面扫描的行全部浪费。优化思路是用延迟关联SELECT o.* FROM orders o JOIN ( SELECT id FROM orders WHERE buyer_id 123456 ORDER BY create_time DESC LIMIT 100000, 20 ) t ON o.id t.id;先用索引覆盖查询只取主键回表只发生在最后20条结果扫描成本大幅下降。第三种是选择性不严比如WHERE status ! 1这种负向条件很少能用上索引。能改成等值条件就尽量改成等值条件不能改就考虑重新设计查询角度。3.2 JOIN与子查询的取舍数据库开发里最经典的问题就是“多表关联到底怎么查效率高”。我个人的经验是能用JOIN尽量别在业务代码里逐条查再拼接。原因很简单每发一次SQL都有网络往返和SQL解析开销循环几百次累加起来非常可观。但JOIN也不是随便写。核心原则是小表驱动大表SELECT user.name, order.amount FROM orders o JOIN users u ON o.buyer_id u.id WHERE o.status 1;在上面的SQL里orders作为驱动表users作为被驱动表。如果外层表只有100条内层表用主键u.id关联速度会非常快但如果反过来让几千上万行的用户表做驱动表每行都要去订单表里查一次成本就大了。判断驱动表的方法很简单EXPLAIN第一行显示的表就是驱动表。如果发现驱动表是数据量大的一方尝试调整STRAIGHT_JOIN提示顺序或者增加过滤条件让驱动表先变小。3.3 批量写入与锁的权衡查询优化完了写入也要注意。最典型的低效写法是循环单条插入for (item : list) { INSERT INTO orders (order_id, amount, buyer_id) VALUES (...); }每条INSERT独立提交不仅开销大还会频繁获取和释放行锁。改成批量插入会快很多INSERT INTO orders (order_id, amount, buyer_id) VALUES (1, 100, 123), (2, 200, 456), (3, 300, 789);但批量值也不是越大越好。一个事务里插入几万条执行时间很长导致锁持有时间变长还可能阻塞其他事务。我通常控制单批在500到1000条之间同时配合事务提交这样能在吞吐量和锁竞争之间找到平衡。4. 分库分表时机、策略和避坑4.1 分库分表的启动条件与替代方案什么情况下才需要分库分表我个人总结的启动条件有三条满足两条以上才值得动手单表数据量长期在2000万以上并且还在快速增加。慢SQL数量经过索引和SQL优化后仍然居高不下。磁盘、IO或备份恢复时间已经明显影响运维操作。这个条件说完相信你已经明白它的门槛比很多人想象中高得多。我遇到过某个业务表不到三百万行但一到大促就卡最后排查发现是几条JOIN没写好跟数据量半毛钱关系没有。在真正分库分表之前有一些替代方案值得先试分区表适合按时间归档数据读写分离可以分担只读压力冷热分离把老订单扔到历史库。这些方案的实现成本远低于分库分表而且不会改变业务层的访问方式。如果这些方案都救不了你再考虑拆分。4.2 垂直拆分与水平拆分怎么选分库分表两个方向很多人都会混淆。垂直拆分是把一张宽表拆成多张窄表或者把不同业务的表放到不同库。比如把商品表拆成基本信息表和库存表。适用场景是表中存在大量不常访问的大字段比如文本内容、图片URL。垂直拆分的缺点是操作比较复杂一次查询可能需要关联多张表跨库JOIN基本不可能最后还是要在业务层组合数据。水平拆分是同一个表名按某种规则分布到多个库/多张表里。比如订单表拆成orders_0、orders_1……直到orders_15。拆分的具体规则有两种范围分片按时间或ID区间切分优点是可以方便归档缺点是数据热点不均匀比如本月的数据全落在同一片。哈希分片对分表键取模table_id hash(sharding_key) % N。优点是分布均匀缺点是不好拓展扩容时需要迁移数据。实际项目中绝大多数选择了哈希分片因为热点均匀才是高并发查询更关心的事情。4.3 分表键怎么选是最核心的决策分表键选错整个拆分方案就废了一半。网上很多文档会告诉你“选高频查询字段”但这句话太模糊了。我通常用三个标准衡量区分度高每个分片的行数尽量接近避免数据倾斜。查询模式集中业务中至少80%的查询都带这个字段作为条件。粗粒度和扩展性优先选择业务主键比如买家ID、商家ID。拿订单系统举例最常见的查询是“用户查自己订单”所以分表键选择buyer_id非常合理。但代价是如果业务方需要“商家查自己店铺的订单”由于查询条件不带buyer_id就会变成一次全分片扫描。这个坑在拆分初期就必须要想到通常是提前把这些跨分片查询引导到备用的查询引擎比如一份同步到分析库或搜索引擎。还有一点不要用数据库自增主键作为分表键。不是说不行而是自增主键在业务层没有查询意义。用户问你“我买了什么”你拿一个自增主键完全无从判断去哪个分片找。所以我建议分表键必须和业务访问路径强关联。4.4 拆分之后必定要面对的三座大山第一座是全局唯一ID。原来依赖单库自增主键拆分后每个分片都各自递增必然冲突。业界方案非常多我比较推荐的是号段模式每次从某个序号发号器取一段连续ID应用层在内存里分配用完再取。也可以用雪花算法生成64位分布式ID但要注意时钟回拨问题。第二座是跨分片查询。分表键之外的条件查询很难直接命中分片。例如按buyer_id分库商家想看跨所有买家的订单只能把所有分片结果合并。面对这种需求我的建议是不要试图在MySQL层解决而是建立一份同步到统计库或搜索引擎的数据副本必要时甚至接受分钟级延迟。第三座是数据迁移。拆分不是建好新表就能切换线上老库数据要平滑搬到新结构。稳妥的做法是“历史数据分批搬迁 增量双写”。新老链路同时写入并在迁移过程中做行数校验和抽样比对确认无误后再切读流量。我见过有人直接把数据导出再导入结果丢了几小时订单这种教训千万别再犯。5. 一条慢SQL的完整排查实战5.1 从慢查询日志抓目标SQLMySQL本身提供了慢查询日志最好一开始就在生产环境打开但参数要收敛。更推荐的做法是无论默认是否开启把阈值设到2秒来观察。两步就能搞定SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 2;注意long_query_time的单位是秒线上如果怕误伤可以先从5秒甚至10秒开始慢慢往下调。慢查询日志会持续记录执行时间超过阈值的SQL。找到目标后先看SQL文本简单分析字段和表不要急着改。我更推荐使用pt-query-digest这类分析工具它可以按指纹把慢SQL分组统计出哪几条语句累计耗时最久。一条SQL出现100次每次2秒比一条出现1次但耗时100秒更值得先治理。5.2 用EXPLAIN和性能分析定位瓶颈拿到慢SQL接下来就是套用前面讲的方法。假设我从日志里抓到了这样一条SELECT order_id, amount FROM orders WHERE buyer_name 张伟 ORDER BY create_time DESC LIMIT 20;执行EXPLAIN后看到type ALL key NULL rows 850000 Extra Using where; Using filesort一眼就能判断850万行全表扫还发生了文件排序。buyer_name可能没有索引或者有索引但因某种写法导致失效。这时再用一次SHOW PROFILE看阶段耗时SET profiling 1; -- 再次执行慢SQL SHOW PROFILE FOR QUERY 1;如果Sending data耗时最长基本可以确认是IO读行如果Sorting result耗时最长问题就在排序。这个信息能帮你决定是先加索引减少扫描行数还是调整SQL避免排序。5.3 索引上线与效果验证确定要加索引后不要随手就在生产环境ALTER TABLE。在线DDL虽然在InnoDB里不阻塞读写但大表仍然可能产生较大压力。更稳妥的做法是使用在线表结构变更工具把添加索引过程做得更平滑。上线后重新执行EXPLAIN再看type、rows和Extra你会看到全表扫描变成了ref排序也通过索引完成不再出现Using filesort。执行时间从几秒降到几十毫秒这才算数。还有一个小提醒索引添加后旧的慢SQL可能还在缓存中验证时要刷新或强制走新索引。新增索引本身不复杂真正的风险是在线变更期间对生产资源的影响提前在预发环境压测过再执行比较好。6. 避坑清单与个人经验记录6.1 典型问题速查表把记忆中多次被人问到的问题整理成一张表方便直接定位。现象可能原因处理建议明明建了索引却不生效对索引列用了函数或隐式类型转换改写SQL条件保持列原始类型分页页数越深越慢深分页导致扫描大量行延迟关联或用游标传入上一页ID联合索引查询还是慢最左前缀不满足重排列索引字段顺序锁等待严重事务过度或索引缺失导致范围锁扩大缩短事务优化索引减少锁范围拆分后查询超时查询条件不带分表键增加路由能力或引入外部索引副本写入速度越来越慢表上索引太多精简冗余索引评估覆盖索引必要性表格只能帮你快速缩小范围真正解决还是要落到具体SQL和表结构上。6.2 我踩过的三个坑第一个坑是在低区分度字段上建索引。某次给一个状态字段加索引EXPLAIN看起来走了索引但rows还是非常大。优化器觉得选择性不够经常走全表扫。后来把索引改成联合索引把高频等值条件的另一个字段放前面效果才出来。第二个坑是直接在线上大表ALTER TABLE加索引。操作倒是没锁表但执行期间IO升高影响了同在实例上的其他业务。从那以后我养成习惯大表结构变更必须用限流工具或者在线变更工具选在业务低峰期执行。第三个坑是分库分表之后业务查询条件不带分表键。当时只想到“用户查自己的订单”这个路径忽略了后台客服需要“按手机号查订单”的场景。结果客服页面一打开全分片扫描比不分库还慢。最后是通过定期同步一份订单索引到独立查询库才算把这个问题压下去。这个教训告诉我分库分表前必须把业务的所有查询场景盘清楚尤其是那些低频但影响业务运营的查询。索引、SQL、分库分表这三件事是一套层层递进的体系。如果你能把前两层做好绝大多数系统根本不需要走到第三步。就算真的需要拆分前面积累的SQL排查能力和索引设计经验也会让你少踩很多坑。我个人始终建议每做一次优化都要保留优化前后的EXPLAIN结果和执行时间数字这是最有说服力的交付物。