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

最新推荐文章于 2023-10-16 06:00:47 发布
最新推荐文章于 2023-10-16 06:00:47 发布
阅读量677 收藏 1
点赞数 2
分类专栏： 数据库 文章标签： mysql 数据库
程序员Forlan
本文链接：https://blog.csdn.net/qq_36433289/article/details/133743103
版权

文章目录

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使用了索引，但实际效果不佳，可以当作索引失效了。

程序员Forlan
关注
  • 2
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
【什么情况会导致 MySQL 索引失效？】
学无止境
03-04 980
【什么情况会导致 MySQL 索引失效？】
索引失效的几种情况
m0_70325779的博客
09-06 598
是数据库比较偏底层的一个东西，这里我就不详细说明了，我们在数据库执行一条SQL指令的时候，都会经过优化器，优化器会以效率最优的方式来处理我们的SQL语句，我们在做一条查询语句的时候，有可能使用索引效率会更高，有可能不使用索引效率会更高，这都是有优化器说了算的，这也就产生了索引会失效的情况，下面我就来详细说一说索引可能失效的几种情况。可以看到，在上面，我为 student 学生表建立了一个三个字段的联合索引，当执行SQL查询语句时，因为 age 字段是联合字段的第一个，底层优化器会用到该字段；
面试突击：什么情况会导致 MySQL 索引失效？
m0_71777195的博客
07-04 744
为了验证 MySQL 中哪些情况下会导致索引失效，我们可以借助 explain 执行计划来分析索引失效的具体场景。explain 使用如下，只需要在查询的 SQL 前面添加上 explain 关键字即可，如下图所示：而以上查询结果的列中，我们最主要观察 key 这一列，key 这一列表示实际使用的索引，如果为 NULL 则表示未使用索引，反之则使用了索引。以上所有结果列说明如下：其中最重要的就是 type 字段，type 值类型如下：all — 扫描全表数据；index — 遍历索引；range — 索引范
使用计算列会导致索引无效
Acsxionian的博客
07-16 281
CREATE INDEX IX_PersonContact_FirstNameLastName ON Person.Person(FirstName,LastName) GO SELECT BusinessEntityID,FirstName,LastName FROM Person.Person WHERE FirstName+' '+ LastName='Gustavo Achong' 改为 -使用计算列 ALTER TABL
Mysql如何避免常见的索引失效
WgRui的博客
04-13 3561
避坑
MySql索引失效的情况
南风知我意
03-06 934
MySql索引失效的情况
Mysql 函数索引（解决mysql使用函数导致索引失效情况）
心流时间的博客
12-19 1261
mysql 函数索引
坚持的力量：mysql学习第19天-为什么用了索引字段却不走索引
epitomizelu的专栏
05-12 1149
有三种情况，即使你用了索引字段，mysql在执行sql的时候也不会走sql。 一，索引字段用函数 比如，create_time上使用了索引，但如下sql却不会走索引： select * from t where month(t) = 7 原因是month(t)破坏了索引字段在索引树上的有序性，索引之所以快，就是因为索引在各层上是有序的。 二，隐式字段类型转换 如果索引字段my_id是varchar类型，如下sql不走索引： select * from t where t.my_id = 1 因为在mys
mysql 使用函数会导致索引失效?
09-23
是的，在 MySQL 中，如果在 WHERE 子句中使用函数处理列的值，则可能会导致索引失效。这是因为当使用函数处理列的值时，MySQL 将无法使用索引进行快速查找。 例如，如果在 WHERE 子句中使用 LOWER() 函数来搜索小写...
什么情况会导致 MySQL 索引失效？
Chenhui98的博客
08-23 535
为了验证 MySQL 中哪些情况下会导致索引失效，我们可以借助 explain 执行计划来分析索引失效的具体场景。
导致MySQL索引失效的一些常见写法总结
09-14
3. **范围查询**：如果查询条件包含索引列的范围，如`WHERE column BETWEEN a AND b`，MySQL通常只能使用索引的一部分，导致部分索引失效。 4. **使用否定条件**：如`WHERE column != value`，MySQL可能无法有效地...
MySQL 函数索引功能终于可以实现了
最新发布
sql server的专栏
10-16 201
开头还是介绍一下群，如果感兴趣PolarDB ,MongoDB ,MySQL ,PostgreSQL ,SQL Server,Redis ，Oracle ,Oceanbase 等有问题，有需求都可以加群群内有各大数据库行业大咖，CTO，可以解决你的问题。加群请加微信号 liuaustin3 （共1280人左右 1 + 2 + 3 +4）新人会进入3群（3群接近400准备关闭自由申请）+ 4群当对...
Oracle索引梳理系列（六）- Oracle索引种类之函数索引
极客神殿
04-06 620
函数索引 1.1 概述 在实际应用中，当条件列使用函数运算进行数据匹配时，即使该列建立了索引，索引也不会被使用。 如下示例，其中在owner列上建立一个普通b-tree索引,观 察两种查询方式（不使用UPPER函数及使用UPPER函数）的执行计划的区别。 --查看表上的数据分布情况，可以确定，对于索引列`owner`，针对`scott`以及`bi`的普通查询，一定会使用索引。 Yumiko...
Mysql使用函数后不走索引怎么优化？
怪咖@的博客
04-28 3582
网上很多人说mysql一旦使用函数就不走函数，但是事实真的是如此吗？我先说明，并不是如此的，本篇文章会通过`DAYOFWEEK()`和`substr()`两个函数作为条件查询，看看究竟是否会走索引（其他函数同理），使用函数不走索引的时候又应该如何做sql优化，本篇文章重点是基于这两点进行分析。
MySQL 索引
name_sakura的博客
02-28 356
假设用数组存储索引，比如下面有一个排序的数组，如果要找出数字 3，最简单办法是遍历查询，时间复杂度是 O(n)，查询效率不高。在InnoDB存储引擎里，利用辅助索引查询，先通过辅助索引找到主键索引的键值，再通过主键值查出主键索引里面没有符合要求的数据，它比基于主键索引的查询多扫描了一棵索引树，这个过程就叫回表。二叉查找树：一个节点的左子树的所有节点都小于这个节点，右子树的所有节点都大于这个节点，这样我们在查询数据时，不需要计算中间节点的位置了，只需将查找的数据与节点的数据进行比较。
MYSQL索引失效
01-29 2153
博客主页：🏆看看是李XX还是李歘歘🏆 🌺每天不定期分享一些包括但不限于计算机基础、算法、后端开发相关的知识点，以及职场小菜鸡的生活。🌺 💗点关注不迷路，总有一些📖知识点📖是你想要的💗 ⛽️今天的内容是 MYSQL索引失效 ⛽️💻💻💻 1. MySQL 索引使用有哪些注意事项呢？ 哪些情况索引会失效，索引不适合哪些场景以及索引规则 索引哪些情况会失效 like通配符可能导致索引失效。 【当我们使用左或者左右模糊匹配的时候，也就是like %xx或者like %...
【学习】数据库：mysql查询条件有函数导致索引失效问题，建立函数索引
u010328311的博客
02-12 1971
mysql查询条件有函数导致索引失效问题，建立函数索引
mysql索引之三：索引使用注意规则（索引失效--存在索引但不使用索引）*
weixin_34415923的博客
03-05 768
使用索引时，有以下一些技巧和注意事项： (1)越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快。(2)简单的数据类型更好：整型数据比起字符，处理开销更小，因为字符串的比较更复杂。在MySQL中，应该用内置的日期和时间数据类型，而不是用字符串来存储时间；以及用整型数据类型存储IP地址。(3)尽量避免NULL：应该指定列为NOT NULL，除非你想存...
函数使得索引列失效
anjichan4261的博客
10-08 308
在索引列上使用函数使得索引失效的是常见的索引失效原因之一，因此尽可能的避免在索引列上使用函数。尽管可以使用基于函数的索引来 解决索引失效的问题，但如此一来带来的比如磁盘空间的占用以及列上过多的索引导致DML性能的下降。本文描述的是一个索引列上使用函数使 其失效的案例。 一、数据版本与原始语句及相关信息 1.版本信息 SQL> select * fr...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

程序员Forlan

你的鼓励将是我创作的最大动力！

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值