在 MySQL 中，SELECT DISTINCT 或 GROUP BY 哪个更快?

2021-11-20 00:00:00 sql group-by database distinct mysql

如果我有一张桌子

CREATE TABLE users (
  id int(10) unsigned NOT NULL auto_increment,
  name varchar(255) NOT NULL,
  profession varchar(255) NOT NULL,
  employer varchar(255) NOT NULL,
  PRIMARY KEY  (id)
)

我想获得 profession 字段的所有唯一值，什么会更快(或推荐):

and I want to get all unique values of profession field, what would be faster (or recommended):

SELECT DISTINCT u.profession FROM users u

或

SELECT u.profession FROM users u GROUP BY u.profession

?

推荐答案

它们本质上是等价的(实际上这就是一些数据库在幕后实现 DISTINCT 的方式).

They are essentially equivalent to each other (in fact this is how some databases implement DISTINCT under the hood).

如果其中一个更快，它将是 DISTINCT.这是因为，尽管两者相同，但查询优化器必须捕捉到这样一个事实，即您的 GROUP BY 没有利用任何组成员，而只是利用了他们的键.DISTINCT 明确说明了这一点，因此您可以使用稍微笨一点的优化器.

If one of them is faster, it's going to be DISTINCT. This is because, although the two are the same, a query optimizer would have to catch the fact that your GROUP BY is not taking advantage of any group members, just their keys. DISTINCT makes this explicit, so you can get away with a slightly dumber optimizer.

如有疑问，请测试！

相关文章