数据库优化---空间换时间优化

数据库优化---空间换时间优化

在查询优化中，有一个重要的概念：

空间换取查询时间

这一理论最好的应用就是：数据仓库（OLAP）：在海量数据库里（一般是TB级）分析数据，通过对数据的ETL和计算汇总，得到有用的数据，并通过不同维度查看统计数据（一般比较少），实现上钻和下钻分析数据。

现在讨论一下在OLTP系统中空间换取查询时间常用的几个方法：

1，增加冗余表（计算汇总表）

2，增加冗余字段（包括计算字段)

3, 增加索引(包括计算索引）

4，增加索引视图（物化视图)

5，数据缓存

5.1，数据库缓存（如换成64位的系统和数据库):

5.2，程序缓存（如:hibernate中的缓存)

我们在开发系统中或多或少的使用了以上的方法，这次结合实际，讨论一下这些方法的具体使用和目的：

案例一：

我们优化一个系统，原来的开发人员，由于数据量比较大，为保证查询性能，对于办事处和分公司是实时计算，片区和总公司是非实时统计，开发人员就使用统计片区和总公司统计汇总静态表，每天晚上凌晨1点计算上一天的的汇总数据。并存放到了静态表。统计时，片区和总公司是统计静态表数据，统计速度很快。

但后来发现总公司汇总数据和分公司汇总数据不对。原来这个系统的网络管理员可以在系统里直接去改数据。这样我们的统计静态数据就不对。为何要改这个数据，因为系统bug和下面业务人员的操作不对，就直接修改数据库数据。

为了保证总公司数据和分公司汇总数据一致，后来取消了这个计算冗余表，直接实时统计。

总结：这个例子说明冗余表的利和弊，假如我们的系统数据可能更改，这时冗余表的数据就不准确了，这点要注意的。这

也是计算冗余表的一个主要问题：数据的更改，统计数据不准。

目的： 减少查询数据量（汇总字段和表）

2，增加冗余字段（包括计算字段)

案例一：

系统的一个模块，业务要求是用户输入一段日期（时间段）的投入总金额，由于业务要求计算每天的金额，这时如果没有每天的数据，则只有去查询出总数，在通过时间相减得到总天数，在计算出每天的金额。

如果数据量比较大，计算时间比较长，这时我们就在程序里做一个计算功能，把这个数据算好自己放到表里，这时就不需要统计时计算了。

总结：我们增加冗余字段，目的就是为了将后期通过数据库计算的时间细化，分散到每次插入数据时的时间，这点时间是很少的。但积累起来却是很多，特别是在后面统计和分析时就很明显。当然，我们可以细化时间和先期汇总数据，如总数。可以插入数据时，把总数计算出来，有利于减少汇总数据量。

目的：减少业务逻辑计算和汇总时间

3, 增加索引(包括计算索引）

索引是数据冗余的一种，也是将表中列的数据冗余出来，这样查询时就可以不用去查询表，同时可以使用索引数据结构快速搜索seek。

我们说的索引覆盖，就是查询中将需要的列全部冗余，通过索引来查询，而不需要去表中查询数据。

目的：查询索引，而不扫描表（尽管扫描表有时是最优的）

4，增加索引视图（物化视图）

我们说的索引视图（物化视图），就是对需要查询关联的数据或汇总数据，预先通过实体表存放起来。这样查询时可以不去关联的表（表有时可能很多，数据量比较大），去直接去查询索引视图（物化视图）。

这里，如上面“增加冗余表”，提到数据可能更改的问题，就可以用索引视图（物化视图）来实现。

当然索引视图（物化视图）有条件限制，不是所有查询都可以使用的。如sql server索引视图就只能用inner join关联，同时要有唯一的聚集索引。由于索引视图（物化视图）成本较高，一般在OLTP系统中使用较少（以大量牺牲DML时间为代价），在OLAP中使用较多。 目的：减少查询数据量

5，数据缓存

一，数据库缓存，也是一种优化方式，如将经常访问的表放到内存里，这样在内存中查询速度要比在硬盘速度快很多。一般的方法如下：

1，将32位操作系统和数据库改成64位的，提高内存使用和cpu寻址能力

2，在Oracle里可以把使用频繁但数据量比较少的表（keep cache）起来，启动时就一直放到内存中。

二，程序缓存

目前有一些缓存框架，如JBOSS Cache，hibernate缓存，Ehcache等缓存框架，可以减少查询数据库，提高速度。

三，静态变量

案例一：

我们以前做一个系统，由于没有用缓存框架，一般使用的静态基本的数据（如查询机构，车辆，人员等），启动程序时，去数据库去查询数据，放到一个List里面，然后封装了一些静态方法，这些基本信息就放到静态list里。这时可以做一些的查询，如取一些数据，不用去查询数据库了，直接调用静态方法，减少和数据库交互次数。

数据缓存目的：1，减少和数据库的交互次数

2，尽可能使用内存（数据库服务器和应用服务器）