表量级相当索引都有的情况，简单的调换表的顺序，执行效率差异很大。

liuzx8888 · 2022 年11 月 22 日 11:01

【 TiDB 使用环境】测试
【 TiDB 版本】6.3.0
【遇到的问题：问题现象及影响】
表量相当的2张表，换了下前后顺序执行的执行计划差异很大，需要怎么去调整：

JZ_MJZ_JZJL2 聚集索引（jzxh，jzlb，yljgdm) 表数据量： 17499037 条
HZ_JZJBXX 聚集索引（jzxh，jzlb，yljgdm) 表数据量： 17147543 条
只是简单的换了下顺序，
关联语句
query1：

   select ....
    from JZ_MJZ_JZJL2   a
    inner  join HZ_JZJBXX  hz  on    a.jzxh=hz.jzxh and a.jzlb=hz.jzlb   and a.yljgdm=hz.yljgdm 
    where  a.hzid= '728615'

query2：

   select ....
    from  HZ_JZJBXX      a
    inner  join JZ_MJZ_JZJL2 hz  on    a.jzxh=hz.jzxh and a.jzlb=hz.jzlb   and a.yljgdm=hz.yljgdm 
    where  a.hzid= '728615'

表结构： JZ_MJZ_JZJL2

+------------------------+---------------+------+------+----------------------+-------+
| Field                  | Type          | Null | Key  | Default              | Extra |
+------------------------+---------------+------+------+----------------------+-------+
| bxh                    | bigint(20)    | NO   |      | NULL                 |       |
| yljgdm                 | varchar(20)   | NO   | PRI  | NULL                 |       |
| yljgmc                 | varchar(50)   | YES  |      | NULL                 |       |
| jzxh                   | varchar(36)   | NO   | PRI  | NULL                 |       |
| jzlb                   | varchar(4)    | NO   | PRI  | NULL                 |       |
| hzid                   | varchar(36)   | YES  | MUL  | NULL                 |       |
| ghjslsh                | varchar(36)   | YES  |      | NULL                 |       |
| ghsj                   | datetime(3)   | YES  |      | NULL                 |       |
| cfzbz                  | varchar(4)    | YES  |      | NULL                 |       |
| yyghbz                 | varchar(4)    | YES  |      | NULL                 |       |
| mzyyxh                 | varchar(36)   | YES  |      | NULL                 |       |
| ghksdm                 | varchar(20)   | YES  |      | NULL                 |       |
| ghksmc                 | varchar(50)   | YES  |      | NULL                 |       |
| ghysdm                 | varchar(20)   | YES  |      | NULL                 |       |
| ghysmc                 | varchar(50)   | YES  |      | NULL                 |       |
| ghfsdm                 | varchar(20)   | YES  |      | NULL                 |       |
| ghfsmc                 | varchar(50)   | YES  |      | NULL                 |       |
| ghlbdm                 | varchar(20)   | YES  |      | NULL                 |       |
| ghlbmc                 | varchar(50)   | YES  |      | NULL                 |       |
| txmzbz                 | varchar(4)    | YES  |      | NULL                 |       |
| ghjlzt                 | varchar(4)    | YES  |      | NULL                 |       |
| tjzxh                  | varchar(36)   | YES  |      | NULL                 |       |
| thsj                   | datetime(3)   | YES  |      | NULL                 |       |
| ghczydm                | varchar(20)   | YES  |      | NULL                 |       |
| ghczymc                | varchar(50)   | YES  |      | NULL                 |       |
| ghhx                   | varchar(20)   | YES  |      | NULL                 |       |
| jzztdm                 | varchar(20)   | YES  |      | NULL                 |       |
| jzztmc                 | varchar(50)   | YES  |      | NULL                 |       |
| zjscjzsj               | datetime(3)   | YES  |      | NULL                 |       |
| zjecjzsj               | datetime(3)   | YES  |      | NULL                 |       |
| zjzhjzsj               | datetime(3)   | YES  |      | NULL                 |       |
| jzksdm                 | varchar(20)   | YES  |      | NULL                 |       |
| jzksmc                 | varchar(50)   | YES  |      | NULL                 |       |
| scjzysdm               | varchar(20)   | YES  |      | NULL                 |       |
| scjzysmc               | varchar(50)   | YES  |      | NULL                 |       |
| zhjzysdm               | varchar(20)   | YES  |      | NULL                 |       |
| zhjzysmc               | varchar(50)   | YES  |      | NULL                 |       |
| sfzlqj                 | varchar(4)    | YES  |      | NULL                 |       |
| sfqjcg                 | varchar(4)    | YES  |      | NULL                 |       |
| sfsw                   | varchar(4)    | YES  |      | NULL                 |       |
| termflag               | varchar(4)    | YES  |      | NULL                 |       |
| termfailedcolumn       | varchar(500)  | YES  |      | NULL                 |       |
| termfailedmessage      | varchar(5000) | YES  |      | NULL                 |       |
| termsuccesscolumn      | varchar(500)  | YES  |      | NULL                 |       |
| source                 | varchar(50)   | YES  |      | NULL                 |       |
| source_tablename       | varchar(50)   | YES  |      | NULL                 |       |
| source_key             | varchar(500)  | YES  |      | NULL                 |       |
| source_keyvalue        | varchar(500)  | YES  |      | NULL                 |       |
| isdelete               | varchar(4)    | YES  |      | NULL                 |       |
| source_lastupdate      | datetime(3)   | YES  |      | NULL                 |       |
| destination_lastupdate | datetime(3)   | YES  |      | NULL                 |       |
| timestp                | varbinary(50) | YES  |      | NULL                 |       |
| pch                    | varchar(20)   | YES  |      | NULL                 |       |
| title_code             | varchar(50)   | YES  |      | NULL                 |       |
| yydm                   | varchar(50)   | YES  |      | NULL                 |       |
| ghf                    | decimal(5,2)  | YES  |      | NULL                 |       |
| fylb                   | varchar(32)   | YES  |      | NULL                 |       |
| ghrq                   | datetime(3)   | YES  |      | NULL                 |       |
| swsj                   | datetime(3)   | YES  |      | CURRENT_TIMESTAMP(3) |       |
| trial674               | char(1)       | YES  |      | NULL                 |       |
+------------------------+---------------+------+------+----------------------+-------+

表结构： HZ_JZJBXX

+------------------------+--------------+------+------+---------+-------+
| Field                  | Type         | Null | Key  | Default | Extra |
+------------------------+--------------+------+------+---------+-------+
| bxh                    | bigint(20)   | YES  |      | NULL    |       |
| yljgdm                 | varchar(50)  | NO   | PRI  | NULL    |       |
| yljgmc                 | varchar(50)  | NO   |      | NULL    |       |
| jzxh                   | varchar(36)  | NO   | PRI  | NULL    |       |
| jzlb                   | varchar(4)   | NO   | PRI  | NULL    |       |
| hzid                   | varchar(36)  | YES  | MUL  | NULL    |       |
| blh                    | varchar(36)  | YES  | MUL  | NULL    |       |
| hzxm                   | varchar(128) | YES  | MUL  | NULL    |       |
| jsm                    | varchar(255) | YES  |      | NULL    |       |
| hzxbdm                 | varchar(128) | YES  |      | NULL    |       |
| hzxbmc                 | varchar(128) | YES  |      | NULL    |       |
| hzcsrq                 | date         | YES  |      | NULL    |       |
| hzsfzh                 | varchar(36)  | YES  | MUL  | NULL    |       |
| hyzkdm                 | varchar(32)  | YES  |      | NULL    |       |
| hyzkmc                 | varchar(128) | YES  |      | NULL    |       |
| mzdm                   | varchar(32)  | YES  |      | NULL    |       |
| mzmc                   | varchar(128) | YES  |      | NULL    |       |
| gjdm                   | varchar(32)  | YES  |      | NULL    |       |
| gjmc                   | varchar(128) | YES  |      | NULL    |       |
| zydm                   | varchar(32)  | YES  |      | NULL    |       |
| zymc                   | varchar(128) | YES  |      | NULL    |       |
| whcddm                 | varchar(32)  | YES  |      | NULL    |       |
| whcdmc                 | varchar(128) | YES  |      | NULL    |       |
| hzgzdwmc               | varchar(128) | YES  |      | NULL    |       |
| hzgddh                 | varchar(32)  | YES  |      | NULL    |       |
| hzyddh                 | varchar(32)  | YES  |      | NULL    |       |
| hzemail                | varchar(50)  | YES  |      | NULL    |       |
| ynkh                   | varchar(36)  | YES  | MUL  | NULL    |       |
| ybkh                   | varchar(36)  | YES  | MUL  | NULL    |       |
| djsj                   | datetime     | YES  |      | NULL    |       |
| djczydm                | varchar(32)  | YES  |      | NULL    |       |
| djczymc                | varchar(128) | YES  |      | NULL    |       |
| jlzt                   | varchar(4)   | YES  |      | NULL    |       |
| termflag               | varchar(4)   | YES  |      | NULL    |       |
| termfailedcolumn       | text         | YES  |      | NULL    |       |
| termfailedmessage      | text         | YES  |      | NULL    |       |
| termsuccesscolumn      | text         | YES  |      | NULL    |       |
| source                 | varchar(50)  | YES  |      | NULL    |       |
| source_tablename       | varchar(50)  | YES  |      | NULL    |       |
| source_key             | text         | YES  |      | NULL    |       |
| source_keyvalue        | text         | YES  |      | NULL    |       |
| isdelete               | varchar(4)   | YES  |      | NULL    |       |
| source_lastupdate      | datetime     | YES  |      | NULL    |       |
| destination_lastupdate | datetime     | YES  |      | NULL    |       |
| timestp                | longblob     | NO   |      | NULL    |       |
| pch                    | varchar(32)  | YES  |      | NULL    |       |
| nl                     | int(11)      | YES  |      | NULL    |       |
| jzdz                   | text         | YES  |      | NULL    |       |
| yydm                   | varchar(50)  | YES  |      | NULL    |       |
| jzcs                   | int(11)      | YES  |      | NULL    |       |
+------------------------+--------------+------+------+---------+-------+

【资源配置】

【附件：截图/日志/监控】
query1 的执行计划

query2 的执行计划

从图中很明显的能看出来，一个走了表扫描最慢的方式，一个直接走了索引，下推了记录。表的量级相当，索引对于关联键完全匹配的上，为什么query1的索引无效。

ANALYZE TABLE 操作，强制走索引操作都试过，速度还是非常的慢。有没有大佬能给解答下为什么。

h5n1 · 2022 年11 月 22 日 13:19

2个表的表结构都发下,试试 INL_JOIN(内表) HINT

buddyyuan · 2022 年11 月 22 日 14:11

使用 PLAN REPLAYER 收集一下信息，上传一下。

https://docs.pingcap.com/zh/tidb/stable/sql-plan-replayer/#plan-replayer-导出示例

ddhe9527 · 2022 年11 月 23 日 01:39

你这两个SQL完全不一样好不好，你只是把表改了，别名没改，那最后那个where条件，query1就是where JZ_MJZ_JZJL2.hzid=XXXX, query2就是HZ_JZJBXX.hzid=XXX，两个SQL的where条件施加的目标表都不一样

xfworld · 2022 年11 月 23 日 01:51

看得真仔细～

liuzx8888 · 2022 年11 月 23 日 02:00

2个表都有主键，而且关连建都是聚集索引，hzid都有索引，一个没走索引，一个走了索引，跟这个where 有很大的关系？理论上这不应该都应该走索引的么，有一个直接是表扫描。

liuzx8888 · 2022 年11 月 23 日 02:09

已经补充了表结果，麻烦铁汁再帮忙看看

ddhe9527 · 2022 年11 月 23 日 02:16

where条件施加的表不一样，会影响表的JOIN方式和驱动表顺序

liuzx8888 · 2022 年11 月 23 日 02:27

PLAN REPLAYER 已经上传：
query1.zip (315.4 KB)
query2.zip (315.4 KB)

h5n1 · 2022 年11 月 23 日 02:57

虽然你的2个SQL 别名是不同的表，但2个表结构类似，主键一样。估计优化器缺陷，之前也碰上过更改关联表出现的顺序后执行计划走了全表。

胡杨树旁 · 2022 年11 月 23 日 03:34

按理说where 条件后面有索引的话应该会走到索引吧

xfworld · 2022 年11 月 23 日 06:18

这是是有代价引擎（CBO）来决定的了
索引，还是扫表，都会有相应的统计信息和数据来支持了，不准的话，就难说了

buddyyuan · 2022 年11 月 23 日 06:27

SHOW STATS_BUCKETS where Column_name=‘hzid’;

麻烦执行一下输出结果看看。

buddyyuan · 2022 年11 月 23 日 06:33

我看Lower_Bound 和 Upper_Bound 一个是半角字符的数据，一个是全角字符的数据。你看看你那边是不是这样。

liuzx8888 · 2022 年11 月 23 日 06:47

嗯

buddyyuan · 2022 年11 月 23 日 07:06

hz_jzjbxx，这个列的 Lower_Bound 和 Upper_Bound 都是空的？

胡杨树旁 · 2022 年11 月 23 日 07:16

想问下这条SQL 是查询统计信息桶的情况吗？ show stats_histograms 和这个有啥区别，一套4.0版本的数据库 show stats_histograms 表的列显示不全，重新收集统计信息也显示不全，不知道这个是版本的问题还是啥问题

liuzx8888 · 2022 年11 月 23 日 07:32

255个bucket_id都是空的

buddyyuan · 2022 年11 月 23 日 09:31

show stats_histograms 可以显示每一个列的 Distinct_count ，也可以称作选择率。通过这个你可以评估是否可以在这个列上创建索引。

SHOW STATS_BUCKETS 是真正的查看直方图每个桶的信息。这应该才是真正的直方图的信息。

buddyyuan · 2022 年11 月 23 日 09:31

重新收集一下这个表的统计信息试试。