js06金沙官网登录-js06.com-欢迎您

来自 计算机 2020-04-07 02:53 的文章
当前位置: js06金沙官网登录-js06.com-欢迎您 > 计算机 > 正文

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

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: rangepossible_keys: NULL key: idx_gid_uid_gc key_len: 4 ref: NULL rows: 10 Extra: Using index for group-by1 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: refpossible_keys: idx_gid_uid_gc key: idx_gid_uid_gc key_len: 4 ref: const rows: 4 Extra: Using WHERE; Using index1 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: rangepossible_keys: idx_gid_uid_gc key: idx_gid_uid_gc key_len: 4 ref: NULL rows: 32 Extra: Using WHERE; Using index; Using temporary1 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: rangepossible_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 filesort1 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 会好一小点日常情状,数据量一点都不小的表,关联字段都会加索引的,,并且加索引后寻找时间独有早前的百分之十六左右。

本文由js06金沙官网登录-js06.com-欢迎您发布于计算机,转载请注明出处:MySQL中distinct语句的基本原理及其与group by的可比

关键词: