史上最强的MySQL数据库设计规范（互联⽹⼤⼚都使⽤的2021年最新版本）⽬录 概述
⽬的
本⽂档主要⽬的是对所有数据库对象（包括库、表、字段、索引、主键、外键、约束、表分区、触发器、存储过程等）的使⽤场景及使⽤规范，进⾏相关的约定，供⽇后应⽤开发、数据库设计、数据库维护提供规范性依据。 读者对象
参考⽂档
⽆。
术语定义
术语解释
字符集字符集(Character set)是多个字符的集合，字符集种类较多，每个字符集包含的字符个数不同，
常见字符集名称：UTF-8字符集，UTF-8-MB4字符集，ASCII字符集、GB2312字符集、BIG5字符集、 GB18030字符集、Unicode字符集等。
存储引擎⽤各种不同的技术将数据存储在⽂件（或者内存）中的机制。每⼀种技术都使⽤不同的存储机制、索引技巧、锁定⽔平并且最终提供⼴泛的不同的
功能和能⼒。
数据
库特
性
跟数据⾃⾝相关，但区别于其他类型数据库的功能或者特征。
范式范式，特指数据库设计范式，是符合某⼀种级别的关系模式的集合。构造数据库必须遵循⼀定的规则。在关系数据库中，这种规则就是范式。关系
数据库中的关系必须满⾜⼀定的要求，即满⾜不同的范式。
冗余特指在数据库设计中，本不属于表实体的属性，但因性能或者其他要求，将这些属性也在本表中存储，定义为冗余。索引在关系数据库中，索引是⼀种单独的、物理的对数据库表中⼀列或多列的值进⾏排序的⼀种存储结构。
前缀
索引
在数据库设计中，特指对某个字段前部分内容建⽴索引，称为前缀索引。
联合
索引
联合索引是由多个字段组成的索引。
主键也称主关键字(primary key)，是表中的⼀个或多个字段，它的值⽤于唯⼀地标识表中的某⼀条记录。
稠密
度
特指经过索引后得到的数据占整个表数据的⽐值。
聚合
函数
SQL的基本函数，主要作⽤是对⼀组值执⾏计算，并返回单个值。
DDL数据定义语⾔(Data Definition Language)，是⽤于描述数据库中要存储的现实世界实体的语⾔。
DML数据操纵语⾔（Data Manipulation Language, DML）是SQL语⾔中，负责对数据库对象运⾏数据访问⼯作的指令集。负载
均衡
负载均衡（Load Balance）其意思就是分摊到多个操作单元上进⾏执⾏，从⽽共同完成⼯作任务。
主从
复制
特指实现两个数据库数据同步的⼀种机制。
数据库设计规范
数据库对象，包括库、表、字段、索引、主键、外键、约束、表分区、触发器、存储过程等，每种对象都会有它的使⽤场景及规范。为提⾼数据库的稳定性、⾼效性、安全性，降低数据库故障率，对数据库的对象制定以下规范，供⽇后应⽤开发、数据库设计和操作提供依据。
整体规范
注释
数据库所有对象必须要有注释，包括：表、字段、索引等，并且要保持最新。
广西中医学院专家楼字符集
默认使⽤utf8mb4字符集，⽆乱码风险。utf8mb4是utf8的超集，可以获得⽐utf8更好的兼容性。
存储引擎
默认使⽤INNODB存储引擎。 MyISAM引擎从MYSQL 5.5
版本后查询性能已经没InnoDB⾼，另外InnoDB的以主键为条件的查询性能是⾮常⾼的，且⽀持事务、⾏级锁、⾼并发性能更好、对多核CPU、⼤内存、SSD等硬件资源⽀持更好，利⽤率更⾼。 如需要使⽤基他类型的存储引擎，请在DBA的建议下使⽤。 不依赖数据库特性
降低对数据库功能的依赖，如在业务上使⽤了MySQL特性，且这个特性是只有MySQL存在的，对以后的数据库迁移会带来⿇烦。
平衡范式与冗余
并⾮⼀定要遵守范式理论，适度的冗余设计，字段长度短⽽且频繁查询的字段可以冗余到其他表，避免表连接查询，可以极⼤提升查询效率。
数据库对象规范
表设计规范
控制单库表数量
单库表数量建议控制在500以内。
控制单表数据量
单表数据量建议控制在1000万以内（参考值）。表的记录数多少合适不能死搬硬套，需要根据服务器的CPU、内存、磁盘IO能⼒综合评估。⽐如服务器总内存有168G，数据库总数据⽂件⼤⼩100G，innodb
缓存池设置为120G，这个时候即便⼤表有3000万条，也可以全部加载到内存中，性能上完全不会有磁盘IO压⼒。根据经验值⼀般热数据占数据总量的10%左右，热数据都能缓存到内存中性能上就不会有磁盘IO压⼒。
控制单表字段数量
控制单表单字段数量的⽬的是为了控制数据⾏的长度避免出现⾏迁移和⾏链接。如果计算⾏长度避免出现⾏链接或⾏迁移呢？MYSQL 的数据⾏是存储在数据页中，数据页的⼤⼩是16KB（默认16KB），file header、Page
Header、File Trailer 占⽤了102字节，Page Directory
记录数据⾏在数据页的位置也需要消耗数据页空间，建议把总消耗空间按1KB算，也就是说数据页可以空间还剩15KB。15KB除去⾏长度可以整除就可以避免⾏链接，尽量少使⽤可变长度的⼤字段可以有效减少⾏迁移。表列数量建议控制在30个以内。
冷热数据分离
访问频率较低的⼤字段拆分出数据表，以免造成IO资源、缓存资源的浪费。经常⼀起使⽤的列应该放到⼀个表中，允许适当冗余，避免更多的关联操作。
采⽤合适的分库分表策略
⼤表查询效率很低，需要考虑⽔平拆分。根据业务特性有很多拆分⽅式。符合时间递增的表，可以根据时间来分，也可以ID的HASH⽅式来拆分。
数据库界面设计如果按HASH散表，表名后缀使⽤⼗进制，下标从0开始。考虑后续的扩容，建议使⽤⼆叉树分库分表策略。
如果按⽇期时间散表，表名需要符合YYYY[MM][DD][HH][mm][sss]的格式。
根据需要设计汇总表
多表关联查询会很慢，可以根据实际情况，考虑在业务上汇总，记录到汇总表。
字段设计规范
基本规范
存储相同数据的列名和列类型必须⼀致，否则会导致隐式转换，造成索引失效，降低查询效率。
在最⼤限度的满⾜可能的需要的前提下，字段应该尽可能的设计得短⼀些，以提⾼查询的效率，且降低索引对资源的消耗。
数据⾏的长度不要超过8020字节，如果超过这个长度的话在物理页中插⼊两⾏数据就会出现⾏链接从⽽造成存储碎⽚，降低查询效率。
单表列数量建议控制在30个以内。
尽量使⽤整型字段，代替IP、枚举类型、字符类型、浮点数类型。
所有字段都需要默认值，如有特殊情况，另作讨论决定。
字符字段
⽤户名、密码等长度变化不⼤的字段选择CHAR类型。
其他不确定长度的字段，统⼀使⽤varchar相关的类型。
整型字段
明确⽆符号的数值，使⽤ unsigned的整型。牛志美
能够⽤整型的字段尽量整型，提⾼查询和连接的性能，降低存储开销、CPU计算开销。如enum、ip、⼩额货币等。
Enum字段
禁⽌使⽤enum，可使⽤tinyint代替。
因为修改ENUM需要使⽤ALTER语句，需要进⾏DDL操作， ENUM类型的ORDER
BY操作效率低，需要额外操作
默认值
所有字段都需要默认值，不允许为null，避免⽆法使⽤索引或null值引发BUG，如有特殊情况，可以存储空⽩字符代替null。
Null字段难以进⾏查询优化，索引需要额外的空间，复合索引⽆效，整体降低数据库处理的性能，也容易导致应⽤层程序报空指针异常。
⼆进制数据
禁⽌在数据库上存储图⽚、⼆进制⽂件等静态资源，应该使⽤合适的⽂件系统，数据库仅存储URL对于⼆进制多媒体数据、超⼤⽂本数据也不要放在数据库字段中。
Text/Blob字段
⼀般避免使⽤text、blob等类型字段，会浪费更多的磁盘和内存空间，⾮必要的⼤量的⼤字段查询会淘汰掉热数据，导致内存命中率急剧降低，影响数据库性能。
考虑使⽤varchar来代替，如果⼀定要使⽤text/blob，要离到单独的扩展表中，如果要⽤到索引，只能使⽤前缀索引。
⽇期时间字段
timestamp类型⽐较精简，可以提⾼查询效率，减少磁盘空间及IO，但范围是1970年-2038年，考虑企业的历史及将来，建议使⽤datetime类型存储⽇期时间。
⾦额字段
禁⽌使⽤float、double来定义⾦额字段，建议使⽤decimal类型或者bigint类型。
⾦额字段使⽤decimal类型，并给予⾜够的长度及精度。在性能要求⽐较苛刻的情况下，使⽤bigint类型，单位是分（如果是其他货币，需要定义其他单位）。
其他业务字段
电话字段
考虑到区号或者国家代号可能会涉及到±()等符号，并且需要⽀持模糊查询，所以应该使⽤字符类型，如varchar等
。
坐标字段
表⽰坐标（0,0），应该使⽤两列表⽰，⽽不是将“0,0”放在1个列中。
不建议使⽤预留字段
预留字段的命名很难做到见名识义。
预留字段⽆法确认存储的数据类型，所以⽆法选择合适的类型。
预留字段是⼀种“过度设计”，我们应该做的就是“按需设计”，在经过详细有效的分析之后，在数据表中只放置必要的字段，⽽不要留出⼤量的备⽤字段。
索引设计规范
索引可以提⾼查询效率，但会降低更新效率，所以索引不是越多越好，原则是能不加就不加，要加的⼀定得加。
控制单表索引数量
单表索引数量不超过5个。
控制单个索引的字段数量护理学基础精品课程
单个索引中的字段数不超过5个。
对于更新频繁的字段要评估读写⽐例和创建索引后的性能收益和醒脑静损坏⽐例再决定是否创建索引
对于频繁更新的字段是否适合创建索引要评估该字段读写⽐例再决定是否创建索引。⽐如⼀个字段每秒更新20次，但每秒查询达到100次，⽽且是直接通过该字段来定位数据⾏的，如果该字段没有索引就会导致全表扫描，如果更新也是需要使⽤该字段定位数据⾏也会导致更新出现全表扫描，这种情况就是⼀定要创建索引的。（相对应的⼀种情况是通过数据⾏的ID可以定位到数据⾏，不需要使⽤被更新字段定位数据⾏，这种情况就不适合创建索引）。
禁⽌在区分度不⾼的属性上建⽴索引
如“性别”这种区分度不⼤的字段，建⽴索引对查询性能的提升有限，与全表扫描差别不⼤。
建⽴组合索引字段顺序优先级
使⽤频率⾼（⼤量SQL 过滤条件都使⽤了这个字段）
使⽤频率⾼
区分度⾼范围查询
建⽴长字符串字段的前缀索引
保证快速有效过滤数据的同时，节省维护索引的开销。
避免冗余重复索引
已经建⽴唯⼀索引的字段，没有必要再建⽴与该字段有关的联合索引。
不要建⽴查询条件⾥根本不会出现的字段的索引或者联合索引。
选择合适的索引类型
唯⼀确定⼀条记录的⼀个字段或多个字段要建⽴主键或者唯⼀索引，不能唯⼀确定⼀条记录，为了提⾼查询效率建⽴普通索引。
建⽴索引时必须考虑索引返回的记录稠密度
汕头马会
业务通过不唯⼀索引访问数据时，需要考虑通过该索引值返回的记录稠密度，原则上稠密度最⼤不能⾼于0.2，如果稠密度太⼤，则不合适建⽴索引了。
当通过这个索引查得到的数据量占到表内所有数据的20%以上时，则需要考虑建⽴该索引的代价，
同时由于索引扫描产⽣的都是随机
I/O，其效率⽐全表顺序扫描的顺序I/O低很多。数据库系统优化query的时候有可能不会⽤到这个索引。
注意联合索引的顺序
联合索引中各字段的顺序，要与查询语句的字段顺序保持⼀致，否则可能⽆法应⽤索引。
区分度最⾼的放在联合索引的最左侧。
使⽤最频繁的列放到联合索引的左侧。
尽量把字段长度⼩的列放在联合索引的最左侧。
主键设计规范
腰医绅⼀般不使⽤联合主键。
必须指定主键，建议使⽤内存型、数值型字段做主建，以应对⼤数据⾼并发的业务场景。如果使⽤⾃增列，在⼀定程度上依赖了数据库⾃⾝的特性，同时也要考虑分布式环境的全局唯⼀性。UUID是字符
类型，增加索引磁盘空间及CPU开销，且不具备⾃增特性。
豫公网安备41160202000603
站长QQ:729038198
关于我们
投诉建议