大数据的存储和查询优化总结

最近的项目里需要对大数据量的数据进行存储和查询, 每天的基础数据大概在500w~1500w之间,  每1000w的数据大概会占用1.5GB~2GB的物理存储, 这对我们的数据库设计, 程序和服务器都是一个不小的挑战.  数据库我们用的是DB2, 下面是一些经验总结(不断更新中..):

1. 在大数据量的情况下,  如果 join的表只是为了数据过滤, 则应该考虑用子查询替换join

2. 避免在索引键上做计算, 即便这个计算很简单的, 因为如果索引键上加上计算的话, 将不能使用索引, 严重影响查询效率

3.  索引排序的选择, 主要考虑查询和插入的性能. 如果一个列在程序里是成不断增长的, 比如记录的创建时间, 则应该考虑用倒序(desc), 这样在插入数据时, 新索引的建立不会影响到之前索引的位置. 所以, 主键生成的策略也应该考虑到这一点, 如果用UUID的话, 因为字符串并不能保证顺序, 左右在插入的时候, 对索引的结构影响比较大; 而自动增长的主键则会避免这个情况, 当是尽量避免使用纯sequence, 因为这样既不方便数据的移植, 也会有seqiemce循环的问题; 时间格式加上sequence混合生成主键, 应该是一个不错的解决方案. 时间可以用yyyyMMddhh格式, 这样的主键也可以比较直观的看出记录的创建时间.

3. 把能够通过索引迅速缩小查询范围的条件放到前面

4. 使用ike： like 'x%' 将会使用索引，而like '%x'将不能使用索引，而且因为like检索时, 如果前面是固定的字符, 那么, 就可以跟索引键做对比, 如果前面是不固定的字符(比如%), 则无法利用索引. 且like的检索时间与字符串长度呈线性关系，使用VARCHAR代替CHAR可以去掉多余的空格，会加快检索

5. 如果可以, 使用 fetch first only 或 limit 限制结果集大小, 这样在大数据量的情况下, 可以更快的返回数据.

6. 在一开始做大表设计的时候, 就要充分考虑表的分区和扩展, 比如使用时间字段作为分区字段. 分区字段要尽量保证数据的均匀, 避免出现数据倾斜. 当然如果历史数据, 不经常用到的话, 还是优先考虑使用历史表来保存历史记录.

7. 如果查询和更新都比较频繁的话, 可以考虑使用数据库读写分离技术的, 不过如果查询对实时性要求比较高的话, 这种查询还是应该在主节点(复制插入)上执行. 

8. 大数据的删除. 主表上为了提高查询性能, 用了大概5-6个索引字段, 导致在删除这个表的数据时, 异常的慢, 即使删除一条也需要大概20-30秒. 而我们有时候要删除的数据是千万级的, 所以普通的delete几乎无法工作. 解决这个问题, 我们换了一种思路, 用update语句来将需要删除的数据与正常业务数据隔离, 比如将需要删除的数据的业务时间改为2000年前或者如果专门有的是否删除字段就更好了. 然后删除的任务就可以慢慢来做了. 正好我们的表分区是通过业务时间来做的, 这个时候, 将2000年前的数据通过分离表分区来独立出来, 再把这个表drop掉, 数据就删除了.

 

评论

1 楼 ljl961890233bear 2014-07-28  

这也叫大数据？也就是SQL优化的一半方法、。。。。