做后端这些年我遇到过不少 MySQL 性能优化的场景。说实话刚入行时我也以为高性能优化就是加索引、调 buffer pool这两板斧直到有一次把一个明明只查单行的接口从 2 秒优化回 20 毫秒才意识到这条路的深浅。太多性能事故表面看是 SQL 慢根子上却是表结构、索引策略、参数配置甚至架构设计一起攒出来的问题。这篇内容我打算把 MySQL 高性能优化从排查思路、表结构设计、索引建设、SQL 改写、参数调优到架构取舍完整过一遍全是这些年实打实踩过的坑和验证过的方法适合刚从 CRUD 走向性能调优的开发者也适合正在被慢查询折磨的运维和 DBA 参考。1. 先别急着加索引从全局状态定位瓶颈源1.1 我习惯先看的三组全局指标很多同学一收到慢查询报警第一反应就是打开慢查询日志找出那条 SQL然后丢一句这里缺索引。这个动作没错但顺序错了。你得先搞清楚数据库到底是被什么压垮的是 CPU 计算瓶颈还是磁盘 IO 卡住或者是连接数被打满、锁等待堆积。方向错了加了索引也救不回来。我的排查入口永远是SHOW GLOBAL STATUS。重点看三组Threads_connected和Threads_running、QPS和TPS、Innodb_row_lock_current_waits和Innodb_buffer_pool_read_hit_rate。如果Threads_running长期超过 50说明有大量请求在排队等资源这时候先别管单条 SQL 慢不慢而是要确认是不是连接数已经逼近max_connections或者某几条大查询把 CPU 打满了。performance_schema和sys库是 MySQL 5.7 之后非常好用的分析工具。我经常直接查sys.statement_analysis看全实例的 SQL 耗时排行再通过sys.session看当前正在跑的会话到底卡在哪一步。这一步能帮你快速回答一个关键问题数据库整体是忙还是堵。1.2 慢查询日志的正确配置姿势慢查询日志是性能排查的基础设施但很多人的配置是有问题的。要么long_query_time设成了 10 秒等于把 99% 的问题都放过了要么日志文件开了半年不轮转把磁盘写爆了。我一般这样配置slow_query_log ONslow_query_log_file /var/log/mysql/slow.loglong_query_time 1。也就是说超过 1 秒的查询都记录下来。这里有个细节long_query_time的单位是秒支持小数比如 0.5 就代表 500 毫秒。如果你用的是云数据库或 MySQL 8.0还建议打开log_queries_not_using_indexes 1它会额外记录那些虽然快但没走索引的查询——很多潜在问题就是这样提前暴露的。日志分析我常用mysqldumpslow -s at -t 10 slow.log先看总耗时 TOP10再配合pt-query-digest做更细的聚合统计。要提醒的是分析慢日志不是把最慢那条拿出来改就完事而是要按平均耗时 × 执行次数来排优先级。一天执行一百万次、每次 800 毫秒的查询远比一天执行一次、每次 10 秒的查询值得优先处理。1.3 一张判断瓶颈的速查表拿不到现场数据的时候下面的对照表能帮你快速缩小范围。我在给团队做培训时也常靠它开场现象大概率瓶颈下一步动作CPU 使用率持续 90%Threads_running高大量计算密集的 SQL 或索引失效导致全表扫描抓sys.statement_analysis定位 TOP SQL 并看执行计划IO 等待高Innodb_buffer_pool_reads频繁增长缓冲池命中率低或磁盘读写能力不足查命中率评估innodb_buffer_pool_size换 SSDThreads_connected打满新连接报 too many connections连接池配置超标或存在慢请求长时间占用连接调连接池上限同时处理慢 SQL 释放连接Innodb_row_lock_current_waits持续非零行锁竞争激烈查sys.innodb_lock_waits找到持有锁的事务优化事务粒度整体 QPS 不高但响应时间很长可能是网络往返、跨机房调用或应用侧串行等待排查应用调用链而不是继续折腾数据库这条路径走完之后你才算有资格去动表结构和索引。2. 表结构设计很多性能问题在建模阶段就已经注定了2.1 数据类型选型的省钱哲学表结构设计阶段的一个隐性成本就是数据类型选大了。InnoDB 的数据页默认 16KB同样一页能装下多少行直接决定了扫描和缓存效率。你选的数据类型越大一页能放的行越少全表扫描要读的页就越多缓冲池能缓存的记录也越少。我这里整理过一张常用的选型对照TINYINT1 字节、SMALLINT2 字节、MEDIUMINT3 字节、INT4 字节、BIGINT8 字节。能用SMALLINT表示的枚举状态就别用INT能确定十年内不会超过 40 亿的计数INT足够别一上来就BIGINT。很多工具生成的建表语句默认全给INT甚至BIGINT这一步省下来的空间在几亿行的大表上就是几十 GB 的差距。字符串方面很多人混淆了CHAR和VARCHAR的适用场景。CHAR是定长适合长度稳定的字段比如 MD5、订单号VARCHAR是变长适合名称、描述这类长度波动大的内容。VARCHAR(255)和VARCHAR(5000)在存储上有区别在临时表排序和内存占用上的差异更明显用到ORDER BY时 VARCHAR 长度过大会直接导致临时表变大。有个容易忽略的点千万不要用浮点类型存金额。FLOAT和DOUBLE是近似存储求和会出细小的误差。金额一律用DECIMAL余额、价格这种核心数据经不起半点糊涂账。2.2 主键设计的页分裂问题主键选择看似简单实际上对写入性能的影响大得惊人。InnoDB 是聚簇索引组织表数据本身按主键顺序存储。如果你用的是 UUID 或者随机字符串做主键新插入的行会被随机分配到任意位置触发大量的页分裂和页重排。页分裂不仅拖慢当前写入还会让索引产生碎片增加后续扫描的 IO。最稳妥的方案还是自增主键或者趋势递增的分布式 ID雪花 ID 这一类。自增主键的新记录永远追加到 B 树的末尾写入顺序和物理顺序一致几乎不触发页分裂。但要注意自增主键在删除大量数据后会留下空洞表现为 ID 不连续这本身不是性能问题不用刻意去填补。如果你的业务确实需要 UUID 作为业务标识正确做法是把它设为普通唯一索引另外建一个自增主键。这样既满足了业务上的全局唯一需求又保住了聚簇索引的顺序写入优势。2.3 冗余字段、范式与 JOIN 的成本权衡三范式是教科书里的标准答案但在高并发场景里标准答案不一定是最优答案。每多一次 JOIN就意味着多一次索引回表和关联查询的 CPU 与 IO 开销。在订单列表这类高频查询场景我见过太多因为严格遵守范式而把查询拖垮的例子。一个务实的做法是冗余字段。比如订单表需要展示商品标题与其每次 JOIN 商品表不如在订单表里冗余一份商品标题字段。用户下单后商品标题一般不常变即使变了订单里的历史快照也本来就该保留当时的信息。类似的还有用户昵称、收货人信息这些字段读多写少冗余收益远大于维护成本。当然冗余不是无脑复制。关键判断标准是三条字段是否低频更新、是否属于查询高频字段、能否接受最终一致。三条都满足就可以放心冗余。如果冗余字段有更新入口记得通过应用层保证同一事务内同步更新或者用消息异步同步不要让数据口径出现不可控的分裂。3. 索引建设联合索引怎么建才有性价比3.1 联合索引的列序与最左前缀单一索引谁都会建真正拉开差距的是联合索引的列顺序。MySQL 里联合索引遵循最左前缀原则索引(a, b, c)能加速a、ab、abc三种查询条件但直接用b或c当条件用不上这个索引。所以设计联合索引时第一列必须是筛选度最高、最常出现在 WHERE 里的字段而且要把等值条件放在前面、范围条件放在后面。举个例子WHERE status 1 AND create_time 2024-01-01索引应该建(status, create_time)而不是反过来。因为一旦create_time走范围条件它后面的status列就没法在索引内继续精确匹配了这和你把等值列放前面正好相反。很多人每次遇到慢查询就加一个新索引结果一张表挂了大大小小十几个索引写入的时候每个索引都要维护插入和更新被拖慢。我的习惯是每张表的核心查询路径一般只需要两到三个联合索引覆盖绝大多数业务场景剩下的边缘查询宁可让它临时扫也别让索引泛滥。3.2 覆盖索引和索引下推把 IO 省到极致判断一个索引建得好不好最直观的标准就是看EXPLAIN里的Extra列。如果显示Using index说明查询所需的列全部在索引里不需要回表这就是覆盖索引。覆盖索引对高频查询的加速效果非常明显尤其是在大宽表场景一次查询少读十几KB的物理数据是常有的事。举个例子用户中心高频查询是根据用户名查用户手机号和头像 URL如果表上有联合索引(username, phone, avatar_url)整个查询就可以完全在索引里完成。代价是你把列都塞进索引后索引体积会变大所以覆盖索引只针对核心高频查询建别指望覆盖所有字段。索引下推ICP是 MySQL 5.6 引入的优化允许存储引擎在索引遍历过程中直接过滤掉不符合条件的记录减少回表次数。最常见的是联合索引里带了LIKE abc%这种前缀匹配后面的条件虽然不能用索引定位但可以通过下推在引擎层过滤。你会在EXPLAIN的Extra里看到Using index condition这通常是个好消息。3.3 哪些写法会让优化器放弃索引说到索引失效很多文章会列一堆场景但我觉得最值得记住的是底层逻辑索引本质是有序的 B 树一旦你在索引列上做了函数运算、类型转换或者破坏了前缀匹配优化器就没办法利用有序性了。最常见的是这四类第一LIKE %xxx前置百分号没法走索引第二在索引列上套函数比如WHERE DATE(create_time) 2024-01-01一定不走索引正确写法是create_time 2024-01-01 AND create_time 2024-01-02第三隐式类型转换比如字符串列被赋了整数条件或者数字列被赋了字符串条件第四用OR连接多个条件如果其中一个条件无法走索引整个查询很可能退化成全表扫描这种情况通常要拆成两个查询用UNION ALL合并或者改用IN。IS NULL和IS NOT NULL的索引行为很多文章说得太绝对。在 MySQL 8.0 里IS NULL通常是可以走索引的具体要看版本和优化器成本估算不要背结论遇到问题打开EXPLAIN验证一次比什么规则都可靠。4. SQL 改写实战用 EXPLAIN 反推优化策略4.1 EXPLAIN 关键字段速读执行计划是 SQL 优化的显微镜。我不会逐字段讲只说最关键的四个。type是访问类型从好到差大致是system、const、eq_ref、ref、range、index、ALL目标是让核心查询至少达到ref或range一旦出现ALL就是全表扫描得警惕。key表示实际用到的索引rows是优化器估算要扫描的行数这个数字能直观告诉你差距Extra则是各种提示信息Using filesort意味着排序没走索引Using temporary意味着用了临时表这两个都是优化信号。我通常的检查顺序是先看type再看rows最后看Extra。rows从几十万变成几十优化效果一目了然。注意 MySQL 8.0 还可以用EXPLAIN ANALYZE拿到真实执行时间比估算更准确特别是在排查rows估算严重不准的疑难问题时。4.2 深分页、子查询、Join 的改写案例深分页是业务系统最常见的性能杀手。LIMIT 100000, 20看起来很平常但它的执行方式是先扫描 100020 行再丢掉前 100000 行。数据量上来之后页数越深这条 SQL 越慢几百毫秒到几十秒都可能。我处理过最典型的一套优化方案叫延迟关联先利用主键或覆盖索引快速拿到目标页的主键 ID再用 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;如果业务场景是滚动翻页而不是跳页更彻底的做法是游标分页在 WHERE 里带上上一页最后一条记录的 IDWHERE id last_id ORDER BY id LIMIT 20这样每条 SQL 都只扫 20 行性能恒定。缺点是用户没法直接跳转到第 N 页需要产品层面权衡。子查询改写也是一个高频优化点。MySQL 对某些IN (SELECT ...)的优化并不总是尽如人意改成JOIN往往能显著减少扫描行数。反过来如果子查询的外表行数远小于内表行数EXISTS可能比IN更快。不要被必须用哪个的说法框死每条 SQL 打开 EXPLAIN 看一眼rows高下立判。4.3 优化器误判时的干预手段偶尔你会遇到一种情况索引明明存在优化器就是不走选择了一个rows大得离谱的全表扫描。原因通常是统计信息过期或者成本估算模型和你想象的完全不一样。优先做法是执行ANALYZE TABLE更新统计信息大概率能解决。如果还是走错可以尝试USE INDEX或FORCE INDEX手动干预或者调整查询写法让优化器更容易做出正确选择。有一条比较实用的经验把大表放到 JOIN 的左侧用小表作为驱动表必要的时候用STRAIGHT_JOIN固定表的连接顺序。干预优化器属于最后手段因为代码是活的统计信息和数据分布会变硬编码的FORCE INDEX可能在数据量变化后变成新的坑。用的时候要留下注释说明原因并且纳入后续巡检范围。5. 参数调优该动的和不该动的5.1 InnoDB 缓冲池与 redo logMySQL 性能调优里被念叨最多的参数就是innodb_buffer_pool_size。它是 InnoDB 用来缓存数据和索引的内存区域理论上是越大越好但有一个核心约束不能挤占操作系统和其他进程的内存。我的经验法则是配置物理内存的 60% 到 70%具体要看机器上还跑不跑别的服务。查命中率用这条语句SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_read%。命中率 Innodb_buffer_pool_read_requests / (Innodb_buffer_pool_read_requests Innodb_buffer_pool_reads)。长期低于 99% 就需要考虑扩容或优化查询了。如果你用的是 MySQL 5.7还可以设置多个innodb_buffer_pool_instances减少大并发下的内部锁竞争默认值在内存大时通常够用不用刻意调。redo log 的大小也容易被忽视。innodb_log_file_size默认值往往偏小写入高峰期日志频繁切换刷新脏页压力很大。我一般建议配置 1GB 到 4GB具体看写入吞吐。太大虽然减少日志切换但崩溃恢复时间会变长这是个需要权衡的点。innodb_flush_log_at_trx_commit是数据安全与性能的经典取舍设置为 1 每次提交都刷盘最安全但最慢设置为 2 每次提交只写操作系统缓存每秒刷一次盘性能好很多但主机掉电会丢最近一秒事务设置为 0 则完全交给后台线程每秒刷一次。金融、订单类业务必须用 1日志、统计类可以接受 2。这个参数不能拍脑袋要先问业务能容忍多少数据丢失。5.2 连接数与线程池max_connections不是越大越好。每一条连接都要占用线程和内存连接数开得太高线程切换开销会反过来拖垮性能。我看到过不少机器配置了max_connections 2000实际并发一上来CPU 全耗在上下文切换上SQL 反而连 100 QPS 都扛不住。更合理的方式是压测找到最优并发点比如 500 并发是拐点就把上限调成 500 或稍高同时让应用侧连接池略小于这个值保证连接队列始终在可控水位。配合wait_timeout和interactive_timeout把长时间空闲的连接及时回收。一般业务系统wait_timeout设置在 60 到 120 秒就够用了别用默认的 8 小时。5.3 需要慎碰的参数清单调优圈里流传着不少万能参数真正资深的人反而会劝你别乱碰。sort_buffer_size和join_buffer_size是典型的每连接独立分配的参数调太大会导致高并发下内存瞬间爆炸。这俩参数的作用域是每个连接每次排序或关联操作一条慢查询里恰好有 filesort它就会按你配置的上限分配内存。所以宁可使用默认值也不要为了单个场景把全局值抬到几十 MB。query_cache在 MySQL 8.0 里已经被彻底移除了如果你看老文章还在调它直接跳过。另外performance_schema在低配机器上会带来 5% 到 10% 的开销但这点开销换来的排查能力绝对值得一般不建议关掉。参数调优最重要的一条纪律是一次只改一个参数改完压测记录前后数据。不要同时动五六个参数出了问题根本不知道是谁的锅。6. 当单机扛不住时读写分离与垂直拆分的取舍6.1 主从复制与读写分离的应用边界单机 MySQL 的性能天花板是有限的当读流量持续增长读写分离成了最自然的下一步。主从复制本身不难难的是你要清楚它的边界。复制延迟是读写分离最大的隐患。MySQL 默认的复制是异步的从库可能落后主库几百毫秒甚至几秒。业务上如果出现写完主库立刻去从库读的场景就会读到旧数据。缓解手段有几种把读实时性要求高的请求路由到主库或者开启半同步复制rpl_semi_sync_master_enabled以及从库并行复制slave_parallel_workers把延迟压到几十毫秒以内。读写分离的收益主要来自把读压力分摊到多个从库。从库不宜太多主从延迟和同步成本会随之上升一般两三个从库覆盖绝大多数场景已经足够。6.2 分库分表的几个前置判断很多人一听到大表问题就喊分库分表这是最大的误判。分库分表带来的分布式事务、跨库 JOIN、全局主键、数据迁移等一系列复杂度是普通团队的噩梦。不到万不得已不要走这一步。我的判断标准是这样第一单表数据量是否已经明显影响写入和查询性能比如千万行级别以上且还在持续增长第二做完整理归档、分区后能不能解决第三业务是否具备清晰的分片键分片键要求被绝大多数查询条件覆盖如果没有一个稳定高效的查询入口分片后大量查询都要走聚合路由性能反而更差。如果确实要分垂直拆分通常优先于水平拆分。先把不同业务域的表拆到不同实例再把 single 大表按时间或业务维度水平分片。中间件选型这件事我不展开重点提醒一句中间件会把你和原生 SQL 的能力做一层隔离很多 MySQL 原生优化在分片后不再适用架构决策一定要早做越晚越被动。6.3 缓存层到底该放在哪个位置缓存不是替代 MySQL 的银弹但放对位置确实能救 MySQL 一命。我推荐的模式是标准的 Cache Aside读请求先查缓存命中直接返回未命中则查数据库回填缓存写请求先更新数据库再删除缓存。这个模式实现简单一致性可控适合绝大多数业务。真正要防的是三个缓存经典问题。穿透查询一个不存在的数据缓存永远没命中每次都打到数据库可以在应用层对空结果也做缓存或加布隆过滤器。击穿热点 key 过期瞬间大量请求涌向数据库可以给热点 key 设置较长过期时间或做互斥回源。雪崩大量 key 同时过期导致数据库压力飙升解决方式是过期时间加随机扰动避免整片失效。缓存层的作用是挡流量但数据持久化的责任始终在数据库。不要因为缓存能挡住 90% 的读请求就把数据库设计得过份依赖缓存兜底缓存一抖动数据库必须能扛住回源流量。7. 复盘几个真实翻车现场7.1 隐式类型转换引发的全表扫描有一次线上告警某用户中心接口从 30 毫秒涨到 3 秒。查慢日志发现一条 SQLWHERE user_id 123456789。问题出在user_id是BIGINT但代码里拼接参数时没注意把数字变成了字符串。MySQL 在比较时对列做了隐式类型转换索引再不生效直接全表扫描。修复方式简单得让人脸红把参数改成数值类型。但这件事给我的教训是任何动态拼接 SQL的参数都要严格检查类型另外排查 SQL 时EXPLAIN是必须的第一步我后来在团队规范里加了硬性要求所有慢 SQL 排查必须先贴执行计划再开会讨论。7.2 偏移量分页导致的慢查询还有一次是后台任务页面用户通过列表接口不断往后翻翻到 100 页左右接口开始超时。典型的深分页问题LIMIT 200000, 20每次要扫描 20 万行再丢弃。复用上面说到的延迟关联方案后耗时从 2.8 秒降到 40 毫秒。这里有个容易忽略的细节优化后要确认EXPLAIN里子查询确实走的是覆盖索引否则子查询本身还会回表优化效果会打折扣。我的联合索引(status, id)在这个案例里同时满足了 WHERE 条件和 ORDER BY 排序子查询纯索引扫描效果才会这么明显。7.3 缓冲池配错导致的间歇性卡顿最后一个案例是典型的配置事故。一台 128GB 内存的数据库服务器部署方把innodb_buffer_pool_size设成了 110GB表面上没超内存总量但没算上操作系统、线程、排序缓冲等其他消耗结果系统开始频繁 swap整个数据库每隔几分钟就卡一下慢查询成片出现。把缓冲池降到 72GB 之后swap 消失整体响应恢复平稳。这个案例的教训是参数调优的边界值一定要留足余量用满内存不是性能最优反而可能因为触发页交换让所有查询集体变慢。我现在的习惯是配置前先看机器上还有没有别的进程至少预留 20% 到 30% 的内存给操作系统和日常开销。最后再说两句优化做到后面你会发现MySQL 高性能这件事拼的不是哪一招奇技淫巧而是完整的决策链路先定位全局瓶颈再反思表结构然后设计索引和 SQL最后才轮到参数和架构。我自己的习惯是每次优化都留一个优化前后对比表把EXPLAIN的rows、type、接口耗时都记录下来这样既能验证效果也能沉淀成团队的 SQL 规范。最后分享一个小技巧日常巡检时顺手把SHOW GLOBAL STATUS里的关键指标存成快照出问题当天拿出来一对比很多疑难杂症的答案其实早就写在这些数据里了。