慢sql执行计划于真实执行计划不付，但命令行explain执行计划确是对的

Demo二棉裤 · 2024 年3 月 28 日 02:08

【 TiDB 使用环境】生产环境
【 TiDB 版本】v2.1.17
【复现路径】慢日志看到慢sql，显示全表扫描，但从命令行看执行计划是正确的
【遇到的问题：问题现象及影响】请教下，这种情况大概率是什么原因，后面附带表结构以及健康度等信息。

# Time: 2024-03-25T22:00:50.391012815+08:00
# Txn_start_ts: 448626727845888001
# User: ecifapp@21.1.201.212
# Conn_ID: 1268
# Query_time: 95.655898391
# Process_time: 14.44 Wait_time: 0.001 Request_count: 18 Total_keys: 6417338 Process_keys: 6395862
# DB: ecifapp
# Index_ids: [3]
# Is_internal: false
# Digest: 4b8465e5203e28b976333816deaeb575173b82bbf2c3cbe7030db3c94cf06442
# Stats: t01_per_rel_per_info:448626645506457609,t02_per_cust_cust_rel:448626641797120007
# Num_cop_tasks: 18
# Cop_proc_avg: 0.802222222 Cop_proc_p90: 1.199 Cop_proc_max: 1.217
# Cop_wait_avg: 5.5555e-05 Cop_wait_p90: 0 Cop_wait_max: 0.001
# Mem_max: 825209759
SELECT   r.REL_TYPE as relType,   p.REL_NAME as relName,   p.CERT_TYPE as certType,   p.CERT_NO as certNo,   p.MOBILE_PHONE as mobilePhone  FROM   t02_per_cust_cust_rel r  LEFT JOIN t01_per_rel_per_info p ON r.REL_ID = p.REL_ID AND r.UPDATED_TS = '99991231'  WHERE   r.UPDATED_TS = '99991231'  AND r.REL_TAB_ID = 'TB010201'  AND r.PARTY_ID = '100000000000000000002'  group by r.REL_TYPE,p.REL_NAME,p.CERT_TYPE,p.CERT_NO,p.MOBILE_PHONE  having count(0)>1;

MySQL [(none)]> use ecifapp;
Database changed
MySQL [ecifapp]> explain analyze SELECT   r.REL_TYPE as relType,   p.REL_NAME as relName,   p.CERT_TYPE as certType,   p.CERT_NO as certNo,   p.MOBILE_PHONE as mobilePhone  FROM   t02_per_cust_cust_rel r  LEFT JOIN t01_per_rel_per_info p ON r.REL_ID = p.REL_ID AND r.UPDATED_TS = '99991231'  WHERE   r.UPDATED_TS = '99991231'  AND r.REL_TAB_ID = 'TB010201'  AND r.PARTY_ID = '100000000000000000002'  group by r.REL_TYPE,p.REL_NAME,p.CERT_TYPE,p.CERT_NO,p.MOBILE_PHONE  having count(0)>1;
+----------------------------------+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------+
| id                               | count | task | operator info                                                                                                                                                                                                                                                                             | execution info                     |
+----------------------------------+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------+
| Projection_9                     | 0.80  | root | ecifapp.r.reltype, ecifapp.p.relname, ecifapp.p.certtype, ecifapp.p.certno, ecifapp.p.mobilephone                                                                                                                                                                                         | time:180.598979ms, loops:1, rows:0 |
| └─Selection_10                   | 0.80  | root | gt(sel_agg_5, 1)                                                                                                                                                                                                                                                                          | time:180.582659ms, loops:1, rows:0 |
|   └─HashAgg_13                   | 1.00  | root | group by:ecifapp.p.cert_no, ecifapp.p.cert_type, ecifapp.p.mobile_phone, ecifapp.p.rel_name, ecifapp.r.rel_type, funcs:count(0), firstrow(ecifapp.r.rel_type), firstrow(ecifapp.p.rel_name), firstrow(ecifapp.p.cert_type), firstrow(ecifapp.p.cert_no), firstrow(ecifapp.p.mobile_phone) | time:180.573756ms, loops:2, rows:3 |
|     └─IndexJoin_17               | 0.00  | root | left outer join, inner:IndexLookUp_16, outer key:ecifapp.r.rel_id, inner key:ecifapp.p.rel_id, left cond:eq(ecifapp.r.updated_ts, 9999-12-31 00:00:00.000000)                                                                                                                             | time:179.489608ms, loops:2, rows:3 |
|       ├─IndexLookUp_26           | 0.00  | root |                                                                                                                                                                                                                                                                                           | time:104.476466ms, loops:3, rows:3 |
|       │ ├─Selection_24           | 0.00  | cop  | eq(ecifapp.r.updated_ts, 9999-12-31 00:00:00.000000)                                                                                                                                                                                                                                      |                                    |
|       │ │ └─IndexScan_22         | 0.00  | cop  | table:r, index:party_id, rel_type, rel_id, updated_ts, range:["100000000000000000002","100000000000000000002"], keep order:false                                                                                                                                                            |                                    |
|       │ └─Selection_25           | 0.00  | cop  | eq(ecifapp.r.rel_tab_id, "TB010201")                                                                                                                                                                                                                                                      |                                    |
|       │   └─TableScan_23         | 0.00  | cop  | table:t02_per_cust_cust_rel, keep order:false, stats:pseudo                                                                                                                                                                                                                               |                                    |
|       └─IndexLookUp_16           | 1.00  | root |                                                                                                                                                                                                                                                                                           | time:74.895481ms, loops:2, rows:3  |
|         ├─IndexScan_14           | 1.00  | cop  | table:p, index:rel_id, updated_ts, range: decided by [ecifapp.r.rel_id], keep order:false                                                                                                                                                                                                 |                                    |
|         └─TableScan_15           | 1.00  | cop  | table:t01_per_rel_per_info, keep order:false                                                                                                                                                                                                                                              |                                    |
+----------------------------------+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------+
12 rows in set (0.27 sec)

MySQL [ecifapp]> select count(*) from t02_per_cust_cust_rel;
+----------+
| count(*) |
+----------+
|  6395710 |
+----------+
1 row in set (0.85 sec)

MySQL [ecifapp]> select count(*) from t01_per_rel_per_info;
+----------+
| count(*) |
+----------+
|  6395704 |
+----------+
1 row in set (0.55 sec)

MySQL [ecifapp]> show stats_healthy where  table_name='t02_per_cust_cust_rel';
+---------+-----------------------+---------+
| Db_name | Table_name            | Healthy |
+---------+-----------------------+---------+
| ecifapp | t02_per_cust_cust_rel |      99 |
+---------+-----------------------+---------+
1 row in set (0.00 sec)

MySQL [ecifapp]> show stats_healthy where  table_name='t01_per_rel_per_info';
+---------+----------------------+---------+
| Db_name | Table_name           | Healthy |
+---------+----------------------+---------+
| ecifapp | t01_per_rel_per_info |      99 |
+---------+----------------------+---------+
1 row in set (0.00 sec)

MySQL [ecifapp]>

MySQL [ecifapp]> show create table t01_per_rel_per_info;
+----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table                | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t01_per_rel_per_info | CREATE TABLE `t01_per_rel_per_info` (
  `rel_id` varchar(30) NOT NULL COMMENT 'TIDB',
  `ecif_cust_no` varchar(30) DEFAULT NULL COMMENT 'TIDB',
  `rel_name` varchar(240) NOT NULL COMMENT 'TIDB',
  `cert_type` varchar(30) DEFAULT NULL COMMENT 'TIDB',
  `cert_no` varchar(40) DEFAULT NULL COMMENT 'TIDB',
  `cert_issue_dt` date DEFAULT NULL COMMENT 'TIDB',
  `cert_expt_dt` date DEFAULT NULL COMMENT 'TIDB',
  `gender` varchar(30) DEFAULT NULL COMMENT 'TIDB',
  `birthday` date DEFAULT NULL COMMENT 'TIDB',
  `prim_nat` varchar(30) DEFAULT NULL COMMENT 'TIDB',
  `people` varchar(30) DEFAULT NULL COMMENT 'TIDB',
  `properties` varchar(30) DEFAULT NULL COMMENT 'TIDB',
  `educ_level` varchar(30) DEFAULT NULL COMMENT 'TIDB',
  `prfssn` varchar(30) DEFAULT NULL COMMENT 'TIDB',
  `poston` varchar(30) DEFAULT NULL COMMENT 'TIDB',
  `salary` varchar(30) DEFAULT NULL COMMENT 'TIDB',
  `employer` varchar(80) DEFAULT NULL COMMENT 'TIDB',
  `unit_addr` varchar(300) DEFAULT NULL COMMENT 'TIDB',
  `fami_addr` varchar(300) DEFAULT NULL COMMENT 'TIDB',
  `unit_tel` varchar(15) DEFAULT NULL COMMENT 'TIDB',
  `mobile_phone` varchar(15) DEFAULT NULL COMMENT 'TIDB',
  `last_updated_te` varchar(20) DEFAULT NULL COMMENT 'TIDB',
  `last_updated_org` varchar(20) DEFAULT NULL COMMENT 'TIDB',
  `created_ts` datetime DEFAULT '9999-12-31 00:00:00' COMMENT 'TIDB',
  `updated_ts` datetime NOT NULL DEFAULT '9999-12-31 00:00:00' COMMENT 'TIDB',
  `init_system_id` varchar(30) DEFAULT NULL COMMENT 'TIDB',
  `init_created_ts` datetime DEFAULT '9999-12-31 00:00:00' COMMENT 'TIDB',
  `last_system_id` varchar(30) DEFAULT NULL COMMENT 'TIDB',
  `last_updated_ts` datetime DEFAULT '9999-12-31 00:00:00' COMMENT 'TIDB',
  PRIMARY KEY (`rel_id`,`updated_ts`),
  KEY `idx_T01PerRelPerInfo1` (`rel_name`,`cert_type`,`cert_no`,`updated_ts`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='TIDB' |
+----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

MySQL [ecifapp]> show create table t02_per_cust_cust_rel;
+-----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table                 | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+-----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t02_per_cust_cust_rel | CREATE TABLE `t02_per_cust_cust_rel` (
  `cust_rel_id` varchar(20) NOT NULL COMMENT 'TIDB',
  `party_id` varchar(30) NOT NULL COMMENT 'TIDB',
  `party_tab_id` varchar(30) DEFAULT NULL COMMENT 'TIDB',
  `rel_type` varchar(30) DEFAULT NULL COMMENT 'TIDB',
  `rel_id` varchar(30) NOT NULL COMMENT 'TIDB',
  `rel_tab_id` varchar(30) DEFAULT NULL COMMENT 'TIDB',
  `rel_bult_date` date DEFAULT NULL COMMENT 'TIDB',
  `rel_end_date` date DEFAULT NULL COMMENT 'TIDB',
  `remark` varchar(200) DEFAULT NULL COMMENT 'TIDB',
  `last_updated_te` varchar(20) DEFAULT NULL COMMENT 'TIDB',
  `last_updated_org` varchar(20) DEFAULT NULL COMMENT 'TIDB',
  `created_ts` datetime DEFAULT '9999-12-31 00:00:00' COMMENT 'TIDB',
  `updated_ts` datetime NOT NULL DEFAULT '9999-12-31 00:00:00' COMMENT 'TIDB',
  `init_system_id` varchar(30) NOT NULL COMMENT 'TIDB',
  `init_created_ts` datetime DEFAULT '9999-12-31 00:00:00' COMMENT 'TIDB',
  `last_system_id` varchar(30) NOT NULL COMMENT 'TIDB',
  `last_updated_ts` datetime DEFAULT '9999-12-31 00:00:00' COMMENT 'TIDB',
  PRIMARY KEY (`cust_rel_id`,`updated_ts`),
  UNIQUE KEY `uniq_T02PerCustCustRel2` (`party_id`,`rel_type`,`rel_id`,`updated_ts`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='TIDB' |
+-----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

caiyfc · 2024 年3 月 28 日 02:13

Total_keys: 6417338
得把慢sql里面的执行计划贴出来看看

Demo二棉裤 · 2024 年3 月 28 日 02:22

2.0版本的话，没办法看历史执行计划，最多的信息只有上面的慢日志，打印出的信息只能看到是全表扫描

caiyfc · 2024 年3 月 28 日 02:29

如果根据这些信息来看的话，感觉是关联走了hash join导致的，hash join导致t01_per_rel_per_info表走了全表扫描。

Demo二棉裤 · 2024 年3 月 28 日 02:30

猜测这个慢日志打印出来的全表扫，是走了hashjoin，测试了一下，确实是跟慢日志的表现一致，



explain analyze
    -> SELECT /*+ TIDB_HJ(r)*/  r.REL_TYPE as relType,   p.REL_NAME as relName,   p.CERT_TYPE as certType,   p.CERT_NO as certNo,   p.MOBILE_PHONE as mobilePhone  FROM   t02_per_cust_cust_rel r  LEFT JOIN t01_per_rel_per_info p ON r.REL_ID = p.REL_ID AND r.UPDATED_TS = '99991231'  WHERE   r.UPDATED_TS = '99991231'  AND r.REL_TAB_ID = 'TB010201'  AND r.PARTY_ID = '100000000000000000002'  group by r.REL_TYPE,p.REL_NAME,p.CERT_TYPE,p.CERT_NO,p.MOBILE_PHONE  having count(0)>1;
+----------------------------------+------------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------+
| id                               | count      | task | operator info                                                                                                                                                                                                                                                                             | execution info                                |
+----------------------------------+------------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------+
| Projection_9                     | 0.80       | root | ecifapp.r.reltype, ecifapp.p.relname, ecifapp.p.certtype, ecifapp.p.certno, ecifapp.p.mobilephone                                                                                                                                                                                         | time:5.195322276s, loops:1, rows:0            |
| └─Selection_10                   | 0.80       | root | gt(sel_agg_5, 1)                                                                                                                                                                                                                                                                          | time:5.195317202s, loops:1, rows:0            |
|   └─HashAgg_13                   | 1.00       | root | group by:ecifapp.p.cert_no, ecifapp.p.cert_type, ecifapp.p.mobile_phone, ecifapp.p.rel_name, ecifapp.r.rel_type, funcs:count(0), firstrow(ecifapp.r.rel_type), firstrow(ecifapp.p.rel_name), firstrow(ecifapp.p.cert_type), firstrow(ecifapp.p.cert_no), firstrow(ecifapp.p.mobile_phone) | time:5.195308402s, loops:2, rows:3            |
|     └─HashLeftJoin_18            | 0.00       | root | left outer join, inner:TableReader_28, equal:[eq(ecifapp.r.rel_id, ecifapp.p.rel_id)], left cond:[eq(ecifapp.r.updated_ts, 9999-12-31 00:00:00.000000)]                                                                                                                                   | time:5.195192941s, loops:2, rows:3            |
|       ├─IndexLookUp_26           | 0.00       | root |                                                                                                                                                                                                                                                                                           | time:65.688361ms, loops:2, rows:3             |
|       │ ├─Selection_24           | 0.00       | cop  | eq(ecifapp.r.updated_ts, 9999-12-31 00:00:00.000000)                                                                                                                                                                                                                                      |                                               |
|       │ │ └─IndexScan_22         | 7.00       | cop  | table:r, index:party_id, rel_type, rel_id, updated_ts, range:["100000000000000000002","100000000000000000002"], keep order:false                                                                                                                                                            |                                               |
|       │ └─Selection_25           | 0.00       | cop  | eq(ecifapp.r.rel_tab_id, "TB010201")                                                                                                                                                                                                                                                      |                                               |
|       │   └─TableScan_23         | 0.00       | cop  | table:t02_per_cust_cust_rel, keep order:false, stats:pseudo                                                                                                                                                                                                                               |                                               |
|       └─TableReader_28           | 6395662.00 | root | data:TableScan_27                                                                                                                                                                                                                                                                         | time:2.595835059s, loops:199872, rows:6395862 |
|         └─TableScan_27           | 6395662.00 | cop  | table:p, range:[-inf,+inf], keep order:false                                                                                                                                                                                                                                              |                                               |
+----------------------------------+------------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------+

Demo二棉裤 · 2024 年3 月 28 日 02:31

是的，您数的对，我测试加hint走了hashjoin，但是为什么会出现这种情况呢，表的健康度也是正常的，这个返回的结果，基本是固定值，1或者2或者0 ，业务逻辑不会超过10以上。

caiyfc · 2024 年3 月 28 日 02:35

我觉得可能是集群版本太低了，那个时候的优化器并不稳定吧。5.0以上都有可能出现执行计划不稳定的情况（健康度也有90+），需要绑定hint呢。所以建议直接绑定走index join就行了

Billmay表妹 · 2024 年3 月 28 日 02:35

找机会升级 v2 的版本实在太旧啦！

Demo二棉裤 · 2024 年3 月 28 日 02:37

哈哈哈，升级是必要的，今年已经升级了好几十套了，全都是7.5，这不是老系统出问题了，还得查么，总不能不管，哈哈哈

Demo二棉裤 · 2024 年3 月 28 日 02:38

通过绑 /+ TIDB_INLJ(p)/ 执行计划变正确了，但是代码改造困难，研发配合度不高，除了这个，还有别的办法吗

caiyfc · 2024 年3 月 28 日 02:40

2.0有这个吗，我找了一下文档好像没找到：执行计划管理 (SPM) | PingCAP 文档中心

Demo二棉裤 · 2024 年3 月 28 日 02:42

有，是找某位神秘大佬，说2.0有这个写法。SELECT /+ TIDB_INLJ(p)/ r.REL_TYPE as relType, p.REL_NAME as relName, p.CERT_TYPE as certType, p.CERT_NO as certNo,。。。。。。

caiyfc · 2024 年3 月 28 日 02:44

有的话就直接binding一下就行了

Demo二棉裤 · 2024 年3 月 28 日 02:45

涉及改代码了。研发配合很不好，我想gantamen

啦啦啦啦啦 · 2024 年3 月 28 日 02:45

binding貌似3.0才有的，这种不需要改代码

Demo二棉裤 · 2024 年3 月 28 日 02:56

嗯，是的，2.0里面就得直接再select里面写hint，先准备用这个方法搞一下，谢谢各位大佬

友利奈绪 · 2024 年3 月 28 日 03:23

蹲一手最佳回复

小于同学 · 2024 年3 月 29 日 02:47

版本升级一下呢

健康的腰间盘 · 2024 年4 月 1 日 07:43

直接hint优化给卡死走哪个索引