mysql distingct_MySQL优化之distinct

distinct实际上和group by的操作非常相似，只不过是在group by之后的每组中取出一条记录而已。

但是distinct分组的时候是不使用排序来做分组的。

同样distinct也分为松散索引扫描和紧凑索引扫描

松散索引

测试过程中我发现如果distinct中的字段本身是唯一的或者没有数据则Extra中不是Using index for group-by，而是显示using Index，说明MySQL在这块还是比较智能的。

如果插入了数据需要更新表信息，analyze table group_message

mysql> explain select distinct gid from group_message \G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: group_message

type: range

possible_keys: IDX_GID_UID_GMT

key: IDX_GID_UID_GMT

key_len: 5

ref: NULL

rows: 3

Extra: Using index for group-by

1 row in set (0.03 sec)

mysql> explain select distinct id from group_message \G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: group_message

type: index

possible_keys: PRIMARY,IDX_GID_UID_GMT

key: IDX_GID_UID_GMT

key_len: 16

ref: NULL

rows: 5

Extra: Using index

1 row in set (0.03 sec)

紧凑索引扫描：

mysql> explain select distinct uid from group_message where gid = 1\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: group_message

type: ref

possible_keys: IDX_GID_UID_GMT

key: IDX_GID_UID_GMT

key_len: 5

ref: const

rows: 5

Extra: Using where; Using index

1 row in set (0.32 sec)

distinct不能使用索引的情况：我们发现只使用了临时表并没有使用到排序。

mysql> explain select distinct uid from group_message \G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: group_message

type: index

possible_keys: IDX_GID_UID_GMT

key: IDX_GID_UID_GMT

key_len: 16

ref: NULL

rows: 5

Extra: Using index; Using temporary

1 row in set (0.02 sec)

表结构：

mysql> show create table group_message \G

*************************** 1. row ***************************

Table: group_message

Create Table: CREATE TABLE `group_message` (

`id` int(11) NOT NULL,

`message` varchar(1000) DEFAULT NULL,

`gid` int(11) DEFAULT NULL,

`uid` int(11) DEFAULT NULL,

`gmt_ctreate` datetime DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `IDX_GID_UID_GMT` (`gid`,`uid`,`gmt_ctreate`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.03 sec)