MySQL数据库设计SQL规范.doc

MySQL数据库设计 SQL规范 1 命名规范 1、库名、表名、字段名必须使用小写字母并采用下划线分割； 2、库名、表名、字段名支持最多 32 个字符，易于辨识以及减少传输量不要超过 32； 3、库名、表名、字段名禁止使用 MySQL保留关键字； 4、临时库、临时表名必须以 tmp 为前缀并以日期为后缀； 5、备份库、备份表名必须以 bak 为前缀并以日期为后缀； ---------------------------------------------------------------- 2 基本规范 1、使用 INNODB存储引擎 5.5 以后的默认引擘，支持事务，行级锁，更好的恢复性，高并发下性能更好，对多核， 大内存， ssd 等硬件支持更好； 2、表字符集使用 UTF8 使用 utf8 字符集，如果是汉字，占 3 个字节，但 ASCII 码字符还是 1 个字节；统一，不 会有转换产生乱码风险； 3、所有表都需要添加注释； 4、不在数据库中存储图片、文件等大数据； 5、禁止在线上做数据库压力测试； 6、禁止从测试、开发环境直连线上数据库； ----------------------------------------------------------------- 3 库表设计规范 1、尽量避免使用分区表 MySQL的分区表实际性能不是很好。 2、拆分大字段和访问频率低的字段，分离冷热数据 3、采用合理的分库分表策略，推荐使用 HASH进行分表，表名后缀使用十进制数，下标从 0 开始 首次分表尽量多的分，避免二次分表，二次分表的难度和成本较高 4、按日期时间分表需符合 YYYY[MM][DD][HH格] 式 5、单表字段数控制在 20 个以内 6、一条完整的建表语句中应包含必要的字段、主键、合理的索引（综合代码中所有的条 件语句创建合理的索引，主键必须要有） ------------------------------------------------------------------- 4 索引设计规范 索引是一把双刃剑，它可以提高查询效率但也会降低插入和更新的速度并占用磁盘空间 1、单张表中索引数量不超过 5 个； 2、单个索引中的字段数不超过 5 个； 对字符串使用前缀索引，前缀索引长度不超过 10 个字符；如果有一个 CHAR(200)列，如 果在前 10 个字符内，多数值是惟一的，那么就不要对整个列进行索引。对前 10 个字符进 行索引能够节省大量索引空间，也可能会使查询更快； 3、表必须有主键，不使用 UUID、MD5、HASH作为主键，尽量不选择字符串列作为主键； 主键建议选择自增 id ； 4、创建复合索引时区分度较大的字段放在最前面；不在低区分度的字段上创建索引，例 如“性别”； 5、避免冗余或重复索引 合理创建联合索引（避免冗余）， index(a 、b、c) 相当于 index(a) 、index(a 、b)、 index(a 、、b、c) ； 6、索引不是越多越好，按实际需要进行创建 每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能 7、不在索引列进行数学运算和函数运算； 8、尽量不要使用外键 外键用来保护参照完整性，可在业务端实现，对父表和子表的操作会相互影响，降低可用 性； 9、不使用%前导的查询，如 like “%xxx”，无法使用索引； 10、不使用反向查询，如 not in / not like 无法使用索引，导致全表扫描 全表扫描导致 buffer pool 利用降低 --------------------------------------------------------------- 5 字段设计规范 1、尽可能不要使用 TEXT、BLOB类型 删除这种值会在数据表中留下很大的 空洞，可以考虑把 BLOB或 TEXT列分离到单独的表 中 2、用 DECIMAL代替 FLOAT和 DOUBLE存储精确浮点数 浮点数相对于定点数的优点是在长度一定的情况下，浮点数能够表示更大的数据范围；浮 点数的缺点是会引起精度问题 3、将字符转化为数字 4、使用 TINYINT 来代替 ENUM类型 5、字段长度尽量按实际需要进行分配，不要随意分配一个很大的容量 VARCHAR(N，) N表示的是字符数不是字节数，比如 VARCHAR(255，) 可以最大可存储 255 个 汉字，需要根据实际的宽度来选择 N； VARCHAR(N，) N尽可能小，因为 MySQL一个表中所有的 VARCHA字R段最大长度是 65535个 字节，进行排序和创建临时表一类的内存操作时，会使用 N的长度申请内存； 6、如果可能的话所有字段均定义为 not null 7、使用 UNSIGNED存储