写点什么

一次多表关联顺序的慢查询——TiDB 关联特性

  • 2022 年 8 月 05 日
  • 本文字数:1817 字

    阅读完需:约 6 分钟

作者: ealam_ 小羽原文来源:https://tidb.net/blog/4a888c14

1. 背景介绍

1.1 基础表

用户表

CREATE TABLE users (  id int primary key auto_increment,    name varchar(30),    key `name` (`name`))
复制代码

帖子表

CREATE TABLE posts (  id int primary key auto_increment,    title varchar(300),    content text,    user_id int,    cate_id int,    key `user_id` (`user_id`),    key `cate_id` (`cate_id`));
复制代码

帖子分类表

CREATE TABLE post_type (  id int primary key auto_increment,    type varchar(10),    name varchar(30));
复制代码

1.2 需求

根据用户名获取该用户的某个分类下的帖子列表。

2. 问题

接到这个任务,看上去真的很简单,用户表关联帖子表关联帖子分类表即可查询到数据。


select title, content, typefrom users a, posts b, post_type cwhere a.id = b.user_idand b.cate_id = c.idand a.name = 'name1'and c.type = 'type1'limit 10;
复制代码


写完 SQL,当然要分析下 SQL 性能了。



怎么回事,索引和预期走的不一样,怎么是帖子表 b 和帖子分类表 c 先做了一次 HashJoin?


线上帖子表 b 数据很多,与分类表 c 做关联的话,那岂不是慢查询!


为什么不是用户表 a 和 帖子表 b 先做关联呢,这两个表做关联之后数据就很少了,查询应该不会慢。

3. 解决过程

3.1 ANALYZE

尝试一下之前还没切换 TiDB 的 MySQL 测试环境,执行符合预期啊!


看起来是 TiDB 的特性嘛!论坛里搜一搜先!



这个帖子结帖信息就很相似啊,执行计划选择错误!


那来看一下 ANALYZE 的作用,为什么会导致这种情况呢?


ANALYZE 语句用于更新 TiDB 在表和索引上留下的统计信息。执行大批量更新或导入记录后,或查询执行计划不是最佳时,建议运行 ANALYZE

当 TiDB 逐渐发现这些统计数据与预估不一致时,也会自动更新其统计数据。

​ —— TiDB 官方文档


看了描述,测试环境应该没有大量删除和导入,死马当活马医,对三个表执行了下 analyze,果然没有效果。


论坛继续搜了搜也没有很符合的案例,但不怕,官方文档里还有有 SQL 性能调优系列文档,一篇篇翻下来吧,应该能找到问题。

3.2 Join Reorder 算法

皇天不负有心人,这个 Join Reorder 算法好像有点意思。


在实际的业务场景中,多个表的 Join 语句是很常见的,而 Join 的执行效率和各个表参与 Join 的顺序有关系。

select * from t1, t2, t3 where t1.a=t2.a and t3.a=t2.a,这个 SQL 中可能的执行顺序有 “t1 和 t2 先做 Join,然后再和 t3 做 Join” 以及 “t2 和 t3 先做 Join,然后再和 t1 做 Join” 两种情况。根据 t1t3 的数据量及数据分布,这两种执行顺序会有不同的性能表现。

因此优化器需要实现一种决定 Join 顺序的算法。目前 TiDB 中使用的算法是 Join Reorder 算法,又称贪心算法。

当前的 Join Reorder 算法存在如下限制:

  • 受结果集的计算算法所限并不会保证一定会选到合适的 Join order

​ —— TiDB 官方文档



很符合我的情况,帖子分类表最小,所以会用帖子分类表 c 来去尝试和用户表 a 和帖子表 b 分别做 Join。


然后就选择了帖子表 b 和帖子分类表 c 做关联。


但因为帖子表数据量非常大,帖子分类表很小,这两个表做关联的话,关联后的结果很多。


而用户表 a 和帖子表 c 关联,可能只有几十条数据,之后再关联分类表 C 这才是一个最佳的执行路径!


既然找到了问题,那 TiDB 当然有现成的解决方案!

3.3 STRAIGHT_JOIN

目前 TiDB 中支持使用 STRAIGHT_JOIN 语法来强制指定一种 Join 顺序。

STRAIGHT_JOIN() 提示优化器在生成表连接顺序时按照表名在 FROM 子句中出现的顺序进行连接。

SELECT /*+ STRAIGHT_JOIN() */ * FROM t t1, t t2 WHERE t1.a = t2.a;


来来来,试一下:



果然,执行结果按照顺序选择了最优执行路径,问题解决!

4. 总结

虽然 TiDB 支持了几乎 100% 的 M 有 SQL 语法,但还是会有很多由于实现、分布式等原因导致由一些独有的特性。


因此,在使用时,不能经验主义,每个 SQL 还是要老老实实在 TiDB 中执行一次,确认是否符合预期。


如果不符合预期,那也不用怕,TiDB 的论坛和文档很丰富,慢慢看,别着急,总有一款适合你的解决方案。


参考文档:


  1. 与 MySQL 兼容性对比

  2. SQL 性能调优

  3. ANALYZE 文档

  4. ANALYZE TABLE 作用 - TiDB - TiDB 的问答社区 (asktug.com)


发布于: 刚刚阅读数: 3
用户头像

TiDB 社区官网:https://tidb.net/ 2021.12.15 加入

TiDB 社区干货传送门是由 TiDB 社区中布道师组委会自发组织的 TiDB 社区优质内容对外宣布的栏目,旨在加深 TiDBer 之间的交流和学习。一起构建有爱、互助、共创共建的 TiDB 社区 https://tidb.net/

评论

发布
暂无评论
一次多表关联顺序的慢查询——TiDB 关联特性_性能调优_TiDB 社区干货传送门_InfoQ写作社区