php - 提高性能 select union 命令 mysql

Question

在我的应用程序中，我有一个 SELECT UNION 语句来从 MySQL 数据库获取数据并将数据显示在网页上。这工作正常，但需要大约 4 秒才能将结果显示在页面上。我在网上搜索了提高 SELECT UNION 语句性能的方法，并找到了两种可能的解决方案。 1 UNION ALL 而不是 UNION。 2 将 WHERE 移动到每个子查询内。我已经尝试过这两种方法，但这并不能提高性能。仍然需要大约 4 秒。还有其他可能的解决方案吗？谨致问候，拉特格。

enter link description here

声明:

SELECT a, b, c, d, e FROM (
(SELECT 
tbl_a.field_1 AS a,
tbl_b.field_1 AS b,
tbl_c.field_1 AS c,
tbl_d.field_1 AS d,
tbl_e.field_1 AS e
FROM (((tbl_a INNER JOIN tbl_b ON tbl_a.bkst_id = tbl_b.bkst_id) INNER JOIN tbl_c 
ON tbl_a.rel_id = tbl_c.rel_id) INNER JOIN tbl_d ON tbl_b.rekA_id = tbl_d.rekB_id) 
INNER JOIN tbl_e ON tbl_a.dgbk_id = tbl_e.dgbk_id
WHERE tbl_b.field_1 = 108 AND tbl_c.field_1 >= '0001' AND tbl_c.field_1 <= '9999' AND tbl_d.field_1 = '2017' AND tbl_e.field_1 <> 'n.v.t.') 
UNION ALL
(SELECT 
tbl_a.field_1 AS a,
tbl_b.field_2 AS b,
tbl_c.field_1 AS c,
tbl_d.field_1 AS d,
tbl_e.field_1 AS e
FROM (((tbl_a INNER JOIN tbl_b ON tbl_a.bkst_id = tbl_b.bkst_id) INNER JOIN tbl_c 
ON tbl_a.rel_id = tbl_c.rel_id) INNER JOIN tbl_d ON tbl_b.rekC_id = tbl_d.rekB_id) 
INNER JOIN tbl_e ON tbl_a.dgbk_id = tbl_e.dgbk_id
WHERE tbl_b.field_1 = 108 AND tbl_c.field_1 >= '0001' AND tbl_c.field_1 <= '9999' AND tbl_d.field_1 = '2017' AND tbl_e.field_1 <> 'n.v.t.') 
UNION ALL
(SELECT 
tbl_a.field_1 AS a,
tbl_b.field_3 AS b,
tbl_c.field_1 AS c,
tbl_d.field_1 AS d,
tbl_e.field_1 AS e
FROM (((tbl_a INNER JOIN tbl_b ON tbl_a.bkst_id = tbl_b.bkst_id) INNER JOIN tbl_c 
ON tbl_a.rel_id = tbl_c.rel_id) INNER JOIN tbl_d ON tbl_b.rekB_id = tbl_d.rekB_id)
INNER JOIN tbl_e ON tbl_a.dgbk_id = tbl_e.dgbk_id
WHERE tbl_b.field_1 = 108 AND tbl_c.field_1 >= '0001' AND tbl_c.field_1 <= '9999' AND tbl_d.field_1 = '2017' AND tbl_e.field_1 <> 'n.v.t.')
UNION ALL
(SELECT 
tbl_a.field_1 AS a,
tbl_b.field_4 AS b,
tbl_c.field_1 AS c,
tbl_d.field_1 AS d,
tbl_e.field_1 AS e
FROM (((tbl_a INNER JOIN tbl_b ON tbl_a.bkst_id = tbl_b.bkst_id) INNER JOIN tbl_c 
ON tbl_a.rel_id = tbl_c.rel_id) INNER JOIN tbl_d ON tbl_b.rekD_id = tbl_d.rekB_id)
INNER JOIN tbl_e ON tbl_a.dgbk_id = tbl_e.dgbk_id
WHERE tbl_b.field_1 = 108 AND tbl_c.field_1 >= '0001' AND tbl_c.field_1 <= '9999' AND tbl_d.field_1 = '2017' AND tbl_e.field_1 <> 'n.v.t.')) AS result
WHERE result.b = 108 AND result.c >= '0001' AND result.c <= '9999' AND result.d = '2017' AND result.e <> 'n.v.t.' 
ORDER BY result.a

Answer 1

您可以尝试在“where”子句和“join”子句涉及的列上放置一些索引。

https://dev.mysql.com/doc/refman/5.7/en/create-index.html