关于数据库Varchar字段类型长度设计问题

标签: 数据库存储oraclemysql测试
35407人阅读 评论(4) 收藏 举报
分类:

关于数据库Varchar字段类型长度设计问题


      现代数据库一般都支持CHAR与VARCHAR字符型字段类型,CHAR是用来保存定长字符,存储空间的大小为字段定义的长度,与实际字符长度无关,当输入的字符小于定义长度时最后会补上空格。VARCHAR是用来保留变长字符,在数据库中存储空间的大小是实际的字符长度,不会像CHAR一样补上空格,这样占用的空间更少。


      从以上特点来看,VARCHAR比CHAR有明显的优势,因此大部份数据库设计时都应该采用VARCHAR类型。那为什么还需要CHAR类型呢,个人认为有以下几个原因:
1、为了跟以前版本的数据库进行一个兼容,因为很久以前数据库只支持CHAR类型,有些应用的业务逻辑也只是针对CHAR类型设计的,所以数据库软件也就一直保留CHAR类型。
2、CHAR类型是定长的,一些数据库可以在每条记录中不存储字段长度信息,这样可以节省部份空间,也可以方便做一些内存对齐提高性能,但个人认为这带来的性能提升非常微小,至少ORACLE数据库是没有意义的。
3、还有说法是有些数据经常修改,长度可能变化,会引起碎片,采用CHAR就不会产生碎片,这个说法比较多,但我认为既然长度会变化,那用VARCHAR更能节省内存与存储空间来提升性能,只要数据块预留的空间没有问题,采用VARCHAR性能更好。


       对于ORACLE数据库,我找不到充足的理由来使用CHAR类型,而且CHAR还会带来讨厌的空格,有些文章说MYSQL的MYISAM存储引擎在和长度固定的情况下CHAR比VARCHAR好,这个没有测试过,不太了解。


       由于VARCHAR是变长存储,那么很多人会有疑问,比如STATUS字段定义VARCHAR(10)与VARCHAR(1000)有什么区别,反正是变长的,存储空间都一样,省得以后要加长又要改变字段定义。 下面说一下我的理解:
1、字段长度是数据库一种约束,可以保证进入数据库的数据符合长度要求,定义合理的字段长度可以减少一部份非法数据进入,比如:我们业务中STATUS只有‘NEW’,‘DELETE’,‘CLOSE’3种状态,使用VARCHAR(5)保存,这样可以有效的减少非法数据进入,定义合理的长度也可以让人容易理解字段的用途,试想一下,如果你所有的字符字段长度都是VARCHAR(4000)会是什么样的情况。


2、VARCHAR的字段长度虽然对数据存储没有太大影响,但对特定的数据库还是有一些细微差别,比如MYSQL中定义的长度如果小于255,字段长度用1个字节表示,如果超过255,字段的长度将固定用2个字节表示。如果你的业务数据最大长度只有10,但定义长度为256则每条记录会多浪费了一个字节来存储长度。ORACLE没有这样的问题,它会根据每条记录字段的实际长度动态选择长度标识。


3、字段定义的长度对索引也有较大影响。ORACLE对索引长度还是有一定限制,8i官方文档说明单条记录索引信息的长度不能超过数据块大小的40%,9i中是75%,实际上也差不多,具体可以见jametong的http://www.dbthink.com/?p=20这篇文档,里面有详细的测试结果。如果你的数据块大小是8K,那么索引字段的定义长度不能超过6398,比如,你要给表上2个VARCHAR(4000)字段建组合索引,创建时会直接报错。另外索引组织表及在线重建索引(因为中间会临时创建一个索引组织表)允许的索引信息长度更小,只能是数据块大小的40%,实际中8K的数据块大小,要使用在线重建索引,那定义的长度不能超过3215。从以上可以看出,数据块大小为8K时,设计字段时如果要定义为VARCHAR(4000),那这个字段就不能考虑建立索引,因为即使能建上,也不能做在线重定义操作,DBA要进行索引维护时只能停止应用,这将对系统的可用性产生较大影响。关于ORACLE索引长度限制测试的脚本如下:

SQL> create table test1
  2  (
  3    c1 varchar2(4000),
  4    c2 varchar2(4000),
  5    c3 varchar2(4000)
  6  )
  7  ;
 
Table created
SQL> create index test1_ind1 on TEST1 (c1);
 
Index created
SQL> alter index test1_ind1 rebuild online;
 
alter index test1_ind1 rebuild online
 
ORA-00604: error occurred at recursive SQL level 1
ORA-01450: maximum key length (3215) exceeded
SQL> create index test1_ind2 on TEST1 (c2, c3);
 
create index test1_ind2 on TEST1 (c2, c3)
 
ORA-01450: maximum key length (6398) exceeded
 
SQL> 

      关于ORACLE的索引长度还有一些特别的规则,比如自定义函数返回的字符定义长度固定是4000,所以要用自定义函数做函数索引需要特别注意一下,这可能会影响在线重建索引不能操作。
      内置函数的索引长度根据函数决定,比如UPPER这种不改变长度的就是索引字段定义的长度,SUBSTR这种会改变长度要根据函数截取长度决定。
      NUMBER类型字段的长度固定是22。
      DATA类型字段的长度固定是7。
      索引默认是升序,如果要降序建的索引长度是字段定义长度*1.5+1。


      MYSQL对索引长度限制比较复杂,每种版本及存储引擎都不一样,如下是MYSQL5.1.58测试的结果:
      INNODB的最大总长度是3072字节,单个字符字段是767字节,如果字段长度大于767则自动截取前767个字符。
      MYISAM最大总长度是1000字节,单个字符字段是1000字节。
      MEMORY的最大总长度是3072字节,单个字符字段是3072字节。


4、变长字段定义的长度虽然不会影响服务器数据空间大小,但是对于客户端的内存有影响,因为客户端在用SQL从数据库读取数据时,首先会取到字段定义的长度,然后分配足够的内存,也就是说如果你定义的字段长度是1K,实际长度是10字节,要取1K记录,那客户端会分配1MB的内存, 但只保存了10K有效数据。这将会比较严重的浪费客户端内存。特别是一些高并发或者是取大量数据的场景,容易产生内存溢出。


5、关于字段长度对齐的问题,有些设计人员喜欢定义字段的长度为4或者8的倍数,如16,32,64,128之类的,理由是可以做到内存对齐,对于这个问题我没有深入分析过,个人认为必要性不大,也没看到过这种优化能提升性能的案例。如果一个VARCHAR(1)定义为VARCHAR(4)反而浪费内存与存储,实际上我看到在ORACLE jdbc驱动中会将所有的字符类型数据保存在一个大的char[]中,把所有NUMBER与DATE类型放在另一个char[]中,这样整合后都不清楚如何内存对齐了。


      综上所述:VARCHAR类型字段长度不能随便定义,并不是越大越好,还是需要根据实际业务数据定义一个合适的长度。我个人对于一些可以完全预估的长度就按实际长度定义,比如年月、状态、标记之类的信息。对于不确定长度的业务数据如NAME、STYLE之类的信息定义一个合理值,如VARCHAR(20),VARCHAR(30) 之类 。对于描述性或备注性的信息,这些字段也确定不会有索引,长度也不可预知,所以留更大的长度,避免以后经常进行长度调整,如VARCHAR(1024),或者直接VARCHAR2(4000) 。

 我的新浪微博:http://weibo.com/yzsind

3
0

猜你在找
【直播】机器学习&深度学习系统实战(唐宇迪)
【直播】Kaggle 神器:XGBoost 从基础到实战(冒教授)
【直播回放】深度学习基础与TensorFlow实践(王琛)
【直播】计算机视觉原理及实战(屈教授)
【直播】机器学习之凸优化(马博士)
【直播】机器学习之矩阵(黄博士)
【直播】机器学习之概率与统计推断(冒教授)
【直播】机器学习之数学基础
【直播】TensorFlow实战进阶(智亮)
【直播】深度学习30天系统实训(唐宇迪)
查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:1668720次
    • 积分:20146
    • 等级:
    • 排名:第379名
    • 原创:330篇
    • 转载:53篇
    • 译文:0篇
    • 评论:2577条
    文章分类
    最新评论