江苏龙网

  1. 主页 > 生活百科 > >

用惨痛教训换来的156条MySQL设计规约( 二 )

MySQL设计


11.【强制】表达是与否概念的字段，使用 is _ xxx 的方式进行命名? 。
四、库设计规范
1.【推荐】数据库使用InnoDB存储引擎 。
说明：支持事务、行级锁、并发性能更好、CPU及内存缓存页优化使得资源利用率更高 。
2.【推荐】数据库和表的字符集统一使用UTF8 。
说明：utf8号称万国码，其无需转码、无乱码风险且节省空间 。若是有字段需要存储emoji表情之类的 ， 则将表或字段设置成utf8mb4，utf8mb4向下兼容utf8 。
3.【推荐】不同业务，使用不同的数据库 ， 避免互相影响 。
4.【强制】所有线上业务库均必须搭建MHA高可用架构，避免单点问题 。
五、表设计规范
1.【推荐】建表规范示例：
CREATE TABLE `student_info` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`stu_name` varchar(10) NOT NULL DEFAULT '' COMMENT '姓名',
`stu_score` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '总分',
`stu_num` int(11) NOT NULL COMMENT '学号',
`gmt_create` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`gmt_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`status` tinyint(4) DEFAULT '1' COMMENT '1代表记录有效，0代表记录无效',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_student_info_stu_num` (`stu_num`) USING BTREE,
KEY `idx_student_info_stu_name` (`stu_name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生信息表';?
2.【强制】禁止使用外键，如果有外键完整性约束，需要应用程序控制 。
3.【强制】每个Innodb 表必须有一个主键 。
说明：Innodb 是一种索引组织表，其数据存储的逻辑顺序和索引的顺序是相同的 。每张表可以有多个索引，但表的存储顺序只能有一种，Innodb 是按照主键索引的顺序来组织表的，因此不要使用更新频繁的列如UUID、MD5、HASH和字符串列作为主键，这些列无法保证数据的顺序增长 ， 主键建议使用自增ID 值 。
4.【推荐】单表列数目最好小于50 。
5.【强制】禁止使用分区表 。
说明：分区表在物理上表现为多个文件，在逻辑上表现为一个表 ， 谨慎选择分区键，跨分区查询效率可能更低，建议采用物理分表的方式管理大数据 。
6.【推荐】拆分大字段和访问频率低的字段 ， 分离冷热数据 。
7.【推荐】采用合适的分库分表策略，例如千库十表、十库百表等（建议表大小控制在2G） 。
8.【推荐】单表不超过50个int字段；不超过20个char字段，不超过2个text字段 。
9.【推荐】表默认设置创建时间戳和更改时间戳字段 。
10.【推荐】日志类型的表可以考虑按创建时间水平切割，定期归档历史数据 。
11.【强制】禁止使用order by rand() 。
说明：order by rand()会为表增加一个伪列，然后用rand()函数为每一行数据计算出rand()值 ， 基于该行排序，这通常都会生成磁盘上的临时表，因此效率非常低 。
12.【参考】可以结合使用hash、range、lookup table进行散表 。
13.【推荐】每张表数据量建议控制在500w以下 ， 超过500w可以使用历史数据归档或分库分表来实现（500万行并不是MySQL数据库的限制 。过大对于修改表结构，备份，恢复都会有很大问题 。MySQL没有对存储有限制，取决于存储设置和文件系统） 。
14.【强制】禁止在表中建立预留字段 。
说明：预留字段的命名很难做到见名识义，预留字段无法确认存储的数据类型，所以无法选择合适的类型；对预留字段类型的修改，会对表进行锁定 。
六、字段设计规范
1.【强制】必须把字段定义为NOT NULL并且提供默认值 。
说明：NULL字段很难查询优化，NULL字段的索引需要额外空间，NULL字段的复合索引无效 。
2.【强制】禁止使用ENUM，可使用TINYINT代替 。
3.【强制】禁止使用TEXT、BLOB类型(如果表的记录数在万级以下可以考虑) 。
4.【强制】必须使用varchar(20)存储手机号 。
5.【强制】禁止使用小数存储国币、使用“分”作为单位，这样数据库里就是整数了 。
6.【强制】用DECIMAL代替FLOAT和DOUBLE存储精确浮点数 。
7.【推荐】使用UNSIGNED存储非负整数 。
说明：同样的字节数 ， 存储的数值范围更大 。
8.【推荐】建议使用INT UNSIGNED存储IPV4 。
说明：用UNSINGED INT存储IP地址占用4字节，CHAR(15)则占用15字节 。另外，计算机处理整数类型比字符串类型快 。使用INT UNSIGNED而不是CHAR(15)来存储IPV4地址，通过MySQL函数.NET_ntoa和inet_aton来进行转化 。IPv6地址目前没有转化函数 ， 需要使用DECIMAL或两个BIGINT来存储 。例如:


推荐阅读


上一篇：解决 Java 打印日志吞异常堆栈的问题

下一篇：业务系统常用限流算法浅析