`

数据库设计规范

    博客分类:
  • SQL
阅读更多
数据库设计规范 ¶
一、命名规范 ¶
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

分享到:
评论

相关推荐

    MYSQL数据库设计规范.docx

    ### MySQL数据库设计规范详解 #### 一、概述 在当今数据驱动的世界中,数据库的设计与管理至关重要。良好的数据库设计不仅能提升系统的稳定性和可扩展性,还能优化查询性能,确保数据安全。MySQL作为一种广泛使用...

    数据库设计规范.pdf

    数据库设计规范是数据库建设的基础性文件,旨在规范数据库的设计过程,提高数据库的性能、可维护性和安全性。规范详细规定了数据库设计的总体要求、对象命名规则、程序编码要求,以及在设计过程中应避免的问题等。 ...

    数据库设计规范-编码规范.docx

    在这样的背景下,制定一套全面且实用的数据库设计规范显得尤为重要。本文档《数据库设计规范-编码规范.docx》正是为了解决这一需求而诞生。 文档的“目的”章节明确指出,规范的设立旨在统一公司软件开发过程中的...

    数据库设计规范word文档

    "数据库设计规范word文档" 数据库设计规范是指在设计数据库时,遵守的一些规则和标准,以确保数据库的结构合理、数据一致、易于维护和扩展。本文档将总结数据库设计规范的主要内容,包括数据库编码规范、字段设计...

    8数据库设计规范.doc

    《8数据库设计规范》文档是针对Oracle数据库设计的一份详细指南,旨在确保系统设计的统一、稳定和优化。本文档的编写目的是为了提供一套标准,让开发者在利用Oracle数据库进行系统设计时,能够遵循一致的规则,从而...

    1数据库设计规范.doc

    数据库设计规范是构建高效、稳定、易于理解和维护的数据库系统的关键。本文档旨在提供一套详细的指导原则,确保数据库设计的合理性和数据访问的高效性,同时也促进数据共享和编码标准化。 **第1章 目的** 数据库...

    数据库设计规范(1).pdf

    数据库设计规范是构建高效、稳定、可扩展的数据库系统的基础,它涵盖了多个方面,包括数据库策略、命名规范以及数据管理等多个关键环节。本规范旨在提供一套标准,以确保数据库的高效运行,降低维护成本,同时增强...

    7数据库设计规范.doc

    《数据库设计规范——Oracle版本》 1、目的 本文档旨在定义一套详细的Oracle数据库设计规范,为数据库的设计、规划、开发以及后期维护提供技术指导。它涵盖了从物理设计到逻辑设计,再到SQL编写等多个关键环节,...

    2数据库设计规范.doc

    保密级别: 绝密 机密 秘密 内部公开 数据库设计规范 变更记录 "版本号 "修改点说明 "变更日期 "变更人 "审批人 " "V1.0 "创建 " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " ...

    数据库设计规范(通用版).7z

    这份“数据库设计规范(通用版)”的压缩包提供了一套通用的指导原则和最佳实践,帮助开发者遵循良好的设计标准,确保数据库高效、可靠且易于扩展。 首先,数据库设计的核心在于需求分析。在设计之初,需要全面理解...

    数据库设计规范-命名规范.docx

    数据库设计规范对于任何大型系统来说都是至关重要的,它确保了数据的一致性、完整性和可维护性。本文档主要关注数据库设计中的命名规范,旨在规范化数据库的构建过程,提高开发效率,并降低后期维护的难度。 首先,...

    数据库设计规范模板.pdf

    《XXX数据库设计规范模板》是一份详尽的指导文档,旨在规范数据库的设计、开发和管理,确保系统的稳定性、可扩展性和安全性。以下是该规范的主要内容: 1. **范围**:这份标准适用于XXX,旨在规定数据库设计和编程...

    MongoDB数据库设计规范.docx

    "MongoDB 数据库设计规范" MongoDB 数据库设计规范是 MongoDB 数据库的设计和实现的重要指南。该规范旨在提供一个全面的 MongoDB 数据库设计指南,涵盖了 MongoDB 的核心优势、BSON 的优化、架构设计、适用场景、...

    SQLServer数据库设计规范.txt

    SQLServer数据库设计规范

    数据库设计规范.docx

    数据库设计规范是软件开发中的重要环节,它确保了数据库的高效、稳定和可维护性。以下是对标题和描述中提到的数据库设计规范的详细说明: 1. **字段命名规范**: - 表示是否的概念字段应以 `is_` 开头,数据类型为...

    数据库设计规范 手册 指导

    本手册将深入探讨数据库设计规范,旨在提供一个清晰的指导框架,确保数据库的命名、字段设计以及文档编制符合最佳实践。 首先,让我们关注数据库的命名规则。对象名,如表、字段、索引等,应具有清晰的含义,避免...

Global site tag (gtag.js) - Google Analytics