mysql中groupby会用到索引吗_mysql order by 与索引的使用

测试数据表 user_group

表结构：

id int(10) primarey key auto_increment

user_id int(10) (唯一)

group_id mediumint(7) (较平均分布，每个约占1%)

数据量：10W

需求：查询数据表中依次按user_id、group_id、id降序取出10条数据

测试1：user_id 、group_id没有索引

SQL: select * from users_group order by user_id desc, group_id desc, id desc limit 10;

时间：0.03s

explain结果：type:all,  并且没有使用到索引。

既然如此，我们给user_id group_id 建立索引

测试2：分别对user_id 、group_id建立索引

SQL: select * from users_group order by user_id desc, group_id desc, id desc limit 10;

时间：0.03s

explain结果：type:all,  并且没有使用到索引。

我们发现结果一样，order by 根本没利用到索引。

测试3：取消原本索引，对user_id 、group_id建立联合索引

SQL: select * from users_group order by user_id desc, group_id desc, id desc limit 10;

时间：0.03s

explain结果：

users_group type:all,   并且没有使用到索引。

我们发现order by 根本没利用到索引。

测试4：保持对user_id 、group_id的联合索引，去除对主键ID的排序

SQL: select * from users_group order by user_id desc, group_id desc limit 10;

时间：0.00s (时间可以忽略不计啦)

explain结果：type:index,   并且使用到联合索引。

我们发现order by 终于利用到索引啦。

测试5：保持对user_id 、group_id的联合索引，对主键ID的排序

SQL:select a.* from (select * from users_group order by user_id desc, group_id desc limit 10) a order by a.id desc;

时间：0.00s (时间可以忽略不计啦)

explain结果：

users_group type:index,   并且使用到联合索引。

临时表：type:all  没使用到索引

我们发现order by利用到索引啦。

事实上这条语句在临时表a内部是成功利用索引扫描排序，对于外围的id依然使用的是全表扫描，这条语句的好处在于先利用索引筛选掉10条有效数据，再进行内部排序，虽然对ID不再利用索引，但是已经达到小结果集驱动的效果。

总结：

假设下面是对group_id、user_id 建设的联合索引

使用索引的MySQL Order By

下列的几个查询都会使用索引来解决 ORDER BY 或 GROUP BY 部分：

SELECT * FROM users_group  ORDER BY group_id, user_id,… ;  (联合索引)

SELECT * FROM users_group  WHERE group_id=’23′ ORDER BY user_id desc;

SELECT * FROM users_group  WHERE group_id=’12′ GROUP BY user_id desc;

SELECT * FROM users_group  WHERE group_id=’1′  ORDER BY group_id DESC, user_id DESC;

不使用索引的MySQL Order By

在另一些情况下，MySQL无法使用索引来满足 ORDER BY，尽管它会使用索引来找到记录来匹配 WHERE 子句。这些情况如下：

* 对不同的索引键做 ORDER BY ：

SELECT * FROM t1 ORDER BY key1, key2;  (未使用联合索引，测试2的情况)

* 在非连续的索引键部分上做 ORDER BY：

SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2;

* 同时使用了 ASC 和 DESC：

SELECT * FROM users_group ORDER BYgroup_id DESC, user_id ASC;

*未遵循左前缀(只有在user_id desc,group_id desc  或者 user_id desc 才利用到索引)

select * FROM users_group ORDER BY group_id DESC, user_id DESC;

或者：select * FROM users_group ORDER BY group_id ASC;

* 用于搜索记录的索引键和做 ORDER BY 的不是同一个：(查询行的关键字必须和索引关键字一致, 假设此时是建立的非联合索引)

SELECT * FROM users_group WHERE group_id = ‘23′ ORDER BY user_id desc;

* 有很多表一起做连接，而且读取的记录中在 ORDER BY 中的字段都不全是来自第一个非常数的表中(也就是说，在 EXPLAIN 分析的结果中的第一个表的连接类型不是 const)。

* 使用了不同的 ORDER BY 和 GROUP BY 表达式。

* 表索引中的记录不是按序存储。例如，HASH 和 HEAP 表就是这样。

到最后，我们再来看一个联表的查询

users表 :

id                  primarykey auto_increment

user_name(索引)

需求：users_group.id = users.id  按users_group 的group_id desc ,users表的username desc 查询20条数据

测试1：

SQL：select u.*, ug.group_id

from users_group ug

left join users u on ug.id = u.id

order by ug.group_id desc, u.username desc limit 10;

大部分人都会用这样的语句，好吧，我们看下时间：0.75s , 触目惊心

explain下：除了作为join 的ID有利用到索引，其他索引完全没使用到。

那怎么办呢，这样的SQL太呕心啦，记得把，小结果集驱动大结果集

测试2：

SQL:

EXPLAIN: