SQL 优化（一）：慎用 SQL 函数

作者：hungxy

2023-06-26
广东
本文字数：1046 字
阅读完需：约 3 分钟

假如有下面这样一张用户表

CREATE TABLE `t_user` (  `user_id` int(11) NOT NULL AUTO_INCREMENT,  `username` varchar(50) DEFAULT NULL,  `sex` tinyint(1) DEFAULT NULL,  `mobile` varchar(45) DEFAULT NULL,  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,  PRIMARY KEY (`user_id`),  KEY `idx_create_time` (`create_time`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8mb4

复制代码

现在如果要查询在 7 月份创建的数据，我们的 SQL 可能这样写

select * from t_user where month(create_time) = '7' limit 1,10

复制代码

接着我们使用explain查看这条 SQL 的执行计划

可以看到，该 SQL 并没有使用索引，这是为什么呢？

由于我们这里的索引，使用的是 B+ Tree，而 B+ Tree 提供的快速定位能力，是由于 B+ Tree 同源节点是有序的，这一特性保证的

借用丁奇的《MySQL45 讲》中的图，如果我们使用where create_time = '2018-7-1'的话，就能根据下图绿色箭头快速从索引中定位到create_time='2018-7-1'这条记录

但是用where month(create_time)='7'，在树的第一层的时候就不知道怎么办了，因此对索引字段做函数操作的话，可能会破坏索引值的有序性，因此优化器就放弃走树索引

需要注意的是，优化器只是放弃走树索引，并不是放弃使用这个索引， 如下面的 SQL

select count(*) from t_user where month(create_time) = '7'

复制代码

使用explain查看这条 SQL 的执行结果

可以看到，扫描的函数还是 10w 行，但是Using index表示该 SQL 使用了覆盖索引，因为 count(*) 统计行数的时候，可以直接在idx_create_time索引树上进行统计

那么有什么优化方案呢？还是查找 7 月份的数据，我们可以讲 SQL 改写成下面这种形式

select * from t_userwhere (create_time >= '2021-07-01' and create_time <= '2021-07-31')  or (create_time >= '2022-07-01' and create_time <= '2022-07-31')  or (create_time >= '2023-07-01' and create_time <= '2023-07-31');

复制代码

到这里，我们知道函数不走索引的原因是因为索引破坏了树结构的有序性，那么，如果我们使用的函数不会破坏树结构的有序性呢？MySQL 还会走索引吗？比如下面的语句

select * from t_user where user_id + 1 = 1000;

复制代码

同样的，使用explain查看执行结果

可以看到，MySQL 还是不走索引，这就属于 MySQL 的偷懒行为啦，只要有函数操作，MySQL 就不走索引，为了使语句走索引，我们需要将 SQL 语句改写成下面这样

select * from t_user where user_id = 1000 - 1;

复制代码

关注公众号：huangxy ，一起学习，一起进步

发布于: 2023-06-26阅读数: 59

原文链接:【http://xie.infoq.cn/article/636d0fcd1d5799ad2758a6248】。文章转载请联系作者。

hungxy

关注

还未添加个人签名 2018-07-19 加入

还未添加个人简介

发布

暂无评论

创作场景

SQL 优化（一）：慎用 SQL 函数

hungxy

评论