ITPub博客

首页 > Linux操作系统 > Linux操作系统 > mysql 特别的 group by

mysql 特别的 group by

原创 Linux操作系统 作者:tangchaoql 时间:2011-08-03 16:45:23 0 删除 编辑

 

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的叶子节点,并且由于是后序遍历,只取第一个值,该索引值指向主键索引id12的,以此类推,下面的语句结果是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)

Mysqlgroup 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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-08-03

  • 博文量
    5
  • 访问量
    11898