专栏首页andychaiMySQL模糊查询性能优化
原创

MySQL模糊查询性能优化

结论写在最前面

  • 用户基数估计
  • 模糊查找接口qps估计
  • 数据检索量估计
  • 支持分布式搜索
  • 支持短语搜索
  • 支持分词

上述每一项都将是决定我们模糊查询最终的实现方案

业务场景分析

根据 模糊查找 的业务场景,比对一下上面列出的6种条件,如果你的场景是全都要支持,并且是 大用户量接口qps高海量的数据检索量,那就不要在数据库上做任何挣扎了,你需要的是一个 全文检索引擎。可以直接看文章最后面~

如果 用户量接口qps 都不高的话,像我的情况一样,是给公司做一个内部OA类系统的话,那么我们完全可以在 MySQL的 FULLTEXT INDEX 全文索引上下功夫,避免出现 大炮打蚊子 的情况。

业务背景

我们团队接到一个IEG市场部的一个内部系统开发,系统内填写工单时需要根据 rtx拼音中文名 模糊匹配用户,没错!就是KM和TAPD那种效果:

1526286645_33_w419_h273.png

首先KM和TAPD都是通过后台给前端生成一个js文件,由前端去做模糊查找,这样比较经济实惠,没有后台查询损耗,定期更新js文件即可。但是我们的业务场景有通过 企业服务号 使用的需求,虽然我们已接入了 移动网关 但毕竟在外网可访问的前提下,前端能够获取所有员工信息太危险了,最后决定用接口实现。

Staffs表结构&查询接口

将员工数据导入到MySQL之后,共有 59066 条数据,大家重点关注 rtxpinyinchn_name 这三个字段,其他都是辅助字段,不参与查询业务。

mysql> select * from staffs limit 5;
+----+----------+--------------+----------+------------+---------------------+
| id | rtx      | pinyin       | chn_name | searchable | updated_at          |
+----+----------+--------------+----------+------------+---------------------+
|  1 | 5001     | TAPDkefu     | TAPD??   |          1 | 2018-05-11 13:32:44 |
|  2 | aachen   | chenfangying | ???      |          1 | 2018-05-11 13:32:44 |
|  3 | aalizzlu | lujianbin    | ???      |          1 | 2018-05-11 13:32:44 |
|  4 | aamli    | lixinyun     | ???      |          1 | 2018-05-11 13:32:44 |
|  5 | aaqin    | qinjian      | ??       |          1 | 2018-05-11 13:32:44 |
+----+----------+--------------+----------+------------+---------------------+
5 rows in set (0.00 sec)

接口设计

1526286695_53_w1407_h621.png

成功示例:

{
    "ret": 0,
    "msg": "ok",
    "result": [
        {
            "rtx": "andychai",
            "pinyin": "chaiyanlin",
            "chn_name": "柴延林"
        },
        {
            "rtx": "andyche",
            "pinyin": "chemaocheng",
            "chn_name": "车懋成"
        },
        {
            "rtx": "candychcao",
            "pinyin": "caohui",
            "chn_name": "曹慧"
        },
        {
            "rtx": "mandycheng",
            "pinyin": "chengwen",
            "chn_name": "程雯"
        },
        {
            "rtx": "nandychen",
            "pinyin": "chennan",
            "chn_name": "陈楠"
        }
    ]
}

简单粗暴一把搜(LIKE %%)

当用户请求:

GET /staffs?q=andy

后台执行sql:

select * from staffs where rtx like '%andy%' or pinyin like '%andy%' or chn_name like '%andy%' limit 5;

别笑!真的别笑!你以为很慢吗?

mysql> select * from staffs where rtx like '%andy%' or pinyin like '%andy%' or chn_name like '%andy%' limit 5;
+------+-------------+-----------+----------+------------+---------------------+
| id   | rtx         | pinyin    | chn_name | searchable | updated_at          |
+------+-------------+-----------+----------+------------+---------------------+
|  214 | acandysui   | suiyanli  | ???      |          1 | 2018-05-11 13:32:44 |
|  871 | alee        | AndyLee   | AndyLee  |          1 | 2018-05-11 13:32:44 |
| 1895 | andyabhuang | huanganbu | ???      |          1 | 2018-05-11 13:32:44 |
| 1896 | andyao      | aonaiyong | ???      |          1 | 2018-05-11 13:32:44 |
| 1897 | andyawang   | wangang   | ??       |          1 | 2018-05-11 13:32:44 |
+------+-------------+-----------+----------+------------+---------------------+
5 rows in set (0.01 sec)

没错用了LIKE,而且是%%,这种查询只能遍历全表:

explain select * from staffs where rtx like '%andy%' or pinyin like '%andy%' or chn_name like '%andy%' limit 5;
+----+-------------+--------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | staffs | ALL  | NULL          | NULL | NULL    | NULL | 59091 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+-------+-------------+

数据库设计准则里,几乎都在不厌其烦的强调不要用LIKE!那么你能怎么办呢?我总不能为了这去去6万不到数据去弄个搜索引擎进来吧?这就是传说中的大炮打蚊子啊!

MySQL全文索引

首先检查你用的MySQL的版本,最好是5.6+。因为InnoDB引擎对FULLTEXT索引的支持是MySQL5.6新引入的特性,之前只有MyISAM引擎支持FULLTEXT索引。

MySQL支持三种模式的全文检索模式:自然语言模式(IN NATURAL LANGUAGE MODE),即通过MATCH AGAINST 传递某个特定的字符串来进行检索。 布尔模式(IN BOOLEAN MODE),可以为检索的字符串增加操作符,例如“+”表示必须包含,“-”表示不包含,“*”表示通配符(这种情况, 即使传递的字符串较小或出现在停词中,也不会被过滤掉),其他还有很多特殊的布尔操作符,可以通过如下参数控制: 查询扩展模式(WITH QUERY EXPANSION), 这种模式是自然语言模式下的一个变种,会执行两次检索,第一次使用给定的短语进行检索,第二次是结合第一次相关性比较高的行进行检索。

但是!对于中文的分词问题,依然没有解决,英文的分词是能够通过空格去区分,而中文名是没空格,所以全文索引只能帮助我们加快rtx和pinyin的查询,中文名依旧无奈

建立全文索引

现在我们来看看加上全文索引之后的表结构:

CREATE TABLE IF NOT EXISTS `ieg_briefs`.`staffs` (
  `id` BIGINT(13) NOT NULL AUTO_INCREMENT,
  `rtx` VARCHAR(45) NOT NULL COMMENT '员工rtx',
  `pinyin` VARCHAR(45) NOT NULL COMMENT '员工姓名拼音',
  `chn_name` VARCHAR(45) NOT NULL COMMENT '员工中文名',
  `searchable` TINYINT(3) NOT NULL DEFAULT 1 COMMENT '是否可检索:1可以,0不可以',
  `updated_at` TIMESTAMP NOT NULL COMMENT '数据更新时间',
  PRIMARY KEY (`id`),
  UNIQUE INDEX `rtx_UNIQUE` (`rtx` ASC),
  FULLTEXT INDEX `fuzzy_search` (`rtx` ASC, `pinyin` ASC),
  INDEX `searchable` (`searchable` ASC),
  INDEX `updated_at` (`updated_at` ASC))
ENGINE = InnoDB

mysql> show indexes from staffs;
+--------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| staffs |          0 | PRIMARY      |            1 | id          | A         |       59091 |     NULL | NULL   |      | BTREE      |         |               |
| staffs |          0 | rtx_UNIQUE   |            1 | rtx         | A         |       59091 |     NULL | NULL   |      | BTREE      |         |               |
| staffs |          1 | searchable   |            1 | searchable  | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| staffs |          1 | updated_at   |            1 | updated_at  | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| staffs |          1 | fuzzy_search |            1 | rtx         | NULL      |       59091 |     NULL | NULL   |      | FULLTEXT   |         |               |
| staffs |          1 | fuzzy_search |            2 | pinyin      | NULL      |       59091 |     NULL | NULL   |      | FULLTEXT   |         |               |
+--------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

为了看的清晰,我把建表语句也贴出来了,大家主需要看重点 FULLTEXT INDEX fuzzy_search (**rtx** ASC, pinyin ASC),我把rtx和拼音字段做了一个全文索引,命名为fuzzy_search。那么在这个时候对应的查询语句就是:

mysql> select * from staffs where match(rtx, pinyin) against('*andy*' IN BOOLEAN MODE) limit 5;
+------+-------------+-----------+----------+------------+---------------------+
| id   | rtx         | pinyin    | chn_name | searchable | updated_at          |
+------+-------------+-----------+----------+------------+---------------------+
|  871 | alee        | AndyLee   | AndyLee  |          1 | 2018-05-11 13:32:44 |
| 1895 | andyabhuang | huanganbu | ???      |          1 | 2018-05-11 13:32:44 |
| 1896 | andyao      | aonaiyong | ???      |          1 | 2018-05-11 13:32:44 |
| 1897 | andyawang   | wangang   | ??       |          1 | 2018-05-11 13:32:44 |
| 1898 | andybi      | bisheng   | ??       |          1 | 2018-05-11 13:32:44 |
+------+-------------+-----------+----------+------------+---------------------+
5 rows in set (0.00sec)

mysql> explain select * from staffs where match(rtx, pinyin) against('*andy*' IN BOOLEAN MODE) limit 5;
+----+-------------+--------+----------+---------------+--------------+---------+------+------+-------------+
| id | select_type | table  | type     | possible_keys | key          | key_len | ref  | rows | Extra       |
+----+-------------+--------+----------+---------------+--------------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs | fulltext | fuzzy_search  | fuzzy_search | 0       | NULL |    1 | Using where |
+----+-------------+--------+----------+---------------+--------------+---------+------+------+-------------+
1 row in set (0.01 sec)

模糊匹配策略

如上,索引完美命中,效率喜人,那么中文怎么办?全文索引解决不了中文分词,不过人是活的嘛~要会变通~在代码层,我们可做出如下逻辑:

  • 如果用户输入参数不包含中文,则默认其搜索rtx或拼音,使用全文索引查询;
  • 如果用户输入参数包含中文,则使用LIKE %%查询中文名字段;
  • 关键字查询接口返回结果做Redis缓存,缓存时间为120分钟;

所以我的解决方式:使用全文索引优化rtx和拼音的模糊查询,中文的模糊查询继续使用**LIKE %%**,最后再加一个Redis缓存。介于IEG市场部也就200多号人, 这已经完全够用了。

<?php

namespace App\Criteria;

use Prettus\Repository\Criteria\RequestCriteria;
use Prettus\Repository\Contracts\RepositoryInterface;

/**
 * Class StaffSearchCriteriaCriteria.
 *
 * @package namespace App\Criteria;
 */
class StaffSearchCriteriaCriteria extends RequestCriteria
{
    /**
     * Apply criteria in query repository
     *
     * @param string              $model
     * @param RepositoryInterface $repository
     *
     * @return mixed
     */
    public function apply($model, RepositoryInterface $repository)
    {
        $str = $this->request->get('q', null);
        $model = $model->where('searchable', 1);
        // 含中文只搜索chn_name
        if (preg_match("/[\x7f-\xff]/", $str)) {
            $model = $model->where('chn_name', 'like', "%$str%");
        } else {
            // IN BOOLEAN MODE
            $str = "*$str*";
            $model = $model->whereRaw("MATCH (rtx, pinyin) AGAINST (? IN BOOLEAN MODE)", [$str]);
        }
        return $model->select(['rtx', 'pinyin', 'chn_name'])->take(5);
    }
}

最后前端同学别忘了加上debounce防抖动函数~

坚持不用LIKE的方案

如果你说我坚持不用LIKE!好兄弟!有理想!

字段内容是中文,没法做全文索引,但是有变通的办法,就是将整句的中文分词,并按urlencode、区位码、base64、拼音等进行编码使之以"字母+数字"的方式存储于数据库中。

你有张表里面有个title字段,可以再加一个字段叫title_pinyin

e.g.

| title | title_pinyin

| ------------ |

| 刺激战场 | ci ji zhan chang

如此你便可以继续使用全文索引了,查询的时候需要将中文转成拼音,再使用全文索引match的方式进行查询。

还是渐进式扩展的好

相信大多数的情况是,前期数据量少,即使用LIKE也无伤大雅,但随着数据量增多,扫描全表必然不是长久之计。所以我个人还是建议,我们开发不要过度设计,思考可以很长远,但做可以只做一点点。中后期引入搜索引擎,一劳永逸的解决问题。

搜索引擎

这里帮助大家罗列能够快速入门的全文检索引擎。

Sphinx

如果你和我一样,数据源存放在MySQL,可是使用:Sphinx

其实咱们KM早期就是使用Sphinx实现全文检索查询的,Sphinx可以非常容易的与SQL数据库和脚本语言集成。当前系统内置MySQL和PostgreSQL 数据库数据源的支持,也支持从标准输入读取特定格式 的XML数据。通过修改源代码,用户可以自行增加新的数据源。现在KM使用的是搜搜的底层实现搜索;

Elasticsearch

如果你是使用MongoDB,中文分词同样是个坑!业界通用方案是使用Elasticsearch 实现中文检索。 大致的路数是:使用 mongo-connector 将数据同步到Elasticsearch中;

RediSearch

最后还有一个好东西RediSearch是一款基于redis的搜索组件。基于redis,性能高效,.实时更新索引,支持Suggest前缀、拼音查找(AutoComplete 功能) ,支持单个或多个分词搜索 ,可根据字段进行结果排序。

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

我来说两句

0 条评论
登录 后参与评论

相关文章

扫码关注云+社区

领取腾讯云代金券