Mysql使用函数后不走索引怎么优化？

￡神魔★判官ぃ 2024-03-22 10:26 54阅读 0赞

网上很多人说mysql一旦使用函数就不走函数，但是事实真的是如此吗？我先说明，并不是如此的，本篇文章会通过`DAYOFWEEK()`和`substr()`两个函数作为条件查询，看看究竟是否会走索引（其他函数同理），使用函数不走索引的时候又应该如何做sql优化，本篇文章重点是基于这两点进行分析。

#### 目录 ####

*   *  一、什么场景下使用函数索引会失效？
     *  二、索引失效了应该怎么处理？
     *   *  1.通过【sql优化】让索引生效
         *  2.通过【虚拟列】让索引生效
     *  三、总结

### 一、什么场景下使用函数索引会失效？ ###

**测试数据如下：**

> create\_time和name列是都建立了索引的。

DROP TABLE IF EXISTS `demo`;
    CREATE TABLE `demo`  (
      `id` int NOT NULL AUTO_INCREMENT,
      `create_time` datetime NULL DEFAULT NULL,
      `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE,
      INDEX `create_time`(`create_time`) USING BTREE,
      INDEX `name`(`name`) USING BTREE
    ) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = DYNAMIC;
    
    
    INSERT INTO `demo` VALUES (1, '2023-04-28 10:41:16', 'zhangsan');
    INSERT INTO `demo` VALUES (2, '2023-04-01 10:41:22', 'lisi');

> `DAYOFWEEK()`：函数返回日期的工作日索引值，即星期日为1，星期一为2，星期六为7。 这些索引值对应于ODBC标准。

通过下面会发现一个问题，假如是`select *` 的情况下是不会走索引的，假如是只返回使用函数的列是会走索引的。

EXPLAIN SELECT * from  demo WHERE dayofweek(create_time) = 6 \G;
    
    EXPLAIN SELECT dayofweek(create_time),create_time from  demo WHERE dayofweek(create_time) = 6 \G;

![在这里插入图片描述][f6d798f3e4324a048b53808c4b379f29.png]

关于执行计划的解读：

![在这里插入图片描述][3890493ae9ec4754b7a4755f4a5328ce.png]

截取字符串语法：`substr(obj，start，length)`

参数：

*  obj：从哪个内容中截取，可以是数值或字符串。
 *  start：从哪个字符开始截取（1开始，而不是0开始）
 *  length：截取几个字符（空格也算一个字符）。

通过下面案例会发现，跟上面的案例是一样的，同样是`select *` 的情况下是不会走索引的。

EXPLAIN SELECT *  from demo WHERE substr(name,1,3) = 'lis'\G;
    
    EXPLAIN SELECT substr(name,1,3),name,id  from demo WHERE substr(name,1,3) = 'lis'\G;

![在这里插入图片描述][77a7306837f1423dbae9e5d223102186.png]

### 二、索引失效了应该怎么处理？ ###

#### 1.通过【sql优化】让索引生效 ####

那么问题来了遇到这种查询所有数据使用函数不走索引的我们应该如何优化。通过以下试验发现可以携带id，id是主键的情况下不会导致索引失效！

EXPLAIN SELECT substr(name,1,3),name,id,create_time  from demo WHERE substr(name,1,3) = 'lis'\G;
    
    EXPLAIN SELECT substr(name,1,3),name,id  from demo WHERE substr(name,1,3) = 'lis'\G;

> 通过以下试验得出结论，假如使用函数作为条件查询，只能返回条件的那一列跟id主键列，一旦返回其他的列就会索引失效！

![在这里插入图片描述][adf4b2489a324147bbfb6500935fae2a.png]

由此优化方案便出来了，假设我们要查name列当中前三个字母是lis的全行数据，然后我们想让他使用到索引，可以使用嵌套查询的方案：

> 这里进行提示以下：MySQL的`IN`运算符可以使用索引，但是，有一点需要注意。如果你的IN子句中包含的值很多，那么MySQL可能会选择不使用索引，因为扫描大量的值可能比使用索引更快。这个阈值通常是1000个值，但这个值是可配置的。表内数据太少使用`IN`也不会使用索引！

EXPLAIN SELECT * FROM demo WHERE id in (SELECT id  from demo WHERE substr(name,1,3) = 'lis') \G;

> 如下案例显示实际上并未使用到索引

![在这里插入图片描述][9081805965e7464d9b58d2cdae1f355f.png]  
上面测试的表当中就两条数据所以显示的in并没有使用索引，如下表内共有一万条数据，然后对主键使用in查询，可以很明显的看到，是使用了索引的。由此可证明in是会使用索引的，只不过mysql会根据权衡利弊到底使用索引快还是不使用索引快。

![在这里插入图片描述][2fa58cde01254783b268a96e14ac5f30.png]

#### 2.通过【虚拟列】让索引生效 ####

Mysql 5.7 中推出了一个非常实用的功能 虚拟列 Generated (Virtual) Columns

*  InnoDB支持在虚拟生成的列上建立二级索引。不支持其他索引类型（主键索引）。在虚拟列上定义的二级索引有时也称为“`虚拟索引`”。
 *  二级索引可以在一个或多个虚拟列上创建，也可以在虚拟列与常规列或存储生成列的组合上创建。包含虚拟列的二级索引可以定义为`UNIQUE`。
 *  当在虚拟列上使用辅助索引时，由于在`INSERT和UPDATE`操作期间在辅助索引（辅助又叫二级索引）记录中实现虚拟列值时执行计算，因此需要考虑额外的写成本。即使有额外的写成本，虚拟列上的二级索引也可能比生成的存储列更可取，生成的存储列在集群索引中具体化，从而导致需要更多磁盘空间和内存的更大的表。如果没有在虚拟列上定义二级索引，则会产生额外的读取成本，因为每次检查列的行时都必须计算虚拟列值。

语法：`ALTER TABLE 表名称 add column 虚拟列名称 虚拟列类型 GENERATED ALWAYS as (表达式) [VIRTUAL | STORED];`

**MySQL 在处理 虚拟列存储问题的时候有两种方式：**

*  VIRTUAL（默认）：不存储列值，在读取表的时候自动计算并返回，不消耗任何存储，这种存储方式仅 InnoDB 支持设置索引。
 *  STORED：在插入或更新时计算存储列值，存储的虚拟列需要存储空间，并且 MyISAM 也可以设置索引。

![在这里插入图片描述][90ed997dc17e462c86eaa9aa4afd7ce8.png]

下面我们基于`substr(name,1,3)`函数来创建一个虚拟列：

ALTER TABLE demo add column virtual_name VARCHAR(5) GENERATED ALWAYS as (substr(name,1,3)) VIRTUAL;

对虚拟列添加索引：

ALTER TABLE `demo`.`demo` 
    ADD INDEX `virtual_name`(`virtual_name`) USING BTREE;

这时候就可以直接通过虚拟列来完成查询操作了

EXPLAIN SELECT *  from demo WHERE virtual_name = 'lis';

![在这里插入图片描述][ad8e9c99a915421a9374c6fa846eda5f.png]

### 三、总结 ###

假如使用函数作为条件查询，只能返回条件的那一列跟id主键列，一旦返回其他的列就会索引失效！针对于使用函数索引失效问题，可以使用嵌套查询来解决，也可以使用虚拟列来解决！

[f6d798f3e4324a048b53808c4b379f29.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/0695ffc4a5bc44909ebfb14b7d4b5b9a.png
[3890493ae9ec4754b7a4755f4a5328ce.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/f793d81acbff47e68b4b173961924aa5.png
[77a7306837f1423dbae9e5d223102186.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/cd01b3320d11473b8d80ddc52e376cd6.png
[adf4b2489a324147bbfb6500935fae2a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/a7793166d4d64694aca6721b4bcc25ef.png
[9081805965e7464d9b58d2cdae1f355f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/2a56b2747835414a81fc78cebae31135.png
[2fa58cde01254783b268a96e14ac5f30.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/c4393bdf4cef46bf94e80b6f58b28d68.png
[90ed997dc17e462c86eaa9aa4afd7ce8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/65b497e679d74f55bfe2fc7c219d3bfd.png
[ad8e9c99a915421a9374c6fa846eda5f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/4cb5f2c22c58451799c105036f5a3c32.png