Rows_sent:12 Rows_examined:549024-如何优化mySQL查询? [英] Rows_sent: 12 Rows_examined: 549024 - how to optimize mySQL query?

问题描述

我有一个数据库，其中包含相当不错的服务器(Quad Core Xeon 2.0Ghz，16GB RAM，SSD驱动器)上的列表.该数据库大约有18万个列表.服务器上还没有流量，我只是用大量清单进行测试，以确保以后没有问题时，实际上有很多实时清单和实际流量.

I have a database with listings on quite a good server (Quad Core Xeon 2.0Ghz, 16GB RAM, SSD Drives). The database has about 180,000 listings. There's no traffic yet on the server and I'm just testing it with a lot of listings to ensure there are no problems later on when there's actually that many live listings and actual traffic.

但是即使没有人流，我觉得他们应该比实际返回的速度更快.

But even with no traffic yet, I feel like they should return faster than they actually are.

从慢查询日志中，我可以找到它:

From the slow queries log, I was able to find this:

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

有18万条记录，它只需要返回12条记录，但是它检查了超过500,000条，并且花费了1.5秒以上的时间?一定有问题，对吧? :(

There are 180,000 records, it has to return only 12 but it examines over 500,000 and it takes over 1.5 seconds? Something has to be wrong, right? :(

实际查询是:

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个用户.

Indexes are already set on listing_id in db_listings as well as on user_id in db_users. I don't think the db_users join is a problem, because there are only 2 users in there right now.

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

If you need any additional information to tackle this problem, just let me know.

非常感谢您的帮助:)

推荐答案

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

First off, there is a problem with your query. You use LEFT JOIN, but you turn into into an implicit INNER JOIN with your where clause: AND (a.list_in='store' OR u.shop_active='1')

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

Why does this turn the LEFT JOIN into an implicit INNER? Because the LEFT JOIN will produce NULL values for u.shop_active when there is no matching user, but NULL will NEVER equal '1'. This turns the query into an INNER JOIN because any rows produced by the OUTER JOIN will be filtered by the WHERE condition.

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

This filter is also the reason for the performance problem. You have an OR condition between columns in two different tables. There is no index that can satisfy such a condition.

这是另一种可能效果更好的方法.此版本仅在列表少于12个list_in ='store'时搜索列表(a.list_in！='store'，而u.shop_active ='1')的列表.

Here is another way which may perform better. This version will only search for listings where (a.list_in != 'store' and u.shop_active = '1') when there are less than 12 list_in='store' listings.

要使用以下内容，请确保您在(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;

这篇关于Rows_sent:12 Rows_examined:549024-如何优化mySQL查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！