mysql - Rows_sent : 12 Rows_examined: 549024 - how to optimize mySQL query?

Question

我有一个数据库，其中列出了相当不错的服务器(四核至强 2.0Ghz、16GB RAM、SSD 驱动器)。该数据库有大约 180,000 个列表。服务器上还没有流量，我只是用大量列表对其进行测试，以确保以后在实际有那么多实时列表和实际流量时不会出现问题。

但即使还没有流量，我觉得他们应该比实际返回得更快。

从慢查询日志中，我能够找到这个:

# Query_time: 1.575742  Lock_time: 0.000113 Rows_sent: 12  Rows_examined: 549024

有 180,000 条记录，它只需要返回 12 条记录，但它检查了超过 500,000 条记录并且需要超过 1.5 秒？一定是出了什么问题，对吧？ :(

实际查询是:

SELECT a.listing_id, a.name, a.item_price, a.max, a.nb, a.currency,
     a.end_time, a.closed, a.bold, a.hl, a.buy_price, a.is_offer, a.reserve,
     a.owner_id, a.postage_amount, a.fb_current_bid, a.type, a.start_time,
     a.is_relisted_item, a.enable
     FROM db_listings a
     LEFT JOIN db_users u ON u.user_id=a.owner_id  WHERE a.active=1 AND
     a.approved=1 AND a.deleted=0 AND a.creation_in_progress=0 AND
     a.closed=0 AND (a.list_in='store' OR u.shop_active='1')
     GROUP BY a.listing_id
     ORDER BY a.list_in ASC, a.end_time ASC  LIMIT 0, 12;

索引已经设置在 db_listings 中的 listing_id 以及 db_users 中的 user_id 上。我认为 db_users 加入不是问题，因为现在那里只有 2 个用户。

如果您需要任何其他信息来解决这个问题，请告诉我。

非常感谢任何帮助:)

Answer 1

首先，您的查询有问题。您使用 LEFT JOIN，但是您使用 where 子句变成了隐式 INNER JOIN: AND (a.list_in='store' 或 u.shop_active='1')

为什么这会将 LEFT JOIN 变成隐式的 INNER？因为当没有匹配的用户时，LEFT JOIN 将为 u.shop_active 生成​​ NULL 值，但 NULL 永远不会等于“1”。这会将查询转换为 INNER JOIN，因为 OUTER JOIN 生成的任何行都将由 WHERE 条件过滤。

这个过滤器也是性能问题的原因。您在两个不同表中的列之间有 OR 条件。没有索引可以满足这样的条件。

这是另一种可能表现更好的方式。此版本将仅搜索列表 where (a.list_in != 'store' and u.shop_active = '1') 当列表少于 12 list_in='store' 时。

要使用以下内容，请确保您在 (list_in, end_time) 上有一个索引

SELECT * FROM
(
    SELECT a.listing_id, a.name, a.item_price, a.max, a.nb, a.currency,
           a.end_time, a.closed, a.bold, a.hl, a.buy_price, a.is_offer, a.reserve,
           a.owner_id, a.postage_amount, a.fb_current_bid, a.type, a.start_time,
           a.is_relisted_item, a.enable
     FROM db_listings a
    WHERE list_in = 'store'
     a.active=1 AND
     a.approved=1 AND 
     a.deleted=0 AND 
     a.creation_in_progress=0 AND
     a.closed=0
    ORDER BY end_time 
    LIMIT 12 
    )
    UNION ALL
    (
        SELECT a.listing_id, a.name, a.item_price, a.max, a.nb, a.currency,
           a.end_time, a.closed, a.bold, a.hl, a.buy_price, a.is_offer, a.reserve,
           a.owner_id, a.postage_amount, a.fb_current_bid, a.type, a.start_time,
           a.is_relisted_item, a.enable
        FROM db_listings a
        JOIN users u 
          ON a.owner_id = u.user_id
         AND u.shop_active = '1'
       WHERE list_in != 'store' AND
       a.active=1 AND
       a.approved=1 AND 
       a.deleted=0 AND 
       a.creation_in_progress=0 AND
       a.closed=0
       ORDER BY end_time 
       LIMIT 12 
    )
) sq
ORDER BY list_in, end_time
LIMIT 12;