现在我们数据库表中的记录大约在17万，每一条记录都有name字段，根据name做模糊匹配，效率非常低下。
表结构如下：
create table T ( id int, name varchar(64) );
总数据量如下:
mysql> select count(*) from T; +----------+ | 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 in range(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"' IN BOOLEAN 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.30 sec) mysql> SELECT count(*) FROM T WHERE MATCH (nameindex) AGAINST ('"5YeM 5Yqo"' IN BOOLEAN MODE) ; +----------+ | count(*) | +----------+ | 70 | +----------+ 1 row in set (0.01 sec) mysql> select count(*) from T where (nameindex like '%凌动惯性%'); +----------+ | count(*) | +----------+ | 9 | +----------+ 1 row in set (0.29 sec) mysql> SELECT count(*) FROM T WHERE MATCH (nameindex) AGAINST ('"5YeM 5Yqo 5oOv 5oCn"' IN BOOLEAN 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个字符的词不会被保存在索引中。因此“-”被忽略。