实践mysql数据库优化

先说下目前的架构

https://static.ffeeii.com/ffeeii.com/file/2016/06/3824eb95c7f706dc0301fe348d783704.png

二台服务器和db服务器的配制都是4核8G,两台web服务器搭配了负载均衡,保证机器可以切换,

按照以上的配制,支撑百万级的数据也是可以做到的。

按照经验来说,后端开发语言PHP一般没有性能问题,出问题都是在Mysql,对于mysqlslow 慢查询的日志必须开启,并且认为超过1秒的都算是慢查询,提前布局,不要把慢查询设置成3秒才是慢查询,超过1秒就算是慢的。

以下是项目中实践中总结出来的mysql优化经验:

https://static.ffeeii.com/ffeeii.com/file/2016/06/445ec304364609c2e27703eea42aeb82.png

1、尽可能的采用任务脚本;

场景:专家列表的排序,排序计算的因子有订单量、好评率、昨日活跃度、工作年限等,有人会说,现在搜索引擎会方便,我们技术强,可以搞个搜索引擎出来。这里,在人员有限,不需要花大气的做法就是任务脚本,任务脚本单独计算专家列表每个要排序的因子,并单独存一张表,这样排序的时候只需要根据这张表的数据进行排序,不需要进行联表实时查询等。

2、索引,老生常谈,大家google;

3、sql拆分查询;

何为拆分查询,将要联表查询的数据库,改为一个个的查询。比如

SELECT * from  info LEFT JOIN  login on info.uid=login.uid where login.uid=10

可以将这条sql分拆为两条

SELECT * from  info where info.uid=10;
SELECT * from  info where login.uid=10;

 不联表查询,这样单条的sql性能很好。

ps,不能代表所以联表查询的都要用此办法。

4、sql语句限制查询范围

这个开发比较常见,比如网站最受欢迎产品、最新的用户评论。一般的sql语句的写法是这样

SELECT * from  comment  ORDER BY id desc limit 10;

大家按照这个写法多少年了,因为数据量少,这个看不了来问题,一量数据到了百万级别,

发现这样一个普通的查询都很慢,然后讨论开始怎么优化,因为数据库到了百万,先加机器,

这样有一个最直接有效的sql查询

SELECT * from  comment where id>1000000  ORDER BY id desc limit 10;

限制id的查询范围,性能马上提升N倍,这个需要开发定期维护这个值,比如到了二百万,就是

SELECT * from  comment where id>2000000  ORDER BY id desc limit 10;

5、空间换时间;

一般在设计数据库的时,讲究一致性,比如用户名,订单。在设计订单的时候,订单只存用户的id,

但这种作法虽然在设计上面是规范了,但是性能比较差,在订单中,经常的一个操作就按照用户名查询查询,这样每次就需要进行联表查询,如果在订单中增加一个用户名字段,这样岂不是很好。减少了联表查询的问题。 

6、mysql slow 神器,自行发掘。

在整个开发过程中,一定要有性能优化的思想,一定会到时的节省而在后面加倍的偿还回来。

我们开发团队在项目中已积累了很多优化的办法,欢迎大家讨论学习。

附:

mysql设计规范

mysql语句规范