mysql - 缓慢的 MySQL 查询

Question

以下 SQL 查询大约需要 1 分钟。 15 秒 直接输入 SQL 控制台时执行。 qv_attribute表中有6636条记录。

这个查询的执行时间是否正常？有什么方法可以通过更改数据库设置或优化查询来减少运行此操作所需的时间吗？

SELECT a.node_id,
MAX(CASE WHEN(b.att_id = 9003) THEN a.att_description END) AS 'ICS_Owner',
MAX(CASE WHEN(b.att_id = 9004) THEN a.att_description END) AS 'Service',
MAX(CASE WHEN(t.rel_id = 12092) THEN t.att_description END) AS 'Contact_Name',
MAX(CASE WHEN(t.rel_id = 12610) THEN t.att_description END) AS 'Address_1',
MAX(CASE WHEN(t.rel_id = 9007) THEN t.att_description END) AS 'Address_2',
MAX(CASE WHEN(t.rel_id = 12612) THEN t.att_description END) AS 'City',
MAX(CASE WHEN(t.rel_id = 12614) THEN t.att_description END) AS 'State',
MAX(CASE WHEN(t.rel_id = 13508) THEN t.att_description END) AS 'a',
MAX(CASE WHEN(t.rel_id = 13509) THEN t.att_description END) AS 'b',
MAX(CASE WHEN(b.att_id = 13132) THEN a.att_description END) AS 'Description',
MAX(CASE WHEN(des.node_id = 10824) THEN des.att_description END) AS 'Row_Description'
FROM `qv_attribute` t, `qv_attribute` a, `qv_attribute` b, `qv_attribute` des
        WHERE b.att_id IN (9003,9004,13132,14542)
        AND b.att_name = a.att_name
        AND ((a.node_id = 1554 AND
                ((t.node_id = 1776 AND des.att_order = 1)
                OR (t.node_id = 1780 AND des.att_order = 2)
                OR (t.node_id = 1781 AND des.att_order = 3)
                OR (t.node_id = 1782 AND des.att_order = 4)
                OR (t.node_id = 1783 AND des.att_order = 5)
                OR (t.node_id = 1784 AND des.att_order = 6)
                OR (t.node_id = 1778 AND des.att_order = 7)
                OR (t.node_id = 1777 AND des.att_order = 8)))
            OR (a.node_id = 1574 AND
                ((t.node_id = 1574 AND des.att_order = 1)
                OR (t.node_id = 1779 AND des.att_order = 2)
                OR (t.node_id = 1576 AND des.att_order = 3)
                OR (t.node_id = 1577 AND des.att_order = 4)
                OR (t.node_id = 1710 AND des.att_order = 5)
                OR (t.node_id = 1711 AND des.att_order = 6)
                OR (t.node_id = 1712 AND des.att_order = 7)))
            OR (a.node_id = 1803 AND
                ((t.node_id = 1838 AND des.att_order = 1)
                OR (t.node_id = 1839 AND des.att_order = 2)
                OR (t.node_id = 1840 AND des.att_order = 3)
                OR (t.node_id = 1805 AND des.att_order = 4)))
            OR (a.node_id = 1831 AND
                ((t.node_id = 1842 AND des.att_order = 1)
                OR (t.node_id = 1847 AND des.att_order = 2)
                OR (t.node_id = 1833 AND des.att_order = 3)
                OR (t.node_id = 1848 AND des.att_order = 4)
                OR (t.node_id = 1845 AND des.att_order = 5)
                OR (t.node_id = 1846 AND des.att_order = 6)
                OR (t.node_id = 1841 AND des.att_order = 7)
                OR (t.node_id = 1844 AND des.att_order = 8)
                OR (t.node_id = 1843 AND des.att_order = 9)))
            OR (a.node_id = 1810 AND
                ((t.node_id = 1854 AND des.att_order = 1)
                OR (t.node_id = 1849 AND des.att_order = 2)
                OR (t.node_id = 1851 AND des.att_order = 3)
                OR (t.node_id = 1853 AND des.att_order = 4)
                OR (t.node_id = 1852 AND des.att_order = 5)
                OR (t.node_id = 1812 AND des.att_order = 6)
                OR (t.node_id = 1850 AND des.att_order = 7))))
        GROUP BY a.node_id,  t.node_id

解释扩展:

+----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                                        |
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------+
|  1 | SIMPLE      | b     | range | PRIMARY       | PRIMARY | 4       | NULL |    4 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | des   | ALL   | NULL          | NULL    | NULL    | NULL | 6695 | Using where                                  |
|  1 | SIMPLE      | t     | range | node_id       | node_id | 4       | NULL |  629 | Using where                                  |
|  1 | SIMPLE      | a     | range | node_id       | node_id | 4       | NULL |  139 | Using where                                  |
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------+
4 rows in set, 1 warning (0.00 sec)

Answer 1

我认为问题在于您没有为 t 或 des 指定任何连接条件，并且您对它们进行了大量比较。因此，您要用 t 和 des 的每个组合重复 a 和 b 的每个匹配项，这可能不是您想要的。

结果表的行数等于 (a join b) * t * des

这是您的 where 子句的内容:

AND ((a.node_id = 1554 AND
  ((t.node_id IN 1776, 1780, 1781, 1782, 1784, 1778, 1777) AND des.att_order BETWEEN 1 AND 8)
OR (a.node_id = 1574 AND
  ((t.node_id IN 1779, 1576, 1577, 1710, 1711, 1712) AND des.att_order BETWEEN 1 AND 7)

...

该查询非常丑陋，但是在大约 7k 行的情况下，带有一堆过滤器的数据透视表不应该占用一分钟的时间