平时跟人聊MySQL优化听到最多的一句话是“我明明加了索引为什么SQL还是慢”问下去往往发现慢SQL都没定位到执行计划也没看过纯粹靠“加索引靠感觉”在试。优化这件事最怕的不是问题复杂而是没搞清楚慢在哪一步就开始改最后配置也调了、索引也加了线上该慢还是慢。这个系列我打算按专题拆开讲第一篇先讲清楚优化前必备的定位手段再重点拆解索引优化和SQL写法优化最后把连接池和几个关键参数一起带过。整体围绕三条线展开慢SQL日志怎么开、执行计划怎么看、索引和写法怎么改。适合刚接触MySQL优化的开发同学也适合想把线上慢查询彻底梳理一遍的运维和一线架构师。这篇里给到的排查方法、SQL写法和参数建议都是我在真实业务环境里验证过的可以直接拿去对照着检查自家的数据库。1. 优化前先搞清楚你的MySQL到底慢在哪我发现很多人一上来就调参数、加索引好像数据库优化就这两件事。但实际上定位慢查询才是优化的第一步。一台数据库里跑着几十上百条SQL你连哪条是元凶都不知道就去改全局配置这不是在做优化是在碰运气。1.1 慢SQL日志最廉价的性能诊断工具MySQL自带的慢查询日志可能是性价比最高的诊断工具没有之一。开启它不需要重新编译也不需要安装额外插件但它能把执行时间超过阈值的SQL全部记录下来让你一眼找到真正需要优化的语句。开启慢查询日志很简单在MySQL的配置文件一般是/etc/my.cnf中添上slow_query_log 1 slow_query_log_file /var/log/mysql/slow-query.log long_query_time 1 log_queries_not_using_indexes 0然后重启MySQL或者直接在客户端里执行SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL slow_query_log_file /var/log/mysql/slow-query.log;这里我特别说明几个参数的含义。long_query_time的单位是秒在5.7及以上的版本里还支持小数比如0.5就是记录执行超过500毫秒的SQL。我通常不建议一开始就设成0那样慢日志会爆炸式增长尤其在高并发业务下可能一小时就把磁盘写满。从1秒起步逐步下调是比较稳妥的做法。慢日志会记录哪条SQL、用了多长时间、扫描了多少行、返回了多少行。扫描行数和返回行数的比例尤其值得关注比如一条SQL扫描了10万行只返回10行说明索引和过滤条件大概率有问题。这就是典型的“扫描范围过大”。慢日志文件出来后不要直接拖进文本编辑器里肉眼翻几百MB的文件你看不完。我推荐两个工具mysqldumpslowMySQL自带语法简单能按平均查询时间、总查询时间、扫描行数排序还能把条件里的具体数值用N和S归一化方便统计同类SQL。pt-query-digestPercona Toolkit里的主力工具输出报表更专业能按响应时间占比把慢SQL排名还会提炼每条SQL的执行频率、平均耗时、最大耗时。# 统计访问次数最多的20条慢SQL mysqldumpslow -s c -t 20 /var/log/mysql/slow-query.log # 用Percona工具分析慢日志并输出排名 pt-query-digest /var/log/mysql/slow-query.log我自己的习惯是先跑一遍pt-query-digest看整体排名挑出响应时间最长的Top 10再结合业务场景判断哪些SQL值得优化。不是所有慢SQL都要立刻改有些就是低频的深夜定时任务执行3秒也无伤大雅真正要优先处理的是那种“执行频率高、单次也慢”的SQL这类对整体吞吐量的影响最大。1.2 从执行计划读懂MySQL的“内心戏”定位到慢SQL之后下一个标准动作就是查看它的执行计划。MySQL里查询执行计划非常简单EXPLAIN SELECT * FROM orders WHERE user_id 123 AND status 1;执行计划的每一列都不是摆设但优化时优先看四个关键字段type、key、rows、Extra。type表示访问类型从好到差大致是consteq_refrefrangeindexALL。看到const说明走的是主键或唯一索引性能最好看到ALL就是全表扫描基本是优化的重点对象。key表示实际用到的索引如果是NULL说明这条查询没走任何索引大概率是全表扫描。rows是预估需要读取的行数值越大消耗越大。Extra里藏着很多额外信息比如Using filesort说明排序没走索引Using temporary说明用了临时表Using index说明查询在索引上就能完成也就是覆盖索引。我希望大家养成一个习惯写任何一条查询前都把EXPLAIN打在前面看一眼。不用等线上出问题了再来看执行计划开发阶段看一遍很多低级问题就能当场暴露。举一个具体例子。假设有一张用户订单表你执行EXPLAIN SELECT * FROM orders WHERE status 0;如果结果里type ALL、rows 2000000说明这条SQL把整张200万行的表全扫了一遍。原因可能是status字段的区分度太低也可能是根本就没建索引。看到这种执行计划你再去谈后续的索引优化、SQL改写才算有依据。2. 索引优化性能提升的立竿见影手段索引是InnoDB里最核心的优化手段没有之一。很多慢SQL问题归根结底就是索引没用好该建的没建、建错了结构、或者建了没被用到。这一章把索引相关的关键点一次讲透。2.1 索引失效的几种典型场景我见过太多案例索引明明建了EXPLAIN一看还是没走问题往往出在SQL写法上。下面这几种索引失效的场景是真正的高频事故。第一种对索引列使用函数或运算SELECT * FROM user WHERE YEAR(create_time) 2024;create_time上如果建了索引这种写法也无法使用。因为MySQL要先对每一行执行YEAR()函数计算完的结果和索引里的值无法直接匹配。改成范围条件就能命中索引SELECT * FROM user WHERE create_time 2024-01-01 AND create_time 2025-01-01;第二种隐式类型转换SELECT * FROM user WHERE phone 13800001111;如果phone列是varchar类型但你拿一个数值去比较MySQL会把字段类型隐式转换为数值等于在索引列上做了一次隐式函数处理索引失效。解决方法是规范参数类型尤其是Java、Go这类语言里的数值类型传参时务必和表字段保持一致。第三种LIKE模糊查询前缀带通配符SELECT * FROM article WHERE title LIKE %MySQL优化%;前缀带%的模糊匹配无法使用索引只有MySQL%这种后模糊才能命中索引。如果业务场景确实需要前模糊要考虑搜索引擎或者全文索引。第四种OR条件中有一个字段没索引SELECT * FROM user WHERE name 张三 OR status 1;只要OR两边的字段有一个没有索引整个查询就可能退化成全表扫描。改写思路是拆成两个查询用UNION ALL合并。我把这些失效场景整理成一张自查表放在文末以后遇到问题可以直接对照。2.2 复合索引的设计思路别把顺序当儿戏复合索引也常叫联合索引是最容易用错也最容易发挥威力的索引类型。它的核心规则是“最左前缀原则”查询条件必须从索引最左列开始且不能跳过中间的列索引才会生效。假设在orders表上建联合索引(user_id, status, create_time)那么条件里包含user_id的查询大概率能走该索引条件里没有user_id只有status和create_time索引用不上条件是user_id 1 AND create_time 2024-01-01跳过了中间的status那只有user_id这一列能用到索引。所以设计复合索引的列顺序是一门取舍的学问。我个人的经验有两条等值条件列放前面范围条件列放后面。因为等值条件能精确定位范围条件只能缩小范围把等值列放前面能让B树快速收敛。区分度高的列优先。比如user_id的区分度远高于status那肯定把user_id放前面。如果一个字段90%的行都是同一个值加在索引里对扫描行数的帮助极其有限。建索引之前用一条SQL看下字段的区分度SELECT COUNT(DISTINCT user_id) / COUNT(*) AS user_dist, COUNT(DISTINCT status) / COUNT(*) AS status_dist FROM orders;区分度越接近1说明这个列的值越唯一排前面效果越好。2.3 覆盖索引、回表与索引下推三个容易混淆的概念这三个概念很多开发同学分不清但它们直接决定了查询快慢。回表是指通过二级索引找到了主键再用主键去聚簇索引里查完整行记录的过程。回表本身不是错误但回表次数多了IO开销自然就上来了。如果你的查询只需要索引列上的数据MySQL就会走覆盖索引直接从索引里拿结果不需要回表。覆盖索引就是“查询需要的字段恰好都在索引里”的状态。比如SELECT user_id, status FROM orders WHERE user_id 123;如果索引是(user_id, status)那这条查询根本不用回表Extra里会显示Using index性能非常理想。所以SQL写法上尽量只select需要的字段不要无脑SELECT *这不仅仅是减少网络传输的问题更是给覆盖索引创造机会。索引条件下推对应Extra里的Using index condition是MySQL 5.6引入的优化。核心逻辑是存储引擎在读取索引时先根据索引里的字段做一次条件过滤过滤掉不满足条件的记录再回表。这样回表次数会大幅减少。举个例子在(user_id, status)联合索引下执行SELECT * FROM orders WHERE user_id 123 AND status 1;status的条件会在读取索引时就参与判断只有同时满足user_id和status的索引项才会回表。这个特性默认开启但如果条件涉及的字段不在索引里也就无从下推了。对这三者的理解可以直接转化为建索引时的判断依据能覆盖就不要回表能下推就不要全量回表。3. SQL写法优化几个干翻慢查询的实战技巧索引优化解决的是“路修得好不好”的问题SQL写法优化解决的是“会不会开车”的问题。同一张表、同一个索引写法不同性能可能是天壤之别。3.1 UPDATE、DELETE别偷懒小心大事务拖垮主库慢查询不止SELECTUPDATE和DELETE如果写不好杀伤力甚至更大。最典型的问题是一次更新影响几百万行导致大事务长时间持有锁binlog和主从复制全部跟着遭殃。UPDATE orders SET status 2 WHERE status 1;这条SQL听上去没毛病但如果status 1的行有300万条它会一次性更新300万行事务执行期间对InnoDB的行锁、undo log空间、binlog写入量都会产生巨大压力。我的建议是分批更新-- 每批更新1000条循环执行直到全部完成 UPDATE orders SET status 2 WHERE status 1 LIMIT 1000;执行完一批记录一下影响行数再继续下一批。这样单事务短暂、锁持有时间短主从延迟的风险也大大降低。另一个UPDATE常见坑是“忘记带条件直接全表更新”。生产环境里这条命令一旦执行基本只能靠闪回或者备份恢复。所以推荐在客户端工具里默认开启--safe-updates它会拦截不带WHERE条件的UPDATE或DELETE等于给操作加了一层保险。3.2 分页深了怎么办LIMIT 100000, 20 的经典困境分页查询是业务系统的高频场景但页码很深时SQL性能会急剧恶化SELECT * FROM orders ORDER BY id LIMIT 100000, 20;这条SQL的逻辑是先把前100020行全部查出来再丢掉前100000行只返回最后20行。前面这些行全部是无效扫描纯粹浪费IO。优化方案有两种。一种是利用主键做条件过滤SELECT * FROM orders WHERE id 100000 ORDER BY id LIMIT 20;这种写法只扫描目标位置之后的数据扫描行数大幅下降。但前提是排序字段和主键顺序一致且业务上能接受“按id翻页”这种模式。另一种是延迟关联先查需要的主键再回表取完整数据SELECT o.* FROM orders o INNER JOIN (SELECT id FROM orders ORDER BY id LIMIT 100000, 20) t ON o.id t.id;子查询先走覆盖索引把20个主键找出来再回大表查这20行的完整数据深分页的消耗集中在索引扫描上而不是大表的全量数据扫描上效果立竿见影。3.3 JOIN怎么优化小表驱动大表是铁律MySQL的表关联最怕的是驱动表选择错误。在JOIN操作里MySQL会选一个表作为驱动表先读它的数据再去被驱动表里匹配。“小表驱动大表”意思是尽量让数据量小的表先查然后拿它去匹配大表这样可以减少被驱动表的扫描次数。SELECT * FROM small_table s LEFT JOIN large_table l ON s.id l.s_id;如果驱动表有100行被驱动表有1000万行且关联字段有索引那匹配次数就是100次索引查询。反过来就变成1000万次索引查询性能相差巨大。实际执行中驱动表的选择通常由优化器自动决定我们能做的是两条保证JOIN关联字段上有索引这是最基本的。没有索引被驱动表就只能全表扫描。使用STRAIGHT_JOIN指定驱动表顺序当优化器选错驱动表、且EXPLAIN里能看到明显问题时可以用这个关键字强制调整。少用子查询嵌套、把过滤条件尽量下沉到JOIN之前也是减少关联数据量的有效手段。3.4 存储过程和预编译语句能复用就别重复解析慢SQL优化还有一个容易忽略的点SQL文本的重复解析和网络往返。这不一定在慢日志里体现得很明显但在高并发场景下特别“吃”CPU。存储过程适合承载复杂的、需要多次调用的批处理逻辑。把一段多步骤的SQL封装成存储过程可以一次解析、多次执行减少客户端和数据库之间的交互次数。比如电商每天凌晨的订单归档任务用存储过程循环处理历史数据比用程序一条条发SQL要快不少。但存储过程也不是越多越好。它在复杂业务里容易变成“黑盒”后人接手时维护成本高而且存储过程内部的临时表使用不当同样可能产生性能瓶颈。我的经验是批量任务和复杂报表这类低频但数据量大的场景适合用存储过程而高频在线交易还是让应用层写清晰的SQL更灵活。另外预编译语句PreparedStatement也是减少重复解析的有效手段。在应用层使用参数化查询除了防止SQL注入还能让同一条SQL只被解析一次后续传入不同参数直接复用执行计划。4. 连接池与参数优化别让配置拖了后腿SQL和索引都优化到位了还有一类慢的来源常常被忽略数据库连接和全局配置。连接建立频繁、连接池参数不合理、缓冲区设置过小都会让看起来不慢的SQL在并发场景下逐步变慢。4.1 连接池大小到底设多大不是越大越好很多开发同学对连接池有个误解连接数设得越大数据库性能就越高。实际恰恰相反连接过多会导致线程上下文切换频繁InnoDB内部锁竞争加剧吞吐量不升反降。业界比较经典的经验公式是连接数 CPU核心数 × 2 1。这个公式的逻辑是数据库操作大多是IO密集型每个CPU核心在等待磁盘IO时可以同时处理两三个连接超过这个数量只会增加排队和切换成本。拿8核的数据库服务器举例连接池大小设为17左右是合理的。如果业务高峰期确实不够用优先分析有没有SQL执行过慢占用了连接而不是盲目调大连接数。HikariCP这类连接池还有一个容易被忽略的配置项connectionTimeout和maxLifetime。前者控制获取连接的超时时间后者控制连接的最大存活时间建议比MySQL的wait_timeout短一些否则连接被数据库端回收后客户端还在继续使用就会出现“连接假死”的问题。4.2 需要重点关注的MySQL参数MySQL的全局参数有几百个但真正影响日常业务性能的其实就那几个。我挑出最值得关注的参数整理成一张表参数建议说明innodb_buffer_pool_size物理内存的60%~70%InnoDB缓存数据和索引的内存区域设置过小会导致频繁磁盘IOmax_connections根据业务峰值设置建议300~1000设置过大反而会有大量空闲连接拖垮系统sort_buffer_size建议2MB起步每个会话独立的内存排序缓存过大反而浪费内存join_buffer_size建议2MB~8MB无法用索引连接时的缓冲区不是越大越好tmp_table_size建议16MB~64MB临时表超过该大小会落盘到磁盘性能骤降wait_timeout建议60~180秒空闲连接超时时间太长会堆积无效连接innodb_buffer_pool_size是所有参数里最优先调整的。它的核心作用是缓存表和索引的数据页。如果命中率低每个查询都要去读磁盘再快的SSD也比内存慢一个量级。你可以在MySQL里通过SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_read%;查看Innodb_buffer_pool_read_requests和Innodb_buffer_pool_reads两者比值就是缓存命中率。一般业务场景下命中率在95%以上算健康如果低于90%优先考虑扩大缓冲池。4.3 数据库连接池相关的常见误区连接池这块我也想多讲几句因为实际踩坑的人太多了。很多人把连接池大小调到200以为高并发就稳了结果数据库CPU飙到100%所有SQL都在排队。这时把连接池调回20压测数据反而好了不少。还有一类问题是连接数上限和应用实际并发不匹配。MySQL默认的max_connections是151如果你把应用连接池调到200高峰期就会有几十个连接被拒掉报Too many connections。调池子之前先看下当前连接数的基线SHOW GLOBAL STATUS LIKE Threads_connected;把Threads_connected和max_connections对比一下留出30%左右的余量再决定连接池上限设多少才是比较稳妥的思路。5. 常见问题与排查技巧实录这一章把我这些年处理MySQL性能问题时最常碰到的场景和排查思路整理出来。下次再遇到“数据库很慢”的问题你可以按这个顺序做一遍体检。5.1 线下快、线上慢是怎么回事这是一个老生常谈但又反复出现的问题。同一条SQL在本地测试库上执行10毫秒一到线上就变成1秒。原因通常出在三个方面数据量差异。本地测试表几万行索引随便扫线上表几千万行优化器选的索引方案完全不同。所以测试环境最好恢复一份接近生产环境的脱敏数据。统计信息差异。优化器是根据rows预估来选择执行计划的如果统计信息不准可能选错索引。ANALYZE TABLE tablename可以重新收集统计信息。并发环境差异。本地就你一个人跑SQL线上几百个请求同时在抢锁、排队。有时不是SQL本身慢而是锁等待和IO争用造成的慢。通过SHOW ENGINE INNODB STATUS;可以看到当前事务锁等待的情况也能看到最近死锁和阻塞的线索。5.2 慢SQL排查速查表我把高频问题和对应的排查动作整理成了一个速查表建议收藏备用。现象可能原因排查与解决EXPLAIN显示全表扫描索引缺失或索引失效检查WHERE和JOIN列的索引看是否触发失效场景Extra里出现Using filesort排序没走索引让ORDER BY字段和索引顺序一致Extra里出现Using temporary分组/去重使用了临时表优化GROUP BY和DISTINCT尽量走索引扫描行数特别多但返回少过滤条件没下推检查索引列顺序增加过滤条件UPDATE/DELETE执行时间突增大事务持有锁分批执行缩短单事务影响行数连接池报获取不到连接连接数达到上限查看Threads_connected调整连接池和max_connectionsCPU高但SQL看起来不慢短频SQL太多用慢日志和性能分析统计执行频率Top N这张表不能覆盖全部问题但能把最常见的80%场景框住。排查过程记得先收集证据再动手不要把“加索引”当万能药。5.3 一个小技巧开启性能分析看单条SQL耗时分布最后分享一个我很常用的排查技巧。MySQL支持在会话级别打开性能分析可以精确看到一条SQL每个阶段的耗时SET profiling 1; -- 执行要分析的SQL SELECT * FROM orders WHERE user_id 123; -- 查看耗时分布 SHOW PROFILES; SHOW PROFILE CPU, BLOCK IO FOR QUERY 1;输出会列出Sending data、Sorting result、Creating sort index等阶段每个阶段耗时多少一目了然。这个方法在定位“SQL到底慢在查询还是慢在排序”的时候特别有用比闷头猜高效得多。说实话MySQL优化这个方向入门容易深入难。如果这一篇能让你养成“写SQL先看执行计划、慢查询先看日志”的习惯我觉得比记住任何一条配置参数都更有价值。这个系列后续我还会继续写执行计划深挖、更复杂的索引设计场景、锁与事务调优、高并发读写的读写分离和分库分表思路有兴趣的读者可以持续关注。
