为什么这个查询要花这么长时间？

Question

这个查询出现在我的mysql系统上的慢日志中，

# Query_time: 37  Lock_time: 0  Rows_sent: 5  Rows_examined: 405199
select euroapps.id, euroapps.name, euroapps.imageurl, euroapps.created,
application_price.retail_price, euroapps.count FROM application_price INNER JOIN
euroapps ON euroapps.id = application_price.application_id WHERE
application_price.storefront_id = '143441' AND application_price.retail_price <= 0
ORDER BY created DESC LIMIT 5;

你看到它检查了405,199行，这可能是查询时间长的原因吗？

类似的查询从未出现在我的慢速日志中，该查询是：

select euroapps.id, euroapps.name, euroapps.imageurl, euroapps.created,
application_price.retail_price, euroapps.count FROM application_price INNER JOIN
euroapps ON euroapps.id = application_price.application_id WHERE
application_price.storefront_id = '$store' AND application_price.retail_price > 0
ORDER BY created DESC LIMIT 5

这是解释的输出：

mysql> explain select euroapps.id, euroapps.name, euroapps.imageurl, euroapps.created, application_price.retail_price, euroapps.count FROM application_price INNER JOIN euroapps ON euroapps.id = application_price.application_id WHERE application_price.storefront_id = '143441' AND application_price.retail_price <= 0 ORDER BY created DESC LIMIT 5;
+----+-------------+-------------------+--------+----------------------------------+--------------------------+---------+---------------------------------------------+--------+-----------------------------------------------------------+
| id | select_type | table             | type   | possible_keys                    | key                      | key_len | ref                                         | rows   | Extra                                                     |
+----+-------------+-------------------+--------+----------------------------------+--------------------------+---------+---------------------------------------------+--------+-----------------------------------------------------------+
|  1 | SIMPLE      | application_price | range  | PRIMARY,idx_storedfront_price_id | idx_storedfront_price_id | 9       | NULL                                        | 110491 | Using where; Using index; Using temporary; Using filesort | 
|  1 | SIMPLE      | euroapps          | eq_ref | PRIMARY                          | PRIMARY                  | 4       | itunesapps.application_price.application_id |      1 |                                                           | 
+----+-------------+-------------------+--------+----------------------------------+--------------------------+---------+---------------------------------------------+--------+-----------------------------------------------------------+
2 rows in set (0.00 sec)

Answer 1

您应该查看执行计划，这将有助于缩小原因范围。
http://dev.mysql.com/doc/refman /5.5/en/execution-plan-information.html

Answer 2

查看 WHERE 子句，您可以看到您使用 application_price.storefront_id 作为过滤因子。
但是，在您的解释中，它没有显示为可能的键，这意味着它没有索引 - 这意味着需要全表扫描。

另一个因素是application_price.retail_price，

正如 Jason Swett 建议的那样 - 索引您的 application_price.storefront_id ，您应该会看到更好的性能（Jason 您可能应该发布您的评论作为答案）。

Answer 3

解释的“行”列表明 MySQL 估计它将必须检查 application_price 表中的 110491 行。也是使用临时的；在此表上使用文件排序。

我建议您为 application_price 添加一个索引，其中包含（storefront_id、application_id、retail_price、created）（如果“created”是 application_price 的字段）。这些字段的组合应该会有所帮助。