MySQL慢查询大查询

Question

MySQL慢查询大查询

7

我有一个庞大的查询需要优化，虽然我已经进行了优化，但仍然有时会很慢（>1s）：

select count(DISTINCT if(ps15.specification in ('All Season'),p.products_id,NULL)) as count1 ,count(DISTINCT if(ps15.specification in ('Winter'),p.products_id,NULL)) as count2 ,count(DISTINCT if(ps15.specification in ('Zomer'),p.products_id,NULL)) as count3 ,count(DISTINCT if(ps15.specification in ('Winter 2012'),p.products_id,NULL)) as count4 ,count(DISTINCT if(ps15.specification in ('Zomer 2013'),p.products_id,NULL)) as count5 ,count(DISTINCT if(ps15.specification in ('Winter 2013'),p.products_id,NULL)) as count6 ,count(DISTINCT if(ps15.specification in ('Zomer 2014'),p.products_id,NULL)) as count7
from (products p)
inner join (products_to_categories p2c)
  on (p.products_id = p2c.products_id)
inner join (products_attributes pa)
  on (p.products_id = pa.products_id)
inner join (products_options_values pv)
  on (pa.options_values_id = pv.products_options_values_id)
inner join (products_stock ps)
  on (p.products_id=ps.products_id 
    and pv.products_options_values_id = ps.products_options_values_id2 
    and ps.products_stock_quantity>0)
INNER JOIN products_specifications ps15 
  ON p.products_id = ps15.products_id  
    AND ps15.specifications_id = '15'
    AND ps15.language_id = '1'
INNER JOIN products_specifications ps10 
  ON p.products_id = ps10.products_id  
    AND ps10.specifications_id = '10' 
    AND ps10.language_id = '1' 
where p.products_status = '1'
  and p2c.categories_id in (72,1,23,100,74,24,33,34,35,77,110,25,45,44,40,41,42,85,76,78,83,102,107,111,119,50,52,81,105,108,112,86,88,87,98,89,90,91,96,79,2,54,60,82,109,115,118,53,58,104,55,101,75,56,64,66,67,68,69,70,71,84,103,114,120,80,92,99,93,94,95,97,106,121)  
  AND ps10.specification in ('Meisje')  
  and products_options_values_name in ( 62,"3M/60cm","56-62","0-4 mnd","3m","0-3m","3-6m","3M","62/68","0-6m","50-62" , 68,"6M/67cm","9M/70cm","4-8 mnd","6m","3-6m","6M","62/68","0-6m" , 74,"4-8 mnd","8-12 mnd","6m","9m","6-9m","6M","9M","74/80","6-12m" );

输出结果为：

+--------+--------+--------+--------+--------+--------+--------+
| count1 | count2 | count3 | count4 | count5 | count6 | count7 |
+--------+--------+--------+--------+--------+--------+--------+
|      1 |    289 |    193 |     49 |    192 |    240 |      0 |
+--------+--------+--------+--------+--------+--------+--------+

解释MySQL输出结果：

+----+-------------+-------+-------+-------------------------------------------+-------------------------------------+---------+---------------------------------------+------+--------------------------+
| id | select_type | table | type  | possible_keys                             | key                                 | key_len | ref                                   | rows | Extra                    |
+----+-------------+-------+-------+-------------------------------------------+-------------------------------------+---------+---------------------------------------+------+--------------------------+
|  1 | SIMPLE      | p     | index | PRIMARY,products_id                       | products_id                         | 5       | NULL                                  | 4539 | Using where; Using index |
|  1 | SIMPLE      | p2c   | ref   | PRIMARY                                   | PRIMARY                             | 4       | kikleding.p.products_id               |    1 | Using where; Using index |
|  1 | SIMPLE      | ps15  | ref   | products_id                               | products_id                         | 12      | kikleding.p2c.products_id,const,const |    1 | Using where              |
|  1 | SIMPLE      | ps10  | ref   | products_id                               | products_id                         | 12      | kikleding.p.products_id,const,const   |    1 | Using where              |
|  1 | SIMPLE      | pa    | ref   | idx_products_attributes_products_id       | idx_products_attributes_products_id | 4       | kikleding.p.products_id               |    6 |                          |
|  1 | SIMPLE      | pv    | ref   | PRIMARY                                   | PRIMARY                             | 4       | kikleding.pa.options_values_id        |    2 | Using where              |
|  1 | SIMPLE      | ps    | ref   | idx_products_stock_attributes,products_id | idx_products_stock_attributes       | 4       | kikleding.ps15.products_id            |    6 | Using where              |
+----+-------------+-------+-------+-------------------------------------------+-------------------------------------+---------+---------------------------------------+------+--------------------------+

我尝试为大部分表建立索引，但在 explain 第一行仍然显示 NULL。

现在它输出了 7 列，有时我需要输出 50 列。

你有什么建议吗？

- Md Dang

products_options_values_names是什么？它属于products表吗？如果是，这可能是规范化数据模型的一个选项，以便优化器可以使用外键。 - ErnestV

它属于product_options_values表。 - Md Dang

你的服务器硬件规格是什么？你确定你的缓冲区足够大吗？（这个查询足够简单，计算7 x 4k行也不应该花费很长时间）。 - RandomSeed

Xeon e5405 4x CPU @ 2.0 GHz，6144 KB缓存| 4000内存，3552已使用，415空闲，360缓冲，2124已缓存。应该足够了吧？ - Md Dang

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- RandomSeed · Accepted Answer

ref = NULL 只意味着从 p 表（即product）中选择的行没有与其他行连接。这些行是查询中首先被选中的行，然后将其他表的行与它们连接。我总是期望 EXPLAIN 的第一行显示 ref = NULL。

基本上，您的执行计划如下：

提取符合 WHERE 条件的 products 表中的行。
然后提取匹配步骤1的 products_to_categories 表中的行，该匹配基于 products.products_id 字段。
以此类推处理所有表。

建议添加的索引：

table(columns)
products(product_id, products_status)
products_specifications(products_id, specification, language_id, specifications_id)
products_to_categories(products_id, categories_id)

第一个索引应该有很大帮助，而对于其他两个我不会期望太多。

我认为问题出在您使用了许多 COUNT(IF())。这是不太好的方法，引擎并没有为这种查询进行优化。相反，您应该尝试返回像这样的结果集：

+---------------+-------+
| specification | count |
+---------------+-------+
| All Season    |     1 |
+---------------+-------+
| Winter        |   289 |
+---------------+-------+
| ...           |   ... |
+---------------+-------+

您的查询应该如下所示：

SELECT
    specification,
    COUNT(*)
FROM products
JOIN ... -- your current JOIN list
GROUP BY specification -- this is the important bit

...并且应该几乎瞬间完成，即使没有额外的索引（或者可能在products_specifications(products_id, specification)上）