MySQL-在字段上使用函数不会走索引的原因是什么？

最新推荐文章于 2024-02-01 12:00:53 发布

置顶君战

最新推荐文章于 2024-02-01 12:00:53 发布

阅读量2.3k

点赞数 4

分类专栏： MySQL 文章标签：数据结构 mysql 数据库索引 java

本文链接：https://blog.csdn.net/m0_43448868/article/details/113841708

版权

MySQL 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

题记

首先，祝大家新年快乐，牛年大吉，万事如意！

我们常常从关于MySQL的各种技术博客、书籍上面看到这样一句话：“不能在字段上使用函数，否则就不会走索引”。那么这句话的依据是什么呢？

本文就围绕“不能在字段上使用函数，否则就不会走索引”这句话来展开分析。

Talk is cheap. Show me the code

假设存在这样一张交易记录表-trade_log，该表中存在三个字段id(主键)，operator_id(操作人id)、create_time(交易时间)。建表语句：

CREATE TABLE `trade_log` (
  `id` int NOT NULL AUTO_INCREMENT,
  `operator_id` int DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB ;

表中有13条记录：

INSERT INTO `user_database`.`trade_log`(`id`, `operator_id`, `create_time`) VALUES (1, 1, '2021-02-18 09:36:22');
INSERT INTO `user_database`.`trade_log`(`id`, `operator_id`, `create_time`) VALUES (2, 2, '2021-02-18 09:37:32');
INSERT INTO `user_database`.`trade_log`(`id`, `operator_id`, `create_time`) VALUES (3, 3, '2021-03-04 09:37:34');
INSERT INTO `user_database`.`trade_log`(`id`, `operator_id`, `create_time`) VALUES (4, 4, '2021-04-08 09:37:37');
INSERT INTO `user_database`.`trade_log`(`id`, `operator_id`, `create_time`) VALUES (5, 5, '2021-05-08 09:37:40');
INSERT INTO `user_database`.`trade_log`(`id`, `operator_id`, `create_time`) VALUES (6, 6, '2021-02-18 09:37:42');
INSERT INTO `user_database`.`trade_log`(`id`, `operator_id`, `create_time`) VALUES (7, 7, '2021-02-18 09:37:46');
INSERT INTO `user_database`.`trade_log`(`id`, `operator_id`, `create_time`) VALUES (8, 8, '2021-02-18 09:37:49');
INSERT INTO `user_database`.`trade_log`(`id`, `operator_id`, `create_time`) VALUES (9, 9, '2021-02-18 09:37:53');
INSERT INTO `user_database`.`trade_log`(`id`, `operator_id`, `create_time`) VALUES (10, 10, '2021-02-18 09:37:57');
INSERT INTO `user_database`.`trade_log`(`id`, `operator_id`, `create_time`) VALUES (11, 11, '2021-02-18 09:37:59');
INSERT INTO `user_database`.`trade_log`(`id`, `operator_id`, `create_time`) VALUES (12, 12, '2021-02-18 09:38:01');
INSERT INTO `user_database`.`trade_log`(`id`, `operator_id`, `create_time`) VALUES (13, 13, '2021-02-18 09:38:06');

现在有一个需求：统计所有二月份的交易记录总数，SQL语句是这样：

select count(*) from trade_log where MONTH(create_time) = 2;

可以看到，在create_time这个条件字段上使用了month()函数，首先我们先猜测下有没有走idx_create_time这个索引呢？

使用explain命令，查看这条SQL语句的执行结果：

mysql> EXPLAIN select count(*) from trade_log where MONTH(create_time) = 2;
+----+-------------+-----------+------------+-------+---------------+-----------------+---------+------+------+----------+--------------------------+
| id | select_type | table     | partitions | type  | possible_keys | key             | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-----------+------------+-------+---------------+-----------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | trade_log | NULL       | index | NULL          | idx_create_time | 6       | NULL |   13 |   100.00 | Using where; Using index |
+----+-------------+-----------+------------+-------+---------------+-----------------+---------+------+------+----------+--------------------------+
1 row in set (0.02 sec)

答案是不是和预想的不一样，居然走idx_create_time索引了！那么这是为什么呢？

在分析问题之前，需要先明白两个概念：索引快速定位和全索引扫描。

我们都知道InnoDB是索引组织表，底层数据结构为B+树，而B+树提供的索引快速定位能力是基于同一层兄弟节点有序性来实现的。
idx_create_time索引示意图
上图是idx_create_time的索引示意图，方框上的数字是month()函数计算框内日期后得到的值。正因为是B+树提供的索引快速定位能力是基于同一层兄弟节点有序性来实现的这个原因，当传入2(month(create_time) = 2)的时候，在树的第一层就不知道该怎么办了。因为只有对索引使用month()函数计算后，才知道到底等不等于2，而这就破坏了索引值的有序性，因此优化器就决定放弃走树搜索功能(索引快速定位)。

但是并不意味着优化器放弃使用这个索引，explain命令返回的Extra 字段中的Using index以及key = idx_create_time佐证了这一点。

虽然无法使用索引快速定位能力，但是优化器可以选择遍历主键索引，也可以选择遍历索引idx_create_time，但是优化器对比这两个索引大小后发现，索引idx_create_time更小，遍历这个索引树意味着比遍历主键索引树来得更快(主键索引叶子节点存放着行数据，这意味着每个数据页上能存放的索引较少)，因此最终还是会选择使用索引idx_create_time。即使用全索引扫描，rows = 13证明了的确进行了全表扫描。

既然我们已经知道在字段上使用函数导致不能走索引的原因，那么我们可以在字段上进行范围查询来让优化器使用索引快速定位能力。

select count(*) from trade_log where create_time BETWEEN '2021-02-01 00:00:00' AND '2021-03-01 00:00:00'; /*如果有其它年份继续拼接**/

继续通过explain命令来查看执行结果：

mysql> EXPLAIN select count(*) from trade_log where create_time BETWEEN '2021-02-01 00:00:00' AND '2021-03-01 00:00:00';
+----+-------------+-----------+------------+-------+-----------------+-----------------+---------+------+------+----------+--------------------------+
| id | select_type | table     | partitions | type  | possible_keys   | key             | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-----------+------------+-------+-----------------+-----------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | trade_log | NULL       | range | idx_create_time | idx_create_time | 6       | NULL |   10 |   100.00 | Using where; Using index |
+----+-------------+-----------+------------+-------+-----------------+-----------------+---------+------+------+----------+--------------------------+
1 row in set (0.03 sec)