[文章+程序 作者：张宴 本文版本：v1.0 最后修改：2008.07.01 转载请注明原文链接：http://blog.zyan.cc/post/356/] MySQL在高并发连接、数据库记录数较多的情况下，SELECT ... WHERE ... LIKE '%...%'的全文搜索方式不仅效率差，而且以通配符%和_开头作查询时，使用不到索引，需要全表扫描，对数据库的压力也很大。MySQL针对这一问题提供了一种全文索引解决方案，这不仅仅提高了性能和效率（因为MySQL对这些字段做了索引来优化搜索），而且实现了更高质量的搜索。但是，至今为止，MySQL对中文全文索引无法正确支持。 中文与西方文字如英文的一个重要区别在于，西方文字以单词为单位，单词与单词之间以空格分隔。而中文以字为单位，词由一个或多个字组成，词与词之间没有空格分隔。当试图在一个含有中文字符的字段中使用全文搜索时，不会得到正确的结果，原因在于中文中没有像英文空格那样对词定界，不能以空格作为分割，对中文词语进行索引。 引用《MySQL 5.1参考手册》中的一段话： 引用 12.7. 全文搜索功能（http://dev.mysql.com/doc/refman/5.1/zh/functions.html） ● MySQL支持全文索引和搜索功能。MySQL中的全文索引类型FULLTEXT的索引。FULLTEXT 索引仅可用于 MyISAM 表；他们可以从CHAR、 VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建，或是随后使用ALTER TABLE 或 CREATE INDEX被添加。对于较大的数据集，将你的资料输入一个没有FULLTEXT索引的表中，然后创建索引， 其速度比把资料输入现有FULLTEXT索引的速度更为快。 ● FULLTEXT分析程序会通过寻找某些分隔符来确定单词的起始位置和结束位置，例如' ' (间隔符号)、 , (逗号)以及 . (句号 )。假如单词没有被分隔符分开，(例如在中文里 ), 则 FULLTEXT 分析程序不能确定一个词的起始位置和结束位置。为了能够在这样的语言中向FULLTEXT 索引添加单词或其它编入索引的术语，你必须对它们进行预处理，使其被一些诸如"之类的任意分隔符分隔开。 ● 诸如汉语和日语这样的表意语言没有自定界符。因此， FULLTEXT分析程序不能确定在这些或其它的这类语言中词的起始和结束的位置。 国内已有的MySQL中文全文索引解决方案有两个：一是海量科技的MySQL5.0.37--LinuxX86-Chinese+，二是hightman开发的mysql-5.1.11-ft-hightman，两者都是基于中文分词技术，对中文语句进行拆分。但是，两者都有弊端，一是不支持64位操作系统；二是对修改了MySQL源码，只支持某一MySQL版本，不便于跟进新版本；三是词库不能做到很大很全，对于专业性质较强的数据库内容（例如搜索“颐和园路东口”、“清华东路西口”等公交站点，“莱镇香格里”、“碧海云天”等楼盘名称），基于中文分词的全文索引经常搜索不出来任何内容，即使添加分词词库，也不会很全面。 由于精准全文查询的需要，我借鉴了二元交叉切分算法的思想，用自创的“三字节交叉切分算法”，写出了这款“MySQL中文全文索引插件──mysqlcft 1.0.0”。由于开发时间仓促，难免存在未发现的问题，这将后续的版本中不断完善。对于百万条记录的MySQL表进行全文检索，mysqlcft已经够用。 Mysqlcft 网址：http://code.google.com/p/mysqlcft/ Mysqlcft 作者：张宴 一、MySQL中文全文索引插件mysqlcft的特点： 1、优点： ①、精准度很高：采用自创的“三字节交叉切分算法”，对中文语句进行分割，无中文分词词库，搜索精准度远比中文分词算法高，能达到LIKE '%...%"的准确率。 ②、查询速度快：查询速度比LIKE '%...%"搜索快3～50倍，文章末尾有测试结果； ③、标准插件式：以MySQL 5.1全文索引的标准插件形式开发，不修改MySQL源代码，不影响MySQL的其他功能，可快速跟进MySQL新版本； ④、支持版本多：支持所有的MySQL 5.1 Release Candidate版本，即MySQL 5.1.22 RC～最新的MySQL 5.1.25 RC； ⑤、支持字符集：支持包括GBK、GB2312、UTF-8、Latin1、BIG5在内的MySQL字符集（其他字符集没有测试过）； ⑥、系统兼容好：具有i386和x86_64两个版本，支持32位（i386）和64位（x86_64）CPU及Linux系统； ⑦、适合分布式：非常适合MySQL Slave分布式系统架构，无词库维护成本，不存在词库同步问题。 2、缺点： ①、mysqlcft中文全文索引只适用于MyISAM表，因为MySQL只支持对MyISAM表建立FULLTEXT索引； ②、MySQL不能静态编译安装，否则无法安装mysqlcft插件； ③、基于“三字节交叉切分算法”的索引文件会比海量、ft-hightman等基于“中文分词算法”的索引文件稍大，但不是大很多。根据我的测试，mysqlcft全文索引的.MYI索引文件是.MYD数据文件的2～5倍。 二、mysqlcft的核心思想──“三字节交叉切分算法” 点击在新窗口中浏览此图片 注：本文以0～7数字序号代表“英文”、“数字”和“半个汉字”，以便说明。 1、按三字节对中文语句进行切分，建立全文索引： 例如：“全文索引”或“1台x光机”四个字会被交叉分拆为6份，建立反向索引： 012 123 234 345 456 567 2、按三字节对搜索的关键字进行切分，在全文索引中找出对应信息： 例①：搜索关键字“文索”，用数字序号表示就是“2～5”，那么它将被切分成： 234 345 这样，就与全文索引对上了。 例②：搜索关键字“x光机”，用数字序号表示就是“3～7”，那么它将被切分成： 345 456 567 这样，也与全文索引对上了。 例③：搜索关键字“1台 光机”，用数字序号表示就是“0～2”和“4～7”，那么它将被切分成： 012 456 567 这样，多关键字搜索也与全文索引对上了。 三、编译安装MySQL（如果已经装有不是静态编译安装的MySQL 5.1.22 RC～MySQL 5.1.25 RC，此步骤可省略） 1、下载并编译安装MySQL 5.1.25 RC 在http://dev.mysql.com/get/Downloads/MySQL-5.1/mysql-5.1.25-rc.tar.gz/from/pick（点击No thanks, just take me to the downloads!链接），选择一个镜像，下载MySQL 5.1.25 RC源码包： tar zxvf mysql-5.1.25-rc.tar.gz cd mysql-5.1.25-rc/ ./configure --prefix=/usr/local/mysqlcft/ --without-debug --enable-assembler --with-extra-charsets=all --with-pthread --enable-thread-safe-client make && make install /usr/sbin/groupadd mysql /usr/sbin/useradd -g mysql mysql chmod +w /usr/local/mysqlcft chown -R mysql:mysql /usr/local/mysqlcft 2、创建MySQL数据文件存放目录/mysql/3306 mkdir -p /mysql/3306 chmod +w /mysql/3306 chown -R mysql:mysql /mysql/3306 mkdir -p /mysql/3306/data chmod +w /mysql/3306/data chown -R mysql:mysql /mysql/3306/data chown -R mysql:mysql /mysql #cp support-files/my-medium.cnf /mysql/3306/my.cnf cd ../ 3、创建配置文件/mysql/3306/my.cnf vi /mysql/3306/my.cnf 输入以下内容（注意：必须设置ft_min_word_len = 1）： 引用 [client] #password = your_password port = 3306 socket = /mysql/3306/mysql.sock default-character-set = gbk [mysqld_safe] datadir = /mysql/3306/data log-error = /mysql/3306/mysql_error.log pid-file = /mysql/3306/mysql.pid [mysqld] port = 3306 socket = /mysql/3306/mysql.sock default-character-set = gbk #init_connect = 'SET NAMES gbk' skip-locking #skip-slave-start key_buffer = 512M max_allowed_packet = 2M table_cache = 1024 sort_buffer_size = 32M read_buffer_size = 2M read_rnd_buffer_size = 32M max_length_for_sort_data = 64 myisam_sort_buffer_size = 128M thread_cache = 8 query_cache_size = 64M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 8 #skip-name-resolve set-variable = max_connections=1000 open_files_limit = 51200 ft_min_word_len = 1 low_priority_updates = 1 slave-skip-errors = 1032,1062,126 server-id = 9 #master-host = host #master-user = user #master-password = password #master-port = 3306 #replicate-do-db = db1 #replicate-do-db = db2 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout 附：MySQL配置文件在全文索引应用中的优化 引用 [mysqld] # key_buffer 指定用于索引的缓冲区大小，在全文索引中，增加它可得到更好的索引处理与查询性能 key_buffer = 512M # sort_buffer_size 为查询排序时所能使用的缓冲区大小，全文索引的SQL语句之后通常会使用ORDER BY排序，增加它可以加快SQL语句执行时间。该参数对应的分配内存是每连接独占，100个连接使用的内存将是32M*100=3200M sort_buffer_size = 32M # 对大于可用内存的表执行GROUP BY或ORDER BY操作，应增加read_rnd_buffer_size的值以加速排序操作后面的行读取 read_rnd_buffer_size = 64M # 如果表出现故障或索引出错，REPAIR TABLE时用到的缓冲区大小 myisam_sort_buffer_size = 128M # 确定使用的filesort算法的索引值大小的限值 max_length_for_sort_data = 64 # MySQL全文索引查询所用关键词最小长度限制（不要改变这项值） ft_min_word_len = 1 # 降低UPDATE优先级，设置查询优先 low_priority_updates = 1 4、以mysql用户帐号的身份建立数据表 /usr/local/mysqlcft/bin/mysql_install_db --defaults-file=/mysql/3306/my.cnf --basedir=/usr/local/mysqlcft --datadir=/mysql/3306/data --user=mysql --pid-file=/mysql/3306/mysql.pid --skip-locking --port=3306 --socket=/mysql/3306/mysql.sock 5、启动MySQL /bin/sh /usr/local/mysqlcft/bin/mysqld_safe --defaults-file=/mysql/3306/my.cnf & 附：停止MySQL /usr/local/mysqlcft/bin/mysqladmin -u root -p -S /mysql/3306/mysql.sock shutdown 四、安装mysqlcft中文全文索引插件 1、从命令行登入MySQL服务器： /usr/local/mysqlcft/bin/mysql -u root -p -S /mysql/3306/mysql.sock 2、查看MySQL插件目录的默认路径的SQL语句： SHOW VARIABLES LIKE 'plugin_dir'; 点击在新窗口中浏览此图片 3、下载mysqlcft中文全文索引插件，解压后拷贝mysqlcft.so文件到MySQL插件目录 ①、32位Linux操作系统： wget http://mysqlcft.googlecode.com/files/mysqlcft-1.0.0-i386-bin.tar.gz tar zxvf mysqlcft-1.0.0-i386-bin.tar.gz mkdir -p /usr/local/mysqlcft/lib/mysql/plugin/ cp mysqlcft.so /usr/local/mysqlcft/lib/mysql/plugin/ ②、64位Linux操作系统： wget http://mysqlcft.googlecode.com/files/mysqlcft-1.0.0-x86_64-bin.tar.gz tar zxvf mysqlcft-1.0.0-x86_64-bin.tar.gz mkdir -p /usr/local/mysqlcft/lib/mysql/plugin/ cp mysqlcft.so /usr/local/mysqlcft/lib/mysql/plugin/ 4、安装mysqlcft.so插件 ①、从命令行登入MySQL服务器： /usr/local/mysqlcft/bin/mysql -u root -p -S /mysql/3306/mysql.sock ②、安装mysqlcft.so插件的SQL语句： INSTALL PLUGIN mysqlcft SONAME 'mysqlcft.so'; ③、查看mysqlcft.so插件是否安装成功的SQL语句： SELECT * FROM mysql.plugin; SHOW PLUGINS; 点击在新窗口中浏览此图片 附：如果要卸载mysqlcft.so插件，执行以下SQL语句（如果已经创建了mysqlcft索引，请先删除mysqlcft索引，再卸载mysqlcft.so插件）： UNINSTALL PLUGIN mysqlcft; 五、为已经存在的表添加mysqlcft中文全文索引 1、创建单列全文索引SQL语句 ALTER IGNORE TABLE 数据库名.表名 ADD FULLTEXT INDEX 全文索引名 (字段名) WITH PARSER mysqlcft; 2、创建全文联合索引SQL语句 ALTER IGNORE TABLE 数据库名.表名 ADD FULLTEXT INDEX 全文联合索引名 (字段名1,字段名2) WITH PARSER mysqlcft; 六、重建mysqlcft中文全文索引（索引损坏时需要用到） REPAIR TABLE 数据库名.表名 QUICK; 七、建表时创建mysqlcft中文全文索引＋全文搜索测试 1、以latin1字符集为例 CREATE DATABASE `mysqlcft_latin1` DEFAULT CHARACTER SET latin1; USE `mysqlcft_latin1`; CREATE TABLE `test` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `title` varchar(200) DEFAULT NULL, `body` mediumtext, PRIMARY KEY (`id`), FULLTEXT KEY `title_body` (`title`,`body`) WITH PARSER mysqlcft ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO `mysqlcft_latin1`.`test` (`id`, `title`, `body`) VALUES (NULL, '北京房价', '北京市统计局、国家统计局北京调查总队近日联合对外发布消息，今年以来，北京的商品房价格一直呈上升趋势，五环路以内住宅期房均价已涨至13754元/平方米。'); INSERT INTO `mysqlcft_latin1`.`test` (`id`, `title`, `body`) VALUES (NULL, '北京中心城区今起可无线宽带上网 奥运期间免费', '新浪科技讯 6月25日消息，北京无线城市一期网络今日起试运行，即日起北京市民和海外游客可以通过无线网络在北京中心城区接入互联网。'); INSERT INTO `mysqlcft_latin1`.`test` (`id`, `title`, `body`) VALUES (NULL, '数据库', '欢迎使用MySQL中文全文索引插件mysqlcft！'); SELECT * FROM mysqlcft_latin1.test WHERE MATCH(title,body) AGAINST ('13754元/平方米' IN BOOLEAN MODE); SELECT * FROM mysqlcft_latin1.test WHERE MATCH(title,body) AGAINST ('6月25日' IN BOOLEAN MODE); SELECT * FROM mysqlcft_latin1.test WHERE MATCH(title,body) AGAINST ('北京' IN BOOLEAN MODE); SELECT * FROM mysqlcft_latin1.test WHERE MATCH(title,body) AGAINST ('北京 宽带' IN BOOLEAN MODE); SELECT * FROM mysqlcft_latin1.test WHERE MATCH(title,body) AGAINST ('mysqlcft' IN BOOLEAN MODE); SELECT * FROM mysqlcft_latin1.test WHERE MATCH(title,body) AGAINST ('数据' IN BOOLEAN MODE); 2、以gbk字符集为例 CREATE DATABASE `mysqlcft_gbk` DEFAULT CHARACTER SET gbk; USE `mysqlcft_gbk`; CREATE TABLE `test` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `title` varchar(200) DEFAULT NULL, `body` mediumtext, PRIMARY KEY (`id`), FULLTEXT KEY `title_body` (`title`,`body`) WITH PARSER mysqlcft ) ENGINE=MyISAM DEFAULT CHARSET=gbk; INSERT INTO `mysqlcft_gbk`.`test` (`id`, `title`, `body`) VALUES (NULL, '北京房价', '北京市统计局、国家统计局北京调查总队近日联合对外发布消息，今年以来，北京的商品房价格一直呈上升趋势，五环路以内住宅期房均价已涨至13754元/平方米。'); INSERT INTO `mysqlcft_gbk`.`test` (`id`, `title`, `body`) VALUES (NULL, '北京中心城区今起可无线宽带上网 奥运期间免费', '新浪科技讯 6月25日消息，北京无线城市一期网络今日起试运行，即日起北京市民和海外游客可以通过无线网络在北京中心城区接入互联网。'); INSERT INTO `mysqlcft_gbk`.`test` (`id`, `title`, `body`) VALUES (NULL, '数据库', '欢迎使用MySQL中文全文索引插件mysqlcft！'); SELECT * FROM mysqlcft_gbk.test WHERE MATCH(title,body) AGAINST ('13754元/平方米' IN BOOLEAN MODE); SELECT * FROM mysqlcft_gbk.test WHERE MATCH(title,body) AGAINST ('6月25日' IN BOOLEAN MODE); SELECT * FROM mysqlcft_gbk.test WHERE MATCH(title,body) AGAINST ('北京' IN BOOLEAN MODE); SELECT * FROM mysqlcft_gbk.test WHERE MATCH(title,body) AGAINST ('北京 宽带' IN BOOLEAN MODE); SELECT * FROM mysqlcft_gbk.test WHERE MATCH(title,body) AGAINST ('mysqlcft' IN BOOLEAN MODE); SELECT * FROM mysqlcft_gbk.test WHERE MATCH(title,body) AGAINST ('数据' IN BOOLEAN MODE); 3、以UTF-8字符集为例 CREATE DATABASE `mysqlcft_utf8` CHARACTER SET utf8; USE `mysqlcft_utf8`; CREATE TABLE `test` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `title` varchar(200) DEFAULT NULL, `body` mediumtext, PRIMARY KEY (`id`), FULLTEXT KEY `title_body` (`title`,`body`) WITH PARSER mysqlcft ) ENGINE=MyISAM DEFAULT CHARSET=utf8; INSERT INTO `mysqlcft_utf8`.`test` (`id`, `title`, `body`) VALUES (NULL, '北京房价', '北京市统计局、国家统计局北京调查总队近日联合对外发布消息，今年以来，北京的商品房价格一直呈上升趋势，五环路以内住宅期房均价已涨至13754元/平方米。'); INSERT INTO `mysqlcft_utf8`.`test` (`id`, `title`, `body`) VALUES (NULL, '北京中心城区今起可无线宽带上网 奥运期间免费', '新浪科技讯 6月25日消息，北京无线城市一期网络今日起试运行，即日起北京市民和海外游客可以通过无线网络在北京中心城区接入互联网。'); INSERT INTO `mysqlcft_utf8`.`test` (`id`, `title`, `body`) VALUES (NULL, '数据库', '欢迎使用MySQL中文全文索引插件mysqlcft！'); SELECT * FROM mysqlcft_utf8.test WHERE MATCH(title,body) AGAINST ('13754元/平方米' IN BOOLEAN MODE); SELECT * FROM mysqlcft_utf8.test WHERE MATCH(title,body) AGAINST ('6月25日' IN BOOLEAN MODE); SELECT * FROM mysqlcft_utf8.test WHERE MATCH(title,body) AGAINST ('北京' IN BOOLEAN MODE); SELECT * FROM mysqlcft_utf8.test WHERE MATCH(title,body) AGAINST ('北京 宽带' IN BOOLEAN MODE); SELECT * FROM mysqlcft_utf8.test WHERE MATCH(title,body) AGAINST ('mysqlcft' IN BOOLEAN MODE); SELECT * FROM mysqlcft_utf8.test WHERE MATCH(title,body) AGAINST ('数据' IN BOOLEAN MODE); 八、性能测试报告 服务器：DELL PowerEdge 6850 (四颗双核Xeon 3.0GHz，8GB内存) 4U机架式服务器 操作系统：RedHat AS4 (x86_64位) 数据库：MySQL 5.1.25 RC + mysqlcft 1.0.0 数据表：超过80万条(807346条)记录的表，字段“id”为int类型，主键；字段“title”为varchar类型，字段“body”为text类型。“title”和“body”分别建有INDEX普通单列索引、INDEX联合索引，FULLTEXT单字段全文索引、FULLTEXT联合全文索引。 1、在字段“title”中搜索中文关键字： SELECT * FROM database.table WHERE MATCH(title) AGAINST ('朝阳区' IN BOOLEAN MODE) limit 0,30; 30 rows in set (0.04 sec) SELECT * FROM database.table WHERE title LIKE '%朝阳区%' limit 0,30; 30 rows in set (6.56 sec) SELECT * FROM database.table WHERE MATCH(title) AGAINST ('通州区' IN BOOLEAN MODE) ORDER BY id DESC limit 0,30; 30 rows in set (0.13 sec) SELECT * FROM database.table WHERE title LIKE '%通州区%' ORDER BY id DESC limit 0,30; 30 rows in set (8.15 sec) SELECT * FROM database.table WHERE MATCH(title) AGAINST ('建国门外' IN BOOLEAN MODE) ORDER BY id DESC limit 0,30; 30 rows in set (0.08 sec) SELECT * FROM database.table WHERE title LIKE '%建国门外%' ORDER BY id DESC limit 0,30; 30 rows in set (5.34 sec) SELECT * FROM database.table WHERE MATCH(title) AGAINST ('靠近通惠河' IN BOOLEAN MODE) ORDER BY id DESC limit 0,30; 4 row in set (0.06 sec) SELECT * FROM database.table WHERE title LIKE '%靠近通惠河%' ORDER BY id DESC limit 0,30; 4 row in set (12.88 sec) 2、在字段“body”中搜索中文关键字： SELECT * FROM database.table WHERE MATCH(body) AGAINST ('海淀区' IN BOOLEAN MODE) ORDER BY id DESC limit 0,30; 30 rows in set (0.23 sec) SELECT * FROM database.table WHERE body LIKE '%海淀区%' ORDER BY id DESC limit 0,30; 30 rows in set (15.71 sec) SELECT * FROM database.table WHERE MATCH(body) AGAINST ('莱镇香格里' IN BOOLEAN MODE) ORDER BY id DESC limit 0,30; 6 rows in set (0.18 sec) SELECT * FROM database.table WHERE body LIKE '%莱镇香格里%' ORDER BY id DESC limit 0,30; 6 row in set (13.34 sec) 3、在字段“title”和“body”中，搜索同时包含“西城区”和“商场”两个关键字的记录： SELECT * FROM database.table WHERE MATCH(title,body) AGAINST ('西城区 商场' IN BOOLEAN MODE) ORDER BY id DESC limit 0,30; 13 rows in set (0.27 sec) SELECT * FROM database.table WHERE title LIKE '%西城区%商场%' AND body LIKE '%西城区%商场%' ORDER BY id DESC limit 0,30; 13 rows in set (51.74 sec)