数据库设计规范(数据库设计三规范)原波波论运维2019-10-12 00:01:00

摘要

与Oracle、SQL Server等数据库相比，MySQL数据库在内核方面有其优势和劣势。我们在使用MySQL数据库时，需要遵循一定的规范，扬长避短。无意中看到github的一个很大的MySQL数据库设计规范，顺便分享到这里。

https://github . com/jly 8866/archer/blob/master/src/docs/MySQL _ db _ design _ guide . MD

写的东西都很实用，建议收藏阅读。

1.数据库设计

以下所有规范将按照[高风险]、[强制]和[推荐]三个级别进行标记，符合性的优先级从高到低。对于不符合【高风险】和【强制】级别的设计，DBA会强制召回修改。

1.库名

【强制】库名必须控制在32个字符以内，相关模块的表名要尽可能的表现出联接关系，比如user表和user_login表。

【强制】库的名称格式:业务系统名称_子系统名称，同一模块使用的表名尽量使用统一前缀。

【强制】通用子库名的命名格式为库通用名_编号，编号从0开始递增。比如wenda_001子库by time就是“库常用名_ time”

[强制]创建数据库时必须明确指定字符集，字符集只能是utf8或utf8mb4。创建数据库SQL的例子:创建数据库db1默认字符集utf8。

2.表结构

[强制]表和列的名称必须控制在32个字符以内。表名只能使用字母、数字和下划线，都是小写。

【强制】表名要求模块名之间有很强的相关性，如教师系统中以“sz”为前缀，通道系统中以“qd”为前缀。

[强制]创建表时，必须明确指定字符集是utf8或utf8mb4。

[强制]创建表时，必须明确指定表存储引擎的类型。如果没有特殊要求，永远是InnoDB。当需要InnoDB/MyISAM/Memory以外的存储引擎时，必须经过DBA的批准，才能在生产环境中使用。Innodb table是业内使用最广泛的MySQL存储引擎，因为它支持关系数据库的重要特性，如事务、行锁、停机恢复和MVCC。这是大多数其他存储引擎都没有的，所以InnoDB是第一个。

[强制]创建表时必须有注释。

【建议】创建表时，关于主键:(1)强制要求主键为id、type int或bigint、auto_increment(2)标识表中每行主体的字段不要设置为主键，但建议设置user_id、order_id等其他字段。，并建立唯一的关键索引(参考cdb.teacher表设计)。如果设置为主键，随机插入主键值，会导致innodb中出现页面拆分和大量随机I/O，性能会下降。

【建议】核心表(如用户表、货币关表)必须有行数据的create_time字段和上次更新时间字段update_time，方便查问题。

【建议】表中所有字段必须为非空属性，业务可以根据需要定义默认值。因为使用NULL值会导致一些问题，比如每行多存储空空间，数据迁移容易出错，聚合函数计算结果偏差等。

【建议】建议将表格中的blob、text等大字段垂直拆分到其他表格中，只在需要读取这些对象时选择。

【建议】反范式设计:冗余其他表中经常需要连接查询的字段。例如，user_name属性在user_account、user_login_log和其他表中是冗余的，减少了连接查询。

【强制】中间表用于保存中间结果集，名称必须以tmp_开头，备份表用于备份或抓取源表的快照，名称必须以bak_ Intermediate tables开头，备份表定期清理。

【强制】对于超过100W行的大表，alter table必须经过DBA的批准，并在业务高峰期执行。因为alter table会生成表锁，所以在此期间对表的所有写入都会被阻塞，可能会对业务造成很大影响。

3.列数据类型优化

【建议】对于表中的自增列(auto_increment属性)，建议使用bigint类型。因为无符号int的存储范围是-2147483648~2147483647(约21亿)，溢出后会报错。

【建议】对于状态、类型等业务中选择性不大的字段，建议使用tinytint或smallint类型，以节省存储空间空。

【建议】业务中IP地址字段推荐int类型，不推荐char(15)。因为int只占用4个字节，可以用下面的函数相互转换，而char(15)至少占用15个字节。一旦表数据的行数达到1亿，1.1G的存储空间应该用得更多空。SQL:select inet _ aton(' 192 . 168 . 2 . 12 ')；选择inet _ ntoa(3232236044)；PHP:IP 2 long(' 192 . 168 . 2 . 12 ')；long 2 IP(3530427185)；

[建议]不建议使用enum，set。因为它们浪费空小时，而且枚举值写死了，不方便更改。建议使用Tinyint或smallint。

【建议】不推荐blob、text等类型。都是浪费硬盘和内存空。加载表数据时，大字段会被读入内存，浪费内存空，影响系统性能。建议PM和RD沟通是否真的需要这么大的场地。在Innodb中，当一行记录超过8098个字节时，将选择记录中最长的字段，其768个字节将放在原始页中，其余的字段将放在溢出页中。不幸的是，在压缩行格式中，原始页面和溢出页面都将被加载。

【建议】建议在程序端使用int，乘以100，除以100来访问存放钱的字段。因为int占4个字节，double占8个字节，所以空浪费了。

【建议】文本数据尽量存储在varchar中。因为varchar是变长存储，所以比char节省空时间。MySQL服务器层规定一行中所有文本的最大存储量为65，535字节，所以utf8字符集的最大存储量21，844个字符会自动转换成mediumtext字段。在utf8字符集中，Text最多可存储21，844个字符，mediumtext最多可存储2.24/3个字符，longtext最多可存储2.32个字符。一般推荐Varchar类型，字符数不要超过2700。

【建议】尽量选择时间戳作为时间类型。因为datetime占8个字节，timestamp只占4个字节，但是范围是1970-01-01 00:00:01到2038-01-01 00:00:00。作为一种高阶方法，使用int存储时间，使用SQL函数unix_timestamp()和from_unixtime()进行转换。

有关详细的存储大小，请参见下图:

4.索引设计

[强制InnoDB表必须有一个id int/bigint auto_increment的主键，并且主键值不能更新。

【建议】主键名以pk_开头，唯一键以uk_或uq_开头，普通索引以idx_开头，均采用小写格式，以表名/字段的名称或缩写作为后缀。

[必填] InnoDB和MyISAM存储引擎表，索引类型必须是BTREE；内存表可以根据需要选择HASH或BTREE类型的索引。

[强制]单个索引中每个索引记录的长度不能超过64KB。

【建议】单个表上的索引数不能超过7个。

【建议】建立指数时，考虑建立联合指数，将区分度最高的领域放在第一位。例如，可以通过select count(distinct userid)计算列userid的区分度。

【建议】在多表连接的SQL中，确保被驱动表的连接列上有索引，这样连接才能以最高的效率执行。

【建议】在构建表或添加索引时，确保表与表之间没有多余的索引。对于MySQL，如果key(a，b)已经存在于表中，那么key(a)就是一个冗余索引，需要删除。

5.子库表和分区表

[强制]分区表的分区字段(分区键)必须有索引或者是组合索引的第一列。

[强制]单个分区表中的分区(包括子分区)数量不能超过1024。

【强制】上线前，RD或DBA必须指定分区表的创建和清理策略。

[强制]访问分区表的SQL必须包含分区键。

【建议】单个分区文件不超过2G，总大小不超过50G。建议分区总数不要超过20。

[强制]分区表的alter table操作必须在业务高峰期执行。

【强制】如果采用分库策略，库数不能超过1024。

【强制】如果采用子表策略，表数不能超过4096。

【建议】单个子表不要超过500W行，ibd文件大小不要超过2G，使数据分布更好。

【建议】横向表格尽量按模块划分，日志和报表的数据按日期划分。

6.字符集

【强制】数据库、表、列中的所有字符集必须一致，为utf8或utf8mb4。

【强制】前端程序字符集或环境变量中的字符集必须与数据库和表的字符集一致，统一为utf8。

2.SQL编写规范

1.DML语句

【强制】SELECT语句必须指定具体的字段名，禁止写*。因为select *也会读出不该从MySQL中读取的数据，造成网卡压力。而一旦表字段更新了，但是模型层来不及更新，系统就会报错。

【强制】insert语句指定具体的字段名，不应该写成insert into t1 values(…)，原因同上。

【建议】插入…值(XX)，(XX)，(XX) …。这里XX的值不应该超过5000。数值太多，虽然上线快，但是会造成主从同步延迟。

[建议]建议在SELECT语句中使用UNION all而不是UNION，并且UNION子句的数量限制为五个。因为union all不需要复制，所以它节省了数据库资源并提高了性能。

【建议】中的值列表限制为500。例如，select … where userid in(小于…500 …)，这是为了减少底层扫描，减轻数据库压力，加快查询速度。

【建议】交易中批量更新数据需要控制数量，进行必要的睡眠，做到次数少。

[强制]事务中涉及的所有表必须是innodb表。否则一旦失败就无法完全回滚，容易造成主从库同步终端。

【强制】写和事务发送到主库，只读SQL发送到从库。

[强制]除了静态表或小表(100行以内)，DML语句必须有一个where条件，并使用index来查找。

【强制】生产环境中禁止使用提示，如sql_no_cache、force index、ignore key、straight join等。Hint是用来强制SQL按照一个执行计划执行的，但是随着数据量的变化，我们无法保证自己当初的预测是正确的，只好相信MySQL优化器了！

[强制]在where条件中，等号两边的字段类型必须相同，否则不能使用索引。

【建议】SELECT|UPDATE|DELETE|REPLACE应该有一个WHERE子句，WHERE子句的条件必须按索引搜索。

[强制]强烈建议不要在生产数据库中对大型表进行全表扫描，但它可以用于少于100行的静态表。查询数据量不能超过表行数的25%，否则索引不会被使用。

【强制】在WHERE子句中，禁止只使用模糊的LIKE条件进行搜索，必须有其他等价或范围查询条件，否则不能使用索引。

【建议】索引列不要使用函数或表达式，否则无法使用索引。例如，其中length(name)='Admin '或其中user_id+2=10023。

【建议】减少or语句的使用，可以优化为union，然后对每个where条件进行索引。例如，其中a=1或b=2优化为其中a = 1 … union …其中b = 2，key (a)，key (b)。

【建议】对于分页查询，当限制起点较高时，可以先使用过滤条件进行过滤。从t1限值10000，20中选择a，b，c；优化:从t1中选择a、b、c，其中id > 10000 limit20。

2.多表连接

[强制]禁止跨数据库联接语句。因为这样可以减少模块之间的耦合，为数据库拆分打下坚实的基础。

[强制]禁止在update SQL业务语句中使用join，如update t1 join t2…

【建议】不建议使用子查询。建议将子查询SQL反汇编，并将其与程序结合起来进行多次查询，或者使用join代替子查询。

【建议】在线环境下，多表连接不要超过3个表。

【建议】多表连接查询推荐使用别名，字段、数据库、表格式都要被选择列表中的别名引用，比如SELECT a from db1 . table 1 alias 1 where。

【建议】在多表连接中，尽量选择结果集较小的表作为连接其他表的驱动表。

3.事务

【建议】事务中INSERT|UPDATE|DELETE|REPLACE语句操作的行数控制在2000以内，WHERE子句中IN列表的参数个数控制在500以内。

【建议】批量操作数据时，要控制好事务处理的间隔，进行必要的睡眠。一般推荐值为5-10秒。

【建议】对于具有auto_increment属性字段的表的插入操作，需要将并发控制在200以内。

【强制】编程必须考虑“数据库事务隔离级别”的影响，包括脏读、不可重复读和幻影读。推荐的在线事务隔离级别是可重复读取。

【建议】交易不超过5 SQL(支付业务除外)。因为过长的事务会导致锁数据过长、MySQL内部缓存、连接消耗过大等雪崩问题。

【建议】交易中的Update语句尽量基于主键或唯一键，比如Update…where id = XX；否则会产生间隙锁，内部扩大锁定范围，导致系统性能下降和死锁。

【建议】尽量将一些典型的外部调用从事务中移除，比如调用webservice、访问文件存储等，避免事务过长。

【建议】对于具有MySQL主从延迟的严格敏感的select语句，请打开transaction强制访问主库。

4.排序和分组

【建议】减少order by的使用，不排序就和业务沟通，或者把排序放在程序端。Order by、group by和distinct语句消耗CPU，数据库的CPU资源极其宝贵。

【建议】order by、group by、distinct SQL尝试使用索引直接检索排序后的数据。例如，在a=1的情况下，order by可以利用key(a，b)。

【建议】它包含order by、group by和distinct查询语句。请将按where条件过滤的结果集保持在1000行以内，否则SQL会非常慢。

5.在线禁止的SQL语句

[高风险]禁用更新|删除t1...其中a = xx极限xx；这个update语句有限制。因为会导致主和数据混乱不一致。按主键添加订单。

【高风险】禁止使用关联的子查询，如update T1 set…where name in(select name from user where…)；效率极低。

[强制]禁用过程、函数、触发器、视图、事件和外键约束。因为它们消耗数据库资源并降低数据库实例的可伸缩性。建议都是在程序端实现的。

[强制]禁用插入...重复密钥更新时...在高并发环境下，会导致主永远不同意。

[强制]禁止更新链接表的语句，如update t1，T2 where t1。ID = T2。身份...

觉得有用的朋友多帮忙转发！后面会分享更多关于devops和DBA的信息，感兴趣的朋友可以关注一下~