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

Question

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

返回的结果只有 3 行，但是当我在 5.1 服务器上运行此查询时对 mysqld 进程执行 strace 时，我可以看到它不断从 读取数据几分钟的事件表(一个只有 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
事件_部分:7000
事件_网站: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 * ...)。