lnmp安装MySQL中文索引插件mysqlcft提升wordpress搜索性能

  • A+
所属分类:LNMP

wordpress网站做大了以后搜索是个头疼的事,
本人网站用的1CPU1G阿里云,数据打了以后lnmp负载一直很高,
春节没事就想着怎么优化下,瓶颈出在mysql,所以从源头来处理,mysql导致云主机负载高,主要是因为搜索时进行全表检索,优化的话创建全文索引是个方法之一,所以就想着用张宴以前写的MySQL中文索引插件mysqlcft

引用张宴的话

MySQL在高并发连接、数据库记录数较多的情况下,SELECT ... WHERE ... LIKE '%...%'的全文搜索方式不仅效率差,而且以通配符%和_开头作查询时,使用不到索引,需要全表扫描,对数据库的压力也很大。MySQL针对这一问题提供了一种全文索引解决方案,这不仅仅提高了性能和效率(因为MySQL对这些字段做了索引来优化搜索),而且实现了更高质量的搜索。但是,至今为止,MySQL对中文全文索引无法正确支持。

恰好自己wordpress网站的mysql统计显示61%的搜索都是SELECT语句。。。wordpress是个好程序,但是需要自己进行diy才能满足需求
mysql mysqlcft,mysqlcft,lnmp安装MySQL中文索引插件mysqlcft提升wordpress搜索性能

下面就用这个插件来创建中文全文索引,试试效果,观察一段时间,不行的话再试试sphinx+插件WordPress Sphinx Search Plugin的方式

MySQL中文索引插件mysqlcft安装及使用
此文结合网文再经过自己实践进行了修改
环境为lnmp1.4
mysql版本为mysql Ver 14.14 Distrib 5.5.56, for Linux (x86_64) using readline 5.1

1.MySQL必须是动态编译安装的,以便加载动态链接库。如果是用的军哥lnmp一键包的话,都是动态编译安装的

2.编辑MySQL配置文件my.cnf,在[mysqld]段中加入ft_min_word_len=1(意为最小词所占字节数)

vi /etc/my.cnf

重新加载mysql配置

/etc/init.d/mysql reload


3.安装mysqlcft中文全文索引插件

先找个目录(没有的的话 根目录 mkdir tools) 下载mysqlcft中文索引插件,https://code.google.com/p/mysqlcft 有32位与64位可选
然后解压mysqlcft插件
32位https://storage.googleapis.com/google-code-archive-downloads/v2/code.google.com/mysqlcft/mysqlcft-1.0.0-i386-bin.tar.gz
64位https://storage.googleapis.com/google-code-archive-downloads/v2/code.google.com/mysqlcft/mysqlcft-1.0.0-x86_64-bin.tar.gz

4、从命令行登入MySQL服务器控制台

mysql -uroot -p -P3306 -h127.0.0.1

根据提示输入password
查看MySQL插件目录

Show variables like 'plugin_dir';

将解压后的mysqlcft.so文件复制到MySQL的插件目录下

cp mysqlcft.so /usr/local/mysql/lib/plugin/

5、再次登录mysql服务器控制台,安装mysqlcft.so插件

Install plugin mysqlcft soname 'mysqlcft.so';

查看mysqlcft.so插件是否安装成功

show plugins;

若安装成功应该是下面这样额
mysql mysqlcft,mysqlcft,lnmp安装MySQL中文索引插件mysqlcft提升wordpress搜索性能

扩展:
卸载mysqlcft.so插件

uninstall plugin mysqlcft;

注意:卸载前请先删除使用mysqlcft建立的全文索引。

6、为已经存在的表添加mysqlcft中文全文索引
创建单列全文索引

alter ignore table 数据库名.表名 add fulltext index 全文索引名(字段名) with parser mysqlcft;

创建全文联合索引

alter ignore table 数据库名.表名 add fulltext index 全文联合索引名(字段名1,字段名2) with parser mysqlcft;

创建wordpress数据库全文索引示例
(1)为wordpress的wp_posts表创建post_content,post_title全文联合索引

alter ignore table 数据库名.wp_posts add fulltext index postsfulltextindex(post_content,post_title) with parser mysqlcft;

创建成功后返回内容

Query OK, 195150 rows affected (2 min 0.38 sec)
Records: 195150  Duplicates: 0  Warnings: 0

(2)为wordpress的wp_postmeta表创建meta_value单列全文索引

alter ignore table 数据库名.wp_postmeta add fulltext index postmetafulltextindex(meta_value) with parser mysqlcft;

创建成功后返回内容

Query OK, 732376 rows affected (3 min 44.61 sec)
Records: 732376  Duplicates: 0  Warnings: 0

(3)为wordpress的wp_comments表创建comment_content单列全文索引

alter ignore table 数据库名.wp_comments add fulltext index commentsfulltextindex(comment_content) with parser mysqlcft;

创建成功后返回内容

Query OK, 16070 rows affected (1.07 sec)
Records: 16070  Duplicates: 0  Warnings: 0

扩展:
重建mysqlcft中文全文索引(索引损坏时需要用到)

REPAIR TABLE 数据库名.表名 QUICK;

查询使用样例(这个还真不知道怎么用,wordpress的搜索功能使用的查询语句恐怕修改起来就要动核心的程序了,暂时没有搞,不知道查询实现的方式,只能祈求是这样的,观察吧,如果负载降下来了,说明还是有用的,没用的话,删除索引卸载插件,然后用sphinx方案吧。。。)

Select * from table where match(title) against(‘搜索词’ in Boolean mode);

7、退出mysql

mysql > quit;

---------------------------------补充-----------------------------------
登录数据库

mysql -uroot -p -P3306 -h127.0.0.1

根据提示输入password

查看所有数据库

show databases;

进入指定数据库

use database_name;

查看所有表

show tables;

一 查看索引

查看当前数据库某个表的索引

mysql> show index from tablename;
mysql> show keys from tablename;

也可以不进入相应数据库,查看指定数据库的某个表的索引

mysql> show index from databasename.tablename;
mysql> show keys from databasename.tablename;

返回数据截图如下:
mysql mysqlcft,mysqlcft,lnmp安装MySQL中文索引插件mysqlcft提升wordpress搜索性能
参数说明:
· Table 表的名称。
· Non_unique 如果索引不能包括重复词,则为0。如果可以,则为1。
· Key_name 索引的名称。
· Seq_in_index 索引中的列序列号,从1开始。
· Column_name 列名称。
· Collation 列以什么方式存储在索引中。在MySQL中,有值‘A'(升序)或NULL(无分类)。
· Cardinality 索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。
基数根据被存储为整数的统计数据来计数,所以即使对于小型表,该值也没有必要是精确的。
基数越大,当进行联合时,MySQL使用该索引的机会就越大。
· Sub_part 如果列只是被部分地编入索引,则为被编入索引的字符的数目。如果整列被编入索引,则为NULL。
· Packed 指示关键字如何被压缩。如果没有被压缩,则为NULL。
· Null 如果列含有NULL,则含有YES。如果没有,则该列含有NO。
· Index_type 用过的索引方法(BTREE, FULLTEXT, HASH, RTREE)。
· Comment

Mysql数据库中索引类型BTREE, FULLTEXT, HASH, RTREE的区别
mysql里目前只支持4种索引分别是:full-text,b-tree,hash,r-tree
b-tree索引应该是mysql里最广泛的索引的了,除了archive基本所有的存储引擎都支持它.

1. full-text索引
full-text在mysql里仅有myisam支持它,而且支持full-text的字段只有char、varchar、text数据类型。
full-text主要是用来代替like "%***%"效率低下的问题

2. b-tree索引
b-tree在myisam里的形式和innodb稍有不同
在 innodb里,有两种形态:一是primary key形态,其leaf node里存放的是数据,而且不仅存放了索引键的数据,还存放了其他字段的数据。二是secondary index,其leaf node和普通的b-tree差不多,只是还存放了指向主键的信息.
而在myisam里,主键和其他的并没有太大区别。不过和innodb不太一样的地方是在myisam里,leaf node里存放的不是主键的信息,而是指向数据文件里的对应数据行的信息.

3. hash索引
目前我所知道的就只有memory和ndb cluster支持这种索引.
hash索引由于其结构,所以在每次查询的时候直接一次到位,不像b-tree那样一点点的前进。所以hash索引的效率高于b-tree,但hash也有缺点,主要如下:
(1)由于存放的是hash值,所以仅支持<=>以及in操作.
(2)hash索引无法通过操作索引来排序,这是因为存放的时候经过hash计算,但是计算的hash值和存放的不一定相等,所以无法排序.
(3)在组合所以里,无法对部分使用索引.
(4)不能避免全表扫描,只是由于在memory表里支持非唯一值hash索引,www.linuxidc.com就是不同的索引键,可能存在相同的hash值.
(5)当存在大量相同hash值得时候,hash索引的效率会变低.

4. r-tree索引
r-tree在mysql很少使用,仅支持geometry数据类型,支持该类型的存储引擎只有myisam、bdb、innodb、ndb、archive几种。相对于b-tree,r-tree的优势在于范围查找.

二 删除索引

DROP INDEX index_name ON talbe_name;
ALTER TABLE table_name DROP INDEX index_name;
ALTER TABLE table_name DROP PRIMARY KEY;

其中,前两条语句是等价的,删除掉table_name中的索引index_name。
第3条语句只在删除PRIMARY KEY索引时使用,因为一个表只可能有一个PRIMARY KEY索引,因此不需要指定索引名。如果没有创建PRIMARY KEY索引,但表具有一个或多个UNIQUE索引,则MySQL将删除第一个UNIQUE索引。
如果从表中删除了某列,则索引会受到影响。对于多列组合的索引,如果删除其中的某列,则该列也会从索引中删除。如果删除组成索引的所有列,则整个索引将被删除。

三 创建索引
在执行CREATE TABLE语句时可以创建索引,也可以单独用CREATE INDEX或ALTER TABLE来为表增加索引。
1.ALTER TABLE
ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。

ALTER TABLE table_name ADD INDEX index_name (column_list);
ALTER TABLE table_name ADD UNIQUE (column_list);
ALTER TABLE table_name ADD PRIMARY KEY (column_list);

其中table_name是要增加索引的表名,column_list指出对哪些列进行索引,多列时各列之间用逗号分隔。索引名index_name可选,缺省时,MySQL将根据第一个索引列赋一个名称。另外,ALTER TABLE允许在单个语句中更改多个表,因此可以在同时创建多个索引。

2.CREATE INDEX
CREATE INDEX可对表增加普通索引或UNIQUE索引。

CREATE INDEX index_name ON table_name (column_list);
CREATE UNIQUE INDEX index_name ON table_name (column_list);

table_name、index_name和column_list具有与ALTER TABLE语句中相同的含义,索引名不可选。另外,不能用CREATE INDEX语句创建PRIMARY KEY索引。

在创建索引时,可以规定索引能否包含重复值。
如果不包含,则索引应该创建为PRIMARY KEY或UNIQUE索引。
对于单列惟一性索引,这保证单列不包含重复的值。
对于多列惟一性索引,保证多个值的组合不重复。
PRIMARY KEY索引和UNIQUE索引非常类似。事实上,PRIMARY KEY索引仅是一个具有名称PRIMARY的UNIQUE索引。
这表示一个表只能包含一个PRIMARY KEY,因为一个表中不可能具有两个同名的索引。
下面的SQL语句对students表在sid上添加PRIMARY KEY索引。

ALTER TABLE students ADD PRIMARY KEY (sid);

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: