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

文章目录

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 |