IT评测·应用市场-qidao123.com

标题: 史上最全Mysql规范 [打印本页]

---

作者: 海哥    时间: 2022-9-16 17:14
标题: 史上最全Mysql规范
1 整体规约

1.1 注释

1）【强制】数据库所有对象必须要有注释，包括：表、字段、索引等，并且要保持最新；
1.2 字符集

1）【强制】默认使用utf8字符集，无乱码风险，除一些需要存储特殊符号的字段，可以采用utf8mb4，比如文章内容字段，支持表情符号等；
2）【强制】排序规则默认使用utf8-general-ci；
1.3 存储引擎

1）【强制】默认使用INNODB存储引擎；
 说明：MyISAM引擎从MYSQL 5.5版本后查询性能已经没InnoDB高，另外InnoDB的以主键为条件的查询性能是非常高的，且支持事务、行级锁、高并发性能更好、对多核CPU、大内存、SSD等硬件资源支持更好，利用率更高；
如需要使用基他类型的存储引擎，请在DBA的建议下使用；
1.4 数据库特性

1）【推荐】降低对数据库功能的依赖，如在业务上使用了MySQL特性，且这个特性是只有MySQL存在的，对以后的数据库迁移会带来麻烦；
1.5 平衡范式与冗余

1）【推荐】并非一定要遵守范式理论，适度的冗余设计，字段长度短而且频繁查询的字段可以冗余到其他表，避免表连接查询，可以极大提升查询效率；
2 数据库对象

2.1 表设计

2.1.1 单库表数量

1）【强制】单库表数量建议控制在500以内；
2.1.2 单表数据量

1）【强制】单表数据量建议控制在1000万以内（参考值）；
说明：表的记录数多少合适不能死搬硬套，需要根据服务器的CPU、内存、磁盘IO能力综合评估，比如服务器总内存有168G，数据库总数据文件大小100G，innodb缓存池设置为120G，这个时候即便大表有3000万条，也可以全部加载到内存中，性能上完全不会有磁盘IO压力。根据经验值一般热数据占数据总量的10%左右，热数据都能缓存到内存中性能上就不会有磁盘IO压力。
2.1.3 单表字段数量

1）【强制】表列数量建议控制在30个以内；
说明：控制单表单字段数量的目的是为了控制数据行的长度避免出现行迁移和行链接。如果计算行长度避免出现行链接或行迁移呢？MYSQL的数据行是存储在数据页中，数据页的大小是16KB（默认16KB），file header、Page、Header、File Trailer 占用了102字节，Page Directory记录数据行在数据页的位置也需要消耗数据页空间，建议把总消耗空间按1KB算，也就是说数据页可以空间还剩15KB。15KB除去行长度可以整除就可以避免行链接，尽量少使用可变长度的大字段可以有效减少行迁移。
2.1.4 冷热数据分离

1）【推荐】访问频率较低的大字段拆分出数据表，以免造成IO资源、缓存资源的浪费。经常一起使用的列应该放到一个表中，允许适当冗余，避免更多的关联操作；
2.1.5 分库分表策略

1）【推荐】如果按HASH散表，表名后缀使用十进制，下标从1开始。考虑后续的扩容，建议使用二叉树分库分表策略。
2）【推荐】如果按日期时间散表，表名需要符合YYYY[MM][DD][HH][mm][sss]的格式。
说明：大表查询效率很低，需要考虑水平拆分。根据业务特性有很多拆分方式。符合时间递增的表，可以根据时间来分，也可以ID的HASH方式来拆分，也可以通过某些特定字段的计算规则拆分。
2.1.6 汇总表

1）【推荐】多表关联查询会很慢，可以根据实际情况，考虑在业务上汇总计算，记录到汇总表。
2.2 字段设计

2.2.1 基本规范

1）【强制】存储相同数据的列名和列类型必须一致，否则会导致隐式转换，造成索引失效，降低查询效率；
2）【强制】在最大限度的满足可能的需要的前提下，字段应该尽可能的设计得短一些，以提高查询的效率，且降低索引对资源的消耗；
3）【强制】数据行的长度不要超过8020字节，如果超过这个长度的话在物理页中插入两行数据就会出现行链接从而造成存储碎片，降低查询效率；
4）【强制】单表列数量建议控制在30个以内；
5）【强制】尽量使用整型字段，代替IP、枚举类型、字符类型、浮点数类型；
6）【强制】所有字段都需要默认值，如有特殊情况，另作讨论决定；
2.2.2 字符字段

1）【强制】长度变化不大的字段选择CHAR类型，减少资源的浪费。
2）【强制】其他不确定长度的字段，统一使用varchar相关的类型。
2.2.3 整型字段

1）【强制】明确无符号的数值，使用的整型。
2）【强制】能够用整型的字段尽量整型，提高查询和连接的性能，降低存储开销、CPU计算开销。如enum、ip、小额货币等。
2.2.4 Enum字段

1）【强制】禁止使用enum，可使用tinyint代替；
说明：因为修改ENUM需要使用ALTER语句，需要进行DDL操作， ENUM类型的ORDER BY操作效率低，需要额外操作。
2.2.5 默认值

1）【强制】所有字段都需要默认值，不允许为null，避免无法使用索引或null值引发BUG，如有特殊情况，可以存储空白字符代替null；
说明：null字段难以进行查询优化，索引需要额外的空间，复合索引无效，整体降低数据库处理的性能，也容易导致应用层程序报空指针异常。
2.2.6 二进制数据

1）【强制】禁止在数据库上存储图片、二进制文件等静态资源，应该使用合适的文件系统，数据库仅存储URL对于二进制多媒体数据、超大文本数据也不要放在数据库字段中；
2.2.7 Text/Blob字段

1）【强制】一般避免使用text、blob等类型字段，会浪费更多的磁盘和内存空间，非必要的大量的大字段查询会淘汰掉热数据，导致内存命中率急剧降低，影响数据库性能。
2）【强制】考虑使用varchar来代替，如果一定要使用text/blob，要离到单独的扩展表中，如果要用到索引，只能使用前缀索引。
2.2.8 日期时间字段

1）【推荐】timestamp类型比较精简，可以提高查询效率，减少磁盘空间及IO，但范围是1970年-2038年，考虑企业的历史及将来，建议使用int类型（10）存储日期时间戳；
2.2.9 金额字段

1）【强制】禁止使用float、double来定义金额字段，建议使用decimal类型或者bigint类型；
2）【强制】金额字段使用decimal类型，并给予足够的长度及精度。在性能要求比较苛刻的情况下，使用bigint类型，单位是分（如果是其他货币，需要定义其他单位）。
2.2.10 其他

电话字段

1）【强制】考虑到区号或者国家代号可能会涉及到±()等符号，并且需要支持模糊查询，所以应该使用字符类型，如varchar等；
坐标字段

1）【强制】表示坐标（0,0），应该使用两列表示，而不是将“0,0”放在1个列中。
预留字段

1）【推荐】预留字段的命名很难做到见名识义；预留字段无法确认存储的数据类型，所以无法选择合适的类型；预留字段是一种“过度设计”，我们应该做的就是“按需设计”，在经过详细有效的分析之后，在数据表中只放置必要的字段，而不要留出大量的备用字段。
2.3 索引设计

索引可以提高查询效率，但会降低更新效率，所以索引不是越多越好，原则是能不加就不加，要加的一定得加。
2.3.1 单表索引数量

1）【推荐】单表索引数量不超过5个。
2.3.2 单个索引的字段数量

1）【强制】单个索引中的字段数不超过5个。
2.3.3 字段选择

1）【强制】对于频繁更新的字段要评估读写比例和创建索引后的性能收益再决定是否创建索引。
比如一个字段每秒更新20次，但每秒查询达到100次，而且是直接通过该字段来定位数据行的，如果该字段没有索引就会导致全表扫描，如果更新也是需要使用该字段定位数据行也会导致更新出现全表扫描，这种情况就是一定要创建索引的。（相对应的一种情况是通过数据行的ID可以定位到数据行，不需要使用被更新字段定位数据行，这种情况就不适合创建索引）。
2）【强制】如“性别”这种区分度不大的字段，建立索引对查询性能的提升有限，与全表扫描差别不大。
3）【强制】已经建立唯一索引的字段，没有必要再建立与该字段有关的联合索引。
4）【强制】不要建立查询条件里根本不会出现的字段的索引或者联合索引。
2.3.4 联合索引

1）【强制】联合索引中各字段的顺序，要与查询语句的字段顺序保持一致，否则可能无法应用索引。
2）【强制】区分度最高的放在联合索引的最左侧。
3）【强制】使用最频繁的列放到联合索引的左侧。
4）【强制】尽量把字段长度小的列放在联合索引的最左侧。
2.3.5 前缀索引

1）【推荐】建立长字符串字段的前缀索引。
当要索引的列字符很多时，索引则会很大且变慢，这时候可以只索引列开始的部分字符串节约索引空间，降低重复的索引值，保证快速有效过滤数据的同时，节省维护索引的开销。
2.3.6 索引类型

1）【强制】唯一确定一条记录的一个字段或多个字段要建立主键或者唯一索引，不能唯一确定一条记录，为了提高查询效率建立普通索引。
2.4 主键设计

1）【推荐】一般不使用联合主键。
2）【强制】必须指定主键，建议使用内存型、数值型字段做主建，以应对大数据高并发的业务场景。如果使用自增列，在一定程度上依赖了数据库自身的特性，同时也要考虑分布式环境的全局唯一性。UUID是字符类型，增加索引磁盘空间及CPU开销，且不具备自增特性。
2.5 其他规约

在大数据、高并发的互联网业务，架构设计的思路是解放数据库，让应用层承担更到的责任。一般禁止使用与数据库自身特性相关的对象，如存储过程、触发器、视图等，降低业务耦合度，让数据库做最擅长的事情。
2.5.1 触发器

1）【推荐】禁止使用数据库的触发器特性，请在应用层寻求对应的解决方案。如有特殊需求，另行研究决定。
2.5.2 存储过程

1）【推荐】禁止使用数据库的存储过程特性，请在应用层寻求对应的解决方案。如有特殊需求，另行研究决定。
2.5.3 函数

1）【推荐】禁止使用数据库的函数特性，请在应用层寻求对应的解决方案。如有特殊需求，另行研究决定。
2.5.4 外键

1）【强制】禁止使用数据库的外键特性，请在应用层寻求对应的解决方案。如有特殊需求，另行研究决定。
说明：外键会导致表与表之间耦合，update与delete操作都会涉及相关联的表，影响sql 的性能，甚至会造成死锁，大数据高并发业务场景下容易造成数据库性能大幅下降。
2.5.5 约束设计

1）【强制】本规范禁止使用数据库的约束特性，请在应用层寻求对应的解决方案。如有特殊需求，另行研究决定。
说明：主键自身会有唯一性约束，其他约束如check、外键等，建议在应用层实现。
2.5.6 表分区

1）【强制】本规范禁止使用数据库的表分区特性，请在应用层寻求对应的解决方案。如有特殊需求，另行研究决定。
说明：分区表在物理上表现为多个文件，在逻辑上表现为一个表，实际性能不是非常好，且管理维护成本较高，建议采用物理分表的方式管理大数据，请参考分库分表策略相关的文档。
3 命名

3.1 基本规约

数据库的所有表(Table)、视图(View)、索引(Index)、触发器(Trigger)、函数(Function)和存储过程(Store  Procedure)均应遵循以下命名规范：
1）【强制】统一小写格式。
2）【强制】统一使用英文字母，数字和下划线来命名，禁止使用其他字符，如中横线等。
3）【强制】不超过32个字符，须见名知意，易于辨识。
4）【强制】禁止使用拼音来命名，禁止拼音英文混用。
5）【强制】禁止使用关键字，可以加上前缀区别关键字，参见附录一《关键字列表》
6）【推荐】临时库、临时表名必须以tmp为前缀并以时间戳为后缀。
7）【推荐】备份库、备份表名必须以bak为前缀并以时间戳为后缀。
8）【推荐】不同表中，存储相同数据的列名要保持一致。
3.2 库命名

1）【推荐】参考格式：[_业务类型/产品类型/其他类型]_
前缀：必选项，如baidu。
类型：非必选，但需要所有库要统一选还是不选。参考类型：产品类型/业务类型/其他类型。
库名：应尽可能和所服务的业务模块名一致。
正例： 

名称​	_​	__​
博客库	baidu_blog	baidu_ssp_blog
学院库	baidu_edu	baidu_ssp _edu
家园库	baidu_home	baidu_ssp _home
用户中心库	baidu_ucenter	baidu_ssp _ucenter
CMS库	baidu_cms	baidussp _cms
下载库	baidu_down	baidu_ssp _down
日志库	baidu_log	baidu_ssp _log

3.3 表命名

3.3.1 常规表

1）【推荐】参考格式：_。
表名应尽可能和所服务的业务模块名一致。
表名应尽量包含与所存放数据对应的单词或者缩写。
同一个模块的表应尽量以模块名(或缩写)为前缀。
正例： 

名称​	_​
博客用户表	blog_user
博客博文表	blog_blog
博客博文内容表	blog_blog_content
博客评论表	blog_comments
博客用户统计表	blog_user_stat

3.3.2 关联表

1）【推荐】参考格式：库名/库名缩写>___rel。
正例： 

名称​	表名1​	表名2​	___rel​
班级用户关联表	blog_class	blog_user	blog_class_user_ref

3.4 字段命名

1）【推荐】参考格式：[前缀_]
一般不用前缀（当和关键词冲突的可以考虑加前缀区别）。
字段名称也应尽量保持和实际数据相对应。
正例： 

名称​	[前缀_]​
用户ID	user_id
用户名	user_name
手机号	phone
创建时间	create_time
状态	status

3.5 索引命名

1）【推荐】普通索引：idx__。
2）【推荐】唯一索引：uidx__。
备注：
【idx】：表示索引，英文index。
【uidx】：表示唯一索引，英文unique index。
联合索引名称应尽量包含所有索引键字段名或缩写，且各字段名在索引名中的顺序应与索引键在索引中的索引顺序一致。
正例： 

普通索引​	唯一索引​
idx_users_username	uidx_users_uid_username：（user_id，username）

4 SQL

4.1 in/or

or的效率是n级别，in的效率是log(n)级别。
1）【强制】应尽量避免在子句中使用 or来连接条件，否则将导致引擎放弃使用索引而进行全表扫描。
2）【强制】in的个数建议控制在1000以内，避免使用在大集合中使用in。
4.2 select *

1）【强制】禁止使用SELECT *，应用层应指定所要的字段，避免消耗不必要的CPU、硬盘IO及网络带宽。
正例：SELECT `blog_id` FROM `blog`;
反例：SELECT * FROM `blog`;
4.3 union all

1）【推荐】使用union all替代union，union有去重开销，尽量由应用层实现去重。
4.4 模糊查询

1）【强制】禁止使用全模糊查询，无法使用索引，导致全表扫描。
2）【强制】可以使用右模糊查询，如like‘xxx%’，可以正常应用索引。
4.5 反向查询

1）【强制】禁止使用反向查询，如NOT、!=、、!、NOT IN、NOT LIKE等，会导致全表扫描。
4.6 隐式类型转换

1）【强制】禁止使用隐式转换，会导致索引失效。
说明：当操作符与不同类型的操作数一起使用时，会发生类型转换以使操作数兼容，则会发生转换隐式。比如user_id数据库字段设计时是int类型，sql中你写成了字符串类型，会导致索引失效。
4.7 join

1）【强制】大表连接字段和其他过滤条件字段没有合适的索引，禁止大表使用JOIN查询。
说明：大表join查询如果全表扫描，会产生临时表，消耗较多内存与CPU，极大影响数据库性能。
2）【推荐】禁止3表及以上连表查询，编写sql查询时，需要用explain分析sql执行效率（指标：扫描行数，是否用到索引，如果连表效率优于单表查询的条件下，允许3表连表）。
4.8 SQL表达式

1）【推荐】避免在数据库中使用数学运算、函数等，容易将业务逻辑和DB耦合在一起，且容易导致索引失效。
4.9 交互

1）【强制】减少与数据库的交互次数，也就是禁止循环查询数据库。
4.10 大批量

1）【推荐】Insert语句中，根据测试，批量一次插入1000条时效率最高，多于1000条时，要拆分，多次进行同样的插入，应该合并批量进行。
说明：大批量写操作会产生大量日志，日志传输和恢复所需要的时间过长，造成主从环境数据同步严重延迟，当因为这种延迟造成数据不一致时，可以考虑直接强制查询主库。
4.11 大事务

1）【推荐】遵循事务相关性最小原则。
2）【推荐】事务尽量简单，事务时间尽可能短。
说明：大批量修改数据，一定是在一个事务中进行的，这就会造成表中大批量数据进行锁定，从而导致大量的阻塞，阻塞会对数据库的性能产生非常大的影响。
4.12 索引字段顺序

1）【强制】查询条件中的字段，要把最有效的索引字段写在前面，同时要注意联合索引中的字段顺序。
4.13 insert 

1）【强制】禁止使用INSERT INTO t_xxx VALUES(xxx)，必须显示指定插入的列属性。
正例：INSERT INTO blog (‘blog_id’,’title’,’user_id’) VALUES(1,’标题’,1)
反例：INSERT INTO blog VALUES(1,’标题’,’1’)
4.14 DDL操作

1）【强制】应用程序里的语句，禁止一切 DDL 操作。
说明：如有特殊需要，必需与协商同意方可使用。
4.15 排序

1）【推荐】使用时，默认会进行排序，当你不需要排序时，可以使用order by null。
4.16 聚合函数

1）【强制】使用count(1)和count(*)代替count(column_name)。
说明：count(1)≈count(*)>count(主键ID)>count(column)
count(*)其实可以理解为等于count(0)，mysql会将参数 * 转化为参数 0 来进行处理，所以count(*)和count(1)的执行过程是基本一样的，性能上没有什么差异。
count(1)、 count(*)、 count(主键字段)在执行的时候，如果表里存在二级索引，优化器就会选择二级索引进行扫描。
不要使用字段)  来统计记录个数，因为它的效率是最差的，会采用全表扫描的方式来统计。如果你非要统计表中该字段不为 NULL 的记录个数，建议给这个字段建立一个二级索引
count()函数不会返回 NULL，但 sum()函数可能返回 NULL。
5 数据库域名

1）【强制】禁止使用IP连接数据库。
正例：

各个环境域名规范（xxx业务模块）​	命名​
开发环境	dev.xxx.db
测试环境	test.xxx.db
生产环境	prod.xxx.db
……	……
主从库域名命令规范
生产环境主库	prod-master.xxx.db
生产环境从库01	prod-slave-01.xxx.db
生产环境从库02	prod-slave-02.xxx.db
……	……

注意：
生产环境：英文取Production，缩写prod。
开发环境：英文取Development，缩写dev。
测试环境：英文取Test，缩写test。
从库：英文取Slave，缩写slave。
主库：英文取Master，缩写master。
6 用户行为

1）【强制】禁止分配super权限的账号给应用程序使用，super权限只能留给DBA处理问题的账号使用。
2）【强制】禁止在数据库中存储明文密码。
3）【强制】禁止从开发环境、测试环境直连线上数据库。
4）【强制】禁止在线上做数据库压力测试。
5）【强制】禁止使用IP连接数据库，应该使用内网域名。
6）【强制】禁止在生产环境创建test库。
7）【强制】合理分配数据库账号所拥有的权限，如应用程序账号原则上不准有drop权限。
8）【推荐】导入导出数据必须提前通知DBA，并让DBA协助观察。
9）【推荐】促销活动或者上线新功能必须提前通知DBA进行流量评估。
10）【推荐】不在业务高峰期批量更新，查询数据库。
11）【推荐】进行DDL/DML操作时，需要DBA进行审查，并在执行过程中观察服务负载等各种指标。
12）【推荐】对特别重要的库表，提前与DBA沟通确定维护和备份优先级。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！

---

欢迎光临 IT评测·应用市场-qidao123.com (https://dis.qidao123.com/)

Powered by Discuz! X3.4