mysql - SQL 查询速度慢且未使用索引

Question

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 秒才能完成

它不使用索引键!

使用的表格

产品表

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

Answer 1

即使您搜索的列中存在一个索引，如果您搜索的值出现在大部分行中，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 做出了错误的选择，您可以尝试使用 index hint告诉 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 秒。我不知道为什么你的查询这么慢。您可能在动力不足的服务器上运行。

我在配备 i7 CPU 和 16GB RAM 的 Macbook Pro 2017 上运行我的测试。