背景：很多业务需求都需要模糊搜索功能，常见的有列表的文本框搜索。

    许多新手或是未主动刷新自我认知的老手在编写模糊搜索功能时容易携带感性思维，把需求一对，然后就是编码了，使用测试环境随意创建的一些数据来测试，发现功能实现得很好呀，真的是这样吗？或许你已经发现问题了，前面提到的“随意创建的一些数据”正是问题的关键所在，测试环境的数据具有随意性、量级小等特点，不容易暴露出问题，与生产环境的真实数据存在比较大的差异，如果你们的生产环境产出的数据量一直稳定在一个小的数量级，那么在未来很长一段时间都很难暴露出问题。
    模糊搜索看起来是个比较简单的功能，对用户来说，只要关注使用就够了，对程序员来说，要把它写好写妙却绝非易事。那么把模糊搜索写好到底有什么意义呢？在笔者看来，一是职业素养，对技术的热爱和极致的追求驱使我们不将就，其实这也完全看个人，因为并非所有的领导会关注你实现的细节，善于自己发现问题可以走的更远；二是性能，性能是一个十分复杂的话题，在应对不同的数据场景往往表现的性能也有所差异。大数据的环境下，一个功能将更加容易暴露出性能问题。本篇文章将全面地解读如何能更好地实现模糊搜索功能。

表、数据准备

CREATE TABLE `sys_user` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `username` varchar(64) COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '用户名',
  `realname` varchar(64) COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '真实姓名',
  `password` varchar(255) COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '密码',
  `salt` varchar(255) COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '随机盐',
  `email` varchar(45) COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '电子邮件',
  `phone` varchar(20) COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '电话',
  `lock_state` tinyint(1) unsigned zerofill DEFAULT '0' COMMENT '锁定状态(0-正常,1-锁定)',
  `del_state` tinyint(1) unsigned zerofill DEFAULT '0' COMMENT '删除状态(0-正常,1-已删除)',
  `create_by` bigint(20) unsigned NOT NULL COMMENT '创建人',
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` bigint(20) unsigned DEFAULT NULL COMMENT '更新人',
  `update_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC COMMENT='用户表';

CREATE TABLE `sys_log` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` bigint(20) unsigned NOT NULL COMMENT '所属用户ID',
  `type` tinyint(1) unsigned zerofill NOT NULL COMMENT '类型(1-登录日志,2-操作日志)',
  `content` varchar(255) COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '内容',
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC COMMENT='日志表';

INSERT INTO `sys_user` (`username`, `realname`, `password`, `salt`, `email`, `phone`, `lock_state`, `del_state`, `create_by`, `create_time`, `update_by`, `update_time`) VALUES ('admin', '张三', 'd80cad79777159cddb847759881b8cb9', 'FRFZ3y63', '', '', '0', '0', '1', '2020-01-01 00:00:00', NULL, NULL);
INSERT INTO `sys_user` (`username`, `realname`, `password`, `salt`, `email`, `phone`, `lock_state`, `del_state`, `create_by`, `create_time`, `update_by`, `update_time`) VALUES ('admin1', '李四', '594727eda22074a7a5d53650a88c0446', 'jDq57dif', '', '', '0', '0', '1', '2020-01-01 00:00:00', NULL, NULL);
INSERT INTO `sys_user` (`username`, `realname`, `password`, `salt`, `email`, `phone`, `lock_state`, `del_state`, `create_by`, `create_time`, `update_by`, `update_time`) VALUES ('admin2', '王五', '3f10f0f132650cfd03ad52f5b4bed12d', 'v653euxV', '', '', '0', '0', '1', '2020-01-01 00:00:00', NULL, NULL);

INSERT INTO `sys_log` (`user_id`, `type`, `content`) VALUES ('1', '1','用户【admin1】登录成功');
INSERT INTO `sys_log` (`user_id`, `type`, `content`) VALUES ('2', '1','用户【admin2】登录成功');
INSERT INTO `sys_log` (`user_id`, `type`, `content`) VALUES ('3', '1','用户【admin3】登录成功');

业务一：单表单字段查询

explain select * from sys_user where del_state = 0 and username like '%admin1%' limit 0,10

1.png

type是ALL，全表扫描，性能低。
加入索引：

alter table sys_user add index idx_username(`username`);
explain select * from sys_user where del_state = 0 and username like '%admin1%' limit 0,10

2.png

type还是ALL，说明“%xxx%”的写法会使索引失效。
使用右模糊匹配查询，索引不变：

explain select * from sys_user where del_state = 0 and username like 'admin1%' limit 0,10

3.png

type是range，查询性能明显提升。key是idx_username，用到了索引。rows为1，只扫描了一行数据。
到这里，查询用户名的功能再难优化下去，但是真实的业务需求往往不会这么简单。

业务二：单表多字段查询

使用or拼接多个查询字段：

explain select * from sys_user where del_state = 0 and (username like 'admin1%' or realname like 'admin1%') limit 0,10;

4.png

使用or关键字无法用到索引。
为每个查询字段加索引，使用union拼接多个查询：

alter table sys_user add index idx_realname(`realname`);
explain 
select * from sys_user where del_state = 0 and username like 'admin1%'
union
select * from sys_user where del_state = 0 and realname like 'admin1%'
limit 0,10;

5.png

    每个独立的查询都用到了索引，但因为union关键字，所以会产生中间表。这种做法依然存在弊端，如果我们查询十几个甚至是几十个个字段呢，使用union编写的SQL语句会过于冗长，可读性低。建立过多的索引需要占用更多的内存，消耗更多的硬盘空间，索引不是越多越好的。

业务三：多表散列字段查询

多表散列字段查表意味着一个以上的表都包含查询字段，这样一来我们就不能再使用union了，会造成结果错误。

select
sl.id,
sl.content,
sl.create_time,
su.username
from sys_log sl left join sys_user su on sl.user_id = su.id  
where sl.content like 'admin1%' or su.username like 'admin1%'
limit 0,10

    了解前两种业务后，我们不需要使用执行计划也可以知道结果了，这显然对于性能提升来说是不友好的。
    既然无法只用SQL语言就能写好模糊搜索，我们为何不尝试在程序里解决呢？将搜索工作交给代码去处理，不要将思维限制在SQL语言中，可能你花费了大量时间去优化SQL，结果还是不尽人意，既浪费了时间又可能耽误团队开发进度。处理步骤如下：

第一步、SQL中忽视掉文本查询

select
sl.id,
sl.content,
sl.create_time,
su.username
from sys_log sl 
  left join sys_user su on sl.user_id = su.id  

6.png

第一个表做了全表扫描，第二个表使用到了主键索引。

第二步、程序处理

场景一、查询列表

/**
  * 查询日志列表
  * 
  * @param searchText 查询文本
  * @return
  */
@GetMapping("logs")
public JsonResult getLogsList(@RequestParam(value = "searchText", require = false) String searchText) {
    // 查询日志列表
    List<LogVO> logVos = logService.listLogs();
  
    // 如果列表为空则返回空列表
    if (CollectionUtil.isEmpty(logVos)) {
        return JsonResult.success(CollectionUtil.newArrayList());
    }
  
    // 文本搜索
    if (StrUtil.isNotBlank(searchText)) {
        logVos = logVos.stream()
            .filter(i -> i.getUsername().contains(searchText) || i.getRealname().contains(searchText))
            .collect(Collectors.toList());
    }

    return JsonResult.success(logVos);
}

场景二、分页查询

/**
  * 分页查询日志
  *
  * @param page       分页参数集
  * @param searchText 查询文本
  * @return
  */
@GetMapping("logs")
public PageResult<LogVO> getLogPage(Page page, @RequestParam(value = "searchText", require = false) String searchText) {
    // 分页查询日志
    Ipage iPage = logService.getLogPage(page);
    long total = iPage.getTotal();
    List<LogVO> records = iPage.getRecords();

    // 文本搜索
    if (StrUtil.isNotBlank(searchText)) {
        // 查询所有的日志
        List<LogVO> completeLogVos = logService.listLogs();

        records = completeLogVos.stream()
                .filter(i -> i.getUsername().contains(searchText) || i.getRealname().contains(searchText))
                .collect(Collectors.toList());
        total = records.size();

        if (CollectionUtil.isNotEmpty(records)) {
            // 通过分页参数截取列表
            records = MyPageUtil.subListByPage(page.getCurrent(), page.getSize(), records);
        }
    }

    return new PageResult<>(total, records);
}

MyPageUtil(自定义分页工具类)

public class MyPageUtil {
    /**
     * 通过分页参数集截取数据列表
     *
     * @param current  当前页
     * @param size     每页记录数
     * @param dataList 数据列表
     * @param <T>
     * @return
     */
    public static <T> List<T> subListByPage(long current, long size, List<T> dataList) {
        int total = dataList.size();

        int fromIndex = (int) ((current - 1) * size);
        int initToIndex = (int) (fromIndex + size);
        int toIndex = initToIndex > total ? total : initToIndex;

        return dataList.subList(fromIndex, toIndex);
    }
}

这样一来，虽然需要在程序中编写额外的逻辑代码块，但是对于SQL优化的帮助其实是巨大的。

注：本文的实验基于MySql数据库，版本是5.6.16。程序使用Java语言编写，基于JDK1.8
附框架清单：