研发案例 慢 SQL 分析与优化 来源: 发表时间: 2022-08-24 来源:火狐体育最新官网登录入口 作者:火狐体育app

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  为助力企业提升智能运维(AIOps)技术实践能力,促进场景应用落地,中国信息通信研究院云计算与大数据研究所联合dbaplus社群,携手来自蚂蚁集团、网易游戏、建信金科的AIOps专家,围绕“释放AI效能,迈向智能运维深水区”这一主题开展线上直播分享,针对智能化运维(AIOps)能力成熟度模型系列标准、金融运维数字化转型、AIOps适用性、智能运维平台构建等话题进行深度探讨,为各行各业的智能运维能力建设提供借鉴和启发。

  dbaplus社群是围绕Database、BigData、AIOps的企业级专业社群。资深大咖、技术干货,每天精品原创文章推送,每周线上技术分享,每月线下技术沙龙,每季度Gdevops&DAMS行业大会。

上一篇:为什么实时分析既需要NoSQL的灵活性又需要SQL系统的严格模式? 下一篇:太全了!用Python操作MySQL的使用教程集锦!
关注我们
©2022 火狐体育最新登录网址_官网app入口 京公网安备110177777720125 火狐体育最新登录网址|火狐体育app