为什么mysql explain显示没有用到辅助索引的排序?

问题描述

问 题

现在又这样一个查询:

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导致的，所以要看下你的表结构

这篇关于为什么mysql explain显示没有用到辅助索引的排序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！