MySQL查询使网站瘫痪

Question

每隔一段时间，我们的网站就会完全瘫痪。

看看SHOW FULL PROCESSLIST;，我注意到当这种情况发生时，有一个特定的查询在很长一段时间(有时是350秒)内都是"Copying to tmp table“，而几乎所有其他查询都是"Locked”。

我不理解的是，在90%的时间里，这个查询都运行得很好。我在进程列表中看到它正在进行，并且在大多数时间内完成得相当快。此查询由我们主页上的ajax调用调用，以显示基于您的浏览历史记录的产品推荐(类似amazon)。

只是有时候，随机的(但太频繁)，它被卡住在“复制到临时表格”。

下面是一个捕获的查询实例，当我查看它时，它已经运行了109秒：

SELECT DISTINCT product_product.id, product_product.name, product_product.retailprice, product_product.imageurl, product_product.thumbnailurl,   product_product.msrp
FROM product_product, product_xref, product_viewhistory
WHERE
(
(product_viewhistory.productId = product_xref.product_id_1 AND product_xref.product_id_2 = product_product.id)
OR
(product_viewhistory.productId = product_xref.product_id_2 AND product_xref.product_id_1 = product_product.id)
)
AND product_product.outofstock='N'
AND product_viewhistory.cookieId = '188af1efad392c2adf82'
AND product_viewhistory.productId IN (24976, 25873, 26067, 26073, 44949, 16209, 70528, 69784, 75171, 75172)
ORDER BY product_xref.hits DESC
LIMIT 10

当然，"cookieId“和"productId”列表会根据请求动态变化。

我使用带有PDO的php。

编辑:我认为涉及到的一些表结构可能会有所帮助：

CREATE TABLE IF NOT EXISTS `product_viewhistory` (
  `userId` int(10) unsigned NOT NULL default '0',
  `cookieId` varchar(30) collate utf8_unicode_ci NOT NULL,
  `productId` int(11) NOT NULL,
  `viewTime` timestamp NOT NULL default CURRENT_TIMESTAMP,
  KEY `userId` (`userId`),
  KEY `cookieId` (`cookieId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `product_xref` (
  `id` int(11) NOT NULL auto_increment,
  `product_id_1` int(11) default NULL,
  `product_id_2` int(11) default NULL,
  `hits` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `IDX_PROD1` (`product_id_1`),
  KEY `IDX_PROD2` (`product_id_2`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=184531 ;

CREATE TABLE IF NOT EXISTS `product_product` (
  `id` int(11) NOT NULL auto_increment,
  `supplierid` int(11) NOT NULL default '0',
  `suppliersku` varchar(100) NOT NULL default '',
  `name` varchar(100) NOT NULL default '',
  `cost` decimal(10,2) NOT NULL default '0.00',
  `retailprice` decimal(10,2) NOT NULL default '0.00',
  `weight` decimal(10,2) NOT NULL default '0.00',
  `imageurl` varchar(255) NOT NULL default '',
  `thumbnailurl` varchar(255) NOT NULL default '',
  `sizechartlink` varchar(255) NOT NULL default '',
  `content` text NOT NULL,
  `remark` varchar(100) NOT NULL default '',
  `colorchartlink` varchar(255) default NULL,
  `outofstock` char(1) NOT NULL default '',
  `summary` text NOT NULL,
  `freehandoutlink` varchar(255) default NULL,
  `msrp` decimal(10,2) default NULL,
  `enabled` tinyint(1) NOT NULL default '1',
  `sales_score` float NOT NULL default '0',
  `sales_score_offset` float NOT NULL default '0',
  `date_added` timestamp NULL default CURRENT_TIMESTAMP,
  `brand` varchar(255) default NULL,
  `tag_status` varchar(20) default NULL,
  PRIMARY KEY  (`id`),
  KEY `product_retailprice_idx` (`retailprice`),
  KEY `suppliersku` (`suppliersku`),
  FULLTEXT KEY `product_name_summary_ft` (`name`,`summary`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

此外，还应请求提供解释的结果：

+----+-------------+---------------------+------+---------------------+----------+---------+-------+-------+------------------------------------------------+
| id | select_type | table               | type | possible_keys       | key      | key_len | ref   | rows  | Extra                                          |
+----+-------------+---------------------+------+---------------------+----------+---------+-------+-------+------------------------------------------------+
|  1 | SIMPLE      | product_xref        | ALL  | IDX_PROD1,IDX_PROD2 | NULL     | NULL    | NULL  | 30035 | Using temporary; Using filesort                |
|  1 | SIMPLE      | product_viewhistory | ref  | cookieId            | cookieId | 92      | const |   682 | Using where                                    |
|  1 | SIMPLE      | product_product     | ALL  | PRIMARY             | NULL     | NULL    | NULL  | 31880 | Range checked for each record (index map: 0x1) |
+----+-------------+---------------------+------+---------------------+----------+---------+-------+-------+------------------------------------------------+
3 rows in set (0.00 sec)

新的更新版本，因为我意识到我根本不需要product_viewhistory。我被旧代码遗弃了：

SELECT DISTINCT product_product.id, product_product.name, product_product.retailprice, product_product.imageurl, product_product.thumbnailurl, product_product.msrp
FROM product_product, product_xref
WHERE 
(
(product_xref.product_id_1 IN (24976, 25873, 26067, 26073, 44949, 16209, 70528, 69784, 75171, 75172) AND product_xref.product_id_2 = product_product.id)
OR 
(product_xref.product_id_2 IN (24976, 25873, 26067, 26073, 44949, 16209, 70528, 69784, 75171, 75172) AND product_xref.product_id_1 = product_product.id)
)
AND product_product.outofstock='N'
ORDER BY product_xref.hits DESC
LIMIT 10

新的解释是：

+----+-------------+-----------------+-------------+---------------------+---------------------+---------+------+-------+-------------------------------------------------------------------------------------+
| id | select_type | table           | type        | possible_keys       | key                 | key_len | ref  | rows  | Extra                                                                               |
+----+-------------+-----------------+-------------+---------------------+---------------------+---------+------+-------+-------------------------------------------------------------------------------------+
|  1 | SIMPLE      | product_xref    | index_merge | IDX_PROD1,IDX_PROD2 | IDX_PROD1,IDX_PROD2 | 5,5     | NULL |    32 | Using sort_union(IDX_PROD1,IDX_PROD2); Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | product_product | ALL         | PRIMARY             | NULL                | NULL    | NULL | 31880 | Range checked for each record (index map: 0x1)                                      |
+----+-------------+-----------------+-------------+---------------------+---------------------+---------+------+-------+-------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Answer 1

首先要做的是看看MySQL和EXPLAIN在幕后做了什么，然后从那里开始。听起来你有一些索引要做。

Answer 2

我将您的查询重写为：

   SELECT DISTINCT
          pp.id, 
          pp.name, 
          pp.retailprice, 
          pp.imageurl, 
          pp.thumbnailurl,
          pp.msrp
     FROM PRODUCT_PRODUCT pp
LEFT JOIN PRODUCT_XREF px1 ON px1.product_id_2 = pp.id
LEFT JOIN PRODUCT_XREF px2 ON px2.product_id_1 = pp.id
    WHERE EXISTS(SELECT NULL
                   FROM PRODUCT_VIEWHISTORY pvh
                  WHERE pvh.productid = px1.product_id_1
                    AND pvh.cookieId = '188af1efad392c2adf82'
                    AND pvh.productId IN (24976, 25873, 26067, 26073, 44949, 16209, 70528, 69784, 75171, 75172))
       OR EXISTS(SELECT NULL
                   FROM PRODUCT_VIEWHISTORY pvh
                  WHERE pvh.productid = px2.product_id_2
                    AND pvh.cookieId = '188af1efad392c2adf82'
                    AND pvh.productId IN (24976, 25873, 26067, 26073, 44949, 16209, 70528, 69784, 75171, 75172))
      AND pp.outofstock = 'N'
 ORDER BY GREATEST(px1.hits, px2.hits) DESC
    LIMIT 10

如果ORDER BY不依赖于PRODUCT_XREF.hits列，事情会更简单。太糟糕了，MySQL不支持通用表表达式(CTE)/Subquery分解...

拥有两个不同的product_id引用是一种非常值得怀疑的方法。我建议回顾一下数据模型。

Answer 3

你需要优化你的查询。从mysql提示符或带有EXPLAIN的mysql客户端运行它，并检查执行计划。您可能需要向表中添加索引。请记住，如果您连续多次运行此查询，mysql服务器将缓存结果，您不应依赖它们的快速执行时间。也许这就是为什么您的查询在90%的时间内都运行得很好的原因。