mysql 特别的 group by

 

Mysql支持不带聚合函数的group by子句造成的相同SQL查询结果集不同的原因分析

测试表TT定义

CREATE TABLE `tt` (

 `id` int(11) NOT NULL AUTO_INCREMENT,

 `cid` smallint(6) DEFAULT NULL,

 `cc` char(1) DEFAULT NULL,

 PRIMARY KEY (`id`),

 KEY `cid` (`cid`),

 KEY `cid_idx` (`cid`)

 ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8

 

数据

TC@SQL>select * from tt;

+----+------+------+

| id | cid  | cc   |

+----+------+------+

|  1 |    1 | f    |

|  2 |    1 | d    |

|  3 |    2 | k    |

|  4 |    2 | b    |

|  5 |    3 | c    |

|  6 |    3 | b    |

|  7 |    1 | b    |

|  8 |    1 | a    |

|  9 |    2 | a    |

| 10 |    2 | l    |

| 11 |    3 | d    |

| 12 |    3 | e    |

+----+------+------+

12 rows in set (0.00 sec)

 

CID是二级索引，他指向主键索引，索引结构图。

                             ………b+tree………….

    /                                |                               \

1  1  1  1          ->                          2  2  2  2                 ->                          3  3  3  3

                                            指向主键索引

1  2  7  8                                                   3  4  9  10                     5  6  11  12

 

后序遍历B+TREE（这个表是innodb，所示b+tree）。首先扫描到值为3的叶子节点，并且由于是后序遍历，只取第一个值，该索引值指向主键索引id为12的，以此类推，下面的语句结果是cc=e,l,a，前序应该是f,k,c。

TC@SQL>select * from tt group by cid order by cid desc;

+----+------+------+

| id | cid  | cc   |

+----+------+------+

| 12 |    3 | e    |

| 10 |    2 | l    |

|  8 |    1 | a    |

+----+------+------+

3 rows in set (0.00 sec)

 

前序

TC@SQL>select * from tt group by cid;

+----+------+------+

| id | cid  | cc   |

+----+------+------+

|  1 |    1 | f    |

|  3 |    2 | k    |

|  5 |    3 | c    |

+----+------+------+

3 rows in set (0.00 sec)

Mysql对group by 不检查聚合函数，所以在没使用聚合函数的情况下，查询返回值由于排序，遍历的不同而不同。

 

 

TC@SQL>select * from (select * from tt order by cid desc) as tmp  group by cid;

+----+------+------+

| id | cid  | cc   |

+----+------+------+

|  8 |    1 | a    |

|  3 |    2 | k    |

| 12 |    3 | e    |

+----+------+------+

3 rows in set (0.00 sec)

 

 

删除索引

TC@SQL>alter table tt drop index cid_idx;

Query OK, 12 rows affected (0.22 sec)

Records: 12  Duplicates: 0  Warnings: 0

 

Mysql在没用索引的情况下进行全表顺序扫描，mysql说了是自带的排序算法，经过测试我怀疑他根本没经过排序，就是简单的进行了全表扫描。

TC@SQL>select * from t group by cid;

+----+------+------+

| id | cid  | cc   |

+----+------+------+

|  1 |    1 | f    |

|  3 |    2 | k    |

|  5 |    3 | c    |

+----+------+------+

3 rows in set (0.00 sec)

 

TC@SQL>select * from t group by cid desc;

+----+------+------+

| id | cid  | cc   |

+----+------+------+

|  5 |    3 | c    |

|  3 |    2 | k    |

|  1 |    1 | f    |

+----+------+------+

3 rows in set (0.00 sec)

 

这种类型显示，分组只会查出最早被插入的数据，他仅对结构结进行了排序。

 

TC@SQL>select * from (select * from t order by cid desc) as tmp;

+----+------+------+

| id | cid  | cc   |

+----+------+------+

| 12 |    3 | e    |

| 11 |    3 | d    |

|  5 |    3 | c    |

|  6 |    3 | b    |

|  3 |    2 | k    |

|  4 |    2 | b    |

| 10 |    2 | l    |

|  9 |    2 | a    |

|  8 |    1 | a    |

|  7 |    1 | b    |

|  2 |    1 | d    |

|  1 |    1 | f    |

+----+------+------+

12 rows in set (0.04 sec)

 

下面的语句显示，他还是只取分组排序后的第一个值

TC@SQL>select * from (select * from t order by cid desc) as tmp group by cid;

+----+------+------+

| id | cid  | cc   |

+----+------+------+

|  8 |    1 | a    |

|  3 |    2 | k    |

| 12 |    3 | e    |

+----+------+------+

3 rows in set (0.00 sec)

 

 

TC@SQL>select * from (select * from t order by cid desc) as tmp group by cid desc;

+----+------+------+

| id | cid  | cc   |

+----+------+------+

| 12 |    3 | e    |

|  3 |    2 | k    |

|  8 |    1 | a    |

+----+------+------+

3 rows in set (0.00 sec)

 

TC@SQL>select * from (select * from t order by cid) as tmp;

+----+------+------+

| id | cid  | cc   |

+----+------+------+

|  1 |    1 | f    |

|  8 |    1 | a    |

|  7 |    1 | b    |

|  2 |    1 | d    |

|  4 |    2 | b    |

|  3 |    2 | k    |

|  9 |    2 | a    |

| 10 |    2 | l    |

|  5 |    3 | c    |

|  6 |    3 | b    |

| 11 |    3 | d    |

| 12 |    3 | e    |

+----+------+------+

12 rows in set (0.04 sec)

 

TC@SQL>select * from (select * from t order by cid) as tmp group by cid;

+----+------+------+

| id | cid  | cc   |

+----+------+------+

|  1 |    1 | f    |

|  4 |    2 | b    |

|  5 |    3 | c    |

+----+------+------+

3 rows in set (0.00 sec)

 

 

Order by索引列，无非是对已经排好序的索引进行前序或后序遍历。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25379809/viewspace-703999/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25379809/viewspace-703999/