对比 TIDB JOIN 优化的疑问

longge93 · 2020 年5 月 6 日 09:07

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

【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的时候，驱动表搞错了？没有使用最佳方案？】

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

yilong · 2020 年5 月 6 日 09:43

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

longge93 · 2020 年5 月 6 日 09:44

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

yilong · 2020 年5 月 6 日 11:12

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

longge93 · 2020 年5 月 7 日 05:10

因为是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;

yilong · 2020 年5 月 7 日 07:46

请帮忙确认下 rental_order ro （ro.STATUS = 1 AND ro.bike_campus = “16ec67c0-4c55-42ae-8b23-bb06ff3605b1” ）这个等值条件在 rental_order 表中有多少记录？
麻烦查看下当前两个表的统计信息，多谢。

SHOW STATS_HEALTHY where table_name='xxx';

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

QBin · 2020 年5 月 7 日 07:46

你好麻烦执行下下面的 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')

longge93 · 2020 年5 月 8 日 04:10

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

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

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

longge93 · 2020 年5 月 8 日 04:29

count = 223380
两张表的healthy都是60,61的样子

来了老弟 · 2020 年5 月 8 日 06:42

你好

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-状态

longge93 · 2020 年5 月 9 日 05:55

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

执行结果：

来了老弟 · 2020 年5 月 9 日 06:12

稍等，这边看下。

来了老弟 · 2020 年5 月 11 日 05:19

你好

可以明确下这个的具体意思吗？
将第一条 sql 在 tidb 和 mysql 上的返回结果上传下，截图带上 sql 语句哈。感谢感谢
将第二条 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')

longge93 · 2020 年5 月 11 日 06:30

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

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

3.使用TIKV的截图

使用TiFlash的截图

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

来了老弟 · 2020 年5 月 11 日 08:35

ok，这边反馈下。

yilong · 2020 年5 月 12 日 10:02

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

能尝试重新analyze下表看看是否能选到正确的执行计划
如果不能的话使用hint/sql bind是否可以接受.
execution info 没有截取全，麻烦反馈下。可以看看具体从ord表中读取了多少条数据和目前优化器估计的行数有多大的差距

longge93 · 2020 年5 月 12 日 10:27

感谢回复！

试了一下，有作用！！
可以接受，不过需要更改很多SQL:sob:

3.

使用Hint后：

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

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

yilong · 2020 年5 月 12 日 11:31

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

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

longge93 · 2020 年5 月 13 日 04:07

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

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

感谢回复，感谢帮助定位问题！

yilong · 2020 年5 月 13 日 04:09