Lazy loaded image
技术知识
📎SQL 慢查询优化
字数 3242阅读时长≈ 9 分钟
2025-5-18
2025-6-9
type
Post
status
Published
date
Jun 9, 2025 01:04 PM
slug
summary
MySQL InnoDB 中高速查询原理，慢查询的发现、分析和优化。
tags
数据库
category
技术知识
icon
password
本文仅讨论 MySQL-InnoDB 的情况。

查询原理

MySQL 高性能查询的核心策略：增量查询和索引加速。 前者指只要收集到了足够的结果，就立刻返回，避免全量计算，减少工作量。后者在数据存储时做预先的排序，在查询时利用有序性快速定位到所需的行，最大发挥增量查询的优势。

评估成本

执行SQL前，优化器会先评估可能使用的索引，然后比较估算得到的全表扫描成本和不同索引成本，最后选择它认为查询成本最低的方案。 查询成本一般由IO成本和CPU成本组成，IO往往占查询成本的大头。 计算口径如下：
MySQL的IO成本默认基于随机IO计算（io_block_read_cost=1.0），而非顺序IO。这里因为全表扫描是顺序IO，io_block_read_cost的默认值则为0.25。表记录越多，占用的页数则随着增长，其查询成本也就不断累加。

基本操作

操作
说明
慢查询信号
ORDER BY
如果对应列上没有索引，就必须把整个表都加载进入内存进行排序，MySQL 可以通过索引直接按顺序读取行。
Using filesort
JOIN
如果内层表 JOIN 列上没有索引，对于外层表的每一行数据内层表都需要做全表扫描，反之则利用索引快速定位。
Using join buffer（hash join、block nested join）<br>
GROUP BY
MySQL 采用排序方案实现。<br>新建临时表，扫描原始数据表，按照顺序插入临时表，对应列相同的值就会被排在一起，然后在临时表上做处理。
Using Temporary
DISTINCT
简单的 GROUP BY
ㅤ
max、min
有索引直接获得，没有索引全表扫描
ㅤ
avg、sum、count
无论是否有索引均全表扫描，有索引则扫描索引，非聚簇索引列比起完整数据行通常要小一些，io 压力更小，速度稍微提升
ㅤ

发现分析

分析

  1. MySQL 原生分析 explain format=tree statement
  1. Arthas 跟踪语句 生产环境中启动 Arthas，使用 trace 命令追踪查询相关方法

发现

方法
描述
优缺点
慢查询日志文件
通过 Mysql 原生的慢查询日志功能，记录执行时间超过阈值的语句到服务器本地文件中。<br>可以选择 Filebeats 等日志文件收集工具读取文件处理，也可以定期人工分析日志。
无法实时监控、日志文件需要定期清理<br>
数据库代理
ShardingProxy、ProxySQL 等数据库代理作为中间件，拦截所有数据库请求并统计性能。
维护成本高
数据库连接池实时监控
阿里 Druid 连接池提供自动和可配置的 sql 监控功能
ㅤ
MyBaitis 拦截器实时监控
自定义拦截器实现 MyBaitis 提供的 Intercepter 接口拦截 Executor 的 query、update 等方法，判断语句并将语句信息写入应用日志。
ㅤ
Spring AOP 手动拦截
定义切点拦截 dao 接口方法，即原子化的查询方法，并通过 @Around 注解记录执行时间。
ㅤ
Skywalking Agent + SkyWalking
通过 Java Agent 技术对 JDBC 驱动或者框架进行字节码插桩自动采集 SQL 性能数据。
维护成本高

优化

影响 SQL 执行效率的主要因素：
  • 数据量大小
  • 数据获取方式
  • 数据加工方式 排序、子查询、聚合和关联等操作一般需要把数据加载到临时表中再对数据进行加工

优化思路

  • 减少数据扫描（减少磁盘访问）
    • 在查询中加入一些可以提前过滤数据的谓词条件，比如按照时间过滤数据等，可以减少数据的扫描量，对查询更友好。
    • 在扫描大表数据时命中索引，减少回表代价，避免全表扫描。
  • 返回更少数据（减少网络传输或者磁盘访问）
    • SQL 执行后返回给客户端的数据量大小
  • 减少交互次数（减少网络传输）
    • 将数据存放在更快的地方，某条查询涉及到大表，无法进一步优化，如果返回的数据量不大且变化频率不高但是访问频率很高，此时应该考虑将返回数据存放在应用层缓存或者分布式缓存中，以提高存取速率。
  • 减少服务器 CPU 开销
  • 避免大事务操作
  • 利用更多资源

优化案例

数据分页优化

传统数据分页方式，当分页深度较大时，尽管不需要某些数据，仍然需要承受查询此前所有数据页的代价，导致磁盘io次数极高。
偏移 id 优化，==核心思想是记录上一次查询的最后一个 id==，将分页查询转换为基于 id 的范围查询，避免大范围扫描，减少磁盘 io 次数。
分段查询优化，可并行查询，每个分段查询互不依赖，但是比较依赖数据连续，如果数据过于分散，代价较高。

Group By 优化

提高 GROUP BY 语句的效率, 可以==通过将不需要的记录在 GROUP BY 之前过滤掉==. 下面两个查询返回相同结果但第二个明显就快了许多。

范围查询

Order By 优化

索引如下： KEY idx_account_trade_date_time (account_numbertrade_date_time),
KEY idx_trade_date_times (trade_date_time)
KEY idx_createtime (create_time) 慢查询：
优化后：
==MySQL 使不使用索引与所查列无关，只与索引本身，where条件，order by 字段，group by 字段有关。==索引的作用一个是查找，一个是排序。

业务拆分

随着业务数据的增长 status='S'的数据基本占据数据的 90%以上，此时该条件无法走索引。 可以结合业务特征，对数据获取按日期进行拆分。 避免在索引选择度不高的情况下进行全表扫描。

优化方法

数据库层面

  • 原生优化
    • Multi Range Read 多返回查询
    • 索引下推
  • 数据库设计
    • 数据库范式 表结构设计符合范式或者反范式，目的分别是消除冗余节省空间和增加冗余减少 join 操作
    • 控制单行数据大小

索引层面

建立索引

什么时候创建索引
  • 频繁出现在 where、group by、order by 语句中的字段（distinct）
  • 经常作为 update 和 delete 条件的字段
  • 多表连接字段建议创建索引 对什么字段创建索引
  • 字段有唯一性限制
  • 字段区分度高
  • 多字段优先使用联合索引，使用最频繁列作为索引最左侧
  • 长字符串字段建议建立前缀索引，但需要保证前缀唯一性

索引生效

  • 索引规则 最左匹配
  • 数据分布 SELECT COUNT(DISTINCT column_name ) / COUNT(*) 区分度低于10%的字段避免单独建索引。 对于联合索引而言，也应尽量将区分度高的字段放在前面。*
  • 内存碎片 SHOW TABLE STATUS LIKE 'xxx_price' 上文提到了成本估算是基于页数以及记录数计算的，这些数据来源于库中的统计信息。当内存碎片过多时，如果出现库表的统计信息未及时更新，也会因为优化器评估的结果与实际差距太大，从而影响实际执行效果。 当内存碎片过多时，首当其冲会让物理IO被放大。原本“id=1，id=10，id=15”这三条记录读一次数据页就能够拿到，现在由于这几条数据被分散在多个数据页中，从而引发IO次数增多。同时，数据页是加载到缓冲池（Buffer Pool）里面的，这也会导致缓存命中率下降。 ==一般情况下，有一定的内存碎片是正常情况。但当内存碎片的占比过高时，则需要关注，可能是因为频繁删除数据==。
[!NOTE] 为什么频繁删除会出现内存碎片问题？ 执行 Delete 时，InnoDB 仅标记记录为已删除，物理空间不立即释放（为 MVCC 和回滚保留可能），短期内产生数据空洞 频繁删除导致数据页内出现大量空洞，页实际利用率下降，读取相同数据量需要访问更多页，而且 InnoDB 仅合并相邻空闲页，分散页则无法合并
  • 实例资源开销 如果SQL没有问题，那么关注点可以放在mysql实例的资源开销上了。因为造成慢查询的原因不单只是SQL本身，有可能是磁盘负载，CPU以及网络 等方面的资源不足引起了。 举个例子：某统计服务数据库，其库表大部分数据源自大数据平台的异步交换任务。某个时间段有多个交换任务往库表里面导入大批量数据，从而引发了磁盘等资源的负载增加，带来慢查询。 另外有时候事务的问题也需要关注。比如当长事务导致Undo Log膨胀时，容易使得扫描效率降低。同时Buffer Pool中缓存页因旧版本数据过多，其缓存命中率也会下降。我们可以通过 SHOW ENGINE INNODB STATUS中History list length值是否飙升，加以判断。

语句层面

  1. 尽量避免使用子查询 引用了子查询的条件不能下推
  1. 用 IN 来替换 OR in 操作在某些情况下比 or 更高效，尤其是在有索引的列上； in 可以一次性匹配多个值
  1. 读取适当的记录 LIMIT M,N，而不要读多余的记录 参考分页查询优化
  1. 总和查询可以禁止排重用 union all union 会自动去重，而 union all 不去重，性能更高
    1. 避免随机取记录
    1. 将多次插入换成批量 Insert 插入 批量插入减少网络往返和事务提交次数，降低数据库日志写入和锁竞争的开销
    1. 只返回必要的列，用具体的字段列表代替 select * 语句
    1. 区分 in 和 exists in 适合小数据集，且子查询结果集较小时，exists 适合大数据集，其在找到第一个匹配项后立即停止搜索
    1. 优化 Group By 语句 为分组字段添加索引，避免使用非索引字段，提前过滤数据
    1. 优化 Join 语句 避免多表连接，连接表数量最后不要超过三张，每增加一张表就相当于增加一次嵌套循环，优先使用内连接，避免不必要的外连接

    架构层面

    • 分库分表
    • 读写分离
    • 大表归档
    上述信息主要来源于： "慢SQL"治理的几点思考
    慢 SQL 分析与优化 加入个人的知识汇总得到
    上一篇
    JUC
    下一篇
    Spring AI
    目录