mysql数据库查询优化

来源：liangbing 的博客

 

上两周一直想办法提高查询速度，取得一点效果，解决了部分问题，记下来以便将来自己查看。

 

由于公司没有专门的DBA，我自己对mysql数据库也不是很熟悉，而且这个JAVA开发的网络审计系统的管理系统，是经过了N多人几年时间的修修改改，今天到我们手里，要改成能支持大流量情况的版本，所以对我们这个只有几个人的JAVA组来说，确实是个难题。

 

这个大流量的情况在以前的文章里也提到过，就是要支持每秒钟处理1G左右的网络数据包，HTTP协议的数据包最多，因此HTTP协议分析模块的流水 日志表记录最大，据估算可能到达一天4000万条记录，采用一天一张表，那也是很大的，我看了.MYD文件大小，已经是8G多了。

 

而我们管理系统查询日志记录时，对好几个字段都要进行条件查询，而且有几个字段长度达到256，在8G这么大的表里查询一个字符串，如果找不到，那 必定从头要查到尾，速度慢得根本受不了。客户还要好几个字段一起设置条件来查询，这样基本上是二三十分钟都出不来，系统可用性极差。

 

我采用的方法是以测试为主，同时看JAVA代码，通过Log4j和Perf4j日志，看每个sql语句使用的时间，寻找性能瓶颈，然后有的放矢地进行优化。

 

对查询最有效果的优化，自然是建立索引了，ID自然是自增、主键，这个前人已经做了；从where语句分析，时间字段作为查询条件很多，时间是8字 节，而且不重复，设置索引比较适合。我把时间设置为索引，有一点效果，但不大，估算一下：8 * 4000 0000 = 320 000 000 字节，4000万记录的表仅仅时间一个字段的索引将是320M，这还仅仅是我们上百张表的一张表而已（客户要求我们至少保存3个月记录）。

 

建立索引能起到一定作用，但还是解决不了我们的问题。物理表建立不能再缩短时间了，因为一天一张表，3个月就91~92张表，30个协议模块就得2700多，这仅仅是协议流水日志表，还有其它表呢。

 

也不能把客户要求做成条件的字段都设置成索引，那索引表将和原表差不多大，索引就失去意义了。在数据库本身上优化，想去想来实在一下子想不到好办法，感觉数据量大了，就算在Oracle上也没有什么神奇办法吧。

 

我最后采用分段查询的方法，就是4000万条数据，我不管你设置什么条件来查询，我都是平均划为成N段来查询，比如400万为一段，在页面上提供一 个下拉单：0~400万，400~800万，…，3600~4000万，虽然查询比较麻烦一点，但每段查询的速度大大提高，控制在30秒左右，牺牲一些可 用性，总比30分钟还查不出来好吧。

 

流水日志可以采用分段查询解决，但客户要求的各种统计呢，这不能说分段统计，别人要统计2天的，你分开是不行的。

 

以前已经采用了一次预统计，预先定时在后台对流水日志表进行统计一次，保存到预统计表，等用户来查询时，从预统计表进行各种查询—-这个做法好，不得不夸下前任开发人员。

 

但现在形势不同了，因为预统计表是采用一个月一张的，就现在流水日志表的规模，那预统计表可能一张表超过4000万，具体看客户网络数据的分布情况，不好估计。

 

最后我和同事们对统计模式详细分析，一个同事提出再在预统计表基础上进行二次预统计，我们估算了一下，基本上等用户来查询时，所面对的表已经很小了，最多几千条记录，很快了。

 

解决统计查询过程中，让我体会到详细分析业务流程细节，作出相应的优化，有时是可以解决问题的。

总体上来说，对数据库查询的优化，我们采取了一些常规的优化之后，如果还没有取得想要的效果，我们有时候不必硬碰硬去优化查询本身，改变一下使用模式，找找业务处理流程是否还有可修改的，说不定就轻松解决了存在的难题。

 

还有就是主管要把整个开发组积极性调动起来，大家一起测试、分析、想办法、验证，最后一致确定一个可行的方案，然后大家分头去不打折扣的实现。