一、概述
近期在做业务优化时发现一个业务数据库当中大量的慢查询,分析原因后发现,数据库采用的是MYSQL,单表当中有150万记录,以纯文本存储为主,表设计的时候只到第一范式。对SQL语句分析后,发现主要来自于搜索功能导致数据库锁死。
二、实现过程
1、第一种优化思路,采用对硬件提升性能的方式实现,但硬件的成本实在太大了,而且就此业务的访问量而言,根本不需要这么高的配置,这很显然需要对软件部分要进行优化了。MYSQL数据库在LINUX下的优化已经到了极致,能调整的配置已经都进行了调整,全是依然无法满足业务需要。
2、第二种优化思路,修改表设计,调整程序代码。这种方式可以比较好的优化此问题,但是需要花费的代价也非常大,业务模式已经运行多年,修改需要付出的时间成本也高。
3、第三种优化思路,直接优化搜索这一个点,于是想到了全文索引,虽然MYSQL数据库也支持全文索引,但是这种应用的情况较少,而且对数据库压力也大。在PHP应用环境下,我们想到了PHP的手册,查找后发现了CoreSeek这个解决方案。当然可以采用其它的语言来解决,如Solr,Lucence,或者比较新的Es搜索解决方案,不过在本方案中我们采用了最简单的解决方案,直接部署coreseek,然后做一个小的改造即可上线。
4、直接上配置,coreseek的安装请读者自行查阅解决。
#
# Minimal Sphinx configuration sample (clean, simple, functional)
#
source main
{
type = mysql
sql_host = 127.0.0.1
sql_user = index
sql_pass =xxxxx
sql_db = xxxxxx
sql_port = 8635 # optional, default is 3306
sql_query_pre = SET NAMES UTF8
sql_query_pre = replace into tb001 select 1,max(nxuh) from tb_xinwen
sql_query_range = select 1,max(nxuh) from tb_xinwen
sql_range_step = 1000
sql_query = \
SELECT nxuh,nxuh as id,clanmmc,cbiaot,cneir,czuozh,ctup,ckeywords, UNIX_TIMESTAMP(dfab) AS dfab \
FROM tb_xinwen where lxians='Y' and nxuh>=$start and nxuh<=$end and\
nxuh<=(select na from tb001 where id=1)
sql_attr_uint = id
sql_attr_timestamp = dfab
sql_query_info_pre = SET NAMES UTF8
sql_query_info = SELECT nxuh,cbiaot,cneir,czuozh,ctup,ckeywords FROM tb_xinwen WHERE nxuh=$id
}
index main
{
source =main
path = /usr/local/coreseek/var/data/main
docinfo = extern
charset_dictpath = /usr/local/mmseg/etc/
charset_type = zh_cn.utf-8
}
source delta:main
{
sql_query_pre = SET NAMES utf8
sql_query = \
SELECT nxuh,nxuh as id,clanmmc,cbiaot,cneir,czuozh,ctup,ckeywords, UNIX_TIMESTAMP(dfab) AS dfab \
FROM tb_xinwen where lxians='Y' and nxuh>=$start and nxuh<=$end and \
nxuh>(select na from tb001 where id=1)
sql_query_post_index = replace into tb001 select 1,max(nxuh) from tb_xinwen
}
index delta:main
{
source = delta
path = /usr/local/coreseek/var/data/deltamain
}
indexer
{
mem_limit = 32M
}
searchd
{
port = 9312
log = /usr/local/coreseek/var/log/searchd.log
query_log = /usr/local/coreseek/var/log/query.log
read_timeout = 5
max_children = 30
pid_file = /usr/local/coreseek/var/log/searchd.pid
max_matches = 10000
seamless_rotate = 1
preopen_indexes = 0
unlink_old = 1
}
此处的配置采用了增量索引的方式,完成之后还需要及时的更新索引、合并索引,使索引保持同步。增加如下的定时任务配置:
* * * * * /usr/local/coreseek/bin/indexer --config /usr/local/coreseek/etc/csft.conf main --rotate #更新主索引,每天。
*/10 * * * * /usr/local/coreseek/bin/indexer --config /usr/local/coreseek/etc/csft.conf delta --rotate #更新增量索引,每10分钟。
30 * * * * /usr/local/coreseek/bin/indexer --config /usr/local/coreseek/etc/csft.conf --merge main delta --merge-dst-range deleted 0 0 --rotate #合并索引,每天凌晨3点
5、代码引用API实现全文索引。sphinxapi.php为coreseek的API文件,在安装目录下复制到程序引用目录即可,当然也可以直接去编译sphinx.so的扩展到PHP,一般情况下直接复制API文件即可。
<?php
require_once ( "./search/sphinxapi.php" );
$cl = new SphinxClient ();
$cl->SetServer ( '127.0.0.1', 9312);
$cl->SetConnectTimeout ( 1 );
$cl->SetArrayResult ( true );
//$cl->SetWeights ( array ( 100, 1 ) );
$cl->SetMatchMode ( SPH_MATCH_ALL );
$cl->SetRankingMode ( SPH_RANK_WORDCOUNT );
$cl->SetSelect ( "*" );
$cl->SetLimits ( 0, 1000, 10000, 0 ); //最大显示1000条
$cl->SetSortMode ( "SPH_SORT_ATTR_DESC" );
//$cl->SetSortMode ( SPH_SORT_EXTENDED, '@weight DESC' );
//$cl->SetSortMode ( SPH_SORT_EXPR, $sortexpr );
//$cl->SetFieldWeights(array('title'=>10,'content'=>1));
if(!isset($_GET['s']) ||empty($_GET['s'])){
exit("请输入搜索词");
}
$q1 = addslashes(htmlspecialchars(strip_tags($_GET['s'])));
$q=iconv("GB2312","UTF-8",$q1); //目前索引的时候coreseek 只支持UTF-8编码,中文数据库当中GBK存储的数据直接转码,然后用UTF8搜索
$res = $cl->Query($q, "*");
//print_r($res['matches']);
if(isset($res['matches'])&&count($res['matches'])>0){
foreach ($res['matches'] as $v)
{
$ids.=$v['id'].',';
}
$idss=substr($ids,0,-1);
$condition = "nxuh in (".$idss.")"; //数据库查询条件,索引出来的条数直接用整数的IDS来替代,大部分场景是不需要全部索引显示的,比如电子商务系统当中的垂直搜索引擎就是100页而已。
$total = count($res['matches']);
}
//echo $condition;
?>
6、目前coreseek只支持到utf8编码,所以需要在中文索引后进行转码才可以检索。
7、服务器的安全配置需要自己实现。
三、结论
在大部分情况下,搜索结果不需要展示所有的内容,比如在百度当中也最大显示大约74页左右,这也是符合日常的需求的,如果全部拉库出来就没有意义了。另外请认真阅读API文档,有更加详细的细节在其中。