当前位置： 首页 > news >正文

MySQL中使用函数会使索引失效？

news 2025/7/18 14:20:13

文章目录

  • 1、前置准备
  • 2、ChatGPT的答案
  • 3、实践证明
    • SQL1
    • SQL2
    • SQL3
    • SQL4
    • SQL5
  • 4、总结

1、前置准备

首先创建我们要测试的库表

CREATE TABLE `lianhe_index` (`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',`name` varchar(255) DEFAULT NULL,`age` int(11) DEFAULT NULL,`number` int(11) DEFAULT NULL,`create_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,PRIMARY KEY (`id`),KEY `idx_nameagenumber` (`name`,`age`,`number`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=200001 DEFAULT CHARSET=utf8;

主要建立了主键索引id和联合索引idx_nameagenumber，造了10W条数据。
一般函数可以使用在select子句中的列，也可以使用在where子句中的列，下面我们就这两种情况来分析下。

2、ChatGPT的答案

首先我们来问下ChatGPT
在这里插入图片描述
在MySQL中，使用函数作为select字段通常不会走索引。这是因为函数的使用会导致MySQL无法直接匹配索引键值，而是需要对所有记录进行函数计算后才能确定结果。然而，某些特殊情况下，MySQL可能会通过某些优化策略来利用索引，例如在某些情况下可以使用覆盖索引来避免访问表数据。
对于where字段的情况，使用函数作为条件通常也不会走索引。同样的原因，函数的使用会导致MySQL无法直接匹配索引键值，而是需要对所有记录进行函数计算后才能确定结果。因此，如果希望where字段能够走索引，建议尽量避免使用函数。
上面是ChatGPT的回答，回答正确？先保留答案，接下来我们来实践下~

3、实践证明

我们主要分析下面这5条SQL，先猜猜看会不会走索引？

SELECT id,age,number,UPPER(name),create_time FROM lianhe_index where name='林伟' and age=69 and number=76291;
SELECT id,age,number,name,create_time FROM lianhe_index where UPPER(name)='伟A' and age=69 and number=76291;
SELECT id,age,number,name,create_time FROM lianhe_index where name=UPPER(name) and age=69 and number=76291;
SELECT id,age,number,name,create_time FROM lianhe_index where UPPER(name)='伟A' and age=69 and number=76291 and DATE(create_time)="2022-11-11";
SELECT id,age,number,name,create_time FROM lianhe_index where name=UPPER(name) and age=69 and number=76291 and DATE(create_time)="2022-11-11";

执行计划如下，和你想的一样？

mysql> EXPLAIN SELECT id,age,number,UPPER(name),create_time FROM lianhe_index where name='林伟' and age=69 and number=76291;
EXPLAIN SELECT id,age,number,name,create_time FROM lianhe_index where UPPER(name)='伟A' and age=69 and number=76291;
EXPLAIN SELECT id,age,number,name,create_time FROM lianhe_index where name=UPPER(name) and age=69 and number=76291;
EXPLAIN SELECT id,age,number,name,create_time FROM lianhe_index where UPPER(name)='伟A' and age=69 and number=76291 and DATE(create_time)="2022-11-11";
EXPLAIN SELECT id,age,number,name,create_time FROM lianhe_index where name=UPPER(name) and age=69 and number=76291 and DATE(create_time)="2022-11-11";
+----+-------------+--------------+------------+------+-------------------+-------------------+---------+-------------------+------+----------+-------+
| id | select_type | table        | partitions | type | possible_keys     | key               | key_len | ref               | rows | filtered | Extra |
+----+-------------+--------------+------------+------+-------------------+-------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | lianhe_index | NULL       | ref  | idx_nameagenumber | idx_nameagenumber | 778     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+--------------+------------+------+-------------------+-------------------+---------+-------------------+------+----------+-------+
1 row in set (0.02 sec)+----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | lianhe_index | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 99954 |     1.00 | Using where |
+----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set (0.02 sec)+----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | lianhe_index | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 99954 |     0.10 | Using where |
+----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set (0.02 sec)+----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | lianhe_index | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 99954 |     1.00 | Using where |
+----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set (0.02 sec)+----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | lianhe_index | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 99954 |     0.10 | Using where |
+----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set (0.02 sec)

下面我们逐个来分析下

SQL1

SELECT id,age,number,UPPER(name),create_time FROM lianhe_index where name='林伟' and age=69 and number=76291;
+----+-------------+--------------+------------+------+-------------------+-------------------+---------+-------------------+------+----------+-------+
| id | select_type | table        | partitions | type | possible_keys     | key               | key_len | ref               | rows | filtered | Extra |
+----+-------------+--------------+------------+------+-------------------+-------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | lianhe_index | NULL       | ref  | idx_nameagenumber | idx_nameagenumber | 778     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+--------------+------------+------+-------------------+-------------------+---------+-------------------+------+----------+-------+

可以看到，执行计划中的key是使用到了组合索引idx_nameagenumber，扫码的行数rows=1，所以，索引生效
虽然RIGHT(name,1)是一个函数，但是它是在where子句中应用于已经在索引中的列name上的，所以MySQL可以使用索引来定位匹配的行

如果我们不查询create_time，还会发现执行计划中Extra中出现了Using index，表示使用了覆盖索引

SELECT id,age,number,UPPER(name) FROM lianhe_index where name='林伟' and age=69 and number=76291;
+----+-------------+--------------+------------+------+-------------------+-------------------+---------+-------------------+------+----------+-------------+
| id | select_type | table        | partitions | type | possible_keys     | key               | key_len | ref               | rows | filtered | Extra       |
+----+-------------+--------------+------------+------+-------------------+-------------------+---------+-------------------+------+----------+-------------+
|  1 | SIMPLE      | lianhe_index | NULL       | ref  | idx_nameagenumber | idx_nameagenumber | 778     | const,const,const |    1 |   100.00 | Using index |
+----+-------------+--------------+------------+------+-------------------+-------------------+---------+-------------------+------+----------+-------------+

SQL2

SELECT id,age,number,name,create_time FROM lianhe_index where UPPER(name)='伟A' and age=69 and number=76291;
+----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | lianhe_index | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 99954 |     1.00 | Using where |
+----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+

可以看到，执行计划中的key=null，rows=99954，Extra列显示为"Using where"，这表示查询使用了where子句进行过滤，但没有使用索引

如果我们不查询create_time，可以发现在执行计划中，“possible_keys"列显示为"NULL”，这表示没有其他可用的索引。但是在"key"列中显示了"idx_nameagenumber"，这表示该索引被选择用于执行查询。此外，Extra列显示了"Using where; Using index"，这表示查询使用了where子句进行过滤，并且使用了索引来定位匹配的行。所以是使用了索引，但效果不佳，个人觉得执行计划这里是有问题的

SELECT id,age,number,name FROM lianhe_index where UPPER(name)='伟A' and age=69 and number=76291;
+----+-------------+--------------+------------+-------+---------------+-------------------+---------+------+-------+----------+--------------------------+
| id | select_type | table        | partitions | type  | possible_keys | key               | key_len | ref  | rows  | filtered | Extra                    |
+----+-------------+--------------+------------+-------+---------------+-------------------+---------+------+-------+----------+--------------------------+
|  1 | SIMPLE      | lianhe_index | NULL       | index | NULL          | idx_nameagenumber | 778     | NULL | 99954 |     1.00 | Using where; Using index |
+----+-------------+--------------+------------+-------+---------------+-------------------+---------+------+-------+----------+--------------------------+

SQL3

SELECT id,age,number,name,create_time FROM lianhe_index where name=UPPER(name) and age=69 and number=76291;
+----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | lianhe_index | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 99954 |     0.10 | Using where |

可以看到，执行计划中的key=null，rows=99954，Extra列显示为"Using where"，这表示查询使用了where子句进行过滤，但没有使用索引

SQL4

SELECT id,age,number,name,create_time FROM lianhe_index where UPPER(name)='伟A' and age=69 and number=76291 and DATE(create_time)="2022-11-11";
+----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | lianhe_index | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 99954 |     1.00 | Using where |
+----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+

可以看到，执行计划中的key=null，rows=99954，Extra列显示为"Using where"，这表示查询使用了where子句进行过滤，但没有使用索引

SQL5

SELECT id,age,number,name,create_time FROM lianhe_index where name=UPPER(name) and age=69 and number=76291 and DATE(create_time)="2022-11-11";
+----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | lianhe_index | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 99954 |     0.10 | Using where |

可以看到，执行计划中的key=null，rows=99954，Extra列显示为"Using where"，这表示查询使用了where子句进行过滤，但没有使用索引

4、总结

当函数应用于select子句中的索引列时，索引仍然生效；当函数应用于where子句中的索引列时，MySQL可能无法使用索引来加快查询速度，虽然存在部分情况，执行计划显示keys使用了索引，但实际效果不佳，可以当作索引失效了。

http://www.lryc.cn/news/191087.html

相关文章：

  • 金和OA C6任意文件读取漏洞 复现[附POC]
  • IP地址技术原理
  • 【网络安全】密码学基础
  • AWS AD Connector 的网络配置
  • kafka详解(三)
  • [Spring] SpringMVC 简介（二）
  • idea一些不太常用但是能提升编码效率的快捷键
  • vsto word属性信息 并读取
  • android之TextView自由选择复制
  • 【mysql】 bash: mysql: command not found
  • 鲲山科技：引入和鲸 ModelWhale，实现量化策略的高效迭代
  • PFSK152 3BSE018877R1 有源滤波器的定义
  • WebDAV之π-Disk派盘 + 恒星播放器
  • 亚马逊，速卖通，敦煌产品测评补单攻略：低成本、高安全实操指南
  • 常用linux解压命令
  • TensorFlow入门(二十二、梯度下降)
  • WPF中的多重绑定
  • 区块链在游戏行业的应用
  • uniapp快速入门系列（4）- 微信小程序开发
  • Kafka保证消息幂等以及解决方案
  • 接口测试用例设计
  • wireshark抓rtp包，提取出H265裸流数
  • Excel往Word复制表格时删除空格
  • 客户机操作系统已禁用 CPU。请关闭或重置虚拟机（解决）
  • UnityShaderLab —— 简单的流光shader
  • 代理IP在保护跨境商家网络安全中的重要作用
  • 2核4G服务器支持多少用户同时在线访问？卡不卡？
  • [Error]在Swift项目Build Settings的Preprocessor Macros中定义的宏无效的问题
  • 网格管理安全巡检系统—助企业全面安全检查
  • 【Java】replace替换方法