- //准备一张测试表
- 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)
//准备一张测试表 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)
//做了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 会好一点点
一般情况,数据量比较大的表,关联字段都会加索引的,,并且加索引后检索时间只有以前的六分之一左右。
作者:海底苍鹰
地址:http://blog.51yip.com/mysql/1105.html
相关推荐
distinct简单来说就是用来去重的,而group by的设计目的则是用来聚合统计的,两者在能够实现的功能上有些相同之处,但应该仔细区分。 单纯的去重操作使用distinct,速度是快于group by的。 distinct支持单列、多列的...
关于group by 与distinct 性能对比:网上结论如下,不走索引少量数据distinct性能更好,大数据量group by 性能好,走索引group by性能好。走索引时分组种类少distinct快。关于网上的结论做一次验证。 准备阶段屏蔽...
主要针对MySQL中distinct与group by之间的性能进行比较,内容比较详细,很直观的能看出比较结果,感兴趣的小伙伴们可以参考一下
主要介绍了MySQL中distinct与group by的一些比较及用法讲解,二者在查询操作中的用法有很多相似之处,需要的朋友可以参考下
MySQL通常使用GROUPBY(本质上是排序动作)完成DISTINCT操作,如果DISTINCT操作和ORDERBY操作组合使用,通常会用到临时表.这样会影响性能. 在一些情况下,MySQL可以使用索引优化DISTINCT操作,但需要活学活用.本文涉及一个...
主要介绍了MySQL中Distinct和Group By语句的基本使用教程,这里主要是针对查询结果去重的用法,需要的朋友可以参考下
DISTINCT 实际上和 GROUP BY 操作的实现非常相似,只不过是在 GROUP BY 之后的每组中只取出一条记录而已。所以,DISTINCT 的实现和 GROUP BY 的实现也基本差不多,没有太大的区别。同样可以通过松散索引扫描或者是...
DISTINCT 实际上和 GROUP BY 操作的实现非常相似,只不过是在 GROUP BY 之后的每组中只取出一条记录而已。所以,DISTINCT 的实现和 GROUP BY 的实现也基本差不多,没有太大的区别。同样可以通过松散索引扫描或者是...
6.7.4优化GROUPBY和DISTINCT239 6.7.5优化LIMIT分页241 6.7.6优化SQL_CALC_FOUND_ROWS243 6.7.7优化UNION查询243 6.7.8静态查询分析244 6.7.9使用用户自定义变量244 6.8案例学习251 6.8.1使用MySQL构建一个...
一旦开启 only_full_group_by ,感觉,group by 将变成和 distinct 一样,只能获取受到其影响的字段信息,无法和其他未受其影响的字段共存,这样,group by 的功能将变得十分狭窄了 only_full_group_by 模式开启比较...
单表的唯一查询用:distinct多表的唯一查询用:group bydistinct 查询多表时,left join 还有效,全连接无效,在使用mysql时,有时需要查询出某个字段不重复的记录,虽然mysql提供有distinct这个关键字来过滤掉多余...
7.2.13. MySQL如何优化GROUP BY 7.2.14. MySQL如何优化LIMIT 7.2.15. 如何避免表扫描 7.2.16. INSERT语句的速度 7.2.17. UPDATE语句的速度 7.2.18. DELETE语句的速度 7.2.19. 其它优化技巧 7.3. 锁定事宜 7.3.1. ...
8.6 ORDER BY、GROUP BY和DISTINCT的优化 8.7 小结 第9章 MySQL数据库Schema设计的性能优化 9.0 引言 9.1 高效的模型设计 9.2 合适的数据类型 9.3 规范的对象命名 9.4 小结 第10章 MySQL Server性能优化 ...