现在又这样一个查询:
EXPLAIN SELECT user_id, name, sum(money) pay_sumFROM tmp_tableWHERE platform = 1GROUP BY user_id, name, order_dateHAVING 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: refpossible_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导致的,所以要看下你的表结构