MySQL优化这事儿说实话已经被聊烂了。网上随便一搜全是“索引失效十大场景”、“SQL优化黄金法则”、“分库分表避坑指南”这类文章。但真正在线上折腾过的人都知道文章写得再花哨落不了地就是废话。你照着文章去建索引结果发现写请求变慢了你辛辛苦苦把SQL从3秒优化到0.3秒结果业务一上量又打回原形你费劲千辛万苦把表拆了最后发现join做不了、事务也没法保证——这些都是真实发生过的。这篇帖子我不打算给你讲什么高深的理论就从一个实际干过几年MySQL运维和架构的人的角度把索引、SQL和分库分表这三件事从头到尾掰开揉碎了讲。核心思路只有一个先搞清楚问题在哪一层再决定用哪把刀。很多人的毛病是想一步到位直接分库分表结果发现根本没到那一步还有些人天天在研究SQL写法却忽略了索引本身就是错的。这篇文章适合谁看适合那些公司业务正在增长MySQL开始出现性能瓶颈但你还没到非上分布式中间件不可阶段的开发者。也适合那些已经分了库分了表但发现为什么还是慢、为什么数据对不上的兄弟。我会把每个关键决策背后的理由讲清楚附上可以直接抄作业的排查思路和参数最后再分享几个线上踩坑的复盘记录。1. 全貌梳理索引、SQL与分库分表的分工与边界在动手优化之前先把这三件事的边界和关系理清楚。搞不清楚边界很容易陷入“拿着锤子看什么都像钉子”的陷阱。1.1 三层优化的定位到底是什么索引、SQL、分库分表本质上是在处理不同层的成本问题。索引和SQL优化解决的是单机单库内部的计算成本问题。说白了就是同样在一张表里查数据怎么让存储引擎少干活。索引决定了数据怎么被快速定位SQL决定了优化器拿到你写的语句之后能不能走上去。这两者其实是配套的索引建得好但SQL写法让优化器不走索引等于白建SQL写得再规范但表上压根没有合适的索引优化器也只能硬着头皮全表扫。分库分表解决的则是单机物理上限的问题。当你的数据量或者写入并发已经超出了单个MySQL实例的承载能力不管索引多完美、SQL多高效都无济于事。这时候不是优化的问题是架构的问题。很多人经常犯的错误是混淆这两个层面。比如表里已经有几亿行数据还在费劲地调SQL企图让它跑进100毫秒。这往往徒劳无功。反过来才几百万行数据的表三天两头有人提分库分表方案这也是典型的过度设计。1.2 优化顺序的决策逻辑我个人的习惯永远遵循一个顺序先SQL后索引再架构。这个顺序看起来跟标题的顺序不一样但实际工作上必须颠倒过来。理由很简单SQL和索引的调整成本低、周期短、风险可控而分库分表的改造成本极高一旦上了这条船意味着你的代码要全面重构事务模型要变join要拆扩容要规划。能通过SQL和索引解决的问题绝不轻易动架构。具体到实操我的判断流程是这样的打开慢查询日志看拿到的是哪些SQL。对每个慢SQL执行explain看是不是走了索引。如果该走索引没走先改SQL写法改了还不行再看索引设计是否合理。如果每一条慢SQL都走到了索引上但延迟依然高数据库负载依然高这时候再考虑参数调优和架构改造。这套流程我用了很多年帮我在很多项目里避免了“业务没到那一步但提前背上了分库分表的债”的尴尬局面。2. 索引优化从数据结构到落地的完整拆解索引是MySQL优化里性价比最高的一环。很多时候一条SQL从几秒降到几十毫秒就只是加了一个索引这么简单。但索引也是出错最多的环节建多了会拖垮写入建错了毫无意义建歪了甚至导致优化器选错执行计划。2.1 为什么B树能成为InnoDB索引标配先说索引的数据结构。InnoDB引擎使用的是B树而不是其他所谓“更高级”的结构。理解了这个选择后面看什么“最左前缀”、“回表”、“覆盖索引”才能顺着逻辑走通。B树的几个特性跟磁盘IO是高度相关的。每一层节点的数据量是有严格上限的树高度一般控制在2到4层。一次索引查找意味着2到4次磁盘IO这基本上就是MySQL能快速响应的物理基础。那B树和B树最大的区别在哪B树的叶子节点和非叶子节点都存数据而B树只有叶子节点存数据非叶子节点只存索引键值。这意味着同样一个4KB的磁盘页B树能放更多的索引项树更矮查询路径更短。另一个关键点是B树的叶子节点之间通过双向链表连在一起这对范围查询极其友好。你要查一条范围内所有记录只需要找到起点然后顺着链表一路往后扫不需要像B树那样反复回溯父节点。所以你在MySQL里做范围查询、排序、分组本质都是在利用B树的物理结构特性。了解了这一点你就能理解为什么联合索引有“最左前缀”的规则了——因为联合索引在B树里是按照索引定义字段的顺序先排序第一列再排序第二列再排序第三列。如果你跳过第一个字段直接查第二个字段那B树的全局有序性就失效了优化器只能放弃这个索引或者退化成索引扫描。2.2 索引设计的几个核心步骤我建索引从来不是拍脑袋。每一张表的索引设计我都会走一遍下面这个流程。先盘点这张表的全部查询维度。查询维度不等于表字段。比如订单表用户查订单是一个维度商家查订单是一个维度平台运营查订单又是一个维度。不同维度的查询频率、实时性要求、数据量级都不同不可能一个索引通吃。然后看每个维度的选择性。选择性高不高取决于字段的重复程度。比如状态字段无非是待支付、已支付、已发货、已完成几类选择性极低。这种字段单独建索引意义不大因为索引一旦扫出来还是大量数据回表成本不比全表扫描低多少。相反用户ID、订单号这类字段选择性极高放在联合索引第一位几乎总能让查询走索引。接着才是设计联合索引。联合索引的设计里有个关键思路范围条件放后面等值条件放前面。为什么还是B树的排序规则。等值条件在树里定位就是直接落到一个节点而范围条件一旦触发后续字段的排序就已经失效了。所以如果你有个查询是“用户ID且创建时间”那联合索引应该是(user_id, created_at)而不是反过来。反过来之后每个用户的时间段数据在索引里并不是连续存放的查询效率大打折扣。最后还要看回表成本。InnoDB的主键索引也就是聚簇索引其叶子节点存的是整行数据。二级索引的叶子节点存的是主键值你要查的字段不在索引里就得拿主键回表找数据。如果一条查询命中了几万行哪怕走索引了回表几万次速度也快不了。此时可以考虑把查询需要的字段全部放进索引里——这就是覆盖索引。比如SELECT user_id, order_status FROM order_table WHERE user_id ?建一个(user_id, order_status)的联合索引就能让查询完全走索引无需回表。注意覆盖索引不是越多越好。索引占空间每次写入都要更新所有相关索引写入性能会下降。一个表二级索引数量我个人建议控制在5个以内索引字段总数控制在10个以内。超出这个范围优化收益会被写放大抵消。2.3 索引失效场景与隐式转换的危害索引建到位了不等于SQL就一定能走索引。有一类特别容易中招的问题就是索引字段上的隐式类型转换。最经典的场景user_id字段在表里是varchar类型但你在SQL里写了WHERE user_id 123456那么这个条件查询时MySQL会把varchar字段隐式转换成数字类型再做比较。一旦字段加了函数或隐式转换索引就失效了。你排查半天发现explain走的是全表扫描但看字段又有索引其实就是这么个不起眼的坑。类似的还有在索引字段上做计算、函数操作比如WHERE DATE(created_at) 2024-01-01、WHERE amount 10 100这些写法。只要索引字段本身就是函数的参数优化器就拿索引没辙。另一个实操里常见的服务端编码问题——字符集不一致。如果两张表的关联字段一个用utf8一个用utf8mb4即使内容完全相同在做join时也会发生隐式转换导致关联查询不走索引。排查这类问题的标准动作就是explain。重点关注type列从好到差大致顺序是const、eq_ref、ref、range、index、ALL。看到ALL基本就说明全表扫了。看到index也要警惕它是全索引扫描虽然遍历的是索引树但对大表而言同样很慢。2.4 索引维护与线上监控很多人把索引建完就抛之脑后这是不对的。业务的查询模式是会变化的几个月前的核心查询可能已经变成了边缘功能而新功能新查询连索引都没有。索引监控这块我常用的方法很简单打开慢查询日志周期性地分析Top N慢SQL发现字段上有查询但没索引就评估增加索引。然后定期去看performance_schema里的统计数据找出那些建了但从来没被使用过的索引评估删除。删除无用索引这件事要特别小心。生产环境删除索引之前先在测试环境用真实查询模式跑一遍确认没有依赖。我自己曾经吃过一次亏删掉了一个当时认为无用的索引结果第二周某个边缘报表开始慢排查半天才发现是那个索引支撑的。索引碎片也值得关注。频繁的更新和删除会在B树里留下空洞。用OPTIMIZE TABLE可以重建表但注意锁表时间可能很长线上操作务必放在低峰期。数据量大的表我更推荐通过Online DDL配合插件或者直接在维护窗口做千万别白天在业务高峰期跑。3. SQL优化慢查询定位与改写实战索引聊完来说SQL优化。这里我给一个比较反直觉的建议优先改业务逻辑其次改SQL语法再次才是改SQL细节。很多慢SQL之所以慢是因为业务设计上就要求数据库干了一件不必在数据库里干的事。3.1 慢查询日志的开启与解读慢查询日志是定位SQL问题的入口。线上环境一般建议开启但要控制阈值。很多公司的慢查询阈值设的是1秒我见过某些核心系统把阈值设到100毫秒甚至更低的。阈值越高越容易漏掉那些“看起来不慢但量极大”的SQL。一条500毫秒的查询不慢但如果每秒被调用了几百次那堆积起来就是数据库的灾难。配置文件里加上以下几行然后重启生效slow_query_log 1 slow_query_log_file /var/log/mysql/slow.log long_query_time 0.5 log_queries_not_using_indexes 1log_queries_not_using_indexes这个参数会把所有没用索引的查询都记进日志。在业务低峰期开一段时间你会惊讶地发现多少你以为是正常的情况其实根本没走索引。拿到慢日志后用mysqldumpslow或者pt-query-digest这类工具聚合分析按总耗时排序优先处理累计时间最长的那些SQL。优化完一批隔一周再看慢日志会有非常明显的曲线变化。3.2 explain执行计划的正确阅读姿势关于explain我只看几个关键的列type、key、rows、Extra。很多人执着于type是不是有system、const这种惊人级别其实在真实OLTP场景大部分正常查询做到ref、range就已经很不错了。关键是key列看看优化器实际用的索引跟你预期的是不是同一个。如果key是NULL就是没走索引立刻返回去查索引和SQL写法。Extra列是宝藏也是最容易被忽略的。看到Using filesort说明排序没走索引要看看排序字段是不是在索引里。看到Using temporary说明用了临时表group by或者distinct出了问题。看到Using index恭喜你这就是前面说的覆盖索引效率最高。Using index condition则是索引条件下推在InnoDB里把一部分WHERE条件在存储引擎层过滤掉了也不差。但如果你看到Using where; Using index别高兴太早通常意味着虽然走了覆盖索引但WHERE条件不是全索引覆盖可能还有回表层级的损耗。实操心得调参前先看慢日志看慢日志前先看业务曲线。凌晨三四点不慢的SQL不代表业务高峰不慢因为锁等待和IO竞争在不同负载下的表现完全不同。优化后的SQL一定要在压测环境里回放真实流量别只测单条。3.3 深分页优化延迟关联与游标方案MySQL深分页问题几乎是所有做To B系统的团队都会遇到的。你写个LIMIT 100000, 20感觉就是取20条记录数据库却要扫描前面10万行然后丢掉。越往后翻页越慢最后直接超时。这里我推荐两种方案。第一种是延迟关联。思路是先利用覆盖索引快速定位到主键ID再用主键去关联获取全行数据。举个例子-- 优化前深分页慢 SELECT * FROM orders WHERE status 1 ORDER BY id LIMIT 100000, 20; -- 优化后延迟关联 SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders WHERE status 1 ORDER BY id LIMIT 100000, 20 ) t ON o.id t.id;优化前的SQL即使是全表顺序扫描加limit也得先扫到10万行。优化后最内层用覆盖索引先扫主键拿到20个id之后再回表拿整行。如果status字段有索引内层查询全程在索引树里走扫描成本大幅降低。第二种是游标式分页。适用于翻页很深的场景比如用户翻了几百页。直接用WHERE id 上一页的最大id来定位而不是OFFSET。这种方案在To B里的工单列表、会话记录里很常见配合order by id或者order by创建时间字段性能是很稳定的。前提是业务能接受“不能跳页”这种交互模式只允许上一页下一页。这两种方案我都在生产环境里用过。延迟关联适合改造成本低的场景游标式适合产品需求本身支持顺序浏览的场景。如果产品非要支持几千页跳页说句实话单靠MySQL已经很难扛了要么限制最大翻页深度要么上搜索引擎类组件。3.4 关联查询、子查询与聚合的取舍关联查询这块我踩过的坑比较多。MySQL的优化器对子查询的处理历史上是有过很多问题的特别是IN子查询很多时候会把子查询物化再跟外层做关联。在数据量不确定、统计信息不准的时候执行计划经常不是最优的。我现在的习惯是join能写就尽量用join而不是IN或EXISTS嵌套子查询。做join之前明确驱动表是哪张。驱动表最好是过滤后结果集最小的那张小表驱动大表是基本准则。join字段两侧都要求有索引否则用不上索引关联算法直接退化成Block Nested Loop慢得你想骂人。尽量避免三层以上的深join。如果业务上有这种需求考虑在应用层分多次查询用内存组装结果。聚合查询是另一个容易中招的场景。COUNT()在InnoDB里是没法像MyISAM那样走计数的必须一条条数。大表的COUNT()就别做了日常统计用定时任务跑一个汇总表。SUM、AVG这类聚合排查一下是不是把不该聚合的数据也拉进来了。一个常见的错误是先用子查询把明细拉出来然后外层做聚合这样就会让临时表膨胀。能用聚合函数解决的问题不要先查明细再聚合。4. 分库分表别轻易碰但要懂怎么碰分库分表这个话题我得泼一盆冷水大部分业务根本不配不需要分库分表。这句话可能有点刺耳但我见过的真实场景里很多“不得不分库”的案例其实是慢SQL加烂索引导致的假性瓶颈硬拆了之后问题更多。但话又说回来业务量真的上去了不分库分表就是死路一条。这时候你对这个技术领域的理解深度直接决定了拆完之后的稳定性。4.1 分库分表的触发信号什么样的业务才需要分库分表我倾向于用数据量和写入并发两个维度做判断。数据量维度单表数据量超过千万级别时我们就开始评估。这不是死标准表结构简单、查询走索引且无大聚合的千万级照样跑得好好的。表结构复杂、字段多、查询模式多样可能500万就已经吃力了。写入并发维度看磁盘IO和主从延迟。如果主库写入的IOPS已经接近磁盘上限binlog同步延迟越来越明显说明单实例写入能力已经逼近物理极限。这时候不是优化能解决的得从架构上分摊写入压力。还有一个容易被忽视的信号单表体积已经大到影响日常运维。比如ALTER TABLE加字段要跑很长时间备份恢复要几个小时这时候哪怕业务还能撑你也该考虑拆分或者分区了。4.2 分片键选择与路由策略分库分表第一步就是选分片键。这一选择直接决定后面所有查询能不能路由到正确的库表。选错了整个架构就是灾难。我的原则是选业务里最高频、最强制、最稳定的那个查询条件。比如订单业务按用户ID分就是最常见的。用户查自己的订单天然就能定位到一个分片。但用户ID分片意味着运营后台想查某时间段全平台订单就变成全分片广播查询了那怎么办要么再维护一个索引表要么运营查询走离线数仓。这是分库分表必须接受的代价不存在完美分片键。重点聊一下分片键能不能改的问题。分片键一旦定了后期修改的成本高到可怕。所以决策时一要想到未来三年二要看业务主流程的查询范式。如果业务里主查询同时存在两个维度且频率相当比如C端用户查单和B端商家查单都很高频那就得考虑双分片或者引入映射表方案。路由策略上业界常用的有三种。Range分片按ID区间切分后续扩展方便但容易造成热点库新的数据都往最后一个分片挤。Hash分片按分片键哈希取模数据分布均匀这是目前用得最多的方式。还有一致性哈希为了扩容时最小化数据迁移。简单业务用取模预判未来会频繁扩容的业务我建议一开始就设计好虚拟桶方案避免后期全量迁移。4.3 常见的分库分表中间件选型市面上主流的方案其实就两大类。一类是客户端中间件典型代表就是ShardingSphere-JDBC。它是以jar包的形式作用于应用层SQL解析、路由、改写都在客户端完成对DBA来说是透明的不需要额外部署Proxy层。优点是性能好因为不需要经过代理转发层直连数据库。缺点是多语言支持比较麻烦每个应用都得接入SDK而且分布式事务、分布式主键这些都得自己适配。另一类是中间代理层比如ShardingSphere-Proxy以及各种公司自研的proxy层。这类方案对应用是透明的业务代码基本不动只需把连接串指向proxy就行。优点是接入成本低、多语言友好、集中管理分片规则。缺点是多一跳网络开销性能上限受限于proxy层本身的处理能力代理集群本身又成了新的高可用关注点。选择上我的建议是中小团队、改造时间紧、业务比较简单的优先客户端方案它简单直接少一套组件少一套故障。如果公司有专职的DBA团队多系统都依赖同一套分库分表能力那就上代理层集中管控。还有个不能回避的问题是分布式主键。分库之后数据库自增主键就不可用了因为每个分片各自自增会重复。业界常用的有Snowflake算法、Leaf或者你用UUID转数字。Snowflake类的方案要点在于保证workerId唯一和时钟回拨问题。时钟回拨会导致生成重复ID这个问题在生产环境真实发生过一定要在方案里处理时钟同步的回退策略。4.4 分片后的数据迁移与平滑扩容分库分表不只是“拆”一下就完事了数据迁移和扩容才是真正让无数团队头疼的事。初次从单库拆到多库最稳妥的方式是双写迁移。解释一下流程旧库继续承载读写同时新库开启同步把增量数据往新库写入。同时写一个数据同步任务把全量历史数据按分片规则写入新库。跑完一轮之后对比新旧数据做校验和修补最后把读流量灰度切到新库观察一段时间后再把写流量切过来下线旧库。这里有个细节容易被忽略迁移脚本里面要控制写入速率。你如果傻乎乎地开一堆线程并行写很容易把新库的IO打爆导致新库延迟飙升又反过来影响双写阶段。我一般会把全量任务限速观察目标库的磁盘IO和主从延迟动态调整并发数。扩容更麻烦。如果用的是普通的hash取模一旦分片数变了模数就变了几乎全量数据都要迁移。所以现在很多团队在初期设计时就引入了虚拟桶或者一致性哈希。虚拟桶方案的思路是先在哈希环或者桶空间上维护一个大的虚拟节点集合每个虚拟节点再映射到物理库表。扩容时只需迁移部分虚拟节点的数据迁移总量可控。这次教训我印象很深一上来就图省事用ID取模4结果一年后业务翻了4倍扩容到8个库数据搬了整整一个周末。如果当时预留虚拟桶可能一个晚上就搞定了。5. 实践中的典型问题与排查实录最后一章我把过去几年在线上环境里遇到的一些有代表性的问题整理出来。这些问题是面试题里永远碰不到的但都是生产环境实实在在的坑。5.1 案例一一个不该走的全表扫描某次线上告警某个核心接口的P99延迟从80ms飙升到3秒。查慢日志发现一条SQL占了一半的慢查询次数SELECT * FROM user_info WHERE mobile 139xxxx1234;表里明明在mobile字段上有唯一索引但explain显示走的是全表扫描。仔细一看字段定义mobile是varchar(20)但表字符集是utf8而连接参数里character_set_client是utf8mb4。这一层隐式转换就让索引彻底失效了。排查步骤其实不复杂show create table看字符集show variables检查连接字符集确认不一致后把表和连接统一到utf8mb4。索引立刻生效。5.2 案例二分页越翻越慢用户直接弃用某个管理后台的列表页数据量大概是160万行。当用户翻到第1800页时SQL执行时间已经超过5秒。我检查后发现这条SQL不但有深分页问题还连着三张表join每张表的过滤字段都建了索引但因为深分页导致扫描的数据量太大。优化方案就是前面说的延迟关联把LIMIT条件从主查询挪到子查询内层让内层先在索引树里快速定位主键ID。改造后翻到第3000页的耗时也在80ms左右。5.3 案例三分库分表之后事务和join怎么搞某订单系统按用户ID分成了16个库拆完之后发现两个问题。第一一个用户的一个订单流程需要更新订单表、扣库存表、写流水表但订单表和流水表按用户分片没问题库存表按商品维度分片跨在两个不同的分片上单库事务就保证不了了。第二运营后台想按店铺查近30天订单结果分片键是用户ID查询要广播到16个库再在应用层合并。这两个问题的最终解法一个是用柔性事务方案通过消息队列做最终一致性引入本地消息表另一个是把运营后台的查询迁到分析型数据库通过binlog同步过去从源头杜绝大查询打在线库。这条路走下来给我最大的感受是分库分表后最大的成本从来不是迁移本身而是业务代码里所有“理所当然”的单库能力全部需要重新设计。5.4 一些容易忽略的参数配置优化不只是表结构和SQL的事几个关键MySQL参数往往被漏掉。innodb_buffer_pool_size是最重要的一个。建议设置为可用内存的60%-75%如果服务器内存是64G那么设置40G-48G是合理的。这个值决定了InnoDB能把多少数据页和索引页缓存在内存里。设太小磁盘IO直线上升设太大留给OS和其他进程的内存不够可能导致swap。innodb_flush_log_at_trx_commit在数据安全性和写入性能之间有个取舍。为1是最安全的但每次事务提交都要刷盘写入TPS会明显下降。对转账这类资金敏感业务必须用1对非关键业务比如日志类、流水类可以调到2这样每秒才做一次刷盘性能提升相当可观。max_connections和thread_cache_size也要配合调整。连接数开得太大不是好事每个连接都会占用内存和CPU反而引发资源争抢。正确思路是限制连接数配合应用层连接池合理设置。连接池上线一般设为20到50就够具体看业务IO时长。写在最后这次先聊到这儿吧。回头看看我做MySQL优化这几年最大的感受是技术方案的取舍永远是在可维护性、扩展性和当前业务规模之间做平衡。索引、SQL、分库分表这三板斧前两板是日常第三板是重武器。不到万不得已不要轻易上重武器但上了之后就得把路由、迁移、事务、运维体系的账都算清楚。实操里我还有个习惯每次优化完都会顺手在文档里记录当时的业务瓶颈指标、优化手段、改动前后的SQL耗时对比以及三个月后的效果跟踪。这套记录办法帮我避开了无数“过几天就忘记当初为什么这么改”的坑。建议你也在自己的项目里养成这个习惯比看任何方法论都管用。后面如果大家有兴趣我可以再写一篇关于线上故障应急处理和数据库高可用切换的实操复盘。 
长河君 · 2026/10/17 1:42:13 · 阅读 1.2k MySQL优化实战：索引、SQL与分库分表的取舍与落地
MySQL优化这事儿说实话已经被聊烂了。网上随便一搜全是“索引失效十大场景”、“SQL优化黄金法则”、“分库分表避坑指南”这类文章。但真正在线上折腾过的人都知道文章写得再花哨落不了地就是废话。你照着文章去建索引结果发现写请求变慢了你辛辛苦苦把SQL从3秒优化到0.3秒结果业务一上量又打回原形你费劲千辛万苦把表拆了最后发现join做不了、事务也没法保证——这些都是真实发生过的。这篇帖子我不打算给你讲什么高深的理论就从一个实际干过几年MySQL运维和架构的人的角度把索引、SQL和分库分表这三件事从头到尾掰开揉碎了讲。核心思路只有一个先搞清楚问题在哪一层再决定用哪把刀。很多人的毛病是想一步到位直接分库分表结果发现根本没到那一步还有些人天天在研究SQL写法却忽略了索引本身就是错的。这篇文章适合谁看适合那些公司业务正在增长MySQL开始出现性能瓶颈但你还没到非上分布式中间件不可阶段的开发者。也适合那些已经分了库分了表但发现为什么还是慢、为什么数据对不上的兄弟。我会把每个关键决策背后的理由讲清楚附上可以直接抄作业的排查思路和参数最后再分享几个线上踩坑的复盘记录。1. 全貌梳理索引、SQL与分库分表的分工与边界在动手优化之前先把这三件事的边界和关系理清楚。搞不清楚边界很容易陷入“拿着锤子看什么都像钉子”的陷阱。1.1 三层优化的定位到底是什么索引、SQL、分库分表本质上是在处理不同层的成本问题。索引和SQL优化解决的是单机单库内部的计算成本问题。说白了就是同样在一张表里查数据怎么让存储引擎少干活。索引决定了数据怎么被快速定位SQL决定了优化器拿到你写的语句之后能不能走上去。这两者其实是配套的索引建得好但SQL写法让优化器不走索引等于白建SQL写得再规范但表上压根没有合适的索引优化器也只能硬着头皮全表扫。分库分表解决的则是单机物理上限的问题。当你的数据量或者写入并发已经超出了单个MySQL实例的承载能力不管索引多完美、SQL多高效都无济于事。这时候不是优化的问题是架构的问题。很多人经常犯的错误是混淆这两个层面。比如表里已经有几亿行数据还在费劲地调SQL企图让它跑进100毫秒。这往往徒劳无功。反过来才几百万行数据的表三天两头有人提分库分表方案这也是典型的过度设计。1.2 优化顺序的决策逻辑我个人的习惯永远遵循一个顺序先SQL后索引再架构。这个顺序看起来跟标题的顺序不一样但实际工作上必须颠倒过来。理由很简单SQL和索引的调整成本低、周期短、风险可控而分库分表的改造成本极高一旦上了这条船意味着你的代码要全面重构事务模型要变join要拆扩容要规划。能通过SQL和索引解决的问题绝不轻易动架构。具体到实操我的判断流程是这样的打开慢查询日志看拿到的是哪些SQL。对每个慢SQL执行explain看是不是走了索引。如果该走索引没走先改SQL写法改了还不行再看索引设计是否合理。如果每一条慢SQL都走到了索引上但延迟依然高数据库负载依然高这时候再考虑参数调优和架构改造。这套流程我用了很多年帮我在很多项目里避免了“业务没到那一步但提前背上了分库分表的债”的尴尬局面。2. 索引优化从数据结构到落地的完整拆解索引是MySQL优化里性价比最高的一环。很多时候一条SQL从几秒降到几十毫秒就只是加了一个索引这么简单。但索引也是出错最多的环节建多了会拖垮写入建错了毫无意义建歪了甚至导致优化器选错执行计划。2.1 为什么B树能成为InnoDB索引标配先说索引的数据结构。InnoDB引擎使用的是B树而不是其他所谓“更高级”的结构。理解了这个选择后面看什么“最左前缀”、“回表”、“覆盖索引”才能顺着逻辑走通。B树的几个特性跟磁盘IO是高度相关的。每一层节点的数据量是有严格上限的树高度一般控制在2到4层。一次索引查找意味着2到4次磁盘IO这基本上就是MySQL能快速响应的物理基础。那B树和B树最大的区别在哪B树的叶子节点和非叶子节点都存数据而B树只有叶子节点存数据非叶子节点只存索引键值。这意味着同样一个4KB的磁盘页B树能放更多的索引项树更矮查询路径更短。另一个关键点是B树的叶子节点之间通过双向链表连在一起这对范围查询极其友好。你要查一条范围内所有记录只需要找到起点然后顺着链表一路往后扫不需要像B树那样反复回溯父节点。所以你在MySQL里做范围查询、排序、分组本质都是在利用B树的物理结构特性。了解了这一点你就能理解为什么联合索引有“最左前缀”的规则了——因为联合索引在B树里是按照索引定义字段的顺序先排序第一列再排序第二列再排序第三列。如果你跳过第一个字段直接查第二个字段那B树的全局有序性就失效了优化器只能放弃这个索引或者退化成索引扫描。2.2 索引设计的几个核心步骤我建索引从来不是拍脑袋。每一张表的索引设计我都会走一遍下面这个流程。先盘点这张表的全部查询维度。查询维度不等于表字段。比如订单表用户查订单是一个维度商家查订单是一个维度平台运营查订单又是一个维度。不同维度的查询频率、实时性要求、数据量级都不同不可能一个索引通吃。然后看每个维度的选择性。选择性高不高取决于字段的重复程度。比如状态字段无非是待支付、已支付、已发货、已完成几类选择性极低。这种字段单独建索引意义不大因为索引一旦扫出来还是大量数据回表成本不比全表扫描低多少。相反用户ID、订单号这类字段选择性极高放在联合索引第一位几乎总能让查询走索引。接着才是设计联合索引。联合索引的设计里有个关键思路范围条件放后面等值条件放前面。为什么还是B树的排序规则。等值条件在树里定位就是直接落到一个节点而范围条件一旦触发后续字段的排序就已经失效了。所以如果你有个查询是“用户ID且创建时间”那联合索引应该是(user_id, created_at)而不是反过来。反过来之后每个用户的时间段数据在索引里并不是连续存放的查询效率大打折扣。最后还要看回表成本。InnoDB的主键索引也就是聚簇索引其叶子节点存的是整行数据。二级索引的叶子节点存的是主键值你要查的字段不在索引里就得拿主键回表找数据。如果一条查询命中了几万行哪怕走索引了回表几万次速度也快不了。此时可以考虑把查询需要的字段全部放进索引里——这就是覆盖索引。比如SELECT user_id, order_status FROM order_table WHERE user_id ?建一个(user_id, order_status)的联合索引就能让查询完全走索引无需回表。注意覆盖索引不是越多越好。索引占空间每次写入都要更新所有相关索引写入性能会下降。一个表二级索引数量我个人建议控制在5个以内索引字段总数控制在10个以内。超出这个范围优化收益会被写放大抵消。2.3 索引失效场景与隐式转换的危害索引建到位了不等于SQL就一定能走索引。有一类特别容易中招的问题就是索引字段上的隐式类型转换。最经典的场景user_id字段在表里是varchar类型但你在SQL里写了WHERE user_id 123456那么这个条件查询时MySQL会把varchar字段隐式转换成数字类型再做比较。一旦字段加了函数或隐式转换索引就失效了。你排查半天发现explain走的是全表扫描但看字段又有索引其实就是这么个不起眼的坑。类似的还有在索引字段上做计算、函数操作比如WHERE DATE(created_at) 2024-01-01、WHERE amount 10 100这些写法。只要索引字段本身就是函数的参数优化器就拿索引没辙。另一个实操里常见的服务端编码问题——字符集不一致。如果两张表的关联字段一个用utf8一个用utf8mb4即使内容完全相同在做join时也会发生隐式转换导致关联查询不走索引。排查这类问题的标准动作就是explain。重点关注type列从好到差大致顺序是const、eq_ref、ref、range、index、ALL。看到ALL基本就说明全表扫了。看到index也要警惕它是全索引扫描虽然遍历的是索引树但对大表而言同样很慢。2.4 索引维护与线上监控很多人把索引建完就抛之脑后这是不对的。业务的查询模式是会变化的几个月前的核心查询可能已经变成了边缘功能而新功能新查询连索引都没有。索引监控这块我常用的方法很简单打开慢查询日志周期性地分析Top N慢SQL发现字段上有查询但没索引就评估增加索引。然后定期去看performance_schema里的统计数据找出那些建了但从来没被使用过的索引评估删除。删除无用索引这件事要特别小心。生产环境删除索引之前先在测试环境用真实查询模式跑一遍确认没有依赖。我自己曾经吃过一次亏删掉了一个当时认为无用的索引结果第二周某个边缘报表开始慢排查半天才发现是那个索引支撑的。索引碎片也值得关注。频繁的更新和删除会在B树里留下空洞。用OPTIMIZE TABLE可以重建表但注意锁表时间可能很长线上操作务必放在低峰期。数据量大的表我更推荐通过Online DDL配合插件或者直接在维护窗口做千万别白天在业务高峰期跑。3. SQL优化慢查询定位与改写实战索引聊完来说SQL优化。这里我给一个比较反直觉的建议优先改业务逻辑其次改SQL语法再次才是改SQL细节。很多慢SQL之所以慢是因为业务设计上就要求数据库干了一件不必在数据库里干的事。3.1 慢查询日志的开启与解读慢查询日志是定位SQL问题的入口。线上环境一般建议开启但要控制阈值。很多公司的慢查询阈值设的是1秒我见过某些核心系统把阈值设到100毫秒甚至更低的。阈值越高越容易漏掉那些“看起来不慢但量极大”的SQL。一条500毫秒的查询不慢但如果每秒被调用了几百次那堆积起来就是数据库的灾难。配置文件里加上以下几行然后重启生效slow_query_log 1 slow_query_log_file /var/log/mysql/slow.log long_query_time 0.5 log_queries_not_using_indexes 1log_queries_not_using_indexes这个参数会把所有没用索引的查询都记进日志。在业务低峰期开一段时间你会惊讶地发现多少你以为是正常的情况其实根本没走索引。拿到慢日志后用mysqldumpslow或者pt-query-digest这类工具聚合分析按总耗时排序优先处理累计时间最长的那些SQL。优化完一批隔一周再看慢日志会有非常明显的曲线变化。3.2 explain执行计划的正确阅读姿势关于explain我只看几个关键的列type、key、rows、Extra。很多人执着于type是不是有system、const这种惊人级别其实在真实OLTP场景大部分正常查询做到ref、range就已经很不错了。关键是key列看看优化器实际用的索引跟你预期的是不是同一个。如果key是NULL就是没走索引立刻返回去查索引和SQL写法。Extra列是宝藏也是最容易被忽略的。看到Using filesort说明排序没走索引要看看排序字段是不是在索引里。看到Using temporary说明用了临时表group by或者distinct出了问题。看到Using index恭喜你这就是前面说的覆盖索引效率最高。Using index condition则是索引条件下推在InnoDB里把一部分WHERE条件在存储引擎层过滤掉了也不差。但如果你看到Using where; Using index别高兴太早通常意味着虽然走了覆盖索引但WHERE条件不是全索引覆盖可能还有回表层级的损耗。实操心得调参前先看慢日志看慢日志前先看业务曲线。凌晨三四点不慢的SQL不代表业务高峰不慢因为锁等待和IO竞争在不同负载下的表现完全不同。优化后的SQL一定要在压测环境里回放真实流量别只测单条。3.3 深分页优化延迟关联与游标方案MySQL深分页问题几乎是所有做To B系统的团队都会遇到的。你写个LIMIT 100000, 20感觉就是取20条记录数据库却要扫描前面10万行然后丢掉。越往后翻页越慢最后直接超时。这里我推荐两种方案。第一种是延迟关联。思路是先利用覆盖索引快速定位到主键ID再用主键去关联获取全行数据。举个例子-- 优化前深分页慢 SELECT * FROM orders WHERE status 1 ORDER BY id LIMIT 100000, 20; -- 优化后延迟关联 SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders WHERE status 1 ORDER BY id LIMIT 100000, 20 ) t ON o.id t.id;优化前的SQL即使是全表顺序扫描加limit也得先扫到10万行。优化后最内层用覆盖索引先扫主键拿到20个id之后再回表拿整行。如果status字段有索引内层查询全程在索引树里走扫描成本大幅降低。第二种是游标式分页。适用于翻页很深的场景比如用户翻了几百页。直接用WHERE id 上一页的最大id来定位而不是OFFSET。这种方案在To B里的工单列表、会话记录里很常见配合order by id或者order by创建时间字段性能是很稳定的。前提是业务能接受“不能跳页”这种交互模式只允许上一页下一页。这两种方案我都在生产环境里用过。延迟关联适合改造成本低的场景游标式适合产品需求本身支持顺序浏览的场景。如果产品非要支持几千页跳页说句实话单靠MySQL已经很难扛了要么限制最大翻页深度要么上搜索引擎类组件。3.4 关联查询、子查询与聚合的取舍关联查询这块我踩过的坑比较多。MySQL的优化器对子查询的处理历史上是有过很多问题的特别是IN子查询很多时候会把子查询物化再跟外层做关联。在数据量不确定、统计信息不准的时候执行计划经常不是最优的。我现在的习惯是join能写就尽量用join而不是IN或EXISTS嵌套子查询。做join之前明确驱动表是哪张。驱动表最好是过滤后结果集最小的那张小表驱动大表是基本准则。join字段两侧都要求有索引否则用不上索引关联算法直接退化成Block Nested Loop慢得你想骂人。尽量避免三层以上的深join。如果业务上有这种需求考虑在应用层分多次查询用内存组装结果。聚合查询是另一个容易中招的场景。COUNT()在InnoDB里是没法像MyISAM那样走计数的必须一条条数。大表的COUNT()就别做了日常统计用定时任务跑一个汇总表。SUM、AVG这类聚合排查一下是不是把不该聚合的数据也拉进来了。一个常见的错误是先用子查询把明细拉出来然后外层做聚合这样就会让临时表膨胀。能用聚合函数解决的问题不要先查明细再聚合。4. 分库分表别轻易碰但要懂怎么碰分库分表这个话题我得泼一盆冷水大部分业务根本不配不需要分库分表。这句话可能有点刺耳但我见过的真实场景里很多“不得不分库”的案例其实是慢SQL加烂索引导致的假性瓶颈硬拆了之后问题更多。但话又说回来业务量真的上去了不分库分表就是死路一条。这时候你对这个技术领域的理解深度直接决定了拆完之后的稳定性。4.1 分库分表的触发信号什么样的业务才需要分库分表我倾向于用数据量和写入并发两个维度做判断。数据量维度单表数据量超过千万级别时我们就开始评估。这不是死标准表结构简单、查询走索引且无大聚合的千万级照样跑得好好的。表结构复杂、字段多、查询模式多样可能500万就已经吃力了。写入并发维度看磁盘IO和主从延迟。如果主库写入的IOPS已经接近磁盘上限binlog同步延迟越来越明显说明单实例写入能力已经逼近物理极限。这时候不是优化能解决的得从架构上分摊写入压力。还有一个容易被忽视的信号单表体积已经大到影响日常运维。比如ALTER TABLE加字段要跑很长时间备份恢复要几个小时这时候哪怕业务还能撑你也该考虑拆分或者分区了。4.2 分片键选择与路由策略分库分表第一步就是选分片键。这一选择直接决定后面所有查询能不能路由到正确的库表。选错了整个架构就是灾难。我的原则是选业务里最高频、最强制、最稳定的那个查询条件。比如订单业务按用户ID分就是最常见的。用户查自己的订单天然就能定位到一个分片。但用户ID分片意味着运营后台想查某时间段全平台订单就变成全分片广播查询了那怎么办要么再维护一个索引表要么运营查询走离线数仓。这是分库分表必须接受的代价不存在完美分片键。重点聊一下分片键能不能改的问题。分片键一旦定了后期修改的成本高到可怕。所以决策时一要想到未来三年二要看业务主流程的查询范式。如果业务里主查询同时存在两个维度且频率相当比如C端用户查单和B端商家查单都很高频那就得考虑双分片或者引入映射表方案。路由策略上业界常用的有三种。Range分片按ID区间切分后续扩展方便但容易造成热点库新的数据都往最后一个分片挤。Hash分片按分片键哈希取模数据分布均匀这是目前用得最多的方式。还有一致性哈希为了扩容时最小化数据迁移。简单业务用取模预判未来会频繁扩容的业务我建议一开始就设计好虚拟桶方案避免后期全量迁移。4.3 常见的分库分表中间件选型市面上主流的方案其实就两大类。一类是客户端中间件典型代表就是ShardingSphere-JDBC。它是以jar包的形式作用于应用层SQL解析、路由、改写都在客户端完成对DBA来说是透明的不需要额外部署Proxy层。优点是性能好因为不需要经过代理转发层直连数据库。缺点是多语言支持比较麻烦每个应用都得接入SDK而且分布式事务、分布式主键这些都得自己适配。另一类是中间代理层比如ShardingSphere-Proxy以及各种公司自研的proxy层。这类方案对应用是透明的业务代码基本不动只需把连接串指向proxy就行。优点是接入成本低、多语言友好、集中管理分片规则。缺点是多一跳网络开销性能上限受限于proxy层本身的处理能力代理集群本身又成了新的高可用关注点。选择上我的建议是中小团队、改造时间紧、业务比较简单的优先客户端方案它简单直接少一套组件少一套故障。如果公司有专职的DBA团队多系统都依赖同一套分库分表能力那就上代理层集中管控。还有个不能回避的问题是分布式主键。分库之后数据库自增主键就不可用了因为每个分片各自自增会重复。业界常用的有Snowflake算法、Leaf或者你用UUID转数字。Snowflake类的方案要点在于保证workerId唯一和时钟回拨问题。时钟回拨会导致生成重复ID这个问题在生产环境真实发生过一定要在方案里处理时钟同步的回退策略。4.4 分片后的数据迁移与平滑扩容分库分表不只是“拆”一下就完事了数据迁移和扩容才是真正让无数团队头疼的事。初次从单库拆到多库最稳妥的方式是双写迁移。解释一下流程旧库继续承载读写同时新库开启同步把增量数据往新库写入。同时写一个数据同步任务把全量历史数据按分片规则写入新库。跑完一轮之后对比新旧数据做校验和修补最后把读流量灰度切到新库观察一段时间后再把写流量切过来下线旧库。这里有个细节容易被忽略迁移脚本里面要控制写入速率。你如果傻乎乎地开一堆线程并行写很容易把新库的IO打爆导致新库延迟飙升又反过来影响双写阶段。我一般会把全量任务限速观察目标库的磁盘IO和主从延迟动态调整并发数。扩容更麻烦。如果用的是普通的hash取模一旦分片数变了模数就变了几乎全量数据都要迁移。所以现在很多团队在初期设计时就引入了虚拟桶或者一致性哈希。虚拟桶方案的思路是先在哈希环或者桶空间上维护一个大的虚拟节点集合每个虚拟节点再映射到物理库表。扩容时只需迁移部分虚拟节点的数据迁移总量可控。这次教训我印象很深一上来就图省事用ID取模4结果一年后业务翻了4倍扩容到8个库数据搬了整整一个周末。如果当时预留虚拟桶可能一个晚上就搞定了。5. 实践中的典型问题与排查实录最后一章我把过去几年在线上环境里遇到的一些有代表性的问题整理出来。这些问题是面试题里永远碰不到的但都是生产环境实实在在的坑。5.1 案例一一个不该走的全表扫描某次线上告警某个核心接口的P99延迟从80ms飙升到3秒。查慢日志发现一条SQL占了一半的慢查询次数SELECT * FROM user_info WHERE mobile 139xxxx1234;表里明明在mobile字段上有唯一索引但explain显示走的是全表扫描。仔细一看字段定义mobile是varchar(20)但表字符集是utf8而连接参数里character_set_client是utf8mb4。这一层隐式转换就让索引彻底失效了。排查步骤其实不复杂show create table看字符集show variables检查连接字符集确认不一致后把表和连接统一到utf8mb4。索引立刻生效。5.2 案例二分页越翻越慢用户直接弃用某个管理后台的列表页数据量大概是160万行。当用户翻到第1800页时SQL执行时间已经超过5秒。我检查后发现这条SQL不但有深分页问题还连着三张表join每张表的过滤字段都建了索引但因为深分页导致扫描的数据量太大。优化方案就是前面说的延迟关联把LIMIT条件从主查询挪到子查询内层让内层先在索引树里快速定位主键ID。改造后翻到第3000页的耗时也在80ms左右。5.3 案例三分库分表之后事务和join怎么搞某订单系统按用户ID分成了16个库拆完之后发现两个问题。第一一个用户的一个订单流程需要更新订单表、扣库存表、写流水表但订单表和流水表按用户分片没问题库存表按商品维度分片跨在两个不同的分片上单库事务就保证不了了。第二运营后台想按店铺查近30天订单结果分片键是用户ID查询要广播到16个库再在应用层合并。这两个问题的最终解法一个是用柔性事务方案通过消息队列做最终一致性引入本地消息表另一个是把运营后台的查询迁到分析型数据库通过binlog同步过去从源头杜绝大查询打在线库。这条路走下来给我最大的感受是分库分表后最大的成本从来不是迁移本身而是业务代码里所有“理所当然”的单库能力全部需要重新设计。5.4 一些容易忽略的参数配置优化不只是表结构和SQL的事几个关键MySQL参数往往被漏掉。innodb_buffer_pool_size是最重要的一个。建议设置为可用内存的60%-75%如果服务器内存是64G那么设置40G-48G是合理的。这个值决定了InnoDB能把多少数据页和索引页缓存在内存里。设太小磁盘IO直线上升设太大留给OS和其他进程的内存不够可能导致swap。innodb_flush_log_at_trx_commit在数据安全性和写入性能之间有个取舍。为1是最安全的但每次事务提交都要刷盘写入TPS会明显下降。对转账这类资金敏感业务必须用1对非关键业务比如日志类、流水类可以调到2这样每秒才做一次刷盘性能提升相当可观。max_connections和thread_cache_size也要配合调整。连接数开得太大不是好事每个连接都会占用内存和CPU反而引发资源争抢。正确思路是限制连接数配合应用层连接池合理设置。连接池上线一般设为20到50就够具体看业务IO时长。写在最后这次先聊到这儿吧。回头看看我做MySQL优化这几年最大的感受是技术方案的取舍永远是在可维护性、扩展性和当前业务规模之间做平衡。索引、SQL、分库分表这三板斧前两板是日常第三板是重武器。不到万不得已不要轻易上重武器但上了之后就得把路由、迁移、事务、运维体系的账都算清楚。实操里我还有个习惯每次优化完都会顺手在文档里记录当时的业务瓶颈指标、优化手段、改动前后的SQL耗时对比以及三个月后的效果跟踪。这套记录办法帮我避开了无数“过几天就忘记当初为什么这么改”的坑。建议你也在自己的项目里养成这个习惯比看任何方法论都管用。后面如果大家有兴趣我可以再写一篇关于线上故障应急处理和数据库高可用切换的实操复盘。