环境说明

mysql 5.7.2
表数据条数 120万条，该表拥有102个字段，用于记录每天的订单信息的数据，有单量，收入金额，支出金额，机构等字段。

建表语句大致如下

CREATE TABLE `t` (
  `order_day` date DEFAULT NULL COMMENT '统计时间日',
  `join_order_num` bigint(20) DEFAULT NULL COMMENT '委托量',
  `channel_cost` decimal(12,2) DEFAULT NULL COMMENT '渠道商结算价',
  `customer_cost` decimal(12,2) DEFAULT NULL COMMENT '客户结算价',
  `provider_cost` decimal(12,2) DEFAULT NULL COMMENT '服务商采购价',
  `case_prov` varchar(20) DEFAULT NULL COMMENT '省',
  `case_city` varchar(32) DEFAULT NULL COMMENT '市',
  `case_area` varchar(32) DEFAULT NULL COMMENT '区',
  `company_id` bigint(20) DEFAULT NULL COMMENT '渠道商Id',
  `channel_name` varchar(32) DEFAULT NULL COMMENT '渠道商名称',
  `provider_id` bigint(20) DEFAULT NULL COMMENT '服务商Id',
   `provider_name` varchar(32) DEFAULT NULL COMMENT '服务商名称',
  `order_type` varchar(16) DEFAULT NULL COMMENT '订单类型',
  `service_name` varchar(16) DEFAULT NULL COMMENT '服务类型',
  `product_name` varchar(16) DEFAULT NULL COMMENT '产品类型'

  ... 其他字段省略
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=''

由于发现经常使用 order_day 字段查询，所以新建order_day索引。
添加索引SQL：

alter table t add index idx_order_day(order_day);

查询SQL如下，发现当统计时间范围过长的时候，查询耗时剧增

select 
-- sql_no_cache
       order_day
       ,sum(join_order_num) as join_order_num
       ,sum(ifnull(channel_cost,0) + ifnull(customer_cost,0)) as td_income_amount
       ,sum(ifnull(provider_cost,0)) as td_pay_amount
       ,sum(ifnull(channel_cost,0) + ifnull(channel_cost,0)) - sum(ifnull(provider_cost,0)) as gross_profit
from zbcf_analysis.t
 -- ignore index (idx_order_day)
where order_day>='2022-01-01'  and order_day <= '2022-11-11'
group by order_day ;

现象

不使用索引查询耗时 3-4s
使用索引查询耗时 11-12 s

explain结果

• 不使用索引

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	All	idx_order_day				1133492	11.11	Using where; Using temporary; Using filesort

• 使用索引

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	index	idx_order_day	idx_order_day	4		1133492	50.00	Using where

用rows × filtered可获得和下一张表连接的行数。使用索引后，filtered 还变大了，这个索引没有达到快速过滤数据的效果，并且增加查询索引的开销。

将时间查询范围改为

order_day>='2022-10-01'  and order_day <= '2022-11-11'

• 不使用索引

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	All	idx_order_day				1133492	11.11	Using where; Using temporary; Using filesort

• 使用索引

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	index	idx_order_day	idx_order_day	4		107470	100.00	Using index condition

可以看到 rows 只有 10万了。

不使用索引查询耗时 2.7 s
使用索引查询耗时 0.9s

总结

MySQL可能会选错索引，在使用索引达到不了快速筛选数据的目的时，直接全表扫描时更好的方案。