做MySQL优化这些年我最深的感受是很多团队不是不会优化而是把优化做成了玄学。索引建了一大堆慢查询却一点没少SQL看着没啥问题执行计划一放出来就是全表扫描数据量涨到几千万的时候又开始纠结要不要拆库拆表怎么拆才不踩坑。这篇文章不打算讲那些永远正确的口号而是围绕索引、SQL调优和分库分表这三块把背后的原理、判断依据和实操路径一次说透。适合谁看如果你正在维护一个用户量增长很快的业务库或者刚接手一个慢到怀疑人生的老系统又或者在设计新系统时想提前把数据架构想清楚这篇文章应该能给你一个相对完整的思考框架。1. 索引不背锅也不邀功先把B树这笔账算明白很多优化文章一上来就让你加索引但索引到底是什么、为什么有的查询加了索引反而更慢这些关键问题反而没人讲。索引本质上是一种牺牲写性能换取读性能的数据结构用了B树实现目的就一个减少磁盘IO。1.1 聚簇索引、回表与覆盖索引的成本模型InnoDB存储引擎下每个表都有一个聚簇索引默认建在主键上。聚簇索引的叶子节点存的是整行数据也就是说数据本身就是按主键顺序组织起来的。而我们在别的字段上建的二级索引叶子节点只存两样东西索引列的值和主键值。这就引出一个关键概念——回表。假设有一条查询SELECT name, email FROM users WHERE phone 13800001111;如果phone上没有索引那只能是全表扫描把整张表的每一行都翻一遍。如果phone上有二级索引MySQL会先沿着phone索引找到对应的主键值再拿着主键值回聚簇索引里找到完整数据行这就是回表。回表不是免费的。每次回表都是一次随机IO数据量大了之后性能损耗会非常明显。所以就有了覆盖索引这个优化手段如果查询需要的所有列都包含在某个二级索引里那MySQL就不需要回表直接在二级索引上就能拿到全部结果。举个例子SELECT phone, name FROM users WHERE phone 13800001111;如果建了联合索引(phone, name)这个查询走到二级索引就能拿到phone和name不需要回表。这就是执行计划里Extra列显示Using index的含义。我自己的判断习惯是回表次数越少越好能用覆盖索引就别select *是索引优化的第一原则。尤其在高并发查询场景每次select * 多出来的回表开销叠加起来是很可观的。1.2 为什么我只是多加了一个条件索引就失效了这是我被问得最多的一类问题我的查询明明带了索引字段为什么执行计划显示全表扫描换句话说不是索引没用是SQL的写法让索引用不上了。最常见的几个坑对索引列用了函数操作比如WHERE YEAR(create_time) 2024索引在create_time上但函数包裹之后索引顺序被破坏B树没法按区间定位。隐式类型转换比如varchar类型的phone字段查询时传了一个数字WHERE phone 13800001111MySQL会隐式把字符串转成数字再做比较索引依然失效。前导模糊查询WHERE name LIKE %张三%因为B树是按前缀排序的你从中间开始匹配自然没法定向查找。这些坑背后其实是同一件事MySQL要用上索引前提是能根据索引列的顺序做等值判断或区间扫描。任何破坏这个顺序的写法都会让优化器放弃索引。从成本模型去理解就通了优化器会估算全表扫描和走索引的代价。反过来看如果你发现一个查询走了全表扫描却没到不可忍受的程度很可能是因为数据量还不够大或者返回行数占全表比例过高优化器认为走索引还不如直接扫全表。这时候你要担心的不是索引失效而是这个查询本身的设计是否合理。2. 联合索引设计实战把最左前缀规则用出效果联合索引是MySQL优化里最容易被用错、也最值得花时间设计的部分。很多人建联合索引的习惯是哪个字段常用就放前面这个思路经常出事。2.1 组合索引的匹配链条与设计顺序联合索引(a, b, c)本质上创建了三个索引(a)、(a, b)、(a, b, c)。也就是说你不需要为a单独再建一个索引了这是联合索引省空间的逻辑。但匹配是有规则的——最左前缀原则查询条件必须从联合索引的第一列开始连续匹配跳过了中间的列后面的列就用不上索引了。举个例子SELECT * FROM orders WHERE user_id 1001 AND status 1;如果索引是(status, user_id)那么user_id作为第二列在查询条件里跳过了第一列status这个索引就完全用不上会退化成全表扫描。如果把索引设计成(user_id, status)那条件里既有第一列的等值匹配又有第二列的等值匹配效果最好。具体设计顺序我的经验是分三步先找等值查询条件查询里用的是还是范围、、BETWEEN等值条件的列应该放在索引前列因为只有等值匹配才能最大程度缩小扫描范围而且不会因为后续列的范围条件阻断。再看区分度区分度高的列放前面。比如status字段只有两三个枚举值区分度极低放在第一列会导致索引扫描范围过大和全表扫描差别不大而user_id区分度极高一个值对应不了多少行放前面能快速收敛。考虑排序和分组如果查询里有ORDER BY或GROUP BY联合索引的列顺序如果能匹配上排序需求能避免Using filesort。这么做的好处不只是快还能让索引覆盖更多查询场景减少建冗余索引的冲动。2.2 订单中心场景下的索引设计过程拿一个典型的订单表来说。业务上最常见的查询是某个用户查自己的订单列表按下单时间倒序后台管理系统查某个店铺某段时间内的订单还有客服通过订单号直接查详情。表结构简化一下CREATE TABLE orders ( id BIGINT PRIMARY KEY, order_no VARCHAR(32) NOT NULL, user_id BIGINT NOT NULL, shop_id BIGINT NOT NULL, status TINYINT NOT NULL, amount DECIMAL(10,2) NOT NULL, create_time DATETIME NOT NULL, KEY idx_user_create(user_id, create_time), KEY idx_shop_status(shop_id, status), UNIQUE KEY uk_order_no(order_no) ) ENGINEInnoDB;看到为什么这么建了吗用户查自己的订单列表条件带user_id排序用create_time索引(user_id, create_time)既能过滤出该用户的订单又能按时间顺序读取避免额外排序。运营后台按店铺和状态筛选用(shop_id, status)。订单号走唯一索引精确命中不需要考虑前缀。一个常见的反面教材是一上来就给status建单列索引。status只有3个值过滤完之后还是几百万条意义不大。除非这个表和某个高基数字段联合使用才可能体现出价值。这也是为什么我一直强调索引设计必须从业务查询出发而不是从表结构出发。先收集这个表要被哪些SQL查询、这些SQL的过滤条件是什么、排序规则是什么再倒推索引怎么建才能避免建了用不上的僵尸索引。3. SQL调优流水线从慢查询定位到执行计划解读索引设计得再好如果SQL本身写得稀烂也是一样白搭。这一章直接给出一套我日常必做的调优流程先让慢查询现形再逐条解读执行计划最后动手改写。3.1 慢查询日志与工具的规范化用法很多人排查性能问题是等用户反馈系统很卡了才去看这样效率太低。平时就该把慢查询日志打开让它替你盯着所有超过阈值时间的SQL。-- 开启慢查询日志 SET GLOBAL slow_query_log 1; SET GLOBAL long_query_time 1; -- 超过1秒算慢查询 SET GLOBAL log_queries_not_using_indexes 1; -- 没有走索引的查询也记下来long_query_time设1秒是个比较合理的起点业务高峰期可以临时调到0.5秒抓更细致的慢SQL。线上库建议用pt-query-digest这类工具做汇总分析它会按指纹聚合同类SQL直接告诉你哪一类SQL最频繁、累计耗时最长。我处理慢SQL的习惯是不要只盯着单条耗时最长的还要看总执行次数×平均耗时的乘积。一个平均200毫秒但每秒执行几百次的查询和一条偶尔出现却要20秒的查询两者的优先级完全不同。3.2 执行计划中那些误导性的信息定位到慢SQL之后第一件事就是看执行计划EXPLAIN SELECT * FROM orders WHERE user_id 1001 ORDER BY create_time DESC LIMIT 20;重点看这几列type访问类型从好到差大致是const eq_ref ref range index ALL。如果看到ALL就是全表扫描基本可以判定有问题。key实际用到的索引。如果为NULL说明没走索引。rows预估扫描行数。这个值越大查询越慢。Extra这里最容易藏坑。Using filesort表示需要额外排序Using temporary表示用了临时表Using index才是理想状态。一个很容易误导人的点是rows只是估算值不是实际值。有时候优化器估算偏差很大比如统计信息过期会导致它选错索引。遇到这类情况可以先ANALYZE TABLE刷新统计信息再看执行计划变化。另外Using temporary在大分页、多表关联排序时很常见这往往意味着MySQL要把一组中间结果放进内存或磁盘临时表里。出现这个标记优先检查能不能通过索引消除排序实在不行再考虑改写SQL。3.3 两个改写案例深分页与隐式转换先说深分页。LIMIT 1000000, 20这种写法是典型的性能杀手。MySQL虽然只返回20条但会先把前100万条都扫一遍然后扔掉耗时主要花在了这上面。改写方案有两种各有适用场景-- 方案一游标法适合按顺序翻页的场景 SELECT * FROM orders WHERE id 1000000 ORDER BY id LIMIT 20; -- 方案二延迟关联适合带复杂条件的场景 SELECT o.* FROM orders o INNER JOIN (SELECT id FROM orders WHERE user_id 1001 ORDER BY create_time DESC LIMIT 1000000, 20) tmp ON o.id tmp.id;游标法本质上是用上一页的最大ID做起点取下一页数据。它的好处是扫描范围永远只限于那20条但要接受一个条件业务上能放弃跳到指定页数这个交互。延迟关联的思路则是先在覆盖索引上完成过滤和排序只拿到少量主键ID再回表取完整数据行减少回表次数。再说隐式转换这也是我排查时经常碰到的问题。表里字段是varchar查询时却写成了数字-- 这种写法会让 uk_order_no 索引失效 SELECT * FROM orders WHERE order_no 202400112233;MySQL内部会把两边都转成浮点数来比较索引列套上转换函数之后B树的排序结构就被破坏了。修正很简单字符串就用字符串来查SELECT * FROM orders WHERE order_no 202400112233;我曾经帮某项目把一个报表页面的核心查询从12秒优化到0.3秒手段无非就是把深分页改成游标法、把隐式转换修掉、把联合索引的列顺序按等值条件优先排好。整个过程没有用到任何高级技巧全是这些基本功。4. 分库分表的决策逻辑拆前最后一问真的非拆不可吗数据库的问题不是所有都要靠分库分表解决。很多团队把分库分表当成包治百病的药结果拆完了引入一堆新问题运维复杂度直线上升。这一章我只讲一件事怎么判断该不该拆以及如果要拆怎么定拆分策略。4.1 单库单表撑不住的真实信号是什么分库分表的触发信号不能只看数据量到了几百万或者感觉有点慢了。更务实的判断标准是这几条单库的连接数成为瓶颈数据库连接数打满应用侧大量等待获取连接而不是SQL本身慢。这通常出现在读多写多的核心库上。单表数据量过大导致索引和维护成本失控B树的层级因为数据量增长而加深IO放大效应明显。比如一个单表2亿行即使走主键查询也要两三次IO才能定位数据这已经逼近硬件极限了。磁盘和备份窗口扛不住单实例数据量超过几个TB全量备份时间长得无法接受恢复时间目标自然也无法保证。写入吞吐量超单机上限单机写入瓶颈通常在几千到上万TPS取决于磁盘和配置业务增长后持续触顶只能拆分。如果只是慢查询优先考虑索引优化、SQL改写、读写分离、冷热数据归档这些方案的复杂度远低于分库分表还是先做掉这些手段再谈拆。一个很重要的前提是分库分表之后很多单机数据库里理所当然的能力都会失效——表关联、事务、唯一约束、复杂聚合都变得很难搞。所以只要没触碰到上述硬瓶颈别轻易动拆分的念头。4.2 垂直拆分与水平拆分的取舍分库分表分成两条路线垂直方向和水平方向。垂直拆分是把一张大表按业务域拆成多张表比如把订单基本信息、订单扩展字段、订单流水拆成三张表分属不同的库。它的核心价值是缓解单库的存储压力把大字段、低频访问的数据挪走让核心表的体积和访问热度降下来。但垂直拆分不会解决单表数据量持续增长的问题所以通常是第一步不是终点。水平拆分是把同一张表的数据按某个规则分散到多张表或多个库中每张表结构一样数据是全集的一个分片。这才是真正解决单表数据量过大的手段。两者怎么选我一般这样判断如果业务表之间的关联天然很少而且单库的连接数和I/O压力已经很大优先考虑垂直拆分先按业务边界把不同的模块拆到不同库。如果某一张核心表的数据量和写入量还在线性增长那就要在这个业务域内部做水平拆分了。4.3 分片键选型的三个约束水平拆分的核心是选对分片键。分片键决定了数据落在哪个分片上也会决定后续查询的效率。选型时我建议用三个约束去卡业务必须接受的查询模式绝大多数查询都要能带上这个键。比如订单场景用户查自己订单是最高频的查询选user_id做分片键用户维度查询天然只打一个分片。数据分布要均匀如果按店铺ID分片但店铺下单量差异巨大大店铺所在分片会很快成为热点其他分片却在浪费。这是分片不均匀导致的性能倾斜。分片键要稳定一旦数据按某个值路由业务上如果出现要修改这个值的需求就涉及跨分片的数据迁移很麻烦。最常见的错误是选了order_no做分片键但业务里90%的查询都是按user_id查列表。这样每次查询都变成全分片广播——把所有分片查一遍再合并结果性能和没拆之前差不多甚至更差。正确做法就是高频查询条件决定了分片键其他维度查询靠冗余表或搜索引擎兜底。5. 分库分表落地之后的连锁问题与迁移方案拆完表、建好分片规则工作才完成了一半。后面还会冒出一堆新问题最典型的是分布式ID、跨分片查询、数据迁移和扩容。这一章把这几件事一次性说清楚。5.1 分布式ID、跨分片查询与扩容困局分库分表后数据库自增主键就失效了因为你没法保证多个分片生成的主键全局唯一。业界主流方案有两个雪花算法和号段模式。雪花算法生成的ID是64位整数包含时间戳、机器ID和序列号趋势递增、全局唯一。但要注意配置好机器ID的分配规则否则在多实例部署时可能出现ID冲突。号段模式的思路是数据库里存一个号段比如从10001到20000应用每次取一段ID缓存到内存里自行分配用完再去取下一段。这个方案更可控也很常用。跨分片查询是另一个绕不开的坑。分片键能覆盖的场景还好一旦遇到非分片键的查询比如按订单号查订单详情就变成了所有分片都要查一遍然后汇总。解决办法通常是建一张全局映射表order_no - user_id先查映射表定位到分片再精确查询。也可以用搜索引擎做宽表同步让非分片键查询直接走搜索引擎。扩容问题更现实。最早的取模分片在扩容时非常疼原来10个分片现在扩到20个数据要大规模迁移。所以现在的实践一般用一致性哈希或者分段式分片——事先把物理分片和逻辑分片隔离物理扩容时尽量只影响迁移少量数据。5.2 双写迁移与数据校验的实践方案切分库分表最关键的一步是平滑迁移最怕的就是切完数据对不上。我最常用的一套流程是建一套新的分片库表结构和分片规则提前准备好。开启双写业务层写旧库的同时把同一份数据按分片规则写到新库。历史数据回放用数据迁移工具或自定义脚本把旧库的历史数据按分片键路由到新库期间要注意去重双写和回放会重复写入同一批数据。对账校验写一个校验脚本按分片键逐条比对旧库和新库的关键字段状态、金额、更新时间发现不一致的单独捞出来重放。灰度切读先让一部分流量读新库确认无异常后再逐步切全量最后下线双写逻辑。这个过程中还容易出问题的环节是增量数据的一致性。双写阶段如果新旧库各自拿到了不同的状态更新就会对不上账。我的经验是给每条数据加上update_time和版本号合并取最新的同时用binlog做增量补传保证两边最终一致。这几年实践下来我对分库分表的态度是能晚拆就晚拆能不拆就不拆必须拆的时候要接受复杂度转移这个现实。做了分库分表本来数据库帮你承担的事——事务、约束、聚合查询、自增主键——全部要搬到应用层自己解决。这不是一条轻松的路但数据规模到了一定的量级它又是一个绕不过去的出口。希望这篇文章能把索引、SQL调优和分库分表这几件事的取舍逻辑讲清楚让你在面对自己业务的时候少一些盲人摸象多一些有的放矢。
