MySQL中distinct语句的基本原理及其与group by的比较
DISTINCT实际上和GROUPBY操作的实现非常相似，只不过是在GROUPBY之后的每组中只取出一条记录而已。所以，DISTINCT的实现和GROUPBY的实现也基本差不多，没有太大的区别。同样可以通过松散索引扫描或者是紧凑索引扫描来实现，当然，在无法仅仅使用索引即能完成DISTINCT的时候，MySQL只能通过临时表来完成。但是，和GROUPBY有一点差别的是，DISTINCT并不需要进行排序。也就是说，在仅仅只是DISTINCT操作的Query如果无法仅仅利用索引完成操作的时候，MySQL会利用临时表来做一次数据的“缓存”，但是不会对临时表中的数据进行filesort操作。当然，如果我们在进行DISTINCT的时候还使用了GROUPBY并进行了分组，并使用了类似于MAX之类的聚合函数操作，就无法避免filesort了。
下面我们就通过几个简单的Query示例来展示一下DISTINCT的实现。
1.首先看看通过松散索引扫描完成DISTINCT的操作：
sky@localhost:example11:03:41>EXPLAINSELECTDISTINCTgroup_id ->FROMgroup_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:Usingindexforgroup-by 1rowinset(0.00sec)
我们可以很清晰的看到，执行计划中的Extra信息为“Usingindexforgroup-by”，这代表什么意思？为什么我没有进行GROUPBY操作的时候，执行计划中会告诉我这里通过索引进行了GROUPBY呢？其实这就是于DISTINCT的实现原理相关的，在实现DISTINCT的过程中，同样也是需要分组的，然后再从每组数据中取出一条返回给客户端。而这里的Extra信息就告诉我们，MySQL利用松散索引扫描就完成了整个操作。当然，如果MySQLQueryOptimizer要是能够做的再人性化一点将这里的信息换成“Usingindexfordistinct”那就更好更容易让人理解了，呵呵。
2.我们再来看看通过紧凑索引扫描的示例：
sky@localhost:example11:03:53>EXPLAINSELECTDISTINCTuser_id ->FROMgroup_message ->WHEREgroup_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:UsingWHERE;Usingindex 1rowinset(0.00sec)
这里的显示和通过紧凑索引扫描实现GROUPBY也完全一样。实际上，这个Query的实现过程中，MySQL会让存储引擎扫描group_id=2的所有索引键，得出所有的user_id，然后利用索引的已排序特性，每更换一个user_id的索引键值的时候保留一条信息，即可在扫描完所有gruop_id=2的索引键的时候完成整个DISTINCT操作。
3.下面我们在看看无法单独使用索引即可完成DISTINCT的时候会是怎样：
sky@localhost:example11:04:40>EXPLAINSELECTDISTINCTuser_id ->FROMgroup_message ->WHEREgroup_id>1ANDgroup_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:UsingWHERE;Usingindex;Usingtemporary 1rowinset(0.00sec)
当MySQL无法仅仅依赖索引即可完成DISTINCT操作的时候，就不得不使用临时表来进行相应的操作了。但是我们可以看到，在MySQL利用临时表来完成DISTINCT的时候，和处理GROUPBY有一点区别，就是少了filesort。实际上，在MySQL的分组算法中，并不一定非要排序才能完成分组操作的，这一点在上面的GROUPBY优化小技巧中我已经提到过了。实际上这里MySQL正是在没有排序的情况下实现分组最后完成DISTINCT操作的，所以少了filesort这个排序操作。
4.最后再和GROUPBY结合试试看：
sky@localhost:example11:05:06>EXPLAINSELECTDISTINCTmax(user_id) ->FROMgroup_message ->WHEREgroup_id>1ANDgroup_id<10 ->GROUPBYgroup_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:UsingWHERE;Usingindex;Usingtemporary;Usingfilesort 1rowinset(0.00sec)
最后我们再看一下这个和GROUPBY一起使用带有聚合函数的示例，和上面第三个示例相比，可以看到已经多了filesort排序操作了，正是因为我们使用了MAX函数的缘故。要取得分组后的MAX值，又无法使用索引完成操作，只能通过排序才行了。
mysqldistinct和groupby谁更好
1，测试前的准备
//准备一张测试表 mysql>CREATETABLE`test_test`( ->`id`int(11)NOTNULLauto_increment, ->`num`int(11)NOTNULLdefault'0', ->PRIMARYKEY(`id`) ->)ENGINE=MyISAMDEFAULTCHARSET=utf8AUTO_INCREMENT=1;
QueryOK,0rowsaffected(0.05sec)
mysql>delimiter||//改变mysql命令结束符为|| //建个储存过程向表中插入10W条数据 mysql>createprocedurep_test(paint(11)) ->begin -> ->declaremax_numint(11)default100000; ->declareiintdefault0; ->declarerand_numint; -> ->selectcount(id)intomax_numfromtest_test; -> ->whilei<pado ->ifmax_num<100000then ->selectcast(rand()*100asunsigned)intorand_num; ->insertintotest_test(num)values(rand_num); ->endif; ->seti=i+1; ->endwhile; ->end||
QueryOK,0rowsaffected(0.00sec)
mysql>callp_test(100000)||
QueryOK,1rowaffected(5.66sec)
mysql>delimiter;//改变mysql命令结束符为; mysql>selectcount(id)fromtest_test;//数据都进去了
+-----------+ |count(id)| +-----------+ |100000| +-----------+ 1rowinset(0.00sec)
mysql>showvariableslike"%pro%";//查看一下，记录执行的profiling是不是开启动了，默认是不开启的
+---------------------------+-------+ |Variable_name|Value| +---------------------------+-------+ |profiling|OFF| |profiling_history_size|15| |protocol_version|10| |slave_compressed_protocol|OFF| +---------------------------+-------+ 4rowsinset(0.00sec)
mysql>setprofiling=1;//开启
QueryOK,0rowsaffected(0.00sec)
2，测试
//做了4组测试 mysql>selectdistinct(num)fromtest_test; mysql>selectnumfromtest_testgroupbynum; mysql>showprofiles;//查看结果
+----------+------------+-------------------------------------------+ |Query_ID|Duration|Query| +----------+------------+-------------------------------------------+ |1|0.07298225|selectdistinct(num)fromtest_test| |2|0.07319975|selectnumfromtest_testgroupbynum| |3|0.07313525|selectnumfromtest_testgroupbynum| |4|0.07317725|selectdistinct(num)fromtest_test| |5|0.07275200|selectdistinct(num)fromtest_test| |6|0.07298600|selectnumfromtest_testgroupbynum| |7|0.07500700|selectnumfromtest_testgroupbynum| |8|0.07331325|selectdistinct(num)fromtest_test| |9|0.57831575|createindexnum_indexontest_test(num)|//在这儿的时候，我加了索引 |10|0.00243550|selectdistinct(num)fromtest_test| |11|0.00121975|selectnumfromtest_testgroupbynum| |12|0.00116550|selectdistinct(num)fromtest_test| |13|0.00107650|selectnumfromtest_testgroupbynum| +----------+------------+-------------------------------------------+ 13rowsinset(0.00sec)
上面的1-8是4组数据，并且是没有加索引的，从中我们可以看出，distinct比groupby会好一点点
10-13是2组数据，是加了索引以后的，从中我们可以看出，groupby比distinct会好一点点
一般情况，数据量比较大的表，关联字段都会加索引的，，并且加索引后检索时间只有以前的六分之一左右。