Should I use distinct or group by to remove duplicates in MySQL?

Preface

About the performance comparison between group by and distinct: the conclusion on the Internet is as follows: distinct has better performance with a small amount of data without index, and group by has better performance with a large amount of data. Group by with index has better performance. When going through the index, the fewer the grouping types, the faster distinct is. Verify the conclusions drawn online.

Disable query cache during the prepare phase

Check whether query cache is set in MySQL. In order not to affect the test results, you need to turn off the query cache.

show variables like '%query_cache%'; 

Check whether query cache is enabled or not, which is determined by query_cache_type and query_cache_size .

• Method 1: To turn off query cache, you need to find my.ini and modify query_cache_type You need to modify the C:\ProgramData\MySQL\MySQL Server 5.7\my.ini configuration file and modify query_cache_type=0或2 .
• Method 2: Set query_cache_size to 0 and execute the following statement.

set global query_cache_size = 0;

Method 3: If you don’t want to turn off the query cache, you can also use RESET QUERY CACHE .

In the current test environment, query_cache_type=2 means query caching on demand. The default query mode is not to cache. If caching is required, you need to add sql_cache to the query statement.

Data preparation

Table t0 stores 100,000少量種類少

drop table if exists t0;
create table t0(
id bigint primary key auto_increment,
a varchar(255) not null
) engine=InnoDB default charset=utf8mb4 collate=utf8mb4_bin;
1
2
3
4
5
drop procedure insert_t0_simple_category_data_sp;
delimiter //
create procedure insert_t0_simple_category_data_sp(IN num int)
begin
set @i = 0;
while @i < num do
	insert into t0(a) value(truncate(@i/1000, 0));
 set @i = @i + 1;
end while;
end
//
call insert_t0_simple_category_data_sp(100000);

Table t1 stores 10,000少量種類多

drop table if exists t1;
create table t1 like t0;
1
2
drop procedure insert_t1_complex_category_data_sp;
delimiter //
create procedure insert_t1_complex_category_data_sp(IN num int)
begin
set @i = 0;
while @i < num do
	insert into t1(a) value(truncate(@i/10, 0));
 set @i = @i + 1;
end while;
end
//
call insert_t1_complex_category_data_sp(10000);

The t2 table stores 5 million大量種類多

drop table if exists t2;
create table t2 like t1;
1
2
drop procedure insert_t2_complex_category_data_sp;
delimiter //
create procedure insert_t2_complex_category_data_sp(IN num int)
begin
set @i = 0;
while @i < num do
	insert into t1(a) value(truncate(@i/10, 0));
 set @i = @i + 1;
end while;
end
//
call insert_t2_complex_category_data_sp(5000000);

Testing Phase

Verify a small amount of data

Not indexed

set profiling = 1;
select distinct a from t0;
show profiles;
select a from t0 group by a;
show profiles;
alter table t0 add index `a_t0_index`(a); 

This shows that when there is a small number of types and little data, without indexing, the performance of distinct and group by is almost the same.

Add index

alter table t0 add index `a_t0_index`(a);

After executing a query similar to the above

This shows that with a small number of types and little data, the performance of distinct and group by are almost the same when adding indexes.

Verify that a small amount of data with many types is not indexed

After executing a similar unindexed query as above

It can be seen from this that when there is a small amount of data with many types and no index, the performance of distinct is slightly higher than that of group by, but the difference is not large.

Add index

alter table t1 add index `a_t1_index`(a);

After executing a similar unindexed query

It can be seen from this that with a small amount of data and a lot of types, the performance of distinct and group by are almost the same when adding indexes.

Verify large amounts of data

Not indexed

SELECT count(1) FROM t2; 

After executing a similar unindexed query as above

This shows that when there is a large amount of data of many types and without indexing, DISTINCT performs better than GROUP BY.

Add index

alter table t2 add index `a_t2_index`(a);

After executing the above similar index query

This shows that with a large amount of data of many types, the performance of distinct and group by are almost the same when adding indexes.

Summarize