MySQL 优化思路从表设计到 SQL 编写一篇讲透面试官“谈谈你对 MySQL 优化的理解。”你“可以从表设计、索引、SQL 编写、事务控制几个方面入手字段类型合理、避免冗余索引、不用 select *、避免前置模糊查询、大分页用延迟关联、少用 join、控制事务大小。”面试官“那你能具体说说为什么大分页要用延迟关联还有哪些常见坑”很多人能列出几点但一问到“为什么”就卡壳了。本文从实战角度把 MySQL 优化的核心思路讲清楚并给出可落地的建议。一、表设计优化表结构是 SQL 执行的根基设计不合理的表后期优化事倍功半。1. 选择合适的数据类型越小越好能使用TINYINT不用INT能使用VARCHAR(20)不用VARCHAR(255)。更小的数据类型占用更少的磁盘、内存CPU 处理也更快。使用内置类型存储日期用DATE/DATETIME/TIMESTAMP而不是字符串。IP 地址用INET_ATON转为整数存储。避免 NULL索引列允许 NULL 会让索引更复杂BTree 需特殊标记且NULL比较效率低。建议字段设置NOT NULL DEFAULT ...。2. 合理范式与反范式三范式消除数据冗余避免更新异常但查询可能需要多表 join。适当冗余对于高频查询的字段可以冗余到主表减少关联。例如订单表冗余商品名称虽然更新商品名时需要同步但查询性能大大提升。3. 分表策略垂直分表将不常用的大字段如TEXT、BLOB拆分到扩展表减少主表的行宽度提高缓存命中率。水平分表数据量极大千万级以上时按时间、ID 范围或哈希拆分到多个物理表配合中间件或应用层路由。二、索引优化索引是查询加速的核心武器但滥用索引会拖慢写入。1. 合理建索引避免冗余选择性高的列优先索引列的区分度COUNT(DISTINCT col)/COUNT(*)越高越好如主键、唯一ID。性别等低区分度列建索引意义不大。复合索引遵循最左前缀根据查询条件设计索引顺序等值查询列在前范围查询列在后。避免冗余索引已有(a,b)索引再建(a)就是冗余的。MySQL 5.6 可借助sys.schema_redundant_indexes查询冗余索引。2. 覆盖索引对于高频查询尽量让索引包含查询需要的所有列避免回表。例如-- 查询只需要 name 和 age索引 (name, age) 就是覆盖索引SELECTname,ageFROMuserWHEREname张三;3. 索引失效场景回顾函数操作WHERE UPPER(name) ZHANG隐式类型转换WHERE phone 13800138000phone 是 varchar前置模糊查询LIKE %abc使用!或IS NULL大多数情况复合索引跳过最左列三、SQL 编写优化1. 避免SELECT *只返回需要的列减少网络传输和内存消耗。更容易形成覆盖索引避免回表。表结构变更时*可能带来意外列。坏习惯SELECT*FROMuserWHEREid1;推荐SELECTid,name,ageFROMuserWHEREid1;2. 避免LIKE %xxx%前置模糊查询前置%会让 BTree 索引失效只能全表扫描。如果需要模糊查询可考虑使用LIKE xxx%后置匹配索引有效。使用全文索引FULLTEXT配合MATCH AGAINST。如果业务允许使用 Elasticsearch 等搜索引擎。3. 大分页用延迟关联传统的LIMIT offset, limit在 offset 很大时性能极差因为 MySQL 需要先扫描 offsetlimit 行然后丢弃前 offset 行。例如-- 偏移量 100000取出 10 条实际扫描 100010 行SELECT*FROMuserORDERBYidLIMIT100000,10;延迟关联优化先通过覆盖索引查出主键只扫描少量数据再回表取完整行。SELECT*FROMuserINNERJOIN(SELECTidFROMuserORDERBYidLIMIT100000,10)AStmpUSING(id);或者记住上一次查询的最大 id适用于递增且无间隙的场景SELECT*FROMuserWHEREid100000ORDERBYidLIMIT10;4. 少用 JOIN控制关联表数量多表 JOIN 会让优化器选择难度增加且可能产生临时表或文件排序。一般建议关联表不超过 3 张如果超过可考虑在应用层多次查询组装。确保关联条件使用索引且尽量让驱动表是小结果集。5. 使用EXPLAIN分析执行计划关键字段typeALL全表扫描-index全索引扫描-range-ref-eq_ref-const好到差possible_keys可能使用的索引key实际使用的索引rows预估扫描行数ExtraUsing filesort需优化、Using temporary需优化、Using index覆盖索引好四、事务优化1. 避免长事务长事务会持有很多锁阻塞其他事务。导致 undo log 版本链过长影响查询性能甚至撑爆磁盘。增加死锁风险。解决尽量将事务控制在单一操作或少量操作内。不要将用户交互放在事务中如请求外系统。编程式事务明确边界及时提交。2. 合理选择隔离级别默认 RR 级别在大部分场景可用但间隙锁可能带来死锁。如果业务允许不可重复读可降级为 RC减少锁冲突。对于只读事务显式设置SET TRANSACTION READ ONLY优化器会优化。五、其他优化思路1. 优化COUNT(*)COUNT(*)在 InnoDB 中需要扫描索引或表如果业务允许可使用information_schema中的近似值。对于条件计数确保过滤列有索引。2. 优化ORDER BY/GROUP BY让排序字段使用索引顺序避免Using filesort。GROUP BY默认会排序如果不需要排序用ORDER BY NULL取消。3. 使用UNION ALL代替UNIONUNION会去重产生临时表性能差UNION ALL不删除重复行效率高。4. 批量操作批量插入、更新、删除比循环单条执行效率高得多。例如INSERT INTO t VALUES (1),(2),(3)...减少日志刷盘次数。5. 配置优化调整innodb_buffer_pool_size通常设置为物理内存的 50%~80%。设置query_cache_type0MySQL 8.0 已移除查询缓存。合理设置max_connections、thread_cache_size等。六、常见误区与排查思路误区正确理解索引越多越好索引会降低写入性能占用空间需要维护。LIMIT后跟大偏移量没问题偏移量越大扫描行数越多性能极差。IN子句总是用索引如果IN列表值过多优化器可能选择全表扫描。EXISTS总是比IN好取决于数据分布现代优化器两者区别不大。排查步骤开启慢查询日志定位问题 SQL。用EXPLAIN分析执行计划。检查索引是否合理是否被使用。观察数据库状态SHOW PROCESSLIST、SHOW ENGINE INNODB STATUS。考虑业务逻辑是否可以改写 SQL 或引入缓存。七、总结优化维度核心原则表设计字段类型合适避免 NULL范式与冗余平衡索引选择性高、覆盖索引、避免冗余SQL 编写不用*不用前置%大分页延迟关联少 JOIN事务短小、合理隔离级别其他批量操作、配置调优一句话记住 MySQL 优化表设计要合理索引覆盖防回表大分页用延迟事务短小性能高。MySQL 优化是一门实践科学需要结合具体业务和数据量反复验证。希望这篇文章能帮你建立系统化的优化思路在面试和工作中游刃有余欢迎继续讨论。