这次我们不看花架子直接完整梳理一遍 MySQL 从索引原理、B 树、联合索引、SQL 优化到 Mysql 调优实战的完整链路。这条链路也是面试最高频、线上问题最集中的一段弄清楚它日常开发里的慢 SQL、接口超时、索引失效问题基本都能自己排查。文章会先讲 B 树为什么是 InnoDB 的默认选择然后给出联合索引、索引下推、覆盖索引这些核心概念的可执行判断方法接着用 EXPLAIN 和慢查询日志走一遍 SQL 优化实战最后补一组高频 MySQL 面试题和调优参数。内容密度会比较高建议先收藏再慢慢对照自己库里的慢 SQL 验证。文章里所有命令和 SQL 都以 MySQL 8.x 为主兼容 5.7涉及生产环境的操作会单独标注注意事项。现在直接进入正题。1. 核心能力速览这是一篇 MySQL 数据库性能优化与面试突击的完整实战教程不是某个工具的安装评测而是把索引、B 树、SQL 优化、Mysql 调优串成一条可落地的知识链路。能力项说明适用数据库MySQL 5.7 / 8.xInnoDB 存储引擎核心内容B 树索引原理、聚簇索引与二级索引、联合索引、索引下推、SQL 优化、慢查询排查、Mysql 调优参数验证方式通过 EXPLAIN 分析执行计划通过慢查询日志定位问题 SQL技能要求需要掌握基础 SQL 语法了解数据库表结构设计适合场景后端开发、DBA、面试突击、线上 SQL 性能排查涉及面试题为什么用 B 树、最左前缀原则、索引失效场景、覆盖索引、索引下推等这里先给结论直接关系到线上性能的常见问题百分之八十都能归到“索引没设计好”或“SQL 写法导致索引失效”两类。把这两类问题解决掉数据库压力会明显下降。2. 索引基础与 B 树原理详解2.1 为什么 InnoDB 选择 B 树一张表的数据量过百万以后全表扫描的代价会非常高。InnoDB 使用 B 树作为索引结构核心原因有四点。第一B 树非叶子节点不存数据只存索引键和指针所以每个节点能容纳更多键值树的高度更低。一般三到四层就能支撑千万级数据磁盘 IO 次数被压到最低。第二B 树的叶子节点按顺序排列并且通过双向链表连接非常适合范围查询和排序。比如WHERE id 100 AND id 500这种条件找到 100 之后就可以沿链表顺序扫描不需要反复回溯。第三叶子节点存的是完整数据或主键值查询路径稳定无论查哪一行IO 次数都差不多不会出现某些行访问特别慢的情况。第四数据在叶子节点按顺序排列插入和删除相对可控。虽然随机插入可能导致页分裂但整体维护成本低于哈希索引和普通 B 树。2.2 聚簇索引与二级索引InnoDB 中索引可以分为两种。聚簇索引就是我们常说的主键索引。表数据本身就是按照主键构建的 B 树叶子节点直接存储整行数据。这就是为什么 InnoDB 表必须要有主键如果没有显式主键InnoDB 会选择一个非空唯一索引再不行就生成隐藏主键。二级索引也叫非聚簇索引叶子节点存储的是索引列的值加主键值。也就是说通过二级索引查数据时先用索引找到主键再回到聚簇索引里查完整行数据这个过程叫回表。-- 创建二级索引示例 CREATE INDEX idx_user_name ON t_user (name);如果查询需要的数据在二级索引里都能拿到比如只查name和主键id那么就不用回表这种场景叫覆盖索引。2.3 为什么不用红黑树、哈希索引和普通 B 树红黑树在内存里效率很高但数据量一大树高度会明显增加。MySQL 数据最终落在磁盘树每高一层就多一次磁盘 IO红黑树高度不可控不适合磁盘存储。哈希索引单点等值查询非常快但不支持范围查询和排序。WHERE age 18这种条件是哈希索引无法优化的所以哈希索引只能作为 InnoDB 的辅助结构存在比如自适应哈希索引。普通 B 树非叶子节点也会存数据导致每个节点能存储的键值数量变少树的高度会比 B 树高磁盘 IO 次数增加。B 树把数据全部集中在叶子节点非叶子节点只做导航本质是拿空间换树高更适合磁盘密集场景。3. 联合索引与最左前缀原则3.1 联合索引的底层结构联合索引是多个列组成的索引比如(a, b, c)。注意联合索引不是单独为每个列建索引而是按照从左到右的顺序整体构建一棵 B 树。先按a排序a相同再按b排序b相同再按c排序。所以查询条件里没有a只有b和c时索引就无法发挥作用。-- 联合索引 ALTER TABLE t_order ADD INDEX idx_user_status (user_id, status, create_time);3.2 最左前缀原则判断方法判断联合索引能否命中不要死记硬背直接看查询条件里是否包含联合索引的最左列。假设索引是(a, b, c)查询条件是否走索引说明WHERE a 1走使用 a 列WHERE a 1 AND b 2走使用 a、b 列WHERE a 1 AND b 2 AND c 3走使用 a、b、c 列WHERE b 2不走缺少最左列 aWHERE c 3不走缺少最左列 aWHERE a 1 AND c 3部分走使用 a 列c 列无法用索引过滤第四种情况值得展开说明。WHERE a 1 AND c 3时索引只能用到a这一列c无法直接利用索引进行过滤。MySQL 会在使用索引定位到a 1的记录后再对结果逐行判断c 3。可以用字段b作为中间列的IN条件来优化让c也能用上索引。3.3 联合索引设计原则联合索引列顺序非常重要基本原则是区分度高的列放前面经常用于等值查询的列放前面范围查询的列放最后。区分度高的列放前面可以更快地缩小查询范围。比如性别列区分度很低只有男和女两种不适合放联合索引最前面。而手机号这类区分度很高的列放前面效果很好。范围查询的列放最后因为范围条件后面的列无法继续使用索引例如WHERE a 1 AND b 10 AND c 3索引最多用到bc就没法参与了。4. 索引优化实战索引失效场景与覆盖索引4.1 常见索引失效场景排查线上慢 SQL 时首先要检查的就是索引是否失效。以下七种情况需要重点检查。情况一LIKE 以通配符开头-- 索引失效a% 才能走索引 SELECT * FROM t_user WHERE name LIKE %张;LIKE %张无法利用 B 树叶子节点的有序性只能全表扫描或扫全索引。如果业务上确实需要后缀匹配建议使用全文索引或搜索引擎。情况二对索引列使用函数或计算-- 索引失效 SELECT * FROM t_user WHERE YEAR(create_time) 2025; -- 正确写法等值范围查询可走索引 SELECT * FROM t_user WHERE create_time 2025-01-01 AND create_time 2026-01-01;只要索引列参与了函数运算优化器就无法使用索引因为索引键值已经被函数改变了。情况三隐式类型转换-- 假设 phone 是 varchar 类型 -- 索引失效因为 12345678901 会被转换为字符串后再比较 SELECT * FROM t_user WHERE phone 12345678901; -- 正确写法 SELECT * FROM t_user WHERE phone 12345678901;字符串列与数字比较时MySQL 会把字符串转换为数字导致索引列上发生了隐式转换。情况四条件中使用 OROR只要有一侧不是索引列整个查询就可能退化为全表扫描。如果name有索引而age没有索引WHERE name 张三 OR age 18会全表扫描。解决办法是把OR改成UNION ALL或者给两侧列都加上索引。情况五使用不等于WHERE status ! 1或WHERE status 1对于索引列来说需要扫描的值过于分散优化器大概率放弃索引。实际生产环境中可以用IN替代不等于来明确指定范围。情况六IS NULL 与 IS NOT NULLMySQL 8 对IS NULL的索引支持已经优化但IS NOT NULL在数据分布不均时依然可能不走索引。判断方式还是看执行计划。情况七联合索引不满足最左前缀字段在前面的查询条件中完全未出现索引直接失效前面已经分析过。4.2 覆盖索引优化覆盖索引是减少回表的重要优化手段。当查询需要的字段全部包含在索引中时InnoDB 可以直接使用索引返回结果不需要再回表读聚簇索引。-- 慢需要回表 SELECT * FROM t_user WHERE name 张三; -- 快覆盖索引直接返回 SELECT id, name FROM t_user WHERE name 张三;如果表上有索引idx_name(name)第二条 SQL 从索引本身就能拿到name和主键id无需回表。这就是为什么部分 SELECT 不建议带头SELECT *的原因。4.3 索引下推索引下推是 MySQL 5.6 引入的优化。在没有索引下推时联合索引(name, age)遇到WHERE name LIKE 张% AND age 18MySQL 先根据name的范围条件从索引中筛出符合的记录然后回表逐行判断age 18。启用索引下推后MySQL 会把age 18的判断下放到存储引擎层在读取索引的时候就过滤掉不符合age条件的记录减少回表次数。可以在执行计划里看到Using index condition关键字这就是索引下推生效的标志。EXPLAIN SELECT * FROM t_user WHERE name LIKE 张% AND age 18;结果中 Extra 列出现Using index condition说明索引下推生效。如果想要关闭下推可以执行SET optimizer_switch index_condition_pushdownoff;但一般不建议关闭。5. SQL 优化实战基于 EXPLAIN 分析执行计划5.1 EXPLAIN 核心字段EXPLAIN 是分析 SQL 性能的第一工具最需要关注的是这几个字段。字段含义type访问类型从好到坏依次是 system const eq_ref ref range index ALLkey实际使用的索引rows预计扫描的行数越小越好Extra额外信息重点关注 Using filesort、Using temporary、Using index conditiontype达到ref或range就已经是不错的状态。如果出现ALL说明是全表扫描要检查为什么没走索引。EXPLAIN SELECT id, order_no, user_id FROM t_order WHERE user_id 10086;执行后重点看type和key如果type ref且key指向idx_user_status说明联合索引生效。5.2 深分页优化LIMIT 100000, 20这种深分页MySQL 会扫描前 100020 行再丢弃前 100000 行越往后翻越慢。-- 慢深分页 SELECT * FROM t_order ORDER BY id LIMIT 100000, 20; -- 优化方案延迟关联先取主键再回表 SELECT t.* FROM t_order t INNER JOIN (SELECT id FROM t_order ORDER BY id LIMIT 100000, 20) tmp ON t.id tmp.id;子查询先在二级索引或主键索引上快速定位到 20 个主键再通过主键回表取完整行数据避免大范围扫描。5.3 ORDER BY 排序优化ORDER BY字段是否有索引直接影响是否出现Using filesort。文件排序在数据量大时非常慢。-- 联合索引 (a, b), WHERE 过滤 aORDER BY 使用 b可以避免 filesort SELECT * FROM t WHERE a 1 ORDER BY b; -- 如果排序字段和过滤字段不在同一个索引就会出现 filesort SELECT * FROM t WHERE a 1 ORDER BY c;优化思路是让排序字段尽量满足联合索引的顺序要求或者减少排序行数。5.4 隐式类型转换与函数计算检查-- 检查是否有隐式类型转换直接看执行计划 EXPLAIN SELECT * FROM t_user WHERE phone 12345678901;如果看到type ALL且字段本身有索引多半是隐式类型转换导致索引失效。把条件改成与字段类型一致的写法即可。5.5 避免 SELECT *这句话已经说过很多次但依然有一堆代码在裸奔。SELECT *的核心问题在于第一可能触发回表。普通索引无法覆盖全部列时每行都要回表一次。第二浪费网络 IO 和内存。不需要的大字段会占用大量资源。第三增加排序和临时表的可能性。建议只查询需要的字段必要的时候用覆盖索引。6. Mysql 调优实战案例与参数配置6.1 定位慢 SQL先开启慢查询日志。# 临时开启重启失效 SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL slow_query_log_file /var/log/mysql/mysql-slow.log;long_query_time 1表示记录执行超过 1 秒的 SQL。线上一般从 1 秒开始如果慢 SQL 太多可以调整到 2 秒或 3 秒先处理最严重的。# 查看慢查询日志路径 SHOW VARIABLES LIKE slow_query_log_file;6.2 Buffer Pool 调优InnoDB Buffer Pool 是缓存表和索引数据的内存区域大小直接决定磁盘 IO 频率。# my.cnf 示例 [mysqld] innodb_buffer_pool_size 4G innodb_buffer_pool_instances 4innodb_buffer_pool_size在纯数据库服务器上通常设置为物理内存的 50% 到 70%但不能超过物理内存。建议使用官方计算公式innodb_buffer_pool_size应大于数据库热数据总量。检查 Buffer Pool 命中率SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_read_requests; SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_reads;命中率 read_requests / (read_requests reads)长期低于 95% 说明 Buffer Pool 偏小。6.3 redo log 刷盘策略innodb_flush_log_at_trx_commit控制 redo log 的刷盘方式。参数值行为安全性性能1每次事务提交都刷盘最高每次提交落盘最慢0每秒刷盘崩溃时可能丢 1 秒数据最快2每次提交写入 OS 缓存每秒刷盘操作系统崩溃时可能丢 1 秒数据较快默认值 1 保证持久性。如果业务允许秒级数据丢失可以改成 2 提升性能。这里要注意这个判断必须结合实际场景金融类业务不建议改。6.4 排序和临时表参数sort_buffer_size 4M join_buffer_size 4M tmp_table_size 64M max_heap_table_size 64M这些参数不是越大越好。每次会话连接都会分配相应的 buffer过大会导致内存浪费。有大量排序和 join 的场景可以适当增大但要以实际监控为准。6.5 数据库开启审计引起索引争用的处理热搜词里提到“数据库开启审计引起索引争用”这是真实生产环境遇到的问题。开启数据库审计后每条操作都会被记录不仅带来大量写入还可能导致共享资源竞争加剧表现为锁等待、索引页争用、TPS 下降。排查思路是第一确认审计日志是否落盘到业务表所在的磁盘如果同一块磁盘IO 竞争会非常严重。第二检查审计策略是否过宽是否存在全量记录SELECT的情况。第三观察SHOW ENGINE INNODB STATUS中的锁等待信息看是否有明显的 latch 争用。审计本身不是问题问题是无差别记录和资源隔离不到位。建议按最小化原则配置审计策略只记录必要的高危操作日志输出到独立磁盘降低对业务索引访问的影响。6.6 一个完整调优案例假设场景订单表t_order有 500 万数据接口按user_id和create_time查订单列表接口经常超时。第一步查看慢查询日志定位到这条 SQLSELECT * FROM t_order WHERE user_id 10086 ORDER BY create_time DESC LIMIT 20;第二步执行 EXPLAIN发现type ALL全表扫描。第三步检查表索引发现只有主键索引没有user_id的索引。第四步添加联合索引ALTER TABLE t_order ADD INDEX idx_user_time (user_id, create_time);第五步再次 EXPLAIN发现type refkey是idx_user_timeExtra不再是Using filesort。接口耗时从原来的 2 秒下降到 30 毫秒以内。这个案例非常典型属于索引缺失加排序字段未纳入联合索引的常见组合。7. MySQL 高频面试题整理这里整理一组高频题每道题附带核心回答思路。7.1 InnoDB 为什么用 B 树而不是 B 树一句话版本B 树非叶子节点只存索引键树更矮磁盘 IO 更少叶子节点有序链表支持高效范围查询查询路径稳定性能可控。7.2 聚簇索引和二级索引的区别聚簇索引叶子节点存整行数据主键决定数据物理排序二级索引叶子节点存索引列加主键值查询可能回表。7.3 什么是覆盖索引需要查询的列全部包含在索引中不需要回表的索引通过 Extra 显示Using index确认。7.4 什么是索引下推存储引擎层在读取索引时先过滤部分条件减少回表次数Extra 显示Using index condition。7.5 联合索引的最左前缀原则联合索引按从左到右的顺序构建查询必须包含最左列才能命中索引。等值查询条件放前面范围查询条件放最后。7.6 索引失效的场景有哪些LIKE %xx、函数计算、隐式类型转换、OR连接非索引列、不满足最左前缀、IS NOT NULL等判断标准是看执行计划。7.7 深分页如何优化延迟关联先通过子查询定位主键再回表取完整数据避免全表扫描加丢弃。7.8find_in_set能走索引吗热搜词里出现这个问题答案是常规情况下FIND_IN_SET(col, 1,2,3)无法走索引。因为函数作用于索引列破坏了索引有序性。如果业务确实需要这种查询可以考虑业务表结构拆分或使用全文检索具体方案依赖实际业务模型。8. 性能监控与排查工具推荐8.1 系统层面top、vmstat、iostat用于观察 CPU、内存和磁盘 IO。# 查看磁盘 IO 是否繁忙 iostat -x 2 # 查看 MySQL 进程资源占用 top -p $(pgrep -x mysqld)磁盘 IO 高发时优先检查慢查询和 Buffer Pool 命中率。8.2 MySQL 层面-- 查看当前线程状态 SHOW FULL PROCESSLIST; -- 查看 InnoDB 状态重点关注锁等待和事务 SHOW ENGINE INNODB STATUS; -- 查看全局状态 SHOW GLOBAL STATUS LIKE Threads%;SHOW FULL PROCESSLIST是排查线上卡顿的第一入口。如果有大量Sending data、Waiting for table metadata lock、updating状态需要立刻定位对应 SQL。8.3 慢查询分析慢查询日志落盘后可以使用mysqldumpslow工具汇总。mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log按耗时排序取前 10 条优先优化出现频率最高、单次耗时最长的 SQL。9. 常见问题与排查方法问题现象可能原因排查方式解决方案有索引但不生效隐式类型转换、函数计算、错误查询写法EXPLAIN 查看 type 和 key重写 SQL避免索引列参与运算查询偶尔快偶尔慢Buffer Pool 命中率不稳定查看命中率观察慢查询时间段增大 buffer pool分析是否为热点数据突然增加数据库频繁 IO大量缓存未命中或结果集过大iostat、命中率监控调大 buffer_pool_size优化 SQL 减少扫描行接口偶发超时锁等待或大事务SHOW PROCESSLIST 查看阻塞源定位长事务拆分事务减少锁持有时间排序慢缺少适合排序的索引Extra 看到 Using filesort将排序字段纳入联合索引联表查询慢关联字段无索引或驱动表选错EXPLAIN 查看驱动表和 key给关联字段加索引使用小表驱动大表深分页慢扫描和丢弃大量行查看 LIMIT 位置使用延迟关联一批相同 SQL 突然变慢统计信息过期或执行计划变化ANALYZE TABLE重新分析表统计信息必要时强制指定索引10. 最佳实践与避坑建议到此为止从 B 树原理到联合索引、SQL 优化、Mysql 调优实战、面试题已经完整走了一遍。这里再给几条工程化建议也是以后优化数据库的固定套路。第一每张表的索引数量控制在 5 个以内索引不是越多越好写入和更新都要维护索引。第二所有上线 SQL 先过一遍 EXPLAIN杜绝type ALL的查询直接上线。第三慢查询日志从第一天就开启收集历史慢 SQL建立优化清单。第四索引命名规范要有例如idx_表名_字段名方便排查。第五涉及生产数据库结构变更时先在测试环境验证执行计划和耗时。文章开头说过索引失效和索引缺失是线上问题的两个大头。现在你可以打开自己项目的数据库查一下慢查询日志把执行时间最长的三条 SQL 拿出来用 EXPLAIN 分析一遍按文中第 5 节的方法调整索引和 SQL 写法大概率能解决相当一部分性能问题。如果想在这个方向继续深入下一步可以研究 InnoDB 的锁机制与隔离级别、MVCC 多版本控制、主从复制延迟以及分库分表方案。这些内容配合本文的索引和调优基础足以覆盖日常工作与大部分技术面试场景。
MySQL索引原理与SQL优化实战：从B+树到调优完整指南
这次我们不看花架子直接完整梳理一遍 MySQL 从索引原理、B 树、联合索引、SQL 优化到 Mysql 调优实战的完整链路。这条链路也是面试最高频、线上问题最集中的一段弄清楚它日常开发里的慢 SQL、接口超时、索引失效问题基本都能自己排查。文章会先讲 B 树为什么是 InnoDB 的默认选择然后给出联合索引、索引下推、覆盖索引这些核心概念的可执行判断方法接着用 EXPLAIN 和慢查询日志走一遍 SQL 优化实战最后补一组高频 MySQL 面试题和调优参数。内容密度会比较高建议先收藏再慢慢对照自己库里的慢 SQL 验证。文章里所有命令和 SQL 都以 MySQL 8.x 为主兼容 5.7涉及生产环境的操作会单独标注注意事项。现在直接进入正题。1. 核心能力速览这是一篇 MySQL 数据库性能优化与面试突击的完整实战教程不是某个工具的安装评测而是把索引、B 树、SQL 优化、Mysql 调优串成一条可落地的知识链路。能力项说明适用数据库MySQL 5.7 / 8.xInnoDB 存储引擎核心内容B 树索引原理、聚簇索引与二级索引、联合索引、索引下推、SQL 优化、慢查询排查、Mysql 调优参数验证方式通过 EXPLAIN 分析执行计划通过慢查询日志定位问题 SQL技能要求需要掌握基础 SQL 语法了解数据库表结构设计适合场景后端开发、DBA、面试突击、线上 SQL 性能排查涉及面试题为什么用 B 树、最左前缀原则、索引失效场景、覆盖索引、索引下推等这里先给结论直接关系到线上性能的常见问题百分之八十都能归到“索引没设计好”或“SQL 写法导致索引失效”两类。把这两类问题解决掉数据库压力会明显下降。2. 索引基础与 B 树原理详解2.1 为什么 InnoDB 选择 B 树一张表的数据量过百万以后全表扫描的代价会非常高。InnoDB 使用 B 树作为索引结构核心原因有四点。第一B 树非叶子节点不存数据只存索引键和指针所以每个节点能容纳更多键值树的高度更低。一般三到四层就能支撑千万级数据磁盘 IO 次数被压到最低。第二B 树的叶子节点按顺序排列并且通过双向链表连接非常适合范围查询和排序。比如WHERE id 100 AND id 500这种条件找到 100 之后就可以沿链表顺序扫描不需要反复回溯。第三叶子节点存的是完整数据或主键值查询路径稳定无论查哪一行IO 次数都差不多不会出现某些行访问特别慢的情况。第四数据在叶子节点按顺序排列插入和删除相对可控。虽然随机插入可能导致页分裂但整体维护成本低于哈希索引和普通 B 树。2.2 聚簇索引与二级索引InnoDB 中索引可以分为两种。聚簇索引就是我们常说的主键索引。表数据本身就是按照主键构建的 B 树叶子节点直接存储整行数据。这就是为什么 InnoDB 表必须要有主键如果没有显式主键InnoDB 会选择一个非空唯一索引再不行就生成隐藏主键。二级索引也叫非聚簇索引叶子节点存储的是索引列的值加主键值。也就是说通过二级索引查数据时先用索引找到主键再回到聚簇索引里查完整行数据这个过程叫回表。-- 创建二级索引示例 CREATE INDEX idx_user_name ON t_user (name);如果查询需要的数据在二级索引里都能拿到比如只查name和主键id那么就不用回表这种场景叫覆盖索引。2.3 为什么不用红黑树、哈希索引和普通 B 树红黑树在内存里效率很高但数据量一大树高度会明显增加。MySQL 数据最终落在磁盘树每高一层就多一次磁盘 IO红黑树高度不可控不适合磁盘存储。哈希索引单点等值查询非常快但不支持范围查询和排序。WHERE age 18这种条件是哈希索引无法优化的所以哈希索引只能作为 InnoDB 的辅助结构存在比如自适应哈希索引。普通 B 树非叶子节点也会存数据导致每个节点能存储的键值数量变少树的高度会比 B 树高磁盘 IO 次数增加。B 树把数据全部集中在叶子节点非叶子节点只做导航本质是拿空间换树高更适合磁盘密集场景。3. 联合索引与最左前缀原则3.1 联合索引的底层结构联合索引是多个列组成的索引比如(a, b, c)。注意联合索引不是单独为每个列建索引而是按照从左到右的顺序整体构建一棵 B 树。先按a排序a相同再按b排序b相同再按c排序。所以查询条件里没有a只有b和c时索引就无法发挥作用。-- 联合索引 ALTER TABLE t_order ADD INDEX idx_user_status (user_id, status, create_time);3.2 最左前缀原则判断方法判断联合索引能否命中不要死记硬背直接看查询条件里是否包含联合索引的最左列。假设索引是(a, b, c)查询条件是否走索引说明WHERE a 1走使用 a 列WHERE a 1 AND b 2走使用 a、b 列WHERE a 1 AND b 2 AND c 3走使用 a、b、c 列WHERE b 2不走缺少最左列 aWHERE c 3不走缺少最左列 aWHERE a 1 AND c 3部分走使用 a 列c 列无法用索引过滤第四种情况值得展开说明。WHERE a 1 AND c 3时索引只能用到a这一列c无法直接利用索引进行过滤。MySQL 会在使用索引定位到a 1的记录后再对结果逐行判断c 3。可以用字段b作为中间列的IN条件来优化让c也能用上索引。3.3 联合索引设计原则联合索引列顺序非常重要基本原则是区分度高的列放前面经常用于等值查询的列放前面范围查询的列放最后。区分度高的列放前面可以更快地缩小查询范围。比如性别列区分度很低只有男和女两种不适合放联合索引最前面。而手机号这类区分度很高的列放前面效果很好。范围查询的列放最后因为范围条件后面的列无法继续使用索引例如WHERE a 1 AND b 10 AND c 3索引最多用到bc就没法参与了。4. 索引优化实战索引失效场景与覆盖索引4.1 常见索引失效场景排查线上慢 SQL 时首先要检查的就是索引是否失效。以下七种情况需要重点检查。情况一LIKE 以通配符开头-- 索引失效a% 才能走索引 SELECT * FROM t_user WHERE name LIKE %张;LIKE %张无法利用 B 树叶子节点的有序性只能全表扫描或扫全索引。如果业务上确实需要后缀匹配建议使用全文索引或搜索引擎。情况二对索引列使用函数或计算-- 索引失效 SELECT * FROM t_user WHERE YEAR(create_time) 2025; -- 正确写法等值范围查询可走索引 SELECT * FROM t_user WHERE create_time 2025-01-01 AND create_time 2026-01-01;只要索引列参与了函数运算优化器就无法使用索引因为索引键值已经被函数改变了。情况三隐式类型转换-- 假设 phone 是 varchar 类型 -- 索引失效因为 12345678901 会被转换为字符串后再比较 SELECT * FROM t_user WHERE phone 12345678901; -- 正确写法 SELECT * FROM t_user WHERE phone 12345678901;字符串列与数字比较时MySQL 会把字符串转换为数字导致索引列上发生了隐式转换。情况四条件中使用 OROR只要有一侧不是索引列整个查询就可能退化为全表扫描。如果name有索引而age没有索引WHERE name 张三 OR age 18会全表扫描。解决办法是把OR改成UNION ALL或者给两侧列都加上索引。情况五使用不等于WHERE status ! 1或WHERE status 1对于索引列来说需要扫描的值过于分散优化器大概率放弃索引。实际生产环境中可以用IN替代不等于来明确指定范围。情况六IS NULL 与 IS NOT NULLMySQL 8 对IS NULL的索引支持已经优化但IS NOT NULL在数据分布不均时依然可能不走索引。判断方式还是看执行计划。情况七联合索引不满足最左前缀字段在前面的查询条件中完全未出现索引直接失效前面已经分析过。4.2 覆盖索引优化覆盖索引是减少回表的重要优化手段。当查询需要的字段全部包含在索引中时InnoDB 可以直接使用索引返回结果不需要再回表读聚簇索引。-- 慢需要回表 SELECT * FROM t_user WHERE name 张三; -- 快覆盖索引直接返回 SELECT id, name FROM t_user WHERE name 张三;如果表上有索引idx_name(name)第二条 SQL 从索引本身就能拿到name和主键id无需回表。这就是为什么部分 SELECT 不建议带头SELECT *的原因。4.3 索引下推索引下推是 MySQL 5.6 引入的优化。在没有索引下推时联合索引(name, age)遇到WHERE name LIKE 张% AND age 18MySQL 先根据name的范围条件从索引中筛出符合的记录然后回表逐行判断age 18。启用索引下推后MySQL 会把age 18的判断下放到存储引擎层在读取索引的时候就过滤掉不符合age条件的记录减少回表次数。可以在执行计划里看到Using index condition关键字这就是索引下推生效的标志。EXPLAIN SELECT * FROM t_user WHERE name LIKE 张% AND age 18;结果中 Extra 列出现Using index condition说明索引下推生效。如果想要关闭下推可以执行SET optimizer_switch index_condition_pushdownoff;但一般不建议关闭。5. SQL 优化实战基于 EXPLAIN 分析执行计划5.1 EXPLAIN 核心字段EXPLAIN 是分析 SQL 性能的第一工具最需要关注的是这几个字段。字段含义type访问类型从好到坏依次是 system const eq_ref ref range index ALLkey实际使用的索引rows预计扫描的行数越小越好Extra额外信息重点关注 Using filesort、Using temporary、Using index conditiontype达到ref或range就已经是不错的状态。如果出现ALL说明是全表扫描要检查为什么没走索引。EXPLAIN SELECT id, order_no, user_id FROM t_order WHERE user_id 10086;执行后重点看type和key如果type ref且key指向idx_user_status说明联合索引生效。5.2 深分页优化LIMIT 100000, 20这种深分页MySQL 会扫描前 100020 行再丢弃前 100000 行越往后翻越慢。-- 慢深分页 SELECT * FROM t_order ORDER BY id LIMIT 100000, 20; -- 优化方案延迟关联先取主键再回表 SELECT t.* FROM t_order t INNER JOIN (SELECT id FROM t_order ORDER BY id LIMIT 100000, 20) tmp ON t.id tmp.id;子查询先在二级索引或主键索引上快速定位到 20 个主键再通过主键回表取完整行数据避免大范围扫描。5.3 ORDER BY 排序优化ORDER BY字段是否有索引直接影响是否出现Using filesort。文件排序在数据量大时非常慢。-- 联合索引 (a, b), WHERE 过滤 aORDER BY 使用 b可以避免 filesort SELECT * FROM t WHERE a 1 ORDER BY b; -- 如果排序字段和过滤字段不在同一个索引就会出现 filesort SELECT * FROM t WHERE a 1 ORDER BY c;优化思路是让排序字段尽量满足联合索引的顺序要求或者减少排序行数。5.4 隐式类型转换与函数计算检查-- 检查是否有隐式类型转换直接看执行计划 EXPLAIN SELECT * FROM t_user WHERE phone 12345678901;如果看到type ALL且字段本身有索引多半是隐式类型转换导致索引失效。把条件改成与字段类型一致的写法即可。5.5 避免 SELECT *这句话已经说过很多次但依然有一堆代码在裸奔。SELECT *的核心问题在于第一可能触发回表。普通索引无法覆盖全部列时每行都要回表一次。第二浪费网络 IO 和内存。不需要的大字段会占用大量资源。第三增加排序和临时表的可能性。建议只查询需要的字段必要的时候用覆盖索引。6. Mysql 调优实战案例与参数配置6.1 定位慢 SQL先开启慢查询日志。# 临时开启重启失效 SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL slow_query_log_file /var/log/mysql/mysql-slow.log;long_query_time 1表示记录执行超过 1 秒的 SQL。线上一般从 1 秒开始如果慢 SQL 太多可以调整到 2 秒或 3 秒先处理最严重的。# 查看慢查询日志路径 SHOW VARIABLES LIKE slow_query_log_file;6.2 Buffer Pool 调优InnoDB Buffer Pool 是缓存表和索引数据的内存区域大小直接决定磁盘 IO 频率。# my.cnf 示例 [mysqld] innodb_buffer_pool_size 4G innodb_buffer_pool_instances 4innodb_buffer_pool_size在纯数据库服务器上通常设置为物理内存的 50% 到 70%但不能超过物理内存。建议使用官方计算公式innodb_buffer_pool_size应大于数据库热数据总量。检查 Buffer Pool 命中率SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_read_requests; SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_reads;命中率 read_requests / (read_requests reads)长期低于 95% 说明 Buffer Pool 偏小。6.3 redo log 刷盘策略innodb_flush_log_at_trx_commit控制 redo log 的刷盘方式。参数值行为安全性性能1每次事务提交都刷盘最高每次提交落盘最慢0每秒刷盘崩溃时可能丢 1 秒数据最快2每次提交写入 OS 缓存每秒刷盘操作系统崩溃时可能丢 1 秒数据较快默认值 1 保证持久性。如果业务允许秒级数据丢失可以改成 2 提升性能。这里要注意这个判断必须结合实际场景金融类业务不建议改。6.4 排序和临时表参数sort_buffer_size 4M join_buffer_size 4M tmp_table_size 64M max_heap_table_size 64M这些参数不是越大越好。每次会话连接都会分配相应的 buffer过大会导致内存浪费。有大量排序和 join 的场景可以适当增大但要以实际监控为准。6.5 数据库开启审计引起索引争用的处理热搜词里提到“数据库开启审计引起索引争用”这是真实生产环境遇到的问题。开启数据库审计后每条操作都会被记录不仅带来大量写入还可能导致共享资源竞争加剧表现为锁等待、索引页争用、TPS 下降。排查思路是第一确认审计日志是否落盘到业务表所在的磁盘如果同一块磁盘IO 竞争会非常严重。第二检查审计策略是否过宽是否存在全量记录SELECT的情况。第三观察SHOW ENGINE INNODB STATUS中的锁等待信息看是否有明显的 latch 争用。审计本身不是问题问题是无差别记录和资源隔离不到位。建议按最小化原则配置审计策略只记录必要的高危操作日志输出到独立磁盘降低对业务索引访问的影响。6.6 一个完整调优案例假设场景订单表t_order有 500 万数据接口按user_id和create_time查订单列表接口经常超时。第一步查看慢查询日志定位到这条 SQLSELECT * FROM t_order WHERE user_id 10086 ORDER BY create_time DESC LIMIT 20;第二步执行 EXPLAIN发现type ALL全表扫描。第三步检查表索引发现只有主键索引没有user_id的索引。第四步添加联合索引ALTER TABLE t_order ADD INDEX idx_user_time (user_id, create_time);第五步再次 EXPLAIN发现type refkey是idx_user_timeExtra不再是Using filesort。接口耗时从原来的 2 秒下降到 30 毫秒以内。这个案例非常典型属于索引缺失加排序字段未纳入联合索引的常见组合。7. MySQL 高频面试题整理这里整理一组高频题每道题附带核心回答思路。7.1 InnoDB 为什么用 B 树而不是 B 树一句话版本B 树非叶子节点只存索引键树更矮磁盘 IO 更少叶子节点有序链表支持高效范围查询查询路径稳定性能可控。7.2 聚簇索引和二级索引的区别聚簇索引叶子节点存整行数据主键决定数据物理排序二级索引叶子节点存索引列加主键值查询可能回表。7.3 什么是覆盖索引需要查询的列全部包含在索引中不需要回表的索引通过 Extra 显示Using index确认。7.4 什么是索引下推存储引擎层在读取索引时先过滤部分条件减少回表次数Extra 显示Using index condition。7.5 联合索引的最左前缀原则联合索引按从左到右的顺序构建查询必须包含最左列才能命中索引。等值查询条件放前面范围查询条件放最后。7.6 索引失效的场景有哪些LIKE %xx、函数计算、隐式类型转换、OR连接非索引列、不满足最左前缀、IS NOT NULL等判断标准是看执行计划。7.7 深分页如何优化延迟关联先通过子查询定位主键再回表取完整数据避免全表扫描加丢弃。7.8find_in_set能走索引吗热搜词里出现这个问题答案是常规情况下FIND_IN_SET(col, 1,2,3)无法走索引。因为函数作用于索引列破坏了索引有序性。如果业务确实需要这种查询可以考虑业务表结构拆分或使用全文检索具体方案依赖实际业务模型。8. 性能监控与排查工具推荐8.1 系统层面top、vmstat、iostat用于观察 CPU、内存和磁盘 IO。# 查看磁盘 IO 是否繁忙 iostat -x 2 # 查看 MySQL 进程资源占用 top -p $(pgrep -x mysqld)磁盘 IO 高发时优先检查慢查询和 Buffer Pool 命中率。8.2 MySQL 层面-- 查看当前线程状态 SHOW FULL PROCESSLIST; -- 查看 InnoDB 状态重点关注锁等待和事务 SHOW ENGINE INNODB STATUS; -- 查看全局状态 SHOW GLOBAL STATUS LIKE Threads%;SHOW FULL PROCESSLIST是排查线上卡顿的第一入口。如果有大量Sending data、Waiting for table metadata lock、updating状态需要立刻定位对应 SQL。8.3 慢查询分析慢查询日志落盘后可以使用mysqldumpslow工具汇总。mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log按耗时排序取前 10 条优先优化出现频率最高、单次耗时最长的 SQL。9. 常见问题与排查方法问题现象可能原因排查方式解决方案有索引但不生效隐式类型转换、函数计算、错误查询写法EXPLAIN 查看 type 和 key重写 SQL避免索引列参与运算查询偶尔快偶尔慢Buffer Pool 命中率不稳定查看命中率观察慢查询时间段增大 buffer pool分析是否为热点数据突然增加数据库频繁 IO大量缓存未命中或结果集过大iostat、命中率监控调大 buffer_pool_size优化 SQL 减少扫描行接口偶发超时锁等待或大事务SHOW PROCESSLIST 查看阻塞源定位长事务拆分事务减少锁持有时间排序慢缺少适合排序的索引Extra 看到 Using filesort将排序字段纳入联合索引联表查询慢关联字段无索引或驱动表选错EXPLAIN 查看驱动表和 key给关联字段加索引使用小表驱动大表深分页慢扫描和丢弃大量行查看 LIMIT 位置使用延迟关联一批相同 SQL 突然变慢统计信息过期或执行计划变化ANALYZE TABLE重新分析表统计信息必要时强制指定索引10. 最佳实践与避坑建议到此为止从 B 树原理到联合索引、SQL 优化、Mysql 调优实战、面试题已经完整走了一遍。这里再给几条工程化建议也是以后优化数据库的固定套路。第一每张表的索引数量控制在 5 个以内索引不是越多越好写入和更新都要维护索引。第二所有上线 SQL 先过一遍 EXPLAIN杜绝type ALL的查询直接上线。第三慢查询日志从第一天就开启收集历史慢 SQL建立优化清单。第四索引命名规范要有例如idx_表名_字段名方便排查。第五涉及生产数据库结构变更时先在测试环境验证执行计划和耗时。文章开头说过索引失效和索引缺失是线上问题的两个大头。现在你可以打开自己项目的数据库查一下慢查询日志把执行时间最长的三条 SQL 拿出来用 EXPLAIN 分析一遍按文中第 5 节的方法调整索引和 SQL 写法大概率能解决相当一部分性能问题。如果想在这个方向继续深入下一步可以研究 InnoDB 的锁机制与隔离级别、MVCC 多版本控制、主从复制延迟以及分库分表方案。这些内容配合本文的索引和调优基础足以覆盖日常工作与大部分技术面试场景。
