MySQl 查询 : Join and Group By, 性能缓慢

Question

我的 MySQL 查询有问题，我无法优化它。

SELECT 
  p.id, 
  p.name,
  p.sku, 
  p.type
FROM 
  xm_products p 
  LEFT JOIN xm_store_product sp ON p.id = sp.product_id 
  LEFT JOIN xm_store s ON sp.store_id = s.id 
WHERE 
  s.id = 1 
ORDER BY 
  p.type, 
  p.name asc 
LIMIT 
  20 OFFSET 0

这个查询很慢:Querytime 2.532s

如果我删除 Order By 子句，查询速度非常快:0.0001 秒

解释显示以下信息:

+----+-------------+-------+--------+-------------------------------------------+----------------------+---------+---------------------------------------+--------+----------------------------------------------+
| id | select_type | table | type   | possible_keys                             | key                  | key_len | ref                                   | rows   | Extra                                        |
+----+-------------+-------+--------+-------------------------------------------+----------------------+---------+---------------------------------------+--------+----------------------------------------------+
|  1 | SIMPLE      | s     | const  | PRIMARY                                   | PRIMARY              | 4       | const                                 |      1 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | sp    | ref    | IDX_CA42254AB092A811,IDX_CA42254A4584665A | IDX_CA42254AB092A811 | 5       | const                                 | 102157 | Using where                                  |
|  1 | SIMPLE      | p     | eq_ref | PRIMARY                                   | PRIMARY              | 4       | model.sp.product_id                   |      1 |                                              |
+----+-------------+-------+--------+-------------------------------------------+----------------------+---------+---------------------------------------+--------+----------------------------------------------+
3 rows in set

我有 3 个表:

• 具有主键 ID 的 xm_product
• 具有主键 ID 的 xm_store
• 在 store_id 和 product_id 上有索引的 xm_store_product

我尝试在 p.name 和 p.type 上添加索引以及组合索引 (p.name, p.type)，但没有帮助。

如何优化此查询的性能？

编辑:

我花了 2 个小时来创建一个 sqlfidle。但这里有一些数据。 http://sqlfiddle.com/#!2/2bf8d/1

问题是“分组依据”导致“使用索引；使用临时；使用文件排序”。

如何更正我的示例？

编辑 2:

de xm_products 表有大约 200'000 条记录。 xm_store_products 大约 400'000。查询用于寻呼机，每页限制为 20 个。

Answer 1

我会在积极反馈后将其作为答案:)

性能上存在(明显的)差异是有道理的。

我不知道您的表有多大，但是 LIMIT 20 只会提高性能而无需 order by。使用 order by，首先必须在 order by 发生之前检索所有记录，而如果没有 order by，执行将在 20 个匹配项后停止。

也许在您的 order-by-clause 上使用组合索引和 View ，您可以获得性能。