做MySQL性能优化这行当久了你会发现一个规律绝大多数被推到DBA面前的“数据库卡死了”的问题最后查下来都不是数据库本身有多弱而是SQL写得有问题、索引没建对、或者架构上压根没给数据库留活路。MySQL单机性能其实相当能打但企业级场景下并发一上来一条烂SQL就能把整库拖垮这真不是危言耸听。这篇文章想聊的就是从SQL调优到架构设计的完整链路。我按自己这些年处理线上问题的实际经验来写不整虚的——慢查询日志怎么开、EXPLAIN怎么看、索引怎么建、主从复制和分库分表什么时候该上每一块都会给出可以直接抄作业的做法。不管你是一线开发、初级DBA还是做架构选型的技术负责人这套思路应该都适用。1. 先从整体视角看企业级MySQL性能优化到底在优化什么1.1 性能问题的本质瓶颈到底在哪里很多人一遇到性能问题就急着调参数把innodb_buffer_pool_size从4G调到8G感觉内存给够了就万事大吉。实际上在企业级场景里真正吃掉数据库资源的往往是那几句高频访问的SQL和低效的索引设计。我见过的案例80%以上的性能瓶颈都出在SQL层配置文件只是背锅侠。你可以把MySQL想象成一个快递分拣中心CPU是分拣员内存是暂存仓库磁盘是运输车队SQL就是包裹上的面单。面单信息不全、地址写错分拣员再快也没用——他得拆开包裹看内容才知道该往哪送这就是全表扫描。而索引就像一本按门牌号排序的地址簿有了它分拣员直接按区段投递就行。所以做优化前第一件事不是调参而是搞清楚瓶颈在哪一环。我一般会从这几个维度去定位响应时间慢是某几条SQL慢还是所有SQL都慢前者大概率是SQL和索引问题后者才需要看硬件和配置。并发上不去是锁等待严重还是连接数被打满这决定了你要去优化事务隔离级别还是先扩容连接池。资源有异常CPU打满可能是排序和全表扫描太多磁盘IO飙升可能是buffer pool太小或者没有走索引内存不够可能查的是大排序和大临时表。把这些搞清楚才知道该往哪个方向使劲。1.2 优化工作的分层思路我个人习惯把企业级MySQL优化拆成三层从下往上依次排查效率最高SQL与索引层这是性价比最高的层。改一条SQL、加一个索引可能只需要几分钟效果却是从秒级到毫秒级的跨越。大部分慢SQL问题在这一层就能解决。参数与实例层确认SQL没问题之后再去看缓冲池大小、刷盘策略、并发线程数这些配置项。注意每改一个参数都要能说清为什么别听网上说调什么就调什么。架构与容量层当单实例已经扛不住流量或者数据量到了上亿级别就需要考虑读写分离、分库分表、引入缓存这些架构手段。这层改动最大、成本最高应该放在最后。这其实也对应了优化的优先级原则先抓大头再抠细节先用小改动解决大问题再考虑动架构。很多团队一上来就搞分库分表结果发现只是少建了一个联合索引这种教训我见过太多次了。2. SQL调优最立竿见影的一环2.1 慢查询日志先找到“嫌疑犯”再说没有慢查询日志就谈SQL调优等于蒙着眼开车。MySQL的慢查询日志是性能优化的起点所有工作都从“找到那条拖后腿的SQL”开始。开启方式很简单在my.cnf的[mysqld]段里加上slow_query_log ON slow_query_log_file /var/log/mysql/slow.log long_query_time 1 log_queries_not_using_indexes ON几个参数的实践经验给到大家long_query_time线上的初始阈值建议先设成1秒抓到的慢SQL太多再逐步收紧到0.5秒或0.1秒如果一开始就设0.1秒日志量会大到没法看。log_queries_not_using_indexes这个一定要开它能记录下没走索引的SQL。很多潜在问题SQL还没慢到阈值就会被它揪出来是预防性的好工具。慢日志分析工具直接看日志文件很痛苦推荐用pt-query-digest对慢日志做聚合分析。它会按执行次数、总耗时、平均耗时排序几分钟就能告诉你哪些SQL最该优先处理。顺带提一句衡量慢SQL不能只看单次耗时一定要看执行频率。一条执行10ms但是每秒跑1000次的SQL和一条执行2秒但每天只跑几次的SQL前者对系统的伤害大得多。合并慢日志的时候用“总耗时平均耗时×执行次数”来排优先级这个思路很重要。2.2 执行计划解读EXPLAIN不是玄学定位到慢SQL之后下一步就是分析执行计划。EXPLAIN SELECT ...就能看到MySQL是怎么执行这条SQL的。很多人只会看那一串结果但不知道重点看哪几列。我建议你按顺序关注这几个关键项type列这是访问类型从好到坏大概是system const eq_ref ref range index ALL。看到ALL就是全表扫描这是最危险的信号index虽然是全索引扫描也要警惕。key列实际使用到的索引。如果显示NULL说明这条SQL没用到任何索引。rows列预估扫描的行数。这个数字越大执行时间通常越长。优化前后对比这个数值就能判断改动是否有效。Extra列这里面有宝藏也有陷阱。看到Using filesort说明SQL在做额外的排序看到Using temporary说明用了临时表看到Using index则是覆盖索引这是最理想的状态。拿一个最常见的例子SELECT * FROM orders WHERE user_id ? AND status ? ORDER BY create_time DESC LIMIT 10。如果这条SQL慢EXPLAIN大概率会看到type ALL和Using filesort。这时你创建一个联合索引(user_id, status, create_time)执行计划立刻变成type ref排序也不再需要filesort因为索引已经天然维护了排序顺序。这里要特别强调一个新手常犯的错误对索引列做函数运算。比如WHERE DATE(create_time) 2024-01-01这么写索引就失效了因为MySQL要对每一行计算函数才能比较。正确的写法是改成范围查询SELECT * FROM orders WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00;另外隐式类型转换也是索引杀手。WHERE phone 13800001111如果phone列是varchar类型这个数字常量会被转成字符串再比较吗恰恰相反MySQL会把字符串列转成数字进行比较导致索引失效。保持类型一致是所有SQL调优的基本功。2.3 索引设计的取舍索引不是越多越好每个索引都会占用空间、拖慢写入。企业级系统里索引设计是一个平衡的艺术。我建索引的几个原则供参考联合索引遵循最左前缀原则联合索引(a, b, c)可以支持a、a,b、a,b,c三组查询条件但直接用b或c开头就废了。建索引时把等值查询的列放前面把范围查询的列放后面排序字段放在更后面。用覆盖索引避免回表如果查询只需要某几个字段那就让索引包含这些字段。比如列表页只显示id, title, status建一个(status, id, title)的覆盖索引查询可以完全从索引树里拿数据连数据页都不碰一下。关注字段区分度区分度低的字段比如status只有0和1两个值放在联合索引最前面意义不大。如果查询条件里既有区分度高的user_id又有区分度低的status应该把user_id放前面。控制单表索引数量我个人的习惯是一张表索引总数建议控制在5个以内写频繁的业务表甚至更少。每多一个索引INSERT和UPDATE就要多维护一棵B树。索引字段的选择没有放之四海而皆准的公式核心是拿真实业务查询去模拟用EXPLAIN验证效果。你可以在测试环境把慢SQL一条条跑一遍记录优化前后的rows和耗时用数据说话。3. 实操过程一个线上慢SQL从发现到优化的完整记录3.1 案例分析订单查询从0.87秒降到8毫秒理论讲再多不如来一个完整的实战案例。之前接手过一个电商系统订单表的量级在千万行左右线上反馈“订单列表页打开要1秒多”用户已经明显感觉到卡顿。第一步先开慢查询日志半小时后抓到了核心问题SQLSELECT order_id, order_no, user_id, amount, status, create_time FROM orders WHERE user_id 12345 AND status IN (1, 2, 3) ORDER BY create_time DESC LIMIT 20;第二步EXPLAIN看执行计划关键结果如下列名优化前值说明typeALL全表扫描千万级数据全过一遍keyNULL没有可用索引rows11240000估算扫描了一千多万行ExtraUsing filesort还用临时文件做了排序在没有索引的情况下这个查询要扫全表找出该用户的数据再对结果排序取前20条慢是必然的。而且注意一个坑IN (1, 2, 3)这种写法在旧版本里会打断索引的等值匹配优化时要么拆成多个等值查询要么调整策略。第三步设计索引。这条SQL的等值条件有user_id和status排序条件是create_time所以联合索引按(user_id, status, create_time)的顺序来建。加索引的DDL如下ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, create_time), ALGORITHMINPLACE, LOCKNONE;这里多说两句千万级表加索引一定要用在线DDL。MySQL 5.6之后支持ALGORITHMINPLACE, LOCKNONE加索引期间可以继续读写不会锁表。如果是5.5或者更老的版本建议用pt-online-schema-change这类工具否则一个加索引操作就能把线上写请求全堵住那是事故级别的操作。第四步优化后再看执行计划type变成refkey显示idx_user_status_timerows从一千多万降到几百行Using filesort消失。实际压测结果这个查询耗时从0.87秒降到8毫秒左右提升了两个数量级。3.2 深分页LIMIT 100000, 20这种写法怎么救还有一个高频问题就是列表页的深分页。LIMIT 100000, 20这种写法MySQL要先扫描到第10万行再丢弃前面的99980行翻页越深越慢。线上遇到过一页比一页慢、最后直接超时的场景。优化的标准做法是延迟关联先通过覆盖索引定位到需要的id再用id回表取完整数据SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders WHERE user_id 12345 AND status IN (1, 2, 3) ORDER BY create_time DESC LIMIT 100000, 20 ) t ON o.id t.id;子查询里只查id能走覆盖索引扫描成本大大降低外层再按id精确取行避免了大范围回表。这个技巧在数据量大的业务表上效果尤其明显分页到几十万行深度时性能差距能达到几十倍。如果业务上允许还可以用游标分页替代偏移量分页——记录上一页最后一条create_time和id下一页带上WHERE create_time ? OR (create_time ? AND id ?)条件。这是性能最优的翻页方式但要求排序字段不能有重复值歧义一般得用create_time id的组合条件来保证唯一序。具体取舍要看业务需求是否接受“只提供上一页/下一页”的交互形态。4. 常见问题与排查技巧实录4.1 典型问题速查表长期处理线上问题我把最常见的MySQL性能故障整理成了一张速查表遇到问题时可以对号入座省去大量排查时间症状常见原因排查方法解决思路某条SELECT突然变慢统计信息不准、索引失效、数据量大增EXPLAIN看rows和type重新ANALYZE TABLE重建或新增索引CPU长期打满大量全表扫描、排序、大临时表慢日志pt-query-digest优化SQL、加索引、减少大排序写入频繁阻塞行锁竞争、长事务未提交查information_schema.innodb_trx和锁等待缩短事务、拆分批量提交连接数被打满连接池配置过大/过小、慢SQL堆积查SHOW PROCESSLIST调连接池、先解决慢SQL磁盘IO飙升buffer pool过小、没有走索引的查询看SHOW ENGINE INNODB STATUS增大内存命中率、优化索引主从延迟持续拉大从库单线程复制、大事务执行看SHOW SLAVE STATUS的Seconds_Behind_Master启用并行复制、拆分大事务这里面最容易被忽视的是长事务。我曾经排查过一个“数据库越来越慢”的问题最后发现一个报表导出接口开了事务一直没提交持有大量行锁导致正常业务写入全都堵在锁等待上。排查方法很简单查询information_schema.innodb_trx表找到trx_state RUNNING且持续时间很长的会话联系对应开发确认后KILL掉。在代码层面事务的黄金法则是尽可能短、尽可能只动必要的行、绝不在事务里做外部API调用。4.2 几个值得养成的排查习惯定期巡检慢日志建议每天或者每周自动汇总一次慢日志分析是否有新增的慢SQL。性能问题都是温水煮青蛙等到用户投诉再处理就晚了。给监控配上告警至少盯住四个指标——慢查询数量、活跃连接数、InnoDB缓冲池命中率、主从复制延迟。任何一个指标出现异常都值得立即查原因。SQL上线前过一遍EXPLAIN很多团队有这个规范但执行不下去。我建议至少在测试环境把新SQL的执行计划打出来看一眼确认不是全表扫描再放行。这个习惯能省掉80%的线上事故。变更留好回滚方案改索引和调参数之前先记录原状准备好回滚SQL。别觉得自己不会失误线上变更没有回滚方案的迟早要吃大亏。关于参数调优我再多说一点。很多人把innodb_buffer_pool_size调到物理内存的70%就觉得万事大吉但纯靠调参解决不了SQL问题。参数是放大器不是修复器SQL烂参数再大也只是延迟崩溃时间。正确的顺序永远是先优化SQL再考虑参数。比如innodb_flush_log_at_trx_commit这个参数每次事务提交是否刷盘关系到数据安全默认值1不要为了性能随意改成0或2除非你真的能接受最近1秒数据的丢失风险。5. 从SQL到架构更上一层的设计考量5.1 连接池别让建立连接的消耗拖垮性能当SQL优化到位之后如果并发量继续上涨下一步需要考虑的就是连接管理。MySQL每次建立连接都要经历TCP握手、权限校验、上下文创建这个开销在高并发下非常可观。所以应用侧一定要用连接池Java生态最推荐HikariCP性能好而且配置简单。一组合理的起步配置maximum-pool-size: 20 minimum-idle: 5 connection-timeout: 30000 idle-timeout: 600000 max-lifetime: 1800000关于连接池大小业界有个反直觉的结论连接数不是越多越好。因为CPU核数是固定的连接太多会导致线程频繁切换反而降低吞吐。经验公式大致是连接数 ((核心数 * 2) 有效存储设备数)比如4核机器单机连接池给10~15个就够用了。如果你的服务是多实例部署还要统计算所有实例加起来的连接数别让数据库端连接数被打满。5.2 主从复制与读写分离什么时候该上单库单实例扛不住读压力时第一个架构手段就是读写分离。主库负责写从库负责读通过binlog把数据同步过去。主从复制的几个实践要点binlog格式建议用ROW虽然日志量大一些但ROW格式最可靠特别是在有主键冲突、DDL变更、数据恢复的场景下statement格式踩过的坑太多了。半同步复制如果业务对一致性要求高可以启用半同步复制rpl_semi_sync_master_enabled ON确保至少一个从库收到binlog才返回commit成功。这会牺牲少量性能但能有效减少主从切换时的数据丢失窗口。关注从库延迟从库长期以来是单线程回放binlog主库并发高时延迟会越来越大。MySQL 8.0的并行复制MTS默认开启能把回放性能提上来不少。大事务是延迟的头号杀手几百万行的UPDATE在主库几秒完成在从库可能要跑好几分钟所以拆分大事务是保障主从健康的重中之重。读写分离在代码层的实现可以用ShardingSphere这类中间件也可以自己在数据源层面做读写路由。不管用哪种方案都要想清楚一致性问题主库写完立即去从库读可能因为复制延迟读不到最新数据。常规做法是对一致性要求高的读操作强制走主库或者设置从库wait_timeout配合短延迟监控。5.3 分库分表最后的手段也是最重的工程当单表数据量达到亿级、或者单库写入达到瓶颈之后才需要考虑分库分表。这里我要先泼一盆冷水分库分表是复杂度放大器一旦上了这条船全局唯一ID、分布式事务、跨库join、分页排序这些问题会一个接一个找上门。所以决定分片之前先看看有没有替代方案数据归档把3年以上的历史数据迁到归档表或者冷存储业务表立刻瘦身。很多“单表数据量太大”的问题根本不需要分表归档就解决了。分区表MySQL的分区表是透明分区的方案按时间分区后查询条件带上分区键可以快速裁剪数据。但要注意分区表在部分场景下性能反而不如普通表使用前务必压测。垂直拆分把大字段text/blob和多变字段拆到扩展表也是一种有效的拆分思路比水平拆分简单得多。如果确实要水平拆分片键的选择是成败关键。重要的原则就一条分片键必须是核心业务的访问维度。比如订单系统绝大多数查询都带着user_id那就按user_id哈希取模分库。可以接受的是后台管理系统的跨分片查询走汇总库或者搜索引擎不要指望在分片后的数据库上做所有查询。5.4 缓存层给数据库松松绑架构层面最后一步是引入缓存。Redis和MySQL的搭配是互联网行业的标配核心目的就一个把高频读请求挡在数据库外面。缓存在设计层面有几个关键点值得聊缓存穿透查一个不存在的key请求会直接打到数据库。解法是缓存空值或者用布隆过滤器先拦一道。缓存击穿某个热点key在过期瞬间被大量请求打到数据库。解法是热点数据不过期或者用互斥锁只让一个线程去重建缓存。缓存雪崩大量key同时过期数据库瞬间被压垮。解法是过期时间加随机值别让所有key在同一个时间点集体失效。我自己的经验是缓存一定要有明确的更新策略最常用的是Cache Aside模式——读走缓存写更新数据库后删缓存。删缓存这个动作很关键它比更新缓存更安全能避免并发写时出现数据不一致。所有缓存代码都要考虑缓存服务挂了之后的降级路径别让Redis一宕机整个系统跟着崩。结尾说点这些年踩坑攒下的实在话做性能优化这些年最大的体会是真正值钱的能力不是会背参数、会写命令而是能准确判断问题在哪一层。拿到一个慢SQL先别急着改花十分钟看清执行计划遇到数据库卡顿先确认是SQL问题、锁问题还是资源问题方向对了解决方案自然就出来了。最后再分享一个工作流上的小技巧我习惯把每一次性能问题的处理过程记录成一份带前后对比的文档——现象、定位过程、执行计划截图、优化措施、优化后的耗时数据。这份文档既是团队的应急预案也是新人培训最好的教材。性能优化没有一劳永逸业务在变、数据在涨、流量在起伏但只要你掌握了这套“看日志、析计划、建索引、查锁争、控架构”的方法论无论遇到什么规模的MySQL问题心里都不会慌。 从SQL调优到架构设计：MySQL性能优化完整指南
做MySQL性能优化这行当久了你会发现一个规律绝大多数被推到DBA面前的“数据库卡死了”的问题最后查下来都不是数据库本身有多弱而是SQL写得有问题、索引没建对、或者架构上压根没给数据库留活路。MySQL单机性能其实相当能打但企业级场景下并发一上来一条烂SQL就能把整库拖垮这真不是危言耸听。这篇文章想聊的就是从SQL调优到架构设计的完整链路。我按自己这些年处理线上问题的实际经验来写不整虚的——慢查询日志怎么开、EXPLAIN怎么看、索引怎么建、主从复制和分库分表什么时候该上每一块都会给出可以直接抄作业的做法。不管你是一线开发、初级DBA还是做架构选型的技术负责人这套思路应该都适用。1. 先从整体视角看企业级MySQL性能优化到底在优化什么1.1 性能问题的本质瓶颈到底在哪里很多人一遇到性能问题就急着调参数把innodb_buffer_pool_size从4G调到8G感觉内存给够了就万事大吉。实际上在企业级场景里真正吃掉数据库资源的往往是那几句高频访问的SQL和低效的索引设计。我见过的案例80%以上的性能瓶颈都出在SQL层配置文件只是背锅侠。你可以把MySQL想象成一个快递分拣中心CPU是分拣员内存是暂存仓库磁盘是运输车队SQL就是包裹上的面单。面单信息不全、地址写错分拣员再快也没用——他得拆开包裹看内容才知道该往哪送这就是全表扫描。而索引就像一本按门牌号排序的地址簿有了它分拣员直接按区段投递就行。所以做优化前第一件事不是调参而是搞清楚瓶颈在哪一环。我一般会从这几个维度去定位响应时间慢是某几条SQL慢还是所有SQL都慢前者大概率是SQL和索引问题后者才需要看硬件和配置。并发上不去是锁等待严重还是连接数被打满这决定了你要去优化事务隔离级别还是先扩容连接池。资源有异常CPU打满可能是排序和全表扫描太多磁盘IO飙升可能是buffer pool太小或者没有走索引内存不够可能查的是大排序和大临时表。把这些搞清楚才知道该往哪个方向使劲。1.2 优化工作的分层思路我个人习惯把企业级MySQL优化拆成三层从下往上依次排查效率最高SQL与索引层这是性价比最高的层。改一条SQL、加一个索引可能只需要几分钟效果却是从秒级到毫秒级的跨越。大部分慢SQL问题在这一层就能解决。参数与实例层确认SQL没问题之后再去看缓冲池大小、刷盘策略、并发线程数这些配置项。注意每改一个参数都要能说清为什么别听网上说调什么就调什么。架构与容量层当单实例已经扛不住流量或者数据量到了上亿级别就需要考虑读写分离、分库分表、引入缓存这些架构手段。这层改动最大、成本最高应该放在最后。这其实也对应了优化的优先级原则先抓大头再抠细节先用小改动解决大问题再考虑动架构。很多团队一上来就搞分库分表结果发现只是少建了一个联合索引这种教训我见过太多次了。2. SQL调优最立竿见影的一环2.1 慢查询日志先找到“嫌疑犯”再说没有慢查询日志就谈SQL调优等于蒙着眼开车。MySQL的慢查询日志是性能优化的起点所有工作都从“找到那条拖后腿的SQL”开始。开启方式很简单在my.cnf的[mysqld]段里加上slow_query_log ON slow_query_log_file /var/log/mysql/slow.log long_query_time 1 log_queries_not_using_indexes ON几个参数的实践经验给到大家long_query_time线上的初始阈值建议先设成1秒抓到的慢SQL太多再逐步收紧到0.5秒或0.1秒如果一开始就设0.1秒日志量会大到没法看。log_queries_not_using_indexes这个一定要开它能记录下没走索引的SQL。很多潜在问题SQL还没慢到阈值就会被它揪出来是预防性的好工具。慢日志分析工具直接看日志文件很痛苦推荐用pt-query-digest对慢日志做聚合分析。它会按执行次数、总耗时、平均耗时排序几分钟就能告诉你哪些SQL最该优先处理。顺带提一句衡量慢SQL不能只看单次耗时一定要看执行频率。一条执行10ms但是每秒跑1000次的SQL和一条执行2秒但每天只跑几次的SQL前者对系统的伤害大得多。合并慢日志的时候用“总耗时平均耗时×执行次数”来排优先级这个思路很重要。2.2 执行计划解读EXPLAIN不是玄学定位到慢SQL之后下一步就是分析执行计划。EXPLAIN SELECT ...就能看到MySQL是怎么执行这条SQL的。很多人只会看那一串结果但不知道重点看哪几列。我建议你按顺序关注这几个关键项type列这是访问类型从好到坏大概是system const eq_ref ref range index ALL。看到ALL就是全表扫描这是最危险的信号index虽然是全索引扫描也要警惕。key列实际使用到的索引。如果显示NULL说明这条SQL没用到任何索引。rows列预估扫描的行数。这个数字越大执行时间通常越长。优化前后对比这个数值就能判断改动是否有效。Extra列这里面有宝藏也有陷阱。看到Using filesort说明SQL在做额外的排序看到Using temporary说明用了临时表看到Using index则是覆盖索引这是最理想的状态。拿一个最常见的例子SELECT * FROM orders WHERE user_id ? AND status ? ORDER BY create_time DESC LIMIT 10。如果这条SQL慢EXPLAIN大概率会看到type ALL和Using filesort。这时你创建一个联合索引(user_id, status, create_time)执行计划立刻变成type ref排序也不再需要filesort因为索引已经天然维护了排序顺序。这里要特别强调一个新手常犯的错误对索引列做函数运算。比如WHERE DATE(create_time) 2024-01-01这么写索引就失效了因为MySQL要对每一行计算函数才能比较。正确的写法是改成范围查询SELECT * FROM orders WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00;另外隐式类型转换也是索引杀手。WHERE phone 13800001111如果phone列是varchar类型这个数字常量会被转成字符串再比较吗恰恰相反MySQL会把字符串列转成数字进行比较导致索引失效。保持类型一致是所有SQL调优的基本功。2.3 索引设计的取舍索引不是越多越好每个索引都会占用空间、拖慢写入。企业级系统里索引设计是一个平衡的艺术。我建索引的几个原则供参考联合索引遵循最左前缀原则联合索引(a, b, c)可以支持a、a,b、a,b,c三组查询条件但直接用b或c开头就废了。建索引时把等值查询的列放前面把范围查询的列放后面排序字段放在更后面。用覆盖索引避免回表如果查询只需要某几个字段那就让索引包含这些字段。比如列表页只显示id, title, status建一个(status, id, title)的覆盖索引查询可以完全从索引树里拿数据连数据页都不碰一下。关注字段区分度区分度低的字段比如status只有0和1两个值放在联合索引最前面意义不大。如果查询条件里既有区分度高的user_id又有区分度低的status应该把user_id放前面。控制单表索引数量我个人的习惯是一张表索引总数建议控制在5个以内写频繁的业务表甚至更少。每多一个索引INSERT和UPDATE就要多维护一棵B树。索引字段的选择没有放之四海而皆准的公式核心是拿真实业务查询去模拟用EXPLAIN验证效果。你可以在测试环境把慢SQL一条条跑一遍记录优化前后的rows和耗时用数据说话。3. 实操过程一个线上慢SQL从发现到优化的完整记录3.1 案例分析订单查询从0.87秒降到8毫秒理论讲再多不如来一个完整的实战案例。之前接手过一个电商系统订单表的量级在千万行左右线上反馈“订单列表页打开要1秒多”用户已经明显感觉到卡顿。第一步先开慢查询日志半小时后抓到了核心问题SQLSELECT order_id, order_no, user_id, amount, status, create_time FROM orders WHERE user_id 12345 AND status IN (1, 2, 3) ORDER BY create_time DESC LIMIT 20;第二步EXPLAIN看执行计划关键结果如下列名优化前值说明typeALL全表扫描千万级数据全过一遍keyNULL没有可用索引rows11240000估算扫描了一千多万行ExtraUsing filesort还用临时文件做了排序在没有索引的情况下这个查询要扫全表找出该用户的数据再对结果排序取前20条慢是必然的。而且注意一个坑IN (1, 2, 3)这种写法在旧版本里会打断索引的等值匹配优化时要么拆成多个等值查询要么调整策略。第三步设计索引。这条SQL的等值条件有user_id和status排序条件是create_time所以联合索引按(user_id, status, create_time)的顺序来建。加索引的DDL如下ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, create_time), ALGORITHMINPLACE, LOCKNONE;这里多说两句千万级表加索引一定要用在线DDL。MySQL 5.6之后支持ALGORITHMINPLACE, LOCKNONE加索引期间可以继续读写不会锁表。如果是5.5或者更老的版本建议用pt-online-schema-change这类工具否则一个加索引操作就能把线上写请求全堵住那是事故级别的操作。第四步优化后再看执行计划type变成refkey显示idx_user_status_timerows从一千多万降到几百行Using filesort消失。实际压测结果这个查询耗时从0.87秒降到8毫秒左右提升了两个数量级。3.2 深分页LIMIT 100000, 20这种写法怎么救还有一个高频问题就是列表页的深分页。LIMIT 100000, 20这种写法MySQL要先扫描到第10万行再丢弃前面的99980行翻页越深越慢。线上遇到过一页比一页慢、最后直接超时的场景。优化的标准做法是延迟关联先通过覆盖索引定位到需要的id再用id回表取完整数据SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders WHERE user_id 12345 AND status IN (1, 2, 3) ORDER BY create_time DESC LIMIT 100000, 20 ) t ON o.id t.id;子查询里只查id能走覆盖索引扫描成本大大降低外层再按id精确取行避免了大范围回表。这个技巧在数据量大的业务表上效果尤其明显分页到几十万行深度时性能差距能达到几十倍。如果业务上允许还可以用游标分页替代偏移量分页——记录上一页最后一条create_time和id下一页带上WHERE create_time ? OR (create_time ? AND id ?)条件。这是性能最优的翻页方式但要求排序字段不能有重复值歧义一般得用create_time id的组合条件来保证唯一序。具体取舍要看业务需求是否接受“只提供上一页/下一页”的交互形态。4. 常见问题与排查技巧实录4.1 典型问题速查表长期处理线上问题我把最常见的MySQL性能故障整理成了一张速查表遇到问题时可以对号入座省去大量排查时间症状常见原因排查方法解决思路某条SELECT突然变慢统计信息不准、索引失效、数据量大增EXPLAIN看rows和type重新ANALYZE TABLE重建或新增索引CPU长期打满大量全表扫描、排序、大临时表慢日志pt-query-digest优化SQL、加索引、减少大排序写入频繁阻塞行锁竞争、长事务未提交查information_schema.innodb_trx和锁等待缩短事务、拆分批量提交连接数被打满连接池配置过大/过小、慢SQL堆积查SHOW PROCESSLIST调连接池、先解决慢SQL磁盘IO飙升buffer pool过小、没有走索引的查询看SHOW ENGINE INNODB STATUS增大内存命中率、优化索引主从延迟持续拉大从库单线程复制、大事务执行看SHOW SLAVE STATUS的Seconds_Behind_Master启用并行复制、拆分大事务这里面最容易被忽视的是长事务。我曾经排查过一个“数据库越来越慢”的问题最后发现一个报表导出接口开了事务一直没提交持有大量行锁导致正常业务写入全都堵在锁等待上。排查方法很简单查询information_schema.innodb_trx表找到trx_state RUNNING且持续时间很长的会话联系对应开发确认后KILL掉。在代码层面事务的黄金法则是尽可能短、尽可能只动必要的行、绝不在事务里做外部API调用。4.2 几个值得养成的排查习惯定期巡检慢日志建议每天或者每周自动汇总一次慢日志分析是否有新增的慢SQL。性能问题都是温水煮青蛙等到用户投诉再处理就晚了。给监控配上告警至少盯住四个指标——慢查询数量、活跃连接数、InnoDB缓冲池命中率、主从复制延迟。任何一个指标出现异常都值得立即查原因。SQL上线前过一遍EXPLAIN很多团队有这个规范但执行不下去。我建议至少在测试环境把新SQL的执行计划打出来看一眼确认不是全表扫描再放行。这个习惯能省掉80%的线上事故。变更留好回滚方案改索引和调参数之前先记录原状准备好回滚SQL。别觉得自己不会失误线上变更没有回滚方案的迟早要吃大亏。关于参数调优我再多说一点。很多人把innodb_buffer_pool_size调到物理内存的70%就觉得万事大吉但纯靠调参解决不了SQL问题。参数是放大器不是修复器SQL烂参数再大也只是延迟崩溃时间。正确的顺序永远是先优化SQL再考虑参数。比如innodb_flush_log_at_trx_commit这个参数每次事务提交是否刷盘关系到数据安全默认值1不要为了性能随意改成0或2除非你真的能接受最近1秒数据的丢失风险。5. 从SQL到架构更上一层的设计考量5.1 连接池别让建立连接的消耗拖垮性能当SQL优化到位之后如果并发量继续上涨下一步需要考虑的就是连接管理。MySQL每次建立连接都要经历TCP握手、权限校验、上下文创建这个开销在高并发下非常可观。所以应用侧一定要用连接池Java生态最推荐HikariCP性能好而且配置简单。一组合理的起步配置maximum-pool-size: 20 minimum-idle: 5 connection-timeout: 30000 idle-timeout: 600000 max-lifetime: 1800000关于连接池大小业界有个反直觉的结论连接数不是越多越好。因为CPU核数是固定的连接太多会导致线程频繁切换反而降低吞吐。经验公式大致是连接数 ((核心数 * 2) 有效存储设备数)比如4核机器单机连接池给10~15个就够用了。如果你的服务是多实例部署还要统计算所有实例加起来的连接数别让数据库端连接数被打满。5.2 主从复制与读写分离什么时候该上单库单实例扛不住读压力时第一个架构手段就是读写分离。主库负责写从库负责读通过binlog把数据同步过去。主从复制的几个实践要点binlog格式建议用ROW虽然日志量大一些但ROW格式最可靠特别是在有主键冲突、DDL变更、数据恢复的场景下statement格式踩过的坑太多了。半同步复制如果业务对一致性要求高可以启用半同步复制rpl_semi_sync_master_enabled ON确保至少一个从库收到binlog才返回commit成功。这会牺牲少量性能但能有效减少主从切换时的数据丢失窗口。关注从库延迟从库长期以来是单线程回放binlog主库并发高时延迟会越来越大。MySQL 8.0的并行复制MTS默认开启能把回放性能提上来不少。大事务是延迟的头号杀手几百万行的UPDATE在主库几秒完成在从库可能要跑好几分钟所以拆分大事务是保障主从健康的重中之重。读写分离在代码层的实现可以用ShardingSphere这类中间件也可以自己在数据源层面做读写路由。不管用哪种方案都要想清楚一致性问题主库写完立即去从库读可能因为复制延迟读不到最新数据。常规做法是对一致性要求高的读操作强制走主库或者设置从库wait_timeout配合短延迟监控。5.3 分库分表最后的手段也是最重的工程当单表数据量达到亿级、或者单库写入达到瓶颈之后才需要考虑分库分表。这里我要先泼一盆冷水分库分表是复杂度放大器一旦上了这条船全局唯一ID、分布式事务、跨库join、分页排序这些问题会一个接一个找上门。所以决定分片之前先看看有没有替代方案数据归档把3年以上的历史数据迁到归档表或者冷存储业务表立刻瘦身。很多“单表数据量太大”的问题根本不需要分表归档就解决了。分区表MySQL的分区表是透明分区的方案按时间分区后查询条件带上分区键可以快速裁剪数据。但要注意分区表在部分场景下性能反而不如普通表使用前务必压测。垂直拆分把大字段text/blob和多变字段拆到扩展表也是一种有效的拆分思路比水平拆分简单得多。如果确实要水平拆分片键的选择是成败关键。重要的原则就一条分片键必须是核心业务的访问维度。比如订单系统绝大多数查询都带着user_id那就按user_id哈希取模分库。可以接受的是后台管理系统的跨分片查询走汇总库或者搜索引擎不要指望在分片后的数据库上做所有查询。5.4 缓存层给数据库松松绑架构层面最后一步是引入缓存。Redis和MySQL的搭配是互联网行业的标配核心目的就一个把高频读请求挡在数据库外面。缓存在设计层面有几个关键点值得聊缓存穿透查一个不存在的key请求会直接打到数据库。解法是缓存空值或者用布隆过滤器先拦一道。缓存击穿某个热点key在过期瞬间被大量请求打到数据库。解法是热点数据不过期或者用互斥锁只让一个线程去重建缓存。缓存雪崩大量key同时过期数据库瞬间被压垮。解法是过期时间加随机值别让所有key在同一个时间点集体失效。我自己的经验是缓存一定要有明确的更新策略最常用的是Cache Aside模式——读走缓存写更新数据库后删缓存。删缓存这个动作很关键它比更新缓存更安全能避免并发写时出现数据不一致。所有缓存代码都要考虑缓存服务挂了之后的降级路径别让Redis一宕机整个系统跟着崩。结尾说点这些年踩坑攒下的实在话做性能优化这些年最大的体会是真正值钱的能力不是会背参数、会写命令而是能准确判断问题在哪一层。拿到一个慢SQL先别急着改花十分钟看清执行计划遇到数据库卡顿先确认是SQL问题、锁问题还是资源问题方向对了解决方案自然就出来了。最后再分享一个工作流上的小技巧我习惯把每一次性能问题的处理过程记录成一份带前后对比的文档——现象、定位过程、执行计划截图、优化措施、优化后的耗时数据。这份文档既是团队的应急预案也是新人培训最好的教材。性能优化没有一劳永逸业务在变、数据在涨、流量在起伏但只要你掌握了这套“看日志、析计划、建索引、查锁争、控架构”的方法论无论遇到什么规模的MySQL问题心里都不会慌。