慢MySQL查询

Question

下面的SQL查询大约需要1分钟。当直接输入到SQL控制台时执行。6636记录在qv_attribute表中。

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

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连接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行，一个带一串过滤器的枢轴不应该占用一分钟。

Answer 2

想想看，如果您创建了一个如下所示的表，那么您可以做些什么：

a_node_id   t_node_id  att_order 
 1554          1776        1
 1554          1780        2
 1554          1781        3
 1554          1782        4
 1554          1783        5
 1554          1784        6
 1554          1778        7
 1554          1777        8

 1574          1574        1
 1574          1779        2
 1574          1576        3
 1574          1577        4
 1574          1710        5
 1574          1711        6
 1574          1712        7

 1803          1838        1
 1803          1839        2
 1803          1840        3
 1803          1805        4

 etc

附加提示:将其放在一个表中，然后左转到这个表中--比您当前的where子句要快得多。

Answer 3

假设您已经索引了您的表-放缓来自您的CASE WHEN组合。将其拆分为11个不同的查询，您将看到一个加速。

另外，如果您必须经常在1分钟太长的地方运行此查询，您可能需要重新设计数据库结构，以保留一些冗余但快速的查询信息。

祝好运!