MySql查询优化以避免文件排序 - MySql Query Optimization to avoid filesort

MySql Query Optimization to avoid filesort

本文关键字：文件排序查询优化 MySql | 更新日期: 2023-09-27

我希望有人能帮助我优化并避免以下查询中的文件排序。附上其解释的屏幕截图

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选择类型：主要表：user_listrongtorage类型：ref可能的密钥：idx_user_list_id、idx_storage_id和idx_user_id键：idx_user_idkey_len:5ref:常量
行：64
额外：使用where；使用临时；使用文件端口

更换

 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，请确保对所有这些id进行索引。