Блог

 SELECT `productTitle`, `orderCnt`, `promPCPriceStr`,
  `productImgUrl`, `oriPriceStr`, `detailUrl`,
  (SELECT count(id) FROM orders t4 
   WHERE t4.productId = t1.productId 
     AND DATE( t4.`date`) > DATE_SUB(CURDATE(), INTERVAL 2 DAY)
  ) as ordertoday
FROM `products` t1
WHERE `orderCnt` > 0 
 AND `orderCnt` < 2000 
 AND `promPCPriceStr` > 0 
 AND `promPCPriceStr` < 2000 
HAVING ordertoday > 5 AND ordertoday < 2000 
order by ordertoday desc limit 150
  

Завершение этого запроса занимает 18 секунд, когда я запускаю команду explain на нем, это показывает

он не использует ключи индекса!

Используемые таблицы

Таблица продуктов

 CREATE TABLE `products` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `productId` bigint(20) NOT NULL,
 `detailUrl` text CHARACTER SET utf32 NOT NULL,
 `belongToDSStore` int(11) NOT NULL,
 `promPCPriceStr` float NOT NULL DEFAULT '-1',
 `oriPriceStr` float NOT NULL DEFAULT '-1',
 `orderCnt` int(11) NOT NULL,
 `productTitle` text CHARACTER SET utf32 NOT NULL,
 `productImgUrl` text CHARACTER SET utf32 NOT NULL,
 `created_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `cat` bigint(20) NOT NULL DEFAULT '-1',
 PRIMARY KEY (`id`),
 UNIQUE KEY `productId` (`productId`),
 KEY `orderCnt` (`orderCnt`),
 KEY `cat` (`cat`),
 KEY `promPCPriceStr` (`promPCPriceStr`)
) ENGINE=InnoDB AUTO_INCREMENT=37773 DEFAULT CHARSET=latin1
  

Таблица заказов

 CREATE TABLE `orders` (
 `oid` int(11) NOT NULL AUTO_INCREMENT,
 `countryCode` varchar(10) NOT NULL,
 `date` datetime NOT NULL,
 `id` bigint(20) NOT NULL,
 `productId` bigint(20) NOT NULL,
 PRIMARY KEY (`oid`),
 UNIQUE KEY `id` (`id`),
 KEY `date` (`date`),
 KEY `productId` (`productId`)
) ENGINE=InnoDB AUTO_INCREMENT=9790205 DEFAULT CHARSET=latin1
  

MySQL не будет использовать индекс, даже если он существует в столбце, который вы ищете, если значения, которые вы ищете, отображаются в большом подмножестве строк.

Я провел тест с MySQL 5.6. Я создал таблицу с ~ 1 000 000 строк, со столбцом x со случайными значениями, равномерно распределенными между 1 и 1000. В столбце есть индекс x .

В зависимости от моих условий поиска я вижу, что индекс используется, если я ищу диапазон значений, соответствующий достаточно небольшому подмножеству строк, в противном случае он решает, что использование индекса — это слишком много проблем, и просто выполняет сканирование таблицы:

 mysql> explain select * from foo where x < 50;
 ---- ------------- ------- ------- --------------- ------ --------- ------ -------- ----------------------- 
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows   | Extra                 |
 ---- ------------- ------- ------- --------------- ------ --------- ------ -------- ----------------------- 
|  1 | SIMPLE      | foo   | range | x             | x    | 4       | NULL | 102356 | Using index condition |
 ---- ------------- ------- ------- --------------- ------ --------- ------ -------- ----------------------- 

mysql> explain select * from foo where x < 100;
 ---- ------------- ------- ------ --------------- ------ --------- ------ --------- ------------- 
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
 ---- ------------- ------- ------ --------------- ------ --------- ------ --------- ------------- 
|  1 | SIMPLE      | foo   | ALL  | x             | NULL | NULL    | NULL | 1046904 | Using where |
 ---- ------------- ------- ------ --------------- ------ --------- ------ --------- ------------- 
  

Я бы сделал вывод, что условия поиска вашего запроса соответствуют довольно большой части строк, и MySQL решает, что индексы в этих столбцах не стоит использовать.

 WHERE `orderCnt` > 0 
 AND `orderCnt` < 2000 
 AND `promPCPriceStr` > 0 
 AND `promPCPriceStr` < 2000 
  

Если вы считаете, что MySQL делает неправильный выбор, вы можете попробовать использовать подсказку индекса, чтобы сообщить MySQL, что сканирование таблицы является чрезмерно дорогостоящим. Это побудит его использовать индекс (если индекс соответствует условию поиска).

 mysql> explain select * from foo force index (x) where x < 100;
 ---- ------------- ------- ------- --------------- ------ --------- ------ -------- ----------------------- 
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows   | Extra                 |
 ---- ------------- ------- ------- --------------- ------ --------- ------ -------- ----------------------- 
|  1 | SIMPLE      | foo   | range | x             | x    | 4       | NULL | 216764 | Using index condition |
 ---- ------------- ------- ------- --------------- ------ --------- ------ -------- ----------------------- 
  

Я бы написал запрос таким образом, без какого-либо подзапроса:

 SELECT t.productTitle, t.orderCnt, t.promPCPriceStr,
  t.productImgUrl, t.oriPriceStr, t.detailUrl,
  COUNT(o.id) AS orderToday
FROM products t
LEFT JOIN orders o ON t.productid = o.productid AND o.date > CURDATE() - INTERVAL 2 DAY
WHERE t.orderCnt > 0 AND t.orderCnt < 2000
 AND t.promPCPriceStr > 0 AND t.promPCPriceStr < 2000
GROUP BY t.productid
HAVING ordertoday > 5 AND ordertoday < 2000
ORDER BY ordertoday DESC LIMIT 150
  

Когда я ОБЪЯСНЯЮ запрос, я получаю этот отчет:

  ---- ------------- ------- ------ ----------------------------------- ----------- --------- ------------------ ------ ---------------------------------------------- 
| id | select_type | table | type | possible_keys                     | key       | key_len | ref              | rows | Extra                                        |
 ---- ------------- ------- ------ ----------------------------------- ----------- --------- ------------------ ------ ---------------------------------------------- 
|  1 | SIMPLE      | t     | ALL  | productId,orderCnt,promPCPriceStr | NULL      | NULL    | NULL             | 9993 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | o     | ref  | date,productId                    | productId | 8       | test.t.productId |    1 | Using where                                  |
 ---- ------------- ------- ------ ----------------------------------- ----------- --------- ------------------ ------ ---------------------------------------------- 
  

Он по-прежнему выполняет сканирование таблицы products , но он объединяет соответствующие совпадающие строки orders с помощью поиска по индексу вместо коррелированного подзапроса.

Я заполнил свои таблицы случайной датой, чтобы создать 98 846 строк продуктов и 215 508 строк заказов. Когда я запускаю запрос, это занимает около 0,18 секунды.

Хотя, когда я запускаю ваш запрос с соответствующим подзапросом, это занимает 0,06 секунды. Я не знаю, почему ваш запрос такой медленный. Возможно, вы работаете на недостаточно мощном сервере.

Я запускаю свой тест на Macbook Pro 2017 с процессором i7 и 16 ГБ оперативной памяти.

В обеих таблицах контрпродуктивно иметь как an AUTO_INCREMENT PRIMARY KEY , так и BIGINT столбец, который есть UNIQUE . Избавьтесь от столбца AI и переместите другой в PK. Это может потребовать изменения части вашего кода, поскольку столбец AI исчез.

Что касается подзапроса…

   (SELECT count(id) FROM orders t4 
   WHERE t4.productId = t1.productId 
     AND DATE( t4.`date`) > DATE_SUB(CURDATE(), INTERVAL 2 DAY)
  ) as ordertoday
  

Измените COUNT(id) на COUNT(*) , если вам не нужно проверять id наличие NOT NULL (в чем я сомневаюсь).

date Столбец скрыт в вызове функции, поэтому никакой индекс не будет полезен. Итак, измените тест даты на

 AND t4.`date` > CURDATE - INTERVAL 2 DAY
  

Затем добавьте этот составной индекс. (Это также поможет с переформулировкой Karwin).

 INDEX(productId, date)