mysql - 如何让mysql查询更快

Question

由@RolandStarke 先生解决

将日期（t4. date）更改为 t4。date它将使用索引

查询耗时 0.5519 秒。

---

我试图让这个查询更快我试过这个

第一个查询# 没有连接

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

结果是

查询耗时 4.0222 秒。

解释结果

第二个查询# with join

SELECT  `productTitle`,`orderCnt`,`promPCPriceStr`,`productImgUrl`,`oriPriceStr`,`detailUrl` FROM `products` p 
INNER JOIN
(
select count(id) AS filterd_orders,productId from orders t4 WHERE DATE( t4.`date`) > DATE_SUB(CURDATE(), INTERVAL 2 DAY)  
GROUP by productId
) o

ON p.productID = o.productId

AND `orderCnt` > 0 AND `orderCnt` < 2000 AND `promPCPriceStr` > 0 AND `promPCPriceStr` < 2000 
and filterd_orders > 5 
AND filterd_orders < 2000 
order by filterd_orders desc limit 30

查询耗时 4.8217 秒。

解释结果

子查询结果

select count(id) AS filterd_orders,productId from orders t4 WHERE DATE( t4.`date`) > DATE_SUB(CURDATE(), INTERVAL 3 DAY) GROUP by productId

查询耗时 0.0044 秒。

解释结果

表结构

产品表

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

还

innodb_buffer_pool_size = {DBInstanceClassMemory*3/4}

服务器硬件

• AWS RDS db.t2.medium
• 4 GB 内存
• 2 个 CPU 核心 2.5Hz

标签： mysqlperformance