mysql - 在 MYSQL View 中对多个相似表进行排序

Question

我都，我有 2 个类似的非常大的表(每个 1M 行)具有相同的布局，我将合并它们并按公共(public)列排序: start 。我还会在“start”中添加一个条件，即:start>X。问题是 View 不关心start的索引，复杂度上升很多，一个简单的查询大约需要15秒，插入LIMIT并不能解决问题，因为结果首先被切断。

CREATE VIEW CDR AS
(SELECT start,    duration, clid, FROM cdr_md ORDER BY start LIMIT 1000) 
 UNION ALL 
 (SELECT start,       duration, clid, FROM cdr_1025 ORDER BY start LIMIT 1000) 
 ORDER BY start ;

查询:

SELECT * FROM CDR WHERE start>10

不会返回预期结果，因为 LIMIT 关键字会先截断结果。

预期的结果将是这样的查询:

CREATE VIEW CDR AS
(SELECT start,    duration, clid, FROM cdr_md WHERE start>X ORDER BY start LIMIT 1000) 
UNION ALL 
(SELECT start,    duration, clid, FROM cdr_1025 WHERE start>X ORDER BY start LIMIT 1000) 
 ORDER BY start ;

有办法避免这个问题吗？谢谢大家法布里齐奥

Answer 1

i have 2 similar table ... with the same layout

这违反了Principle of Orthogonal Design .

不要这样做。至少不是没有非常充分的理由——使用合适的索引，每个表 100 万条记录足以让 MySQL 轻松处理，无需分区；即使确实需要对数据进行分区，也有better ways比这种手动拼凑(这可能会产生不明确的、可能不一致的数据，并导致数据操作代码冗余和复杂)。

相反，请考虑将您的表合并为一个具有合适列的表，以区分记录的差异。例如:

CREATE TABLE cdr_combined AS
  SELECT *, 'md'   AS orig FROM cdr_md
UNION ALL
  SELECT *, '1025' AS orig FROM cdr_1025
;

DROP TABLE cdr_md, cdr_1025;

如果您始终沿着先前“分区”的轴查看数据，请包含区分列作为索引前缀，与使用单独的表相比，性能通常会提高。

然后，您将不需要执行任何 UNION 并且您的 VIEW 定义实际上变为:

CREATE VIEW CDR AS
SELECT start, duration, clid, FROM cdr_combined ORDER BY start

但是，请注意，对 View 的查询可能并不总是能够像直接使用基础表一样执行。如 Restrictions on Views 下所述:

View processing is not optimized:

• It is not possible to create an index on a view.

• Indexes can be used for views processed using the merge algorithm. However, a view that is processed with the temptable algorithm is unable to take advantage of indexes on its underlying tables (although indexes can be used during generation of the temporary tables).