MySQL Like模糊查询速度太慢如何进行优化_mysql like 慢_程序员小王java的博客

一、前言：

我建了一个《学生管理系统》，其中有一张 学生表 和四张表（ 小组表，班级表，标签表，城市表 ）进行联合的模糊查询，效率非常的低，就想了一下如何提高like模糊查询效率问题

注：看本篇博客之前请查看： Mysql中如何查看Sql语句的执行时间

二、第一个思路建索引

1、like %keyword 索引失效，使用全表扫描。

2、like keyword% 索引有效。

3、like %keyword% 索引失效,使用全表扫描。

使用explain测试了一下：

原始表（ 注：案例以学生表进行举例 ）

-- 用户表
create table t_users(
                        id int primary key auto_increment,
-- 用户名
                        username varchar(20),
-- 密码
                        password varchar(20),
-- 真实姓名
                        real_name varchar(50),
-- 性别 1表示男  0表示女
                        sex int,
-- 出生年月日
                        birth date,
-- 手机号
                        mobile varchar(11),
-- 上传后的头像路径
                        head_pic varchar(200)
#create index 索引名 on 表名(列名);                 
create index username on t_users(username);
like %keyword% 索引失效，使用全表扫描 
explain select id,username,password,real_name,sex,birth,mobile,head_pic 
 from t_users where username like '%h%';
like keyword% 索引有效。 
 explain select id,username,password,real_name,sex,birth,mobile,head_pic 
 from t_users where username like 'wh%';
like %keyword 索引失效，使用全表扫描。 
三、INSTR
 
这个我最开始都没听说过，今天查阅了一下资料，才知道有这个宝贝东西， 
instr(str,substr)：返回字符串str串中substr子串第一个出现的位置，没有找到字符串返回0，否则返回位置（从1开始） 
#instr(str,substr)方法
select id,username,password,real_name,sex,birth,mobile,head_pic 
      from t_users  
      where instr(username,'wh')>0 #0.00081900
#模糊查询
select id,username,password,real_name,sex,birth,mobile,head_pic 
        from t_users 
        where username like 'whj'; # 0.00094650
				问题：明明建立了索引，为何Like模糊查询速度还是特别慢？
Like是否使用索引?
　　1、like %keyword 索引失效，使用全表扫描。但可以通过翻转函数+like前模糊查询+建立翻转函数索引=走翻转函数索引，不走全表扫描。
　　2、like keyword% 索引有效。
　　3、like %keyword% 索引失效，也无法使用反向索引。
　　使用mysql的explain简单测试如下：
　　explain select * from company_info where cname like ‘%小%’
　　explain select * from company_inf
				REGEXP例1.查询字段中包含非英文的数据 代码如下: SELECT *FROM `m_user`WHERE `emp_no`REGEXP ‘[^ -~]’ =1 列2.这样能把所有不含英文的都搞出来 代码如下:SELECT *FROM tableWHERE nameNOT REGEXP ‘[a-zA-Z0-9]+’当然除了regexp之外还可以使用FIND_IN_SET,like来操作FIND_IN_SETmysql中如何使用FIND_IN_SET()，以及使用FIND_IN_SET()注意的地方，还有F第二世界整理发布IND_IN_SET()与in()的使用区别。在mysql中查询表字段
				%xxx%这种方式对于数据量少的时候，我们倒可以随意用，但是数据量大的时候，我们就体验到了查询性能的问题，像老化的车子艰难趴着坡一样，并且这种方式并未使用到索引，而是全表扫描
mysql高效模糊查询 代替like
而对于xxx% 或者%xxx方式，explain一下可以发现查询使用到了索引，性能提升了不少，当然这种方式不适用与所有的查询场景。
可以采取以下的函数进行查询。
LOCATE（'substr',str,pos）方法
POSITION('substr' IN field)方法
INSTR.
本文的测试数据是基于740w数据进行的。
查询开头是“今天不开心”的聊天记录，是可以走索引的。
select * from message_1 where content like "今天不开心%”;
查询包含“今天不开心”的聊天记录，是不能走索引的。
select * from message_1 where content like "%今天不开心%";
咱们主要优化的是第二种情况，我本人测试查询耗时是在9秒。
优化方案
对于查询包含某个关键词的需求，从业务上来说尽量避免。