数据库表结构设计原则

转载自： http://hi.baidu.com/yzx110/blog/item/0159fadc7b7839a4cd116686.html

数据库表结构设计浅谈

这篇文章如题所述，只打算谈一下数据库表本身设计，同时讲到和表结构相关的性能和扩展性问题。下面讲到的东西大多是从实际经验中总结而来，算是对这项技术的一个反思。    基本上在设计数据库表的时候，首先考虑设计要满足功能需求，这是最根本的，其次是满足性能需求，再次则是满足扩展性需求，这一点在大规模系统中是必须要考虑的。功能性需求比较容易满足，下面我主要谈谈对性能和扩展性需求的一些设计方法。     没人不想速度更快，但是怎样才能更快呢。设计高性能的表，我认为主要需要做好：设计精简合理的结构、减小数据量，具体的做法下面逐个分析。     合理利用字段类型和长度。字段类型尽可能反映真实的数据含义，满足功能外字段应该尽可能的短。    比如能用int字段的就不要用bigint，如果在某一个关系表里只有两个id字段，那么bigint类型显然比int类型的大了一倍。不同的数据库系统里面varchar和text类型在数据长度限制上不一样，性能上也不一样，选取要谨慎。标记位字段如果有bit就用bit类型，否则就用byte，用int就很浪费了（下面有一种特例）。     选取高效的主键和索引。关于主键的选取，特别需要注意，因为对表中数据的读取都直接或间接通过 主键，所以应该根据应用的特性设计满足最接近数据存取顺序的主键。例如数据读取按照r1、r2、r3的顺序，那么他们的主键也最好是1、2、3的顺序。有些人喜欢在关系表里面也另外加一个主键字段，我认为这样算是浪费空间，而用关系ID作联合主见更合理。     索引的大小基本上由字段来决定，所以需要建立索引的字段应该简化到最小。但是有些字段必须建立索引却又无法简化，这时候可以考虑用hash算法计算出较小的值作为索引。例如url字段不适合做索引，但是可以用一个url_md5字段来存储url的md5值来作为索引，有效降低键值长度。         减小数据量。除了缩小字段长度减小数据外，数据压缩也是一个行之有效的办法。目前有些数据库引擎支持自动压缩，相当方便，否则的自行通过程序压缩、解压也是可行的方案，压缩对较长的文章、帖子性能提升显著。压缩还需要注意的一点就是内容太短，压缩只会增加长度，压缩过的内容无法再压缩。     精简表结构。一个表复杂了不光处理起来更麻烦，而其性能也不好。如果一个表里面有多部分（几个字段合起来为一部分）的字段并不同时存取，那么这多部分字段应该根据存取特性分开为多个表，这样避免并发操作的锁竞争。如果实在无法再分并且还是字段众多，那么可以把描述同一个对象的字段合并成一个字段存储，有效降低字段数目，如果空字段较多时，这样更能节省资源。例如，在customer表里面company_name，company_phone等字段可以合并为company字段，当然这样做的前提是company_name字段不需要单独作为查询条件（如果使用数据库的xml技术，conpmay_name也可以作为查询条件）。     适当采用冗余字段，其实在我设计大部分表里面是没有冗余字段的，并不是说冗余字段不好，而是目前通过缓存系统可以适当代替冗余字段的好处。冗余字段主要是为了避免多次关联的查询，但是如果关联数据很容易被缓存，那么查询出主要数据后，关联数据直接从缓存中读取，这样冗余字段方案就可以被替代了。但是在缓存不利的情况下，冗余字段确实是提升性能行之有效的办法。     其实影响数据库性能的还有包括磁盘IO、内存、数据库锁、系统配置、数据库配置、CPU性能等其他因素，但是这些并不在本文范畴。在大规模系统中，除了性能，可扩展性也是设计的关键字点，而数据库表扩展性主要包含表逻辑结构、功能字段的增加、分表等。     对于表的逻辑结构我遵循的设计原则：一个表只包含一个主要实体，如果主要实体中包含从属实体数据，并且多个主要实体共享一个从属实体，则把从属实体单独设计为表，与主要实体关联，这样增加一个从属实体增加单独的表就行，不会影响以前的功能。如果主要实体不共享从属实体，把从属实体多个字段打包合并为一个字段。合并字段的方式在上面也有提及，它不仅减少字段数目，而且让在合并的字段中增加数据字段变得非常容易。     在数据库里面经常用到标记位字段，取值只有0/1（true/false），有时候一个表里有很多这样的字段，这种情况下我认为把所有标记为字段合并到一个数字字段更好，数字中的每一位就表示一个标记位，例如用一个int型字段可以表示32个标记位。这可能带来一些使用上的不便，不过却大大增加了可扩展性。例如当16个标记位字段合并到int型字段后，还留下了16位的扩展余地。并且用byte、int还是bigint可以随取所需。    增加表字段，好像也并不是难事，一条SQL而已。但是如果在Mysql里面，修改表结构后引擎会导出再导入数据，在大数据量下（比如1000w、1亿）增加字段变得几乎不可能。对于这个问题，有人喜欢提前在表里面多加一到多个保留字段，我个人比较反对这样的做法：一是扩展性有限、二是命名太奇怪、三是类型不一定合适。我的设计原则：小表（比如50w行、100MB数据以内的表）不用特别考虑此扩展性问题，设计时只需要设计符合当前需求就可以，因为即使以后对结构修改，也可以在很快的时间内完成。关系表等结构很稳定的表也不用考虑此问题。复杂的大表里，首先确定核心的业务实体字段、外键和索引，而其他的字段则根据情况包合并到一个extra（xml或者字符串类型）的字段里，这样也就可以满足了以后的扩展需求，因为字符串或者xml结构里增加数据字段是很容易的事情。     分表（非分区，分区后并不会产生多个表，在部署上和分表会有不同，并非所有的数据库版本都支持），也就是对表垂直切分，得到结构相同的多个小表，是提升大表性能的首选方案。分表最基本的方法就是，固定法：根据ID特性把表拆分成固定的N个表、动态增长法：根据ID值分成等值区间任意多表、外键划分法：根据外键值得特性划分。如果ID增长没有规律，那么分表可采用固定法，基本算法为：用ID对N取模或者获取HASH（ID）的某部分字符串作为表名的一部分。如果ID连续变化，则采用而动态增长法，基本算法为：测试单表最合理的数据行数N，然后根据N作为区间长度对ID拆分，拆分结果为1-N，N+1-2N...。外键划分法是根据外键值对表进行划分，基本的方法也就是固定法和动态增长法。不同的分表方法是由数据的特性和数据之间的关系决定的，例如需要根据URL查询到文章，由于URL是无规律的，那么分表方法可以为固定法，按照URL的MD5值对表进行划分。例如论坛的帖子可以按照论坛板块ID来分表，每个板块一个表多个板块一个表，这是外键划分法。如果论坛和帖子是多对多关系，那么帖子可以采用动态增长法分表，然后再把帖子和板块关系表采用外键划分法来分。这里描述的方法算是比较基本的方法，而真实系统中分表情况要复杂的多，例如用户表里如果根据ID分表，但是又需要根据Email/密码登录，如果有10个用户表，登录操作显然是很昂贵的，怎么办呢？分表，不是简单的事情。