MySQL：使用嵌套子查询的查询非常慢

QUERY:

 SELECT 
    (
        SELECT COUNT(cleanpoi.ID) FROM cleanpoi
        WHERE cleanpoi.EstablishmentID=parent.ID
    ) AS POIs,
    (
        SELECT COUNT(ID) FROM cleanamenitymappings WHERE CleanPOIID IN 
        (
            SELECT cleanpoi.ID FROM cleanpoi
            WHERE cleanpoi.EstablishmentID=parent.ID
        )
    ) AS Amenities,
    (
        SELECT COUNT(ID) FROM cleanamenityvalues WHERE CleanPOIID IN 
        (
            SELECT cleanpoi.ID FROM cleanpoi
            WHERE cleanpoi.EstablishmentID=parent.ID
        )
    ) AS AmenityValues
    FROM establishment parent
    WHERE parent.ID=3

EXPLAIN RESULT:

 id  select_type         table                 type             possible_keys                              key              key_len  ref       rows  Extra                     
------  ------------------  --------------------  ---------------  -----------------------------------------  ---------------  -------  ------  ------  --------------------------
     1  PRIMARY             parent                const            PRIMARY                                    PRIMARY          4        const        1  Using index               
     5  DEPENDENT SUBQUERY  cleanamenityvalues    ALL              (NULL)                                     (NULL)           (NULL)   (NULL)   31778  Using where               
     6  DEPENDENT SUBQUERY  cleanpoi              unique_subquery  PRIMARY,EstablishmentID_2,EstablishmentID  PRIMARY          4        func         1  Using where               
     3  DEPENDENT SUBQUERY  cleanamenitymappings  index            (NULL)                                     CleanPOIID       4        (NULL)  673591  Using where; Using index  
     4  DEPENDENT SUBQUERY  cleanpoi              unique_subquery  PRIMARY,EstablishmentID_2,EstablishmentID  PRIMARY          4        func         1  Using where               
     2  DEPENDENT SUBQUERY  cleanpoi              ref              EstablishmentID_2,EstablishmentID          EstablishmentID  4        const      181

Any advice with this query? Your answers are really appreciated!

对此查询有何建议?你的答案非常感谢!

3 个解决方案

#1

Your query does scan through 700k rows, all which need to be read from disk. I am guessing on poor disk performance (bandwidth and/or seek time).

您的查询会扫描700k行,所有这些行都需要从磁盘读取。我猜测磁盘性能不佳(带宽和/或寻道时间)。

If you run this query often, consider creating a materialized view, which is a table holding the answer to the entire query and which you update using triggers when the underlying data changes.

如果经常运行此查询,请考虑创建实例化视图,该视图是包含整个查询的答案的表,以及在基础数据更改时使用触发器更新的表。

#2

I would create temporary tables with the intermediate results, and then select from this temporary tables.

我将使用中间结果创建临时表,然后从此临时表中进行选择。

It might be useful to create a stored procedure to do that:

创建存储过程可能很有用:

delimiter ;;
create procedure MyLongProcedure()
begin
    -- Just in case, drop the temporary tables before creating them
    drop table if exists temp_step01;
    -- Make a temporary table for each subquery
    create temporary table temp_step01
         select ... ;
    -- Be sure to create the appropriate indexes for each temporary table
    alter table temp_step01
         add index .... ;
    -- Create as much tables as you need
    -- Finally, execute your final query using all the tables you just created
    select ... ;
end;;
delimiter ;

Remember: Temporary tables can be read only by the connection that created them. In a multi-user environment that can be useful... or not, depending on your specific needs.

请记住:临时表只能由创建它们的连接读取。在多用户环境中,根据您的具体需求,是否有用。

This is just an idea. Hope it helps you.

这只是一个想法。希望它能帮到你。

#3

Thanks for all the answers but this solved my problem.

感谢所有的答案,但这解决了我的问题。

SELECT 
    (
        SELECT COUNT(cleanpoi.ID) FROM cleanpoi
        WHERE cleanpoi.EstablishmentID=parent.ID
    ) AS POIs,
    (
        SELECT COUNT(a.ID) 
        FROM cleanamenitymappings a LEFT JOIN cleanpoi b ON a.CleanPOIID=b.ID
        WHERE b.EstablishmentID=parent.ID
    ) AS Amenities,
    (
        SELECT COUNT(a.ID) 
        FROM cleanamenityvalues a LEFT JOIN cleanpoi b ON a.CleanPOIID=b.ID
        WHERE b.EstablishmentID=parent.ID
    ) AS AmenityValues
    FROM establishment parent
    WHERE parent.ID=3

#1