database - Mysql慢查询: JOIN + multiple WHERES + ORDER BY

Question

潜伏已久，第一个问题!

我正在努力优化此查询，它会选择与所选过滤器匹配的价格最低的商品:

SELECT product_info.*, MIN(product_all.sale_price) as sale_price, product_all.buy_link
FROM product_info
NATURAL JOIN (SELECT * FROM product_all WHERE product_all.date = '2010-09-30') as product_all
WHERE (product_info.category = 2  
AND product_info.gender = 'W' )
GROUP BY product_all.prod_id
ORDER BY MIN(product_all.sale_price) ASC LIMIT 13

它的解释:

| id | select_type | table        | type   | possible_keys                                             | key     | key_len | ref                 | rows   | Extra                           |  
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+  
|  1 | PRIMARY     | <derived2>   | ALL    | NULL                                                     | NULL    | NULL    | NULL                | 89801  | Using temporary; Using filesort | 
|  1 | PRIMARY     | product_info | eq_ref | PRIMARY,category_prod_id_retail_price,category_ret...     | PRIMARY | 4       | product_all.prod_id | 1      | Using where                     | 
|  2 | DERIVED     | product_all  | ref    | date_2                                                    | date_2  | 3       |                     | 144107 |                                 | 

我已经尝试消除子查询，这在直觉上似乎更好，但实际上需要更长的时间:

SELECT product_info.*, MIN(product_all.sale_price) as sale_price, product_all.buy_link
FROM product_info
NATURAL JOIN product_all
WHERE (product_all.date = '2010-09-30'
AND product_info.category = 2 
AND product_info.gender = 'W' )
GROUP BY product_all.prod_id
ORDER BY MIN(product_all.sale_price) ASC LIMIT 13

及其解释:

| id | select_type | table        | type | possible_keys                                             | key                      | key_len | ref                               | rows | Extra                                        |  
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+  
|  1 | SIMPLE      | product_info | ref  | PRIMARY,category_prod_id_retail_price,category_ret...     | category_retail_price    | 5       | const                             | 269  | Using where; Using temporary; Using filesort | 
|  1 | SIMPLE      | product_all  | ref  | PRIMARY,prod_id,date_2                                    | prod_id                  | 4       | equipster_db.product_info.prod_id | 141  | Using where                                  | 

表格如下:

CREATE TABLE `product_all` (
`prod_id` INT( 10 ) NOT NULL PRIMARY KEY ,
`ref_id` INT( 10) NOT NULL PRIMARY KEY ,
`date` DATE NOT NULL ,
`buy_link` BLOB NOT NULL ,
`sale_price` FLOAT NOT NULL
) ENGINE = MYISAM ;


CREATE TABLE `product_info` (
`prod_id` INT( 10 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`prod_name` VARCHAR( 200 ) NOT NULL,
`brand` VARCHAR( 50 ) NOT NULL,
`retail_price` FLOAT NOT NULL
`category` INT( 3 ) NOT NULL,
`gender` VARCHAR( 1 ) NOT NULL,
`type` VARCHAR( 10 ) NOT NULL
) ENGINE = MYISAM ;

我的问题:
- 哪种查询结构似乎是最佳的？
- 哪些索引可以优化此查询？
-不太重要:当添加或删除 WHERE 子句或使用不同的 ORDER BY 时，索引方法如何变化，例如按 % off 排序:

ORDER BY (1-(MIN(product_all.sale_price)/product_info.retail_price)) DESC  

编辑:两个查询的自然连接都作用于 prod_id(product_info 中的一条记录可以在 product_all 中有多个实例，这就是它们需要分组的原因)

Answer 1

索引在 mysql 中产生了巨大的差异，一个查询使用一组错误的索引需要 15 分钟，而使用正确的索引需要 0.2 秒，但它找到正确的平衡通常是问题所在。自然地，如果没有一些示例数据，很难说下面的解决方案是否会为您节省时间，但理论上它应该。

为了回答您的问题，我会像这样重新设计表格:

CREATE TABLE `product_all` ( 
`prod_id` INT( 10 ) NOT NULL, 
`ref_id` INT( 10) NOT NULL, 
`date` DATE NOT NULL , 
`buy_link` BLOB NOT NULL , 
`sale_price` FLOAT NOT NULL,
PRIMARY KEY (prod_id, ref_id) ,
INDEX date_Index (`date` ASC),
UNIQUE INDEX prod_price_Index (prod_id ASC, sale_price ASC)
) ENGINE = MYISAM ; 


CREATE TABLE `product_info` ( 
`prod_id` INT( 10 ) NOT NULL AUTO_INCREMENT, 
`prod_name` VARCHAR( 200 ) NOT NULL, 
`brand` VARCHAR( 50 ) NOT NULL, 
`retail_price` FLOAT NOT NULL, 
`category` INT( 3 ) NOT NULL, 
`gender` VARCHAR( 1 ) NOT NULL, 
`type` VARCHAR( 10 ) NOT NULL,
PRIMARY KEY (prod_id) ,
UNIQUE INDEX prod_id_name_Index (prod_id ASC, prod_name ASC),
INDEX category_Index (category ASC),
INDEX gender_Index (gender ASC)
) ENGINE = MYISAM ;

SELECT product_info.*, MIN(product_all.sale_price) as sale_price, product_all.buy_link         
FROM product_info         
NATURAL JOIN (SELECT * FROM product_all WHERE product_all.date = '2010-09-30') as product_all         
WHERE (product_info.category = 2           
AND product_info.gender = 'W' )         
GROUP BY product_all.prod_id         
ORDER BY MIN(product_all.sale_price) ASC LIMIT 13        

此处的性能提升是通过索引正在连接并在 where 子句中显示的主要字段获得的。就个人而言，我会选择您的第一个查询，因为当您考虑它应该表现得更好时。

据我了解第一个和第二个查询中发生了什么:

• 第一个查询被过滤在执行之前的子查询自然加入，这意味着它唯一的加入结果数据而不是整张 table 。
• 第二个查询是加入整个第二张 table 然后过滤的结果行一切回到你想要的。

根据经验，通常您希望在主要连接字段以及您在 where 子句中使用最多的字段上添加索引。我还在一些您希望定期查询的字段上放置了一些唯一索引，例如 prod_id_name_Index。

如果这不能提高你的性能，如果你可以发布一些虚拟数据来玩我可能会得到一个更快的解决方案，我可以进行基准测试。

Here是一篇通过索引来提高 mysql 性能的文章，如果您想了解更多信息，值得一读。

祝你好运!

编辑:我第一次错过了你的最后一个问题，答案是如果你索引主要的连接字段然后更改到 where 只会稍微影响整体性能，但我放在表格上的唯一索引应该占您希望基于查询的大部分内容。要记住的主要事情是，如果您经常查询或加入某个字段，那么它确实应该被索引，但是您不应该担心重新调整索引策略方面的次要查询和对顺序的更改。