mysql - 如何优化此 VIEW 语句？

Question

我有两个表 table1 和 table2。表 1 由解析器生成，每隔几天自动更新一次。表 2 是表 1 的用户编辑版本。如果 table2 中存在一条记录，则该记录应该覆盖 View 中 table1 中的记录。

table2 中的任何编辑版本分别在 OldColumn2 和 OldColumn3 中具有来自 table1 的 Column2 和 Column3 的原始内容。当用户删除记录时，删除的列的值为 1，如果用户想再次添加记录，则该列的值为 0 以显示在 View 中。如果从未被删除，默认值为 NULL。

如果一条新记录插入到table2中，OldColumn2和OldColumn3的值都是new字符串，以区别table1中不存在该记录。

这是我的 table 的设计。

Table1
+------+------+------+-------------+
| Col1 | Col2 | Col3 | OtherColumns|
+------+------+------+-------------+
|   a1 |   b1 |   c1 |    Data     |
+------+------+------+-------------+
|   a2 |   b2 |   c2 |    Data     |
+------+------+------+-------------+
|   a3 |   b3 |   c3 |    Data     |
+------+------+------+-------------+
|   a4 |   b4 |   c4 |    Data     |
+------+------+------+-------------+


Table2
+------+------+------+-------------+----------+----------+---------+
| Col1 | Col2 | Col3 | OtherColumns| OldCol2  |  OldCol3 | Deleted |
+------+------+------+-------------+----------+----------+---------+
|  a1  |   e1 |  f1  |   Data      |    b1    |     c1   |   NULL  |
+------+------+------+-------------+----------+----------+---------+
|  a2  |   k2 |  m2  |   Data      |    b2    |     c2   |   0     |
+------+------+------+-------------+----------+----------+---------+
|  a3  |   k3 |  m3  |   Data      |    b3    |     c3   |   1     |
+------+------+------+-------------+----------+----------+---------+
|  z1  |   kk |  jj  |   Data      |   new    |   new    |   1     |
+------+------+------+-------------+----------+----------+---------+
|  z2  |   kj |  uu  |   Data      |   new    |   new    |   0     |
+------+------+------+-------------+----------+----------+---------+

    View
+------+------+------+-------------+----------+----------+---------+
| Col1 | Col2 | Col3 | OtherColumns| OldCol2  |  OldCol3 | Deleted |
+------+------+------+-------------+----------+----------+---------+
|  a1  |   e1 |  f1  |   Data      |    b1    |     c1   |   NULL  |
+------+------+------+-------------+----------+----------+---------+
|  a2  |   k2 |  m2  |   Data      |    b2    |     c2   |   0     |//Deleted then added
+------+------+------+-------------+----------+----------+---------+
|  a4  |   k4 |  j4  |   Data      |    NULL  |     NULL |   0     |
+------+------+------+-------------+----------+----------+---------+
|  z2  |   kj |  uu  |   Data      |   new    |   new    |   0     |
+------+------+------+-------------+----------+----------+---------+

这是我的观点。

CREATE VIEW NEWVIEW
AS
SELECT t2.* FROM table1 t1
LEFT JOIN table2 t2
ON t1.column1 = t2.column1 AND t1.column2 = t2.oldColumn2 AND t1.column3 = t2.oldColumn3
WHERE t2.column1 IS NOT NULL AND t2.Deleted = 0

UNION

SELECT t1.*, null, null, null FROM table1 t1
LEFT JOIN table2 t2
ON t1.column1 = t2.column1 AND t1.column2 = t2.oldColumn2 AND t1.column3 = t2.oldColumn3
WHERE t2.column1 IS NULL

UNION

SELECT * FROM table2 WHERE oldColumn2 = 'new' AND oldColumn3 = 'new' AND Deleted = 0

现在的 View 有点慢。如何优化此 View ？

Answer 1

尝试用这个更改 View 中的前两个 SELECT 语句 -

SELECT
  t1.column1,
  t1.column2,
  t1.column3,
  IF(t2.column1 IS NULL, t1.OtherColumns, t2.OtherColumns) OtherColumns,
  IF(t2.column1 IS NULL, NULL, t2.OldCol2) OldCol2,
  IF(t2.column1 IS NULL, NULL, t2.OldCol3) OldCol3,
  IF(t2.column1 IS NULL, NULL, t2.Deleted) Deleted
FROM
  table1 t1
LEFT JOIN table2 t2
  ON t1.column1 = t2.column1 AND t1.column2 = t2.oldColumn2 AND t1.column3 = t2.oldColumn3
WHERE 
  t2.column1 IS NULL OR t2.column1 IS NOT NULL AND t2.Deleted = 0

请注意， View 的性能可能不佳。