假如有下面这样一张用户表
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_user
where (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）：SQL 优化（一）：慎用 SQL 函数
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/160082.html
