Infobright的数据类型以及优化相关

在前面的章节中,我们已经简单介绍了数据库仓库Infobright的安装和启动,数据导入导出的介绍,在本章节中我们着重介绍下Infobright的数据类型,尽管Infobright里面支持所有的MySQL原有的数据类型。但是Integer类型比其他数据类型更加高效,所以在建表的时候要尽可能使用以下的数据类型:

TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT
DECIMAL(尽量减少小数点位数)
DATE ,TIME

效率比较低的、不推荐使用的数据类型有:
BINARY VARBINARY
FLOAT
DOUBLE
VARCHAR
TINYTEXT TEXT
针对这几个字段,在Infobright数据类型使用的时候,总结出一些经验和注意点:

(1)Infobright的数值类型的范围和MySQL有点不一样,比如Infobright的Int的最小值是-2147483647,而MySQl的Int最小值应该是-2147483648。其他的数值类型都存在这样的问题。

(2)能够使用小数据类型就使用小数据类型,比如能够使用SMALLINT就不适用INT,这一点上Infobright和MySQL保持一致。

(3)避免效率低的数据类型,像TEXT之类能不用就不用,像FLOAT尽量用DECIMAL代替,但是需要权衡毕竟DECIMAL会损失精度。

(4)尽量少用VARCHAR,在MySQL里面动态的Varchar性能就不强,所以尽量避免VARCHAR。如果适合的话可以选择把VARCHAR改成CHAR存储甚至专程INTEGER类型。VARCHAR的优势在于分配空间的长度可变,既然Infobright具有那么优秀的压缩性能,个人认为完全可以把VARCHAR转成CHAR。CHAR会具有更好的查询和压缩性能。

(5)能够使用INT的情况尽量使用INT,很多时候甚至可以把一些CHAR类型的数据往整型转化。比如搜索日志里面的客户永久id、客户id等等数据就可以用BIGINT存储而不用CHAR存储。其实把时间分割成year、month、day三列存储也是很好的选择。在我能见到的系统里面时间基本上是使用频率最高的字段,提高时间字段的查询性能显然是非常重要的。当然这个还是要根据系统的具体情况,做数据分析时有时候很需要MySQL的那些时间函数。

(6)varchar和char字段还可以使用comment lookup,comment lookup能够显著地提高压缩比率和查询性能。
Infobright执行查询语句的时候,大部分的时间都是花在优化阶段。Infobright优化器虽然已经很强大,但是编写查询语句的时候很多的细节问题还是需要程序员注意。这里涉及的内容比较多了,总结如下:

  • 尽量不适用or,可以采用in或者union取而代之。
  • 减少IO操作,原因是infobright里面数据是压缩的,解压缩的过程要消耗很多的时间。
  • 查询的时候尽量条件选择差异化更明显的语句
  • Select中尽量使用where中出现的字段。原因是Infobright按照列处理的,每一列都是单独处理的。所以避免使用where中未出现的字段可以得到较好的性能。所以select count(*) from table的速度要比select count(*) from table where condition的速度要快,同时查询数据时尽量使用 select count(game_code) from game_log where game_code=’qjp’,而不是 select count(uid) from game_log where game_code=’qjp’,前者使用同一列而后者要用两列计算,解压缩
  • 限制在结果中的表的数量,也就是限制select中出现表的数量。
  • 尽量使用独立的子查询和join操作代替非独立的子查询
  • 尽量不在where里面使用MySQL函数和类型转换符
  • 尽量避免会使用MySQL优化器的查询操作
  • 使用跨越Infobright表和MySQL表的查询操作
  • 尽量不在group by 里或者子查询里面使用数学操作,如sum(a*b)。
  • select里面尽量剔除不要的字段。尽可能少用select *
  • 有关varchar和char的区别,以及怎么选择,可以参考这篇文章