MySql优化的场景有哪些?

一、EXPLAIN执行计划是什么?为什么看它?

比喻:
想象你要去图书馆找一本书,你会先查目录,看书在哪个区域、哪排书架,这就是执行计划告诉你“数据库怎么找数据”的路线图。


二、EXPLAIN输出主要字段解释

字段作用/比喻你要重点看什么?
id任务编号,谁先干活看清楚执行顺序
select_type查询类型,比如简单查、子查询复杂SQL有子查询要注意
table当前正在查的表确认表名
type访问方式,数据库找书的效率等级ALL最差(全书架找),const最好(目录直达)
possible_keys可能用的索引有索引就好
key实际用的索引最重要,看数据库到底用哪个索引
key_len索引长度,越长越精准越长越精确,代表范围更窄
ref哪个字段或值和索引匹配帮你理解条件怎么用索引
rows大概扫多少行,相当于找多少本书越小越好
Extra额外信息,比如“用临时表”或“文件排序”不希望看到 Using filesortUsing temporary


三、访问类型(type)详解

访问类型意义比喻优劣排序(好到差)
system表只有1行图书馆只有1本书最快
const主键或唯一索引匹配直接拿到指定书极快
eq_ref关联主键唯一匹配按目录找到特定书
ref普通索引匹配找索引区段里的多本书
range索引范围扫描按分类范围查找书
index全索引扫描扫描所有目录页
ALL全表扫描一排排书架全翻最慢


四、重要Extra说明

  • Using index:只用索引,不回主表(回表),效率高。

  • Using filesort:额外排序,可能慢。

  • Using temporary:用临时表,可能慢,出现时注意优化。

  • Using index condition:索引条件下推,InnoDB新技术,提升效率。


五、什么是回表?为什么要避免?

回表比喻:
你先在目录(索引)找到书编号,再跑到书架(主表)去拿书。多了跑动,花时间。

避免方法:
用覆盖索引,索引里有你需要的所有信息,就不用再跑书架。

六、EXPLAIN实际示例

EXPLAIN SELECT id, name FROM user WHERE id = 100;

假设结果:

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEuserconstPRIMARYPRIMARY4const1Using index

解释:

  • type是const,说明用主键直接定位到一行,非常快。

  • Extra显示Using index,表示只用了索引,不回表。


七、优化技巧与工具

  • 索引设计:理解联合索引的最左前缀原则。

  • 参数化查询:避免拼接字符串导致缓存失效。

  • 数据类型匹配:条件字段类型必须和索引字段一致。

  • 慢查询日志:定位长时间SQL。

  • performance_schema:细粒度性能监控。

  • 读写分离:减轻主库压力。

  • 缓存:热点数据用Redis缓存。


八、面试回答模板

“我一般先用EXPLAIN查看执行计划,重点关注type和key字段,判断是否用到索引。比如type是ALL说明全表扫描,应该考虑加索引。遇到回表时,我会考虑设计覆盖索引,减少回表。额外信息里如果有Using filesort或Using temporary,说明有排序或临时表开销,需优化排序或分组。实际执行我会用EXPLAIN ANALYZE,结合慢查询日志和performance_schema,定位具体瓶颈。优化常用手段有索引设计、SQL重写、分页游标和缓存。结合读写分离和监控,保证系统整体性能稳定。”


九、总结口诀

看执行计划,先看type,索引用上performance high;
回表要少,覆盖为王,filesort临时别用忙;
分页别深游标快,缓存统计秒杀慢;
慢查询抓得紧,性能提升少不了。


索引命中看 key,扫描行数看 rows;
字段尽量不 *,回表避免漏;
Extra 一查 filesort,优化排序是王道;
LIMIT 慢查游标快,COUNT 靠缓存好。


MySQL常见优化场景详解(带问题、原因、优化及示例)

  • 场景编号优化场景问题表现原因分析优化建议SQL示例
    1全表扫描查询慢,扫描大量数据查询字段无索引,导致全表扫描针对查询字段建立合适索引CREATE INDEX idx_name ON user(name);
    SELECT * FROM user WHERE name='张三';
    2函数包裹字段导致索引失效查询慢,执行计划未用索引对索引字段使用函数,MySQL无法用索引避免在索引字段上使用函数,改用范围查询WHERE DATE(create_time)='2025-06-09' 改为
    WHERE create_time BETWEEN '2025-06-09 00:00:00' AND '2025-06-09 23:59:59'
    3回表次数多查询响应慢,IO开销大查询字段未被索引覆盖,导致频繁回表设计覆盖索引(索引包含所有查询字段)CREATE INDEX idx_email_name_age ON user(email, name, age);
    SELECT name, age FROM user WHERE email='a@b.com';
    4排序用filesort查询慢,额外排序开销大ORDER BY字段未被索引覆盖,MySQL使用filesort排序为ORDER BY字段建立合适索引CREATE INDEX idx_create_time ON orders(create_time DESC);
    SELECT * FROM orders ORDER BY create_time DESC;
    5深分页慢OFFSET值大时查询响应极慢OFFSET跳过大量数据,导致大量扫描用“游标分页”或基于主键ID分页SELECT * FROM user WHERE id < ? ORDER BY id DESC LIMIT 10;
    6COUNT(*) 慢COUNT(*)全表扫描导致慢无法用索引统计,MySQL扫描全表维护缓存计数,或建小型统计表用Redis缓存计数,或者维护订单计数表
    7大事务导致锁竞争事务锁时间长,阻塞其他请求批量数据操作事务过大,锁持有时间长拆分事务,缩短事务时间分批处理数据,每批1000条提交
    8联合索引顺序不合理查询索引失效,性能下降联合索引最左前缀原则不满足调整联合索引字段顺序,或补充索引CREATE INDEX idx_name_age ON user(name, age);
    9小查询过多导致压力大频繁连接数据库,连接数高每条业务逻辑小查询导致数据库压力大批量查询,或使用缓存优化批量查询示例:SELECT * FROM orders WHERE id IN (...);
    10锁争用严重高并发时大量锁等待,响应慢热点记录被频繁修改导致锁竞争使用乐观锁,减少锁持有时间乐观锁示例:版本号或时间戳字段控制更新


    详细示例讲解

    示例1:函数包裹字段导致索引失效

    -- 慢查询示例
    SELECT * FROM orders WHERE DATE(create_time) = '2025-06-09';
    
    -- 优化:改成范围查询,索引生效
    SELECT * FROM orders 
    WHERE create_time >= '2025-06-09 00:00:00' 
    AND create_time <= '2025-06-09 23:59:59';
    

    解释:
    使用DATE()函数导致MySQL无法利用索引,只能全表扫描。改为范围查询,让索引起作用。


    示例2:回表次数多,设计覆盖索引

    -- 查询email对应用户的name和age字段
    SELECT name, age FROM user WHERE email = 'test@example.com';
    
    -- email字段有索引,但name、age没有,导致查询先查email索引,再回主表查name和age,性能差
    
    -- 优化:设计覆盖索引,包含所有查询字段
    CREATE INDEX idx_email_name_age ON user(email, name, age);
    
    -- 这时查询只需索引,避免回表
    

    示例3:深分页慢,改用游标分页

    
    
    -- 慢查询,深度分页
    SELECT * FROM user ORDER BY id DESC LIMIT 10000, 10;
    
    -- 优化,游标分页,利用上次查询最大ID
    SELECT * FROM user WHERE id < 123456 ORDER BY id DESC LIMIT 10;
    


    示例4:排序用filesort,建立索引优化

    
    
    -- 无索引排序,执行文件排序,慢
    SELECT * FROM orders ORDER BY create_time DESC LIMIT 20;
    
    -- 优化,建立支持排序的索引
    CREATE INDEX idx_create_time ON orders(create_time DESC);
    
    -- 查询快速,避免filesort
    

    示例5:大事务拆分

    -- 慢事务示例
    BEGIN;
    UPDATE orders SET status = 'processed' WHERE id IN (...10000条...);
    COMMIT;
    
    -- 优化:分批处理,每次处理1000条
    BEGIN;
    UPDATE orders SET status = 'processed' WHERE id IN (...1000条...);
    COMMIT;
    -- 循环执行多次
    

    ✅ 面试官常见反问拆解(+应答建议)

    ❓ 1. 为什么组合索引要遵守“最左前缀原则”?

    ✅ 回答:

    MySQL 的 B+ 树索引是有序的多级结构,组合索引会按照第一个字段排序。只有查询条件从左往右连续命中,索引才能被使用。
    比如索引 (a, b, c),如果只查 WHERE b=1,不能利用索引;但 WHERE a=1a=1 AND b=2 可以。


    ❓ 2. 为什么不能在索引字段上使用函数?会导致什么?

    ✅ 回答:

    索引是基于原始字段值构建的,函数改变了字段的值,导致无法用原始索引匹配。例如:
    WHERE DATE(create_time) = '2024-06-01' 无法使用 create_time 上的索引。
    正确写法应为:
    WHERE create_time >= '2024-06-01' AND create_time < '2024-06-02'


    ❓ 3. 字段如果是 null,会不会走索引?为什么你说“避免 null 值”?

    ✅ 回答:

    MySQL B+ 树中可以存 null 值,但在实际查询中,如果用 IS NULL、IS NOT NULL 做判断,可能不走索引(取决于版本和统计信息)。
    而且在组合索引中,一旦最左字段为 null,会影响后续字段使用。
    所以生产中我们通常避免将索引字段设计成 null。


    ❓ 4. 你说“不能使用 like 前模糊”,那用 like '%abc' 会怎样?为什么不走索引?

    ✅ 回答:

    前模糊 %abc 会让 MySQL 无法定位索引的起点,只能全表扫描。
    索引是按顺序查找的,必须能从前缀部分匹配才行。
    like 'abc%' 是可以走索引的。


    ❓ 5. 你说“覆盖索引更快”,为什么?什么是回表?

    ✅ 回答:

    覆盖索引指的是:查询字段正好都在索引里,不需要回主表查数据
    回表是:先通过索引找到主键,然后去主表根据主键再查一次数据,成本更高。
    所以合理设计索引列顺序,可以最大程度提升查询性能。


    ❓ 6. explain 里面出现了 type=ALL,意味着什么?

    ✅ 回答:

    type=ALL 表示全表扫描,一般说明没有命中索引,是最差的情况。
    优化目标应该是 type=ref、range、const 等。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值