MySQL SELECT 慢，但只有 2 x 300K 行和索引

讓下面的 MySQL SELECT 查詢非常慢。

儘管只有 300K 行和索引，但執行它需要大約 1.0 秒，所以我很想找到一種方法讓它更快地執行，因為它是一個需要一次又一次執行的查詢。

查詢：

SELECT p.id, p.image, c.name, s.name, MIN(p.saleprice)
FROM products p 
JOIN shops s ON p.shopid = s.id 
JOIN products_category pc ON p.id = pc.product_id 
JOIN categories c ON pc.category_id = c.id
WHERE p.brand_id > 0
AND pc.category_id = 46
AND pc.active = 1
AND p.price > 0
AND p.saleprice > 0
AND p.saleprice < p.price
AND (p.last_seen > DATE_SUB(NOW(), INTERVAL 2 DAY))
GROUP BY p.image

該查詢返回 960 行。

表products有 300.000 行，這些列 + 一個索引：

CREATE TABLE `products` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(512) DEFAULT NULL,
 `shopid` int(11) DEFAULT NULL,
 `image` varchar(512) DEFAULT NULL,
 `price` int(11) DEFAULT NULL,
 `saleprice` int(11) DEFAULT NULL,
 `last_seen` datetime DEFAULT NULL,
 `brand_id` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `brand_id etc` (`brand_id`,`shopid`,`last_seen`,`price`,`saleprice`)
) ENGINE=InnoDB AUTO_INCREMENT=391671 DEFAULT CHARSET=utf8

表products_categories也有 300.000 行，這些列 + 兩個索引：

CREATE TABLE `products_category` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `product_id` int(11) DEFAULT NULL,
 `category_id` int(11) DEFAULT NULL,
 `active` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `category_id etc` (`category_id`,`active`),
 KEY `product_id etc` (`product_id`,`active`)
) ENGINE=InnoDB AUTO_INCREMENT=373364 DEFAULT CHARSET=utf8

商店表。它有 15 行和這些列：

CREATE TABLE `shops` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(256) DEFAULT NULL,
 `active` int(11) DEFAULT '1',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=56 DEFAULT CHARSET=utf8

基於這裡的類似問題，我嘗試使用內部選擇嵌套事物：

SELECT p.id, p.image, c.name, s.name, MIN(p.saleprice)
FROM 
(SELECT * FROM products WHERE p.brand_id > 0 AND price > 0 AND saleprice > 0 AND saleprice < price AND (p.last_seen > DATE_SUB(NOW(), INTERVAL 3 DAY))) p 
JOIN shops s ON p.shopid = s.id 
JOIN products_category pc ON p.id = pc.product_id 
JOIN categories c ON pc.category_id = c.id 
WHERE pc.category_id = 46
AND pc.active = 1
GROUP BY p.image

它沒有幫助。帶有內部選擇的版本需要大約 1.3 秒才能執行。

問題似乎是 products 和 products_category 之間的連接，即兩個各有 300K 行的大表。

GROUP BY p.image是必要的（有些產品只是尺寸不同，所以我們使用 p.image 將它們過濾掉）。

也許我可以用我的索引做一個技巧？或者你們中的任何人都可以發現我應該優化的其他東西嗎？

查詢的解釋：

id  select_type table   partitions  type    possible_keys                   key             key_len ref             rows    filtered    Extra
1   SIMPLE      c       \N          const   PRIMARY                         PRIMARY         4       const           1       100.00      Using temporary; Using filesort
1   SIMPLE      pc      \N          ref     category_id etc,product_id etc  category_id etc 10      const,const     43104   100.00      Using where
1   SIMPLE      p       \N          eq_ref  PRIMARY,brand_id etc            PRIMARY         4       pc.product_id   1       5.00        Using where
1   SIMPLE      s       \N          eq_ref  PRIMARY                         PRIMARY         4       p.shopid        1       100.00      \N

鍵brand_id etc( brand_id, shopid, last_seen, price, saleprice)

這個索引不能很好地支持這個查詢。

索引中的第一個欄位是brand_id，但您僅按“> 0”進行過濾。

由於我懷疑您有許多帶有負ID 的品牌，因此這可能不會有太大幫助。

索引中的下一個欄位是 shopid，它根本不會出現在您的 where 子句中。

基於這個粗略的分析（MySQL 會做更多的事情），這個索引可能會被打折，而表會被串列掃描。

選擇要返回的行的更好鑑別器是last_seen。考慮在其上添加索引（或將其作為第一個欄位），您應該會看到一些改進。

引用自：https://dba.stackexchange.com/questions/270693