兄弟们有没有过这种经历面试的时候面试官笑眯眯地问你“小伙子你做过 MySQL 性能优化吗”你一拍胸脯“做过加索引啊”面试官点点头接着追问“那你说说索引为什么会失效”你“呃... 好像是...like % xxx还有... 函数”面试官“那你再说说联合索引的最左前缀原则是啥为什么会有这个原则”你“... 就是... 左边的列要先查”面试官“那你线上遇到过慢查询吗你是怎么排查的”你“... 开启慢查询日志然后加索引”然后就没有然后了面试官说“行那你回去等通知吧。”是不是扎心了其实不是你不会是你只知道 “怎么做”不知道 “为什么这么做”面试官要的不是你背几个名词是你能不能从底层原理出发把整个优化的逻辑讲清楚能不能解决线上的真实问题。今天这篇文章我把 MySQL 性能优化的所有面试考点从底层原理到实战优化从 SQL 到架构全部给你讲透。看完这篇下次面试官再问你 MySQL 优化你直接把这些甩给他保准他当场给你发 offer。一、先搞懂优化不是瞎搞要按优先级来很多人一听到性能优化第一反应就是“老板给我加个 8 核 16G 的服务器” 或者 “我们搞个分库分表吧”大错特错MySQL 的优化是有优先级的就像这个金字塔越靠下的优化成本越低收益越高最底层SQL 与索引优化这是性价比最高的80% 的性能问题改改 SQL加个索引就解决了成本几乎为 0收益能翻几十倍。第二层结构优化表结构设计字段类型选择范式反范式分区表这些成本也不高。第三层架构优化读写分离分库分表缓存这些成本就高了要改代码要搭新的服务。最顶层硬件优化加内存换 CPU升级服务器这是最后才考虑的成本最高收益最低。你想想你一个慢 SQL本来加个索引就能从 5 秒降到 0.01 秒你非要去买个几十万的服务器这不就是冤大头吗所以优化的正确顺序是先改 SQL再加索引再调结构最后才考虑架构和硬件。记住这个顺序面试的时候你一说出来面试官就知道你是懂行的不是那种上来就搞分库分表的菜鸟。二、第一步怎么找到问题慢查询与 EXPLAIN优化的前提是你得先知道哪里出问题了对吧总不能闭着眼睛瞎优化吧2.1 怎么定位慢查询线上系统慢了你怎么知道是哪个 SQL 慢了很简单开启 MySQL 的慢查询日志。你可以在 my.cnf 里配置# 开启慢查询日志 slow_query_log1# 超过2秒的SQL就记录下来 long_query_time2# 把没走索引的SQL也记录下来 log_queries_not_using_indexesON然后你就可以用工具去分析这个日志了比如 MySQL 自带的mysqldumpslow或者更强大的pt-query-digest直接就能给你把最慢的 SQL执行最多的 SQL 都排出来。如果你的公司有监控系统比如 Skywalking、Prometheus那就更简单了直接在监控面板上就能看到哪个接口慢哪个 SQL 慢一目了然。2.2 拿到慢 SQL怎么分析用 EXPLAIN找到慢 SQL 之后别急着改先拿 EXPLAIN 分析一下看看这个 SQL 到底是哪里慢了。EXPLAIN 就是 MySQL 的执行计划你在 SQL 前面加个 EXPLAINMySQL 就会告诉你它会怎么执行这个 SQL有没有走索引扫描了多少行有没有排序有没有临时表。比如这个这里面有几个字段是你必须要懂的面试必考1. type访问类型这个是最核心的这个字段表示 MySQL 是怎么查找数据的性能从好到差依次是system const eq_ref ref range index ALLALL全表扫描这是最差的说明你的 SQL 没走索引把整个表都扫了一遍数据量大的话这能慢死。index扫描整个索引树比 ALL 好一点但也很慢比如你查 count (*) 的时候可能会出现。range范围查询比如 where id 10这个还可以至少是走了索引的只扫描了索引的一部分。ref普通索引的等值查询比如 where name 张三 这个不错能命中索引。eq_ref连接查询的时候被驱动表用主键或者唯一索引等值查询比如连表的时候每个主表的行去从表查一条这个很快。const主键或者唯一索引的等值查询比如 where id 1MySQL 能把这个查询转换成常量一次就查到了。优化的目标是什么至少要达到 range最好是 ref绝对不能出现 ALL如果你的 type 是 ALL那说明你这个 SQL 肯定有问题要么没索引要么索引失效了。2. key实际使用的索引这个字段告诉你MySQL 实际用了哪个索引如果是 NULL说明没用到索引那你就要小心了。这里要注意possible_keys 是可能用到的索引key 是实际用到的有时候可能会出现 possible_keys 有好几个但是 key 是 NULL说明 MySQL 觉得全表扫描比走索引还快比如你这个表数据很少的时候或者你索引的区分度太低了。3. rows预估扫描的行数这个就是 MySQL 预估它要扫描多少行才能拿到你要的数据越小越好。比如你查 10 条数据rows 显示 500 万那说明它扫了整个表肯定慢。4. Extra额外信息这个字段里有很多重要的信息比如Using index覆盖索引这是好事说明你查询的所有字段都在索引里不需要回表速度非常快。Using filesort这是坏事说明 MySQL 需要额外的排序因为索引里的顺序不是你要的它只能把数据查出来之后再排序数据量大的话这个排序能慢死。Using temporary这更糟说明 MySQL 用了临时表一般是你 group by 的时候或者 distinct 的时候没有索引只能建个临时表来处理这个非常慢。Using index condition这是好事索引下推MySQL5.6 之后的优化能在索引层就过滤掉不符合条件的数据减少回表的次数。所以你拿到一个慢 SQL先跑个 EXPLAIN看看这几个字段马上就知道问题出在哪了。比如如果你看到 type 是 ALL那就是没走索引如果你看到 Extra 是 Using filesort那就是排序没走索引如果你看到 rows 是几百万那就是扫描了太多行。三、核心中的核心索引优化80% 的问题都在这讲完了怎么定位问题接下来就是最核心的部分索引优化。为什么索引这么重要因为没有索引的话MySQL 查数据就要全表扫描就像你看书没有目录你要一页一页翻那能不慢吗有了索引就像有了目录你直接就能找到你要的内容在哪。3.1 索引的底层为什么是 B 树面试必问MySQL 的索引为什么用 B 树不用 Hash不用二叉树不用红黑树这个问题你要是答不上来那你就别说你懂索引了。首先我们要知道数据库的数据是存在磁盘上的查询的时候要把磁盘的页加载到内存里一次磁盘 I/O 是很慢的大概 10ms 左右所以我们要尽量减少磁盘 I/O 的次数。那为什么不用二叉树因为二叉树太高了比如你有 1000 万条数据二叉树的高度就是 log2 (1000 万)≈24那你查一条数据就要 24 次 I/O这谁受得了那红黑树呢红黑树是平衡二叉树高度也差不多还是太高了1000 万数据也要 20 多层还是要 20 多次 I/O。那 B 树呢B 树是平衡多叉树一个节点能存多个 key这样树的高度就低了。比如一个节点能存 100 个 key那 3 层的 B 树就能存 100100100100 万数据4 层就能存 1 亿这样查数据最多 4 次 I/O快多了。但是 B 树还是有问题B 树的非叶子节点也存数据这样的话一个节点能存的 key 就少了因为数据占了空间那树的高度还是会变高。而且 B 树做范围查询很麻烦因为数据分散在各个节点你要遍历整个树。那 B 树呢B 树就是 B 树的改进版B 树的特点是非叶子节点只存索引不存数据所有的数据都存在叶子节点。这样的话非叶子节点就能存更多的 key树的高度就更低了比如一个 16KB 的页存 int 的 key一个 key4 字节那一个节点就能存 400 多个 key3 层的 B 树就能存 4004004006400 万数据也就是说你查任何一条数据最多 3 次 I/O 就够了叶子节点是有序的而且用双向链表连起来这样的话范围查询就太方便了你找到起点之后直接遍历链表就行了不用再遍历整个树。所有的查询都要走到叶子节点所以查询的性能非常稳定不会像 B 树那样有时候查一次就查到了有时候要查好几次。那 Hash 索引呢Hash 索引等值查询确实快O (1)但是它不支持范围查询不支持排序不支持最左前缀而且有 Hash 冲突的问题所以大部分场景都不用。这就是为什么 MySQL 用 B 树做索引这个问题你这么答面试官绝对满意。3.2 聚簇索引 vs 二级索引回表是什么接下来面试常问聚簇索引和非聚簇索引有什么区别什么是回表InnoDB 的索引分两种聚簇索引主键索引叶子节点存的是整行的数据。也就是说主键索引的叶子节点就是数据本身所以 InnoDB 的表数据就是按主键的顺序存的。二级索引也叫辅助索引就是我们普通建的索引比如给 name 建个索引它的叶子节点存的不是整行数据是主键的 id。哦原来如此那你用二级索引查询的时候会发生什么比如你有个索引在 name 上你执行select * from user where name 张三。首先你去 name 的索引树里找找到 name 张三 对应的主键 id10。然后你还要去主键的索引树里根据 id10找到对应的整行数据。这个过程就叫回表你看本来查一次就够了结果回表要查两次这就多了一次 I/O性能就差了。那怎么避免回表用覆盖索引啊什么是覆盖索引就是你查询的所有字段都在索引里那你就不需要回表了。比如你执行select id, name from user where name 张三id 是主键name 是索引字段这两个字段都在 name 的索引树里那你查到 name 张三 的时候直接就能拿到 id 和 name 了不需要再去主键索引里查了这就是覆盖索引Extra 里会显示 Using index速度非常快。这就是为什么我们说不要用 select \因为你用 select \的话你要查所有字段那肯定要回表但是如果你只查需要的字段就能用上覆盖索引速度快好几倍。3.3 联合索引的最左前缀原则到底是啥接下来面试必问联合索引的最左前缀原则是什么为什么会有这个原则比如我们建了一个联合索引idx_prof_age_status(profession, age, status)三个字段profession、age、status。那这个索引的排序是怎么排的它是先按 profession 排序profession 相同的再按 age 排序age 相同的再按 status 排序。就像我们的通讯录先按姓排序姓相同的再按名排序对吧那最左前缀原则是什么意思就是你查询的时候必须从最左边的列开始不能跳过中间的列。比如where profession 软件工程能用到索引因为最左列有了。where profession 软件工程 and age 30能用到两个列都有。where profession 软件工程 and age 30 and status 0全用到最好。where profession 软件工程 and status 0只能用到 profession 这个列status 用不到因为跳过了 age。where age 30 and status 0用不到索引因为最左列 profession 没了。where status 0完全用不到。为什么会这样因为索引是先按 profession 排的你连 profession 都不给MySQL 根本不知道从哪开始找啊就像你查通讯录你不给姓直接给名那索引就没用了你只能全表扫。哦原来如此所以联合索引的字段顺序是非常重要的怎么排等值查询的字段放前面范围查询的放后面。比如你经常查where profession ? and age ?那你就把 profession 放前面age 放后面因为 profession 是等值age 是范围这样的话profession 能用到age 也能用到但是 age 后面的字段就用不到了所以范围字段要放最后。这就是最左前缀原则你这么讲面试官就知道你真的懂了不是只会背名词。3.4 索引失效的 7 种经典场景面试必考好了最头疼的问题来了为什么我建了索引查询还是慢为什么索引失效了这 7 种场景你一定要记牢面试的时候面试官肯定会问你你能说出 5 种以上就赢了。1. 对索引列用函数或者计算比如-- 错对create_time用了DATE函数索引失效了 select * from user where DATE(create_time) 2024-01-01 -- 对改成范围查询索引就能用了 select * from user where create_time 2024-01-01 and create_time 2024-01-02为什么因为索引存的是字段的原始值你对字段用了函数那索引里的值就不对了MySQL 根本没法用索引啊它只能把每一行的字段都拿出来算一下那不就是全表扫描吗2. 模糊查询以 % 开头比如-- 错%在开头索引失效 select * from user where name like %张三 -- 对%在结尾索引能用 select * from user where name like 张三%为什么因为索引是按字符串的顺序排的你要查以张三结尾的那索引的顺序就没用了你只能全表扫。如果是开头的那就能用因为索引里 name 是排好序的开头是张三的都在一块。3. 隐式类型转换比如你的 phone 字段是 varchar 类型你这么查-- 错phone是varchar你传了数字隐式转换了索引失效 select * from user where phone 13800138000 -- 对加引号类型对了索引就能用了 select * from user where phone 13800138000这个坑很多人踩过为什么因为 MySQL 会把字符串转成数字那你对索引列做了转换就跟用了函数一样索引就失效了。4. OR 条件里有非索引列比如-- 错name有索引age没有那整个OR的条件都用不到索引 select * from user where name 张三 or age 30为什么因为 OR 的意思是只要满足其中一个条件就行那如果 age 没有索引那 MySQL 只能全表扫才能找到 age30 的那 name 的索引也就没用了。那怎么优化要么给 age 也加个索引要么改成 UNION ALLselect * from user where name 张三 union all select * from user where age 30这样两个查询各自用自己的索引然后合并结果就快了。5. 联合索引里范围查询右边的列失效比如我们的联合索引是 (profession, age, status)-- 这里age用了范围查询那后面的status就用不到索引了 select * from user where profession 软件工程 and age 30 and status 0为什么因为 age 是范围age 后面的 status 的顺序就乱了profession 相同的age 是排好序的但是 age30 的那些行status 是没排序的所以 status 的索引就用不到了。那怎么优化把范围查询的字段放最后或者调整索引的顺序把 status 放前面age 放后面。6. 使用、、NOT IN 这些负向查询比如-- 错普通索引用!索引会失效 select * from user where status ! 0为什么因为负向查询的话你要查所有不等于 0 的那大部分数据可能都是不等于 0 的MySQL 觉得全表扫描比走索引还快所以就不走索引了。当然如果你的主键用那还是会走索引的因为主键的索引不一样。7. IS NULL 或者 IS NOT NULL很多人说 IS NULL 会导致索引失效其实也不一定要看情况。如果你的字段允许 NULL那索引里是会存 NULL 的但是如果大部分数据都是 NULL那索引的区分度就太低了MySQL 就不会用索引了。所以最好的办法是给字段加 NOT NULL给个默认值比如 0这样就不会有 NULL 的问题了。这 7 种场景你记牢了下次面试官问你索引为什么失效你一个一个给他列出来他绝对服。四、SQL 查询优化这些写法能让你的 SQL 快 10 倍索引搞完了接下来就是 SQL 本身的优化了很多时候你的 SQL 写的不对就算有索引也快不起来。4.1 绝对不要用 SELECT *这个我已经说了很多次了为什么你查了很多没用的字段增加了网络传输的开销本来你要查 3 个字段你查了 10 个那传输的数据就多了 3 倍。你用了 SELECT *就没法用覆盖索引了必须要回表性能差很多。如果你的表有大字段比如 text、blob那你查出来就更慢了。所以永远只查你需要的字段不要偷懒用 SELECT *。4.2 大分页查询的优化limit offset 的坑你有没有遇到过分页查询越往后越慢比如-- 第一页很快0.01秒 select * from user order by id limit 10, 10 -- 第一百万页慢死了10秒 select * from user order by id limit 1000000, 10为什么因为 MySQL 不是跳过 100 万行直接拿后面的它是先把前 1000010 行都查出来然后扔掉前 1000000 行给你最后 10 行那前 100 万行它都要扫描一遍能不慢吗那怎么优化用主键分页啊-- 优化之后0.01秒 select * from user where id 1000000 order by id limit 10哦原来如此你记录一下上一页的最大 id下一页就从这个 id 之后开始查这样 MySQL 就只需要扫描 10 行就行了快的飞起。如果你的排序不是主键是别的字段比如 create_time那也一样select * from user where create_time 2024-01-01 and id ( select id from user where create_time 2024-01-01 order by create_time limit 1000000, 1) order by create_time limit 10这样就搞定了大分页再也不怕了。4.3 小表驱动大表EXISTS 代替 IN连表查询的时候谁驱动谁很重要。比如你有两个表A 表 100 行B 表 100 万行。如果你用A join B那就是循环 A 的 100 行去 B 里查循环 100 次。如果你用B join A那就是循环 B 的 100 万行去 A 里查循环 100 万次。那肯定是小表驱动大表快啊那子查询的时候IN 和 EXISTS 怎么选如果子查询的表小主表大那用 IN。如果子查询的表大主表小那用 EXISTS。比如-- 子查询的order表很大user表小用EXISTS select * from user u where exists (select 1 from order o where o.user_id u.id)EXISTS 的原理是先查主表然后把主表的每一行带到子查询里判断所以主表小的话就很快。4.4 UNION ALL 代替 UNION如果你要合并两个查询的结果能用 UNION ALL 就不要用 UNION。因为 UNION 会给你去重去重就要排序就要建临时表非常慢。而 UNION ALL 只是把结果合并起来不会去重快很多。所以如果你确定两个查询的结果没有重复那就用 UNION ALL。4.5 ORDER BY 和 GROUP BY 的优化ORDER BY 为什么会出现 Using filesort因为你的排序字段没有索引MySQL 只能自己排序。那怎么优化给排序字段建索引或者让排序字段在联合索引的后面这样索引本身就是排好序的MySQL 直接拿就行了不需要再排序了。比如你的联合索引是 (profession, age)那你order by age如果 profession 是等值查询那 age 就是排好序的就不需要排序了。GROUP BY 也是一样给分组字段建索引这样就不用临时表了。还有默认 GROUP BY 会排序你可以加order by null取消排序能快很多。五、锁与事务优化高并发下的性能杀手讲完了查询接下来就是并发的问题了很多时候你的系统慢不是查询慢是锁冲突是事务的问题。5.1 InnoDB 的锁行锁、表锁、间隙锁InnoDB 的锁不是只有行锁和表锁还有间隙锁。行锁锁某一行比如你 update user where id1就锁 id1 这一行其他行可以正常操作并发很高。表锁锁整个表比如你执行 alter table就会锁表这时候所有的操作都要等非常影响性能。间隙锁锁一个范围比如你 where id 10那它会锁 id10 的这个间隙防止其他事务在这个间隙里插入数据这就是为了解决幻读的。那什么时候会锁表比如你没有索引那 MySQL 就没法用行锁只能用表锁因为它不知道你要改哪一行只能把整个表都锁了这就坑了比如-- 你以为这是行锁不对age没有索引所以MySQL锁了整个表 update user set name 张三 where age 30我的天这就是很多人踩过的坑没有索引导致行锁变表锁然后整个系统的并发就没了所有的操作都要等系统直接卡死。所以更新的条件一定要有索引不然锁表就完了。5.2 死锁怎么解决高并发下死锁是很常见的什么是死锁两个事务互相等对方的锁谁也不让谁就卡死了。比如事务 1先锁 id1再锁 id2事务 2先锁 id2再锁 id1然后事务 1 等事务 2 的 id2事务 2 等事务 1 的 id1就死锁了。那怎么解决死锁固定加锁的顺序所有的事务都按 id 的顺序来加锁比如先锁 id 小的再锁 id 大的这样就不会交叉了。缩短事务的时间事务越小越好不要把大的操作放在事务里不要在事务里做 RPC 调用不要在事务里等事务执行完赶紧提交锁早点释放。开启死锁检测InnoDB 默认是开启的检测到死锁会回滚其中一个小的事务让另一个继续执行。设置锁超时时间比如 innodb_lock_wait_timeout5等 5 秒拿不到锁就超时回滚不要一直等。5.3 MVCC为什么读不加锁写不阻塞读面试必问MVCC 是什么它是怎么工作的为什么 InnoDB 能做到读不加锁写不阻塞读就是因为 MVCC多版本并发控制。简单来说就是每行数据都有多个版本你读的时候读的是你事务开启的时候的那个版本不管别人怎么改你都看不到这样你读的时候就不需要加锁了写的时候也不会阻塞读。它的原理是啥每行数据有三个隐藏列DB_TRX_ID最后修改这个行的事务 IDDB_ROLL_PTR回滚指针指向 undo log也就是这个行的历史版本DB_ROW_ID隐藏的行 id如果没有主键的话当你开启一个事务的时候MySQL 会给你生成一个 Read View就是你这个事务的快照里面记录了当前所有活跃的事务 ID。你读数据的时候会拿行的 DB_TRX_ID 跟 Read View 比判断这个版本对你是不是可见的如果行的事务 ID 比最小的活跃事务还小说明这个事务已经提交了你能看到。如果比最大的还大说明这个事务是你开启之后才开的你看不到。如果在中间那看这个事务是不是在活跃列表里如果在说明还没提交你看不到就去 undo log 里找历史版本。哦原来如此这就是 MVCC所以你读的时候根本不需要加锁直接读历史版本就行了所以读和写不冲突并发就上去了。这就是为什么 MySQL 默认的隔离级别是可重复读就是靠 MVCC 实现的能解决不可重复读和幻读的问题。六、配置优化这几个参数调对了性能翻几倍SQL 和索引都搞完了接下来就是 MySQL 的配置了这几个核心参数你调对了性能能翻好几倍。6.1 innodb_buffer_pool_size最重要的参数这个是 InnoDB 用来缓存数据和索引的内存这个越大越好因为你把数据都缓存到内存里就不用读磁盘了磁盘多慢啊。推荐值是你物理内存的 70%-80%比如你服务器有 16G 内存那你就给它 12G别太小了不然缓存不住经常读磁盘能慢死。6.2 innodb_log_file_sizeRedo 日志的大小这个是 Redo 日志的大小Redo 日志是用来崩溃恢复的越大的话刷盘的次数就越少写入的性能就越好。推荐设成 1-4G别太小了不然频繁刷盘写入就慢了。6.3 max_connections最大连接数很多人以为这个越大越好不对连接数太多的话MySQL 的上下文切换就会很频繁反而会慢。一般来说4 核 8G 的服务器设个 1000 就够了8 核 16G 的设个 2000 就够了不要设太大。而且应用层一定要用连接池比如 HikariCP不要每次请求都新建连接那连接数很容易就满了。6.4 innodb_flush_log_at_trx_commit刷盘策略这个参数控制 Redo 日志的刷盘策略1默认事务提交的时候立即把 Redo 日志刷到磁盘最安全不会丢数据但是性能差一点。2事务提交的时候先写到操作系统的缓存每秒刷一次盘性能好一点但是如果服务器宕机可能会丢 1 秒的数据。0每秒刷一次不管事务性能最好但是最不安全宕机就丢 1 秒的数据。如果是金融业务对数据一致性要求高那就用 1保证不丢数据。如果是普通业务能接受一点数据丢失那就用 2性能更好。6.5 Linux 层面的优化还有 Linux 的配置比如把 swappiness 设成 1禁止用 swap数据库尽量用内存不要用交换分区不然慢死。打开文件数限制ulimit -n 65535因为 MySQL 要打开很多文件。文件系统用 XFS挂载的时候加 noatime不要记录访问时间提升性能。七、架构优化当单库扛不住了怎么办当你的单库单表SQL 和索引都优化到极致了还是扛不住了那就要考虑架构层面的优化了。7.1 读写分离最常见的就是读写分离主库负责写从库负责读因为大部分系统都是读多写少比如读的 QPS 是 1 万写的是 1 千那你搞几个从库把读的压力分摊到从库上就搞定了。怎么实现用中间件比如 ShardingSphere或者应用层自己做数据源路由写的时候走主库读的时候走从库。当然读写分离有个问题就是主从延迟主库写了从库同步需要时间这时候你读从库可能读不到最新的数据。那怎么解决如果是对一致性要求高的就强制读主库或者用缓存或者二次读取先读从库没读到再读主库。7.2 分库分表当单表的数据量太大了比如超过了 1000 万那就算有索引查询也会慢这时候就要分库分表了。分库分表有两种垂直拆分把一个大表按字段拆成多个小表比如把 user 表拆成 user_baseid,name,age和 user_extendid,address,bio热数据放一个表冷数据放另一个这样每次查询的页就少了性能就高了。或者按业务拆把用户的放用户库订单的放订单库把压力分开。水平拆分把一个表的数据按某种规则拆到多个结构相同的表里面比如按 user_id%16拆成 16 个表每个表存一部分数据这样每个表的数据量就小了。常用的中间件就是 ShardingSphere帮你做分片你不用自己写代码就像用单库一样。当然分库分表也有坑比如跨表的 join 不能用了事务不好做了分页也麻烦了所以不要上来就分库分表能不分就不分只有当单库单表真的扛不住了再考虑。7.3 缓存还有缓存把热点数据放到 Redis 里读的时候先读 Redis没有再读 MySQL这样大部分的读请求都被缓存挡住了MySQL 的压力就小了。比如你有个商品详情的接口QPS 很高那你把商品详情放到 Redis 里过期时间设个 1 小时更新的时候删一下缓存这样 99% 的请求都走 RedisMySQL 根本不用管压力就小了。7.4 冷热数据分离还有很多数据比如一年前的订单你很少会查那你就把它归档到归档库或者 OSS 里主库只存最近一年的热数据这样主库的数据量就小了查询就快了。八、实战案例一个慢 SQL 从 6 秒优化到 0.05 秒的全过程讲了这么多我们来看一个真实的案例看看我们是怎么优化一个慢 SQL 的。问题描述我们的用户列表页加载的时候要 6 秒SQL 是这样的SELECT * FROM user WHERE create_time BETWEEN 2024-01-01 AND 2024-12-31 ORDER BY create_time LIMIT 100000, 20;这个表有 500 万行数据你看分页到第 10000 页慢的要死。第一步EXPLAIN 分析我们先跑个 EXPLAINtype: ALL rows: 5000000 Extra: Using filesort哦原来全表扫描还需要排序这能不慢吗第二步加索引我们先给 create_time 加个索引ALTER TABLE user ADD INDEX idx_create_time(create_time);再跑 EXPLAINtype: range rows: 2000000 Extra: Using index哦现在走了索引了但是还是有 filesort不对create_time 是索引排序应该走索引啊哦不对因为我们的 limit offset 太大了还是要扫描很多行。执行时间变成了 2 秒比之前的 6 秒快了但是还是不够。第三步优化大分页我们用之前说的主键分页的方法改 SQLSELECT * FROM user WHERE create_time BETWEEN 2024-01-01 AND 2024-12-31 AND id ( SELECT id FROM user WHERE create_time BETWEEN 2024-01-01 AND 2024-12-31 ORDER BY create_time LIMIT 100000, 1 ) ORDER BY create_time LIMIT 20;哦这样改了之后子查询用了覆盖索引很快就拿到了起始的 id然后主查询从这个 id 之后查只需要扫描 20 行。现在执行时间是多少0.05 秒我的天从 6 秒降到 0.05 秒快了 120 倍你看这就是优化的力量就改了个 SQL加了个索引就搞定了根本不需要加服务器不需要分库分表。九、面试高频问题汇总直接背就行最后我把面试最常问的问题给你整理好了你直接背面试官问你你直接答1. 你做过哪些 MySQL 性能优化答我一般是先开启慢查询日志找到慢 SQL然后用 EXPLAIN 分析执行计划首先优化 SQL 和索引比如加联合索引避免索引失效优化大分页避免 select *然后调整 MySQL 的配置比如增大 buffer pool最后如果单库扛不住了再做读写分离和分库分表。2. 索引为什么会失效答有 7 种情况1. 对索引列用函数或者计算2.like % 开头的模糊查询3. 隐式类型转换4.OR 条件里有非索引列5. 联合索引范围查询右边的列6. 负向查询比如、NOT IN7. 违背最左前缀原则。3. 什么是回表怎么避免答用二级索引查询的时候先查到主键再去主键索引查完整数据这个过程叫回表。可以用覆盖索引避免就是查询的字段都在索引里不需要回表。4. 最左前缀原则是什么答联合索引是按最左列排序的所以查询的时候必须从最左列开始不能跳过中间列不然索引就失效了。所以建联合索引的时候等值查询的字段放前面范围的放后面。5. 慢查询你是怎么排查的答先开启慢查询日志抓到慢 SQL然后用 EXPLAIN 分析执行计划看 type 是不是全表扫描有没有 filesort有没有临时表然后看索引有没有失效然后优化 SQL 和索引。6. 为什么 MySQL 用 B 树做索引答因为 B 树的非叶子节点只存索引不存数据所以树的高度很低3 层就能存 6400 万数据最多 3 次 I/O而且叶子节点是有序的双向链表支持范围查询查询性能稳定。7. MVCC 是什么怎么工作的答MVCC 是多版本并发控制用来实现读写不冲突每行数据有多个版本事务开启的时候生成 Read View读的时候读自己的快照不需要加锁靠 undo log 存历史版本实现了可重复读。8. 死锁怎么解决答首先固定加锁的顺序然后缩短事务的时间不要长事务开启死锁检测设置锁超时时间。9. 大分页怎么优化答用主键分页记录上一页的最大 id下一页从 id 之后查避免用 limit offset因为 offset 会扫描前面的所有行。10. 主从延迟的原因是什么怎么解决答主从延迟是因为主库的写压力大从库同步慢或者大事务或者从库的性能差。解决的话对一致性要求高的强制读主库用缓存或者用半同步复制。写在最后兄弟们MySQL 性能优化真的不是什么玄学它是一套有章可循的方法论。你不需要背多少八股文你只要搞懂底层的原理搞懂优化的优先级遇到问题的时候按步骤来先定位再分析再优化80% 的问题都能解决。下次面试面试官再问你 MySQL 优化你就把这篇文章里的内容从优化金字塔到 EXPLAIN到索引原理到实战案例给他讲一遍保准他当场就给你发 offer你直接怼他“你还有啥要问的”祝大家都能拿到心仪的 offer面试顺利