【产品名称】
OceanBase
【产品版本】
3.1.2
【问题描述】
两个亿级record的表之间inner join,是因为没有设定表组导致大表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哪?