wordpress网站做大了以后搜索是个头疼的事，
本人网站用的1CPU1G阿里云，数据打了以后lnmp负载一直很高，
春节没事就想着怎么优化下，瓶颈出在mysql，所以从源头来处理，mysql导致云主机负载高，主要是因为搜索时进行全表检索，优化的话创建全文索引是个方法之一，所以就想着用张宴以前写的MySQL中文索引插件mysqlcft
引用张宴的话
1 | MySQL在高并发连接、数据库记录数较多的情况下，SELECT ... WHERE ... LIKE '%...%'的全文搜索方式不仅效率差，而且以通配符%和_开头作查询时，使用不到索引，需要全表扫描，对数据库的压力也很大。MySQL针对这一问题提供了一种全文索引解决方案，这不仅仅提高了性能和效率（因为MySQL对这些字段做了索引来优化搜索），而且实现了更高质量的搜索。但是，至今为止，MySQL对中文全文索引无法正确支持。 |
恰好自己wordpress网站的mysql统计显示61%的搜索都是SELECT语句。。。wordpress是个好程序，但是需要自己进行diy才能满足需求
下面就用这个插件来创建中文全文索引，试试效果，观察一段时间，不行的话再试试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（意为最小词所占字节数）
1 | vi /etc/my.cnf |
重新加载mysql配置
1 | /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服务器控制台
1 | mysql -uroot -p -P3306 -h127.0.0.1 |
根据提示输入password
查看MySQL插件目录
1 | Show variables like 'plugin_dir'; |
将解压后的mysqlcft.so文件复制到MySQL的插件目录下
1 | cp mysqlcft.so /usr/local/mysql/lib/plugin/ |
5、再次登录mysql服务器控制台，安装mysqlcft.so插件
1 | Install plugin mysqlcft soname 'mysqlcft.so'; |
查看mysqlcft.so插件是否安装成功
1 | show plugins; |
若安装成功应该是下面这样额
扩展：
卸载mysqlcft.so插件
1 | uninstall plugin mysqlcft; |
注意：卸载前请先删除使用mysqlcft建立的全文索引。
6、为已经存在的表添加mysqlcft中文全文索引
创建单列全文索引
1 | alter ignore table 数据库名.表名 add fulltext index 全文索引名(字段名) with parser mysqlcft; |
创建全文联合索引
1 | alter ignore table 数据库名.表名 add fulltext index 全文联合索引名(字段名1,字段名2) with parser mysqlcft; |
创建wordpress数据库全文索引示例
（1）为wordpress的wp_posts表创建post_content,post_title全文联合索引
1 | alter ignore table 数据库名.wp_posts add fulltext index postsfulltextindex(post_content,post_title) with parser mysqlcft; |
创建成功后返回内容
1 2 | Query OK, 195150 rows affected (2 min 0.38 sec) Records: 195150 Duplicates: 0 Warnings: 0 |
（2）为wordpress的wp_postmeta表创建meta_value单列全文索引
1 | alter ignore table 数据库名.wp_postmeta add fulltext index postmetafulltextindex(meta_value) with parser mysqlcft; |
创建成功后返回内容
1 2 | Query OK, 732376 rows affected (3 min 44.61 sec) Records: 732376 Duplicates: 0 Warnings: 0 |
（3）为wordpress的wp_comments表创建comment_content单列全文索引
1 | alter ignore table 数据库名.wp_comments add fulltext index commentsfulltextindex(comment_content) with parser mysqlcft; |
创建成功后返回内容
1 2 | Query OK, 16070 rows affected (1.07 sec) Records: 16070 Duplicates: 0 Warnings: 0 |
扩展：
重建mysqlcft中文全文索引（索引损坏时需要用到）
1 | REPAIR TABLE 数据库名.表名 QUICK; |
查询使用样例（这个还真不知道怎么用，wordpress的搜索功能使用的查询语句恐怕修改起来就要动核心的程序了，暂时没有搞，不知道查询实现的方式，只能祈求是这样的，观察吧，如果负载降下来了，说明还是有用的，没用的话，删除索引卸载插件，然后用sphinx方案吧。。。）
1 | Select * from table where match(title) against(‘搜索词’ in Boolean mode); |
7、退出mysql
1 | mysql > quit; |
———————————补充———————————–
登录数据库
1 | mysql -uroot -p -P3306 -h127.0.0.1 |
根据提示输入password
查看所有数据库
1 | show databases; |
进入指定数据库
1 | use database_name; |
查看所有表
1 | show tables; |
一 查看索引
查看当前数据库某个表的索引
1 2 | mysql> show index from tablename; mysql> show keys from tablename; |
也可以不进入相应数据库,查看指定数据库的某个表的索引
1 2 | mysql> show index from databasename.tablename; mysql> show keys from databasename.tablename; |
返回数据截图如下:
参数说明:
· 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的优势在于范围查找.
二 删除索引
1 2 3 | 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索引。
1 2 3 | 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索引。
1 2 | 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索引。
1 | ALTER TABLE students ADD PRIMARY KEY (sid); |
评论1