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

【 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 操作,强制走索引操作都试过,速度还是非常的慢。有没有大佬能给解答下为什么。

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

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

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

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

看得真仔细~ :+1: :smiley_cat:

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

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

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

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

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

1 个赞

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

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

SHOW STATS_BUCKETS where Column_name=‘hzid’;

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

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


hz_jzjbxx,这个列的 Lower_Bound 和 Upper_Bound 都是空的?

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

255个bucket_id都是空的

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

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

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