声明:本文章内容是根据极客时间中林晓斌的课程《MYSQL45讲》,经过学习,加以自己的理解形成的笔记。具体原文可以到官网进行阅读。如有侵权请,告知删除。
1.条件字段函数操作
现如今有下表交易日志表tradelog
CREATE TABLE `tradelog` (
`id` int(11) NOT NULL,
`tradeid` varchar(32) DEFAULT NULL, -- 交易id
`operator` int(11) DEFAULT NULL, -- 操作员
`t_modified` datetime DEFAULT NULL, -- 修改时间
PRIMARY KEY (`id`), KEY `tradeid` (`tradeid`),
KEY `t_modified` (`t_modified`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;)
假设记录中记录了 2016-2018年所有的数据,运营部门的一个需求是 要同居发生所有年份中7月份的数据,这个逻辑看上去并不复杂。sql如下:
select count(*) from tradelog where month(t_modified)=7;
但是上面的查询语句,确执行了很久。至于为什么,很多了人就会回答:对字段进行函数操作,这样的话索引会失效
,至于为啥?很多人可能就答不上来,答案如图:
下面是这个 t_modified 索引的示意图。方框上面的数字就是 month() 函数对应的值。如果直接按完整的日期查询,索引B+树就能快速找到相应的位置。但如果是经过运算的话,因为其对应的结果不存在有序性,也就失去了索引快速定位的能力,需要全索引树遍历了。其实并不是没有用到索引;是没有用到索引树的搜索功能
案例二:隐式转换
select * from tradelog where tradeid=110717;
上面的sql语句,虽然tradeid上建立了索引,但是执行计划显示的是 全表扫描。因为tradeid 的字段类型是 varchar(32),而输入的参数却是整型,所以需要做类型转换。那么做类型转换相当于,对应的字段上进行函数操作了,那么也就如同案例一,也就解释了为什么需要全表扫描
案例三:编码不一致
select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2;
当d表与l表的编码不一致时,同样会导致 无法使用索引树查询