mysql group by 千万_Mysql 如何提升 Group by + Having 型子查询的查询速度（千万级别数据）...

abcfyk

2015-03-18 18:55:18 +08:00

@jk2r

类似这种：

CREATE TABLE `outDetail`{

`id` int(11) NOT NULL AUTO_INCREMENT,

`outId` int(11) NOT NULL,

`skuId` varchar(30) NOT NULL,

`outDate` datetime NOT NULL,

`outQty` int(11) NOT NULL

PRIMARY KEY (`id`),

KEY `indexOutId` (`outId`),

KEY `indexSkuId` (`skuId`),

KEY `indexOutDate` (`outDate`),

}ENGINE=InnoDB DEFAULT CHARSET=utf8

explain 结果大概如下

+----+-------------+-----------------+-------+---------------+-------------+---------+------+----------+-------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-----------------+-------+---------------+-------------+---------+------+----------+-------+

| 1 | SIMPLE | outDetail | index | NULL | IndexSkuId | 92 | NULL | 23980757 | |

+----+-------------+-----------------+-------+---------------+-------------+---------+------+----------+-------+