更快，SELECT DISTINCT或GROUP BY在MySQL中？ [英] What's faster, SELECT DISTINCT or GROUP BY in MySQL?

问题描述

如果我有一个表

  CREATE TABLE users（
 id int（10）unsigned NOT NULL auto_increment， 
 name varchar（255）NOT NULL，
 profession varchar（255）NOT NULL，
 employee varchar（255）NOT NULL，
 PRIMARY KEY（id）
） 
  

，我想获取 profession 字段，会更快（或推荐）：

  SELECT DISTINCT u.profession FROM users u 
  

或

  SELECT u.profession FROM用户u GROUP BY u.profession 
  

？

< （实际上这是一些数据库如何实现 DISTINCT 下的） 。

如果其中一个更快，它将是 DISTINCT 。这是因为，虽然两者是相同的，一个查询优化器必须抓住这样的事实，你的 GROUP BY 没有利用任何组成员，只是他们的键。 DISTINCT 使得这个显式，所以你可以逃避一个轻微的dumber优化器。

If I have a table

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)
)

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

SELECT DISTINCT u.profession FROM users u

or

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

?

解决方案

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

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.

When in doubt, test!

这篇关于更快，SELECT DISTINCT或GROUP BY在MySQL中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！