MySQL数据库性能优化这件事几乎每个后端团队都会遇到。业务跑着跑着接口变慢了数据库CPU飙高慢SQL一条接一条一到高峰期就提心吊胆。我接触过很多项目组从单机小项目到分库分表的中型系统都踩过类似的坑。坦白说MySQL优化的方法论并不复杂核心就一句话让数据库少干活。少扫描数据、少回表、少排序、少锁等待。这篇文章我把自己常用的优化路径完整捋一遍包括先看什么、怎么定位、索引怎么建、SQL怎么改、参数怎么调、架构怎么做适合正在被慢查询困扰的后端开发者也适合刚接触数据库优化、想建立完整思路的读者。1. 先定位再动手慢查询、性能基线与常用工具1.1 慢查询日志是第一步很多人一听到“数据库慢”第一反应就是改参数或者加内存其实90%的情况下问题出在SQL和索引上根本轮不到动配置。所以我的优化流程永远从定位开始而定位的第一步就是打开慢查询日志。SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;long_query_time设多少合适生产环境一般设 1 秒如果是核心交易库、接口响应要求很高可以设 0.5 秒。不建议设太小比如 0.1 秒否则日志量会大到失去重点全是些无意义的快查询。日志位置可以通过SHOW VARIABLES LIKE slow_query_log_file;查看。打开之后过一两天去翻日志你会看到类似这样的记录# Query_time: 2.845239 Lock_time: 0.000032 Rows_sent: 10 Rows_examined: 1200000 SET timestamp...; SELECT * FROM orders WHERE status 0 AND create_time 2024-01-01 ORDER BY id DESC LIMIT 10;这条日志信息量很大查询只返回了 10 行却扫描了 120 万行。说明什么说明数据在磁盘里翻了 120 万行才捞出 10 条典型的索引缺失或索引设计不合理。慢日志多了以后手动看太累可以用自带工具归类mysqldumpslow -s at -t 10 /var/log/mysql/slow.log-s at表示按平均查询时间排序-t 10取前 10 条。这样可以快速知道哪些SQL是真正的“销冠”。我见过不少团队连慢查询日志都没开出了性能问题全靠猜这种做法非常被动。建议所有核心业务库都打开慢查询日志按月归档分析长期跟踪才有数据、有方向。1.2 性能指标先拉齐别拿感觉当证据慢日志解决的是“哪条SQL慢”的问题但在动手之前我习惯先把数据库的整体性能指标拉出来看一眼确认瓶颈到底是CPU、磁盘IO、锁等待还是连接数不够。常用的几个状态指标QPS/TPSSHOW GLOBAL STATUS LIKE Questions;当前连接数SHOW STATUS LIKE Threads_connected;InnoDB缓冲池命中率Innodb_buffer_pool_read_requests除以Innodb_buffer_pool_read_requests Innodb_buffer_pool_reads磁盘临时表数量SHOW STATUS LIKE Created_tmp_disk_tables;行锁等待次数SHOW STATUS LIKE Innodb_row_lock_waits;还有一个非常实用的口径看“扫描行数”和“返回行数”的比值。如果一条查询扫描了 50 万行只返回 20 行那这个比值是 25000说明索引设计大概率有问题。反过来扫描 10 万行返回 10 万行虽然绝对值大但可能是合理的全表读取。MySQL 5.7 以上可以用 sys 库快速拉出 SQL 消耗排行榜SELECT * FROM sys.statement_analysis LIMIT 10;这个视图会把平均扫描行数、平均耗时、执行次数都列出来比纯看慢日志更全面。不同瓶颈的表现也很有规律CPU 高逻辑读多常见于全表扫描、大量排序、函数计算。磁盘 IO 高缓冲池命中率低、大量刷新脏页、或者慢日志落盘过频。锁等待高行锁争用、大事务长时间持有锁。我的经验是先看指标判断方向再用慢日志和EXPLAIN精准定位。不要一上来就改innodb_buffer_pool_size这类参数那是最后一步不是第一步。2. 索引优化性价比最高的一步2.1 B树、回表和覆盖索引MySQL 的 InnoDB 引擎用的是 B 树索引可以把它理解成一本字典的目录页你要查一个词不用从第一页翻到最后一页而是先查目录、再翻到具体页码。B 树把表数据按主键顺序组织起来叶子节点存的是整行数据这叫聚簇索引。除了主键索引其他索引都叫二级索引。二级索引的叶子节点不存整行数据只存主键值。所以当你用WHERE name 张三去查走的是 name 上的二级索引找到主键后再回聚簇索引里取整行这个过程叫“回表”。回表是随机 IO性能损耗很大。怎么避免用覆盖索引——让索引里的字段覆盖你要查询的所有列。比如SELECT id, name FROM users WHERE name 张三;如果name上有索引而name和id都在索引里那么 MySQL 可以直接从索引里拿到 id 和 name不需要回表。EXPLAIN 里会出现Using index意思是这个查询完全可以用索引覆盖。所以我设计索引的时候会先看查询要哪些字段而不是只看 WHERE 条件。这往往是很多人忽略的点。2.2 索引失效的常见场景索引建了不等于一定生效。以下几个场景是我在线上排查时遇到最多的情况隐式类型转换。表里 phone 字段是 VARCHAR查询写成WHERE phone 13800000000数字会被隐式转换成字符串或者反过来导致索引失效。我遇到过好几次问题看起来一模一样实际就是类型不匹配。解决办法是应用层把参数类型统一或者用CAST显式转换。函数包裹索引列。WHERE DATE(create_time) 2024-01-01会让 create_time 上的索引用不上。正确写法是范围查询WHERE create_time 2024-01-01 AND create_time 2024-01-02这样不仅能用索引而且范围还更清晰。前导模糊匹配。LIKE %关键词%无法利用索引因为 B 树的顺序性要求从左往右匹配。如果业务真的需要模糊搜索建议走全文索引或外部搜索引擎不要指望数据库。OR 条件。WHERE status 0 OR user_id 123这种写法如果两端字段上都有独立索引MySQL 有可能走索引合并但更多情况下会退化成全表扫描。性能敏感的查询可以用UNION ALL拆开。联合索引不满足最左前缀。建了(a, b, c)联合索引查询只用到 b 或 c索引就发挥不了作用。这一点在 2.3 里我会展开说。每次写完 SQL都养成习惯跑一下EXPLAIN看 key 列是否为 NULL、type 是否为 ALL。这条习惯能帮你提前拦下大部分索引失效问题。2.3 索引怎么设计索引设计没有固定公式但有三个基本原则。第一区分度高的字段放前面。COUNT(DISTINCT col) / COUNT(*)就是区分度。区分度高意味着通过这个字段能过滤掉大部分数据。比如订单表里user_id区分度通常很高status只有 0/1/2 几种区分度很低。单独给 status 建索引往往效果很差因为 1/3 的数据都长一个样。第二联合索引的顺序等值字段优先范围字段靠后。比如业务查询是WHERE user_id ? AND status ? ORDER BY create_time DESC我会建ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, create_time);为什么这个顺序user_id和status都是等值查询先过滤create_time用于排序放最后可以让排序也走索引避免Using filesort。第三不要为了“万能”建一堆冗余索引。比如已经建了(a, b, c)再建(a)就是冗余因为(a, b, c)已经可以用 a 作为最左前缀。单表索引数量建议控制在 5-6 个以内。每个索引都要占用存储空间写入时还要更新索引过多的索引会让插入变慢。我之前优化过一个订单流水表查询条件是user_id status create_time建联合索引后一条原本扫描 120 万行的慢查询变成扫描几百行耗时从 2.8 秒降到 20 毫秒。这个效果不是靠调参能调出来的就是索引的功劳。3. SQL语句改写与执行计划解读3.1 EXPLAIN怎么看拿到一条慢SQL我的固定动作是先EXPLAIN。不要急着改SQL先看它现在的执行路径长什么样。EXPLAIN SELECT * FROM orders WHERE status 0 AND create_time 2024-01-01 ORDER BY id DESC LIMIT 10;重点看四列type访问类型性能从好到差是const、eq_ref、ref、range、index、ALL。如果看到ALL说明是全表扫描基本可以确定索引没走对。key实际用到的索引。如果为 NULL说明没走任何索引。rows预估扫描行数。这个数字和实际扫描行数越接近说明优化器的判断越准。Extra常见的有Using filesort文件排序、Using temporary临时表、Using index覆盖索引、Using where先取数据再过滤。我习惯先把rows和Extra放在一起看。如果 rows 上百万但最后只返回 10 条说明查询像“在图书馆里一本书一本书地翻最后只挑出 10 页”。这时候改索引或改写SQL的空间就很大。MySQL 8.0 还可以用EXPLAIN ANALYZE看实际的执行时间和扫描行数比EXPLAIN的预估值更准确适合在测试环境排查复杂SQL时使用。3.2 最常见的几种SQL改写手法改SQL不是炫技目的是减少扫描量、减少回表、减少排序和临时表。我常用的改写手段有这么几种。SELECT * 改显式字段。只查需要的列至少能让覆盖索引的命中率更高。尤其不要在大表上SELECT *这会让 MySQL 不得不回表取整行。深分页优化。这是后台列表页最常见的问题。LIMIT 1000000, 20这种写法会让 MySQL 扫描前 100 万行再丢弃非常浪费。优化思路是“延迟关联”SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders WHERE status 1 ORDER BY create_time DESC LIMIT 1000000, 20 ) t ON o.id t.id;子查询只查 id 和 create_time用覆盖索引快速定位到需要的 20 个 id再回表取完整行。我在一个 600 万行的订单表上测试过原 SQL 耗时 1.9 秒改写后降到 0.3 秒。OR 改 UNION ALL。条件字段上没有合适索引时OR 容易触发全表扫描拆成多个查询再合并每个分支都可以独立走索引。避免在索引列上做运算和函数。前端传参如果有格式要求应用层先处理好再传不要在 SQL 里靠函数兜底。JOIN 字段类型保持一致。字符集不一致或者字段类型不一致都会引发隐式转换导致索引失效。关联表的时候先看两边字段定义是不是一模一样。3.3 一个实际的慢SQL优化案例说一个后台订单列表的案例。业务方反馈翻到第 5000 页的时候接口要等将近 2 秒。原SQL大概是SELECT * FROM orders WHERE status 1 ORDER BY create_time DESC LIMIT 100000, 20;orders 表在 status 和 create_time 上有联合索引所以EXPLAIN能看到 key 不为空type 是 rangerows 预估 12 万。问题出在LIMIT 100000——MySQL 需要先扫描 10 万行再丢掉前 10 万行只留下最后 20 行。优化方案就是延迟关联先把 id 拿出来再关联原表取详情。改写之后子查询走(status, create_time)联合索引只扫描 100020 行里的索引条目而不是回表取 10 万次。再EXPLAIN一下子查询的 rows 从 12 万降到 100020其实还是那 10 万但扫描的内容轻多了耗时从 1.9 秒降到 0.3 秒。这个案例我后来在多个项目里都复用过效果都很稳定。提示任何SQL改写必须在测试环境验证结果集一致性。尤其分页、聚合、排序这类SQL差一行数据都可能是事故。先记录旧SQL的结果集再对比新SQL的结果集两边一致才能上线。4. 表结构与参数配置的取舍4.1 表设计优化类型、长度、范式很多性能问题其实是表结构设计阶段埋下的雷。字段类型选得不好索引再优化也救不回来。能用整型就不要用字符串。状态、数量、类型这些字段用 TINYINT、INT 比 VARCHAR 高效得多。整型比较快占空间小索引也更紧凑。时间字段用 DATETIME 或 TIMESTAMP不要用字符串存时间。我见过有人把时间存成2024-01-01 12:00:00的 VARCHAR然后查询时用字符串比较。这样不仅无法高效范围查询还容易因为格式不统一踩坑。用 DATETIME 之后create_time ?这种查询就能正常走索引。TEXT/BLOB 大字段单独拆表。大字段会影响行存储的紧凑度导致其他字段的查询效率下降。如果业务确实需要存长文本建议拆到附属表按主键关联查询。范式与反范式要权衡。三范式不是教条。我优化过的订单查询场景里订单表直接冗余了用户昵称查询订单列表时少一次 JOIN 用户表。读多写少的场景冗余字段换性能非常划算。代价是更新用户昵称时需要额外更新订单表里的冗余字段这个成本要提前评估。分表要克制。单表数据量确实大了先考虑索引优化和历史数据归档不要一上来就分表。业务形态没定型之前分表会引入中间件、分布式事务、跨表查询等一系列复杂度。我的经验是单表数据量到千万级、索引和SQL优化都做过了还是慢再考虑分表。4.2 关键配置参数别乱调很多人喜欢在网上找一份“最佳配置”直接粘到生产环境这是很危险的做法。硬件环境不同、业务特征不同参数最优解完全不同。我只说三个最核心、也最常被调错的参数。innodb_buffer_pool_size是 InnoDB 的缓冲池相当于数据库的“缓存仓库”数据页和索引页都放在这里。这个参数通常建议设为物理内存的 60%-75%但不是每台机器都合适。怎么判断看命中率SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_read_requests; SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_reads;命中率 读请求数 / (读请求数 读磁盘次数)。如果命中率低于 99%内存又有富余可以考虑逐步调大。注意是逐步一次调太大可能和其他进程抢内存。innodb_flush_log_at_trx_commit控制事务提交时日志的刷盘策略。值设为 1 时每次提交都刷磁盘最安全但性能开销最大设为 0 时每秒刷一次最快但崩溃时可能丢最后一秒的数据设为 2 是写入系统缓存每秒刷盘性能和数据安全性居中。对账类业务、金融类业务我建议老老实实用 1。日志、非核心业务可以评估用 0 或 2但必须让业务方知道数据丢失的容忍度。我见过一个团队为了性能把这个参数改成 0结果一次断电丢了最近一秒的订单记录业务方直接崩溃。max_connections连接数上限。MySQL 默认值通常是 151连接数调太高不一定好——每个连接都要消耗线程栈和缓冲区内存。线上如果经常报Too many connections优先排查是不是有慢查询堆积、连接池配置不合理而不是一味调高上限。注意参数调整一定基于监控数据一次只改一个参数改完观察性能变化再改下一个。复制网上的参数一顿乱改生产环境 OOM 往往就是这么来的。5. 架构层面从单库到缓存的扩展路径5.1 缓存要用得聪明先顶住读压力当数据库优化做到一定程度单机性能已经到瓶颈这时候要考虑架构层的手段。最先想到的通常是缓存。缓存放哪里Redis 是主流选择。使用模式我推荐 Cache-Aside读先查缓存命中直接返回未命中查数据库再把结果回填缓存。写先更新数据库再删除缓存。为什么写的时候是“删缓存”而不是“更新缓存”因为更新缓存存在并发问题两个请求同时写后写的先更新缓存先写的后更新缓存里就留下了旧值。删除缓存则简单得多下次读的时候自然会把新值回填进去。用缓存还要处理三个经典问题缓存穿透查询一个不存在的 key缓存里没有每次都打到数据库。解决方案是查不到结果也缓存一个空值或者用布隆过滤器提前拦截。缓存击穿某个热点 key 过期瞬间大量请求同时打到数据库。可以用互斥锁或者让热点数据不过期。缓存雪崩大量 key 在同一时间到期或者缓存实例宕机。过期时间加随机数可以避免同时过期。我之前优化过一个商品详情接口QPS 2000 时数据库连接池被打满接口超时率很高。加上 Redis 缓存后QPS 涨到 8000数据库压力几乎降为 0。缓存对读多写少场景的提升是立竿见影的但它不是银弹——写入频繁、每次写入后都要立刻读取的数据缓存的意义就不大反而增加一致性维护成本。5.2 读写分离与主从延迟读多写少的业务数据库读压力大了以后第二个常用手段是读写分离。结构上就是主库负责写从库负责读可以挂多个从库分担读流量。实现方式可以自己写路由规则也可以引入中间件。我的建议是一开始不要上重中间件先做好主从复制监控应用层把写请求路由到主库、读请求路由到从库等规模确实撑不住了再考虑中间件。读写分离最大的坑是主从延迟。刚写完主库的数据马上从从库读可能读不到。解决办法关键实时性要求高的请求强制走主库。从库延迟过大时自动摘掉该从库的读流量。对延迟不敏感的业务前端提示用户稍后刷新即可。监控主从延迟用这条命令SHOW SLAVE STATUS\G看Seconds_Behind_Master字段这个值越大说明延迟越严重。没有做好延迟监控就上线读写分离容易出线上事故这个问题我在不止一个团队见过。5.3 冷热数据分离与归档最后一个架构手段是冷热数据分离。本质上业务数据里真正高频访问的只是很小一部分大部分历史数据并不会被频繁读取但躺在主表里会拖慢索引维护和全表扫描的速度。做法是主表只保留近 6 个月或 1 年的数据历史数据定期归档到历史表、归档库或者数据仓库。应用层按时间参数分流查询。我优化过一个订单查询系统订单表有 8000 万行查询近 3 个月订单的接口经常超 1 秒。归档了 6 个月之前的数据后主表降到 1200 万行查询响应时间稳定在 100 毫秒以内全表扫描的代价也小了很多。归档操作本身要有监控尽量在业务低峰期执行避免大批量迁移占用数据库资源。6. 常见问题与排查技巧实录6.1 典型问题速查表下面是我在实际排查中经常遇到的几类问题整理成速查表方便对应症状快速定位。症状可能原因优先排查方向CPU 持续飙高大量全表扫描、排序、热点行竞争SHOW PROCESSLIST、慢日志、EXPLAIN磁盘 IO 高缓冲池命中率低、大量刷新脏页查看Innodb_buffer_pool_reads、iostat连接数打满慢查询堆积、连接池配置太小、连接泄漏processlist 状态、慢日志、连接池监控单条 SQL 偶尔慢统计信息过期、索引选择错误ANALYZE TABLE、重新 EXPLAIN固定时间段变慢定时任务、备份任务、缓存集中过期对比任务执行时间和慢SQL时间线主从延迟持续增大从库性能不足、大事务、网络延迟查看Seconds_Behind_Master、开启并行复制事务锁等待多长事务、批量更新、热点行更新查information_schema.innodb_trx这张表不是万能的但能帮你从“不知道从哪里查起”快速进入定位状态。我每次处理线上问题都会先对号入座再决定下一步动作。6.2 避坑经验与工作习惯最后分享几个我从实战里总结的习惯这些习惯救过我很多次。优化前先留证据。慢SQL文本、EXPLAIN结果、当前表结构、优化前后耗时全部截图或记录存档。改完以后对照数据才能判断优化是否有效。上线顺序要稳。加索引、改SQL都要先在测试环境跑验证结果集一致。生产环境操作要选低峰期准备回滚方案。加索引本身不复杂但大表加索引可能会锁表或长时间占用IO需要评估影响。定期ANALYZE TABLE。表数据变更剧烈时MySQL 的统计信息可能失真优化器会选错索引。定期执行ANALYZE TABLE可以刷新统计信息成本很低收益很大。不要随便用FORCE INDEX。强制走某个索引短期内可能变快但一旦数据分布变化那个索引可能变成最差选择反而导致性能暴跌。FORCE INDEX只建议用来临时验证方案不建议留到生产长期使用。每月复盘慢查询 Top 10。数据一直在变SQL 模式也在变。把优化当成持续过程而不是一次性的救火行动。我做了这么多年数据库相关工作最深的感受是MySQL 性能优化不是玄学也不是靠某个参数一锤定音而是一条“采集证据—定位瓶颈—做最小改动—验证效果”的闭环。索引和 SQL 写法是性价比最高的两块缓存和架构是放大手段参数调整只是最后的微调。遇到问题别慌先记录下来一步步拆绝大多数慢查询都能在索引和 SQL 层面解决。最后再提醒一句任何优化动作上线前都留好回滚余地数据库层面的变更谨慎永远不过分。