很多做后端开发的朋友一到线上接口变慢第一反应就是DBA 在哪帮我看下数据库但拿到命令权限后对着show processlist;的结果又不知道先看哪个字段。MySQL 优化这件事说白了就是先找到瓶颈再对症下药。大部分情况下问题根本不是机器配置不够而是 SQL 写得太烂、索引建得不对、连接池配置不合理。这周抽空把 MySQL 优化这块的实战经验整理成系列博文第一篇先聊聊定位慢 SQL 的完整路径和索引优化的几个核心原则内容适配 MySQL 5.7 和 8.0别到时候你看别人的优化方案一脸懵先把你手头数据库的版本搞清楚再动手。这个系列的读者我默认你已经在生产环境碰过 MySQL至少会SELECT和JOIN但不一定系统梳理过优化方法论。如果你是刚入职的初中级开发、自己搭项目要搞性能调优的独立开发者或者准备 MySQL 面试题但总感觉答不到点子上的同学这篇值得认真看一遍。我不会堆砌概念全程拿实际案例讲把优化思路拆开揉碎给你看。1. 先定位你的 MySQL 到底慢在哪一步很多人的优化方式就是看哪个 SQL 慢就 EXPLAIN 一下这种做法太靠感觉。正确路子是先建立一套可量化的观测体系搞明白慢是慢在 SQL 本身、锁等待、还是数据库服务器的整体资源出了问题。下面这套流程是我每次排查问题的基础操作建议直接照抄。1.1 开启慢查询日志让数据库自己举报凶手MySQL 默认是不开慢查询日志的因为记录日志本身有性能开销。但你在排查阶段可以临时打开定位到问题 SQL 之后再关掉这个度要把握好。-- 查看当前慢查询日志状态 SHOW VARIABLES LIKE slow_query_log%; SHOW VARIABLES LIKE long_query_time; -- 临时开启重启后失效先用这个别一上来就改 my.cnf SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; -- 执行时间超过1秒的SQL会被记录 SET GLOBAL log_queries_not_using_indexes ON; -- 没用索引的SQL也记录下来long_query_time这个阈值我建议线上从 1 秒开始跑个一两天看看日志再逐步调整。我见过新手直接把阈值设 0.1 秒结果慢查询日志文件一小时涨几个 G把自己服务器磁盘塞满了属于典型的没想清楚就动手。日志文件路径用SHOW VARIABLES LIKE slow_query_log_file;查看拿到文件后用mysqldumpslow工具分析这个工具 MySQL 自带会把相似的 SQL 自动聚合# 按执行次数排序取前10条最频繁的慢SQL mysqldumpslow -s c -t 10 /var/lib/mysql/your-slow.log # 按总执行时间排序 mysqldumpslow -s t -t 10 /var/lib/mysql/your-slow.log-s c是按 count 排序-s t是按 time 排序-t 10是只显示 top 10。我一般习惯两个都跑一遍执行次数多的往往是要优化的重点因为单次可能没到阈值但频繁执行累积的消耗非常大。1.2 使用 EXPLAIN 解读执行计划看查询是否走了索引拿到了慢 SQL 之后下一步就是用EXPLAIN看看这条 SQL 是怎么执行的。我见过很多人看 EXPLAIN 只盯着type是不是ALL其实信息量远不止这些。EXPLAIN SELECT o.order_id, u.user_name, o.amount FROM orders o LEFT JOIN users u ON o.user_id u.id WHERE o.status 1 AND o.created_at 2024-01-01 ORDER BY o.pay_time DESC LIMIT 20;输出结果里几个关键字段按优先级看type从好到坏依次是systemconsteq_refrefrangeindexALL。看到ALL表示全表扫描基本是优化重点index虽然也走索引了但扫描的是全索引比全表强点有限。key实际使用的索引名。如果为 NULL 说明没走索引。rowsMySQL 估算需要扫描的行数。这个数字和实际返回行数差距越大说明优化空间越大。Extra这块经常藏着问题。看到Using filesort说明排序没用上索引看到Using temporary说明用了临时表看到Using where说明索引条件下推没生效。注意EXPLAIN只是估算不是真实执行。想拿真实执行数据用EXPLAIN ANALYZEMySQL 8.0.18 才支持它会真跑这条 SQL 并给出实际耗时和扫描行数。1.3 用 SHOW PROFILE 定位单条 SQL 内部的耗时环节有的 SQL EXPLAIN 看起来一切正常索引也走了但就是慢。这时候要用SHOW PROFILE看这条 SQL 内部到底卡在哪个阶段。-- 先开启 profiling SET profiling 1; -- 执行你要分析的SQL SELECT * FROM orders WHERE order_no 202401010001; -- 查看所有 SQL 的执行档案 SHOW PROFILES; -- 查看指定 Query_ID 的详细耗时 SHOW PROFILE FOR QUERY 1;输出结果里重点看Sending data这个阶段它包含实际的磁盘 I/O、网络传输和 WHERE 过滤耗时占比高是正常的。如果看到Creating sort index占比异常说明排序性能有问题Waiting for table metadata lock则代表有别的会话占着表锁SQL 在排队等锁。这一套组合拳打下来慢 SQL 的图谱基本就出来了是没走索引还是索引走了但排序慢或者是锁等待。定位不准就开始优化很容易做无用功这点一定要记住。2. 索引优化从原理到实战的关键五问索引优化是 MySQL 优化里性价比最高的部分搞懂几个核心问题你写的 SQL 质量会提升一大截。慢 SQL 九成以上都能通过加索引或者改 SQL 让索引生效来解决所以这部分值得多花点篇幅。2.1 索引为什么能让查询快那么多很多人背过索引是 B 树但对为什么 B 树就快没概念。这里类比一下没有索引的表找一条记录就像在一个 500 页的书里从第一页翻到最后一页找某个字这就是全表扫描。有了索引相当于书后面多了一个按拼音排序的检字表你直接翻到对应拼音所在页码就找到了。B 树的精髓在于矮胖InnoDB 默认页大小 16KB一个非叶子节点大概能存 1000 个键值三层 B 树就能支撑千万级数据量的查找。三层意味着最多做 3 次磁盘 I/O 就能定位到数据这对机械硬盘和 SSD 都很友好。这也是为什么 MySQL 的索引结构选了 B 树而不是二叉树——二叉树高度太高I/O 次数太多。2.2 联合索引的最左前缀原则10个索引不如3个联合索引新手最容易犯的错是每个查询的 WHERE 字段各建一个单列索引结果索引建了一堆查询还是慢。正解是建联合索引。假设你的表长这样CREATE TABLE orders ( id INT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, status TINYINT NOT NULL DEFAULT 0, order_no VARCHAR(32) NOT NULL, created_at DATETIME NOT NULL, INDEX idx_user_status_time (user_id, status, created_at) ) ENGINEInnoDB;这个联合索引(user_id, status, created_at)能命中哪些查询-- 能命中从最左边开始连续使用 WHERE user_id 1 WHERE user_id 1 AND status 0 WHERE user_id 1 AND status 0 AND created_at 2024-01-01 -- 不能命中/部分命中 WHERE status 0 AND created_at 2024-01-01 -- 没有 user_id完全失效 WHERE user_id 1 AND created_at 2024-01-01 -- 跳过了 status只用到 user_id 部分最左前缀原则的本质是联合索引像一个多级目录先按第一列排序再按第二列排序再按第三列。你跳过了某一级后面就没法用了。实战里的建索引经验把区分度高的字段放前面等值查询的字段放前面范围查询的字段放最后。比如user_id明显比status区分度高所以放第一位。created_at是范围查询放最后面这样user_id 1 AND created_at ...还能用前面两列如果created_at放中间status放最后那这段范围查询直接废掉后面所有列。2.3 回表与覆盖索引理解 Extra 里的 Using indexInnoDB 的主键索引聚簇索引直接存的是整行数据而普通索引二级索引只存索引列 主键值。你用普通索引查数据流程是先在索引 B 树找到主键值再拿主键回到聚簇索引里查整行这一步叫回表。减少回表次数的一个有效手段是覆盖索引——让查询的列都在同一个索引里查询引擎就能直接从索引拿数据不需要回表。这也是为什么SELECT *经常是性能杀手因为*基本不可能被索引覆盖。举个例子-- 这条 SQL 需要回表 SELECT order_no, amount FROM orders WHERE user_id 1; -- 建这个覆盖索引后不需要回表 ALTER TABLE orders ADD INDEX idx_user_order_amount (user_id, order_no, amount);EXPLAIN的Extra字段变成Using index就说明走了覆盖索引。如果你的查询频繁用到某些列可以考虑把这几列组合成联合索引注意控制索引列数量不是越多越好。2.4 索引失效的6个典型场景这块是 MySQL 面试题高频考点也是生产环境里踩坑重灾区。我把最常见的索引失效场景整理成表场景错误示范正确示范对索引列使用函数WHERE DATE(created_at) 2024-01-01WHERE created_at 2024-01-01 AND created_at 2024-01-02隐式类型转换WHERE user_id 123user_id 是 INTWHERE user_id 123模糊查询前置通配符WHERE user_name LIKE %张%WHERE user_name LIKE 张%联合索引未遵循最左前缀见 2.2 节见 2.2 节OR 连接非索引列WHERE user_id 1 OR user_name 张三拆成两条 UNION ALL或建好双索引NOT IN/WHERE status NOT IN (0, 1)能改为IN尽量改或者根据业务重新设计这里第 5 条OR的问题比较隐蔽。MySQL 8.0 的部分场景下 OR 能用索引合并Index Merge但稳定性不可靠很多时候优化器会直接选全表扫描还不告诉你原因。我在生产环境里把大量 OR 改成了 UNION ALL 之后查询耗时从 600ms 降到了 80ms效果非常明显。2.5 不要把索引建得太多维护成本也是成本新手容易走向另一个极端把所有列都建上索引。索引能加速查询不假但每次INSERT、UPDATE、DELETE都要同步维护索引 B 树索引越多写入越慢。我见过一张 10 个字段的表建了 12 个索引结果写入性能掉了 40%查询也没快多少。索引的合理数量我是这样把握的单表索引一般不超过 5~6 个联合索引尽量覆盖多个高频查询场景重复的索引比如只有一个列重合的联合索引和单列索引能合并就合并。3. 实战优化慢 SQL排序、JOIN、分页三大坑定位到具体 SQL 之后最常遇到的就是排序慢、多表 JOIN 慢、深分页慢这三个问题。逐个说清楚原理和解决方案这些都是可以直接抄的作业。3.1 排序优化让 filesort 消失看执行计划的时候Extra里出现Using filesort就说明排序操作没有走索引。filesort 不是说你用了文件但确实是把数据查出来之后在内存或磁盘上单独排了一遍这个开销在数据量大时极其可观。要消除 filesort核心思路是让 ORDER BY 的字段和 WHERE 条件里的字段组成联合索引并且顺序要匹配。因为 B 树本身就是有序的查询走索引时数据天然排好序MySQL 就不需要再额外排序了。-- 这条 SQL 有 WHERE user_id 等值过滤 ORDER BY created_at SELECT * FROM orders WHERE user_id 1 ORDER BY created_at DESC LIMIT 20; -- 联合索引 (user_id, created_at) 能让排序直接用索引 ALTER TABLE orders ADD INDEX idx_user_created (user_id, created_at);注意事项ORDER BY涉及多个字段时排序方向要一致。索引是(a ASC, b ASC)的话ORDER BY a DESC, b DESC理论上能走索引反向扫描但ORDER BY a ASC, b DESC这种混合方向在旧版本里大概率还是要 filesort。另外一个常见场景是排序字段上有函数操作比如ORDER BY DATE(created_at) DESC这种属于对索引列使用了函数索引必然失效建议在 SQL 层面改写或者另建冗余字段存日期值。3.2 JOIN 优化驱动表与被驱动表的正确姿势先纠正一个常见误解JOIN 性能差不是 JOIN 本身慢而是没搞清楚哪张表是驱动表。MySQL 执行 JOIN 的机制是嵌套循环拿驱动表的每一行去被驱动表里匹配。所以基本法则是——小表驱动大表驱动表的行数越少循环次数越少整体越快。-- 假设 users 是小表1000行orders 是大表100万行 -- 这条 JOIN 会用 users 驱动 orders SELECT u.user_name, o.amount FROM users u LEFT JOIN orders o ON u.id o.user_id WHERE u.role vip;LEFT JOIN中左表是驱动表这里是 users但实际优化器可能会根据统计信息调整。你要做的是检查被驱动表的 JOIN 字段有没有索引。上例中orders.user_id如果是 100 万行数据却没有索引等于每循环一次 users 的 1000 行都要全表扫一遍 orders那是灾难。JOIN 优化第一原则永远先确认被驱动表的 ON 字段建了索引。InnoDB 的 JOIN 还有个细节如果被驱动表走的是主键type会显示eq_ref效率极高走的普通索引type是ref可能回表。所以要尽量让 JOIN 的 ON 字段是被驱动表的主键或者覆盖索引能覆盖的列。3.3 深分页慢的解法延迟关联LIMIT 100000, 20这种写法在数据量大时非常慢。原因是 MySQL 先查出前 100020 行然后丢掉前 100000 行只返回最后的 20 行——前面的 100000 行扫描和回表全是白干的。经典的优化方案叫延迟关联deferred join核心是先用覆盖索引定位到主键 ID再回表拿完整数据-- 原始写法慢 SELECT * FROM orders ORDER BY created_at DESC LIMIT 100000, 20; -- 优化后快很多 SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders ORDER BY created_at DESC LIMIT 100000, 20 ) tmp ON o.id tmp.id;子查询里只查id和排序字段走了覆盖索引不回表扫描的成本大幅降低外面再根据 20 个主键 ID 回表取整行数据总共只回表 20 次性能能提升一个数量级。数据量到千万级以上时延迟关联也会慢慢吃力这时候就得换思路记录上次查询的最大created_at或最大id做基于游标的分页keyset paginationSELECT * FROM orders WHERE created_at 2024-01-15 00:00:00 ORDER BY created_at DESC LIMIT 20;后端把上一次返回的最后一条记录的created_at传下来下一页就用它做条件。这种方案变相把翻页变成了过滤不管翻到第几页都只查 20 条性能恒定。缺点是用户没法直接跳转到任意页只能下一页但大部分业务场景完全够用。如果产品经理死活要你实现跳页功能可以考虑用 Redis 缓存每页的起始 ID 或者用搜索引擎兜底。4. 架构层面的连接与参数优化排除掉 SQL 本身的问题之后另一个影响 MySQL 性能的重要因素是服务端配置和连接管理。很多团队数据库服务器 CPU 不高、IO 不忙但接口就是慢这种时候大概率是连接数被打满了。4.1 连接池为什么不能每次请求都新建数据库连接MySQL 建立连接的过程包含 TCP 三次握手、认证、权限校验一次下来要几十毫秒而一条简单查询可能也就 1 毫秒。每次请求都新建连接等于把大头开销花在了打招呼上业务本身反而没干多少活。所以生产环境必须用连接池复用连接。Java 生态用 HikariCPSpring Boot 2.x 默认、DruidPython 用 SQLAlchemy 的连接池Go 用 database/sql 自带连接池。连接池的核心参数就三个最大连接数maximum-pool-size压测得出一般数据库连接 50~100 就够用了别贪多。连接数太多MySQL 线程调度开销大反而变慢。最小空闲连接数minimum-idle保持几个空闲连接防止冷启动建议和最大连接数一致或者至少 10 以上。连接超时时间connection-timeout等待连接的最大毫秒数超过这个时间直接报错别让请求无限等下去。连接池的最大连接数还要考虑数据库自身的max_connections限制SHOW VARIABLES LIKE max_connections;如果连接池最大连接数是 100但多台应用服务器加起来有 10 个实例那数据库要承受 1000 个连接很可能超出 MySQL 默认限制导致报错。分布式部署时连接池参数的规划要按全局来算不能只看单机。4.2 MySQL 核心参数调优别上来就改 8G网上 MySQL 优化教程动辄让你把innodb_buffer_pool_size改成 8G你复制过去重启服务器 OOM 了才知道问题。参数调优前先搞清楚你的数据量和硬件资源。最重要的一个参数innodb_buffer_pool_size这是 InnoDB 缓存表和索引数据的内存区域。经验值是物理内存的 50%~70%但前提是这台机器只跑 MySQL。如果你的服务器还要跑应用和 Redis顶多给 30% 就差不多了。[mysqld] # 假设物理内存16GMySQL独占设置10G innodb_buffer_pool_size 10G # 开启 buffer pool 多实例避免并发争抢内存大于1G时建议开启 innodb_buffer_pool_instances 8另一个常被忽视的参数是innodb_flush_log_at_trx_commit。默认值 1 表示每次事务提交都要刷盘最安全但速度最慢设为 2 表示每秒刷盘一次性能提升明显但服务器宕机会丢 1~2 秒的事务日志设为 0 表示交给系统决定性能最高但宕机丢数据的风险更大。线上业务对数据一致性要求高就保持 1日志类、流水类的非核心库可以调成 2。还有max_connections和wait_timeout。wait_timeout默认 8 小时连接池里的空闲连接可能被数据库断开应用拿到失效连接就报错。这个值可以调小到 60~300 秒配合连接池的validationQuery或心跳检测来保活。排障时出现MySQL server has gone away类型错误先查这个参数。4.3 千万级表的常规操作归档、分区、分库分表的取舍表数据量增长到一定规模后即使索引建得没问题写入和查询都会变慢。这时候要把优化眼光从 SQL 层面上升到表结构层面。第一步先做数据归档把历史数据搬到归档表或冷存储热表只保留最近几个月的数据。我见过 2000 万行的日志表归档后只剩 200 万行查询速度直接从 2 秒降到 50 毫秒。这往往是最便宜、见效最快的方案。第二步是表分区PARTITION BY RANGE...。分区不是万能的别为了分区而分区。如果业务查询经常带时间范围按时间做 RANGE 分区能让 MySQL 自动跳过不相关的分区文件减少扫描量。但分区表在后续维护加索引、DDL上有不少坑MySQL 8.0 之前分区表上的查询性能反而不确定。我的建议是数据量 5000 万以下优先用归档解决分区当作备用手段。第三步才轮到分库分表。这是个大工程涉及分布式事务、全局主键、跨库 JOIN 等问题线上改造风险极高没有专业 DBA 团队的团队别轻易上。把前三步做好大部分业务撑到大几十亿数据量没太大问题真到了必须分库分表那一步优先考虑引入成熟的中间件方案而不是自研。5. MySQL 连接故障排查与常见面试题串讲优化做完了最终还是要落在能不能连上、能不能不出错这个底线问题上。这个部分把高频的连接故障案例和 MySQL 面试题一起串讲一遍面试和工作都能用上。5.1 ERROR 2002 (HY000)socket 连接失败的排查思路很多人本地装完 MySQL 用命令行登录结果报这样一条错ERROR 2002 (HY000): Cant connect to local MySQL server through socket /tmp/mysql.sock (2)常见原因和排查顺序如下MySQL 服务没启动。这是最高频的原因先看进程和端口ps -ef | grep mysqld netstat -tlnp | grep 3306 # 如果没启动 systemctl start mysqldsocket 文件路径对不上。客户端默认会去/tmp/mysql.sock找 socket但 MySQL 配置的 socket 路径可能不同。用 mysql_config 查一下实际 socket 路径然后在连接时指定mysql -u root -p -S /var/run/mysqld/mysqld.sock权限问题。socket 文件权限不对或者 MySQL 用户没法写/tmp/目录。检查报错信息里(2)这个 errno2表示文件不存在13表示权限不够逐个对症。TCP 连接不通。如果你指定了127.0.0.1却连不上检查是否只绑定了本地回环地址或者防火墙拦截-- 看 MySQL 是否绑定了所有网卡 SELECT bind_address;生产环境 MySQL 一般只允许内网访问bind-address设置成内网 IP 是正常的如果从外部访问不了优先查安全组和防火墙而不是 MySQL 配置。5.2 MySQL update 语法与事务安全的几个坑工作中永远绕不开UPDATE说两个比较有代表性的坑坑一忘记加 WHERE 条件直接全表更新。新手手滑执行UPDATE users SET status 1;整张表状态全改追悔莫及。这是 SQL 审核要重点卡的场景最好在 GUI 客户端里打开安全模式禁止不带 WHERE 条件的 UPDATE/DELETE。线上环境建议所有 UPDATE 先SELECT COUNT(*) WHERE 同条件确认行数再执行更新。坑二UPDATE 和 SELECT 的并发不一致。两个连接同时更新同一行后提交的覆盖先提交的这叫丢失更新。解决方式是用版本号或时间戳做乐观锁注意 UPDATE 一条满足条件的行如果影响行数是 0说明版本号变了让业务重试UPDATE orders SET amount 100, version version 1 WHERE id 123 AND version 5;5.3 存储过程在业务系统里到底建不建搜索引擎里mysql 存储过程的搜索量不低但我的态度很明确业务系统里尽量别用存储过程。原因不是 MySQL 不支持或性能差而是这东西把业务逻辑和数据库耦合死了——代码里调一个CALL sp_do_something()后期想扩展逻辑得改数据库里的过程版本管理、测试、灰度发布都没有好工具支持团队协作开发非常痛苦。不过如果你是做数据分析、批量跑数、ETL 数据清洗这类内部任务存储过程反而很有用MySQL 8.0 对存储过程的性能也有了明显提升。怎么声明也顺便记一下面试偶尔会问DELIMITER $$ CREATE PROCEDURE get_order_stats(IN uid INT, OUT total DECIMAL(10,2)) BEGIN SELECT SUM(amount) INTO total FROM orders WHERE user_id uid; END$$ DELIMITER ; -- 调用 CALL get_order_stats(1, total); SELECT total;DELIMITER是告诉 MySQL 客户端不要遇到分号就当成语句结束这样过程体里的分号才能被正确解析。写完用SHOW PROCEDURE STATUS查看是否创建成功。5.4 MySQL 面试题高频追问一条 SQL 的执行流程面试时经常被问一条 SQL 从输入到返回结果经历了什么很多人只会背连接器、分析器、优化器、执行器这四层面试官一追问细节就答不上来。作为优化内容这里串讲一下更完整的链路连接器验证账号密码获取权限建立连接。这就是连接池复用的原因所在连接建立的开销大头在这里。分析器词法分析和语法分析检查 SQL 语法是否正确。遇到报错You have an error in your SQL syntax就是这段没通过。优化器决定用哪个索引、采用哪种 JOIN 顺序。表结构和索引设计是否合理直接决定优化器能不能选出好方案——索引建得乱七八糟优化器再智能也无力回天。执行器真正调用存储引擎接口逐条读取数据并做条件判断最后返回结果。没有索引的表在这里就是痛苦的逐行扫描。存储引擎InnoDB 通过缓冲池Buffer Pool读取数据页判断是否命中内存。innodb_buffer_pool_size设置不合理会导致频繁磁盘 I/O这是很多慢查询的底层原因。这整条链路和前面讲的慢查询定位、索引优化、连接池调优是严格对应的——哪一段出问题就从哪一段入手解决。我个人的体会是MySQL 优化没有银弹先掌握定位手段再理解背后原理最后动手时才不会像无头苍蝇一样乱撞。整个系列的第一篇先写到这下一篇准备聊聊索引的更深入场景和更多实战案例到时再展开。
