电商网站分析,网站推广方案注意事项?,html转wordpress教程,软件开发模型的v模型图MySQL在高并发连接、数据库记录数较多的情况下#xff0c;SELECT ... WHERE ... LIKE %...%的全文搜索方式不仅效率差#xff0c;而且以通配符%和_开头作查询时#xff0c;使用不到索引#xff0c;需要全表扫描#xff0c;对数据库的压力也很大。MySQL针对这一问题提供了一…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已经够用。#p#分页标题#e#Mysqlcft 网址http://code.google.com/p/mysqlcft/Mysqlcft 作者张宴一、MySQL中文全文索引插件mysqlcft的特点1、优点①、精准度很高采用自创的“三字节交叉切分算法”对中文语句进行分割无中文分词词库搜索精准度远比中文分词算法高能达到LIKE %...%的准确率。②、查询速度快查询速度比LIKE %...%搜索快350倍文章末尾有测试结果③、标准插件式以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数据文件的25倍。二、mysqlcft的核心思想──“三字节交叉切分算法” 注本文以07数字序号代表“英文”、“数字”和“半个汉字”以便说明。1、按三字节对中文语句进行切分建立全文索引例如“全文索引”或“1台x光机”四个字会被交叉分拆为6份建立反向索引012 123 234 345 456 5672、按三字节对搜索的关键字进行切分在全文索引中找出对应信息例①搜索关键字“文索”用数字序号表示就是“25”那么它将被切分成234 345这样就与全文索引对上了。例②搜索关键字“x光机”用数字序号表示就是“37”那么它将被切分成345 456 567这样也与全文索引对上了。例③搜索关键字“1台 光机”用数字序号表示就是“02”和“47”那么它将被切分成012 456 567这样多关键字搜索也与全文索引对上了。三、编译安装MySQL(如果已经装有不是静态编译安装的MySQL 5.1.22 RCMySQL 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.gzcd mysql-5.1.25-rc/./configure --prefix/usr/local/mysqlcft/ --without-debug --enable-assembler --with-extra-charsetsall --with-pthread --enable-thread-safe-clientmake make install/usr/sbin/groupadd mysql/usr/sbin/useradd -g mysql mysqlchmod w /usr/local/mysqlcftchown -R mysql:mysql /usr/local/mysqlcft2、创建MySQL数据文件存放目录/mysql/3306mkdir -p /mysql/3306chmod w /mysql/3306chown -R mysql:mysql /mysql/3306mkdir -p /mysql/3306/datachmod w /mysql/3306/datachown -R mysql:mysql /mysql/3306/datachown -R mysql:mysql /mysql#cp support-files/my-medium.cnf /mysql/3306/my.cnfcd ../3、创建配置文件/mysql/3306/my.cnfvi /mysql/3306/my.cnf输入以下内容(注意必须设置ft_min_word_len 1)[client]#password your_passwordport 3306socket /mysql/3306/mysql.sockdefault-character-set gbk[mysqld_safe]datadir /mysql/3306/datalog-error /mysql/3306/mysql_error.logpid-file /mysql/3306/mysql.pid[mysqld]port 3306socket /mysql/3306/mysql.sockdefault-character-set gbk#init_connect SET NAMES gbkskip-locking#skip-slave-startkey_buffer 512Mmax_allowed_packet 2Mtable_cache 1024sort_buffer_size 32Mread_buffer_size 2Mread_rnd_buffer_size 32Mmax_length_for_sort_data 64myisam_sort_buffer_size 128Mthread_cache 8query_cache_size 64M# Try number of CPUs*2 for thread_concurrencythread_concurrency 8#skip-name-resolve#p#分页标题#e#set-variable max_connections1000open_files_limit 51200ft_min_word_len 1low_priority_updates 1slave-skip-errors 1032,1062,126server-id 9#master-host host#master-user user#master-password password#master-port 3306#replicate-do-db db1#replicate-do-db db2[mysqldump]quickmax_allowed_packet 16M[mysql]no-auto-rehash# Remove the next comment character if you are not familiar with SQL#safe-updates[isamchk]key_buffer 256Msort_buffer_size 256Mread_buffer 2Mwrite_buffer 2M[myisamchk]key_buffer 256Msort_buffer_size 256Mread_buffer 2Mwrite_buffer 2M[mysqlhotcopy]interactive-timeout附MySQL配置文件在全文索引应用中的优化[mysqld]# key_buffer 指定用于索引的缓冲区大小在全文索引中增加它可得到更好的索引处理与查询性能key_buffer 512M# sort_buffer_size 为查询排序时所能使用的缓冲区大小全文索引的SQL语句之后通常会使用ORDER BY排序增加它可以加快SQL语句执行时间。该参数对应的分配内存是每连接独占100个连接使用的内存将是32M*1003200Msort_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 14、以mysql用户帐号的身份建立数据表/usr/local/mysqlcft/bin/mysql_install_db --defaults-file/mysql/3306/my.cnf --basedir/usr/local/mysqlcft --datadir/mysql/3306/data --usermysql --pid-file/mysql/3306/mysql.pid --skip-locking --port3306 --socket/mysql/3306/mysql.sock5、启动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#p#分页标题#e#四、安装mysqlcft中文全文索引插件1、从命令行登入MySQL服务器/usr/local/mysqlcft/bin/mysql -u root -p -S /mysql/3306/mysql.sock2、查看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.gztar zxvf mysqlcft-1.0.0-i386-bin.tar.gzmkdir -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.gztar zxvf mysqlcft-1.0.0-x86_64-bin.tar.gzmkdir -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;#p#分页标题#e#③、查看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,#p#分页标题#e#body mediumtext,PRIMARY KEY (id),FULLTEXT KEY title_body (title,body) WITH PARSER mysqlcft) ENGINEMyISAM DEFAULT CHARSETlatin1;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) ENGINEMyISAM DEFAULT CHARSETgbk;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);#p#分页标题#e#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) ENGINEMyISAM DEFAULT CHARSETutf8;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.0GHz8GB内存) 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;#p#分页标题#e#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)
