[转]mysql 优化心得一点点

最近网站数据量节节攀升，据BD方面通报短期内UV还要上升30%-50%。当前最突出的问题是后台内容审核系统压力太大，已经逐渐力不从心。尽管加了一些硬件但效果并不是太理想，主要还是前一段时间把工作重点都放在前端模块上了。内容管理平台的部分代码没有仔细斟酌。接下来一段时间集中精力优化后台。

首先将多表连查的SQL拿出来，拆分成单表查询。将查询出来的数据返回到页面上再用ajax得到附表中的数据。这样虽然增加了请求次数，但数据库的slow query不见了。

使用 EXPLAIN 命令逐条检查SQL语句。发现了很多 using sortfile 。说明这些地方是需要重点优化的。检查where 条件后面的字段，建立联合索引。使用 use index(`index_name`) 告诉mysql使用哪个索引进行检索

order by / group by 子句是产生slow query 的多发区。一定要检查 排序字段是否建立了索引，并且要使用 use index(`index_name`) 强制使用这个索引。

终于达到了理想的效果。以前一个分页查询要执行8秒，现在不到0.1秒。但是mysql中文关键字查询一直是优化工作的最后绊脚石。

后台内容审核工作比较特殊，他要求数据不能缓存，检索条件较多，并且需要精准匹配，因此使用Lucene作后台检索是早就被否定的方案。而mysql官方版本又不支持 中文全文检索（主要是没有解决中文分词问题）。一旦碰到标题或内容关键字检索就要使用like '%xxxx%' ，这种方式数据量小的时候还可以接受，但上了几十万数据后就明显体力不支了。

最后确定了两套可行的方案待选。

1、在需要中文关键字词检索的表中，根据要检索的字段再增加一个字词拆分字段并在这个字段上建立 FULLTEXT 类型索引。在数据写入的时候，使用一个分词算法将内容拆分成词并用空格分开，存入新增的拆分字段。检索的时候 使用 match against 对这个FULLTEXT做全文检索。

2、使用海量科技研发的mysql chinese plus 。实际上这个东东是在海量中文分词技术的基础上为mysql增加了中文字词的全文检索。试用版效果很不错。很适合做后台中文检索工作。但是价格也不低。

总结如下：

1、要把建立索引当成一种习惯。

2、善用EXPLAIN命令 分析SQL语句， 检查我们建立的索引是否命中，Extro列中是否还存在 "using sortfile" 如果有的话一定要通过调整索引或修改SQL甚至修改逻辑等方法把他消灭。否则随着数据量攀升mysql早晚会趴下。

3、要评估某字段取值在所有记录中的数量。比如一个字段A 类型为ENUM('yes','no')，表中的100万条记录有99万条记录 A的值为'yes'，那么当查询条件为A='yes' 时，查询效率主要取决于ORDER BY 子句后面的字段索引。反之，如果查询条件A='no'时，查询效率主要取决于 字段本身的索引。因此在使用 USE INDEX 是要充分考虑这个问题

4、选择合适的中文全文检索策略，能不用like就不要用Like。

 

from：http://www.wangmeng.cn/Article/DATABASE/MySQL/200705/1219.html