SQL 优化一: 慎用 SQL 函数

``

1. SQL 优化一: 慎用 SQL 函数

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

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;