IT博文
MySQL 事务隔离级别详解
使用 docker compose 安装 tidb
架构师日记-如何写的一手好代码
生产事故-记一次特殊的OOM排查
Docker安装RabbitMQ——基于docker-compose工具
使用 docker-compose 部署单机 RabbitMQ
只需3步,即刻体验Oracle Database 23c
长达 1.7 万字的 explain 关键字指南!
Redis为什么能抗住10万并发?揭秘性能优越的背后原因
深度剖析Redis九种数据结构实现原理
【绩效季】遇到一个好领导有多重要,从被打差绩效到收获成长
为什么Redis不直接使用C语言的字符串?
Java阻塞队列中的异类,SynchronousQueue底层实现原理剖析
如何调整和优化 Go 程序的内存管理方式?
应用部署引起上游服务抖动问题分析及优化实践方案
Java 并发工具合集 JUC 大爆发!!!
卷起来!!这才是 MySQL 事务 & MVCC 的真相。
JDK8 到 JDK17 有哪些吸引人的新特性?
告别StringUtil:使用Java 11的全新String API优化你的代码
从JDK8飞升到JDK17,再到未来的JDK21
Java JMH Benchmark Tutorial
linux和macOS下top命令区别
Windows10关闭Hyper-V的三种方法
为什么应该选择 POSTGRES?
阿里云对象存储 OSS 限流超过阈值自动关闭【防破产,保平安】
Java高并发革命!JDK19新特性——虚拟线程(Virtual Threads)
“请不要在虚拟机中运行此程序”的解决方案
Spring中的循环依赖及解决
浅谈复杂业务系统的架构设计 | 京东云技术团队
面试题:聊聊TCP的粘包、拆包以及解决方案
操作日志记录实现方式
字节跳动技术团队-慢 SQL 分析与优化
Spring Boot 使用 AOP 防止重复提交
Controller层代码就该这么写,简洁又优雅!
SpringBoot 项目 + JWT 完成用户登录、注册、鉴权
重复提交不再是问题!SpringBoot自定义注解+AOP巧妙解决
SpringBoot 整合 ES 实现 CRUD 操作
SpringBoot 整合 ES 进行各种高级查询搜索
SpringBoot操作ES进行各种高级查询
SpringBoot整合ES查询
如何做架构设计? | 京东云技术团队
最值得推荐的五个VPN软件(便宜+好用+稳定),靠谱的V2ray梯子工具
我说MySQL每张表最好不超过2000万数据,面试官让我回去等通知?
vivo 自研鲁班分布式 ID 服务实践
使用自带zookeeper超简单安装kafka
推荐 6 个很牛的 IDEA 插件
喜马拉雅 Redis 与 Pika 缓存使用军规
「程序员转型技术管理」必修的 10 个能力提升方向
jdk17 下 netty 导致堆内存疯涨原因排查 | 京东云技术团队
如何优雅做好项目管理?
MySQL 到 TiDB:Hive Metastore 横向扩展之路
聊聊即将到来的 MySQL5.7 停服事件
Linux终端环境配置
微软 Edge 浏览器隐藏功能一览:多线程下载、IE 模式、阻止视频自动播放等
Hutool 中那些常用的工具类和实用方法
clash 内核删库?汇总目前常用的内核仓库和客户端
JDK11 升级 JDK17 最全实践干货来了 | 京东云技术团队
我是如何写一篇技术文的?
虚拟线程原理及性能分析
Java线程池实现原理及其在美团业务中的实践
Editplus和EmEditor配置一键编译java运行环境
用Spring Boot 3.2虚拟线程搭建静态文件服务器有多快?
SpringBoot中使用LocalDateTime踩坑记录 - 程序员偏安 - 博客园
程序员必备!10款实用便捷的Git可视化管理工具 - 追逐时光者 - 博客园
基于Netty开发轻量级RPC框架
开发Java应用时如何用好Log
复杂SQL治理实践 | 京东物流技术团队
火山引擎ByteHouse:分析型数据库如何设计并发控制?
多次崩了之后,阿里云终于改了
推荐程序员必知的四大神级学习网站
初探分布式链路追踪
新项目为什么决定用 JDK 17了
Java上进了,JDK21 要来了,并发编程再也不是噩梦了
mapstruct这么用,同事也开始模仿
再见RestTemplate,Spring 6.1新特性:RestClient 了解一下!
【MySQL】MySQL表设计的经验(建议收藏)
如何正确地理解应用架构并开发
解读工行专利CN112905176B
工商银行取得「基于 Spring Boot 的 web 系统后端实现方法及装置」专利
IDEA 2024.1:Spring支持增强、GitHub Action支持增强、更新HTTP Client等
TIOBE 2 月:Go 首次进入前十、“上古语言” COBOL 和 Fortran 排名飙升
Java 21 虚拟线程如何限流控制吞吐量
🎉 通用、灵活、高性能分布式 ID 生成器 | CosId 2.6.6 发布
20年编程,AI编程6个月,关于Copliot辅助编码工具,你想知道的都在这里
Java 8 内存管理原理解析及内存故障排查实践
消息队列选型之 Kafka vs RabbitMQ
从 MongoDB 到 PostgreSQL 的大迁移
腾讯云4月8日故障复盘及情况说明
PHP 在 2024 年还值得学习吗?
AMD集显安装显卡驱动之后出现黑屏,建议这样解决
使用 Docker 部署 moments 微信朋友圈 - 谱次· - 博客园
Java 17 是最常用的 Java LTS 版本
盘点Lombok的几个骚操作
Llama 3 + Ollama + Open WebUI打造本机强大GPT
如何优雅地编写缓存代码
Gmeek快速上手
笔记软件思源远程和本地接入大语言模型服务Ollama实现AI辅助写作(Windows篇)
Git Subtree:简单粗暴的多项目管理神器
这款轻量级规则引擎,真香!!
Ollama教程:本地LLM管理、WebUI对话、Python/Java客户端API应用
GLM-4-9B支持 Ollama 部署
智谱AI开源代码生成大模型第四代版本:CodeGeeX4-ALL-9B
美团二面:如何保证Redis与Mysql双写一致性?连续两个面试问到了!
免费开源好用,Obsidian和Omnivore真正实现一键联动剪藏文章,手把手教程!
得物 Redis 设计与实践
架构图怎么画?手把手教您,以生鲜电商为例剖析业务/应用/数据/技术架构图
使用Hutool要注意了!升级到6.0后你调用的所有方法都将报错 - 掘金
别再用雪花算法生成ID了!试试这个吧
无敌的Arthas!
Navicat Premium v16、v17 破解激活
🎉 分布式接口文档聚合,Solon 是怎么做的?
深入体验全新 Cursor AI IDE 后,说杀疯了真不为过!
Nacos 3.0 架构全景解读,AI 时代服务注册中心的演进
本文档使用 MrDoc 发布
-
+
字节跳动技术团队-慢 SQL 分析与优化
## 背景介绍 从系统设计角度看,一个系统从设计搭建到数据逐步增长,SQL 执行效率可能会出现劣化,为继续支撑业务发展,我们需要对慢 SQL 进行分析和优化,严峻的情况下甚至需要对整个系统进行重构。所以我们往往需要在系统设计前对业务进行充分调研、遵守系统设计规范,在系统运行时定期结合当前业务发展情况进行系统瓶颈的分析。 从数据库角度看,每个 SQL 执行都需要消耗一定 I/O 资源,SQL 执行的快慢,决定了资源被占用时间的长短。假如有一条慢 SQL 占用了 30%的资源共计 1 分钟。那么在这 1 分钟时间内,其他 SQL 能够分配的资源总量就是 70%,如此循环,当资源分配完的时候,所有新的 SQL 执行将会排队等待。所以往往一条慢 SQL 会影响到整个业务。 > 本文仅讨论 MySQL-InnoDB 的情况。 ## 优化方式 SQL 语句执行效率的主要因素 - 数据量 - SQL 执行后返回给客户端的数据量的大小; - 数据量越大需要扫描的 I/O 次数越多,数据库服务器的 IO 更容易成为瓶颈。 - 取数据的方式 - 数据在缓存中还是在磁盘上; - 是否能够通过全局索引快速寻址; - 是否结合谓词条件命中全局索引加速扫描。 - 数据加工的方式 - 排序、子查询、聚合、关联等,一般需要先把数据取到临时表中,再对数据进行加工; - 对于数据量比较多的计算,会消耗大量计算节点的 CPU 资源,让数据加工变得更加缓慢; - 是否选择了合适的 join 方式 ### 优化思路 - 减少数据扫描(减少磁盘访问) - 尽量在查询中加入一些可以提前过滤数据的谓词条件,比如按照时间过滤数据等,可以减少数据的扫描量,对查询更友好; - 在扫描大表数据时是否可以命中索引,减少回表代价,避免全表扫描。 - 返回更少数据(减少网络传输或磁盘访问) - 减少交互次数(减少网络传输) - 将数据存放在更快的地方 - 某条查询涉及到大表,无法进一步优化,如果返回的数据量不大且变化频率不高但访问频率很高,此时应该考虑将返回的数据放在应用端的缓存当中或者 Redis 这样的缓存当中,以提高存取速度。 - 减少服务器 CPU 开销(减少 CPU 及内存开销) - 避免大事务操作 - 利用更多资源(增加资源) ### 优化案例 #### 数据分页优化 ``` 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;} ``` #### 优化 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 ``` #### 范围查询 联合索引中如果有某个列存在范围(大于小于)查询,其右边的列是否还有意义? ``` 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, 100explain 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 的情况下 - 从索引里找到所有 trade\_date\_time 在'2019-05-01' 到'2020-05-01' 区间的主键 id。假设有 100 万个。 - 对这些 id 进行排序(为的是在下面一步回表操作中优化 I/O 操作,因为很多挨得近的主键可能一次磁盘 I/O 就都取到了) - 回表,查出 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'为例: 1. 在范围查找的时候,直接找到最大,最小的值,然后进行链表遍历,故仅能用到 trade\_date\_time 的索引,无法使用到 org\_code 索引 2. 基于 MySQL5.6+的索引下推特性,虽然 org\_code 字段无法使用到索引树,但是可以用于过滤回表的主键 id 数。 小结:对于该 case, 索引效果\[org\_code,trade\_date\_time\] > \[trade\_date\_time, org\_code\]>\[trade\_date\_time\]。实际业务场景中,检索条件中 trade\_date\_time 基本上肯定会出现,但 org\_code 却不一定,故索引的设计还需要结合实际业务需求。 #### 优化 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 statementWHERE (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 statementWHERE (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 字段有关。索引的作用一个是查找,一个是排序。 ``` #### 业务拆分 ``` select * from order where status='S' and update_time < now-5min limit 500 ``` 拆分优化: 随着业务数据的增长 status='S'的数据基本占据数据的 90%以上,此时该条件无法走索引。我们可以结合业务特征,对数据获取按日期进行拆分。 ``` date = now; minDate = now - 10 dayswhile(date > minDate) {select * from order where order_date={#date} and status='S' and update_time < now-5min limit 500date = data + 1} ``` ### 数据库结构优化 1. 范式优化:表的设计合理化(符合 3NF),比如消除冗余(节省空间); 2. 反范式优化:比如适当加冗余等(减少 join) 3. 拆分表:分区将数据在物理上分隔开,不同分区的数据可以制定保存在处于不同磁盘上的数据文件里。这样,当对这个表进行查询时,只需要在表分区中进行扫描,而不必进行全表扫描,明显缩短了查询时间,另外处于不同磁盘的分区也将对这个表的数据传输分散在不同的磁盘 I/O,一个精心设置的分区可以将数据传输对磁盘 I/O 竞争均匀地分散开。对数据量大的表可采取此方法,可按月建表分区。 ### SQL 语句优化 **SQL 检查状态及分数计算逻辑** 1. 尽量避免使用子查询 2. 用 IN 来替换 OR 3. 读取适当的记录 LIMIT M,N,而不要读多余的记录 4. 禁止不必要的 Order By 排序 5. 总和查询可以禁止排重用 union all 6. 避免随机取记录 7. 将多次插入换成批量 Insert 插入 8. 只返回必要的列,用具体的字段列表代替 select \* 语句 9. 区分 in 和 exists 10. 优化 Group By 语句 11. 尽量使用数字型字段 12. 优化 Join 语句 ### 大表优化 - 分库分表(水平、垂直) - 读写分离 - 数据定期归档 ## 原理剖析 MySQL 逻辑架构图:  ### 索引的优缺点 **优点** - 提高查询语句的执行效率,减少 IO 操作的次数 - 创建唯一性索引,可以保证数据库表中每一行数据的唯一性 - 加了索引的列会进行排序,在使用分组和排序子句进行查询时,可以显著减少查询中分组和排序的时间 **缺点** - 索引需要占物理空间 - 创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加 - 当对表中的数据进行增删改查时,索引也要动态的维护,这样就降低了数据的更新效率 ### 索引的数据结构 #### 主键索引  #### 普通索引  #### 组合索引  ### 索引页结构  索引页由七部分组成,其中 Infimum 和 Supremum 也属于记录,只不过是虚拟记录,这里为了与用户记录区分开,还是决定将两者拆开。  #### 数据行格式: MySQL 有 4 种存储格式: 1. Compact 2. Redundant (5.0 版本以前用,已废弃) 3. Dynamic (MySQL5.7 默认格式) 4. Compressed  Dynamic 行存储格式下,对于处理行溢出(当一个字段存储长度过大时,会发生行溢出)时,仅存放溢出页内存地址。 ### 索引的设计原则 **哪些情况适合建索引** - 数据又数值有唯一性的限制 - 频繁作为 where 条件的字段 - 经常使用 group by 和 order by 的字段,既有 group by 又有 order by 的字段时,建议建联合索引 - 经常作为 update 或 delete 条件的字段 - 经常需要 distinct 的字段 - 多表连接时的字段建议创建索引,也有**注意事项** - 连接表数量最好不要超过 3 张,每增加一张表就相当于增加了一次嵌套循环,数量级增长会非常快 - 对多表查询时的 where 条件创建索引 - 对连接字段创建索引,并且数据类型保持一致 - 在确定数据范围的情况下尽量使用数据类型较小的,因为索引会也会占用空间 - 对字符串创建索引时建议使用字符串的前缀作为索引 - 这样做的好处是: - 能节省索引的空间, - 虽然不能精确定位,但是能够定位到相同的前缀,然后通过主键查询完整的字符串,这样既能节省空间,又减少了字符串的比较时间,还能解决排序问题。 - 区分度高(散列性高)的字段适合作为索引。 - 在多个字段需要创建索引的情况下,联合索引优先于单值索引。使用最频繁的列作为索引的最左侧 。 **哪些情况下不需要使用索引** - 在 where 条件中用不到的字段不需要。 - 数据量小的不需要建索引,比如数据少于 1000 条。 - 由大量重复数据的列上不要建索引,比如性别字段中只有男和女时。 - 避免在经常更新的表或字段中创建过多的索引。 - 不建议主键使用无序的值作为索引,比如 uuid。 - 不要定义冗余或重复的索引 - 例如:已经创建了联合索引 key(id,name)后就不需要再单独建一个 key(id)的索引 ### 索引优化之 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) 多返回查询。 ### 索引下推 假设有索引(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 操作。对于的聚簇索引来说,数据和索引是在一起的,不存在回表这一说。 - 引用了子查询的条件不能下推; - 引用了存储函数的条件不能下推,因为存储引擎无法调用存储函数。 ### 思考: 1. MySQL 一张表到底能存多少数据? 2. 为什么要控制单行数据大小? 3. 优化案例 4 中优化前的 SQL 为什么走不到索引? ## 总结 抛开数据库硬件层面,数据库表设计、索引设计、业务代码逻辑、分库分表策略、数据归档策略都对 SQL 执行效率有影响,我们只有在整个设计、开发、运维阶段保持高度敏感、追求极致,才能让我们系统的可用性、伸缩性不会随着业务增长而劣化。 参考资料 1. https://help.aliyun.com/document\_detail/311122.html 2. https://blog.csdn.net/qq\_32099833/article/details/123150701 3. https://www.cnblogs.com/tufujie/p/9413852.html 原文:[慢 SQL 分析与优化 (qq.com)](https://mp.weixin.qq.com/s/CaSVhAJgycjjbCxAkII2ZA)
admin
2023年5月14日 11:55
转发文档
收藏文档
上一篇
下一篇
手机扫码
复制链接
手机扫一扫转发分享
复制链接
Markdown文件
PDF文档(打印)
分享
链接
类型
密码
更新密码