MySQL与InnoDB:如何避免使用COUNT? [英] MySQL with InnoDB: How to avoid using COUNT?

问题描述

因此，我有一个表，其中填充了约700K个条目用于测试，这表明它在MySQL查询中很痛苦.

So I have a table which I populated with ~700K entries for testing, and it is showing it's pain in MySQL queries.

相关表格:

CREATE TABLE `trades` (
 `tradeId` int(11) NOT NULL AUTO_INCREMENT,
 `userId` int(11) NOT NULL,
 `visible` int(11) NOT NULL DEFAULT '1',
 `sourceItem` int(11) NOT NULL,
 `sourceKeyTierId` int(11) DEFAULT NULL,
 `sourceKeyTypeId` int(11) DEFAULT NULL,
 `sourceKeyAmount` int(11) DEFAULT NULL,
 `sourceModId` int(11) DEFAULT NULL,
 `sourceModLevel` int(11) DEFAULT NULL,
 `destinationItem` int(11) NOT NULL,
 `destinationPlatinum` int(11) DEFAULT NULL,
 `destinationKeyTierId` int(11) DEFAULT NULL,
 `destinationKeyTypeId` int(11) DEFAULT NULL,
 `destinationKeyAmount` int(11) DEFAULT NULL,
 `destinationModId` int(11) DEFAULT NULL,
 `destinationModLevel` int(11) DEFAULT NULL,
 `added` datetime NOT NULL,
 PRIMARY KEY (`tradeId`),
 KEY `userId` (`userId`),
 KEY `sourceKeyTierId` (`sourceKeyTierId`),
 KEY `sourceKeyTypeId` (`sourceKeyTypeId`),
 KEY `sourceModId` (`sourceModId`),
 KEY `destinationKeyTierId` (`destinationKeyTierId`),
 KEY `destinationKeyTypeId` (`destinationKeyTypeId`),
 KEY `destinationModId` (`destinationModId`),
 CONSTRAINT `trades_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `users` (`userId`),
 CONSTRAINT `trades_ibfk_2` FOREIGN KEY (`sourceKeyTierId`) REFERENCES `keytiers` (`keyTierId`),
 CONSTRAINT `trades_ibfk_3` FOREIGN KEY (`sourceKeyTypeId`) REFERENCES `keytypes` (`keyTypeId`),
 CONSTRAINT `trades_ibfk_4` FOREIGN KEY (`sourceModId`) REFERENCES `mods` (`modId`),
 CONSTRAINT `trades_ibfk_5` FOREIGN KEY (`destinationKeyTierId`) REFERENCES `keytiers` (`keyTierId`),
 CONSTRAINT `trades_ibfk_6` FOREIGN KEY (`destinationKeyTypeId`) REFERENCES `keytypes` (`keyTypeId`),
 CONSTRAINT `trades_ibfk_7` FOREIGN KEY (`destinationModId`) REFERENCES `mods` (`modId`)
) ENGINE=InnoDB AUTO_INCREMENT=732544 DEFAULT CHARSET=latin1

现在，当获取结果集时，我想计算结果的数量，以决定是否显示未找到结果的消息.

Now when obtaining a result set I want to count the amount of results to decide whether to display a message that no results were found, or not.

SELECT SUM(count) AS sum 
FROM
(
  (SELECT COUNT(1) AS count
   FROM trades t
   WHERE t.sourceItem = 1 AND t.destinationItem = 1)
UNION ALL
  (SELECT COUNT(1) AS count
   FROM trades t
   WHERE t.sourceItem = 1 AND t.destinationItem = 2)
UNION ALL
  (SELECT COUNT(1) AS count
   FROM trades t
   WHERE t.sourceItem = 1 AND t.destinationItem = 3)
UNION ALL
  (SELECT COUNT(1) AS count
   FROM trades t
   WHERE t.sourceItem = 2 AND t.destinationItem = 1)
UNION ALL
  (SELECT COUNT(1) AS count
   FROM trades t
   WHERE t.sourceItem = 2 AND t.destinationItem = 2)
UNION ALL
  (SELECT COUNT(1) AS count
   FROM trades t
   WHERE t.sourceItem = 2 AND t.destinationItem = 3)
) AS derived

该查询正在运行，但需要花费2.63秒，这很长.

The query is working but it takes 2.63 seconds, which is way to long.

一个人将如何优化它?我以为我几乎做了我能做的一切，除了一件事情:

How would one optimize this? I thought I had almost done everything I could do, except for one thing:

• 由于sourceItem的可能值为(1, 2)，而destinationItem的可能值为(1, 2, 3)，我可以制作另一个表，并通过TRIGGER ON INSERT写入包含值的表. /li>

• As the possible values of sourceItem will be (1, 2) and the possible values of destinationItem will be (1, 2, 3), I could make another table and write via a TRIGGER ON INSERT to that table containing the values.

同样重要的是，查询是由依赖于POST变量的PHP脚本创建的，这意味着UNION ALL中的每个SELECT可能存在，也可能不存在.不幸的是，这个问题并不像返回整个表的最大值那样简单.

Also not unimportantly, the query is created by a PHP script dependant on POST-variables, which means that every SELECT in the UNION ALL may or may not be there. Unfortunately this question is not as easy as returning the maximum of the full table.

也欢迎其他所有建议.

更新:显然，查询的实际构造方式有些混乱，如下所示:

UPDATE: Appereantly there has been some confusion on how the query is actually constructed, see it as this:

• 您有2个对应于1和2
的sourceItem复选框
• 您有3个destinationItem复选框，分别与1，2和3

• You have 2 checkboxes for sourceItem corresponding to 1 and 2
• You have 3 checkboxes for destinationItem corresponding to 1, 2 and 3

用户可以按自己想要的任何方式对其进行检查.

The user can check them in any way he wants.

更新2:看来，即使使用索引，我的原始查询也不会削减它，有人会这么想考虑一种完全不同的设置，从而基本上消除了对COUNT的需要或SUM或类似的东西?

UPDATE 2: It seems like my original query will not cut it, even with using indexes, could someone be so kind to think about an entirely different setup that basically eliminates the need for COUNT or SUM or anything of that sort?

更新3:我在我的问题中忘记了一个非常重要的部分，如下:

UPDATE 3: I have forgot a very important part in my question, which is the following:

• 具有sourceItem = 1 可能与(sourceKeyTierId = ? AND sourceKeyTypeId =?)相关联
• 具有sourceItem = 2 可能与(sourceModId = ?)相关联
• 具有destinationItem = 2 可能与(destinationKeyTierId = ? AND destinationKeyTypeId =?)相关联
• 具有destinationItem = 3 可能与(destinationModId = ?)相关联

• Having sourceItem = 1 may have (sourceKeyTierId = ? AND sourceKeyTypeId = ?) associated with it
• Having sourceItem = 2 may have (sourceModId = ?) associated with it
• Having destinationItem = 2 may have (destinationKeyTierId = ? AND destinationKeyTypeId = ?) associated with it
• Having destinationItem = 3 may have (destinationModId = ?) associated with it

您仍然可以在带有复选框的示例中看到它，但是选中某些复选框也可以选择输入另一个数字(在实际情况下，这是一个选择下拉列表)，无需选择.

You can still see it in terms of the example with checkboxes, however checking certain checkboxes also leads to the option to input another number (in real scenario it is a select dropdown), which need not to be chosen.

我的第一个未优化查询的更新示例将是也可能发生以下查询 ，但这是最大示例:

An updated example of my first unoptimized query would be that the following query could also occur, but this is the maximal example:

SELECT SUM(count) AS sum 
FROM
(
  (SELECT COUNT(1) AS count
   FROM trades t
   WHERE t.sourceItem = 1 AND t.destinationItem = 1 AND t.sourceKeyTierId = ? AND t.sourceKeyTypeId = ?)
UNION ALL
  (SELECT COUNT(1) AS count
   FROM trades t
   WHERE t.sourceItem = 1 AND t.destinationItem = 2 AND t.sourceKeyTierId = ? AND t.sourceKeyTypeId = ? AND t.destinationKeyTierId = ? AND t.destinationKeyTypeId = ?)
UNION ALL
  (SELECT COUNT(1) AS count
   FROM trades t
   WHERE t.sourceItem = 1 AND t.destinationItem = 3 AND t.sourceKeyTierId = ? AND t.sourceKeyTypeId = ? AND t.destinationModId = ?)
UNION ALL
  (SELECT COUNT(1) AS count
   FROM trades t
   WHERE t.sourceItem = 2 AND t.destinationItem = 1 AND t.sourceModId = ?)
UNION ALL
  (SELECT COUNT(1) AS count
   FROM trades t
   WHERE t.sourceItem = 2 AND t.destinationItem = 2 AND t.sourceModId = ? AND t.destinationKeyTierId = ? AND t.destinationKeyTypeId = ?)
UNION ALL
  (SELECT COUNT(1) AS count
   FROM trades t
   WHERE t.sourceItem = 2 AND t.destinationItem = 3 AND t.sourceModId = ? AND t.destinationModId = ?)
) AS derived

推荐答案

将条件放入sum().像这样，您将获得单一计数

Put your conditions in a sum(). Like this you get single counts

SELECT SUM(sourceItem = 1 AND destinationItem = 1) AS count1,
       SUM(sourceItem = 1 AND destinationItem = 2) AS count2
FROM trades

要获取完整的条件计数，请执行

To get the complete conditional count do

SELECT SUM(case when sourceItem > 0 and destinationItem > 0 then 2
                when sourceItem > 0 or destinationItem > 0 then 1
                else 0 
           end) AS complete_sum
FROM trades

或

SELECT SUM(sourceItem > 0) + sum(destinationItem > 0) AS complete_sum
FROM trades

这篇关于MySQL与InnoDB:如何避免使用COUNT?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！