执行计划不准确现象

wfxxh · 2021 年8 月 24 日 06:13

为提高效率，请提供以下信息，问题描述清晰能够更快得到解决：
【 TiDB 使用环境】v5.1.1

【概述】场景 + 问题概述：对一个有tiflash副本的表执行sql，查看计划，底层的执行引擎居然不一样

表结构：

CREATE TABLE `dwd_resource` (
  `event_id` varchar(255) DEFAULT NULL,
  `event_time` date DEFAULT NULL,
  `user_id` varchar(255) DEFAULT NULL,
  `user_type` varchar(255) DEFAULT NULL,
  `institution` varchar(255) DEFAULT NULL,
  `pid` varchar(255) DEFAULT NULL,
  `ip` varchar(255) DEFAULT NULL,
  `operate_type` int(11) DEFAULT NULL,
  `authtoken` text DEFAULT NULL,
  `channelId` varchar(255) DEFAULT NULL,
  `deduction` varchar(255) DEFAULT NULL,
  `exportSize` int(11) DEFAULT NULL,
  `module` varchar(255) DEFAULT NULL,
  `perio_id` varchar(255) DEFAULT NULL,
  `perio_name` text DEFAULT NULL,
  `publish_year` varchar(255) DEFAULT NULL,
  `resource_title` text DEFAULT NULL,
  `resource_type` varchar(255) DEFAULT NULL,
  `language` varchar(20) DEFAULT NULL,
  `producer` varchar(255) DEFAULT NULL,
  `source_db` text DEFAULT NULL,
  `source_id` varchar(255) DEFAULT NULL,
  KEY `idx_module` (`module`),
  KEY `idx_operate_type` (`operate_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
PARTITION BY RANGE COLUMNS(event_time) (
  PARTITION `p202001` VALUES LESS THAN ("2020-02-01"),
  PARTITION `p202002` VALUES LESS THAN ("2020-03-01"),
  PARTITION `p202003` VALUES LESS THAN ("2020-04-01"),
  PARTITION `p202004` VALUES LESS THAN ("2020-05-01"),
  PARTITION `p202005` VALUES LESS THAN ("2020-06-01"),
  PARTITION `p202006` VALUES LESS THAN ("2020-07-01"),
  PARTITION `p202007` VALUES LESS THAN ("2020-08-01"),
  PARTITION `p202008` VALUES LESS THAN ("2020-09-01"),
  PARTITION `p202009` VALUES LESS THAN ("2020-10-01"),
  PARTITION `p202010` VALUES LESS THAN ("2020-11-01"),
  PARTITION `p202011` VALUES LESS THAN ("2020-12-01"),
  PARTITION `p202012` VALUES LESS THAN ("2021-01-01"),
  PARTITION `p202101` VALUES LESS THAN ("2021-02-01"),
  PARTITION `p202102` VALUES LESS THAN ("2021-03-01"),
  PARTITION `p202103` VALUES LESS THAN ("2021-04-01"),
  PARTITION `p202104` VALUES LESS THAN ("2021-05-01"),
  PARTITION `p202105` VALUES LESS THAN ("2021-06-01"),
  PARTITION `p202107` VALUES LESS THAN ("2021-08-01"),
  PARTITION `p202108` VALUES LESS THAN ("2021-09-01"),
  PARTITION `p202109` VALUES LESS THAN ("2021-10-01"),
  PARTITION `p202110` VALUES LESS THAN ("2021-11-01"),
  PARTITION `p202111` VALUES LESS THAN ("2021-12-01"),
  PARTITION `p202112` VALUES LESS THAN ("2022-01-01")
);

查询语句：

desc SELECT
  str_to_date(
    concat(
      date_format(`dwd_resource`.`event_time`, '%Y-%m'),
      '-01'
    ),
    '%Y-%m-%d'
  ) AS `event_time`,
  count(*) AS `count`
FROM
  `dwd_resource`
WHERE
  (
    (
      `dwd_resource`.`operate_type` = 2
      OR `dwd_resource`.`operate_type` = 17
    )
    AND `dwd_resource`.`event_time` >= '2021-01-01 00:00:00'
    AND `dwd_resource`.`event_time` < '2021-07-01 00:00:00'
  )
GROUP BY
  str_to_date(
    concat(
      date_format(`dwd_resource`.`event_time`, '%Y-%m'),
      '-01'
    ),
    '%Y-%m-%d'
  )
ORDER BY
  str_to_date(
    concat(
      date_format(`dwd_resource`.`event_time`, '%Y-%m'),
      '-01'
    ),
    '%Y-%m-%d'
  ) ASC

执行计划：

songxuecheng · 2021 年8 月 25 日 09:05

正常啊。混合索引的，全表扫就认为tiflash优，有索引就tikv

wfxxh · 2021 年8 月 26 日 00:14

请看我的表结构。

zz-jason · 2021 年8 月 26 日 00:54

可以看看 explain analyze 的结果中，走 TiKV 的那个 IndexLookupReader 返回的数据量是多少

wfxxh · 2021 年8 月 26 日 01:21

explain analyze.txt (74.2 KB)

zhenjiaogao · 2021 年8 月 26 日 02:28

从执行计划可见，p202102，p202103，p202101 走 TiKV 的执行计划中，estRows 和 actRows 差异比较大，建议通过下面的文档，查看下目标表 dwd_resource 以及其分区的统计信息健康度：

https://docs.pingcap.com/zh/tidb/stable/statistics#表的健康度信息

wfxxh · 2021 年8 月 26 日 02:42

STATS_HEALTHY.txt (2.2 KB)
健康度都是100

zhenjiaogao · 2021 年8 月 26 日 02:49

辛苦再看下这个表的 SHOW STATS_META 信息 ~

wfxxh · 2021 年8 月 26 日 03:00

zhenjiaogao · 2021 年8 月 26 日 03:07

收到，如果方便的话，您那里可以导出这个表的统计信息吗？

https://docs.pingcap.com/zh/tidb/stable/statistics#导出统计信息

wfxxh · 2021 年8 月 26 日 03:21

链接: https://pan.baidu.com/s/1AlNxfG3lcIpBWf9UGEJsiA
提取码: 7kca

文件超过大小限制，传到网盘了。这个表是先建的表，后创建的索引

zhenjiaogao · 2021 年8 月 26 日 06:33

我这边把统计信息导入到本地 v5.1.1 环境后，查看该 sql 的执行计划，能看到 p202102，p202103，p202101 分区的估算数据相对是准确的，以下是走 TiKV 的执行计划：

这个是优化器自动选择的执行计划：

看起来是没有复现您上面提到的问题。所以，这个 SQL 辛苦 explain analyze 下，并且将结果输入到文本文件中，注意该操作会将真正的执行一遍对应的 SQL 语句，谢谢 ~

explain analyze SELECT str_to_date( concat( date_format(`dwd_resource`.`event_time`, '%Y-%m'), '-01' ), '%Y-%m-%d' ) AS `event_time`, count(*) AS `count` FROM `dwd_resource` WHERE ( ( `dwd_resource`.`operate_type` = 2 OR `dwd_resource`.`operate_type` = 17 ) AND `dwd_resource`.`event_time` >= '2021-01-01 00:00:00' AND `dwd_resource`.`event_time` < '2021-07-01 00:00:00' ) GROUP BY str_to_date( concat( date_format(`dwd_resource`.`event_time`, '%Y-%m'), '-01' ), '%Y-%m-%d' ) ORDER BY str_to_date( concat( date_format(`dwd_resource`.`event_time`, '%Y-%m'), '-01' ), '%Y-%m-%d' ) ASC```

wfxxh · 2021 年8 月 26 日 07:31

我这里发的就是啊，你不是还看了吗:cold_sweat:

zhenjiaogao · 2021 年8 月 26 日 07:34

是的，看到了，但是我这里没有复现你上面的情况，所以，建议你那里再次执行下看下信息 ~

wfxxh · 2021 年8 月 26 日 08:03

出现了一个神奇的现象，我有三个tidb节点，前端用nginx做的负载均衡，我本地连的是nginx的地址，有时会出现直接走tiflash的情况。我直接连tidb节点，其中两个都是走tikv，另一个是走tiflash。

zhenjiaogao · 2021 年8 月 26 日 08:39

嗯嗯，收到，当前的情况是 3 个 tidb server，其中一个 tidb server 该 sql 中所有的 partition 都走了 TiFlash，剩下两个 tidb server 该 sql 中的 partition 有的走了 tikv，有的走了 TiFlash ~

确认下，当前集群确实是 v5.1.1 版本的哈，另外，辛苦看下下列参数的设置：

feedback-probability

wfxxh · 2021 年8 月 26 日 08:43

是的v5.1.1版本，这个参数没有修改过是默认值。

zhenjiaogao · 2021 年8 月 26 日 08:46

好的，收到，辛苦把 3 个 TiDB Server 执行相同的 SQL 语句执行计划的不同的情况分别截图上传下吧 ~

这边再确认下，后续有进展会跟帖回复 ~

这个问题，可尝试 analyze 重新收集下目标表 partition 的统计信息，然后再观察下该 SQL 在每个 TiDB Server 的执行计划是否一致，并且是否符合预期 ~

wfxxh · 2021 年8 月 26 日 08:53

好的我试试。

zhenjiaogao · 2021 年8 月 26 日 08:54

辛苦再上传下这个信息哈，谢谢 ~