建议收藏:一份完整的数据库规范

科技   2024-06-20 14:00   上海  

大家好,根据以往的工作经验,很多时候数据库出问题,都是没有规范使用数据库导致的。

比如条件字段没有添加合适索引导致查询缓慢,从而影响到用户体验;

还有就是什么都往Redis里存放,导致成本浪费。

等等。

所以,制定内部的数据库规范,显得尤为重要。

在我制作的DBA体系课中,就有一部分是模拟DBA去维护一套电商网站的数据库(MySQL、Redis、MongoDB),包括高可用环境准备、日常维护、备份、监控、自动化、迁移等。

当然,也包括这套电商网站数据库的开发规范。

这篇文章就来分享一下。


MySQL

1 命名规范

1.1、表名建议使用有业务意义的英文词汇,必要时可加数字和下划线,并以英文字母开头;

比如商品表products,如果有多个表,可以写成products_001


1.2、库、表、字段全部采用小写;

防止因为大小写问题找不到表或者弄错表。


1.3、避免用MySQL的保留字;

关于哪些是MySQL的保留字,可以参考官方文档:

https://dev.mysql.com/doc/refman/8.0/en/keywords.html


1.4、命名(包括表名、列名)禁止超过 30 个字符;

一张表,表名四五十个字符,比如:

online_store_product_information_table


真没必要啊,维护和管理也挺麻烦的。


1.5、临时库、表名必须以 tmp 为前缀,并以日期为后缀;

如:tmp_shop_info_20240101;


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

如:bak_shop_info_20240101;


1.7、索引命名:

非唯一索引必须按照“idx_字段名称”进行命名;

唯一索引必须按照“uniq_字段名称”进行命名。


2 设计规范

2.1、主键:

无特殊要求,主键均按如下语句来设置:

`id` INT  NOT NULL AUTO_INCREMENT COMMENT '主键',


2.2、如无特殊要求,建议都使用 InnoDB 引擎;


2.3、字符集

默认使用utf8mb4字符集;

数据排序规则使用utf8mb4_general_ci;

原因:utf8mb4为万国码,无乱码风险;与utf8编码相比,utf8mb4能支持Emoji表情。


2.4、所有表、字段都需要增加comment来描述此表、字段所表示的含义;

比如:

data_status TINYINT NOT NULL DEFAULT 1 COMMENT ‘1代表记录有效,0代表记录无效’。


2.5、如无说明,建议表包含create_time和update_time字段,即表必须包含记录创建时间和修改时间的字段;


2.6、用尽量少的存储空间来存放一个字段的数据:

能用 int 的就不用 char 或者 varchar;

能用 tinyint 的就不用 int;

使用 UNSIGNED 存储非负数值;

只存储年使用 YEAR 类型;

只存储日期使用 DATE 类型。


2.7、存储精确浮点数必须使用DECIMAL替代FLOAT和DOUBLE;

原因:在存储的时候,FLOAT和DOUBLE都存在精度损失的问题,很可能在比较值的时候,得到不正确的结果。


2.8、尽可能不使用 TEXT、BLOB 类型;

原因:会浪费更多的磁盘和内存空间,非必要的大量大字段查询会淘汰掉热数据,导致内存命中率急剧降低,影响数据库性能。

如果实在有某个字段过长需要使用 TEXT、BLOB 类型,则建议独立出来一张表,用主键来对应,避免影响原表的查询效率。


2.9、禁止在数据库中存储明文密码;


2.10、索引设计规范:

a、需要添加索引的字段

UPDATE、DELETE 语句的 WHERE 条件列;

ORDER BY、GROUP BY、DISTINCT 的字段

JOIN 语句的关联字段


b、单表索引建议控制在 5 个以内;


c、适当配置联合索引;

比如方便查询能走覆盖索引,或者几个字段同时作为条件的概率很高时,可以增加合适的联合索引。


d、业务上具有唯一性的字段,添加成唯一索引;

遇到过几次字段在业务场景上要求唯一,但是该字段在数据库里的数据却出现了重复。因此在代码层考虑外,还需要在 MySQL 上的对应字段添加唯一索引。


e、在 varchar 字段上建立索引时,建议根据实际文本区分度指定索引长度;

原因:可以降低索引所占用的空间,并且很多时候,比如字符串基本是长度大于 20,但是只要建立长度为 20 的索引,就已经有很高的区分度了。可以使用 count(distinct left(列名, 索引长度))/count(*) 的区分度来确定。


f、索引禁忌:

不在低基数列上建立索引,例如:性别字段。

不在索引列进行数学运算和函数运算


2.11、不建议使用外键;

原因:外键会导致表与表之间耦合,update 与 delete 操作都会涉及相关联的表,很影响sql 的执行效率,甚至会造成死锁。高并发情况下容易造成数据库性能下降。


2.12、禁止使用存储过程、视图、触发器、Event ;

原因:高并发的情况下,这些功能很可能将数据库拖死,业务逻辑放到服务层具备更好的扩展性。


2.13、单表列数目建议小于30;


2.14、表示例:

CREATE TABLE student_info (`id` INT  NOT NULL AUTO_INCREMENT COMMENT '主键',`stu_name` VARCHAR(10) NOT NULL DEFAULT '' COMMENT '姓名',`stu_class` VARCHAR(10) NOT NULL DEFAULT '' COMMENT '班级',`stu_num` INT NOT NULL DEFAULT '0' COMMENT '学号',`stu_score` SMALLINT UNSIGNED NOT NULL DEFAULT '0' COMMENT '总分',`tuition` DECIMAL(5, 2) NOT NULL DEFAULT '0' COMMENT '学费',`phone_number` VARCHAR(20) NOT NULL DEFAULT '0' COMMENT '电话号码',`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录更新时间',`status` TINYINT NOT NULL DEFAULT '1' COMMENT '1代表记录有效,0代表记录无效',PRIMARY KEY (`id`),UNIQUE KEY uniq_stu_num (`stu_num`),KEY idx_stu_score (`stu_score`),KEY idx_update_time_tuition (`update_time`, `tuition`)) ENGINE = INNODB charset = utf8mb4 COMMENT '学生信息表';


3 SQL语句规范

3.1、避免隐式转换;

隐式转换将使用不了索引。


3.2、尽量不使用select *,只select需要的字段 ;

原因:读取不需要的列会增加CPU、IO、NET消耗,并且不能有效的利用覆盖索引。使用SELECT *容易在增加或者删除字段后导致程序报错。


3.3、禁止代码中使用INSERT INTO t_xxx VALUES (xxx),必须显示指定插入的列名 ;

原因:容易在增加或者删除字段后导致程序报错。


3.4、尽量不使用负向查询;

比如 not in/like。


3.5、禁止以%开头的模糊查询;

原因:使用不了索引


3.6、禁止单条SQL语句同时更新多个表;

同时更新多个表的SQL语句可能会变得非常复杂,难以理解和维护。

当出现问题时,排查和修复错误也会更加困难。


3.7、统计记录数使用select count(*)

而不是select count(primary_key)或者select count(普通字段名);

原因:可能会导致走的索引不是最优的或者导致统计数字不准确。


3.8、建议将子查询转换为关联查询;

关联查询通常比子查询执行速度更快。

子查询会在内部执行多次,而关联查询会以更有效的方式一次性检索所需的数据。这可以减少数据库的负载,提高查询性能。


3.9、建议应用程序捕获SQL异常,并有相应处理;

这样,可以大大提升出错时的排查速度


3.10、SQL中不建议使用 sleep(),如特殊需求需要用到sleep(),请提前告知DBA;

可能占用数据库连接和资源,并且可能会在高负载的情况下导致性能下降


4 行为规范

4.1、批量导入、导出数据必须提前通知DBA协助观察;

操作前,可以一起看一下语句是否可以优化,还有就是DBA可以临时关闭一些定时任务(比如备份),方便加快批量导入导出。

另外导入导出过程,可以让DBA关注数据库性能。


4.2、有可能导致MySQL QPS上升的活动,提前告知DBA;

DBA可以评估数据库是否会到瓶颈,或者有些细节是否可以再调整;活动期间,DBA也会观察数据库监控,看数据库是否存在性能问题。


4.3、同一张表的多个alter合成一次操作;

比如加三个字段,有些人会执行三条语句:

ALTER TABLE your_table_name ADD COLUMN new_column1 INT;ALTER TABLE your_table_name ADD COLUMN new_column2 INT;ALTER TABLE your_table_name ADD COLUMN new_column3 INT;

实际一条语句可以执行:

ALTER TABLE your_table_nameADD COLUMN new_column1 INT,ADD COLUMN new_column2 INT,ADD COLUMN new_column3 INT;


4.4、不在业务高峰期批量更新、查询数据库;

避免影响正常业务


4.5、删除表或者库要求尽量先rename,观察几天,确定对业务没影响,再drop。

这样,防止一些我们不知道的业务还在使用这些库或者表的情况。


Redis规范

1 键值对的建议

1.1、key名建议;

建议key name设计:业务名:表名:id

比如:

set school:student:1 martin

要求:不包含特殊字符


1.2、string 类型控制在10 KB以内,hash、list、set、zset元素个数不要超过5000;


1.3、控制key的过期时间;

使用expire设置key的过期时间,防止Redis中残留大量废弃的数据,Redis不是垃圾桶,内存很贵的。

当然,同一个Redis里面的key过期时间也尽量错开,集中过期,可能会导致缓存雪崩


2 禁用一些高危命令

2.1、危险命令直接禁用;

比如:

keys *flushallflushdb

等。

当然,可以考虑直接在配置中禁用这些命令

在配置文件中增加

rename-command flushall ""rename-command flushdb ""rename-command keys ""


或者创建一个ACL用户

ACL SETUSER martin on >martin123 +@all -@dangerous ~*

解释

+@all 所有的命令

-@dangerous 表示禁用危险命令

~* 表示授权所有的key

dangerous危险命令包括:FLUSHALL, MIGRATE, RESTORE, SORT, KEYS, CLIENT, DEBUG, INFO, CONFIG, SAVE, REPLICAOF


2.2、尽量不全量操作Hash、Set等集合结构;

如果单个集合结构里有多个元素,单次操作过多的元素,效率可能会很低,并且可能把网卡流量打满。


3 设计规范

3.1、不同场景合理使用不同的数据结构;

比如排行榜可以使用zset;

地理位置,可以用GEO;

队列可以用list;

计数器可以用string类型。


3.2、不要将所有的数据都放Redis里面;

建议Redis只做热数据缓存,冷数据放到MySQL或者其他数据库里。

一方面,内存是比较贵的,可以节约成本;

另一方面,放到关系型数据库中,数据很难丢失。


MongoDB的规范

1 库名

1.1、库名全小写;


1.2、不能包括除_以外其他的一些特殊字符;


1.3、库名不超过30个字符。


2 集合名命规范

2.1、使用小写字母;


2.2、不能包括除_以外其他的一些特殊字符;


2.3、不能以system.开头,因为这种是系统表;


2.4、建议是有业务意义的单词组合,用下划线连接,比如:shipping_orders;


2.5、集合名不超过30个字符串。


3 文档规范

3.1、文档的键值不能包括除_以外其他的一些特殊字符;


3.2、禁止使用_id,因为这个是默认的主键;


3.3、一个集合中尽可能使用相同类型的文档;


3.4、经常作为条件的字段添加索引。


4 语句

4.1、单条语句避免查询过多的数据;


4.2、单个文档的大小建议不超过16M;


4.3、禁用不带条件的查询和更新;


4.4、每次查询的文档数建议不超过500。


清空集群建议

建议用drop(),而不是deleteMany()。


好的,数据库规范就聊到这里,如果觉得文章还不错,帮忙点个赞哈

MySQL数据库联盟
关注后,回复“高可用”,可获取8篇MySQL高可用文章
 最新文章