mysql - Extra :-Using where; Using temporary; Using filesort如何优化MYSQL

Question

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

我尝试为此查询提供不同的索引组合，但它仍在使用临时文件、文件排序等。

总表数据 - 7,60,346

产品= '连衣裙' - 总行数 = 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 total, Query took 1.0941 sec)

解释计划:

---

可能的键:- step_one、step_two、step_three、step_four、step_five

关键:- step_two

引用:- 常量，常量

行:- 229438

额外:-使用哪里；使用临时的；使用文件排序

我试过这些索引

---

Key step_one(产品，可用性)

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)

Answer 1

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

这个

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

将以正确的顺序“覆盖”。但请注意，我已经交换了 store 和 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 开头，以反射(reflect) ORDER BY 排序——这避免了额外的排序。并将 ORDER BY 更改为与 GROUP BY 相同，以便将两者结合。

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

任何少于所有这些变化都不会那么有效。

进一步讨论:

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 中找到，因此无需访问数据.

但是...整个查询没有意义，因为 SELECT 中包含了 id。如果您想查找哪些商店有可用的连衣裙，请去掉 id。如果您想列出所有可用的衣服，请将 id 更改为 GROUP_CONCAT(id)。