先聊句实在的。早几年我维护过一个电商系统平时一切正常一到活动大促接口就卡成幻灯片。打开慢查询日志一看好家伙一条统计订单的SQL把整张订单表扫了个底朝天。从那天起我就悟了MySQL调优不是装个监控面板就算完事而是一套从细节到架构的完整动作。这篇内容就是基于我自己这些年踩过的坑、扒过的官方文档总结出来的实战指南从最基础的SQL和索引优化讲起一路讲到参数配置和主从架构升级最后附上我日常排查问题的思路。适合刚接手MySQL维护的开发、自学数据库的运维新手也适合准备给系统做扩容的技术负责人把你从“数据库老慢”的焦虑里拉出来。1. 调优第一步先搞清楚你的数据库到底慢在哪1.1 慢查询日志最快定位病根的手段很多人一听说数据库慢上来就是一顿操作改参数、加机器、上缓存。可我觉得调优的第一步永远应该是观察而观察最直接的入口就是慢查询日志。开启慢查询日志很简单动态开启和持久化配置分开说。-- 动态开启当前实例即刻生效 SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL log_queries_not_using_indexes ON;注意long_query_time的单位是秒设置为 1 表示执行时间超过 1 秒的 SQL 就会被记录。动态设置只对当前实例有效重启后失效必须写进配置文件[mysqld] slow_query_log 1 slow_query_log_file /var/log/mysql/slow.log long_query_time 1 log_queries_not_using_indexes 1log_queries_not_using_indexes这个参数我强烈建议打开它会把没走索引的查询也记下来哪怕只跑了几毫秒。因为很多慢查询其实是“隐性的”单次快并发一上来就完蛋这类SQL会吃满CPU。日志有了怎么分析两种方式白嫖版用官方自带的mysqldumpslow专业版用 Percona Toolkit 里的pt-query-digest。# 按执行次数排序看看哪些SQL是高频慢查询 mysqldumpslow -s c -t 10 /var/log/mysql/slow.log # pt-query-digest 会更详细按总耗时排序还能输出报表 pt-query-digest /var/log/mysql/slow.log slow_report.txt我之前处理过一个案例线上接口每天晚高峰超时因为是JavaWeb项目一开始怀疑是连接池不够扩了连接数依旧没解决。后来翻慢查询日志发现每天有几十万次同一条查询——SELECT * FROM order WHERE status 0 ORDER BY create_time DESC LIMIT 10这个status列没有索引MySQL只能全表扫描。加了个普通索引之后查询耗时从 800 毫秒降到 5 毫秒接口也就彻底顺了。1.2 实时状态与连接数解读慢查询日志能告诉我们哪些SQL历史表现差但要想看“此刻”数据库在干嘛得用SHOW PROCESSLIST。SHOW FULL PROCESSLIST;这个命令会列出所有正在执行的连接重点看State字段。如果看到大量Sending data通常是有大查询正在全表扫描或者排序如果看到Waiting for table metadata lock那多半是有DDL语句没结束后面排队的查询全被卡住如果是Locked就要考虑是不是行锁冲突了。再看几个全局状态指标SHOW GLOBAL STATUS LIKE Threads%; SHOW GLOBAL STATUS LIKE Innodb_row_lock%; SHOW GLOBAL STATUS LIKE Bytes_received;比较关键的两组Threads_connected是当前连接数Threads_running是正在跑SQL的连接数。如果running长期大于 CPU 核心数说明SQL效率有严重问题。Innodb_row_lock_waits和Innodb_row_lock_time是行锁等待次数和总耗时数值太高说明存在事务竞争。顺带说一下很多人被提醒“连接数太多”就急着去调max_connections但连接数只是表象本质是SQL跑得太慢一个连接的事情拖成了几十个连接。所以调优的核心永远在SQL层面而不是无限堆资源。1.3 瓶颈分类CPU、IO、锁方向不同别乱治同样是“慢”但慢的原因可能天差地别。我习惯先把问题分成三类症状可能原因优先排查方向CPU 使用率飙升大量排序、聚合计算、并发解析SQL优化、加索引、减少不必要函数计算IO 等待高内存命中率低、刷盘频繁、数据量大innodb_buffer_pool_size、磁盘换SSD、归档冷数据锁等待严重事务过长、热点行更新、DDL阻塞查长时间事务、拆分大事务、错峰执行DDL内存不足导致SWAPbuffer pool 设置过大、服务器内存被其他进程吃掉按实际物理内存重新分配参数举个例子如果top里显示 mysql 进程 CPU 200%但磁盘IO很低那十有八九是SQL本身算得慢比如大范围排序、子查询嵌套这时候加索引比换机器管用。反过来如果iostat显示 util 接近100%CPU反而有大量iowait那说明数据页在磁盘和内存之间频繁搬运重点就应该看buffer pool够不够大。2. SQL与索引优化花最少成本换最大收益2.1 索引设计不是越多越好我见过不少项目表里每个字段都建了索引理由很直接“查得快”。但索引的本质是一棵额外的B树每次插入、更新、删除都要同步维护索引多了写性能反而会崩。正确的做法是“索引设计跟着查询走”先看SQL的WHERE、JOIN、ORDER BY条件再决定建什么索引。先明确几个基本规则左前缀原则联合索引(a, b, c)可以命中a、a,b、a,b,c三种情况的查询但WHERE b 1 AND c 2这种跳过第一列的查询用不上这个索引。覆盖索引如果查询需要的列全部包含在索引里就不用回表效率极高。比如SELECT name FROM user WHERE age 20如果有一个(age, name)联合索引那这个查询直接走索引就出结果了。隐式类型转换WHERE phone 13800138000如果phone是VARCHAR数字会被转成字符串再比较但MySQL可能因为类型转换放弃索引。函数和运算WHERE DATE(create_time) 2024-01-01会对索引列做函数运算索引失效。正确写法是WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00。我之前处理过一个报表统计的需求原SQL是这样SELECT user_id, COUNT(*) FROM order_record WHERE DATE(create_time) CURDATE() GROUP BY user_id;这张表有两千万行create_time上明明有索引但就是因为套了个DATE()函数每次都要全表扫描。改成范围查询之后一个原本要跑7秒的统计SQL直接落到0.08秒。这就是一个典型的高性价比优化。2.2 抓一抓SQL里的坏味道有些SQL写法不报错但就是慢属于典型的“坏味道”。第一类是SELECT *。应用层可能只需要三个字段却把一行里几十个字段全部捞出来。如果这行数据很大又需要回表网络的传输量、InnoDB的buffer pool占用都会成倍增加。我的习惯是只查需要的列并且尽量保证这些列能被索引覆盖。第二类是深分页。应用里常见的分页SQLSELECT * FROM order_record ORDER BY id LIMIT 1000000, 20;MySQL会先读1000020行再丢掉前1000000行这个OFFSET越深越慢。优化方式可以用“延迟关联”SELECT t.* FROM order_record t INNER JOIN (SELECT id FROM order_record ORDER BY id LIMIT 1000000, 20) tmp ON t.id tmp.id;先在主键索引上定位到目标行的id再回表取完整行内存中的临时数据量小很多。第三类是乱用OR。比如WHERE status 1 OR status 2如果有两个索引分别服务于不同条件优化器可能选择全表扫描。能用UNION ALL拆开的查询就去拆或者干脆用INWHERE status IN (1, 2)走索引的情况会好很多。还有一点容易被忽略长事务。一个事务里既写了订单又去查了大量数据做校验持锁时间过长后面的更新全部排队。业务上能拆就拆不能拆就尽量缩小事务范围把查询放到事务外面。2.3 EXPLAIN 的正确打开方式任何SQL要上线我都建议先跑一遍EXPLAIN。EXPLAIN SELECT user_id, order_amount FROM order_record WHERE order_no 20250101001 ORDER BY create_time DESC;重点看这几列type从好到差大概是const、eq_ref、ref、range、index、ALL。看到ALL基本就是全表扫描要警惕看到index也不一定好可能是扫描了整个索引树。key实际使用的索引名。如果key为NULL说明没走索引。rows预估扫描的行数。这个数字和实际执行时间强相关越小越好。Extra容易出现Using filesort文件排序、Using temporary临时表这两种情况需要优化如果出现Using index就是走了覆盖索引是最理想的状态。这里有个容易被新手忽略的细节rows是优化器的估算值它依赖表的统计信息。如果表数据量剧烈变化但统计信息陈旧执行计划可能选错索引。这时候ANALYZE TABLE 表名;更新统计信息往往有奇效。3. MySQL参数调优配置文件里的那些数字3.1 innoDB缓冲池最重要的内存参数MySQL 里最影响性能的单个参数我觉得就是innodb_buffer_pool_size它是InnoDB用来缓存数据页和索引页的内存区域。如果这个值太小数据放不下每次查询都要去磁盘读那性能就全浪费在IO上了。默认值一般是128M对稍微有点体量的库来说根本不够。我的经验是如果服务器是专门的MySQL实例可以把这个值设置为物理内存的50%到70%。举个例子一台16G内存的机器系统自身和其他进程预留个4G剩下12G里分出8G给buffer pool是比较稳的[mysqld] innodb_buffer_pool_size 8G innodb_buffer_pool_instances 8instances参数是为了把大缓冲池拆成多个小实例减少并发访问时的互斥锁竞争。8.0版本里还有一个innodb_buffer_pool_chunk_size默认128M修改的时候最好让buffer_pool_size是chunk_size的整数倍否则实际分配会向上取整可能比预期的多占内存。改这个参数时要注意如果机器上还跑着Web服务中间件、Redis之类的别贪心留足余量。我之前犯过类似的错给一台业务数据库服务器设了70%内存给buffer pool结果操作系统开始SWAP反而慢得更厉害——MySQL的数据页被换到磁盘等于绕了一圈又回到磁盘IO。MySQL 5.7 支持动态修改SET GLOBAL innodb_buffer_pool_size 8G;但配置文件里也要同步改不然重启又变回原样。3.2 日志与刷盘策略安全性和性能的取舍很多初学者对innodb_flush_log_at_trx_commit这个参数一头雾水我打个比方事务提交的时候MySQL要往redo log里写一条记录这个参数决定这条记录什么时候真正落到磁盘。值为1每次事务提交都刷盘最安全但性能开销最大。值为2事务提交时只写入操作系统缓存每秒批量刷一次盘性能好很多但数据库所在机器断电时可能丢1秒左右的数据。值为0由系统每秒刷盘崩溃时可能丢更多数据。默认是1绝大多数业务我建议保持1尤其是涉及订单、支付、账号余额的场景。只有当系统对性能极度敏感、又能接受最后一两秒数据丢失时比如缓存系统、日志系统才改成2。还有个参数innodb_log_file_size默认较小生产环境建议调大比如512M或1G。它的作用是保存redo log如果太小InnoDB还没等日志文件循环复用就得频繁做checkpoint把脏页刷回磁盘会增加磁盘写入压力。调大以后批量更新和导入数据的效率会明显改善。注意8.0版本里这个参数默认48M实际生产基本不够看。[mysqld] innodb_flush_log_at_trx_commit 1 innodb_log_file_size 1G innodb_log_buffer_size 16M这里我想强调一个原则参数调优永远是业务导向的取舍。自己先想清楚业务能不能容忍数据丢失再去动安全底线相关的参数顺序千万不能反。3.3 连接数与线程配置max_connections是很多人喜欢乱调的参数默认151看到“连接数满了”就往上加。说实话这个参数真不是越大越好。每个连接在MySQL内部至少要占一些线程和内存空间排序缓冲、join缓冲也都是按连接分配的连接数开个几千光内存就吃掉几个G。我更建议的做法压榨SQL效率让连接能快速释放。连接池里的连接数一般控制在几十到一两百就够用了。如果业务洪峰特别猛优先考虑在应用层加缓存、限流而不是把数据库连接数调上天。旁边几个相关参数也可以顺手调一下[mysqld] max_connections 500 thread_cache_size 64 table_open_cache 2048thread_cache_size是线程缓存能减少频繁创建销毁线程的开销table_open_cache控制表描述符缓存的数量如果SHOW GLOBAL STATUS LIKE Opened_tables一直飙升说明这个值不够。3.4 参数调优的黄金流程我自己调参数不会一次改一堆理由是改了多个参数之后如果效果变好根本无法判断是哪个起了作用如果变差回滚都不知道回哪个。稳妥流程是先备份原始配置文件记录当前所有关键参数。只改一个参数重启或动态调整。跑基准测试比如用sysbench模拟读写压力对比改动前后的QPS、延迟。观察业务态监控确认没有异常才进行下一个参数调整。sysbench --testoltp --oltp-table-size1000000 --mysql-userroot --mysql-passwordxxx run建议你也准备一份自己的参数速查表我列一个常用版本参数推荐值参考说明innodb_buffer_pool_size物理内存50%~70%InnoDB数据页缓存最重要innodb_log_file_size512M~1Gredo log总大小太小频繁刷盘innodb_flush_log_at_trx_commit1安全或2性能事务刷盘策略max_connections按实际压测别乱调每个连接占内存sort_buffer_size2M~4M排序缓冲太大浪费内存join_buffer_size2M~4Mjoin缓冲同样别贪大long_query_time1慢查询阈值4. 架构升级从单点走向主从复制与读写分离4.1 为什么要做主从复制单机MySQL能扛的压力始终有限数据库到了一个体量之后最自然的架构升级方向就是主从复制。核心思路主库负责写从库负责读把读流量从主库身上卸下来。这个设计在绝大多数业务里都很划算因为互联网应用基本都是读多写少可能80%的请求都在SELECT。一台从库能扛住的读压力很可观扩从库的成本远低于换更强的单机。主从复制的原理也不复杂主库把所有的写操作记录到binlog二进制日志从库通过网络把binlog拉到自己的relay log再在本地重放这些日志等效于把主库执行过的事务在自己身上再执行一遍。这里给一个朴素的比喻主库是写日记的人从库是抄日记的人。主库每写下一行从库就誊抄一行最后两个人的日记内容保持一致。4.2 主从复制配置实操以MySQL 8.0为例两台服务器一主一从我将配置过程拆成四步。第一步主库和从库的配置文件里都要有唯一的server-id主库要开启log-bin# 主库 my.cnf [mysqld] server-id 1 log-bin mysql-bin binlog_format ROW gtid_mode ON enforce_gtid_consistency ONGTID全局事务标识符是现代MySQL复制推荐的方式它给每个事务分配一个全局唯一的ID从库据此判断哪些事务已经执行过主从切换时定位位置会方便很多。第二步在主库创建复制专用账号CREATE USER repl% IDENTIFIED BY YourPass123; GRANT REPLICATION SLAVE ON *.* TO repl%; FLUSH PRIVILEGES;第三步在主库导出初始数据并传到从库mysqldump --single-transaction --source-data2 -u root -p --all-databases all_db.sql # MySQL 8.0.26 用 --source-data老版本用 --master-data scp all_db.sql rootslave:/tmp/在从库上执行导入mysql -u root -p /tmp/all_db.sql第四步在从库执行挂接动作MySQL 8.0 里START SLAVE改成了START REPLICACHANGE REPLICATION SOURCE TO SOURCE_HOST192.168.1.10, SOURCE_USERrepl, SOURCE_PASSWORDYourPass123, SOURCE_AUTO_POSITION1; START REPLICA;然后看状态SHOW REPLICA STATUS\G重点关注Seconds_Behind_Source这个值表示从库延迟了多少秒。正常情况下应该非常接近0。出现Last_IO_Errno或Last_SQL_Errno非0就说明链路断了需要根据错误码处理。从库上还要注意在配置文件里把read_only 1设为只读避免人为误写数据导致复制链路紊乱。4.3 读写分离的落地方式与坑主从复制搭好了应用层怎么把读和写分到不同的库常见有三种做法应用代码层自己写数据源路由写操作走主库读操作走从库。灵活但侵入业务代码。数据库中间件ProxySQL、MyCat这类工具对应用透明SQL自动路由。连接驱动层MySQL Connector/J、Spring的AbstractRoutingDataSource。我早期的项目比较简单直接在Spring里配了读写分离spring: datasource: dynamic: primary: master strict: false datasource: master: url: jdbc:mysql://192.168.1.10:3306/app slave: url: jdbc:mysql://192.168.1.11:3306/app用动态数据源注解在方法上标明DS(slave)或DS(master)。这里必须提醒一个经典坑主从延迟导致读到旧数据。比如用户刚下单成功立刻刷新页面要看到订单结果请求打到从库复制还没完成页面上就看不到刚下的单。这种场景一般有两个处理思路一是“强制路由主库”对强一致性的请求直接读主库二是把刚写入的数据同步刷新到Redis读接口优先走缓存。主从真正的高可用还需要再进一步。比如半同步复制、MHA、Orchestrator、MySQL 8.0的InnoDB Cluster都有各自的适用场景。我的建议是先从标准异步复制入手跑通流程观察稳定性再考虑引入高可用组件不要一上来就把链路搞复杂。5. 再进一步分库分表与容量规划5.1 什么时候才真的需要分库分表这个话题是很多开发最纠结的表数据量到了几千万行就想着分表但我见过不少案例表面上数据量很大实际上一条合适的索引就把问题解决了根本不需要拆。我觉得触发分库分表决策的信号不是单纯的“行数”而是以下几个现象同时出现时才值得考虑单表数据量巨大即使命中索引查询延迟也满足不了业务要求。写入并发太高单机写入能力已经到天花板。单库的连接数、IO、磁盘持续告警且无法通过升级硬件解决。已经尝试过归档冷数据、清理索引、加缓存仍然扛不住。分库分表是复杂度最高的优化手段一旦做了跨库查询、分布式事务、全局主键、数据迁移这些问题全会冒出来。所以我的判断顺序永远是先SQL优化再参数优化然后加缓存和归档最后才轮到分库分表。5.2 垂直拆分与水平拆分的选择分库分表有两个方向。垂直拆分是把不同业务模块拆到不同库。比如把用户表、商品表放一个库订单表放另一个库各拆各的负责团队。好处是隔离故障、独立扩容但跨库的JOIN就做不了了只能在应用层组装。水平拆分是把同一张表的数据按某个维度分到多个表或多个库。比如user_id % 16拆成16个分片每个分片存一部分数据。水平拆分最关键的是选对分片键。分片键的选择直接决定这个架构是加分项还是灾难。理想的分片键是查询中最常用、且值分布均匀的字段。比如订单表按user_id分片那么“查某用户的订单列表”这个高频查询就能精确定位到单个分片但如果有一天产品经理要“按店铺查订单”这个查询只能对所有分片广播用户体验会急剧下降。分片键一旦定下来后续改起来极其痛苦。我曾经接手过一个系统早期用订单号哈希分片后来要支持按用户维度汇总只能用中间件做全分片扫描跑一次要几十秒最后不得不做一次全量数据重分布。5.3 数据迁移与容量规划真到了拆分这一步数据迁移和灰度切换是技术含量最高的环节。常用的姿势是“双写同步 校验追平”思路不复杂但要做细在业务代码层同时写旧库和新库旧库仍作为线上主库。用数据同步工具把旧库数据持续同步到新库比如基于binlog解析的canal。写校验任务定期对比新旧两边的数据差异部分用历史数据补齐。观察一段时间确认数据一致把读流量灰度切到新库最后把写流量也切过去关掉旧库写入。这类工作我建议座位旁边常备一张容量规划表核心指标包括每天新增行数、binlog增长速度、磁盘剩余量、慢查询数量趋势。数据库的增长很少是线性的月初月末、节假日、大促都会产生明显峰值不提前盯着等磁盘报警再清理就晚了。6. 常见问题与排查技巧实录6.1 经典报错Cant connect to local MySQL server through socket这大概是MySQL新手最常遇到的报错之一完整提示是ERROR 2002 (HY000): Cant connect to local MySQL server through socket /tmp/mysql.sock (2)别慌按顺序排查检查服务是否在运行ps -ef | grep mysqld或者systemctl status mysql。确认socket文件的路径。客户端连接时默认找/tmp/mysql.sock但服务端可能把socket放在/var/lib/mysql/mysql.sock两者对不上就会报这个错。查看服务是否因为磁盘满、权限问题、配置错误而反复重启失败journalctl -u mysql或看错误日志。如果只是连本机报错可以改用TCP方式连接mysql -h 127.0.0.1 -P 3306 -u root -p能连上说明就是socket路径配置问题。6.2 慢查询突然变多怎么定位原本稳定的系统某天突然慢查询数量上升我的排查顺序是这样的先看SHOW GLOBAL STATUS LIKE Uptime如果实例曾经重启那可能是参数没生效或者热数据缓存被清空。然后看最近是否上线了新SQL用performance_schema自带的语句统计表能按摘要聚合SELECT DIGEST_TEXT, COUNT_STAR, SUM_ROWS_EXAMINED, SUM_TIMER_WAIT FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_TIMER_WAIT DESC LIMIT 20;这个查询会列出所有SQL按总执行耗时排序很容易揪出谁是“新晋拖油瓶”。统计信息陈旧也可能导致执行计划突变这时候ANALYZE TABLE刷新一下统计信息配合EXPLAIN检查是否还走原来的索引。6.3 主从延迟越来越大处理思路主从架构下Seconds_Behind_Source持续增大就要怀疑以下几类原因从库硬件配置比主库差回放速度跟不上。主库有大事务比如一次更新几百万行binlog里的这个大事务在从库上要连续执行很久。主库有高频DDL从库回放DDL时如果目标表很大会长时间锁表。从库上还运行着其他分析查询挤占了回放线程的资源。MySQL 5.7 默认开启并行复制可以通过SHOW REPLICA STATUS看Replica_Parallel_Workers是否大于1。如果没开可以动态打开STOP REPLICA; SET GLOBAL replica_parallel_workers 4; START REPLICA;还需要注意从库不要只挂一张表或一个库如果某个库的表特别大并行复制也容易退化成单线程瓶颈。6.4 我日常会用的工具清单最后分享一份我机子里常驻的排查工具帮你少走弯路mysqldumpslow快速分析慢查询日志系统自带。pt-query-digestPercona Toolkit里的SQL日志宝藏分析器。pt-heartbeat高精度监测主从延迟比Seconds_Behind_Source更准。mysqld_exporter Grafana Prometheus可视化监控指标齐全强烈建议搭一套。MySQL Workbench图形化看性能报表、执行计划适合查单条SQL。我自己现在给团队定的规矩很简单每条新SQL上线前过一遍EXPLAIN每天扫一次慢查询日志每周看一次主从延迟和磁盘增长。坚持三个月大部分隐患都能在爆发前被你摁住。调优这件事做到最后就是个体力活加细心活没有一套神级参数能解决所有问题每一个上线过的参数、每一条改过的SQL都要记录好变更原因和效果形成自己的基线数据。等下一次系统再变慢你翻一翻自己的历史记录比翻文档快得多。