当在查询中包含子查询时，MySQL 将 CPU 推至 100%

Question

在此先感谢您的帮助。

我的服务器的 CPU 使用率突然达到 100%。在关闭所有脚本并逐渐恢复它们之后，我将问题追溯到在两个地方使用的一种特定类型的查询。查询是:

select * from zzproductdata
where amazonproductid <> '' and amazoncategory1 ='' and disabled = 0
and productid in (select productid from zzdropshipstock where quantity >= 10)

并且:

select 'ZZ Product Data With Image' as 'Statistic', count(zzproductdataid) as 
'Number' from zzproductdata
where imagescollecteddate <> '0000-00-00' and zzproductdata.productid in 
(select productid from zzdropshipstock where quantity >= 80)

两个查询都包含一个子查询，并且都处理一个名为 zzproductdata 的表。

检查了几件事:1.其他包含子查询的查询继续工作。2. 其他包含 zzproductdata 的查询继续工作，只要它们不包含子查询。

当上述两个查询运行时，CPU 在大约 90 秒内逐渐增加到 100%。它保持在 100%，似乎没有恢复——我让它这样运行了 3 个小时，但它没有停止或完成查询。

表 zzproductdata 逐渐收集数据，最近 12 天增加到 1,500,000 条记录。我监控每个查询在数据库中运行所花费的时间，这两个查询逐渐变长但并不过分——它们已经从 2.5 秒增加到 5.6 秒。

突然之间，他们在没有任何警告的情况下开始使用 100% CPU。在终端的 mysqladmin processlist 中，我看到两条记录 - 一条显示进程处于“ sleep ”状态，另一条显示“正在发送数据”状态。

我怀疑查询刚刚达到某种 sql 缓存限制，但我无法确定 mysql 使用哪个缓存限制来使用子查询。我试过更改 mysql 中的一些设置，但它们似乎都没有效果。有什么建议么？

编辑

CREATE TABLE `zzproductdata` (
 `zzproductdataid` int(11) NOT NULL AUTO_INCREMENT,
 `zzproductdataname` text NOT NULL,
 `disabled` int(11) NOT NULL,
 `datecreated` date NOT NULL,
 `masteronly` int(11) NOT NULL,
 `productid` int(11) NOT NULL,
 `isbn` text NOT NULL,
 `ean` text NOT NULL,
 `publishername` text NOT NULL,
 `imagethumbnail` text NOT NULL,
 `imagefull` text NOT NULL,
 `amazonproductid` text NOT NULL,
 `productdatasource` text NOT NULL,
 `datelastupdatedamazon` date NOT NULL,
 `datelastupdatedgoogle` date NOT NULL,
 `googleproductid` text NOT NULL,
 `publicationdate` date NOT NULL,
 `binding` text NOT NULL,
 `imagescollecteddate` date NOT NULL,
 `amazoncategory1` text NOT NULL,
 `amazoncategory2` text NOT NULL,
 `amazoncategory3` text NOT NULL,
 `datelastcheckedamazoncategory` date NOT NULL,
 `datelastupdatedopenlibrary` date NOT NULL,
 PRIMARY KEY (`zzproductdataid`)
)
ENGINE=InnoDB
AUTO_INCREMENT=1674296
DEFAULT CHARSET=utf

CREATE TABLE `zzdropshipstock` (
 `zzdropshipstockid` int(11) NOT NULL AUTO_INCREMENT,
 `zzdropshipstockname` text NOT NULL,
 `disabled` int(11) NOT NULL,
 `datecreated` date NOT NULL,
 `masteronly` int(11) NOT NULL,
 `zzdropshipsupplierid` int(11) NOT NULL,
 `isbn` varchar(13) NOT NULL,
 `quantity` double NOT NULL,
 `suppliercode` text NOT NULL,
 `supplierprice` double NOT NULL,
 `standardshipcost` double NOT NULL,
 `weightgram` double NOT NULL,
 `rrp` double NOT NULL,
 `productid` int(11) NOT NULL,
 `initialimportdate` date NOT NULL,
 `lastupdateddate` date NOT NULL,
 `changed` int(11) NOT NULL,
 `lastcheckedproductiddate` date NOT NULL,
 `lastcheckedproductinamazondate` date NOT NULL,
 `lastcheckedrawstockdata` date NOT NULL,
 `lastcheckedproductstockitemlive` date NOT NULL,
 `changedquantity` int(11) NOT NULL,
 PRIMARY KEY (`zzdropshipstockid`),
 KEY `isbn` (`isbn`)
)
ENGINE=InnoDB
AUTO_INCREMENT=7037817
DEFAULT CHARSET=latin1

在第一个查询中使用 EXPLAIN - 我得到了这个:

这是我在终端中看到的:

我尝试添加 DISTINCT 但没有效果。它减缓了 CPU 使用率的上升，因此几乎用了 200 秒才达到 100% 的 CPU 使用率，但它没有运行查询。

查询的第一部分在 0.23 秒内返回 1,300,000 条记录。子查询在 2.3 秒内返回 118,000 条记录。这是您分别运行这两个部分的时候。将两者合并为一个查询，目前应该返回大约 15,000 条记录。

Answer 1

首先，您可以尝试添加这些索引:

1. ALTER TABLE zzdropshipstock ADD INDEX IX_zzdropshipstock_key (quantity, productid);

2. ALTER TABLE zzproductdata ADD INDEX IX_zzproductdata_productid (productid);

然后执行ANALYZE TABLE zzdropshipstock、ANALYZE TABLE zzproductdata并显示新的EXPLAIN计划。