mysql数值比中文检索快_【mysql】利用全文索引实现中文的快速查找

现在我们数据库表中的记录大约在17万，每一条记录都有name字段，根据name做模糊匹配，效率非常低下。

表结构如下：

create tableT

(

idint,

namevarchar(64)

);

总数据量如下:

mysql> select count(*) fromT;+----------+

| count(*) |

+----------+

| 175152 |

+----------+

1 row in set (0.00 sec)

按照名称进行模糊匹配执行结果如下，用时0.29秒：

mysql> select count(*) from T where (name like '%玻璃奶瓶%');+----------+

| count(*) |

+----------+

| 712 |

+----------+

1 row in set (0.29 sec)

mysql> select id, name from T where (name like '%玻璃奶瓶%') limit 0,10;+---------+---------------------------------------------------------------+

| id | name |

+---------+---------------------------------------------------------------+

| 1206852 | 伊诗比蒂-120ML标准口径玻璃奶瓶防摔护套BT-1250 |

| 1206853 | 伊诗比蒂-120ML宽口径玻璃奶瓶防摔护套BT-1251 |

| 1206854 | 伊诗比蒂-200ML标准口径玻璃奶瓶防摔护套BT-1252 |

| 1206855 | 伊诗比蒂-160ML宽口径玻璃奶瓶防摔护套BT-1253 |

| 1206856 | 伊诗比蒂-240ML标准口径玻璃奶瓶防摔护套 |

| 1206857 | 240ML宽口径玻璃奶瓶防摔护套BT-1255 |

| 1309742 | 贝亲标准口径玻璃奶瓶240ml |

| 1309743 | 贝亲标准口径玻璃奶瓶200ml |

| 1309744 | 贝亲标准母乳实感奶嘴玻璃奶瓶120ml |

| 1309745 | 贝亲硅胶宽口径玻璃奶瓶240ml |

+---------+---------------------------------------------------------------+

10 rows in set (0.19 sec)

like操作无法走到索引，正好趁着机会尝试下MySql的全文索引功能。

因为中文语句不像英文，单词与单词之间用空格隔开，存在天然的分隔符，因此当前MySql的全文索引无法支持中文。

当然，也有办法可以绕过去，比如把汉字进行转码或者转成拼音，经过比较，最终还是确定使用转base64的方法，只针对中文做转换，英文和和符号不转。

首先，需要修改表结构，增加一个字段用来保存转码后的值，并创建这个字段的全文索引

alter table T add column nameindex varchar(256) not null default '';alter table T add fulltext name_index_fc(`nameindex `);

然后，通过脚本批量刷新数据，把每条记录中的name转码后存入nameindex。空格是mysql全文索引的分词符之一，为了能实现和like匹配一样的功能，需要以字为最小单位，字与字之间的base64编码用空格分开。

下面是python的转码实现：

d='伊诗比蒂-160ML宽口径玻璃奶瓶防摔护套BT-1253'baseStr= ''

for i inrange(len(d)):#print base64.encodestring(d[i].encode('utf8')).strip('\n\r')

#if re.match('[ \u4e00 -\u9fa5]+',d[i]) == None:

if d[i] >= u'\u4e00' and d[i]<=u'\u9fa5':

b= ''

if len(baseStr) !=0:

b= ' 'b+= base64.encodestring(d[i].encode('utf8')).strip('\n\r')

baseStr+=b

baseStr+= ' '

else:

dt=d[i]if d[i] == '"' or d[i] == "'":

dt= '\\'dt+=d[i]

baseStr+=dt

baseStr+= ' '

转换完成后，结果如下：

mysql> select id,name,nameindex from T where (name like '%玻璃奶瓶%') limit 0,10;+---------+---------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------+

| id | name | nameindex |

+---------+---------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------+

| 1206852 | 伊诗比蒂-120ML标准口径玻璃奶瓶防摔护套BT-1250 | 5LyK 6K+X 5q+U 6JKC -120ML 5qCH 5YeG 5Y+j 5b6E 5467 55KD 5aW2 55O2 6Ziy 5pGU 5oqk 5aWX BT-1250 |

| 1206853 | 伊诗比蒂-120ML宽口径玻璃奶瓶防摔护套BT-1251 | 5LyK 6K+X 5q+U 6JKC -120ML 5a69 5Y+j 5b6E 5467 55KD 5aW2 55O2 6Ziy 5pGU 5oqk 5aWX BT-1251 |

| 1206854 | 伊诗比蒂-200ML标准口径玻璃奶瓶防摔护套BT-1252 | 5LyK 6K+X 5q+U 6JKC -200ML 5qCH 5YeG 5Y+j 5b6E 5467 55KD 5aW2 55O2 6Ziy 5pGU 5oqk 5aWX BT-1252 |

| 1206855 | 伊诗比蒂-160ML宽口径玻璃奶瓶防摔护套BT-1253 | 5LyK 6K+X 5q+U 6JKC -160ML 5a69 5Y+j 5b6E 5467 55KD 5aW2 55O2 6Ziy 5pGU 5oqk 5aWX BT-1253 |

| 1206856 | 伊诗比蒂-240ML标准口径玻璃奶瓶防摔护套 | 5LyK 6K+X 5q+U 6JKC -240ML 5qCH 5YeG 5Y+j 5b6E 5467 55KD 5aW2 55O2 6Ziy 5pGU 5oqk 5aWX |

| 1206857 | 240ML宽口径玻璃奶瓶防摔护套BT-1255 | 240ML 5a69 5Y+j 5b6E 5467 55KD 5aW2 55O2 6Ziy 5pGU 5oqk 5aWX BT-1255 |

| 1309742 | 贝亲标准口径玻璃奶瓶240ml | 6LSd 5Lqy 5qCH 5YeG 5Y+j 5b6E 5467 55KD 5aW2 55O2 240ml |

| 1309743 | 贝亲标准口径玻璃奶瓶200ml | 6LSd 5Lqy 5qCH 5YeG 5Y+j 5b6E 5467 55KD 5aW2 55O2 200ml |

| 1309744 | 贝亲标准母乳实感奶嘴玻璃奶瓶120ml | 6LSd 5Lqy 5qCH 5YeG 5q+N 5Lmz 5a6e 5oSf 5aW2 5Zi0 5467 55KD 5aW2 55O2 120ml |

| 1309745 | 贝亲硅胶宽口径玻璃奶瓶240ml | 6LSd 5Lqy 56GF 6IO2 5a69 5Y+j 5b6E 5467 55KD 5aW2 55O2 240ml |

+---------+---------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------+

10 rows in set (0.21 sec)

测试一下性能，耗时0.05秒，提升了四倍的速度

mysql> SELECT count(*) FROM T WHERE MATCH (nameindex AGAINST ('"5467 55KD 5aW2 55O2"' INBOOLEAN MODE) ;+----------+

| count(*) |

+----------+

| 713 |

+----------+

1 row in set (0.05 sec)

再多试几次，性能都有很大的提升

mysql> select count(*) from T where (nameindex like '%凌动%');+----------+

| count(*) |

+----------+

| 70 |

+----------+

1 row in set (0.30sec)

mysql> SELECT count(*) FROM T WHERE MATCH (nameindex) AGAINST ('"5YeM 5Yqo"' INBOOLEAN MODE) ;+----------+

| count(*) |

+----------+

| 70 |

+----------+

1 row in set (0.01sec)

mysql> select count(*) from T where (nameindex like '%凌动惯性%');+----------+

| count(*) |

+----------+

| 9 |

+----------+

1 row in set (0.29sec)

mysql> SELECT count(*) FROM T WHERE MATCH (nameindex) AGAINST ('"5YeM 5Yqo 5oOv 5oCn"' INBOOLEAN MODE) ;+----------+

| count(*) |

+----------+

| 9 |

+----------+

1 row in set (0.00 sec)

细心的你，可能发现了查找‘玻璃奶瓶’的时候，用like和全文索引查找出来的记录总数相差1。

相差的这条记录如下：

mysql> select id ,name,nameindex from T where id = 1314118;+---------+-----------------------------------------------+------------------------------------------------------------------------------+

| id | name | nameindex |

+---------+-----------------------------------------------+------------------------------------------------------------------------------+

| 1314118 | NUK宽口-玻璃-奶瓶妈咪礼包40.260.718 | NUK 5a69 5Y+j - 5467 55KD - 5aW2 55O2 5aaI 5ZKq 56S8 5YyF 40.260.718 |

+---------+-----------------------------------------------+------------------------------------------------------------------------------+

在玻璃和奶瓶之间有一个“-”，因为“-”前后都是汉字，因此转码操作了，“-”成为了一个独立的字符。根据MySql全文索引的默认配置ft_min_word_len = 4，低于4个字符的词不会被保存在索引中。因此“-”被忽略。