mysql - 在多列上连接大量表的策略？

Question

我知道我可以通过编写简单的连接轻松地连接 2-3 个小表。但是，当您有 7-8 个表，行数超过 2000 万，在 1-3 列上进行连接时，这些连接会变得非常慢，即使你有正确的索引。此外，查询也变得又长又难看。

是否有替代策略来进行如此大的连接，最好是与数据库无关？

编辑

这是连接的伪代码。请注意，某些表在用于连接之前可能必须先取消透视 -

select * from
    (select c1,c2,c3... From t1 where) as s1
inner join 
    (select c1,... From t2 where) as s2
inner join
    (unpivot table to get c1,c2... From t3 where) as s3
inner join 
    (select c1,c2,c3... From t2 where) as s4
on
    (s1.c1 = s2.c1)
and
    (s1.c1 = s3.c1 and s1.c2 = s3.c2)
and
    (s1.c1 = s4.c1 and s2.c2 = s4.c2 and s1.c3 = s4.c3)

显然，这很复杂且丑陋。有没有一种方法可以在不使用如此复杂的联​​接的情况下以更简洁的方式获得相同的结果集？

Answer 1

“7-8 table ”听起来一点也不担心。现代 RDBMS 可以处理更多很多。您的伪代码查询可以从根本上简化为这种形式:

SELECT a.c1 AS a_c1, a.c2 AS a_c2, ...  -- use column aliases ...
      ,b.c1, b.c2, ...  -- .. If you really have same names more than once
      ,c.c1, c.c2, ...
      ,d.c1, d.c2, ...
FROM   t1 a
JOIN   t2 b  USING (c1)
JOIN  (unpivot table to get c1,c2... From t3 where) c USING (c1,c2)
JOIN   t2 d ON d.c1 = a.c1 AND d.c2 = b.c2 AND d.c3 = d.c3
WHERE <some condition on a>
AND   <more conditions> ..

只要匹配的列名在 JOIN 的 left 表中是明确的，USING 语法就会缩短代码。如果有什么不明确的地方，请使用我上一个连接条件中演示的显式形式。这都是标准 SQL，但根据 this Wikipedia page :

The USING clause is not supported by MS SQL Server and Sybase.

在大多数 RDBMS 的伪代码中使用所有这些子查询 是没有意义的。查询规划器找到应用条件和自己获取列的最佳方式。智能查询规划器还会以他们认为合适的任何顺序重新排列表，以达成快速查询计划。

此外，所谓的“数据库不可知论” 仅存在于理论上。主要的 RDBMS 都没有完全实现 SQL 标准，它们都有不同的弱点和长处。您必须优化您的 RDBMS 或充其量获得平庸的性能。

索引策略非常重要。 2000 万行在 SELECT 中并不重要，只要我们可以从索引中塞满一手行指针即可。索引策略在很大程度上取决于您的 RDBMS 品牌。列:

• 您加入，
• 有 WHERE 条件，
• 或用于ORDER BY

可能受益于索引。

还有各种类型的索引，针对不同的需求而设计。 B 树、GIN、GiST、.部分，多列，功能，覆盖。各种运算符类。要优化性能，您只需要了解 RDBMS 的基础知识和功能。 The excellent PostgreSQL manual on indexes to give you an overview.