mysql distinct跟group by性能

时间：2023-11-22 12:02:20
mysql distinct和group by性能  
1，测试前的准备
  1. //准备一张测试表
  2. mysql> CREATE TABLE `test_test` (
  3. ->   `id` int(11) NOT NULL auto_increment,
  4. ->   `num` int(11) NOT NULL default '0',
  5. ->   PRIMARY KEY  (`id`)
  6. -> ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
  7. Query OK, 0 rows affected (0.05 sec)
  8. mysql> delimiter ||  //改变mysql命令结束符为||
  9. //建个储存过程向表中插入10W条数据
  10. mysql> create procedure p_test(pa int(11))
  11. -> begin
  12. ->
  13. ->  declare max_num int(11) default 100000;
  14. ->  declare i int default 0;
  15. ->  declare rand_num int;
  16. ->
  17. ->  select count(id) into max_num from test_test;
  18. ->
  19. ->  while i < pa do
  20. ->          if max_num < 100000 then
  21. ->                  select cast(rand()*100 as unsigned) into rand_num;
  22. ->                  insert into test_test(num)values(rand_num);
  23. ->          end if;
  24. ->          set i = i +1;
  25. ->  end while;
  26. -> end||
  27. Query OK, 0 rows affected (0.00 sec)
  28. mysql> call p_test(100000)||
  29. Query OK, 1 row affected (5.66 sec)
  30. mysql> delimiter ;//改变mysql命令结束符为;
  31. mysql> select count(id) from test_test;  //数据都进去了
  32. +-----------+
  33. | count(id) |
  34. +-----------+
  35. |    100000 |
  36. +-----------+
  37. 1 row in set (0.00 sec)
  38. mysql> show variables like "%pro%";   //查看一下，记录执行的profiling是不是开启动了，默认是不开启的
  39. +---------------------------+-------+
  40. | Variable_name             | Value |
  41. +---------------------------+-------+
  42. | profiling                 | OFF   |
  43. | profiling_history_size    | 15    |
  44. | protocol_version          | 10    |
  45. | slave_compressed_protocol | OFF   |
  46. +---------------------------+-------+
  47. 4 rows in set (0.00 sec)
  48. mysql> set profiling=1;           //开启
  49. 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，测试

  1. //做了4组测试
  2. mysql> select distinct(num) from test_test;
  3. mysql> select num from test_test group by num;
  4. mysql> show profiles;    //查看结果
  5. +----------+------------+-------------------------------------------+
  6. | Query_ID | Duration   | Query                                     |
  7. +----------+------------+-------------------------------------------+
  8. |        1 | 0.07298225 | select distinct(num) from test_test       |
  9. |        2 | 0.07319975 | select num from test_test group by num    |
  10. |        3 | 0.07313525 | select num from test_test group by num    |
  11. |        4 | 0.07317725 | select distinct(num) from test_test       |
  12. |        5 | 0.07275200 | select distinct(num) from test_test       |
  13. |        6 | 0.07298600 | select num from test_test group by num    |
  14. |        7 | 0.07500700 | select num from test_test group by num    |
  15. |        8 | 0.07331325 | select distinct(num) from test_test       |
  16. |        9 | 0.57831575 | create index num_index on test_test (num) |  //在这儿的时候，我加了索引
  17. |       10 | 0.00243550 | select distinct(num) from test_test       |
  18. |       11 | 0.00121975 | select num from test_test group by num    |
  19. |       12 | 0.00116550 | select distinct(num) from test_test       |
  20. |       13 | 0.00107650 | select num from test_test group by num    |
  21. +----------+------------+-------------------------------------------+
  22. 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://www.myexception.cn/mysql/516305.html

相关文章