事件背景：

某 CRM 系统，定义了如下两个表：

客户表 t_custom

字段名	类型	描述
id	long	自增主键
phone	string	客户手机
...	...	...

客户产品关系表 t_custom_product

字段名	类型	描述
id	long	自增主键
custom_id	long	客户id
product_id	long	产品id
...	...	...

有个页面查询的需求，需要根据手机号模糊匹配，查询出所有匹配上的产品信息。

想要快速实现，可以写出如下 sql：

select * from t_custom_product as ta left join t_custom as tb on ta.custom_id = tb.id where tb.phone like %#{phone}%

过了3年，其中 t_custom 已经有了100w 数据、t_custom_product 有了 1000w 数据，这时候，这条 sql 理所当然成了头号慢 sql。

新来的开发 @纪潘霞，受命解决这个问题。

sql">改造 sql

一开始，纪先生想快速解决，就将 sql 改造成如下模式，然后给 phone 字段添加了正向以及反向索引 index(phone,id)   和 index (reverse(phone),id）

SELECT *  FROM (     (select * from t_custom_product as ta left join t_custom as tb on ta.custom_id = tb.id where tb.phone like #{phone}% order by id desc limit 10)         UNION ALL      (select * from t_custom_product as ta left join t_custom as tb on ta.custom_id = tb.id where tb.phone like %#{phone} order by id desc limit 10) ) AS combined  ORDER BY id DESC   LIMIT 10;

改造之后，查询速度飞起，慢 sql 没有了。但是线上用户开始抱怨了，只输中间号码的场景，无法查询了。例如某客户手机号为：13098830998，查询 988 就无法查询出来。

纪先生说：

es 保存宽表

因为不符合需求，方案打回重做。

这时候，纪先生申请了一套 ES 集群。使用黄工提供的 canal 、datax 技术，将这个表的数据打成大宽表，写入 ES，字段大致如下：

客户关系宽表 index_custom_product (1000w数据）

字段名	类型	描述
id	long	t_custom_product 的主键
custom_id	long	t_custom 的主键
phone	string	t_custom 的手机号
product_id	long	产品id
...	...	...

先采用 wildcard 的语句进行查询，查询语句如下 （不加 keyword 什么都查不出来）：

GET index_custom_product/_search {     "query": {         "wildcard" : {             "phone.keyword" : "*#{phone}*"         }     } }

结果发现性能超差，查询资料得知，这种也是走的全表扫描。

es 匹配搜索

考虑 es 本身支持搜索，所以将查询改用搜索的方式：

GET index_custom_product/_search {     "query": {         "match" : {             "phone" : "#{phone}"         }     } }

结果在搜索号码片段的时候，什么都查不出来。查阅资料得知，默认分词器，不会对数字进行分词。

考虑切换成 N-gram 分词器，这个分词器特性如下：

POST _analyze {   "tokenizer": "ngram",   "text": "Quick Fox" }

将会返回（其中 min length = 1 ，max length = 2 ）

[ Q, Qu, u, ui, i, ic, c, ck, k, "k ", " ", " F", F, Fo, o, ox, x ]

原理讲解部分（略，即兴演讲）

纪先生切换之后，这时候对手机号 13098830995 分词，会返回如下结果：

[ 1, 13, 3, 30, 0, 09, 9, 98, 8...... ]

然后如果用户查询 988，这个查询会被解析为如下词组的查询

[ 9,98,8,88 ]

显然，可以匹配到手机号 13098830995 的分词，从而查询出结果。
 

仍然有问题

改造之后，纪先生高兴的发布了，结果毫无疑问的，被测试打回来了。

因为测试拿 998 查询，结果只要手机号有 9 的数据都查询出来了。

因为 match  只要有一个词匹配，即匹配成功。

将 match 改成 match_phrase 即可。

GET index_custom_product/_search {     "query": {         "match_phrase" : {             "phone" : "#{phone}"         }     } }

还有问题？

改造之后，纪先生高兴的发布了，结果又被测试打回来了。

测试拿出 9888 进行查询，这个查询的分词组为：

[ 9,98,8,88 ]

显然，可以匹配到手机号 13098830995。

纪先生接着又改了一版，考虑到查询出的结果集已经比较固定了，所以加了 filter 作为后置过滤，通过正则过滤出正确的手机号。

GET index_custom_product/_search {     "query": {       "must": [         { "match_phrase": { "phone":   "#{phone}"        }}       ],       "filter": [         { "regexp":  { "phone": "#{phone_regexp}" }}       ]     } }

性能问题？调优

发布之后，某天，用户输入了 1111111111111(11个1) 进行查询，因为查询很慢，用户等不及狂点起来了。

毫无疑问的，ES 集群挂了。

这个是个开放性的结果，有后面几个调优方向：

1. 经过纪先生和产品的激烈沟通，业务同意这个查询框加入一个判断条件：至少输入 4 位数字。然后 ngram length 调整到 4、4。（需要业务妥协，资源消耗少）
2. ngram 调整 length 到 2、11，但是这样会让 es 内存占用加倍，需要扩容一下 ES。（业务体验最好，最占资源）
3. ngram 调整 length 到 2、2，然后改用 term 查询，但是会有一定的幻觉。例如 8398 可以查询出 13398830995 => 高亮词汇为 83、39、98。（资源消耗少，极端情况会有异常数据）

这里只是给一个思路，调优本质上还是根据具体业务场景进行定制，技术和业务的互相妥协