背景：很多業(yè)務(wù)需求都需要模糊搜索功能诈豌，常見(jiàn)的有列表的文本框搜索组题。

????許多新手或是未主動(dòng)刷新自我認(rèn)知的老手在編寫(xiě)模糊搜索功能時(shí)容易攜帶感性思維橱脸，把需求一對(duì)础米，然后就是編碼了，使用測(cè)試環(huán)境隨意創(chuàng)建的一些數(shù)據(jù)來(lái)測(cè)試添诉，發(fā)現(xiàn)功能實(shí)現(xiàn)得很好呀屁桑，真的是這樣嗎？或許你已經(jīng)發(fā)現(xiàn)問(wèn)題了栏赴，前面提到的“隨意創(chuàng)建的一些數(shù)據(jù)”正是問(wèn)題的關(guān)鍵所在蘑斧，測(cè)試環(huán)境的數(shù)據(jù)具有隨意性、量級(jí)小等特點(diǎn)须眷，不容易暴露出問(wèn)題竖瘾，與生產(chǎn)環(huán)境的真實(shí)數(shù)據(jù)存在比較大的差異，如果你們的生產(chǎn)環(huán)境產(chǎn)出的數(shù)據(jù)量一直穩(wěn)定在一個(gè)小的數(shù)量級(jí)花颗，那么在未來(lái)很長(zhǎng)一段時(shí)間都很難暴露出問(wèn)題捕传。
????模糊搜索看起來(lái)是個(gè)比較簡(jiǎn)單的功能，對(duì)用戶來(lái)說(shuō)扩劝，只要關(guān)注使用就夠了庸论，對(duì)程序員來(lái)說(shuō)，要把它寫(xiě)好寫(xiě)妙卻絕非易事棒呛。那么把模糊搜索寫(xiě)好到底有什么意義呢葡公？在筆者看來(lái)，一是職業(yè)素養(yǎng)条霜，對(duì)技術(shù)的熱愛(ài)和極致的追求驅(qū)使我們不將就催什，其實(shí)這也完全看個(gè)人，因?yàn)椴⒎撬械念I(lǐng)導(dǎo)會(huì)關(guān)注你實(shí)現(xiàn)的細(xì)節(jié)宰睡，善于自己發(fā)現(xiàn)問(wèn)題可以走的更遠(yuǎn)蒲凶；二是性能，性能是一個(gè)十分復(fù)雜的話題拆内，在應(yīng)對(duì)不同的數(shù)據(jù)場(chǎng)景往往表現(xiàn)的性能也有所差異旋圆。大數(shù)據(jù)的環(huán)境下，一個(gè)功能將更加容易暴露出性能問(wèn)題麸恍。本篇文章將全面地解讀如何能更好地實(shí)現(xiàn)模糊搜索功能灵巧。

表搀矫、數(shù)據(jù)準(zhǔn)備

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 '真實(shí)姓名',
  `password` varchar(255) COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '密碼',
  `salt` varchar(255) COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '隨機(jī)鹽',
  `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 '鎖定狀態(tài)(0-正常,1-鎖定)',
  `del_state` tinyint(1) unsigned zerofill DEFAULT '0' COMMENT '刪除狀態(tài)(0-正常,1-已刪除)',
  `create_by` bigint(20) unsigned NOT NULL COMMENT '創(chuàng)建人',
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時(shí)間',
  `update_by` bigint(20) unsigned DEFAULT NULL COMMENT '更新人',
  `update_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時(shí)間',
  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 '內(nèi)容',
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時(shí)間',
  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】登錄成功');

業(yè)務(wù)一：?jiǎn)伪韱巫侄尾樵?/h1>

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，說(shuō)明“%xxx%”的寫(xiě)法會(huì)使索引失效敏弃。
使用右模糊匹配查詢卦羡，索引不變：

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瓶颠，只掃描了一行數(shù)據(jù)拟赊。
到這里，查詢用戶名的功能再難優(yōu)化下去粹淋，但是真實(shí)的業(yè)務(wù)需求往往不會(huì)這么簡(jiǎn)單要门。

業(yè)務(wù)二：?jiǎn)伪矶嘧侄尾樵?/h1>

使用or拼接多個(gè)查詢字段：

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

4.png

使用or關(guān)鍵字無(wú)法用到索引。
為每個(gè)查詢字段加索引廓啊，使用union拼接多個(gè)查詢：

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

????每個(gè)獨(dú)立的查詢都用到了索引欢搜，但因?yàn)閡nion關(guān)鍵字，所以會(huì)產(chǎn)生中間表谴轮。這種做法依然存在弊端炒瘟，如果我們查詢十幾個(gè)甚至是幾十個(gè)個(gè)字段呢，使用union編寫(xiě)的SQL語(yǔ)句會(huì)過(guò)于冗長(zhǎng)第步，可讀性低疮装。建立過(guò)多的索引需要占用更多的內(nèi)存，消耗更多的硬盤(pán)空間粘都，索引不是越多越好的廓推。

業(yè)務(wù)三：多表散列字段查詢

多表散列字段查表意味著一個(gè)以上的表都包含查詢字段，這樣一來(lái)我們就不能再使用union了翩隧，會(huì)造成結(jié)果錯(cuò)誤樊展。

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

????了解前兩種業(yè)務(wù)后，我們不需要使用執(zhí)行計(jì)劃也可以知道結(jié)果了堆生，這顯然對(duì)于性能提升來(lái)說(shuō)是不友好的专缠。
????既然無(wú)法只用SQL語(yǔ)言就能寫(xiě)好模糊搜索，我們?yōu)楹尾粐L試在程序里解決呢淑仆？將搜索工作交給代碼去處理涝婉，不要將思維限制在SQL語(yǔ)言中，可能你花費(fèi)了大量時(shí)間去優(yōu)化SQL蔗怠，結(jié)果還是不盡人意墩弯，既浪費(fèi)了時(shí)間又可能耽誤團(tuán)隊(duì)開(kāi)發(fā)進(jìn)度吩跋。處理步驟如下：

第一步、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

第一個(gè)表做了全表掃描渔工，第二個(gè)表使用到了主鍵索引锌钮。

第二步、程序處理

場(chǎng)景一涨缚、查詢列表

/**
  * 查詢?nèi)罩玖斜?  * 
  * @param searchText 查詢文本
  * @return
  */
@GetMapping("logs")
public JsonResult getLogsList(@RequestParam(value = "searchText", require = false) String searchText) {
    // 查詢?nèi)罩玖斜?    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);
}

場(chǎng)景二、分頁(yè)查詢

/**
  * 分頁(yè)查詢?nèi)罩?  *
  * @param page       分頁(yè)參數(shù)集
  * @param searchText 查詢文本
  * @return
  */
@GetMapping("logs")
public PageResult<LogVO> getLogPage(Page page, @RequestParam(value = "searchText", require = false) String searchText) {
    // 分頁(yè)查詢?nèi)罩?    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)) {
            // 通過(guò)分頁(yè)參數(shù)截取列表
            records = MyPageUtil.subListByPage(page.getCurrent(), page.getSize(), records);
        }
    }

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

MyPageUtil(自定義分頁(yè)工具類)

public class MyPageUtil {
    /**
     * 通過(guò)分頁(yè)參數(shù)集截取數(shù)據(jù)列表
     *
     * @param current  當(dāng)前頁(yè)
     * @param size     每頁(yè)記錄數(shù)
     * @param dataList 數(shù)據(jù)列表
     * @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);
    }
}

這樣一來(lái)策治，雖然需要在程序中編寫(xiě)額外的邏輯代碼塊脓魏，但是對(duì)于SQL優(yōu)化的幫助其實(shí)是巨大的。

注：本文的實(shí)驗(yàn)基于MySql數(shù)據(jù)庫(kù)通惫，版本是5.6.16茂翔。程序使用Java語(yǔ)言編寫(xiě)，基于JDK1.8
附框架清單：