sql 中使用like%、函数导致索引失效的解决方案

2024-09-06 18:20

本文主要是介绍sql 中使用like%、函数导致索引失效的解决方案,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

SELECTp1.*FROM cdr_voice_202407_0 AS p1 
WHERELENGTH( p1.calling_number ) < 11 AND p1.calling_number LIKE '%10086%'

上边的sql中如果 calling_number  是索引  会导致索引失效

涉及的 WHERE 子句有两个条件:

  1. LENGTH(p1.calling_number) < 11:这是对字符串长度的判断。
  2. p1.calling_number LIKE '%10086%':这是一个通配符匹配,且通配符 % 位于前后(全局匹配)。

对于这两个条件,如果有索引,它们的表现如下:

1. LENGTH(p1.calling_number) < 11

  • 索引失效:一般来说,使用函数(如 LENGTH)会导致索引失效,因为数据库无法利用常规的 B-tree 索引或其他索引类型。
  • 解决办法:可以考虑创建一个虚拟列生成列,然后在该列上创建索引。例如,创建一个虚拟列存储 calling_number 的长度,并在该列上创建索引。
ALTER TABLE cdr_voice_202409_0
ADD calling_number_len INT AS (LENGTH(calling_number)) VIRTUAL;

使用生成列(computed column),这些列可以基于已有的列进行计算 

MySQL 中的虚拟列

在 MySQL 中,你可以使用生成列(generated column)来创建虚拟列,生成列可以是存储的(即在物理上存储在磁盘中)或虚拟的(即动态计算的)。

GENERATED 列(虚拟列)是从 MySQL 5.7 及以上版本支持的。如果你使用的 MySQL 版本低于 5.7,将不支持这个功能。请先确认你的 MySQL 版本 

这样,在查询 LENGTH(calling_number) 时,数据库可以使用索引。

2. p1.calling_number LIKE '%10086%'

  • 索引失效:因为 % 放在了字符串的开头,常规的 B-tree 索引无法被有效使用。B-tree 索引是顺序索引,只有在字符串开头匹配时(例如 LIKE '10086%')才能利用索引。
  • 解决办法:
    • 全文索引(Full-Text Index):如果数据库支持全文索引(Oracle 使用 Text 索引,MySQL 支持 FULLTEXT 索引),你可以使用它来加速这种模式匹配。

    • ALTER TABLE cdr_voice_202409_0
      ADD FULLTEXT INDEX idx_calling_number (calling_number);
  • FULLTEXT:指定为全文索引类型。
  • idx_calling_number:是索引的名称,可以自行命名。
  • calling_number:是需要加全文索引的列。

 

总结

  • LIKE '%10086%' 更灵活,但对大数据集性能较差,因为它通常会导致全表扫描,无法利用索引。
  • MATCH ... AGAINST('10086') 依赖于全文索引,性能更高,但需要先为目标字段创建全文索引,适用于较大文本的关键词搜索。
  • MATCH(calling_number):表示要搜索的列。
  • AGAINST('10086'):表示要搜索的关键字。

优化后的sql为 

SELECT p1.*
FROM cdr_voice_202407_0 AS p1
WHERE LENGTH(p1.calling_number) < 11 AND p1.calling_number LIKE '%10086%'

 

 

这篇关于sql 中使用like%、函数导致索引失效的解决方案的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/1142773

相关文章

MySQL大表数据的分区与分库分表的实现

《MySQL大表数据的分区与分库分表的实现》数据库的分区和分库分表是两种常用的技术方案,本文主要介绍了MySQL大表数据的分区与分库分表的实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有... 目录1. mysql大表数据的分区1.1 什么是分区?1.2 分区的类型1.3 分区的优点1.4 分

MySQL错误代码2058和2059的解决办法

《MySQL错误代码2058和2059的解决办法》:本文主要介绍MySQL错误代码2058和2059的解决办法,2058和2059的错误码核心都是你用的客户端工具和mysql版本的密码插件不匹配,... 目录1. 前置理解2.报错现象3.解决办法(敲重点!!!)1. php前置理解2058和2059的错误

Mysql删除几亿条数据表中的部分数据的方法实现

《Mysql删除几亿条数据表中的部分数据的方法实现》在MySQL中删除一个大表中的数据时,需要特别注意操作的性能和对系统的影响,本文主要介绍了Mysql删除几亿条数据表中的部分数据的方法实现,具有一定... 目录1、需求2、方案1. 使用 DELETE 语句分批删除2. 使用 INPLACE ALTER T

Spring Boot3虚拟线程的使用步骤详解

《SpringBoot3虚拟线程的使用步骤详解》虚拟线程是Java19中引入的一个新特性,旨在通过简化线程管理来提升应用程序的并发性能,:本文主要介绍SpringBoot3虚拟线程的使用步骤,... 目录问题根源分析解决方案验证验证实验实验1:未启用keep-alive实验2:启用keep-alive扩展建

MySQL INSERT语句实现当记录不存在时插入的几种方法

《MySQLINSERT语句实现当记录不存在时插入的几种方法》MySQL的INSERT语句是用于向数据库表中插入新记录的关键命令,下面:本文主要介绍MySQLINSERT语句实现当记录不存在时... 目录使用 INSERT IGNORE使用 ON DUPLICATE KEY UPDATE使用 REPLACE

MySQL Workbench 安装教程(保姆级)

《MySQLWorkbench安装教程(保姆级)》MySQLWorkbench是一款强大的数据库设计和管理工具,本文主要介绍了MySQLWorkbench安装教程,文中通过图文介绍的非常详细,对大... 目录前言:详细步骤:一、检查安装的数据库版本二、在官网下载对应的mysql Workbench版本,要是

mysql数据库重置表主键id的实现

《mysql数据库重置表主键id的实现》在我们的开发过程中,难免在做测试的时候会生成一些杂乱无章的SQL主键数据,本文主要介绍了mysql数据库重置表主键id的实现,具有一定的参考价值,感兴趣的可以了... 目录关键语法演示案例在我们的开发过程中,难免在做测试的时候会生成一些杂乱无章的SQL主键数据,当我们

使用Java实现通用树形结构构建工具类

《使用Java实现通用树形结构构建工具类》这篇文章主要为大家详细介绍了如何使用Java实现通用树形结构构建工具类,文中的示例代码讲解详细,感兴趣的小伙伴可以跟随小编一起学习一下... 目录完整代码一、设计思想与核心功能二、核心实现原理1. 数据结构准备阶段2. 循环依赖检测算法3. 树形结构构建4. 搜索子

找不到Anaconda prompt终端的原因分析及解决方案

《找不到Anacondaprompt终端的原因分析及解决方案》因为anaconda还没有初始化,在安装anaconda的过程中,有一行是否要添加anaconda到菜单目录中,由于没有勾选,导致没有菜... 目录问题原因问http://www.chinasem.cn题解决安装了 Anaconda 却找不到 An

Spring定时任务只执行一次的原因分析与解决方案

《Spring定时任务只执行一次的原因分析与解决方案》在使用Spring的@Scheduled定时任务时,你是否遇到过任务只执行一次,后续不再触发的情况?这种情况可能由多种原因导致,如未启用调度、线程... 目录1. 问题背景2. Spring定时任务的基本用法3. 为什么定时任务只执行一次?3.1 未启用