MySQL的：很慢使用嵌套子查询的查询

Question

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结果：

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  

与此查询有何建议？你的答案真的很感谢！

Answer 1

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

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 

Answer 2

您的查询确实扫描了700k行，所有需要从磁盘读取的行。我猜测磁盘性能差（带宽和/或寻道时间）。

如果经常运行此查询，考虑创建一个物化视图，这是拿着答案的整个查询和更新使用触发器时，数据的变化的表。

Answer 3

我会用中间结果创建临时表，然后从这个临时表中选择。

这可能是有用的，以创建一个存储过程做到这一点：

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 ; 

记住：临时表可以由创建它们的连接读取仅。在多用户环境中，根据您的具体需求，可以使用或不使用。

这只是一个想法。希望它可以帮助你。