MySql查询优化以避免文件短缺

Question

我希望有人帮助我优化和避免在下面的查询文件排序。随附其解释的截图

SELECT DISTINCT(storages.id), 
     IF(admin_approve = 1 
           OR storage_free_auctions.user_id = 167 
           OR ISNULL(storage_free_auctions.user_id), 
        auction_date, NULL) AS auction_date1
FROM user_list_storage 
JOIN storages ON storages.id = user_list_storage.storage_id 
JOIN states ON states.id = storages.state 
LEFT JOIN storage_auctions ON storage_auctions.id = (
          SELECT storage_auctions.id FROM storage_auctions 
          LEFT JOIN storage_free_auctions 
          ON storage_free_auctions.storage_auctions_id=storage_auctions.id 
          WHERE storage_auctions.storage_id = storages.id 
          AND storage_auctions.status = 'Active' 
          AND (  ISNULL(storage_free_auctions.user_id) 
                 OR admin_approve = 1 
                 OR storage_free_auctions.user_id = 167) 
          AND CONCAT(auction_date,' ',start_time) >= CURDATE() 
          ORDER BY auction_date ASC LIMIT 1) 
LEFT JOIN storage_free_auctions 
     ON storage_free_auctions.storage_auctions_id=storage_auctions.id 
LEFT JOIN storage_auction_units 
     ON storage_auction_units.storage_auction_id = storage_auctions.id 
     AND storage_auction_units.status='Active' 
WHERE storages.storage_status = 'Active' 
AND user_list_storage.user_id = 167 
AND user_list_storage.user_list_id = 3 
AND (storage_free_auctions.user_id = 167 
     OR admin_approve = 1 
     OR ISNULL(storage_free_auctions.user_id) 
     OR user_list_storage.user_id = 167) 
GROUP BY storages.id 
order by auction_date1

解释结果的第一行如下所示。

id : 1 Select_type :主表: user_list_storage 类型: ref possible_keys：E 210 idx_user_list_id，idx_storage_id，idx_user_id E 111键：E 212 idx_user_id E 113key_len：E 2145E 115ref：E 216 const >const

行: 64

额外:使用where的；使用临时的；使用文件

Answer 1

替换

 LEFT JOIN storage_auctions 
        ON storage_auctions.id= (SELECT storage_auctions.id 
            FROM storage_auctions 
            LEFT JOIN storage_free_auctions 
                ON storage_free_auctions.storage_auctions_id = storage_auctions.id 
            WHERE storage_auctions.storage_id = storages.id 
              AND storage_auctions.status = 'Active' 
              AND (ISNULL(storage_free_auctions.user_id)
                    OR admin_approve = 1 
                    OR storage_free_auctions.user_id = 167) 
            AND CONCAT(auction_date,' ',start_time) >= CURDATE() ORDER BY auction_date ASC LIMIT 1) 

使用

AND EXISTS (SELECT 1 
    FROM storage_auctions  
    WHERE storage_auctions.id = storages.id 
      AND CONCAT(auction_date,' ',start_time) >= CURDATE())

因为左边联接中的子句是多余的，并且是在前面完成的，所以您想要的是将来存在一个具有相同id的记录，对吗？

你的其他连接都没问题。它们都是基于整数列的，如果它们不是主键和runstats，请确保对所有这些if进行索引。