您当前的位置:首页 > 电脑百科 > 数据库 > 百科

慢 SQL 分析与优化

时间:2022-08-17 13:06:27  来源:字节跳动技术团队  作者:

一、背景介绍

从系统设计角度看,一个系统从设计搭建到数据逐步增长,SQL 执行效率可能会出现劣化,为继续支撑业务发展,我们需要对慢 SQL 进行分析和优化,严峻的情况下甚至需要对整个系统进行重构。所以我们往往需要在系统设计前对业务进行充分调研、遵守系统设计规范,在系统运行时定期结合当前业务发展情况进行系统瓶颈的分析。

从数据库角度看,每个 SQL 执行都需要消耗一定 I/O 资源,SQL 执行的快慢,决定了资源被占用时间的长短。假如有一条慢 SQL 占用了 30%的资源共计 1 分钟。那么在这 1 分钟时间内,其他 SQL 能够分配的资源总量就是 70%,如此循环,当资源分配完的时候,所有新的 SQL 执行将会排队等待。所以往往一条慢 SQL 会影响到整个业务。

本文仅讨论 MySQL-InnoDB 的情况。

二、优化方式

SQL 语句执行效率的主要因素:

1)数据量

  • SQL 执行后返回给客户端的数据量的大小;
  • 数据量越大需要扫描的 I/O 次数越多,数据库服务器的 IO 更容易成为瓶颈。

2)取数据的方式

  • 数据在缓存中还是在磁盘上;
  • 是否能够通过全局索引快速寻址;
  • 是否结合谓词条件命中全局索引加速扫描。

3)数据加工的方式

  • 排序、子查询、聚合、关联等,一般需要先把数据取到临时表中,再对数据进行加工;
  • 对于数据量比较多的计算,会消耗大量计算节点的 CPU 资源,让数据加工变得更加缓慢;
  • 是否选择了合适的 join 方式。

1、优化思路

1)减少数据扫描(减少磁盘访问)

  • 尽量在查询中加入一些可以提前过滤数据的谓词条件,比如按照时间过滤数据等,可以减少数据的扫描量,对查询更友好;
  • 在扫描大表数据时是否可以命中索引,减少回表代价,避免全表扫描。

2)返回更少数据(减少网络传输或磁盘访问)

3)减少交互次数(减少网络传输)

  • 将数据存放在更快的地方;
  • 某条查询涉及到大表,无法进一步优化,如果返回的数据量不大且变化频率不高但访问频率很高,此时应该考虑将返回的数据放在应用端的缓存当中或者 redis 这样的缓存当中,以提高存取速度。

4)减少服务器 CPU 开销(减少 CPU 及内存开销)

5)避免大事务操作

6)利用更多资源(增加资源)

2、优化案例

1)数据分页优化

select * from table_demo where type = ? limit ?,?;

  • 优化方式一:偏移 id
  •  

lastId = 0 or min(id)

do {

select * from table_demo where type = ? and id >{#lastId} limit ?;

lastId = max(id)

} while (isNotEmpty)

  • 优化方式二:分段查询

该方式较方式一的优点在于可并行查询,每个分段查询互不依赖;较方式一的缺点在于较依赖数据的连续性,若数据过于分散,代价较高。

minId = min(id) maxId = max(id)

for(int i = minId; i<= maxId; i+=pageSize){

select * from table_demo where type = ? and id between i and i+ pageSize;

}

2)优化 GROUP BY

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

  • 低效:
  •  

select job , avg(sal) from table_demo group by job having job = ‘manager'

  • 高效:
  •  

select job , avg(sal) from table_demo where job = ‘manager' group by job

3)范围查询

联合索引中如果有某个列存在范围(大于小于)查询,其右边的列是否还有意义?

explAIn select count(1) from statement where org_code='1012' and trade_date_time >= '2019-05-01 00:00:00' and trade_date_time<='2020-05-01 00:00:00'

explain select * from statement where org_code='1012' and trade_date_time >= '2019-05-01 00:00:00' and trade_date_time<='2020-05-01 00:00:00' limit 0, 100

explain select * from statement where org_code='1012' and trade_date_time >= '2019-05-01 00:00:00' and trade_date_time<='2020-05-01 00:00:00'

  • 使用单键索引 trade_date_time 的情况下

a. 从索引里找到所有 trade_date_time 在'2019-05-01' 到'2020-05-01' 区间的主键 id,假设有 100 万个;

b. 对这些 id 进行排序(为的是在下面一步回表操作中优化 I/O 操作,因为很多挨得近的主键可能一次磁盘 I/O 就都取到了);

c. 回表,查出 100 万行记录,然后逐个扫描,筛选出 org_code='1020'的行记录。

  • 使用联合索引 trade_date_time, org_code -联合索引 trade_date_time, org_code 底层结构推导如下:

以查找 trade_date_time >='2019-05-01' and trade_date_time <='2020-05-01' and org_code='1020'为例:

a. 在范围查找的时候,直接找到最大,最小的值,然后进行链表遍历,故仅能用到 trade_date_time 的索引,无法使用到 org_code 索引;

b. 基于 MySQL5.6+的索引下推特性,虽然 org_code 字段无法使用到索引树,但是可以用于过滤回表的主键 id 数。

小结:对于该 case, 索引效果[org_code,trade_date_time] > [trade_date_time, org_code]>[trade_date_time]。实际业务场景中,检索条件中 trade_date_time 基本上肯定会出现,但 org_code 却不一定,故索引的设计还需要结合实际业务需求。

4)优化 Order by

  • 索引:
  •  

KEY `idx_account_trade_date_time` (`account_number`,`trade_date_time`),

KEY `idx_trade_date_times` (`trade_date_time`)

KEY `idx_createtime` (`create_time`),

  • 慢 SQL:
  •  

SELECT id,....,creator,modifier,create_time,update_time FROM statement

WHERE (account_number = 'XXX' AND create_time >= '2022-04-24 06:03:44' AND create_time <= '2022-04-24 08:03:44' AND dc_flag = 'C') ORDER BY trade_date_time DESC,id DESC LIMIT 0,1000;

  • 优化前:SQL 执行超时被 kill 了
  •  

SELECT id,....,creator,modifier,create_time,update_time FROM statement

WHERE (account_number = 'XXX' AND create_time >= '2022-04-24 06:03:44' AND create_time <= '2022-04-24 08:03:44' AND dc_flag = 'C') ORDER BY create_time DESC,id DESC LIMIT 0,1000;

  • 优化后:执行总行数为:6 行,耗时 34ms。
  •  

MySQL使不使用索引与所查列无关,只与索引本身,where条件,order by 字段,group by 字段有关。索引的作用一个是查找,一个是排序。

5)业务拆分

select * from order where status='S' and update_time < now-5min limit 500

  • 拆分优化:

随着业务数据的增长 status='S'的数据基本占据数据的 90%以上,此时该条件无法走索引。我们可以结合业务特征,对数据获取按日期进行拆分。

date = now; minDate = now - 10 days

while(date > minDate) {

select * from order where order_date={#date} and status='S' and update_time < now-5min limit 500

date = data + 1

}

3、数据库结构优化

1)范式优化:表的设计合理化(符合 3NF),比如消除冗余(节省空间);

2)反范式优化:比如适当加冗余等(减少 join)

3)拆分表:分区将数据在物理上分隔开,不同分区的数据可以制定保存在处于不同磁盘上的数据文件里。这样,当对这个表进行查询时,只需要在表分区中进行扫描,而不必进行全表扫描,明显缩短了查询时间,另外处于不同磁盘的分区也将对这个表的数据传输分散在不同的磁盘 I/O,一个精心设置的分区可以将数据传输对磁盘 I/O 竞争均匀地分散开。对数据量大的表可采取此方法,可按月建表分区。

4、SQL 语句优化

SQL 检查状态及分数计算逻辑

  • 尽量避免使用子查询
  • 用 IN 来替换 OR
  • 读取适当的记录 LIMIT M,N,而不要读多余的记录
  • 禁止不必要的 Order By 排序
  • 总和查询可以禁止排重用 union all
  • 避免随机取记录
  • 将多次插入换成批量 Insert 插入
  • 只返回必要的列,用具体的字段列表代替 select * 语句
  • 区分 in 和 exists
  • 优化 Group By 语句
  • 尽量使用数字型字段
  • 优化 Join 语句

5、大表优化

  • 分库分表(水平、垂直)
  • 读写分离
  • 数据定期归档

三、原理剖析

MySQL 逻辑架构图:

1、索引的优缺点

1)优点

  • 提高查询语句的执行效率,减少 IO 操作的次数
  • 创建唯一性索引,可以保证数据库表中每一行数据的唯一性
  • 加了索引的列会进行排序,在使用分组和排序子句进行查询时,可以显著减少查询中分组和排序的时间

2)缺点

  • 索引需要占物理空间
  • 创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加
  • 当对表中的数据进行增删改查时,索引也要动态的维护,这样就降低了数据的更新效率

2、索引的数据结构

1)主键索引

2)普通索引

3)组合索引

3、索引页结构

索引页由七部分组成,其中 Infimum 和 Supremum 也属于记录,只不过是虚拟记录,这里为了与用户记录区分开,还是决定将两者拆开。

数据行格式

MySQL 有 4 种存储格式:

  • Compact
  • Redundant (5.0 版本以前用,已废弃)
  • Dynamic (MySQL5.7 默认格式)
  • Compressed

Dynamic 行存储格式下,对于处理行溢出(当一个字段存储长度过大时,会发生行溢出)时,仅存放溢出页内存地址。

4、索引的设计原则

1)哪些情况适合建索引

  • 数据又数值有唯一性的限制
  • 频繁作为 where 条件的字段
  • 经常使用 group by 和 order by 的字段,既有 group by 又有 order by 的字段时,建议建联合索引
  • 经常作为 update 或 delete 条件的字段
  • 经常需要 distinct 的字段
  • 多表连接时的字段建议创建索引,也有注意事项

a. 连接表数量最好不要超过 3 张,每增加一张表就相当于增加了一次嵌套循环,数量级增长会非常快

b. 对多表查询时的 where 条件创建索引

c. 对连接字段创建索引,并且数据类型保持一致

  • 在确定数据范围的情况下尽量使用数据类型较小的,因为索引会也会占用空间
  • 对字符串创建索引时建议使用字符串的前缀作为索引
  • 这样做的好处是:

a. 能节省索引的空间;

b. 虽然不能精确定位,但是能够定位到相同的前缀,然后通过主键查询完整的字符串,这样既能节省空间,又减少了字符串的比较时间,还能解决排序问题。

  • 区分度高(散列性高)的字段适合作为索引。
  • 在多个字段需要创建索引的情况下,联合索引优先于单值索引。使用最频繁的列作为索引的最左侧。

2)哪些情况下不需要使用索引

  • 在 where 条件中用不到的字段不需要。
  • 数据量小的不需要建索引,比如数据少于 1000 条。
  • 由大量重复数据的列上不要建索引,比如性别字段中只有男和女时。
  • 避免在经常更新的表或字段中创建过多的索引。
  • 不建议主键使用无序的值作为索引,比如 uuid。
  • 不要定义冗余或重复的索引。
  • 例如:已经创建了联合索引 key(id,name)后就不需要再单独建一个 key(id)的索引。

5、索引优化之 MRR

例如有一张表 user,主键 id,普通字段 age,为 age 创建非聚集索引,有一条查询语句 select* user from table where age > 18;(注意查询语句中的结果是*)

在 MySQL5.5 以及之前的版本中如何查询呢?先通过非聚集索引查询到 age>18 的第一条数据,获取到了主键 id;然后根据非聚集索引中的叶子节点存储的主键 id 去聚集索引中查询行数据;根据 age>18 的数据条数每次查询聚集索引,这个过程叫做回表。

上述的步骤有什么缺点呢?如何 age>18 的数据非常多,那么每次回表都需要经过 3 次 IO(假设 B+树的高度是 3),那么会导致查询效率过低。

在 MySQL5.6 时针对上述问题进行了优化,优化器先查询到 age>3 的所有数据的主键 id,对所有主键的 id 进行排序,排序的结果缓存到 read_rnd_buffer,然后通过排好序的主键在聚簇索引中进行查询。

如果两个主键的范围相近,在同一个数据页中就可以之间按照顺序获取,那么磁盘 io 的过程将会大大降低。这个优化的过程就叫做 Multi Range Read(MRR) 多返回查询。

6、索引下推

假设有索引(name, age), 执行 SQL: select * from tuser where name like '张%' and age=10;

MySQL 5.6 以后, 存储引擎根据(name,age)联合索引,找到,由于联合索引中包含列,所以存储引擎直接在联合索引里按照age=10过滤。按照过滤后的数据再一一进行回表扫描。

索引下推使用条件

  • 只能用于range、 ref、 eq_ref、ref_or_null访问方法;
  • 只能用于InnoDB和 MyISAM存储引擎及其分区表;
  • 对存储引擎来说,索引下推只适用于二级索引(也叫辅助索引);

索引下推的目的是为了减少回表次数,也就是要减少 IO 操作。对于的聚簇索引来说,数据和索引是在一起的,不存在回表这一说。

  • 引用了子查询的条件不能下推;
  • 引用了存储函数的条件不能下推,因为存储引擎无法调用存储函数。

7、思考

1)MySQL 一张表到底能存多少数据?

2)为什么要控制单行数据大小?

3)优化案例 4 中优化前的 SQL 为什么走不到索引?

四、总结

抛开数据库硬件层面,数据库表设计、索引设计、业务代码逻辑、分库分表策略、数据归档策略都对 SQL 执行效率有影响,我们只有在整个设计、开发、运维阶段保持高度敏感、追求极致,才能让我们系统的可用性、伸缩性不会随着业务增长而劣化。

>>>>参考资料

  • https://help.aliyun.com/document_detail/311122.html
  • https://blog.csdn.NET/qq_32099833/article/details/123150701
  • https://www.cnblogs.com/tufujie/p/9413852.html

作者丨伍楼华

来源丨公众号:字节跳动技术团队(ID:BytedanceTechBlog)



Tags:慢SQL   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,不构成投资建议。投资者据此操作,风险自担。如有任何标注错误或版权侵犯请与我们联系,我们将及时更正、删除。
▌相关推荐
▌简易百科推荐
向量数据库落地实践
本文基于京东内部向量数据库vearch进行实践。Vearch 是对大规模深度学习向量进行高性能相似搜索的弹性分布式系统。详见: https://github.com/vearch/zh_docs/blob/v3.3.X/do...【详细内容】
2024-04-03  京东云开发者    Tags:向量数据库   点击:(5)  评论:(0)  加入收藏
原来 SQL 函数是可以内联的!
介绍在某些情况下,SQL 函数(即指定LANGUAGE SQL)会将其函数体内联到调用它的查询中,而不是直接调用。这可以带来显著的性能提升,因为函数体可以暴露给调用查询的规划器,从而规划器...【详细内容】
2024-04-03  红石PG  微信公众号  Tags:SQL 函数   点击:(5)  评论:(0)  加入收藏
如何正确选择NoSQL数据库
译者 | 陈峻审校 | 重楼Allied Market Research最近发布的一份报告指出,业界对于NoSQL数据库的需求正在持续上升。2022年,全球NoSQL市场的销售额已达73亿美元,预计到2032年将达...【详细内容】
2024-03-28    51CTO  Tags:NoSQL   点击:(14)  评论:(0)  加入收藏
为什么数据库连接池不采用 IO 多路复用?
这是一个非常好的问题。IO多路复用被视为是非常好的性能助力器。但是一般我们在使用DB时,还是经常性采用c3p0,tomcat connection pool等技术来与DB连接,哪怕整个程序已经变成以...【详细内容】
2024-03-27  dbaplus社群    Tags:数据库连接池   点击:(14)  评论:(0)  加入收藏
八个常见的数据可视化错误以及如何避免它们
在当今以数据驱动为主导的世界里,清晰且具有洞察力的数据可视化至关重要。然而,在创建数据可视化时很容易犯错误,这可能导致对数据的错误解读。本文将探讨一些常见的糟糕数据可...【详细内容】
2024-03-26  DeepHub IMBA  微信公众号  Tags:数据可视化   点击:(7)  评论:(0)  加入收藏
到底有没有必要分库分表,如何考量的
关于是否需要进行分库分表,可以根据以下考量因素来决定: 数据量和负载:如果数据量巨大且负载压力较大,单一库单一表可能无法满足性能需求,考虑分库分表。 数据增长:预估数据增长...【详细内容】
2024-03-20  码上遇见你  微信公众号  Tags:分库分表   点击:(15)  评论:(0)  加入收藏
在 SQL 中写了 in 和 not in,技术总监说要炒了我……
WHY?IN 和 NOT IN 是比较常用的关键字,为什么要尽量避免呢?1、效率低项目中遇到这么个情况:t1表 和 t2表 都是150w条数据,600M的样子,都不算大。但是这样一句查询 &darr;select *...【详细内容】
2024-03-18  dbaplus社群    Tags:SQL   点击:(6)  评论:(0)  加入收藏
应对慢SQL的致胜法宝:7大实例剖析+优化原则
大促备战,最大的隐患项之一就是慢SQL,对于服务平稳运行带来的破坏性最大,也是日常工作中经常带来整个应用抖动的最大隐患,在日常开发中如何避免出现慢SQL,出现了慢SQL应该按照什...【详细内容】
2024-03-14  京东云开发者    Tags:慢SQL   点击:(5)  评论:(0)  加入收藏
过去一年,我看到了数据库领域的十大发展趋势
作者 | 朱洁策划 | 李冬梅过去一年,行业信心跌至冰点2022 年中,红衫的一篇《适应与忍耐》的报告,对公司经营提出了预警,让各个公司保持现金流,重整团队,想办法增加盈利。这篇报告...【详细内容】
2024-03-12    InfoQ  Tags:数据库   点击:(32)  评论:(0)  加入收藏
SQL优化的七个方法,你会哪个?
一、插入数据优化 普通插入:在平时我们执行insert语句的时候,可能都是一条一条数据插入进去的,就像下面这样。INSERT INTO `department` VALUES(1, &#39;研发部(RD)&#39;, &#39...【详细内容】
2024-03-07  程序员恰恰  微信公众号  Tags:SQL优化   点击:(20)  评论:(0)  加入收藏
站内最新
站内热门
站内头条