MySQL 模糊搜索测试

为了解决棘手的字符模糊匹配问题,于是对MySQL进行了一次大数据模糊匹配测试。测试环境:1台Linux虚拟机(CentOS 7.x), 单核,1G内存, MySQL 5.6,未做任何优化(默认安装)。

#key建了一个索引
mysql> show create table indexes \G;
*************************** 1. row ***************************
       Table: indexes
Create Table: CREATE TABLE `indexes` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `key` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '',
  `type` tinyint(4) NOT NULL DEFAULT '0',
  `refto` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `key_idx` (`key`)
) ENGINE=MyISAM AUTO_INCREMENT=91830001 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)

#总行数,约1亿条
mysql> select count(*) from indexes;
+----------+
| count(*) |
+----------+
| 91830000 |
+----------+
1 row in set (0.00 sec)

mysql> select count(id) from indexes;
+-----------+
| count(id) |
+-----------+
|  91830000 |
+-----------+
1 row in set (0.00 sec)

#前缀like,走索引
mysql> explain select * from indexes where `key` like 'aa%' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: indexes
         type: range
possible_keys: key_idx
          key: key_idx
      key_len: 98
          ref: NULL
         rows: 44955
        Extra: Using index condition
1 row in set (0.01 sec)

#毫秒级完成
mysql> select * from indexes where `key` like 'bcd%' limit 100;
+----------+----------------------------------+------+-----------+
| id       | key                              | type | refto     |
+----------+----------------------------------+------+-----------+
|  9772263 | bcd0G5dNGJCE3ZLTvX4hTM8uOdvP     |    7 | 398601199 |
| 52244881 | bcd0zoLlD3cIgxoyAKHUQ1           |    1 | 397094852 |
| 50534282 | bcdGOh3euqx34nQUj                |    9 | 314711977 |
+----------+----------------------------------+------+-----------+
100 rows in set (0.00 sec)

#前后模糊(全表扫描) 几十秒完成
mysql> select * from indexes where `key` like '%bcd%' limit 100;
+---------+----------------------------------+------+-----------+
| id      | key                              | type | refto     |
+---------+----------------------------------+------+-----------+
|   22704 | 7jXbcd6Ryp3SUM1nCK9xqeszg2PV4    |    1 | 729091231 |
|   29434 | zlbcdN6dEK1TZW4mKvHjbGrQvD4b04   |    2 | 520555898 |
| 1465369 | g1bcdtfpW1NwcQl8uo               |    8 | 292527767 |
+---------+----------------------------------+------+-----------+
100 rows in set (0.60 sec)

mysql> select * from indexes where `key` like '%bdddcd%' limit 100;
Empty set (38.23 sec)

虽然有大约1亿条数据,但是前缀like是非常快速的,而且对于表有多少行数据并不影响查询效率(扫描索引)。但是前后like模糊搜索就比较不满意了。由于前缀like比较高效率,所以我们可以把类似%abcd%这样的查询,拆分成abcd, bcd, cd和d来存储,这样就可以把前面的%去掉。

不过,对于太长的字符串,这么做就会产生大量数据,查询效率上可能不会遇到问题,不过会占用大量存储空间,看看1亿条数据占用了多少空间:

-rw-rw---- 1 mysql mysql 8.5K May 23 20:02 indexes.frm
-rw-rw---- 1 mysql mysql 3.3G May 24 08:00 indexes.MYD
-rw-rw---- 1 mysql mysql 4.6G May 24 08:00 indexes.MYI

总共用8G空间,大概就是1000万占用800兆,10万占用8兆,1万条记录占用800K,100条占用8K, 1条数据占用0.08K(约82字节)。实际如果表字段多一些,会占用更大空间。

还好,我们可以用空间换时间。一个应用场景,需要从所有订单中搜索包含有指定字符串的所有订单,由于订单是不断增长的,加入1天有1万个订单,一年就有365万,3年就到达1000万,如果要从1000万中(订单号,SKU等)搜索包含有”abc”字符串的订单,那么必定要写成%abc%,虽然全局扫表也是可以出结果的,不过时间就比较难接受了。为了更好的体验,就需要建立“倒排”索引,比如订单号为AS-12345678(对应id为1),变成如下记录进行存储:

1 AS-12345678  1
2  S-12345678  1
3   -12345678  1
4    12345678  1
5     2345678  1
6      345678  1
7       45678  1
8        5678  1
9         678  1

一条记录拆成10条,如果还有其它字段,1000万记录建索引,轻松上亿。不过即使是亿级别的数据,查询依然很轻松。