MySQL子查询性能5.0到5.1(及更高版本)

Question

我在PHP应用程序中遇到了以下查询。这个查询在mysql 5.0中运行得很快(不到1秒)，但是在mysql 5.1和更高版本中需要大约7分钟才能完成。

返回的结果只有3行，但是当我在5.1服务器上的mysqld进程上执行strace时，我可以看到它连续几分钟从Event表中读取数据(一个只有7000行，每行200字节的表)——可能是多次重新读取它。

所以问题是，我在这里遇到的不同之处是什么，我如何修改东西(无论是查询还是更好的MySQL设置)，以便它在5.1下运行得和5.0一样快。

---

引用数据

该归咎于的查询

SELECT S.Sections_ID, S.Sections_Name, S.Sections_CustomURL
FROM Sections S
WHERE S.Sections_Status = 'Active'
    AND S.Sections_Name!='Hidden'
    AND S.Sections_ParentID = 0
    AND S.Sections_MainSection = 1
    AND (
        SELECT COUNT(MainEvent.Event_ID) AS tot
        FROM Event MainEvent, Event_Section ES
        WHERE ES.EventSection_EventID=MainEvent.Event_ID
            AND ES.EventSection_SectionID=S.Sections_ID
            AND (
                (MainEvent.Event_DateTime > '2011-12-27 18:05:00')
                OR
                    (
                        (
                        SELECT ChildEvent.Event_DateTime
                        FROM Event ChildEvent
                        WHERE ChildEvent.Event_ParentEventID=MainEvent.Event_ID
                        ORDER BY ChildEvent.Event_DateTime DESC LIMIT 1
                        ) > '2011-12-27 18:05:00'
                    )
                )
            AND (MainEvent.Event_ParentEventID=0 or MainEvent.Event_ParentEventID IS NULL) 
            AND (MainEvent.Event_Status='Active' or MainEvent.Event_Status='Canceled') 
            AND MainEvent.Event_ID IN (
                SELECT
                Event_Website.EventWebsite_EventID
                FROM Event_Website 
                WHERE Event_Website.EventWebsite_CompanyID='3'
            )
        )>0
ORDER BY S.Sections_Order ASC, S.Sections_Name ASC

所引用的表具有以下行数

章节:60
事件:7000
Event_Section: 7000
Event_Website: 15000

下面是5.0(快速)和5.1(慢速)服务器上上述查询的EXPLAIN。
为节省空间而剪裁;希望我没有剪掉任何有用的东西。

缓慢(5.1)

+----+---------------+-------------+----------------------------+------------------------+---------+----------------------------------+------+-----------------------------+
| id | table         | type        | possible_keys              | key                    | key_len | ref                              | rows | Extra                       |
+----+---------------+-------------+----------------------------+------------------------+---------+----------------------------------+------+-----------------------------+
|  1 | S             | ref         | Sections_ParentID          | Sections_ParentID      | 5       | const                            |   10 | Using where; Using filesort |
|  2 | MainEvent     | ref_or_null | PRIMARY,Event_DateTime,... | Event_ParentID         | 5       | const                            | 4582 | Using where                 |
|  2 | ES            | ref         | EventSection_EventID       | EventSection_EventID   | 10      | MainEvent.Event_ID,S.Sections_ID |    1 | Using where; Using index    |
|  4 | Event_Website | ref         | EventWebsite_CompanyID     | EventWebsite_CompanyID | 4       | const                            | 4421 | Using where                 |
|  3 | ChildEvent    | index       | Event_ParentID             | Event_DateTime         | 8       | NULL                             |    1 | Using where                 |
+----+---------------+-------------+----------------------------+------------------------+---------+----------------------------------+------+-----------------------------+

快(5.0)

+----+---------------+--------+---------------------------+------------------------+---------+-------------------------+------+-----------------------------+
| id | table         | type   | possible_keys             | key                    | key_len | ref                     | rows | Extra                       |
+----+---------------+--------+---------------------------+------------------------+---------+-------------------------+------+-----------------------------+
|  1 | S             | ref    | Sections_ParentID         | Sections_ParentID      | 5       | const                   |   10 | Using where; Using filesort | 
|  2 | ES            | index  | EventSection_EventID      | EventSection_EventID   | 10      | NULL                    | 5610 | Using where; Using index    | 
|  2 | MainEvent     | eq_ref | PRIMARY,Event_DateTime,...| PRIMARY                | 4       | ES.EventSection_EventID |    1 | Using where                 | 
|  4 | Event_Website | ref    | EventWebsite_CompanyID    | EventWebsite_CompanyID | 4       | const                   | 5809 | Using where                 | 
|  3 | ChildEvent    | ref    | Event_ParentID            | Event_ParentID         | 5       | MainEvent.Event_ID      |    4 | Using where; Using filesort | 
+----+---------------+--------+---------------------------+------------------------+---------+-------------------------+------+-----------------------------+

Answer 1

两个重写建议:

1. 将IN (SELECT ...)改为JOIN查询

2. 将(SELECT COUNT(MainEvent.Event_ID) ...) > 0更改为EXISTS (SELECT * ...)

Answer 2

Mysql版本5.0和5.1(以及所有较低的版本)不是以这种方式处理(依赖的)子查询时使用的最佳版本。

如果你在那些版本上卡住了，试着把你的查询重写成连接。使用

"{columnname} IN (SELECT ....FROM {tablename} WHERE ....)"

通常是性能杀手。将其拆分为2个查询通常比使用该语法更快。

执行第一个查询，获取所需的所有ID，然后将这些ID放入上述查询

"{columnname} IN(12,345, 356,653，等等…)"

MariaDB已经部分解决了这个问题，并且是一个彻底的MySQL替代品。如果没有敏感数据，可否将数据库转储发给我，我将在MariaDB

Answer 3

我已经重写了您的查询，现在它使用join。我无法测试这个查询，因为您没有附加数据库模式。请把它附在你的问题后面。

SELECT S.Sections_ID, S.Sections_Name, S.Sections_CustomURL
FROM Sections AS S
JOIN Event_Section AS ES ON ES.EventSection_SectionID = S.Sections_ID
JOIN Event AS MainEvent ON ES.EventSection_EventID = MainEvent.Event_ID
JOIN Event_Website ON Event_Website.EventWebsite_EventID = MainEvent.Event_ID
LEFT JOIN Event AS ChildEvent ON ChildEvent.Event_ParentEventID = MainEvent.Event_ID
WHERE S.Sections_Status = 'Active'
    AND S.Sections_Name != 'Hidden'
    AND S.Sections_ParentID = 0
    AND S.Sections_MainSection = 1
    AND (MainEvent.Event_ParentEventID = 0 OR MainEvent.Event_ParentEventID IS NULL)
    AND (MainEvent.Event_DateTime > '2011-12-27 18:05:00' OR ChildEvent.Event_DateTime > '2011-12-27 18:05:00')
    AND (MainEvent.Event_Status='Active' OR MainEvent.Event_Status='Canceled')
    AND Event_Website.EventWebsite_CompanyID = '3'
GROUP BY S.Sections_ID
ORDER BY S.Sections_Order ASC, S.Sections_Name ASC