对比 TIDB JOIN 优化的疑问

为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。

  • 【TiDB 版本】:3.1.0-RC
  • 【问题描述】:同一条JOIN语句,对比TIDB和Mysql的执行计划,发现TIDB扫描JOIN的数据是Mysql的20多倍,耗时也是mysql的十多倍。想咨询该如何优化。

TIDB截图

Mysql截图

SQL语句:

explain SELECT
count(*) 
FROM
rental_order ro
LEFT JOIN order_record ord ON ord.rental_order = ro.uuid 
WHERE
ro.STATUS = 1 
AND ro.bike_campus = "16ec67c0-4c55-42ae-8b23-bb06ff3605b1" 
AND ord.return_time >= DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s')

【感觉是JOIN的时候,驱动表搞错了?没有使用最佳方案?】

若提问为性能优化、故障排查类问题,请下载脚本运行。终端输出打印结果,请务必全选并复制粘贴上传。

看执行计划应该都走了索引。 当前tidb 和mysql的两个表的数据量是否一致呢?

完全一样的~TIDB通过Mysql的Binlog订阅

麻烦反馈下 tidb 和 mysql 中表结构信息,多谢。

因为是binlog同步,所以表结构在TIDB和Mysql中一样。

CREATE TABLE rental_order ( uuid char(36) COLLATE utf8mb4_general_ci NOT NULL COMMENT ‘’, user char(36) COLLATE utf8mb4_general_ci NOT NULL COMMENT ‘’, bike char(36) COLLATE utf8mb4_general_ci NOT NULL COMMENT ‘’, bike_type char(36) COLLATE utf8mb4_general_ci NOT NULL COMMENT ‘’, bike_campus char(36) COLLATE utf8mb4_general_ci NOT NULL COMMENT ‘’, user_campus char(36) COLLATE utf8mb4_general_ci NOT NULL COMMENT ‘’, pay_status tinyint(1) NOT NULL DEFAULT ‘0’ COMMENT ‘’, status tinyint(3) NOT NULL DEFAULT ‘0’ COMMENT ‘’, user_lock char(36) COLLATE utf8mb4_general_ci NOT NULL COMMENT ‘’, bike_lock char(36) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT ‘’, appeal tinyint(1) NOT NULL DEFAULT ‘0’ COMMENT ‘’, cycling_cost decimal(10,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘’, total_cost decimal(10,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘’, booking tinyint(5) NOT NULL DEFAULT ‘0’ COMMENT ‘’, create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘订单创建时间’, modify_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘修改时间’, PRIMARY KEY (uuid), UNIQUE KEY user_lock (user_lock), UNIQUE KEY bike_lock (bike_lock), KEY bike_campus (bike_campus), KEY bike_type (bike_type), KEY status (status), KEY pay_status (pay_status), KEY booking (booking), KEY create_time (create_time), KEY bike (bike), KEY user (user), KEY total_cost (total_cost) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE order_record ( uuid char(36) COLLATE utf8mb4_general_ci NOT NULL DEFAULT ‘’, rental_order char(36) COLLATE utf8mb4_general_ci NOT NULL COMMENT ‘所属订单’, rental_fence char(36) COLLATE utf8mb4_general_ci NOT NULL DEFAULT ‘’ COMMENT ‘’, return_fence char(36) COLLATE utf8mb4_general_ci NOT NULL DEFAULT ‘’ COMMENT ‘’, start_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘计费开始时间’, return_time datetime DEFAULT NULL COMMENT ‘计费结束时间’, mileage decimal(10,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘’, return_location_mode tinyint(5) NOT NULL DEFAULT ‘0’ COMMENT ‘’, status tinyint(1) NOT NULL DEFAULT ‘0’ COMMENT ‘’, booking datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘’, PRIMARY KEY (uuid), KEY order (rental_order), KEY start_time (start_time), KEY return_time (return_time) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

  1. 请帮忙确认下 rental_order ro (ro.STATUS = 1 AND ro.bike_campus = “16ec67c0-4c55-42ae-8b23-bb06ff3605b1” ) 这个等值条件在 rental_order 表中有多少记录?

  2. 麻烦查看下当前两个表的统计信息,多谢。

SHOW STATS_HEALTHY where table_name='xxx';  

https://pingcap.com/docs-cn/v3.0/reference/performance/statistics/#统计信息简介

你好麻烦执行下下面的 SQL 看看结果:

explain SELECT /*+ USE_INDEX(order_record, return_time),INL_JOIN(ro) */
count(*) 
FROM
rental_order ro
LEFT JOIN order_record ord ON ord.rental_order = ro.uuid 
WHERE
ro.STATUS = 1 
AND ro.bike_campus = "16ec67c0-4c55-42ae-8b23-bb06ff3605b1" 
AND ord.return_time >= DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s')

执行速度快了很多,和Mysql的速度基本一样了,不知道是不是因为TiFlash的加速

现象:USE_INDEX(order_record, return_time)好像可以去掉,优化器好像能选对索引,对查询影响不大。主要是使用INL_JOIN(ro)后,速度快了很多。

猜测:
1.TIFlash加速效果明显
2.因为TIDB和TiFlash在同一台机器上,会不会提速效果也很明显。

  1. count = 223380

  2. 两张表的healthy都是60,61的样子

你好

1,烦请执行下面 sql 看看看看结果,使用 tikv 作为存储引擎:

explain SELECT /*+ read_from_storage(tikv[ord]),USE_INDEX(order_record, return_time),INL_JOIN(ro) */
count(*) 
FROM
rental_order ro
LEFT JOIN order_record ord ON ord.rental_order = ro.uuid 
WHERE
ro.STATUS = 1 
AND ro.bike_campus = "16ec67c0-4c55-42ae-8b23-bb06ff3605b1" 
AND ord.return_time >= DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s')

可以根据文档调整下参数,也可以手动收集下。
https://pingcap.com/docs-cn/stable/reference/performance/statistics/#查看-analyze-状态

使用TiKV,耗时更少,直接下降了一个数量级…………

执行结果:

稍等,这边看下。

你好

  1. 可以明确下这个的具体意思吗?

  2. 将第一条 sql 在 tidb 和 mysql 上的返回结果上传下,截图带上 sql 语句哈。感谢感谢

  3. 将第二条 sql 在 tidb 上执行下,并上传下返回结果。

# tidb、mysql

explain analyze SELECT
count(*) 
FROM
rental_order ro
LEFT JOIN order_record ord ON ord.rental_order = ro.uuid 
WHERE
ro.STATUS = 1 
AND ro.bike_campus = "16ec67c0-4c55-42ae-8b23-bb06ff3605b1" 
AND ord.return_time >= DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s')
# tidb
explain analyze  SELECT /*+ read_from_storage(tikv[ord]),USE_INDEX(order_record, return_time),INL_JOIN(ro) */
count(*) 
FROM
rental_order ro
LEFT JOIN order_record ord ON ord.rental_order = ro.uuid 
WHERE
ro.STATUS = 1 
AND ro.bike_campus = "16ec67c0-4c55-42ae-8b23-bb06ff3605b1" 
AND ord.return_time >= DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s')

1.就是使用Hint指定在tikv里运行后,执行耗时下降了一个数量级。换句话说,我开启TiFlash后,使一个SQL变的更慢了。

2.第一条SQL在Mysql中报错无法执行(我这用的是阿里云的PolarDB For Mysql)。去掉analyze后可以兼容执行

3.使用TIKV的截图

使用TiFlash的截图

可以发现,去掉指定TiKV的Hint后,TIDB默认使用了TiFlash,使查询变慢了。

ok,这边反馈下。

这条sql , tikv和tiflash的差距在读取ord表上,走tikv可以使用index,走tiflash只能走全表扫+Selection。

  1. 能尝试重新analyze下表 看看是否能选到正确的执行计划

  2. 如果不能的话 使用hint/sql bind是否可以接受.

  3. execution info 没有截取全,麻烦反馈下。 可以看看具体从ord表中读取了多少条数据 和目前优化器估计的行数有多大的差距

感谢回复!

  1. 试了一下,有作用!!

  2. 可以接受,不过需要更改很多SQL:sob:

3.

使用Hint后:

前面analyze table搞错表了,后面搞对后,发现可以选定正确的执行计划了。如下图:

想咨询一下,TIDB哪些常见的情况下会导致表的执行计划不准?需要怎么日常运维呢?

执行计划不准的大多数情况都是 统计信息不准确, 统计信息收集有触发条件,可以参考以下文章。 如果比较难达到触发条件,感觉可以设置定时任务来收集。

https://pingcap.com/docs-cn/v3.0/reference/performance/statistics/#统计信息简介

当某个表 tbl 的修改行数与总行数的比值大于 tidb_auto_analyze_ratio == 0.5

这个感觉对于大表而言,很难达到。感觉这块可优化一下。

感谢回复,感谢帮助定位问题!

:handshake: