temp view的查询很多时候比inner join快很多，请问是啥原因

OceanBase
#1

【产品名称】

OceanBase

【产品版本】

3.1.2

【问题描述】

两个亿级record的表之间inner join，是因为没有设定表组导致大表join产生跨机查询导致的嘛？

#2

explain extended_noaddr 看看执行计划呢.

#3

technologyofcorporation为3300万records的表，technology为1.2亿records的表，其中technologyofcorporation的sort_value加了btree索引。

inner join执行计划：

EXPLAIN SELECT * FROM technologyofcorporation INNER JOIN technology WHERE technologyofcorporation.corporation_id = 'corporation:02d78b81419759e3de9219fefefe796681a6a17c' AND technologyofcorporation.technology_id = technology.id ORDER BY technologyofcorporation.sort_value DESC LIMIT 0,3;

temp view执行计划：

EXPLAIN SELECT * FROM technology INNER JOIN (SELECT technologyofcorporation.technology_id FROM technologyofcorporation WHERE technologyofcorporation.corporation_id = 'corporation:02d78b81419759e3de9219fefefe796681a6a17c' ORDER BY technologyofcorporation.sort_value DESC LIMIT 0,3) AS t WHERE t.technology_id=technology.id;

#4

索引改成：corporation_id + sort_value哪？

1 个赞