explain
SELECT group_id,gmt_create
FROM group_message
WHERE user_id = 1 GROUP BY gmt_create
group_id,gmt_create 连接起来并不符合索引“最左前缀”原则
explain
SELECT gmt_create
FROM group_message
WHERE group_id=1 GROUP BY gmt_create;
3、使用临时表实现 GROUP BY
MySQL Query Optimizer 发现仅仅通过索引扫描并不能直接得到 GROUP BY 的结果之后，他就不得不选择通过使用临时表然后再排序的方式来实现 GROUP BY了。在这样示例中即是这样的情况。 group_id 并不是一个常量条件，而是一个范围，而且 GROUP BY 字段为 user_id。所以 MySQL 无法根据索引的顺序来帮助 GROUP BY 的实现，只能先通过索引范围扫描得到需要的数据，然后将数据存入临时表，然后再进行排序和分组操作来完成 GROUP BY。
explain
SELECT group_id
FROM group_message
WHERE group_id between 1 and 4
GROUP BY user_id;
示例数据库文件
| -- -------------------------------------------------------- -- Host: 127.0.0.1 -- Server version: 5.1.57-community - MySQL Community Server (GPL) -- Server OS: Win32 -- HeidiSQL version: 7.0.0.4156 -- Date/time: 2012-08-20 16:52:10 -- -------------------------------------------------------- /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET NAMES utf8 */; /*!40014 SET FOREIGN_KEY_CHECKS=0 */; -- Dumping structure for table test.group_message DROP TABLE IF EXISTS `group_message`; CREATE TABLE IF NOT EXISTS `group_message` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `group_id` int(10) unsigned DEFAULT NULL, `user_id` int(10) unsigned DEFAULT NULL, `gmt_create` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `abc` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `group_id_user_id_gmt_create` (`group_id`,`user_id`,`gmt_create`) ) ENGINE=MyISAM AUTO_INCREMENT=27 DEFAULT CHARSET=utf8; -- Dumping data for table test.group_message: 0 rows DELETE FROM `group_message`; /*!40000 ALTER TABLE `group_message` DISABLE KEYS */; INSERT INTO `group_message` (`id`, `group_id`, `user_id`, `gmt_create`, `abc`) VALUES (1, 1, 1, '2012-08-20 09:25:35', 1), (2, 2, 1, '2012-08-20 09:25:39', 1), (3, 2, 2, '2012-08-20 09:25:47', 1), (4, 3, 1, '2012-08-20 09:25:50', 2), (5, 3, 2, '2012-08-20 09:25:52', 2), (6, 3, 3, '2012-08-20 09:25:54', 0), (7, 4, 1, '2012-08-20 09:25:57', 0), (8, 4, 2, '2012-08-20 09:26:00', 0), (9, 4, 3, '2012-08-20 09:26:02', 0), (10, 4, 4, '2012-08-20 09:26:06', 0), (11, 5, 1, '2012-08-20 09:26:09', 0), (12, 5, 2, '2012-08-20 09:26:12', 0), (13, 5, 3, '2012-08-20 09:26:13', 0), (14, 5, 4, '2012-08-20 09:26:15', 0), (15, 5, 5, '2012-08-20 09:26:17', 0), (16, 6, 1, '2012-08-20 09:26:20', 0), (17, 7, 1, '2012-08-20 09:26:23', 0), (18, 7, 2, '2012-08-20 09:26:28', 0), (19, 8, 1, '2012-08-20 09:26:32', 0), (20, 8, 2, '2012-08-20 09:26:35', 0), (21, 9, 1, '2012-08-20 09:26:37', 0), (22, 9, 2, '2012-08-20 09:26:40', 0), (23, 10, 1, '2012-08-20 09:26:42', 0), (24, 10, 2, '2012-08-20 09:26:44', 0), (25, 10, 3, '2012-08-20 09:26:51', 0), (26, 11, 1, '2012-08-20 09:26:54', 0); /*!40000 ALTER TABLE `group_message` ENABLE KEYS */; /*!40014 SET FOREIGN_KEY_CHECKS=1 */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; |
