MySQL group-by 非常慢
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10278878/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL group-by very slow
提问by Treach
I have the folowwing SQL query
我有以下 SQL 查询
SELECT CustomerID FROM sales WHERE `Date` <= '2012-01-01' GROUP BY CustomerID
The query is executed over 11400000 rows and runs very slow. It takes over 3 minutes to execute. If I remove the group-by part, this runs below 1 second. Why is that?
查询执行超过 11400000 行并且运行速度非常慢。执行需要 3 多分钟。如果我删除 group-by 部分，则运行时间低于 1 秒。这是为什么？
MySQL Server version is '5.0.21-community-nt'
MySQL 服务器版本是“5.0.21-community-nt”
Here is the table schema:
CREATE TABLE `sales` (
`ID` int(11) NOT NULL auto_increment,
`DocNo` int(11) default '0',
`CustomerID` int(11) default '0',
`OperatorID` int(11) default '0',
PRIMARY KEY (`ID`),
KEY `ID` (`ID`),
KEY `DocNo` (`DocNo`),
KEY `CustomerID` (`CustomerID`),
KEY `Date` (`Date`)
) ENGINE=MyISAM AUTO_INCREMENT=14946509 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
回答by rgvcorley
Try putting an index on (Date,CustomerID).
尝试将索引放在 (Date,CustomerID) 上。
Have a look at the mysql manual for optimizing group by queries:- Group by optimization
看看优化分组查询的mysql手册：-分组优化
You can find out how mysql is generating the result if you use EXPLAINas follows:-
如果您使用EXPLAIN以下方法，您可以了解 mysql 如何生成结果：-
EXPLAIN SELECT CustomerID FROM sales WHERE `Date` <= '2012-01-01' GROUP BY CustomerID
This will tell you which indexes (if any) mysql is using to optimize the query. This is very handy when learning which indexes work for which queries as you can try creating an index and see if mysql uses it. So even if you don't fully understand how mysql calculates aggregate queries you can create a useful index by trial and error.
这将告诉您 mysql 使用哪些索引（如果有）来优化查询。这在了解哪些索引适用于哪些查询时非常方便，因为您可以尝试创建索引并查看 mysql 是否使用它。因此，即使您不完全了解 mysql 如何计算聚合查询，您也可以通过反复试验来创建有用的索引。
回答by Daan
Without knowing what your table schema looks like, it's difficult to be certain, but it would probably help if you added a multiple-column index on Dateand CustomerID. That'd save MySQL the hassle of doing a full table scan for the GROUP BYstatement. So try ALTER TABLE sales ADD INDEX (Date,CustomerID).
在不知道您的表架构是什么样的情况下，很难确定，但如果您在Date和上添加多列索引可能会有所帮助CustomerID。这会为 MySQL 省去对GROUP BY语句进行全表扫描的麻烦。所以试试吧ALTER TABLE sales ADD INDEX (Date,CustomerID)。
回答by IT ppl
try this one :
试试这个：
SELECT distinct CustomerID FROM sales WHERE `Date` <= '2012-01-01'
回答by Miguel Angel Ca?edo
I had the same problem, I changed the key fields to the same Collation and that fix the problem. Fields to join the tables had different Collate value.
我遇到了同样的问题，我将关键字段更改为相同的排序规则并解决了问题。连接表的字段具有不同的 Collate 值。
回答by Tom van der Woerdt
Wouldn't this one be a lot faster and achieve the same?
这不会更快并实现相同的目标吗？
SELECT DISTINCT CustomerID FROM sales WHERE `Date` <= '2012-01-01'
Make sure to place an index on Date, of course. I'm not entirely sure but indexing CustomerIDmight also help.
当然，请确保在 上放置索引Date。我不完全确定，但索引CustomerID也可能有所帮助。
