SQL 优化(一):慎用 SQL 函数

65 阅读2分钟

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

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,一起学习,一起进步