命名规范
- 【强制】库名、表名、字段名要小写,下划线风格,不超过32个字符,必须见名知意,建议使用名词而不是动词,词义与业务、产品线等相关联,禁止拼音英文混用
- 【强制】普通索引命名格式:idx_表名_索引字段名(如果以首个字段名为索引有多个,可以加上第二个字段名,太长可以考虑缩写),唯一索引命名格式:uk_表名_索引字段名(索引名必须全部小写,长度太长可以利用缩写),主键索引命名:pk_ 字段名
- 【推荐】使用CRC32求余(或者类似的算术算法)进行散表,表名后缀使用数字,数字必须从0开始并等宽,比如散100张表,后缀从00-99
- 【推荐】使用时间散表,表名后缀必须使用特定格式,比如按日散表user_20110209、按月散表user_201102
- 【强制】表达是与否概念的字段,使用 is _ xxx 的方式进行命名
库设计规范
- 【推荐】数据库使用InnoDB存储引擎
- 【推荐】数据库和表的字符集统一使用
utf8mb4
, 排序规则utf8mb4_unicode_ci
表设计规范
- 【强制】禁止使用外键,如果有外键完整性约束,需要应用程序控制
- 【推荐】采用合适的分库分表策略,例如千库十表、十库百表等(建议表大小控制在2G)
- 【推荐】表默认设置创建时间戳和更改时间戳字段
- 【强制】禁止使用order by rand()
说明:order by rand()会为表增加一个伪列,然后用rand()函数为每一行数据计算出rand()值,基于该行排序,这通常都会生成磁盘上的临时表,因此效率非常低。 - 【推荐】每张表数据量建议控制在500w以下,超过500w可以使用历史数据归档或分库分表来实现(500万行并不是MySQL数据库的限制。过大对于修改表结构,备份,恢复都会有很大问题。MySQL没有对存储有限制,取决于存储设置和文件系统)
字段设计规范
- 【强制】必须把字段定义为NOT NULL并且提供默认值
说明:NULL字段很难查询优化,NULL字段的索引需要额外空间,NULL字段的复合索引无效 - 【强制】禁止使用ENUM,可使用TINYINT代替
- 【强制】禁止使用TEXT、BLOB类型(如果表的记录数在万级以下可以考虑)
- 【强制】禁止使用小数存储国币、使用“分”作为单位,这样数据库里就是整数了
- 【强制】用DECIMAL代替FLOAT和DOUBLE存储精确浮点数
- 【强制】所有存储相同数据的列名和列类型必须一致(在多个表中的字段如user_id,它们类型必须一致)
索引设计规范
- 【推荐】单表索引建议控制在5个以内
说明:索引可以增加查询效率,但同样也会降低插入和更新的效率,甚至有些情况下会降低查询效率,所以不是越多越好 - 【强制】不在索引列进行数学运算和函数运算
- 【推荐】研发要经常使用explain,如果发现索引选择性差,必须要学会使用hint
- 【强制】索引字段要保证不为NULL,考虑default value进去。NULL也是占空间,而且NULL非常影响索引的查询效率
- 【强制】表必须有无符号int型自增主键,对应表中id字段
说明:必须得有主键的原因:采用RBR模式复制,无主键的表删除,会导致备库夯住 ;使用自增的原因:
数据写入可以提高插入性能,避免page分裂,减少表碎片 - 【推荐】WHERE条件中的非等值条件(IN、BETWEEN、<、<=、>、>=)会导致后面的条件使用不了索引
- 【推荐】复合索引中的字段数建议不超过5个
其他
建表规范示例
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 '学号',
`status` tinyint(4) DEFAULT '1' COMMENT '1代表记录有效,0代表记录无效',
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_stu_num` (`stu_num`) USING BTREE,
KEY `idx_stu_name` (`stu_name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生信息表';
- 【参考】区分使用DATETIME和TIMESTAMP
说明:存储年使用YEAR类型、存储日期使用DATE类型、存储时间(精确到秒)建议使用TIMESTAMP类型。
DATETIME和TIMESTAMP都是精确到秒,优先选择TIMESTAMP,因为TIMESTAMP只有4个字节,而DATETIME8个字节,同时TIMESTAMP具有自动赋值以及⾃自动更新的特性。
补充:如何使用TIMESTAMP的自动赋值属性?
自动初始化,而且自动更新:column1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATECURRENT_TIMESTAMP 只是自动初始化:column1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP 自动更新,初始化的值为0:column1 TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP 初始化的值为0:column1 TIMESTAMP DEFAULT 0 - 【参考】VARCHAR(N),N尽可能小,因为MySQL一个表中所有的VARCHAR字段最大长度是65535个字节,进行排序和创建临时表一类的内存操作时,会使用N的长度申请内存
- 【推荐】对长度过长的VARCHAR字段建立索引时,添加crc32或者MD5 Hash字段,对Hash字段建立索引
说明:下面的表增加一列url_crc32,然后对url_crc32建立索引,减少索引字段的长度,提高效率
CREATE TABLE url( … url VARCHAR(255) NOT NULL DEFAULT 0, url_crc32 INT UNSIGNED NOT NULL DEFAULT 0, … index idx_url(url_crc32) )