Mysql慢速查询：JOIN +多个WHERES + ORDER BY

2018-06-17 01:34:30

很久以前的潜伏者，第一个问题！

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

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中有多个实例，这就是为什么需要对它们进行分组）

索引在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子句中提供的。就我个人而言，当你考虑它应该表现更好时，我会与你的第一个查询一起去。

据我所知在第一个和第二个查询中发生了什么：

在进行自然联接之前，第一个查询将被子查询过滤，这意味着它只能加入结果数据而不是整个表。

第二个查询是加入整个第二个表格，然后将整个结果的行过滤回你想要的。

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

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

这里是一篇文章，通过索引mysql的性能，值得一读，如果你想知道更多。

祝你好运！

编辑：你最后的问题我第一次错过了，答案是，如果你的索引主要加入领域然后改变到哪里只会稍微影响整体表现，但我已经放在表格上的独特索引应考虑到大部分你想要基于查询的东西。要记住的主要问题是，如果你经常查询或加入某个领域，那么它应该被真正编入索引，但是对于您的次序查询和更改顺序，您应该不必担心重新对齐索引策略。

性能明智，它从来不是一件好事使用

select *

您应该使用单个列名称。

select column1,column2 etc...

就个人而言，我是一个SQL极简主义者，并避免任何类型的子查询或不能索引到索引列的连接。

如果这不是真的可能，我可能会单独运行子查询来收集我的密钥，对它们的客户端网站进行排序，然后在（...）子句中构建一个where。

JohnVD提供了很多优点，但如果您需要制作一个包含product_name的唯一密钥，则应该确定是否可以将其归一化为一个唯一密钥。

如果可能的话，索引varchar列可以避免任何代价。每个索引条目都与列的最大大小一样大，即使它们通常只是其中的一小部分。如果你使用utf-8这样的字符集，那么大小是〜maxlen + 3。

随着你的限制，它似乎是需要的顺序。但是就像你在做群组时那样，如果你打算消耗整个结果集，那么就使用ORDER BY NULL。通过解释来运行这两个变体，看看为什么; null的顺序消除了隐含的文件夹，你可以对客户端进行排序。（如果你通过汇总来进行分组，这是不可能的）

链接地址: http://www.djcxy.com/p/48253.html

上一篇: Mysql slow query: JOIN + multiple WHERES + ORDER BY

下一篇: Mocking Asynchronous Calls in Silverlight WCF Proxy using Moq