如何在Extra中优化MYSQL：-Using where;使用临时;使用filesort [英] How to Optimize MYSQL in Extra :-Using where; Using temporary; Using filesort

问题描述

此查询的正确索引是什么。

What is the proper indexing for this query.

我尝试给出了这个查询的不同索引组合，但它仍在使用tempory，使用filesort等。

I tried given different combinations of indexes for this query but it is still using from using tempory , using filesort etc.

总表数据 - 7,60,346

Total table data - 7,60,346

产品 ='礼服' - 总行数= 122 554

product= 'Dresses' - Total rows = 122 554

CREATE TABLE IF NOT EXISTS `product_data` (
  `table_id` int(11) NOT NULL AUTO_INCREMENT,
  `id` int(11) NOT NULL,
  `price` int(11) NOT NULL,
  `store` varchar(255) NOT NULL,
  `brand` varchar(255) DEFAULT NULL,
  `product` varchar(255) NOT NULL,
  `model` varchar(255) NOT NULL,
  `size` varchar(50) NOT NULL,
  `discount` varchar(255) NOT NULL,
  `gender_id` int(11) NOT NULL,
  `availability` int(11) NOT NULL,
  PRIMARY KEY (`table_id`),
  UNIQUE KEY `table_id` (`table_id`),
  KEY `id` (`id`),
  KEY `discount` (`discount`),
  KEY `step_one` (`product`,`availability`),
  KEY `step_two` (`product`,`availability`,`brand`,`store`),
  KEY `step_three` (`product`,`availability`,`brand`,`store`,`id`),
  KEY `step_four` (`brand`,`store`),
  KEY `step_five` (`brand`,`store`,`id`)
) ENGINE=InnoDB ;

查询：

SELECT id ,store,brand FROM `product_data` WHERE product='dresses' and 
availability='1' group by brand,store order by store limit 10;

excu..time： - （总共10次，查询耗时1.0941秒）

excu..time :- (10 total, Query took 1.0941 sec)

EXPLAIN PLAN：

EXPLAIN PLAN :

possible_keys： - step_one，step_two，step_three，step_four，step_five

possible_keys :- step_one, step_two, step_three, step_four, step_five

键： - step_two

ref： - const，const

行： - 229438

额外： - 使用where;使用临时;使用filesort

我试过这些索引

密钥 step_one（产品，可用性）

Key step_one (product,availability)

密钥 step_two（产品，可用性，品牌，商店）

Key step_two (product,availability,brand,store)

密钥 step_three（产品，可用性，品牌，商店， id ）

Key step_three (product,availability,brand,store,id)

Key step_four（品牌，商店）

Key step_four (brand,store)

密钥 step_five（品牌 ，商店， id ）

Key step_five (brand,store,id)

推荐答案

真正的问题不是索引，而是 GROUP BY 和 ORDER BY 之间的不匹配利用 LIMIT 。

The real problem is not the index, but the mismatch between GROUP BY and ORDER BY preventing taking advantage of LIMIT.

此

INDEX(product, availability, store, brand, id)

将是覆盖并按正确的顺序。但请注意，我已经交换商店和品牌 ...

will be "covering" and in the right order. But note that I have swapped store and brand...

将查询更改为

SELECT  id ,store,brand
    FROM  `product_data`
    WHERE  product='dresses'
      and  availability='1'
    GROUP BY store, brand    -- change
    ORDER BY store, brand    -- change
    limit  10;

这会改变 GROUP BY 开始 store ，以反映 ORDER BY 订购 - 这可以避免额外的排序。并且它将 ORDER BY 更改为与 GROUP BY 相同，以便两者可以合并。

That changes the GROUP BY to start with store, to reflect the ORDER BY ordering -- this avoid an extra sort. And it changes the ORDER BY to be identical to the GROUP BY so that the two can be combined.

鉴于这些变化， INDEX 现在可以一直到 LIMIT ，从而允许处理只查看10行，而不是更大的集合。

Given those changes, the INDEX can now go all the way through to the LIMIT, thereby allowing the processing to look at only 10 rows, not a much larger set.

任何低于所有这些变化的东西都不会那么高效。

Anything less than all these changes will not be as efficient.

进一步讨论：

INDEX(product, availability,   -- these two can be in either order
      store, brand,      -- must match both `GROUP BY` and `ORDER BY`
      id)   -- tacked on (on the end) to make it "covering"

覆盖意味着所有 SELECT 可在 INDEX 中找到，因此无需覆盖数据。

"Covering" means that all the columns for the SELECT are found in the INDEX, so no need to reach over into the data.

但是...... 整个查询没有意义因为 SELECT <中包含 id / code> 。如果你想找到哪些商店有可用的礼服，那么摆脱 id 。如果要列出所有可用的连衣裙，请将 id 更改为 GROUP_CONCAT（id）。

But... The whole query does not make sense because of the inclusion of id in the SELECT. If you want to find what stores have available dresses, then get rid of id. If you want to list all the available dresses, then change id to GROUP_CONCAT(id).

这篇关于如何在Extra中优化MYSQL：-Using where;使用临时;使用filesort的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！