MySQL学习笔记之SQL语句逻辑相同，性能差异巨大（十八）

Love The Way You Lie 2023-07-10 12:58 70阅读 0赞

> 声明：本文章内容是根据极客时间中林晓斌的课程《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;

但是上面的查询语句，确执行了很久。至于为什么，很多了人就会回答：`对字段进行函数操作，这样的话索引会失效`，至于为啥？很多人可能就答不上来，**答案如图：**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczMjk1NQ_size_16_color_FFFFFF_t_70]  
下面是这个 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表的编码不一致时，同样会导致 无法使用索引树查询

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczMjk1NQ_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200302193437627.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczMjk1NQ==,size_16,color_FFFFFF,t_70