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

【产品名称】

OceanBase

【产品版本】

3.1.2

【问题描述】

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

explain extended_noaddr 看看执行计划呢.

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;

索引改成:corporation_id + sort_value哪?

1 个赞