做 MySQL 优化这些年我发现一个很尴尬的现象很多人一遇到数据库慢第一反应就是加缓存上分库分表结果缓存击穿把后端打挂分库分表引入一堆分布式事务的坑最后不得不回滚重来。实际上绝大多数慢查询问题根源都出在索引没建好、SQL 写法不配合优化器这两件事上。真正的分库分表是最后不得不上的一步不是优化万能药。这篇文章我会从一次真实的慢查询排查讲起把索引设计、SQL 改写、执行计划解读、分库分表这些内容串起来每一步都说明为什么这样做以及我在实操中踩过的坑、验证过的方案。内容偏向 MySQL 8.0部分命令在 5.7 上略有差异但不影响整体思路。适合被慢查询折磨过的开发者也适合正在规划数据库架构的技术负责人。看完之后你至少能回答自己三个问题索引到底该怎么建SQL 怎么写优化器才买账什么情况下才真的需要分库分表1. 慢查询定位不靠猜靠日志和执行计划说话先别急着改 SQL。线上数据库出问题的时候第一件事永远是把慢查询找出来把执行计划拉出来看。没有这两样东西所有的优化都是拍脑袋。1.1 慢查询日志先确认慢的长什么样MySQL 的慢查询日志是排查性能问题的起点。我见过很多团队连这个都没开DBA 被业务方催得焦头烂额只能靠猜。正确的做法是提前把慢查询日志打开并且设置一个合理的阈值。-- 查看当前慢查询配置 SHOW VARIABLES LIKE slow_query_log; SHOW VARIABLES LIKE long_query_time; -- 开启慢查询日志8.0 支持在线修改 SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL log_queries_not_using_indexes ON;long_query_time设成 1 秒比较合理生产环境可以设成 0.5 秒再低就会刷屏。log_queries_not_using_indexes这个参数很容易被忽略它会把没走索引的查询也记录到慢日志里这往往才是性能隐患的预兆。慢日志文件里每一条记录都包含执行时间、锁等待时间、返回行数、扫描行数以及完整的 SQL 语句。拿到这些之后不要急着看语句本身先记住两个数字Rows_sent和Rows_examined。如果Rows_examined比Rows_sent大好几个数量级说明这条 SQL 扫描了大量数据但只返回了很少的结果典型的索引缺失或者索引失效。1.2 EXPLAIN执行计划才是数据库的真实意图拿到慢 SQL 之后下一步就是看执行计划。EXPLAIN SELECT * FROM orders WHERE user_id 123 ORDER BY created_at DESC LIMIT 10;执行计划里我最关心四个字段type、key、rows、Extra。type访问类型从好到坏大致是consteq_refrefrangeindexALL。看到ALL就是全表扫描基本可以断定这条 SQL 有问题。key实际用到的索引。如果为 NULL说明没走任何索引。rows预估扫描行数这个数字越大越危险。Extra出现Using filesort或者Using temporary的时候要提高警惕前者代表排序没能用到索引后者代表查询过程中创建了临时表。有一个很容易踩的坑type显示index不代表索引生效了。index表示全索引扫描相当于把整棵索引树遍历一遍虽然比全表扫描好一点但数据量大的时候依然很慢。真正健康的执行计划type至少应该是range最好是ref或者const。我在处理一次线上告警的时候发现一条查询的type是indexrows显示几十万行。乍一看走了索引但实际是把整个索引树扫了一遍因为查询条件和索引列的顺序对不上。这种问题光看key字段根本发现不了必须结合rows和Extra一起判断。注意执行计划是基于当前表统计信息的估算值不是真实值。如果表数据变化剧烈建议先执行ANALYZE TABLE更新统计信息否则执行计划可能误导你。2. 索引设计从 B 树结构看懂为什么有的索引快、有的索引废索引不是万能的但没有索引是万万不能的。很多人建索引就是 哪个字段查询多就给哪个字段加索引这种做法运气成分太大。要想真正设计好索引先得明白 B 树是怎么组织数据的。2.1 B 树的高度和扫描代价MySQL 的 InnoDB 存储引擎使用 B 树作为索引结构。B 树的所有数据都存放在叶子节点非叶子节点只存索引键和指针。这带来一个关键特性树的高度通常很低三到四层就能支撑千万级数据量。每次从根节点走到叶子节点一次 I/O 对应一层。树高为 3 的时候定位一条记录最多需要三次磁盘 I/O这就是索引快的原因。但 B 树快有一个前提查询能走索引。如果查询条件无法匹配索引优化器只能选择全表扫描逐页读取所有数据页I/O 次数就完全不可控了。我在给团队做分享的时候经常打一个比方索引就像一本书的目录全表扫描就像从头到尾翻书找一句话走索引则是查目录、翻页码、直接定位。但目录也有讲究——如果一本书的目录只标了章节号没标页码你还是得翻半天。联合索引的字段顺序就是目录条目的排序规则。2.2 主键索引与普通索引的区别主键索引聚簇索引和普通索引二级索引在 InnoDB 中的结构完全不同。主键索引的叶子节点存的是整行数据所以通过主键查询是最快的访问路径一次 B 树查找就能拿到全部数据列。普通索引的叶子节点存的是主键值查到主键之后还需要回表——回到主键索引里再查一次才能拿到完整的行数据。这个差距在高并发场景下非常明显。比如查询SELECT * FROM orders WHERE status 1如果status上有普通索引MySQL 会先查status索引得到一堆主键再回表查完整数据。如果结果集很大回表次数就很多。但如果查询只需要status和主键写成SELECT id FROM orders WHERE status 1那就用到了覆盖索引连回表都省了。覆盖索引这个技巧经常被忽略但收益极高。实际上不需要记得多复杂只需要记住一点查询列表里包含的字段越多覆盖索引越难满足回表的概率越大。这也是为什么不要动不动就SELECT *的底层原因。2.3 联合索引最左前缀原则和字段顺序联合索引是最容易踩坑的地方。索引(a, b, c)可以加速查询条件的组合包括a、a,b、a,b,c但无法加速单独的b或c查询。这就是最左前缀原则。在一起配合工作的多个字段中区分度高的字段放前面。区分度指的是字段中不同值的比例比如性别字段只有男女两个值区分度极低用户 ID 基本每条记录都不同区分度极高。还有一个很多人忽略的点联合索引中后置字段可以用于排序和分组但不参与缩小扫描范围的定位。比如索引(user_id, created_at)查询WHERE user_id 123 ORDER BY created_at DESC时created_at已经天然有序不需要额外排序直接从user_id123的范围内倒序取数据就行这就是索引排序。但如果查询是WHERE user_id 123 ORDER BY amount DESCamount不在索引里MySQL 就得先查出user_id123的所有记录再排序很容易触发Using filesort。我实际测试过一组数据一张 500 万行的订单表查询某个用户最近 10 条订单。建了(user_id, created_at)联合索引之后耗时从原来的 800 毫秒降到 3 毫秒执行计划里的Using filesort也消失了。效果立竿见影但前提是把字段顺序摆对。3. SQL 实战优化让优化器心甘情愿走索引索引建得再漂亮SQL 写法不配合优化器照样白搭。这一节我整理了日常排查中最常见的几类 SQL 优化场景全部来自真实生产环境。3.1 深分页问题limit 越大越慢我翻车了一次分页是最容易暴露性能问题的场景。传统写法SELECT * FROM orders WHERE user_id 123 ORDER BY created_at DESC LIMIT 100000, 20;这条 SQL 的执行逻辑是扫描到第 100000 行之前的所有满足条件的记录然后全部丢弃只返回最后 20 行。扫描行数等于偏移量加返回行数偏移量越大越慢。我接手过一个项目后台管理系统的订单列表翻到第 1000 页的时候接口响应时间已经超过 5 秒。第一反应是加缓存但订单数据更新频繁缓存命中率极低。后来定位到就是深分页导致的。优化的核心方式之一是基于游标分页。业务上记录上次查询结果的最后一条created_at和id下一页查询用条件过滤SELECT * FROM orders WHERE user_id 123 AND (created_at, id) (2024-01-15 10:00:00, 56789) ORDER BY created_at DESC, id DESC LIMIT 20;这种写法让 MySQL 直接走索引定位到游标位置只扫描 20 条耗时从秒级降到毫秒级。代价是分页的页码跳转功能会受到限制但大多数业务场景里上一页/下一页比跳到第 300 页用得频繁得多完全值得。还有一种常见手段是延迟关联SELECT * FROM orders JOIN ( SELECT id FROM orders WHERE user_id 123 ORDER BY created_at DESC LIMIT 100000, 20 ) AS tmp ON orders.id tmp.id;子查询里只索引覆盖了id然后关联回原表取完整数据。缺点是扫描量依然存在没有游标分页那么干净但在业务必须支持页码跳转的时候是很好的折中。3.2 隐式类型转换和函数运算索引是怎么失效的索引失效这个问题网上说法很多但大多数失效其实是优化器的正确选择。比如字段上有函数运算索引就帮不上忙了。-- 假设 phone 字段有索引但存储时带区号 SELECT * FROM users WHERE SUBSTRING(phone, 3) 13812345678;即使phone上有索引这条查询也不会走索引因为在 B 树里索引键是完整的电话号码SUBSTRING(phone, 3)的结果没有一个固定的排序位置优化器只能放弃索引全表扫描。正确的写法是改写条件让字段保持原始形态SELECT * FROM users WHERE phone LIKE CONCAT(86-, 13812345678);隐式类型转换更隐蔽。如果字段是VARCHAR类型但 SQL 里用了整数值MySQL 会把字段转为数值再比较一样导致索引失效。-- phone 为 VARCHAR 类型下面这条查询极可能全表扫描 SELECT * FROM users WHERE phone 13812345678;正确的写法是SELECT * FROM users WHERE phone 13812345678;我见过不止一次线上事故都是因为 ORM 框架把字符串参数自动转成了整数导致本应毫秒级的查询变成全表扫描把数据库 CPU 打满。排查时用EXPLAIN看一眼type字段是ref还是ALL马上就能判断。另一个常见的坑是在列上使用函数操作日期。比如WHERE DATE(created_at) 2024-01-15改写为created_at 2024-01-15 00:00:00 AND created_at 2024-01-16 00:00:00就能走索引了。这个规则放之四海皆准不要让索引列参与运算或函数转换让数据库能用原始的索引键去做范围查找。3.3 慢 SQL 优化从 N1 查询到批量操作N1 查询是 ORM 框架时代的高发问题。业务代码先查了 100 条订单再循环 100 次查询每条订单的明细数据库就要执行 101 条 SQL。优化方式很简单一次性把需要的数据查出来-- 原来循环查询 100 次 SELECT * FROM order_items WHERE order_id ?; -- 优化后批量查询 SELECT * FROM order_items WHERE order_id IN (1001, 1002, ..., 1100);批量查询还需要注意IN列表的长度。MySQL 对IN列表的优化有限一次性塞入上万条 ID 反而可能导致执行计划退化。经验值控制在 500 到 1000 之间比较稳妥超过这个量就分批处理。另外EXISTS和IN的选择在 MySQL 8.0 里没有绝对的优劣优化器会自动做等价改写。真正要注意的是别在IN子查询里返回大量数据比如IN (SELECT id FROM orders WHERE ...)如果子查询返回几十万行性能一定崩。这种情况下改成JOIN 分组去重更适合。3.4 统计类 SQL 的优化思路先缩小范围再聚合统计类 SQL 的常见写法是直接对全表做聚合比如统计每个用户今年的订单总额SELECT user_id, SUM(amount) FROM orders WHERE created_at 2024-01-01 GROUP BY user_id;这条 SQL 如果created_at有索引WHERE阶段会缩小数据范围但GROUP BY仍然要处理大量数据。优化方向有两个一是确保user_id上有索引二是如果业务对实时性要求不高可以提前用汇总表在后台定时维护统计结果查询时直接查汇总表而不是原始表。我在实际项目里常用的方案是创建一张订单日汇总表每天凌晨跑批任务把前一天的订单按用户聚合写入汇总表。线上查询只查汇总表响应时间从原来的几秒降到几十毫秒。这种空间换时间的思路属于业务层面的优化比硬在 SQL 上死磕实用得多。4. 分库分表该不该做、什么时候做、怎么做分库分表是重型手段一旦做了就很难回头。我不建议在数据量还没到瓶颈的时候就主动分库分表但也不建议等数据库真的崩了再临时补救。关键是要识别出触发条件。4.1 分库分表的触发条件根据一线运维经验当以下指标中出现一两条的时候就需要认真考虑分库分表了单表数据量超过千万级别且查询性能明显下降数据库连接数达到瓶颈应用频繁报Too many connections单库磁盘 I/O 持续高水位加大硬件配置后改善不明显单表写入 QPS 过高锁竞争严重很多团队在单表数据量过亿之后才开始动刀其实已经有点晚了。千万级数据量时开始规划分库分表周期会比较从容。当然不是说一千万就一定要分如果查询条件都走了索引单表 5000 万依然可以跑得动这个阈值需要结合真实业务场景实测。4.2 垂直拆分与水平拆分怎么选垂直拆分的核心是按业务域划分。比如订单库拆成订单基础库、订单扩展库、订单日志库或者把一个大的用户表拆成用户基本信息表和用户扩展属性表。垂直拆分的优点是实施难度相对低能缓解单库的 I/O 压力但无法解决单表数据量过大的问题。水平拆分是真正面对单表数据量太大的解法。把一张订单表按照某个分片键散到多个库多张表里。分表数量不是随便定的要结合数据增长预测和单表容量。举个例子预计订单数据一年增长 1000 万单表控制在 500 万左右体验最佳那就分 4 张表未来两年就是 8 张。分表键的选择需要围绕最核心的查询维度比如大部分查询都是按user_id查订单就按user_id分片。4.3 分片键设计选错就是灾难分片键是整个分库分表方案中最重要的决策。选错了直接导致数据倾斜某个分片数据量爆炸其他分片空闲。最常见的选择有按用户 ID 分片用户维度查询友好但订单维度跨片查询成本高按订单 ID 分片订单全局唯一但按用户查会打遍所有分片按时间分片适合归档类数据但不适合高频访问的热数据理想的情况是分片键本身就是核心查询条件。如果业务同时存在按用户查订单和按订单号查订单两种高频场景可以考虑双写或者冗余索引订单表按用户 ID 分片同时维护一张按订单号分片的映射表。分片算法上取模算法简单直观但扩展分片数时需要迁移数据。一致性哈希算法在增加分片时受影响的数据量更小但实现复杂度更高。4.4 数据迁移与双写方案分库分表改动大上线的时候不能直接切数据库。成熟的方案是双写迁移老库继续承担读写新库开始同步老库的增量数据业务代码把写操作同时发送到新库读操作暂时仍在老库历史数据通过批量任务迁入新库边迁移边校验条数和关键字段数据校验通过后把读流量逐步切到新库观察监控指标稳定运行一段时间后老库降级为只读备份最后下线这个过程中最容易出问题的坑是数据不一致。订单这类核心数据不能全量重新灌入最好在迁移任务里加一个数据对比步骤定期对比老库和新库相同主键的关键字段发现不一致自动报警。注意分库分表之后跨分片的JOIN、聚合查询、分页排序都会变得很麻烦。方案选型之前一定要充分评估业务里有没有大量的跨分片查询。如果有要么在设计分片键时绕开要么引入额外的汇总层。5. 一次线上慢 SQL 的完整排查复盘光讲理论和方案毕竟有点干这里放一个我之前处理过的真实案例完整走一遍从告警到优化的链路。5.1 现象凌晨接口超时数据库 CPU 飙到 90%当时线上的一个订单查询接口在凌晨出现超时监控平台报警显示数据库 CPU 使用率从 10% 急剧升到 90% 以上慢查询日志每分钟写入几十条。接口逻辑比较简单根据用户 ID 查最近 30 天的订单列表再关联订单明细表。初步排查时先把慢查询日志里出现频率最高的几条 SQL 捞出来基本上都是同一类SELECT * FROM oms_order WHERE user_id 123456 AND create_time DATE_SUB(NOW(), INTERVAL 30 DAY) ORDER BY create_time DESC LIMIT 20;5.2 定位问题出在索引区分度和排序字段上执行计划显示如下typeref理论上是正常的keyidx_user_id确实走了索引rows预估扫描 3.8 万行ExtraUsing filesort看到没有Using index condition且出现Using filesort基本判断是二级索引(user_id)筛出来的记录太多还要再次排序排序过程依赖临时表性能极差。继续深挖数据后发现这个用户是平台的连锁大客户每天产生几千条订单30 天的订单量超过 10 万条。虽然走了user_id索引但扫描行数太高再叠加filesort慢得理所当然。5.3 修复联合索引和覆盖索引双管齐下修复方案一是把普通索引升级为联合索引ALTER TABLE oms_order ADD INDEX idx_user_create (user_id, create_time);这个索引把用户筛选和排序统一起来ORDER BY create_time不再需要临时文件排序。修复方案二是把SELECT *改成了只查必要字段SELECT order_id, order_no, amount, status FROM oms_order WHERE user_id 123456 AND create_time DATE_SUB(NOW(), INTERVAL 30 DAY) ORDER BY create_time DESC LIMIT 20;配合拆掉不必要的查询列之后查询条件正好命中联合索引的前置列查询列表也尽量往覆盖索引的方向靠减少回表次数。优化后线上验证执行计划里Extra从Using filesort变成了空扫描行数从 3.8 万降到 255 行接口 P95 耗时从 1800 毫秒降到 80 毫秒数据库 CPU 回落到 15%这个案例最有价值的地方在于type用的是ref看着很健康但性能一样崩。所以看执行计划的时候一定把rows和Extra结合起来判断单看一个字段太容易误诊。6. 优化工具箱我平时常用的 MySQL 调优工具和命令最后分享一套我每周都在用的排查工具集。刚接触 MySQL 优化的同事照着这套来不容易走弯路。6.1 性能监控类SHOW ENGINE INNODB STATUS看最近一次的 InnoDB 死锁和性能相关事件SHOW PROCESSLIST看当前正在执行的 SQL重点关注State和Timeinformation_schema.TABLES查各表的行数和数据大小performance_schema8.0 的sys库里有很多现成的诊断视图比如sys.statement_analysis直接统计消耗最高的 SQL-- 查看消耗时间最多的前10条 SQL SELECT * FROM sys.statement_analysis ORDER BY avg_latency DESC LIMIT 10;6.2 执行计划分析类核心就是EXPLAIN。除了默认输出还可以加EXPLAIN ANALYZE8.0.18它会真实执行 SQL 并输出每一步的时间和行数对定位性能瓶颈非常直观。EXPLAIN ANALYZE SELECT * FROM oms_order WHERE user_id 123456;注意EXPLAIN ANALYZE会真实执行 SQL生产中如果对大数据量表执行要谨慎最好在只读从库上操作。6.3 备份与补数据类运维场景中经常会遇到需要批量更新或补数据的情况这时候mysqldump选对大表参数非常关键mysqldump -u user -p --single-transaction --quick --skip-lock-tables dbname table_name backup.sql--single-transaction在 InnoDB 下可以保证备份期间读到一致快照不锁表--quick避免一次性读取所有数据到内存尤其适合大表--skip-lock-tables防止备份期间阻塞线上写入6.4 GUI 工具与客户端实际排查中我用的比较多的工具是 Navicat 和 DBeaver前者适合日常操作后者开源免费适合团队统一标准化。命令行风格的话HeidiSQL也是一个轻量的选择导出 SQL 比较方便。工具是次要的最重要的还是脑子里有排查思路。给刚入门的读者一个建议工具的使用可以熟练之后再谈但EXPLAIN的每一列、慢日志的每一个字段值得花时间彻底弄清楚。这些才是真正陪着你解决每一次线上问题的基本功。7. 多说一句我的体会做 MySQL 优化这行最难的不是掌握索引原理或者分库分表方案而是建立先定位、再优化、最后验证的工作习惯。很多工程师看到慢查询第一反应就是改 SQL结果改了十版没效果其实连慢在哪都不知道。我在实际工作里养成了几个固定动作每周扫一次慢日志每月拉一次执行计划做对比把消耗最高的前 20 条 SQL 拿出来过一遍。大部分性能隐患在这个频率下都能提前暴露完全不用等到线上告警。如果你的项目正处在数据量高速增长的阶段一定要尽早安排索引评审和 SQL 审查。等到单表几千万行、分库分表方案已经进退两难的时候再优化的成本和风险就完全不是一个量级了。这套排查方法论我从 5.7 用到 8.0从电商到金融项目都验证过希望也能帮你少走一些弯路。