关于SQL调优与报表的性能优化

前一段时间做了某产品实时统计的报表，这周在对报表性能进行优化。

其中逐步积累了一些优化经验，总结一下记录下来，欢迎大家一起探讨。

本文仅限于探讨单个数据库的查询sql及统计报表调优，不涉及分布式数据库架构或垂直、水平扩展等知识。

(本文中的统计数据库为Mysql)

 

一、SQL调优：

1、提升统计查询性能，首当其冲当然还是对查询SQL进行优化，说到SQL调优，不得不先说一下Mysql里SQL的执行顺序。网上查了一些资料，引用如下：

from -> (on -> join ->) where -> group by -> 聚集函数计算 -> having -> 计算所有表达式 ->  order by -> limit

2、SQL调优的基本思想：

Sql所需处理的资源总量/Sql单位时间所能处理的资源量=Sql执行时间  

(引用自：http://blog.163.com/hs_admin_jsjd/blog/static/175159035201031705956983/)

所以为减少Sql执行时间：

(1)可以增大Sql单位时间所能处理的资源量。

(2)可以减少Sql所需处理的资源总量。

要实现方法(1)一般需要提升硬件处理能力或对RDBMS进行优化。因此我们这里主要讨论方法(2)。即减少Sql所需处理的资源总量，就是减少执行sql所处理的行数。

3、如何减少Sql执行时所处理的行数？

参考了这个人下面的几篇blog，在实际工作中感觉确实比较有帮助，特此记录一下。

http://hideto.iteye.com/blog/218584 SQL性能调优(共3篇)

 

二、关于索引

经过了以上的SQL调优，查询的速度应该已经提升了不少，但是随着库表中的数据日渐增多，简单的SQL调优可能已经无法满足性能的需求，此时可以通过对表添加索引来加快查询速度。本次在优化过程中同样建立了索引和组合索引。下面是一些概念：

1、什么是索引？

索引类似于书的目录，主要用于提高查询效率，也就是按条件查询的时候，先查询索引，再通过索引找到相关的数据，索引相当于记录了对某个关键词，指定到不同的文件，或者文件里的不同位置，当然索引自身也是通过文件来保存的。(本段来源于网络)

2、建立索引。

但是同样不要超出需求创建索引，DBMS在插入、更新或删除行之后必须更新索引。随着表索引的增长，DBMS要花费越来越多的时间维护索引，行的修改速度也会越来越慢。

引用一列的索引称为简单索引，引用多列的索引称为组合索引。列的顺序在组合索引中是重要的。组合索引只作用于定义它的那组列，并非分别作用于每个列或相同列地其他顺序。

以上可以参考我另外一篇blog：http://shensy.iteye.com/blog/1486869 里面记录了一些关于使用索引的知识点。

3、关于索引类型(概念总结来源于网络)。

两种基本的索引结构，也就是索引文件的保存方式：

一个是顺序索引，就是根据值的顺序排序的（这个文件里面的值，也就是为其建索引的字段值，是顺序的放在索引文件里面）。

另外一个是散列索引，就是将值平均分配到若干散列桶中，通过散列函数定位的。

顺序索引中如果被索引的字段本身按照一定的顺序排序，那么这种索引叫做聚集索引，否则叫做非聚集索引。

如果被索引的字段的每个值都有一个索引与其对应，那么这种索引叫做稠密索引，否则叫做稀疏索引。

顺序索引分为两类，单级索引（不怎么用）和多级索引（通常是B+树，大量使用）。

 

B+树最常用，性能也不差，用于范围查询和单值查询都可以。特别是范围查询，非得用B+树这种顺序的才可以了。

HASH的如果只是对单值查询的话速度会比B+树快一点，但是ORACLE好像不支持HASH索引，只支持HASH表空间。

(参考资料：http://blog.csdn.net/coolzyt/article/details/4085678)

 

三、关于统计报表的性能优化

一般来说统计报表的查询SQL计算和关联关系都比较复杂，如果真的一句Sql查询出一张报表所需要的结果集其实是很耗时的(原来自己就干过这种事,一句Sql几十行,汗...)。其实对于报表，可以采用如下方法调优：

1、分页处理：

在左连接或子查询的最内层查询中使用LIMIT限制结果集大小，最大程度的保证后面对小结果集进行操作。

2、只查询基础数据：

在统计报表的查询SQL中有很多表达式计算或者需要连接多张其它表时，可以只查出需要计算的列后使用程序在内存中计算，或者只查出关联Id后，在程序中使用该Id到另一张表里再查一次(也方便使用Cache，见下文)。这样确实比一句Sql关联出所有结果集更快。

3、使用Cache：

如果分页后的结果集数量还是比较大，例如100条记录每页。那么如果将这100条记录中的某id拿出来到另外一张关联表里查一次的话，也就需要查100次，这其实也是比较耗时间的。即使用连接池进行单表sql查询，实践发现总用时也在1秒以上，毕竟每次重新连接也要消耗时间。所以，这里可以使用一些<K,V>的缓存来减少到数据库中查询的次数，例如memcached或redis等...但是这也加大了程序的复杂度，比如设计缓存粒度的大小(对某列缓存还是对整个报表结果缓存)、同步缓存内容的时机以及失效时间等多方面的问题。不过如果使用得当的话，确实可以提升不少统计报表的查询性能。

4、尽量不使用跨库join：

在Mysql中提供了一个跨库join的功能，就是查询sql中将表名前面加数据库名：dbname.tablename的形式，即可关联另外一个数据库中的表了。这个跨库join对于那些sql简单且表数量小的查询确实很方便，但是对于数据量大的表，而且比较复杂的SQL查询就比较费时了，不建议使用。实际工作中我使用了定时程序把另外一个库表中需要的列加载到统计库的一张表内，然后再直接join统计库中的表来实现避免跨库join。

 

PS：

SQL优化续篇 http://shensy.iteye.com/blog/1887786