史上最全Mysql规范

1 整体规约

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

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

2）【强制】排序规则默认使用utf8-general-ci；

1）【强制】默认使用INNODB存储引擎；

说明：MyISAM引擎从MYSQL 5.5版本后查询性能已经没InnoDB高，另外InnoDB的以主键为条件的查询性能是非常高的，且支持事务、行级锁、高并发性能更好、对多核CPU、大内存、SSD等硬件资源支持更好，利用率更高；

如需要使用基他类型的存储引擎，请在DBA的建议下使用；

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

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

2 数据库对象

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

1）【强制】单表数据量建议控制在1000万以内（参考值）；

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

1）【强制】表列数量建议控制在30个以内；

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

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

1）【推荐】如果按HASH散表，表名后缀使用十进制，下标从1开始。考虑后续的扩容，建议使用二叉树分库分表策略。

2）【推荐】如果按日期时间散表，表名需要符合YYYY[MM][DD][HH][mm][sss]的格式。

说明：大表查询效率很低，需要考虑水平拆分。根据业务特性有很多拆分方式。符合时间递增的表，可以根据时间来分，也可以ID的HASH方式来拆分，也可以通过某些特定字段的计算规则拆分。

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

1）【强制】存储相同数据的列名和列类型必须一致，否则会导致隐式转换，造成索引失效，降低查询效率；

2）【强制】在最大限度的满足可能的需要的前提下，字段应该尽可能的设计得短一些，以提高查询的效率，且降低索引对资源的消耗；

3）【强制】数据行的长度不要超过8020字节，如果超过这个长度的话在物理页中插入两行数据就会出现行链接从而造成存储碎片，降低查询效率；

4）【强制】单表列数量建议控制在30个以内；

5）【强制】尽量使用整型字段，代替IP、枚举类型、字符类型、浮点数类型；

6）【强制】所有字段都需要默认值，如有特殊情况，另作讨论决定；

1）【强制】长度变化不大的字段选择CHAR类型，减少资源的浪费。

2）【强制】其他不确定长度的字段，统一使用varchar相关的类型。

1）【强制】明确无符号的数值，使用的整型。

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

1）【强制】禁止使用enum，可使用tinyint代替；

说明：因为修改ENUM需要使用ALTER语句，需要进行DDL操作， ENUM类型的ORDER BY操作效率低，需要额外操作。

1）【强制】所有字段都需要默认值，不允许为null，避免无法使用索引或null值引发BUG，如有特殊情况，可以存储空白字符代替null；

说明：null字段难以进行查询优化，索引需要额外的空间，复合索引无效，整体降低数据库处理的性能，也容易导致应用层程序报空指针异常。

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

1）【强制】一般避免使用text、blob等类型字段，会浪费更多的磁盘和内存空间，非必要的大量的大字段查询会淘汰掉热数据，导致内存命中率急剧降低，影响数据库性能。

2）【强制】考虑使用varchar来代替，如果一定要使用text/blob，要离到单独的扩展表中，如果要用到索引，只能使用前缀索引。

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

1）【强制】禁止使用float、double来定义金额字段，建议使用decimal类型或者bigint类型；

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

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

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

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

索引可以提高查询效率，但会降低更新效率，所以索引越多越好。原则是，如果你不能加法，你必须加法。

[En]

The index can improve the query efficiency, but it will reduce the update efficiency, so the more indexes, the better. The principle is that if you can not add, you must add.

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

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

1）【强制】对于频繁更新的字段要评估读写比例和创建索引后的性能收益再决定是否创建索引。

比如一个字段每秒更新20次，但每秒查询达到100次，而且是直接通过该字段来定位数据行的，如果该字段没有索引就会导致全表扫描，如果更新也是需要使用该字段定位数据行也会导致更新出现全表扫描，这种情况就是一定要创建索引的。（相对应的一种情况是通过数据行的ID可以定位到数据行，不需要使用被更新字段定位数据行，这种情况就不适合创建索引）。

2）【强制】如”性别”这种区分度不大的字段，建立索引对查询性能的提升有限，与全表扫描差别不大。

3）【强制】已经建立唯一索引的字段，没有必要再建立与该字段有关的联合索引。

4）【强制】不要建立查询条件里根本不会出现的字段的索引或者联合索引。

1）【强制】联合索引中各字段的顺序，要与查询语句的字段顺序保持一致，否则可能无法应用索引。

2）【强制】区分度最高的放在联合索引的最左侧。

3）【强制】使用最频繁的列放到联合索引的左侧。

4）【强制】尽量把字段长度小的列放在联合索引的最左侧。

1）【推荐】建立长字符串字段的前缀索引。

当有许多列字符要编制索引时，索引将非常大且速度很慢。此时，只有索引列开头的部分字符串可以节省索引空间，减少重复索引值，确保快速有效地过滤数据。节省维护索引的成本。

[En]

When there are many column characters to be indexed, the index will be very large and slow. At this time, only part of the string at the beginning of the index column can save the index space, reduce the repeated index value, and ensure that the data is filtered quickly and effectively. save the cost of maintaining the index.

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

1）【推荐】一般不使用联合主键。

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

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

[En]

In big data, highly concurrent Internet business, the idea of architecture design is to liberate the database and let the application layer assume more responsibility. It is generally forbidden to use objects related to the characteristics of the database, such as stored procedures, triggers, views, etc., to reduce business coupling and let the database do what it does best.

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

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

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

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

说明：外键会导致表与表之间耦合，update与delete操作都会涉及相关联的表，影响sql 的性能，甚至会造成死锁，大数据高并发业务场景下容易造成数据库性能大幅下降。

1）【强制】本规范禁止使用数据库的约束特性，请在应用层寻求对应的解决方案。如有特殊需求，另行研究决定。

说明：主键自身会有唯一性约束，其他约束如check、外键等，建议在应用层实现。

1）【强制】本规范禁止使用数据库的表分区特性，请在应用层寻求对应的解决方案。如有特殊需求，另行研究决定。

注：分区表物理上表示为多个文件，逻辑上表示为一个表，实际性能不是很好，管理维护成本较高。建议使用物理子表来管理大数据。请参考与子库和子表策略相关的文档。

[En]

Note: the partition table is physically represented as multiple files and logically as a table, the actual performance is not very good, and the cost of management and maintenance is high. It is recommended to use physical sub-table to manage big data. Please refer to the documents related to the strategy of sub-database and sub-table.

3 命名

数据库的所有表(Table)、视图(View)、索引(Index)、触发器(Trigger)、函数(Function)和存储过程(Store Procedure)均应遵循以下命名规范：

1）【强制】统一小写格式。

2）【强制】统一使用英文字母，数字和下划线来命名，禁止使用其他字符，如中横线等。

3）【强制】不超过32个字符，须见名知意，易于辨识。

4）【强制】禁止使用拼音来命名，禁止拼音英文混用。

5）【强制】禁止使用关键字，可以加上前缀区别关键字，参见附录一《关键字列表》

6）【推荐】临时库、临时表名必须以tmp为前缀并以时间戳为后缀。

7）【推荐】备份库、备份表名必须以bak为前缀并以时间戳为后缀。

8）【推荐】不同表中，存储相同数据的列名要保持一致。

1）【推荐】参考格式：

前缀：必选项，如baidu。

类型：非必填项，但是否需要选择所有库。参照类型：产品类型/业务类型/其他类型。

[En]

Type: not required, but all libraries need to be selected or not. Reference type: product type / business type / other type.

库名称：应尽可能与所服务的业务模块的名称一致。

[En]

Library name: it should be consistent with the name of the business module served as far as possible.

正例：

名称 ​

* *​

* *​

博客库

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

1）【推荐】参考格式：

表名应尽可能与所服务的业务模块的名称匹配。

[En]

The table name should match the name of the business module served as far as possible.

表名应尽可能包含与存储数据对应的单词或缩写。

[En]

The table name should contain words or abbreviations corresponding to the stored data as far as possible.

同一模块的表应尽可能以模块名称(或缩写)为前缀。

[En]

The table of the same module should be prefixed with the module name (or abbreviation) as far as possible.

正例：

名称 ​

* *​

博客用户表

blog_user

博客博文表

blog_blog

博客博文内容表

blog_blog_content

博客评论表

blog_comments

博客用户统计表

blog_user_stat

1）【推荐】参考格式：库名/库名缩写>_

正例：

名称 ​

表名1 ​

表名2 ​

* *​

班级用户关联表

blog_class

blog_user

blog_class_user_ref

1）【推荐】参考格式：[前缀_]

一般不使用前缀(当与关键字冲突时可以考虑添加前缀进行区分)。

[En]

Generally do not use prefixes (when conflicts with keywords can be considered to add prefixes to distinguish).

字段名称也应尽可能与实际数据相对应。

[En]

Field names should also be kept corresponding to the actual data as far as possible.

正例：

名称 ​

[前缀_] ​

用户ID

user_id

用户名

user_name

手机号

phone

创建时间

create_time

status

1）【推荐】普通索引：idx_

2）【推荐】唯一索引：uidx_

备注：

【idx】：表示索引，英文index。

【uidx】：表示唯一索引，英文unique index。

联合索引名应该包含尽可能多的索引键字段名或缩写，并且索引名中字段名的顺序应该与索引中索引键的索引顺序相同。

[En]

The federated index name should contain as many index key field names or abbreviations as possible, and the order of the field names in the index name should be the same as the index order of the index key in the index.

正例：

普通索引 ​

唯一索引 ​

idx_users_username

uidx_users_uid_username：（user_id，username）

4 SQL

or的效率是n级别，in的效率是log(n)级别。

1）【强制】应尽量避免在子句中使用 or来连接条件，否则将导致引擎放弃使用索引而进行全表扫描。

2）【强制】in的个数建议控制在1000以内，避免使用在大集合中使用in。

1）【强制】禁止使用SELECT *，应用层应指定所要的字段，避免消耗不必要的CPU、硬盘IO及网络带宽。

正例：SELECT blog_id FROM blog;

反例：SELECT * FROM blog;

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

1）【强制】禁止使用全模糊查询，无法使用索引，导致全表扫描。

2）【强制】可以使用右模糊查询，如like’xxx%’，可以正常应用索引。

1）【强制】禁止使用反向查询，如NOT、!=、<>、!

1）【强制】禁止使用隐式转换，会导致索引失效。

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

1）【强制】大表连接字段和其他过滤条件字段没有合适的索引，禁止大表使用JOIN查询。

说明：大表join查询如果全表扫描，会产生临时表，消耗较多内存与CPU，极大影响数据库性能。

2）【推荐】禁止3表及以上连表查询，编写sql查询时，需要用explain分析sql执行效率（指标：扫描行数，是否用到索引，如果连表效率优于单表查询的条件下，允许3表连表）。

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

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

1）【推荐】Insert语句中，根据测试，批量一次插入1000条时效率最高，多于1000条时，要拆分，多次进行同样的插入，应该合并批量进行。

注意：海量写操作会产生大量日志，日志传输和恢复时间过长，导致主备环境数据同步严重延迟。当该延迟导致数据不一致时，可以考虑直接强制查询master数据库。

[En]

Note: mass write operations will generate a large number of logs, and the time required for log transfer and recovery is too long, resulting in serious delays in master-slave environment data synchronization. When data inconsistencies are caused by this delay, you can consider directly forcing the query of the master database.

1）【推荐】遵循事务相关性最小原则。

2）【推荐】事务尽量简单，事务时间尽可能短。

描述：在一个事务中必须进行大量的数据修改，这会导致表中的大量数据被锁定，导致大量的阻塞，阻塞会对数据库的性能产生很大的影响。

[En]

Description: mass modification of data must be carried out in a transaction, which will cause a large number of data in the table to be locked, resulting in a large number of blocking, blocking will have a great impact on the performance of the database.

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

1）【强制】禁止使用INSERT INTO t_xxx VALUES(xxx)，必须显示指定插入的列属性。

正例：INSERT INTO blog (‘blog_id’,’title’,’user_id’) VALUES(1,’标题’,1)

反例：INSERT INTO blog VALUES(1,’标题’,’1′)

1）【强制】应用程序里的语句，禁止一切 DDL 操作。

注：如有特殊需要，须经协商同意后方可使用。

[En]

Note: if there is a special need, it must be agreed with consultation before use.

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

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沟通确定维护和备份优先级。

Original: https://www.cnblogs.com/freestu/p/16555248.html
Author: 存活至此的李元霸
Title: 史上最全Mysql规范