现在又这样一个查询:
EXPLAIN SELECT
user_id,
name,
sum(money) pay_sum
FROM
tmp_table
WHERE
platform = 1
GROUP BY
user_id,
name,
order_date
HAVING
pay_sum >= 2000;
我建立了这样两条索引:
alter table tmp_table add index index_tmp2(platform, user_id, name,order_date,money);
alter table tmp_table add index index_tmp3(platform, user_id, name,order_date);
结果我发现实际上explain的结果是:
id: 1
select_type: SIMPLE
table: tmp_table
type: ref
possible_keys: index_tmp2,index_tmp3
key: index_tmp3
key_len: 1
ref: const
rows: 136906
Extra: Using where; Using temporary; Using filesort
但是我觉得这是十分没有道理的一件事情啊.
首先where子句后的platform=1是等值查询,所以索引后面的:
user_id, name,order_date 肯定是按照group by的要求排好序的了,那我最后加一个money字段,这样mysql不就直接可以通过索引完成这条sql语句了吗,为什么不使用money字段呢,而而且Extra列还有:
Using where; Using temporary; Using filesort
Using temporary;的话可以理解,有group by的聚集操作导致需要使用临时表,但是为什么会有Using where; Using filesort 这两个东西呢,platform不是已经被过滤了吗,为什么还用where?而且filesort也不需要啊.
请求各位能赐教,谢谢!
| tmp_table | CREATE TABLE `tmp_table` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`user_id` varchar(100) NOT NULL DEFAULT '' COMMENT '用户ID',
`money` decimal(20,4) NOT NULL DEFAULT '0.0000' COMMENT '金额',
`order_date` int(11) unsigned NOT NULL DEFAULT '19700701' COMMENT '订单日期',
`name` varchar(256) NOT NULL DEFAULT '' COMMENT '名称',
`platform` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0安卓1IOS2H5',
PRIMARY KEY (`id`),
KEY `index_tmp2` (`user_id`,`name`(191),`order_date`,`platform`,`money`)
KEY `index_tmp3` (`user_id`,`name`(191),`order_date`,`platform`)
) ENGINE=InnoDB AUTO_INCREMENT=276249 DEFAULT CHARSET=utf8mb4 |
这是表结构,数据大概是20万行测试数据,实际生产环境应该超过800万行.
表结构发出来看看,正常情况应该是要使用index_tmp2才对
另外你的where条件使用到了,并不是说用了索引就不会用where,根据where筛选的时候使用index和使用where并没有什么必然联系,至于Using filesort就是group by导致的,所以要看下你的表结构