数据库设计规范

数据库设计规范 ¶
一、命名规范 ¶
1. 数据库、表、字段、别名规范 ¶
识别符 最大长度(字节) 允许的字符
数据库 64 [a-z_] (所有字符均小写, 字之间用 _ 分割)
表 64 [a-z_] (所有字符均小写, 字之间用 _ 分割)
列 64 [a-z_] (所有字符均小写, 字之间用 _ 分割)
索引 64 [a-z_] (所有字符均小写, 字之间用 _ 分割)
别名 255 [a-z_] (所有字符均小写, 字之间用 _ 分割)

数据库、表、列、索引、别名的命名应尽可能描述其真实的意思。
2. 统一命名 ¶

    字段

命名空间为：
数据库::表（数据库_表名）:: 字段(简)

* 名称或标题      name (char[])
* 创建时间  create_time (datetime)
* 更新时间  update_time (datetime)
* 过期时间  expire_time (datetime)
* 数据状态  status (tinyint) ''0:正常 1:隐藏''
* ID       id (int)
* IP       ip (char[19])
* fetion号   fetion (char[20])
* qq号   qq (char[20])
* msn/email msn/email(varchar[255])
* 资源文件/图片Id   resource_id (int)
* 标签  tag  (char[])
* 类型 type (tinyint)

索引 命名空间为：

index_table_field
unique_table_field
key_table_field

3. 所有日志表均以 log_ 开头 如 ：log_user_login ¶
4. 各模块表以模块名开头 如奖品：award award_exchange ¶
5. 数据库、表的备份，请使用数据库、表加备份时间 如： ¶

  数据库 '''camp camp_20091130'''
  表 '''award award_20091130'''

6. 对于与用户表关联的其它表，对于用户表的关联字段，除非有特殊需要，请使用 username 关联而不要使用 user_id 关联。因为许多查询中都使用 username，这样可以避免不必要的查询 (从 user_id 查得 username ¶
二、设计规则 ¶

    在设计过程中，应该从实际需求出发，以性能提升为根本目标来展开工作，很多时候为了尽可能提高性能，必须做反范式设计。

1. 适度冗余，让查询尽量减少 JOIN (MYSQL JOIN 性能不是很高) ¶
2. 大字段垂直分表 ¶

    大字段垂直分表简单来说就是将自己身上的字段拆分出去放到另外的表里。
    大字段一般都是存放着一些较长的 Detail 信息，如文章内容、帖子内容、产品的介绍等。
    其次是和表中的其它字段相比访问频率明显要少很多。

3. 合适的数据类型 ¶

   1. 通过选用更 "小" 的数据类型减少存储空间, 使查询相同数据需要的IO资源降低.
   2. 通过合适的数据类型加速数据的比较.
   3. 选择字段时尽量不要选用 SET, EMNUM 类型, 不便于扩展.
   4. 除了像 TEXT, BLOB, AUTO_INCREMENT 等这些列不能指定默认值的列类型之外, 应尽量为每个字段指定默认值. 这样可以增强数据的移植性和减少严格模式下出错的机会.
   5. 关联字段尽可能地建成相同列类型, 这样可以加快表关联搜索.
   6. 尽可以为每列指定 NOT NULL, 除了在确实需要 NULL 值的情况下. 这样可以减少存储空间和索引优化.
   7. 如果一个表没有像 text 这类字段，一个表尽可能用 char 替代 varchar，因为固定长度的表有更高的查询和恢复性能.
   8. 所有表、字段均应用 comment 列属性来描述此表、字段所代表的真正含义，除了意思明了的字段如： id

注意:

CHAR[M] 属于静态长度类型, 存放长度完全以字符数来计算, 所以最终的存储长度是基于字符集的, 如 latin1 其最大存储长度为 255 字节, 但是如果使用 gbk 则最大存储长度为 510 (255x2) 字节. CHAR 类型的存储特点是不管实际存放的数据多长, 在数据库中都会存放 M 个字符, 不够通过空格补上, M 默认为1. 虽然 CHAR 会通过空格补齐存放空间, 但是在访问数据时, MYSQL会忽略最的的所有空格, 所以如果实数据在最后确实需要空格, 则不能使用 CHAR 类型来存放. 在MYSQL 5.03 之前的版本中, 如果定义 CHAR时 M值超过 255, MYSQL 会自动将 CHAR 类型转换为可以存入对应数据量的 TEXT类型, 如 CHAR(1000) 会自动转换为 TEXT, CHAR(10000) 则会转为 MEDIUMTEXT. 而从 MYSQL 5.0.3 开始, 所有超过 255 的定义 MYSQL 都会直接拒绝并给出错误信息, 不再自动转换.

VARCHAR[M] 属于动态存储长度类型, 仅存储占用实际存储数据的长度. 其存放的最大长度与 MYSQL 版本有关, 在 5.0.3 之前的版本 VARCHAR 以字符数控制存储的最大长度, 最大只能存放 255 个字符, 占用存储空间的实际大小与字符集有关. 但是从 5.0.3 开始, VARCHAR 的最大存储限制已经更改为字节数限制了, 扩展到可以存放 65535 字节的数据, 不同的字符集可能存放的字符数并不一样. 也就是说, 在 MYSQL 5.0.3 之前的版本, M 所代表的是字符数, 而从 5.0.3 版本开始, M 代表字节数了. VARCHAR 的存储特点是不管设定 M 为多大值, 真正占用的存储空间只有存入的实际数据的大小, 和 CHAR 不同的是 VARCAHR 会保留存入数据最后的空格, 也就是说我们存入什么, MYSQL 返回的就是什么. 在 VARCHAR 类型字段的数据中, MYSQL 会在每个 VARCHAR 数据中使用 1 到 2 个字节来存放 VARCHAR 数据的实际长度, 当实际数据在 255 字节之内时, 会使用 1 字节来存放实际长度, 而大于 255 字节时, 则需要使用 2 字节来存放.

TINYTEXT, TEXT, MEDIUMTEXT 和 LONGTEXT 这 4 种类型同属于一种存储方式, 即动态存储长度类型, 不同的仅是最大长度的限制. 4 种类型的定义都是通过最大字符数来限制, 但它们的字符数限制实际上是可以理解为字节数限制, 因为当使用多字节字符集时, 实际能存放的字符数并没最大字符数那么多, 而是以单字节字符来计算的字符数. 此外, 由于是动态存储长度类型, 所以和 VARCHAR 一样, 每个字段数据之前都需要一个存放实际长度的空间. TINYTEXT 需要 1 个字节来存放, TEXT 需要 2 个字节, MEDIUMTEXT 和 LONGTEXT 则分别需要 3 个和 4 个字节来存放实际数据长度. 实际上, 除了 MYSQL 内嵌的最大长度限制之外, 它们还受到客户端与服务器端的网络通信缓冲区最大值 (max_allowed_packet 默认为 1M, 也就是说, MEDIUMTEXT 和 LONGTEXT 在默认情况可能存不进去值) 的限制.

这 4 种 TEXT 类型和 CHAR 及 VARCHAR 在实际使用中存在几个不一样的地方:

1. 不能设置默认值.
2. 只有 TEXT 可以使用 TEXT[M] 这样的方式通过 M 设置大小.
3. 基于这 4 种类型的索引必须指定前缀长度.

TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT 分别占用的字节为 1, 2, 3, 4, 8. INT 类型的值就上亿了.
对于 INT[M] 中 M 值的解释: 以前我遇到很多人他们认为 INT(4), INT(10) 其取值范围分别是 (-9999 到 9999), (-9999999999 到 9999999999). 这种理解是错误的. 其实对整型中的 M 值, 与 ZEROFILL 属性结合使用时, 可以实现列值等宽. 不管 INT[M] 中 M 值是多少, 其取值范围还是 (-2147483648 到 2147483647 有符号时), (0 到 4294967295 无符号时). 官方文档说明: 显示宽度并不限制可以在列内保存的值的范围, 也不限制超过列的指定宽度的值的显示. 当结合可选扩展属性ZEROFILL使用时, 默认补充的空格用零代替. 例如: 对于声明为INT(5) ZEROFILL的列, 值4检索为00004. 请注意如果在整数列保存超过显示宽度的一个值, 当MySQL为复杂联接生成临时表时会遇到问题, 因为在这些情况下MySQL相信数据适合原列宽度.如果为一个数值列指定ZEROFILL, MySQL自动为该列添加UNSIGNED属性.

三、创建索引 ¶

a. 较频繁的作为查询条件的字段应该创建索引.

b. 唯一性太差的字段不适合单独创建索引, 即使频繁作为查询条件.

唯一性太差的字段主要是指哪些呢? 如状态字段, 类型字段等这些字段中存放的数据可能总共就那么几个或几十个值重复使用, 每个值都会存在于成千上万或更多的记录中. 对于这类字段, 完全没有必要创建单独的索引. 因为即使创建了索引, MYSQL QUERY OPTIMIZER 大多数时候也不会去选择使用, 如果什么时候 MYSQL QUERY OPTIMIZER 选择了这各索引, 那么非常遗憾地告诉你, 这可能会带来极大的性能问题. 由于索引字段中每个值都会含有大量的记录, 那么存储引擎在根据索引访问数据的时候会带来大量的随机 IO, 甚至有些时候还会出现大量的重复 IO.

c. 更新百常频繁的字段不适合创建索引

索引中的字段被更新的时候, 不仅要更新表中的数据, 还要更新索引数据, 以确保索引信息是准确. 这个问题致使 IO 访问量较大增加, 不仅仅影响了更新 Query 的响应时间, 还影响了整个存储系统资源消耗, 加大了整个存储系统负载.

d. 不会出现在 WHERE 子句中的字段不该创建索引.

注意:
MYSQL 中索引的限制
1. MYISAM 存储引擎索引长度的总和不能超过 1000 字节.
2. BLOB 和 TEXT 类型的列只能创建前缀索引.
3. MYSQL 目前不支持函数索引.
4. 使用不等于 (!= 或者 <>) 的时候, MYSQL 无法使用索引.
5. 过滤字段使用函数运算 (如 abs (column)) 后, MYSQL无法使用索引.
6. Jion 语句 中 Jion 条件字段类型不一致的时候, MYSQL无法使用索引.
7. 使用 LIKE 操作的时候如果条件以通配符开始 (如 '%abc...')时, MYSQL无法使用索引.
8. 使用非等值查询的时候, MYSQL 无法使用 Hash 索引.

建表 SQL 语句示例：

CREATE TABLE `admin` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `role_id` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '所属组ID',
  `username` char(16) NOT NULL COMMENT '用户名',
  `password` char(33) NOT NULL COMMENT '密码',
  `acl` text NOT NULL COMMENT '资源控制',
  `ctime` datetime NOT NULL DEFAULT '2009-01-01 00:00:00' COMMENT '创建时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='后台管理用户表';

1.表太多怎么办？
分库
2.主键auto_increment/ID