怎么可能有一个好的解释和一个缓慢的查询?

How is it possible to have a good plan in EXPLAIN like below and have a slow query. With few rows, using index, no filesort.

如何在EXPLAIN中有一个好的计划，并有一个缓慢的查询。只有很少的行，使用索引，没有文件排序。

The query is running in 9s. The main table has around 500k rows.

查询在9s中运行。主表有大约500k行。

When I had 250k rows in that table, the query was running in < 1s.

当我在该表中有250k行时，查询在< 1中运行。

Suggestions plz?

建议请吗?

Query (1. fields commented can be enabled according user choice. 2. Without FORCE INDEX I got 14s. 3. SQL_NO_CACHE I use to prevent false results):

查询(1。可以根据用户选择启用字段注释。2。没有力指数，我得到14。3所示。我用来防止错误结果的SQL_NO_CACHE):

    SELECT SQL_NO_CACHE
          p.property_id
        , lct.loc_city_name_pt
        , lc.loc_community_name_pt
        , lc.loc_community_image_num_default
        , lc.loc_community_gmap_longitude
        , lc.loc_community_gmap_latitude
    FROM property as p FORCE INDEX (ix_order_by_perf)
    INNER JOIN loc_community lc 
        ON lc.loc_community_id = p.property_loc_community_id
    INNER JOIN loc_city lct FORCE INDEX (loc_city_id)
        ON lct.loc_city_id = lc.loc_community_loc_city_id
    INNER JOIN property_attribute pa
        ON pa.property_attribute_property_id = p.property_id        
    WHERE p.property_published = 1
        AND (p.property_property_type_id = '1' AND p.property_property_type_sale_id = '1') 
        AND p.property_property_housing_id = '1' 
--      AND p.property_loc_community_id = '36'  
--      AND p.property_bedroom_id = '2'
--      AND p.property_price >= '50000' AND p.property_price <= '150000'
--      AND lct.loc_city_id = '1'
--      AND p.property_loc_subcommunity_id IN(7,8,12) 
    ORDER BY 
          p.property_featured DESC
        , p.property_ranking_date DESC
        , p.property_ranking_total DESC
    LIMIT 0, 15

Query Profile

查询资料

The resultset always outputs 15 rows. But the table property and property_attribute has around 500k rows.

resultset总是输出15行。但是表属性和property_attribute有大约500k行。

Thanks all,

谢谢,

Armando Miani

阿曼德Miani

4 个解决方案

#1

There's a moment when your query will be optimized around a model which might not be anymore valid for a given need.

有时，您的查询将围绕一个模型进行优化，这个模型对于给定的需求可能不再有效。

A plan could be great but, even if the filters you are using in the where clause respect indexes definitions, it doesn't mean the parser doesn't parse may rows.

计划可能很好，但即使where子句中使用的过滤器与索引定义有关，也并不意味着解析器不解析may行。

What you have to analize is how determinating are your indexes. For instance, if there's an index on "name, family name" in a "person" table, the performances are going to be poor if everybody has the same name and family name. The index is a real trap pulling down performances when it doesn't manage to be enough describing a certain segment of your datasets.

你要分析的是你的索引有多确定。例如，如果“person”表中有一个关于“name, family name”的索引，那么如果每个人都有相同的名字和姓氏，表演就会很糟糕。当索引不能很好地描述数据集的某一段时，它就会降低性能。

#2

This really seems to be an odditity in EXPLAIN in this case. This doesn't occur on MySQL 4.x, but it does on MySQL 5.x.

在这种情况下，这似乎是一种不可思议的解释。这在MySQL 4中不会发生。x，但它在MySQL 5。x上。

What MySQL is really showing you is that MySQL is trying to use the forced index ix_order_by_perf for sorting the rows, and it's showing you 15 rows because you have LIMIT 15.

MySQL真正向你展示的是MySQL试图使用强制索引ix_order_by_perf对行进行排序，它向你展示了15行，因为有15行限制。

However, the WHERE clause is still scanning all 500K rows since it can't utilize an index for the criteria in your WHERE clause. If it were able to use the index for finding the required rows, you would see the forced index listed in the 'possible_keys' field.

但是，WHERE子句仍然扫描所有500K行，因为它不能使用WHERE子句中的索引作为条件。如果它能够使用索引来查找所需的行，您将看到在“possible_keys”字段中列出的强制索引。

You can prove this by keeping the FORCE INDEX clause and removing the ORDER BY clause. You'll see that MySQL now won't use any indexes, even the one you're forcing (because the index doesn't work for this purpose).

你可以通过保留力量指数条款和删除顺序条款来证明这一点。您将看到MySQL现在不会使用任何索引，甚至是您所强制执行的索引(因为索引并不为此目的工作)。

Try adding property_property_type_id, property_property_type_sale_id, property_property_housing_id, and any other columns that you refer to in your WHERE clause to the beginning of the index.

尝试添加property_property_property_type_id、property_property_type_sale_id、property_property_housing_id，以及在WHERE子句中引用到索引开头的任何其他列。

#3

Based on the output of your explain the query, here are my initial thoughts:

根据您的explain the query的输出，以下是我的初步想法:

This portion of your query (rewritten to excluded the unneeded parentheses):

查询的这一部分(重写以排除不必要的括号):

    p.property_published = 1
    AND p.property_property_type_id = '1' 
    AND p.property_property_type_sale_id = '1'
    AND p.property_property_housing_id = '1' 

Put conditions so many conditions on the property table that it's unlikely any index you have can be used. Unless you have a single index that has all four of those attributes in it, you're forcing a full table scan on the query just to find the rows that meet those conditions (though, it's possible if you have an index on one of the attributes it could use that).

在属性表中设置如此多的条件，不太可能使用任何索引。除非您有一个包含所有这4个属性的索引，否则您将强制对查询执行全表扫描，以找到满足这些条件的行(不过，如果您在其中一个属性上有一个索引，它可以使用这些属性)。

First, I'd add the following index (have not checked this for syntax errors):

首先，我要添加以下索引(没有检查语法错误):

CREATE INDEX property_published_type_sale_housing_idx  
ON property (property_published, 
             property_property_type_id, 
             property_property_type_sale_id, 
             property_property_housing_id );

Then I'd re-run your EXPLAIN to see if you hit the index now. (Take off the FORCE INDEX on that part of the query).

然后我重新运行你的解释，看看你现在是否达到了索引。(删除查询中该部分的FORCE索引)。

Also, even given this issue, it's possible the slow down may be memory related. That is, you may have enough memory to process the table with a smaller number of rows, but it may be that when the table gets larger MySQL can't process the entire query in memory and is forced to start using disk to get the entire query handled. This would explain why there's a sudden drop off in performance.

而且，即使考虑到这个问题，慢下来也可能与记忆有关。也就是说，您可能有足够的内存来使用更少的行来处理表，但是当表变得更大时，MySQL可能无法在内存中处理整个查询，并且*开始使用磁盘来处理整个查询。这就解释了为什么性能会突然下降。

If that's the case, then two things might help:

如果是这样的话，有两件事可能会有帮助:

1. Adding more memory (and tune the mysql config file to take advantage of it) so that the number of rows that can br processed at once is larger. This is at best a temporary solution.

   添加更多的内存(并优化mysql配置文件以利用它)，以便能够同时处理br的行数更大。这充其量是一个临时解决方案。

2. Tune the indexes (like I'm saying above) so that the number of rows that mysql needs to process is lower. If it can be more precise in picking the rows it selects for processing.

   调整索引(如上所述)，以便mysql需要处理的行数更少。如果可以更精确地选择要处理的行。

#4

except a good plan you need to have enough resources to run query.

除了一个好的计划，您需要有足够的资源来运行查询。

check buffers size and another critical parameters in your config.

检查缓冲区大小和配置中的另一个关键参数。

And your query is?

您的查询呢?