IT数码 购物 网址 头条 软件 日历 阅读 图书馆
TxT小说阅读器
↓语音阅读,小说下载,古典文学↓
图片批量下载器
↓批量下载图片,美女图库↓
图片自动播放器
↓图片自动播放器↓
一键清除垃圾
↓轻轻一点,清除系统垃圾↓
开发: C++知识库 Java知识库 JavaScript Python PHP知识库 人工智能 区块链 大数据 移动开发 嵌入式 开发工具 数据结构与算法 开发测试 游戏开发 网络协议 系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑 笔记本 显卡 显示器 固态硬盘 硬盘 耳机 手机 iphone vivo oppo 小米 华为 单反 装机 图拉丁
 
   -> 大数据 -> 京东云TiDB SQL优化的最佳实践 -> 正文阅读

[大数据]京东云TiDB SQL优化的最佳实践

京东云TiDB?SQL层的背景介绍

从总体上概括 TiDB 和 MySQL 兼容策略,如下表:

通讯协议SQL语法功能和行为
完全兼容兼容绝大多数兼容大多数

SQL层的架构

用户的 SQL 请求会直接或者通过 Load Balancer 发送到 京东云TiDB Server,TiDB Server 会解析 MySQL Protocol Packet,获取请求内容,对 SQL 进行语法解析和语义分析,制定和优化查询计划,执行查询计划并获取和处理数据。数据全部存储在 TiKV 集群中,所以在这个过程中 TiDB Server 需要和 TiKV 交互,获取数据。最后 TiDB Server 需要将查询结果返回给用户。

417a91d421348b687f9b79e39397da7e.png

一条SQL的生命周期图


● SQL优化流程的概览

在 TiDB 中,从输入的查询文本到最终的执行计划执行结果的过程可以见下图:

969de2003144029839fdd80a5b88b7b1.png

在经过了 parser 对原始查询文本的解析以及一些简单的合法性验证后,TiDB 首先会对查询做一些逻辑上的等价变化,通过这些等价变化,使得这个查询在逻辑执行计划上可以变得更易于处理。在等价变化结束之后,TiDB 会得到一个与原始查询等价的查询计划结构,之后根据数据分布、以及一个算子具体的执行开销,来获得一个最终的执行计划,同时,TiDB 在执行 PREPARE 语句时,可以选择开启缓存来降低 TiDB 生成执行计划的开销。

● 使用 EXPLAIN 语句查看执行计划

执行计划由一系列的算子构成。和其他数据库一样,在 TiDB 中可通过 EXPLAIN 语句返回的结果查看某条 SQL 的执行计划。

目前 TiDB 的 EXPLAIN 会输出 5 列,分别是:id,estRows,task,access object, operator info。执行计划中每个算子都由这 5 列属性来描述,EXPLAIN结果中每一行描述一个算子。每个属性的具体含义如下:

a88fa70692dcd58280aefdc4fade0b88.png

●?EXPLAIN ANALYZE 输出格式

和 EXPLAIN 不同,EXPLAIN ANALYZE 会执行对应的 SQL 语句,记录其运行时信息,和执行计划一并返回出来,可以视为 EXPLAIN 语句的扩展。EXPLAIN ANALYZE 语句的返回结果中增加了 actRows, execution info,memory,disk 这几列信息:

3223494162e65eb40b640f889938411f.png

举个例子如下:

fe5d8e7c250f47c12362ccd018f96f8c.png

从上述例子中可以看出,优化器估算的 estRows 和实际执行中统计得到的 actRows 几乎是相等的,说明优化器估算的行数与实际行数的误差很小。同时 IndexLookUp_10 算子在实际执行过程中使用了约 9 KB 的内存,该 SQL 在执行过程中,没有触发过任何算子的落盘操作。


SQL优化案例最佳实践

案例一:

索引的错误选择导致SQL变慢的优化实践

场景:数据库迁移到TiDB,SQL在MySQL运行不到1S,在TiDB运行超过30S

SQL执行计划如下:

b28be7098de06387ececf9a11e5c0b62.png

execution info列,有该执行计划的时间,这个SQL的表的连接顺序,要从最里面的循环开始看,如下图,m,d是最先开始进行连接的:

d8a7097af03abad2f02eb8516f71dcd3.png

关注下图的time变化,执行计划由毫秒级变成了秒级的地方,由71ms变成了33s,所以瓶颈卡在((m join d) join taskm)join taskd 这个地方,对应的SQL片段如下:

INNER JOIN taskd
ON taskd.no = d.no
 AND taskd.o_no = d.o_no
 AND taskd.d_no = d.d_no
 AND taskd.w_no = d.w_no
 AND taskd.g_no = d.g_no
 AND IF(NULL = d.MD5_VALUE, 1, d.MD5_VALUE) = IF(NULL = d.MD5_VALUE, 1, taskd.MD5_VALUE)
 AND taskd.yn = 0


● 优化思路

1、首先观察 explain analyze 结果,看到慢在最内 3 层的 join 上 ,(m join d) join taskd;

2、对比 MySQL 的执行计划,发现 MySQL 最内的 3 层的 join 是 (m join d) join taskm, 所以把相关的3张表提取出来,修改其join顺序;

3、修改顺序后,join 的时间能减少但是和 MySQL差距还是很大,再次观察,发现 taskd 上TiDB和MySQL使用的索引不一样,所以使用了 use index 来强制TIDB走和MySQL相同的索引。

案例二:

表关联的错误选择导致SQL变慢的优化实践


场景:在MySQL运行时间毫秒级别,在TiDB运行时间18S

在TiDB的运行时间及执行计划

80dd9a60d8b96b552350c103fbe91548.png

优化前后的执行计划

0b70ebc47f7f46c8720863d1940cf62c.png

优化后加了hint的SQL

d1b8582d6d8d38c603517d784ee3563a.png


●?优化思路:

1. TiDB执行耗时 10+s 的原因是对 wps 表的估算不准确,导致优化器认为 w表 和 p表 走 hash join 效率更高,然后我们看到的执行计划的主要耗时在 pri 表回表获取数据的耗时较长 ;

2. w 表估算不准确的原因为TiDB 会把 w 的条件 有range scan 转换点查,然后利用这个索引的统计信息去估算;

3. 点查估算是会利用对应的 CMSketch 去进行估算,结合 p 表数据量很大,根据经验推测可能是 CMSketch 内部 hash 冲突导致。

● 案例一、二的延伸扩展:

在SQL优化的工作中,经常会通过加hint的方式改变SQL的执行计划,从而达到了优化的目的,但是缺点是对SQL进行了硬编码,如果业务程序使用了ORM框架,SQL的改造难度会增加。SQL Binding(SPM)则很好的解决了硬编码的问题,通过SQL Binding,DBA可以在不改变SQL文本的情况下,优化sql的执行计划,从而达到优化的目标,从而使SQL优化变得更加优雅。

京东云联合 PingCAP 基于国内开源 NewSQL 数据库 TiDB 打造的一款同时支持 OLTP 和 OLAP 两种场景的分布式云数据库产品,实现了自动的水平伸缩,强一致性的分布式事务,部署简单,在线异步表结构变更不影响业务,同时兼容 MySQL 协议,使迁移使用成本降到极低。

了解详情可点击底部“阅读原文”。

- End -

  大数据 最新文章
实现Kafka至少消费一次
亚马逊云科技:还在苦于ETL?Zero ETL的时代
初探MapReduce
【SpringBoot框架篇】32.基于注解+redis实现
Elasticsearch:如何减少 Elasticsearch 集
Go redis操作
Redis面试题
专题五 Redis高并发场景
基于GBase8s和Calcite的多数据源查询
Redis——底层数据结构原理
上一篇文章      下一篇文章      查看所有文章
加:2022-01-25 10:39:53  更:2022-01-25 10:40:02 
 
开发: C++知识库 Java知识库 JavaScript Python PHP知识库 人工智能 区块链 大数据 移动开发 嵌入式 开发工具 数据结构与算法 开发测试 游戏开发 网络协议 系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑 笔记本 显卡 显示器 固态硬盘 硬盘 耳机 手机 iphone vivo oppo 小米 华为 单反 装机 图拉丁

360图书馆 购物 三丰科技 阅读网 日历 万年历 2024年12日历 -2024/12/25 1:51:32-

图片自动播放器
↓图片自动播放器↓
TxT小说阅读器
↓语音阅读,小说下载,古典文学↓
一键清除垃圾
↓轻轻一点,清除系统垃圾↓
图片批量下载器
↓批量下载图片,美女图库↓
  网站联系: qq:121756557 email:121756557@qq.com  IT数码
数据统计