MySQL LIKE 优化

数据库

前面文章介绍了最近一直在维护的老项目,业务运行经过 一段简单的优化后,运行还算流畅. 但是总有那么不可能的可能存在, 在一个同事 搜索联系人手机号码的时候 卡主了整个系统, 于是 公司群里 又炸开了锅. 开始了问题的追溯之旅:

个人怀疑:

  1. 网络有问题了
  1. 数据库爆了

先解决

网络问了运维没有问题, 那最大的可能是 数据库的问题了, 于是 直接开始:

1
2
3

SHOW FULL PROCESSLIST

发现有好几个相同的sql卡死了数据库.sql语句如下:

1
2
SELECT incc.customer_id from  contacts inc  left join r_contacts_customer incc  on incc.contacts_id = inc.contacts_id where inc.telephone like '%18958102776%'

先复制出来 SQL, 然后 把 数据库中的进程 kill 掉, 去前台 发现系统不卡了, 接着通过 SHOW FULL PROCESSLIST 发现有很多的 Sleep 链接, 一看数据库 本身配置了500,但是目前 Sleep 都到达 480了,本着死道友不死贫道的原则,整了个脚本来清理:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
<?php
/**
 * Author: PFinal南丞
 * Date: 2023/12/28
 * Email: <lampxiezi@163.com>
 */

/** 确保这个函数只能运行在 shell 中 **/
if (!str_starts_with(php_sapi_name(), "cli")) {
    die("此脚本只能在cli模式下运行.\n");
}
/** 关闭最大执行时间限制 */
set_time_limit(0);
error_reporting(E_ALL);
ini_set('display_errors', 1);
const MAX_SLEEP_TIME = 10;
$hostname   = '';
$username   = '';
$password   = '';
$connection = mysqli_connect($hostname, $username, $password);

if (!$connection) {
    die('Could not connect: ' . mysqli_error($connection));
}

$query  = "SHOW PROCESSLIST";
$result = mysqli_query($connection, $query);
if (!$result) {
    die("Query failed: " . mysqli_error($connection));
}
while ($process = mysqli_fetch_assoc($result)) {
    $command = $process["Command"];
    $time    = $process["Time"];
    if ($command === "Sleep" && $time > MAX_SLEEP_TIME) {
        $id        = $process["Id"];
        $killQuery = "kill " . $id;
        mysqli_query($connection, $killQuery);
        echo "kill " . $id . PHP_EOL;
    } // 查询时间超过 100秒的 查询 语句
}

mysqli_close($connection);

运行上面的脚本,一下清爽了好多.

分析原因

由于 上面的 SQL 语句 卡死了系统, 于是在预发布的环境上 跑了一下 EXPLAIN 如下:

1
2
3

EXPLAIN select incc.customer_id from  contacts inc     left join r_contacts_customer incc  on incc.contacts_id = inc.contacts_id  where inc.telephone like '%6911%'

结果如下:

1
2
3
4

1	SIMPLE	inc	index		idx_telephone_fixedphone	608		12822520	      Using where; Using index
1	SIMPLE	incc	ref	idx_all,idx_contacts_id	idx_all	4	    inc.contacts_id	   1	Using index

通过上面发下,虽然 key 是 idx_telephone_fixedphone 但是 由于 like 的原因 导致了全表扫描了 12822520 行的数据. 于是 改了一下 上面的 sql 再试一下 EXPLAIN 

1
2
3

EXPLAIN select incc.customer_id from  contacts inc     left join r_contacts_customer incc  on incc.contacts_id = inc.contacts_id  where inc.telephone like '6911%'

结果如下:

1
2
3

1	SIMPLE	inc	range	idx_telelphone,telephone	idx_telelphone	302		2	Using where
1	SIMPLE	incc	ref	idx_all,idx_contacts_id	idx_all	4	inc.contacts_id	1	Using index

如上结果所示, like 去掉了开头的 ‘%’ 走了索引,但是 由于业务的关系, 需要查询 手机号的任何 4位, 所以如果 去掉 开头的 ‘%’ 就会不符合业务的需求.

解决问题

都知道 一般解决LIKE 方法主要下面几种:

  • 使用内置函数 Instr()、Locate()和Position() 等内置函数
1
2
3
SELECT INSTR("hello,pfinalclub", 'pfinalclub');-- 1 
SELECT LOCATE('pfinalclub', "hello,pfinaclub");-- 1 
SELECT POSITION('pfinalclub' in "hello, pfinalclub"); -- 1
  • 使用前缀索引 可以创建一个适当长度的前缀索引来匹配LIKE语句中的前缀。这样可以提高查询性能并利用索引。
1
2
3

ALTER TABLE your_table ADD INDEX idx_telephone_prefix (telephone(10)); -- 10是索引前缀的长度

  • 使用全文索引 可以在数据库中创建一个全文索引，以便在LIKE语句中使用。全文索引可以提高查询性能并利用索引。
1
2
3

ALTER TABLE your_table ADD FULLTEXT idx_telephone_fulltext (telephone);

  • 使用正则表达式 可以在LIKE语句中使用正则表达式来匹配字符串。这样可以避免使用LIKE语句，并利用正则表达式的索引。
1
2
3
    
SELECT * FROM your_table WHERE telephone REGEXP '^17621166911$';

  • 使用覆盖索引： 如果查询中只涉及到了一个表，可以尝试创建一个覆盖索引，该索引包含了LIKE语句中的列和查询中涉及到的其他列。这样可以使MySQL避免回表操作，提高查询性能。
1
2
3

CREATE INDEX idx_telephone_fulltext ON your_table (telephone, other_column);

  • 使用第三方工具: 使用 lucene或者elasticsearch

以上的解决方案中,最贴近业余需求的,就是使用内置函数和 全文索引了.先试试以下2种方法

使用内置函数

1
2
SELECT incc.customer_id from  contacts inc  left join r_contacts_customer incc  on incc.contacts_id = inc.contacts_id  where LOCATE('6911',inc.telephone)>1

上面的语句虽然比 like 快了点 但是 EXPLAIN 的结果还是 不走索引 并且全表扫描 contacts 

1
2
3
4

1	SIMPLE	inc	index		idx_telephone_fixedphone	608		12822520	      Using where; Using index
1	SIMPLE	incc	ref	idx_all,idx_contacts_id	idx_all	4	    inc.contacts_id	   1	Using index

使用全文索引

使用全文索引要求:

  • 存储引擎： 全文索引功能仅适用于特定的MySQL存储引擎，例如InnoDB和MyISAM。确保表使用的是支持全文索引的存储引擎。

  • 列类型： 全文索引通常仅适用于CHAR、VARCHAR和TEXT类型的列。其他类型的列可能不支持全文索引，或者会有一些限制。

  • 最小词长度： MySQL的全文索引功能默认要求索引的词长度大于等于4个字符。如果需要索引更短的词，可能需要调整MySQL的全文索引配置参数，如ft_min_word_len。

  • 停用词列表: 全文索引功能通常会忽略一些常用词，例如“the”、“and”等，这些词被称为停用词。确保查询不包含停用词，以避免影响查询结果。

  • 配置参数: 在一些MySQL版本中，全文索引的功能和性能可能会受到一些配置参数的影响，例如innodb_ft_min_token_size、innodb_ft_result_cache_limit等。

查看一下当前表 contacts 的结构, 满足上面的 要求,于是果断的 给 contacts 表创建了一个 全文索引:

1
2
3

ALTER TABLE contacts ADD FULLTEXT idx_telephone_fulltext (telephone);

接着 执行 EXPLAIN 如下:

1
2

EXPLAIN select incc.customer_id from  contacts inc  left join r_contacts_customer incc  on incc.contacts_id = inc.contacts_id  where inc.telephone like '%6911%'

结果是:

1
2
3
4

1	SIMPLE	inc	index		idx_telephone_fixedphone	608		12822520	Using where; Using index
1	SIMPLE	incc	ref	idx_all,idx_contacts_id	idx_all	4	smm.inc.contacts_id	1	Using index

使用 MATCH() 匹配 全文索引 如下:

1
2
3

SELECT incc.customer_id from  contacts inc  left join r_contacts_customer incc  on incc.contacts_id = inc.contacts_id  where MATCH(inc.telephone) AGAINST('"6911"' IN BOOLEAN MODE)

结果如下,速度溜得飞起.

注意

这里由于在存储手机号吗的时候, 是按照 176 2166 6911 这种格式存储的,所以 上面的 全文索引 是能够通过 6911 匹配的到的. 如果是 17621666911 这种可就匹配不到了哈