很多时候我们需要查询类似于所有人做题量之类的查询,一般第一时间想到的会是group by 加count,这个在数据量不大的情况下还没问题,但数据达到百万级别就会是很大的问题,因为group by的字段如果过多会出现索引失效,例如以下例子:
* 该表主要为了给大家看个结构,有删减
CREATE TABLE `practice_answer_log` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`u_id` int(11) unsigned DEFAULT 0 COMMENT '用户id',
`first_id` int(10) unsigned DEFAULT 0 COMMENT '一级栏目ID',
`istrue` tinyint(1) DEFAULT 0 COMMENT '是否答对 1是0否',
PRIMARY KEY (`id`),
KEY `u_id` (`u_id`),
KEY `istrue` (`istrue`) USING HASH,
KEY `first_id_btree` (`first_id`) USING BTREE,
KEY `u_id_4` (`u_id`,`first_id`),
KEY `first_id` (`first_id`,`istrue`),
KEY `u_id_2` (`u_id`,`istrue`),
KEY `u_id_3` (`u_id`,`first_id`,`istrue`)
) ENGINE=InnoDB AUTO_INCREMENT=6790746 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='所有练习记录表';
当执行下面的这句group by 时消耗约40s,explain发现因为是查询所有人的数据,u_id索引失效,相关联合索引也无效
SELECT count(*) AS `t_count`,`u_id` FROM `practice_answer_log` WHERE `istrue` = 1 GROUP BY `u_id`
当我们改为子查询以下形式,已经快了n倍,从explain也可以看出走了联合索引
select id,(SELECT count(*) AS `t_count` FROM `practice_answer_log` WHERE `istrue` = 1 and u_id = user.id) cnt from user
一点小经验希望对大家有用