数据库建表规范（摘自rrc-wiki）

转载

一个考虑转行的程序猿 2021-05-27 15:58:58

文章标签 数据库建表规范 文章分类 MySQL 数据库

1 基础要求

以下为一般业务场景的通用要求

1.1 使用innodb引擎，字符集编码为UTF-8；

1.2 有自增主键，类型建议为bigint；

1.3 表数据的增删改，不使用并且依赖外键，触发器，存储过程；

1.4 非空约束必须为not null；

2 字段要求

2.1 不使用enum枚举类型，可用int，varchar等替代；

2.2 精确的数值计算使用decimal类型；

2.3 字符小于255并且长度较固定时，使用char类型，其它情况使用varchar类型；

~~2.4 时间字段，createtime updatetime等unix时间戳字段直接使用int类型，日期优先使用timestamp类型，需要较大时间跨度的情况下再使用datetime；~~

createtime updatetime不推荐使用int类型，可读性太差，除非性能可能成为瓶颈

3 索引要求

3.1 索引根据实际情况优化，原则上索引的数量不超过字段数的20%；

3.2 根据索引覆盖，最左原则等原则建索引；

3.3 有唯一语义的情况下使用uniqe索引；

3.4 区分度不高的情况下，不必建索引(依据：过滤后的结果集/总量 > 1/10)

例如：需要查询某个电话从某个product进来的线索详情，只需要在phone建索引，不需要phone+product的联合索引，因为product区分度低

4 变更要求

对于已经上线的表进行变更时要考虑对线上业务的影响；

4.1 所有的表结构的操作都需要在线下进行测试；(通过阿里云创建临时库，进行测试，估计运行时间)

4.2 对于牵涉到删除索引的操作，需要确认索引缺失可能造成的查询性能下降；

4.3 对于小表(10万行以下)，可以确认后执行；

4.4 对于大表，必须在低峰期(上午9点前或者凌晨)执行，推荐凌晨执行，出现故障影响更可控；

4.5 对于确定的执行时间超过10分钟并且会造成锁表的，推荐使用创建新表，同步旧表数据，rename的方式来更新；

CREATE TABLE `rrc_order`.`cm_user_c2_intent_20160912` LIKE `rrc_order`.`cm_user_c2_intent`;


ALTER TABLE `rrc_order`.`cm_user_c2_intent_20160912`  
    CHANGE COLUMN `created_at` `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录生成时间|2016-09-17' ,  
    CHANGE COLUMN `updated_at` `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录变更时间|2016-09-17' ,  
    ADD INDEX `update_time_index` (`updated_at` DESC);


INSERT INTO `rrc_order`.`cm_user_c2_intent_20160912` SELECT * FROM `rrc_order`.`cm_user_c2_intent`;
 
INSERT INTO `rrc_order`.`cm_user_c2_intent_20160912` SELECT * FROM `rrc_order`.`cm_user_c2_intent` WHERE `id` > (SELECT MAX(`id`) FROM `cm_user_c2_intent_20160912`);

RENAME TABLE  
    `rrc_order`.`cm_user_c2_intent` TO `rrc_order`.`cm_user_c2_intent_temp`,  
    `rrc_order`.`cm_user_c2_intent_20160912` TO `rrc_order`.`cm_user_c2_intent`,  
    `rrc_order`.`cm_user_c2_intent_temp` TO `rrc_order`.`cm_user_c2_intent_20160912`;

4.6、其他tips：

操作前确保有dms的连接在，防止出现问题时连接被占满，无法连接数据库;

如果操作中出现故障，尽量将流量先从故障实例切走(例如从库出现问题，可以把读请求都切到主库)，再进行故障分析；