在查询优化中,有一个重要的概念:空间换取查询时间
这一理论最好的应用就是:数据仓库(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,尽可能使用内存(数据库服务器和应用服务器)