本人微信公众号"aeolian"~

用where导致group by分组字段的索引失效

把两个单独的索引合并成一个组合索引,即把where条件字段的索引和group by的分组字段索引组合成一个。

如果分组的字段需要用函数处理,可以用索引函数

Generated Column(函数索引)

mysql5.7版本,函数索引用虚拟列,virtual是查询时在内存中计算,而store是计算好后存放在磁盘中。一般作为索引,默认用virtual。

语法:

[ GENERATED ALWAYS ] AS ( ) [ VIRTUAL|STORED ] [ UNIQUE [KEY] ] [ [PRIMARY] KEY ] [ NOT NULL ] [ COMMENT ]

修改table添加一个新列,由函数处理已有字段自动生成。

新建虚拟列

 ALTER TABLE tblName ADD virtualField varchar(50) GENERATED ALWAYS AS (FROM_UNIXTIME(bus_remind.arrivingTimeStamp)) virtual;

删除虚拟列

alter table tblName drop column fieldName;

例子

组合索引如下

《用where导致group by分组字段的索引失效》

sql语句

SELECT
    avg(arrivingBattery) AS battery
FROM
    bus_remind 
WHERE
    parkingCode = '101001' 
GROUP BY 
    DATE_FORMAT( FROM_UNIXTIME(arrivingTimeStamp ), '%Y-%m-%d %H:%i' )

explain结果

《用where导致group by分组字段的索引失效》

分析原因:

分组字段根据分钟分组(对字段进行函数处理)导致抛弃索引。

解决方案:

复合索引使用虚拟列技术,将虚拟列作为索引,Sql如下

/*创建*/
ALTER TABLE bus_remind ADD virtualArrivingTimeStamp char(16) GENERATED ALWAYS AS (DATE_FORMAT(FROM_UNIXTIME(arrivingTimeStamp),'%Y-%m-%d %H:%i')) VIRTUAL comment '来车时间函数索引列';
/*删除*/
alter table bus_remind drop column virtualArrivingTimeStamp

复合索引更改为(版本必须为5.7及以上)

《用where导致group by分组字段的索引失效》

sql语句更改

SELECT
    avg(arrivingBattery) AS battery
FROM
    bus_remind 
WHERE
    parkingCode = '101001' 
GROUP BY 
    virtualArrivingTimeStamp

explain结果

《用where导致group by分组字段的索引失效》

原文链接

官方链接

点赞

Leave a Reply

Your email address will not be published. Required fields are marked *