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

    文章目录

    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;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    主要建立了主键索引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";

    • 1
    • 2
    • 3
    • 4
    • 5

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

    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)


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40

    下面我们逐个来分析下

    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  |
+----+-------------+--------------+------------+------+-------------------+-------------------+---------+-------------------+------+----------+-------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    可以看到，执行计划中的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 |
+----+-------------+--------------+------------+------+-------------------+-------------------+---------+-------------------+------+----------+-------------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    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 |
+----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    可以看到，执行计划中的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 |
+----+-------------+--------------+------------+-------+---------------+-------------------+---------+------+-------+----------+--------------------------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    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 |

    • 1
    • 2
    • 3
    • 4
    • 5

    可以看到，执行计划中的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 |
+----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    可以看到，执行计划中的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 |

    • 1
    • 2
    • 3
    • 4
    • 5

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

    4、总结

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

  • 相关阅读:
    Android相机调用-libusbCamera【外接摄像头】【USB摄像头】 【多摄像头预览】
    【SLAM论文笔记】PL-EVIO笔记（中）
    国稻种芯-中广香1号”新品配奇盛源液肽酶通过第一次验收
    Cookie的使用
    Python爬虫获取王者荣耀英雄全皮肤图片，并下载到本地
    运算符、流程控制
    java计算机毕业设计服装连锁店后台管理系统源码+mysql数据库+系统+lw文档+部署
    发票自动处理——摆脱纸张和数据输入的束缚，自动化工作流程和异常处理，大幅缩短审核准备时间
    [DIY]自己设计微软MakeCode街机，官方开源软硬件
    计算机网络的故事——HTTP首部
  • 原文地址：https://blog.csdn.net/qq_36433289/article/details/133743103