mysql如何让左模糊查询也能走索引

目录
  • 让左模糊查询也能走索引
    • 测试表USER_INFO表数据以及结构如下
    • 有一个USER_NAME字段的索引
  • 模糊查询(like、instr)
    • 1. like
    • 2. instr
    • 3. A>=’’ and A<’’
    • 3的补充讲解

让左模糊查询也能走索引

测试表USER_INFO表数据以及结构如下

有一个USER_NAME字段的索引

有个业务需求,需要模糊搜索出用户名后几位有杰这个词的所有用户信息,这时候不可能说为了一个搜索就引入ES,但是如果sql使用左模糊查询的话,根据索引的最左匹配原则,该sql语句是不可能使用到idx_user_name索引的,如下:

EXPLAIN SELECT * from USER_INFO where USER_NAME like '%杰'

执行计划如下:

可以发现是用不到索引的。

需要做模糊匹配,又要用到索引,索引的最左匹配原则更是不能被打破,这时候可以增加一个字段,这个字段的内容等于USER_NAME字段内容的反转,同时加上这个字段的相关索引,如下:

此时如果是要模糊搜索出用户名后几位有杰这个词的所有用户信息,可以对REVERSE_USER_NAME字段做右模糊查询,效果其实就是和对USER_NAME字段做左模糊查询是一样的,因为二者的内容是相反的,结果如下:

SELECT * from USER_INFO where REVERSE_USER_NAME like '杰%'

执行计划如下:

小结一下:索引的最左匹配原则不能打破,那么要让左匹配也走索引的话,换个思路,让右匹配的效果和左匹配一样就好了,同时右匹配又能走索引,间接达到了左模糊查询也能走索引的目的。

模糊查询(like、instr)

SQL中经常会遇到模糊查询,现在模糊查询正常、最常用的有两种,一种是like、另一种是instr,这两种单单是简单的搜索,instr的效率是比like要高的(这也得看%在哪儿了)。

1. like

like中分右模糊、左模糊,右模糊比如’abc%‘时,扫描索引,高效。当模糊查询含左模糊时,比如’%abc’,进行全表扫描,低效。当然更别提’%abc%'了。

2. instr

instr(字段名, string),instr的使用也很简单,就是填写一下字段名,然后与后面需要查找的内容相关。这个比like的左模糊效率要高,但是要比右模糊还是相差不多的(因为在instr不分左右模糊)。

3. A>=’’ and A<’’

今天在搜索查找之时还找到了这么一个查找方法,这个方法要比上面的instr效率还要高,不过这个方法局限性还是比较高的。例如:

select * from formtable_main_200_dt1 where hth >= '16040610' and hth < '16040611'

这个方法只适用于字符型字段,且除了我们想要的字段外,还得加上一个超过此类型的,并且他只支持右模糊。但是在这几个当中就右模糊而言,他的效率是最高的。

3的补充讲解

在数据中进行字符的比较时发现自己并不是对此了解,这里记下这里字符的比较是比较的哪里。

这里的比较是比较的ASCII,但这不是比较的总的ASCII,而是一个字符一个字符的比较,例如我这里有数据库的字段为’123456123’,而要比较的还有’123456223’,这里进行比较,当到了61的1和62的2时就可以比较出大小了。

以上为个人经验,希望能给大家一个参考,也希望大家多多支持我们。

时间: 2022-11-19

MySQL Like模糊查询速度太慢如何解决

问题:明明建立了索引,为何Like模糊查询速度还是特别慢? Like是否使用索引? 1.like %keyword 索引失效,使用全表扫描.但可以通过翻转函数+like前模糊查询+建立翻转函数索引=走翻转函数索引,不走全表扫描. 2.like keyword% 索引有效. 3.like %keyword% 索引失效,也无法使用反向索引. 使用mysql的explain简单测试如下: explain select * from company_info where cname like '%小%'

解决mysql模糊查询索引失效问题的几种方法

我们在使用like %通配符时常常会引起索引失效的问题. 这里,我们讨论一下like使用%的几种情况: 下列例子用到的索引(VC_STUDENT_NAME) 一.like 'xx%' EXPLAIN select * from t_student where VC_STUDENT_NAME like '王%' 我们发现使用%不放在开头的时候,索引是有效的 二.like '%xx' EXPLAIN select * from t_student where VC_STUDENT_NAME like

mysql中模糊查询的四种用法介绍

下面介绍mysql中模糊查询的四种用法: 1,%:表示任意0个或多个字符.可匹配任意类型和长度的字符,有些情况下若是中文,请使用两个百分号(%%)表示. 比如 SELECT * FROM [user] WHERE u_name LIKE '%三%' 将会把u_name为"张三","张猫三"."三脚猫","唐三藏"等等有"三"的记录全找出来. 另外,如果需要找出u_name中既有"三"又有

Mysql使用索引的正确方法及索引原理详解

一 .介绍 为何要有索引? 一般的应用系统,读写比例在10:1左右,而且插入操作和一般的更新操作很少出现性能问题,在生产环境中,我们遇到最多的,也是最容易出问题的,还是一些复杂的查询操作,因此对查询语句的优化显然是重中之重.说起加速查询,就不得不提到索引了. 什么是索引? 索引在MySQL中也叫做"键",是存储引擎用于快速找到记录的一种数据结构.索引对于良好的性能 非常关键,尤其是当表中的数据量越来越大时,索引对于性能的影响愈发重要. 索引优化应该是对查询性能优化最有效的手段了.索引能

MySQL索引背后的数据结构及算法原理详解

摘要 本文以MySQL数据库为研究对象,讨论与数据库索引相关的一些话题.特别需要说明的是,MySQL支持诸多存储引擎,而各种存储引擎对索引的支持也各不相同,因此MySQL数据库支持多种索引类型,如BTree索引,哈希索引,全文索引等等.为了避免混乱,本文将只关注于BTree索引,因为这是平常使用MySQL时主要打交道的索引,至于哈希索引和全文索引本文暂不讨论. 文章主要内容分为三个部分. 第一部分主要从数据结构及算法理论层面讨论MySQL数据库索引的数理基础. 第二部分结合MySQL数据库中My

Java方法反射实现原理详解

博主说:Java 反射机制是在运行状态中,对于任意一个类,都能够知道这个类的所有属性和方法:对于任意一个对象,都能够调用它的任意方法和属性:这种动态获取信息以及动态调用对象方法的功能称为 Java 语言的反射机制.在本文中,占小狼分析了 Java 反射机制的实现原理(源码),感兴趣的同学可以通过阅读本文花上几分钟了解了解. 正文 方法反射实例 public class ReflectCase { public static void main(String[] args) throws Exce

MySQL索引原理详解

目录 索引是什么 索引数据结构 树形索引 树的动画 为什么不是简单的二叉树? 为什么不是红黑树? 为什么最终选择B+树 而不是B树 水平方向可以存放更多的索引key 数据量估算 叶子节点包含所有的索引字段 叶子节点直接包含双向指针,范围查找效率高 Hash 索引 更快 不支持范围查询 hash 冲突问题 表引擎 MyISAM 和 InnoDB 引擎 MyISAM 引擎 InnoDB 表数据组织形式 聚集与非聚集索引 ★★★ 为什么建议InnoDB 表必须有主键,并且是整型自增的? 为什么是整型

mysql中的mvcc 原理详解

目录 简介 前言 一.mysql 数据写入磁盘流程 二.redo log 1.redolog 的整体流程 2.为什么需要 redo log 三.undo log 1.undo log 特点 2.undo log 类型 3.undo log 生成过程 4.undo log 回滚过程 5.undo log的删除 四.mvcc 1.什么是MVCC 2.MVCC组成 3.快照读与当前读 快照读 当前读 五.mvcc操作演示 1.READ COMMITTED 隔离级别 2.REPEATABLE READ 

MySQL prepare原理详解

Prepare的好处  Prepare SQL产生的原因.首先从mysql服务器执行sql的过程开始讲起,SQL执行过程包括以下阶段 词法分析->语法分析->语义分析->执行计划优化->执行.词法分析->语法分析这两个阶段我们称之为硬解析.词法分析识别sql中每个词,语法分析解析SQL语句是否符合sql语法,并得到一棵语法树(Lex).对于只是参数不同,其他均相同的sql,它们执行时间不同但硬解析的时间是相同的.而同一SQL随着查询数据的变化,多次查询执行时间可能不同,但硬解

浅谈MySQL中授权(grant)和撤销授权(revoke)用法详解

MySQL 赋予用户权限命令的简单格式可概括为: grant 权限 on 数据库对象 to 用户 一.grant 普通数据用户,查询.插入.更新.删除 数据库中所有表数据的权利 grant select on testdb.* to common_user@'%' grant insert on testdb.* to common_user@'%' grant update on testdb.* to common_user@'%' grant delete on testdb.* to c

MySQL Innodb 存储结构 和 存储Null值 用法详解

背景: 表空间:INNODB 所有数据都存在表空间当中(共享表空间),要是开启innodb_file_per_table,则每张表的数据会存到单独的一个表空间内(独享表空间). 独享表空间包括:数据,索引,插入缓存,数据字典.共享表空间包括:Undo信息(不会回收<物理空间上>),双写缓存信息,事务信息等. 段(segment):组成表空间,有区组成. 区(extent):有64个连续的页组成.每个页16K,总共1M.对于大的数据段,每次最后可申请4个区. 页(page):是INNODB 磁盘

Vue之vue.$set()方法源码案例详解

在使用vue开发项目的过程中,经常会遇到这样的问题:当vue的data里边声明或者已经赋值过的对象或者数组(数组里边的值是对象)时,向对象中添加新的属性,如果更新此属性的值,是不会更新视图的. 这是因为新加入的属性不是响应式的,因此不会触发视图的更新,通常使用静态方法Vue.set()或者实例方法this.$set()解决 ,使用方式: 对象:this.$set(target,key,  value) 数组:this.$set(target,index,  value) 但不管是静态方法Vue.

MySQL into_Mysql中replace与replace into用法案例详解

Mysql replace与replace into都是经常会用到的功能:replace其实是做了一次update操作,而不是先delete再insert:而replace into其实与insert into很相像,但对于replace into,假如表中的一个旧记录与一个用于PRIMARY KEY或一个UNIQUE索引的新记录具有相同的值,则在新记录被插入之前,旧记录被删除. replace是mysql 里面处理字符串比较常用的函数,可以替换字符串中的内容.类似的处理字符串的还有trim截取