Distinct multiple fields mysql_MySQL index optimization distinct statement and distinct multi-field operations_Kuiyan Blog

MySQL usually uses GROUP BY (essentially a sorting action) to complete the DISTINCT operation. If the DISTINCT operation is combined with the ORDERBY operation, a temporary table is usually used. This will affect performance. In some cases, MySQL can use index optimization DISTINCT operation, but it needs to be learned and used flexibly. This article involves an instance where the DISTINCT operation cannot be completed using the index.

Example 1 Use index to optimize DISTINCT operation

create table m11 (a int, b int, c int, d int, primary key(a)) engine=INNODB;

insert into m11 values ​​(1,1,1,1),(2,2,2,2),(3,3,3,3),(4,4,4,4),(5, 5,5,5),(6,6,6,6),(7,7,7,7),(8,8,8,8);

explain select distinct(a) from m11;

mysql> explain select distinct(a) from m11;

+—-+————-+——-+————+—— -+—————+———+———+——+—– -+———-+————-+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+—-+————-+——-+————+——- +—————+———+———+——+—— +———-+————-+| 1 |