大表关联查询 全表扫描问题

OceanBase 技术问题
#1

【 使用环境 】生产环境
【 OB or 其他组件 】OB
【 使用版本 】3.2.2
【问题描述】标题表关联内容表，总是会触发内容表的全表扫描
【复现路径】
测试语句:
SELECT b.title,a.new FROM title b join new a ON b.INFOCODE = a.INFOCODE
WHERE b.id = 83 AND b.is_deleted = 0

=================================================
|ID|OPERATOR         |NAME|EST.ROWS|EST.TIME(us)|
-------------------------------------------------
|0 |HASH JOIN        |    |1       |7           |
|1 |├─TABLE GET      |b   |1       |3           |
|2 |└─TABLE FULL SCAN|a   |1       |4           |
=================================================
Outputs & filters:
-------------------------------------
  0 - output([b.title], [a.new]), filter(nil)
      equal_conds([b.INFOCODE = a.INFOCODE]), other_conds(nil)
  1 - output([b.INFOCODE], [b.title]), filter([b.is_deleted = 0])
      access([b.INFOCODE], [b.is_deleted], [b.title]), partitions(p0)
      is_index_back=false, is_global_index=false, filter_before_indexback[false], 
      range_key([b.id]), range[83 ; 83], 
      range_cond([b.id = 83])
  2 - output([a.INFOCODE], [a.new]), filter(nil)
      access([a.INFOCODE], [a.new]), partitions(p0)
      is_index_back=false, is_global_index=false, 
      range_key([a.id]), range(MIN ; MAX)always true

测试表与测试数据.txt (3.5 KB)

#3

这个没问题啊，B表因为有过滤条件里面有B.ID=83所以能走上table_get，但是A表没有任何过滤条件和索引，所以是table full scan

1 个赞
#4

这个是符合预期的，HASH JOIN的适用场景就是大表关联且不使用索引，这里title表走了点查，是因为b.id = 83，id是主键

#5

可以参考下

https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000001432426

image
image1824×540 165 KB

#6

请问一下有没有好的优化方案
生产这个内容表150G 查询一次需要一分钟
同sql在mysql里却能执行的很好 在title表中获取到code之后再拿code通过索引查询内容表

#7

但是我的A表a.INFOCODE 有索引

#8

你给的这个case实际上查询结果为空，你实际环境的执行信息麻烦取一下

参考
SQL性能问题信息收集，默认不需要添加任何参数的话收集最近30分钟的信息
obdiag gather scene run --scene=observer.perf_sql --env “{db_connect=’-h127.0.0.1 -P2881 -utest@test -p****** -Dtest’, trace_id=‘Yxx’}”

https://www.oceanbase.com/docs/common-obdiag-cn-1000000001326882

#9

好吧，你给的建表语句里面没有索引信息，最简单的优化方式就是加并行，加快数据扫描的速度。
你说的title表的连接列无法走上索引这个问题，其实还得看索引是怎么建的，如果索引列只有INFOCODE优化器不选择也正常，毕竟还要回表，如果建一个INFOCEDE和title列的联合索引应该能用上 :joy:

#10

mysql 的计划用的是什么 join？nest loop join 还是 hash join？

mysql 的查询时间是多久？

#11

如果走的是 hash join，根据 hash join 的实现原理，b 表用过滤之后的数据建了 hash 表，因为 a 表没有过滤条件，所以得让 a 表拿每一行都去 hash 表里探测一把，肯定没法儿走索引，是符合预期的。

你先手动收集下统计信息，看看是不是还是这个计划。

如果还是这个计划的话，手动加 hint 改成 nlj 试试会不会让性能更好？

obclient [test]> explain SELECT /*+leading(b a) use_nl(a)*/ b.title,a.new FROM title b join new a ON b.INFOCODE = a.INFOCODE WHERE b.id = 83 AND b.is_deleted = 0;
+------------------------------------------------------------------------------------+
| Query Plan                                                                         |
+------------------------------------------------------------------------------------+
| ==========================================================================         |
| |ID|OPERATOR                      |NAME            |EST.ROWS|EST.TIME(us)|         |
| --------------------------------------------------------------------------         |
| |0 |NESTED-LOOP JOIN              |                |1       |24          |         |
| |1 |├─TABLE GET                   |b               |1       |3           |         |
| |2 |└─DISTRIBUTED TABLE RANGE SCAN|a(idx_info_code)|1       |21          |         |
| ==========================================================================         |
| Outputs & filters:                                                                 |
| -------------------------------------                                              |
|   0 - output([b.title], [a.new]), filter(nil)                                      |
|       conds(nil), nl_params_([b.INFOCODE(:0)]), use_batch=true                     |
|   1 - output([b.INFOCODE], [b.title]), filter([b.is_deleted = 0])                  |
|       access([b.INFOCODE], [b.is_deleted], [b.title]), partitions(p0)              |
|       is_index_back=false, is_global_index=false, filter_before_indexback[false],  |
|       range_key([b.id]), range[83 ; 83],                                           |
|       range_cond([b.id = 83])                                                      |
|   2 - output([a.new]), filter(nil)                                                 |
|       access([GROUP_ID], [a.id], [a.new]), partitions(p0)                          |
|       is_index_back=true, is_global_index=false,                                   |
|       range_key([a.INFOCODE], [a.id]), range(MIN ; MAX),                           |
|       range_cond([:0 = a.INFOCODE])                                                |
+------------------------------------------------------------------------------------+
21 rows in set (0.005 sec)

还有就是 idx_info_code 这个索引需要回表，可以把这个索引改成 KEY idx_info_code (INFOCODE, new) 让他不回表。哦哦，不行，new 列还是个大对象，建不了索引……忽略改索引这个吧。

1 个赞
#12

hash join 的右表数据在进行 hash 探测时，是用不了索引的。因为这时候左表的数据已经放在 hash 表里了，只能逐行探测。

改成 nlj 就能用索引了，不过还是有索引回表的问题。new 这个字段这么大吗？varchar 的长度上限也不小呦，看看能不能把 new 的数据类型改成 varchar，哈哈~ https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000001433673

1 个赞