MySQL数据库设计规范

  • Post author:
  • Post category:mysql



数据库命名规范

1、所有数据库对象名称必须使用小写字母并用下划线分割。

2、所有数据库对象名称禁止使用MySQL保留关键字

3、数据库对象的命名要能做到见名识义,并且最好不要超过32个字符。

4、临时表必须以tmp为前缀并以日期为后缀。

5、备份库,备份表必须以bak为前缀并以日期为后缀。

6、所有存储相同数据的列名和列类型必须一致。


数据库基本设计规范

1、所有表必须使用Innodb存储引擎。

2、数据库和表的字符集统一使用UTF-8

3、所有的表和字段都需要添加注释。

4、尽量控制单表数据量的大小,建议控制在500万以内。

5、尽量做到冷热数据分离,减少表的宽度。

5.1 减少磁盘IO,保证热数据的内存缓存命中率。

5.2 有效的利用缓存,避免读入无用的冷数据。

5.3 经常使用的列放到一个表中。

6、禁止在表中建立预留字段。

6.1 预留字段的命名很难做到见名识义。

6.2 预留字段无法确认存储的数据类型,所以无法选择合适的类型。

6.3 对预留字段类型的修改,会对表进行锁定。

7、禁止在数据库中存储图片,文件等二进制数据。

8、禁止在线上做数据库压力测试。

9、禁止从开发环境,测试环境直连生产环境数据库。


数据库索引设计规范


数据库字段设计规范

1、优先选择符合存储需要的最小的数据类型。

1.1 将字符串转化为数字类型存储。

例如:INET_ATON(‘255.255.255.255’) = 4294967295

INET_NTOA(4294967295) = ‘255.255.255.255’

1.2 对于非负数据采用无符号整型进行存储。

例如:SIGNED INT -2147483648 ~ 2147483648

UNSIGNED INT 0 ~ 4294967295

1.3 VARCHAR(N) 中的N代表的是字符数,而不是字节数。

1.4 使用UTF-8存储汉字 varchar(255) = 765个字节。

1.5 过大的长度会消耗更多的内存。

2 避免使用TEXT、BLOB数据类型。

3 避免使用ENUM数据类型。

4 尽可能把所有列定义为NOT NULL。

4.1 索引NULL列需要额外的空间来保存,所以要占用更多的空间。

4.2 进行比较和计算时要对NULL值做特别的处理。

4.3 字符串存储日期类型的数据,是不正确的做法,

缺点1:无法用日期函数进行计算和比较。

缺点2:用字符串存储日期要占用更多的空间。

5 使用TIMESTAMP或DATETIME类型存储时间。

6 对于财务相关的金额类数据,必须使用decimal类型。


数据库SQL开发规范

1 建议使用预编译语句进行数据库操作。

2 避免数据类型的隐式转换。

2.1 隐式转换会导致索引失效。

3 充分利用表上已存在的索引。

避免使用双%号的查询条件。如 a like ‘%123%’.

一个SQL只能利用到复合索引中的一列进行范围查询。

使用left join或not exists来优化not in 操作。

4 避免使用子查询,可以把子查询优化为join操作

子查询的结果集无法使用索引。

子查询会产生临时表操作,如果子查询数据量大则严重影响效率。

消耗过多的CPU及IO资源。

5 避免使用JOIN 关联太多的表

每join一个表会多占用一部分内存(join_buffer_size)

会产生临时表操作,影响查询聊率。

MySQL最多允许关联61个表,建议不超过5个。

6 减少同数据库交互的次数。

数据库更适合处理批量操作。

合并多个相同的操作到一起,可以提高处理效率。

7 使用in代替or

in的值不要超过500个。

in操作可以有效的利用索引。

8 where 从句中禁止对列进行函数转换和计算

对列进行函数转换或计算会导致无法使用索引。


数据库操作行为规范