MySQL中distinct语句的基本原理及其与group by的比较_Mysql

DISTINCT 实际上和 GROUP BY 操作的实现非常相似，只不过是在 GROUP BY 之后的每组中只取出一条记录而已。所以，DISTINCT 的实现和 GROUP BY 的实现也基本差不多，没有太大的区别。同样可以通过松散索引扫描或者是紧凑索引扫描来实现，当然，在无法仅仅使用索引即能完成 DISTINCT 的时候，MySQL 只能通过临时表来完成。但是，和 GROUP BY 有一点差别的是，DISTINCT 并不需要进行排序。也就是说，在仅仅只是 DISTINCT 操作的 Query 如果无法仅仅利用索引完成操作的时候，MySQL 会利用临时表来做一次数据的“缓存”，但是不会对临时表中的数据进行 filesort 操作。当然，如果我们在进行 DISTINCT 的时候还使用了 GROUP BY 并进行了分组，并使用了类似于 MAX 之类的聚合函数操作，就无法避免 filesort 了。

下面我们就通过几个简单的 Query 示例来展示一下 DISTINCT 的实现。

1.首先看看通过松散索引扫描完成 DISTINCT 的操作：

sky@localhost : example 11:03:41> EXPLAIN SELECT DISTINCT group_id
  -> FROM group_messageG

*************************** 1. row ***************************
      id: 1
 SELECT_type: SIMPLE
    table: group_message
     type: range
possible_keys: NULL
     key: idx_gid_uid_gc
   key_len: 4
     ref: NULL
     rows: 10
    Extra: Using index for group-by
1 row in set (0.00 sec)

我们可以很清晰的看到，执行计划中的 Extra 信息为“Using index for group-by”，这代表什么意思？为什么我没有进行 GROUP BY 操作的时候，执行计划中会告诉我这里通过索引进行了 GROUP BY 呢？其实这就是于 DISTINCT 的实现原理相关的，在实现 DISTINCT的过程中，同样也是需要分组的，然后再从每组数据中取出一条返回给客户端。而这里的 Extra 信息就告诉我们，MySQL 利用松散索引扫描就完成了整个操作。当然，如果 MySQL Query Optimizer 要是能够做的再人性化一点将这里的信息换成“Using index for distinct”那就更好更容易让人理解了，呵呵。

2.我们再来看看通过紧凑索引扫描的示例：

sky@localhost : example 11:03:53> EXPLAIN SELECT DISTINCT user_id
  -> FROM group_message
  -> WHERE group_id = 2G

*************************** 1. row ***************************
      id: 1
 SELECT_type: SIMPLE
    table: group_message
     type: ref
possible_keys: idx_gid_uid_gc
     key: idx_gid_uid_gc
   key_len: 4
     ref: const
     rows: 4
    Extra: Using WHERE; Using index
1 row in set (0.00 sec)

这里的显示和通过紧凑索引扫描实现 GROUP BY 也完全一样。实际上，这个 Query 的实现过程中，MySQL 会让存储引擎扫描 group_id = 2 的所有索引键，得出所有的 user_id，然后利用索引的已排序特性，每更换一个 user_id 的索引键值的时候保留一条信息，即可在扫描完所有 gruop_id = 2 的索引键的时候完成整个 DISTINCT 操作。

3.下面我们在看看无法单独使用索引即可完成 DISTINCT 的时候会是怎样：

sky@localhost : example 11:04:40> EXPLAIN SELECT DISTINCT user_id
  -> FROM group_message
  -> WHERE group_id > 1 AND group_id < 10G

*************************** 1. row ***************************
      id: 1
 SELECT_type: SIMPLE
    table: group_message
     type: range
possible_keys: idx_gid_uid_gc
     key: idx_gid_uid_gc
   key_len: 4
     ref: NULL
     rows: 32
    Extra: Using WHERE; Using index; Using temporary
1 row in set (0.00 sec)

当 MySQL 无法仅仅依赖索引即可完成 DISTINCT 操作的时候，就不得不使用临时表来进行相应的操作了。但是我们可以看到，在 MySQL 利用临时表来完成 DISTINCT 的时候，和处理 GROUP BY 有一点区别，就是少了 filesort。实际上，在 MySQL 的分组算法中，并不一定非要排序才能完成分组操作的，这一点在上面的 GROUP BY 优化小技巧中我已经提到过了。实际上这里 MySQL 正是在没有排序的情况下实现分组最后完成 DISTINCT 操作的，所以少了 filesort 这个排序操作。

4.最后再和 GROUP BY 结合试试看：

sky@localhost : example 11:05:06> EXPLAIN SELECT DISTINCT max(user_id)
  -> FROM group_message
  -> WHERE group_id > 1 AND group_id < 10
  -> GROUP BY group_idG

*************************** 1. row ***************************
      id: 1
 SELECT_type: SIMPLE
    table: group_message
     type: range
possible_keys: idx_gid_uid_gc
     key: idx_gid_uid_gc
   key_len: 4
     ref: NULL
     rows: 32
    Extra: Using WHERE; Using index; Using temporary; Using filesort
1 row in set (0.00 sec)

最后我们再看一下这个和 GROUP BY 一起使用带有聚合函数的示例，和上面第三个示例相比，可以看到已经多了 filesort 排序操作了，正是因为我们使用了 MAX 函数的缘故。要取得分组后的 MAX 值，又无法使用索引完成操作，只能通过排序才行了。

mysql distinct和group by谁更好
1，测试前的准备

//准备一张测试表
mysql> CREATE TABLE `test_test` (
 ->  `id` int(11) NOT NULL auto_increment,
 ->  `num` int(11) NOT NULL default '0',
 ->  PRIMARY KEY (`id`)
 -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

  

Query OK, 0 rows affected (0.05 sec)

 

mysql> delimiter || //改变mysql命令结束符为|| 

//建个储存过程向表中插入10W条数据
mysql> create procedure p_test(pa int(11))
 -> begin
 ->
 -> declare max_num int(11) default 100000;
 -> declare i int default 0;
 -> declare rand_num int;
 ->
 -> select count(id) into max_num from test_test;
 ->
 -> while i < pa do
 ->     if max_num < 100000 then
 ->         select cast(rand()*100 as unsigned) into rand_num;
 ->         insert into test_test(num)values(rand_num);
 ->     end if;
 ->     set i = i +1;
 -> end while;
 -> end||

Query OK, 0 rows affected (0.00 sec)

 

mysql> call p_test(100000)||

Query OK, 1 row affected (5.66 sec)

 

mysql> delimiter ;//改变mysql命令结束符为;
mysql> select count(id) from test_test; //数据都进去了

+-----------+
| count(id) |
+-----------+
|  100000 |
+-----------+
1 row in set (0.00 sec)

 

mysql> show variables like "%pro%";  //查看一下，记录执行的profiling是不是开启动了，默认是不开启的

+---------------------------+-------+
| Variable_name       | Value |
+---------------------------+-------+
| profiling         | OFF  |
| profiling_history_size  | 15  |
| protocol_version     | 10  |
| slave_compressed_protocol | OFF  |
+---------------------------+-------+
4 rows in set (0.00 sec)

 

mysql> set profiling=1;      //开启

Query OK, 0 rows affected (0.00 sec)

2，测试

//做了4组测试
mysql> select distinct(num) from test_test;
mysql> select num from test_test group by num; 

mysql> show profiles;  //查看结果

+----------+------------+-------------------------------------------+
| Query_ID | Duration  | Query                   |
+----------+------------+-------------------------------------------+
|    1 | 0.07298225 | select distinct(num) from test_test    |
|    2 | 0.07319975 | select num from test_test group by num  |
|    3 | 0.07313525 | select num from test_test group by num  |
|    4 | 0.07317725 | select distinct(num) from test_test    |
|    5 | 0.07275200 | select distinct(num) from test_test    |
|    6 | 0.07298600 | select num from test_test group by num  |
|    7 | 0.07500700 | select num from test_test group by num  |
|    8 | 0.07331325 | select distinct(num) from test_test    |
|    9 | 0.57831575 | create index num_index on test_test (num) | //在这儿的时候，我加了索引
|    10 | 0.00243550 | select distinct(num) from test_test    |
|    11 | 0.00121975 | select num from test_test group by num  |
|    12 | 0.00116550 | select distinct(num) from test_test    |
|    13 | 0.00107650 | select num from test_test group by num  |
+----------+------------+-------------------------------------------+
13 rows in set (0.00 sec)

上面的1-8是4组数据，并且是没有加索引的，从中我们可以看出，distinct比group by 会好一点点
10-13是2组数据，是加了索引以后的，从中我们可以看出，group by 比distinct 会好一点点
一般情况，数据量比较大的表，关联字段都会加索引的，，并且加索引后检索时间只有以前的六分之一左右。

以上是小编为您精心准备的的内容，在的博客、问答、公众号、人物、课程等栏目也有的相关内容，欢迎继续使用右上角搜索按钮进行搜索mysql
， 性能
distinct
mysql distinct group、mysql distinct 原理、mysql语句group by、mysql group by 原理、distinct group by，以便于您获取更多的相关知识。

时间： 2024-10-23 20:39:10

MySQL中distinct语句的基本原理及其与group by的比较_Mysql的相关文章

MySQL中select语句介绍及使用示例

数据表都已经创建起来了,我们就可以用自己喜欢的方式对数据表里面的信息进行检索和显示了,下面为大家讲解下MySQL中select语句的应用,感兴趣的碰可以学习下   数据表都已经创建起来了,假设我们已经插入了许多的数据,我们就可以用自己喜欢的方式对数据表里面的信息进行检索和显示了,比如说:可以象下面这样把整个数据表内的内容都显示出来 select * from president; 也可以只选取某一个数据行里的某一个数据列 select birth from president where las

MySQL中select语句使用order按行排序_Mysql

本文介绍MySQL数据库中执行select查询语句,并对查询的结果使用order by 子句进行排序. 再来回顾一下SQL语句中的select语句的语法: Select 语句的基本语法: Select <列的集合> from <表名> where <条件> order by <排序字段和方式> 如果要对查询结果按某个字段排序,则要使用order by 子句,如下: select * from <表名> order by <字段名称>

MySQL中UPDATE语句使用的实例教程_Mysql

一.UPDATE常见用法首先建立测试环境:   DROP TABLE IF EXISTS t_test; CREATE TABLE t_test ( bs bigint(20) NOT NULL auto_increment, username varchar(20) NOT NULL, password varchar(20) default NULL, remark varchar(200) default NULL, PRIMARY KEY (bs) ) ENGINE=InnoDB AUT

MySQL中distinct与group by之间的性能进行比较_Mysql

最近在网上看到了一些测试,感觉不是很准确,今天亲自测试了一番.得出了结论,测试过程在个人计算机上,可能不够全面,仅供参考. 测试过程: 准备一张测试表  CREATE TABLE `test_test` ( `id` int(11) NOT NULL auto_increment, `num` int(11) NOT NULL default '0', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1

MySQL中insert语句的使用与优化教程_Mysql

MySQL 表中使用 INSERT INTO SQL语句来插入数据. 你可以通过 mysql> 命令提示窗口中向数据表中插入数据,或者通过PHP脚本来插入数据. 语法 以下为向MySQL数据表插入数据通用的 INSERT INTO SQL语法: INSERT INTO table_name ( field1, field2,...fieldN ) VALUES ( value1, value2,...valueN ); 如果数据是字符型,必须使用单引号或者双引号,如:"value"

MySQL中Distinct和Group By语句的基本使用教程_Mysql

MySQL Distinct 去掉查询结果重复记录DISTINCT 使用 DISTINCT 关键字可以去掉查询中某个字段的重复记录. 语法: SELECT DISTINCT(column) FROM tb_name 例子: 假定 user 表有如下记录: uid username 1 小李 2 小张 3 小李 4 小王 5 小李 6 小张 SQL 语句: SELECT DISTINCT(username) FROM user 返回查询结果如下: username 小李 小张 小王 提示 使用 D

MySQL中distinct和count(*)的使用方法比较_Mysql

首先对于MySQL的DISTINCT的关键字的一些用法: 1．在count 不重复的记录的时候能用到,比如SELECT COUNT( DISTINCT id ) FROM tablename:就是计算talbebname表中id不同的记录有多少条. 2,在需要返回记录不同的id的具体值的时候可以用,比如SELECT DISTINCT id FROM tablename:返回talbebname表中不同的id的具体的值. 3．上面的情况2对于需要返回mysql表中2列以上的结果时会有歧义,比如SE

10个mysql中select语句的简单用法_Mysql

1.select语句可以用回车分隔 $sql="select * from article where id=1" 和 $sql="select * from article where id=1",都可以得到正确的结果,但有时分开写或许能更明了一点,特别是当sql语句比较长时 2.批量查询数据 可以用in来实现 $sql="select * from article where id in(1,3,5)" 3.使用concat连接查询的结果 $

mysql中distinct去除重复值方法

,而无法返回其它字段,这个问题让我困扰了很久,用distinct不能解决的话,我只有用二重循环查询来解决,而 这样对于一个数据量非常大的站来说,无疑是会直接影响到效率的.所以我花了很多时间来研究这个问题 mysql的DISTINCT的关键字有很多你想不到的用处 1.在count 不重复的记录的时候能用到 比如SELECT COUNT( DISTINCT id ) FROM tablename; 就是计算talbebname表中id不同的记录有多少条 2,在需要返回记录不同的id的具体值的时候可以