常见SQL优化手段总结

SQL 优化不要只背规则。真正排查问题时，应该先定位慢 SQL，再看执行计划，最后结合业务访问路径决定是改索引、改 SQL、改表结构，还是把查询挪到缓存、搜索引擎或离线报表里。 一个比较稳的排查顺序： 1. 先确认现象 ：慢的是平均耗时、P99，还是偶发超时？是单条 SQL 慢，还是数据库整体负载高？ 2. 再定位 SQL ：通过慢查询日志、APM、数据库监控找出高频慢 SQL。 3. 分析执行计划 ：重点看 、 、 、 、 ，确认是否全表扫描、回表过多、临时表或文件排序。 4. 结合业务优化 ：减少查询字段、补合适索引、改写 SQL、拆分大事务、限制分页深度。 5. 验证效果 ：用相同数据量和相同条件复测，避免只在小数据集上验证。 避免使用 SELECT \ 会消耗更多的 CPU。 无用字段增加网络带宽资源消耗，增加数据传输时间，尤其是大字段（如 varchar、blob、text）。 无法使用 MySQL 优化器覆盖索引的优化（基于 MySQL 优化器的“覆盖索引”策略又是速度极快，效率极高，业界极为推荐的查询优化方式） 可减少表结构变更带来的影响。 尽量避免多表做 join 阿里巴巴《Java 开发手册》中有这样一段描述： 【强制】超过三个表禁止 join。需要 join 的字段，数据类型保持绝对一致;多表关联查询时，保证被关联 的字段需要有索引。 join 的效率比较低，主要原因是因为其使用嵌套循环（Nested Loop）来实现关联查询，以前常见的实现效率都不是很高： Simple Nested Loop Join ：直接使用笛卡尔积实现 join，逐行遍历/全表扫描，效率最低。 Block Nested Loop Join (BNL) ：利用 JOIN BUFFER 进行优化。 注意：在 MySQL 8.0.20 及更高版本中，BNL 已被 Hash Join 取代 ，Hash Join 通常能将非索引列关联的复杂度从 O(M\ N) 降低到接近 O(M+N)。 Index Nested Loop Join ：在必要的字段上增加索引，性能得到进一步提升。 实际业务场景避免多表 join 常见的做法有两种： 1. 单表查询后在内存中自己做关联 ：对数据库做单表查询，再根据查询结果进行二次查询，以此类推，最后再进行关联。 2. 数据冗余 ，把一些重要的数据在表中做冗余，尽可能地避免关联查询。很笨的一种做法，表结构比较稳定的情况下才会考虑这种做法。进行冗余设计之前，思考一下自己的表结构设计的是否有问题。 更加推荐第一种，这种在实际项目中的使用率比较高，除了性能不错之外，还有如下优势： 1. 拆分后的单表查询代码可复用性更高 ：join 联表 SQL 基本不太可能被复用。 2. 单表查询更利于后续的维护 ：不论是后续修改表结构还是进行分库分表，单表查询维护起来都更容易。 不过，如果系统要求的并发量不大的话，我觉得多表 join 也是没问题的。很多公司内部复杂的系统，要求的并发量不高，很多数据必须 join 5 张以上的表才能查出来。 深度分页优化 深度分页问题的根本原因在于：当 的偏移量过大时，MySQL 需要扫描并跳过大量记录才能获取目标数据，查询优化器可能放弃索引而选择全表扫描。此时即使有索引，也无法避免大量的回表操作，导致查询性能急剧下降。 本文介绍了四种常见的深度分页优化方案，各方案的特点及适用场景对比如下： | 优化方案 | 核心思路 | 适用场景 | 限制 | | | | | | | 范围查询 | 记录上一页最后一条 ID，通过 获取下一页 | 按 ID 排序、允许游标式翻页 | 不支持跳页、非 ID 排序需使用联合游标 | | 子查询 | 先通过子查询获取起始主键，再根据主键过滤 | 需要支持传统 OFFSET 翻页 | 子查询可能产生临时表、依赖排序字段的索引 | | 延迟关联 | 用 将分页转移到主键索引，减少回表 | 大数据量分页、需要传统翻页逻辑 | SQL 相对复杂 | | 覆盖索引 | 建立包含查询字段的联合索引，避免回表 | 查询字段固定、可建立合适索引 | 字段较多时索引维护成本高、大结果集可能走全表扫描 | 方案选择建议 ： 优先使用延迟关联 ：对于大多数需要支持传统 翻页逻辑的场景，延迟关联是性能和可维护性较好的选择。 考虑范围查询（游标分页） ：如果业务允许使用“下一页”式的游标翻页（如社交媒体 feed 流、无限滚动），范围查询性能最佳且稳定。 覆盖索引作为补充 ：当查询字段固定且数量不多时，可配合其他方案建立覆盖索引进一步优化。 注意事项 ： 无论采用哪种方案，都应注意监控实际执行计划（ ），确保优化器按预期使用索引。 对于超深分页（如百万级偏移量），应从业务层面评估是否真的需要支持，考虑限制最大翻页数或采用其他检索方式（如搜索引擎）。 详细介绍可以阅读这篇文章：深度分页介绍及优化建议。 建议不要使用外键与级联 阿里巴巴《Java 开发手册》中有这样一段描述： 不得使用外键与级联，一切外键概念必须在应用层解决。 网络上已经有非常多分析外键与级联缺陷的文章了，个人认为不建议使用外键主要是因为对分库分表不友好，性能方面的影响其实是比较小的。 选择合适的字段类型 存储字节越小，占用也就空间越小，性能也越好。 a.某些字符串可以转换成数字类型存储比如可以将 IP 地址转换成整型数据。 数字是连续的，性能更好，占用空间也更小。 MySQL 提供了两个方法来处理 ip 地址 ： 把 IPv4 转为无符号整型（4 字节，32 位）。对于 IPv6，可使用 转为 16 字节（128 位）的二进制字符串。 :把整型的 ip 转为地址 插入数据前，先用 把 ip 地址转为整型，显示数据时，使用 把整型的 ip 地址转为地址显示即可。 b.对于非负型的数据 (如自增 ID,整型 IP，年龄) 来说,要优先使用无符号整型来存储。 无符号不会改变整数类型占用的存储字节数，只是把可表示范围从包含负数改为从 0 开始的非负范围；例如 无论 signed 还是 unsigned 都占用 4 字节，范围分别如下： 具体存储字节数和取值范围可参考 MySQL 官方文档：Integer Types 和 Data Type Storage Requirements。 c.小数值类型（比如年龄、状态表示如 0/1）优先使用 TINYINT 类型。 d.对于日期类型来说， 一定不要用字符串存储日期。可以考虑 DATETIME、TIMESTAMP 和 数值型时间戳。 这三种种方式都有各自的优势，根据实际场景选择最合适的才是王道。下面再对这三种方式做一个简单的对比，以供大家实际开发中选择正确的存放时间的数据类型： 注意 ：以下存储空间基于 MySQL 5.6.4+（支持微秒精度）。5.6.4 之前，DATETIME 固定 8 字节，TIMESTAMP 固定 4 字节。小数秒精度每增加 1 位，额外占用 1 字节（最多 5 字节）。 | 类型 | 存储空间 | 日期格式 | 日期范围 | 是否带时区信息 | | | | | | | | DATETIME | 5 8 字节 | YYYY MM DD hh:mm:ss[.fraction] | 1000 01 01 00:00:00[.000000] ～ 9999 12 31 23:59:59[.999999] | 否 | | TIMESTAMP | 4 7 字节 | YYYY MM DD hh:mm:ss[.fraction] | 1970 01 01 00:00:01[.000000] ～ 2038 01 19 03:14:07[.999999] | 是 | | 数值型时间戳 | 4 字节 | 全数字如 1578707612 | 1970 01 01 00:00:01 之后的时间 | 否 | MySQL 时间类型选择的详细介绍请看这篇：MySQL 时间类型数据存储建议。 e.金额字段用 decimal，避免精度丢失。 decimal 用于存储有精度要求的小数比如与金钱相关的数据，可以避免浮点数带来的精度损失。 在 Java 中，MySQL 的 decimal 类型对应的是 Java 类 。 的详细介绍请参考这篇：BigDecimal 详解。 f.尽量使用自增 id 作为主键。 如果主键为自增 id 的话，新数据会追加到 B+ 树的尾部，避免了中间位置的页分裂，性能相对最优。在写满一个数据页的时候，直接申请另一个新数据页接着写就可以了。 如果主键是非自增 id 的话，为了让新加入数据后 B+ 树的叶子节点还能保持有序，它就需要往叶子结点的中间找位置插入。如果目标页已满，就需要进行 页分裂 ——将页一分为二，移动一半数据到新页。页分裂操作需要加悲观锁，涉及大量数据移动，性能较差。 不过， 像分库分表这类场景就不建议使用自增 id 作为主键，应该使用分布式 ID 比如 uuid 。 相关阅读：数据库主键一定要自增吗？有哪些场景不建议自增？。 g.不建议使用 作为列默认值。 跟 (空字符串)是两个完全不一样的值，区别如下： 代表一个不确定的值,就算是两个 ,它俩也不一定相等。例如， 的结果为 false，但是在我们使用 , , 时, 又被认为是相等的。 的长度是 0，是不占用空间的，而 是需要占用空间的。 会影响聚合函数的结果。例如， 、 、 、 等聚合函数会忽略 值。 的处理方式取决于参数的类型。如果参数是 ( )，则会统计所有的记录数，包括 值；如果参数是某个字段名( )，则会忽略 值，只统计非空值的个数。 查询 值时，必须使用 或 来判断，而不能使用 =、!=、 <、 之类的比较运算符。而 是可以使用这些比较运算符的。 尽量用 UNION ALL 代替 UNION UNION 会把两个结果集的所有数据放到临时表中后再进行去重操作，更耗时，更消耗 CPU 资源。 UNION ALL 不会再对结果集进行去重操作，获取到的数据包含重复的项。 不过，如果实际业务场景中不允许产生重复数据的话，还是可以使用 UNION。 优先使用批量操作 对于数据库中的数据更新，如果能使用批量操作就要尽量使用，减少请求数据库的次数，提高性能。 Show Profile 分析 SQL 执行性能 为了更精准定位一条 SQL 语句的性能问题，需要清楚地知道这条 SQL 语句运行时消耗了多少系统资源。 和 展示 SQL 语句的资源使用情况，展示的消息包括 CPU 的使用，CPU 上下文切换，IO 等待，内存使用等。 MySQL 在 5.0.37 版本之后才支持 Profiling， 命令返回 表示该功能可以使用。 注意 ： 和 已经被弃用，未来的 MySQL 版本中可能会被删除，取而代之的是使用 Performance Schema。在该功能被删除之前，我们简单介绍一下其基本使用方法。 推荐替代方案 ：MySQL 5.7+ 推荐使用 Performance Schema 的 表： 此外，MySQL 8.0.18+ 还支持 ，可以直接输出 SQL 的实际执行时间和行数统计。 想要使用 Profiling，请确保你的 是开启（on）的状态。 你可以通过 命令查看其状态： 也可以通过 命令进行查看： 默认情况下， 是关闭（off）的状态，你直接通过 命令即可开启。 开启成功之后，我们执行几条 SQL 语句。执行完成之后，使用 可以展示当前 Session 下所有 SQL 语句的简要的信息包括 Query\ ID（SQL 语句的 ID 编号） 和 Duration（耗时）。 具体能收集多少个 SQL，由参数 决定，默认值为 15，最大值为 100。如果设置为 0，等同于关闭 Profiling。 如果想要展示一个 SQL 语句的执行耗时细节，可以使用 命令。 命令的具体用法如下： 在执行 命令时，可以加上类型子句，比如 CPU、IPC、MEMORY 等，查看具体某类资源的消耗情况： 如果不加 子句，默认展示最新的一次 SQL 的执行情况，加了 ，表示展示 Query\ ID 为 n 的 SQL 的执行情况。 优化慢 SQL 为了优化慢 SQL ，我们首先要找到哪些 SQL 语句执行速度比较慢。 MySQL 慢查询日志是用来记录 MySQL 在执行命令中，响应时间超过预设阈值的 SQL 语句。因此，通过分析慢查询日志我们就可以找出执行速度比较慢的 SQL 语句。 出于性能层面的考虑，慢查询日志功能默认是关闭的，你可以通过以下命令开启： 设置成功之后，使用 命令进行查看。 我们故意在百万数据量的表(未使用索引)中执行一条排序的语句： 确保自己有对应目录的访问权限： 查看对应的慢查询日志： 我们刚刚故意执行的 SQL 语句已经被慢查询日志记录了下来： 这里对日志中的一些信息进行说明： ：被日志记录的代码在服务器上的运行时间。 ：谁执行的这段代码。 ：这段代码运行时长。 ：执行这段代码时，锁定了多久。 ：慢查询返回的记录。 ：慢查询扫描过的行数。 实际项目中，慢查询日志通常会比较复杂，我们需要借助一些工具对其进行分析。像 MySQL 内置的 工具就可以把相同的 SQL 归为一类，并统计出归类项的执行次数和每次执行的耗时等一系列对应的情况。 慢 SQL 治理时建议同时关注这几个指标： | 指标 | 说明 | 常见问题 | | | | | | | SQL 总耗时 | 执行慢、等待锁、I/O 慢 | | | 等锁耗时 | 行锁冲突、表锁、DDL 影响 | | | 扫描行数 | 索引缺失、索引选择性差 | | | 返回行数 | 查询范围过大、分页过深 | | 执行频次 | 单位时间执行次数 | 单次不慢但总量压垮数据库 | 不要只盯着耗时最长的 SQL。线上最危险的往往是“单次 50 ms，但每秒执行几千次”的高频 SQL。 找到了慢 SQL 之后，我们可以通过 命令分析对应的 语句： 比较重要的字段说明： ：查询的类型，常用的取值有 SIMPLE（普通查询，即没有联合查询、子查询）、PRIMARY（主查询）、UNION（UNION 中后面的查询）、SUBQUERY（子查询）等。 ：表示查询涉及的表或衍生表。 ：执行方式，判断查询是否高效的重要参考指标，结果值从差到好依次是： ALL （全表扫描）< index （索引全扫描）< range （索引范围扫描）< index\ merge （索引合并）< ref （非唯一索引查找）< eq\ ref （唯一索引查找）< const （单行常量）< system （系统表）。实际性能还需结合 rows、Extra 等字段综合判断。 : SQL 要查找到结果集需要扫描读取的数据行数，原则上 rows 越少越好。 …… 推荐阅读 ：MySQL 执行计划分析 详细介绍了 EXPLAIN 各列的含义（id、select\ type、type、key、rows、Extra 等），包括 MySQL 8.0.18+ 新增的 实际执行分析功能。另外，阿里的 慢 SQL 治理经验总结 也总结得不错。 正确使用索引 正确使用索引可以大大加快数据的检索速度（大大减少检索的数据量）。 选择合适的字段创建索引 不为 NULL 的字段 ：索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用 0,1,true,false 这样语义较为清晰的短值或短字符作为替代。 被频繁查询的字段 ：我们创建索引的字段应该是查询操作非常频繁的字段。 被作为条件查询的字段 ：被作为 WHERE 条件查询的字段，应该被考虑建立索引。 频繁需要排序的字段 ：索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。 被经常频繁用于连接的字段 ：经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。 避免索引失效 索引失效也是慢查询的主要原因之一，常见的导致索引失效的情况有下面这两类： 1. SQL 写法与底层逻辑冲突（破坏 B+Tree 有序性） 此类问题最为常见，本质是查询条件让底层的 B+Tree 失去了“二分查找”的快速定位能力。 违背最左前缀原则 ：跳过联合索引前导列，或遇到范围查询（如 、 、 、 ）导致后续列中断精确定位，降级为范围扫描加过滤。 对索引列进行加工 ：在 左侧对索引列进行数学计算或应用函数，导致原始数据发生逻辑改变，在索引树中呈现无序状态。 隐式类型转换（隐蔽且致命） ：当“字符串类型的列”去比较“数字类型的值”时，MySQL 会默认在列上套用转换函数，直接破坏树的有序性。 LIKE 模糊查询前置通配符 ：如 ，前缀字符的不确定性使得优化器无法锁定扫描区间的起始点。 ORDER BY 排序陷阱 ：排序列未命中索引、排序方向与索引结构不一致等触发额外的内存或磁盘排序（ ）。 2. 优化器的成本决策（基于 I/O 成本妥协） 此类问题并非索引本身不可用，而是 MySQL 优化器经过计算后，认为“不走普通索引”整体开销反而更小。 无脑 导致回表成本超载 ：查询大量非索引覆盖列时，若命中数据量较大（通常超 20% 30%），优化器会判定全表扫描的顺序 I/O 优于频繁回表的随机 I/O，从而主动放弃索引。 条件导致全表扫描 ：只要 连接的任意一侧条件没有对应索引，就会触发全表扫描。即使两侧都有索引，若 Index Merge（索引合并）的预期成本过高，依然会被放弃。 列表过长引发估算失真 ：当 列表长度超过系统阈值（默认 200）时，优化器会从精准的深入探测（Index Dive）切换为粗略的统计估算，极易因统计信息陈旧而产生执行成本的误判。 详细介绍：MySQL索引失效场景总结。 被频繁更新的字段应该慎重建立索引 虽然索引能带来查询上的效率，但是维护索引的成本也是不小的。 如果一个字段不被经常查询，反而被经常修改，那么就更不应该在这种字段上建立索引了。 尽可能的考虑建立联合索引而不是单列索引 因为索引是需要占用磁盘空间的，可以简单理解为每个索引都对应着一颗 B+树。如果一个表的字段过多，索引过多，那么当这个表的数据达到一个体量后，索引占用的空间也是很多的，且修改索引时，耗费的时间也是较多的。如果是联合索引，多个字段在一个索引上，那么将会节约很大磁盘空间，且修改数据的操作效率也会提升。 注意避免冗余索引 冗余索引指的是索引的功能相同，能够命中索引(a, b)就肯定能命中索引(a) ，那么索引(a)就是冗余索引。如（name,city ）和（name ）这两个索引就是冗余索引，能够命中前者的查询肯定是能够命中后者的 在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。 考虑在字符串类型的字段上使用前缀索引代替普通索引 前缀索引仅限于字符串类型，较普通索引会占用更小的空间，所以可以考虑使用前缀索引带替普通索引。 删除长期未使用的索引 删除长期未使用的索引，不用的索引的存在会造成不必要的性能损耗 MySQL 5.7 可以通过查询 sys 库的 schema\ unused\ indexes 视图来查询哪些索引从未被使用 生产优化注意事项 不要在高峰期直接加大索引 ：大表加索引可能长时间占用资源，生产环境需要评估在线 DDL、备份、回滚和业务低峰窗口。 不要为了一个低频查询牺牲写入性能 ：索引越多，写入、更新和删除的维护成本越高。 不要忽略数据分布 ：同一条 SQL 在测试库很快，到了生产库可能因为数据倾斜、统计信息陈旧而走完全不同的执行计划。 不要只优化 SQL 文本 ：有些问题需要产品侧限制查询范围，有些需要缓存，有些需要异步报表或数仓承接。 优化后继续观察 ：上线后持续观察慢查询数量、CPU、I/O、Buffer Pool 命中率、连接数和锁等待。 参考 MySQL 8.2 Optimizing SQL Statements：<https://dev.mysql.com/doc/refman/8.0/en/statement optimization.html 为什么阿里巴巴禁止数据库中做多表 join Hollis：<https://mp.weixin.qq.com/s/GSGVFkDLz1hZ1OjGndUjZg MySQL 的 COUNT 语句，竟然都能被面试官虐的这么惨 Hollis：<https://mp.weixin.qq.com/s/IOHvtel2KLNi Ol4UBivbQ MySQL 性能优化神器 Explain 使用分析：<https://segmentfault.com/a/1190000008131735 如何使用 MySQL 慢查询日志进行性能优化 ：<https://kalacloud.com/blog/how to use mysql slow query log profiling mysqldumpslow/