一、命名规范1. 数据库、表、字段、别名规范
数据库、表、列、索引、别名的命名应尽可能描述其真实的意思。 2. 统一命名
命名空间为: * 名称或标题 name (char[]) * 创建时间 create_time (datetime) * 更新时间 update_time (datetime) * 过期时间 expire_time (datetime) * 数据状态 status (tinyint) ''0:正常 1:隐藏'' * ID id (int) * IP ip (char[19]) * 资源文件/图片Id resource_id (int) * 标签 tag (char[]) * 类型 type (tinyint) 索引 命名空间为: index_table_field unique_table_field key_table_field 3. 所有日志表均以 log_ 开头 如 :log_user_login4. 各模块表以模块名开头 如奖品:award award_exchange5. 数据库、表的备份,请使用数据库、表加备份时间 如:数据库 '''camp camp_20091130''' 表 '''award award_20091130''' 6. 对于与用户表关联的其它表,对于用户表的关联字段,除非有特殊需要,请使用 username 关联而不要使用 user_id 关联。因为许多查询中都使用 username,这样可以避免不必要的查询 (从 user_id 查得 username二、设计规则
1. 适度冗余,让查询尽量减少 JOIN (MYSQL JOIN 性能不是很高)2. 大字段垂直分表
3. 合适的数据类型
4. 适度的空间换时间 比如一个查询频繁的表,如果大部分字都时静态长度的;可以全部都换成静态的长度的(静态表),以提高查询效率。 注意: 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. 不能设置默认值. TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT 分别占用的字节为 1, 2, 3, 4, 8. INT 类型的值就上亿了. 三、创建索引a. 较频繁的作为查询条件的字段应该创建索引. b. 唯一性太差的字段不适合单独创建索引, 即使频繁作为查询条件. 唯一性太差的字段主要是指哪些呢? 如状态字段, 类型字段等这些字段中存放的数据可能总共就那么几个或几十个值重复使用, 每个值都会存在于成千上万或更多的记录中. 对于这类字段, 完全没有必要创建单独的索引. 因为即使创建了索引, MYSQL QUERY OPTIMIZER 大多数时候也不会去选择使用, 如果什么时候 MYSQL QUERY OPTIMIZER 选择了这各索引, 那么非常遗憾地告诉你, 这可能会带来极大的性能问题. 由于索引字段中每个值都会含有大量的记录, 那么存储引擎在根据索引访问数据的时候会带来大量的随机 IO, 甚至有些时候还会出现大量的重复 IO. c. 更新频繁的字段不适合创建索引 索引中的字段被更新的时候, 不仅要更新表中的数据, 还要更新索引数据, 以确保索引信息是准确. 这个问题致使 IO 访问量较大增加, 不仅仅影响了更新 Query 的响应时间, 还影响了整个存储系统资源消耗, 加大了整个存储系统负载. d. 不会出现在 WHERE 子句中的字段不该创建索引. 注意: 建表 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 '0000-00-00 00:00:00' COMMENT '创建时间', PRIMARY KEY (`id`), UNIQUE KEY `username` (`username`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='用户表'; |
数据库设计规范(MYSQL、WEB)
最新推荐文章于 2024-04-04 05:22:37 发布