explain SELECT a, b, COUNT(*) FROM tbname
GROUP BY a, b
order by a DESC
limit 1
a 和 b 列已经设置联合索引, 为什么这种操作也会执行全表扫描呢?explain SELECT a, b FROM tbname
GROUP BY a, b
order by a DESC
去掉 COUNT 和 limit 则走索引没有扫描, 这是为什么呢?
这种场景有什么优化思路呢?
表结构CREATE TABLE `tbname` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`a` INT(11) NULL DEFAULT '0',
`b` INT(11) NULL DEFAULT '0',
PRIMARY KEY (`id`),
INDEX `testkey` (`a`, `b`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;
INSERT INTO `tbname` (`a`, `b`) VALUES ('1', '1');
INSERT INTO `tbname` (`a`, `b`) VALUES ('1', '1');
INSERT INTO `tbname` (&#