mysql性能优化的探讨

    最近做的在线考试系统oes，在部署的时候出现些问题，一台几年前的奔D双核PC做服务器，差是差了点，将就吧。但是300人同时在线考试，在进入考试的时候，很多被卡死，进不去，看一眼服务器的CPU占用，mysql占了99%，apache tomcat占了1%，100%！点关机重启都不搭理我。faint，都怪我，以为才几百个并发而已，mysql成名这么久，应该没那么差，所以根本没想着要loadrunner一下压力，这下可好，挂掉了。

    既然出了问题，那就分析问题、解决问题吧。试卷是考生进去的那一刹那根据组卷策略随机生成，策略比较灵活，支持难度、知识点、题型、数量等参数，原来的做法是:

select * from table1 left join table2....left join table3....
where ....
order by rand() limit n

 每一条策略都执行一个这样的sql，一共有10几条策略，试题库不算大，只有3000多题，抽出题目来后还要一一插入到考生的试卷表，每份试卷大概80多道题，完全随机组卷，每个考生生成的试卷都不一样，300个考生。情况就是这样了，先show profile一下吧，发现瓶颈在上面的那个随机抽题上，再explain一下，出现了filesort，My Gold!

    怎么办呢？先想着做多段索引，把mysql的help翻了又翻，不管怎么设置indexs，始终出现该死的filesort。后来发现，多表join查询，只要order by了，貌似是无法消除filesort的。顶你个肺，多表联合查询是必须的，否则要N+1了，那就把order by去掉吧。mysql随机那么慢，不如拿到java的List里面去随机，2、3行代码的事，这下好了，explain一下，没了filesort，show profile一下，I/O耗时成百倍的提高，总效率提高了10倍。既然java运算效率那么高，那就把所有带sortOrder字段的表查询处理下，去掉order by，利用java的collections.sort(....)来排序，只是让bean实现一下comparable接口而已，简单方便。好了，其它优化也做点，基本上是可以用了，不会卡到不动的程度。

    但是发现，还是不流畅，硬件上我不可奈何，技术上我已经黔驴技穷，现在只有改变业务流程了。300个学生同时进入考试，每个学生都要执行10几条sql来随机获取试题，然后执行80多条sql插入到试卷明细表，然后再按照试卷模板顺序执行10多条sql取出数据，中间的处理就不说了，这么繁杂的流程，又发生在几乎同一时刻，想不慢都难啊。看来得变化一下，把每个学生的试卷随机查询生成的工作放在前头，安排考试的时候就去做，等到学生过来考试，只需要查询出自己的试卷明细不就行了，好了，说干就干，这下果然有效！

    但是，接着但是，还是没有理想中的速度，7000多个考生，试卷明细表有60多万条数据，每个考生从中查询到属于自己的80多条，300个考生并发取，想快还真不容易。算了，祭出最终法宝，静态化！直接提前生成静态html试卷，这下真的是王道啊，配合以牛叉的apache，破烂PC式服务器也焕发了活力，速度那是杠杠di 哟。虽然静态化以后也带来一些麻烦和困扰，但是都是可以处理和克服的，为了速度，就算是牺牲性命也是值得的！

    好了，总结一下吧：

1、平时开发的时候多多explain和show profile，尽量优化，最大限度去掉filesort

2、order by是个很扯淡的东东，大数据量的时候禁用

3、不要给数据库太多的压力，多交给java去干吧，因为java很强悍了撒

4、靠技术和硬件解决不了的，可以考虑改变流程和规则，少花钱、多办事要成为座右铭

5、静态化是王道之剑，但是威力太大，会给自己惹麻烦，不得已时大胆为之

 

    BTW:木有UI设计人员帮忙，系统暴丑陋啊，哪位美工兄弟提携提携俺？

评论

4 楼 niumd 2011-06-18  

Don’t use ORDER BY RAND() if you have > ~2K records
楼主不听话造成滴

3 楼 魔力猫咪 2011-06-16  

其实是你执行rand() 造成的。因为你使用rand函数排序。那么首先必须全表，第二，所有记录都要计算rand。最后是大排序。当然性能很差。
这个系统的问题在于你数据库开发水平太差了。

2 楼 antonia 2011-06-16  

美工不可或缺啊

1 楼 ak121077313 2011-05-13  

order 是有问题，不知道mysql是怎么想的，试卷静态化是怎么做的？？生成html？freemark？