【笔记】Mysql 数据库操作规范

【笔记】Mysql 数据库操作规范

1. 命名规范：

1. 库名、表名、字段名必须使用小写字母，并采用下划线分割。[FAQ-1-01]
2. 库名、表名、字段名禁⽌超过 32 个字符。[FAQ-1-02]
3. 库名、表名、字段名必须见名知意。命名与业务、产品线等相关联。
4. 库名、表名、字段名禁止使用 MySQL 保留字。[FAQ-1-03] 【MySQL 保留字】
5. 临时库、表名必须以 tmp 为前缀，并以日期为后缀。例如 tmp_test01_20180704。
6. 备份库、表名必须以 bak 为前缀，并以日期为后缀。例如 bak_test01_20180704。

2. 基础规范：

1. 使⽤ InnoDB 存储引擎。[FAQ-2-01]
2. 库表字符集默认使⽤ UTF8MB4，特殊情况可以使用 UTF8 等其他字符集。[FAQ-2-02]
3. 所有表都需要添加注释，除主键外的其他字段都需要增加注释。推荐采⽤英文标点，避免出现乱码。
4. 禁⽌在数据库中存储图片、文件等大数据。
5. 每张表数据量建议控制在 5000W 以内。
6. 禁⽌在线上做数据库压力测试。
7. 禁止从测试、开发环境直连线上数据库。

3. 库表设计：

1. 禁⽌使用分区表。[FAQ-3-01]
2. 将⼤字段、访问频率低的字段拆分到单独的表中存储，分离冷热数据。[FAQ-3-02]
3. 推荐使⽤ HASH 进行拆表，表名后缀使用⼗进制数，数字必须从 0 开始。
4. 按⽇期时间分表需符合 YYYY[MM][DD][HH] 格式，例如 2018071601。年份必须用 4 位数字表示。例如按日散表 user_20180709、按月散表 user_201807。
5. 采用合适的分库分表策略。例如千库十表、⼗库百表等。[FAQ-3-03]

4. 字段设计：

1. 建议使用 UNSIGNED 存储非负数值。[FAQ-4-01]
2. 建议使用 INT UNSIGNED 存储 IPV4。[FAQ-4-02]
3. ⽤ DECIMAL 代替 FLOAT 和 DOUBLE 存储精确浮点数。例如与货币、金融相关的数据。
4. INT 类型固定占用 4 字节存储，例如 INT(4) 仅代表显⽰字符宽度为 4 位，不代表存储长度。[FAQ-4-03]
5. 区分使用 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT 数据类型。例如取值范围为 0 -80 时，使用 TINYINT UNSIGNED。【数据类型存储空间需求说明】
6. 强烈建议使用 TINYINT 来代替 ENUM 类型。[FAQ-4-04]
7. 禁止使用 TEXT、BLOB 类型。
8. 禁⽌在数据库中存储明文密码。[FAQ-4-05]
9. 使用 VARBINARY 存储⼤小写敏感的变⻓字符串或二进制内容。[FAQ-4-06]
10. 使⽤尽可能⼩的 VARCHAR 字段。VARCHAR(N) 中的 N 表示字符数而非字节数。
11. 区分使⽤ DATETIME 和 TIMESTAMP。存储年使用 YEAR 类型。存储日期使用 DATE 类型。存储时间（精确到秒甚至微妙）建议使用 TIMESTAMP 类型。[FAQ-4-07]
12. DATETIME 和 TIMESTAMP 类型字段禁止使用 ‘0000-00-00 00:00:00’ 作为 DEFAULE 值。
13. 所有字段均定义为 NOT NULL。[FAQ-4-08]

【笔记】Mysql 数据库操作规范

InnoDB 存储字段补充说明

5. 索引规范：

1. 单张表中索引数量不超过 5 个。
2. 单个索引中的字段数不超过 5 个。
3. 索引名必须全部使用⼩写。
4. ⾮唯⼀索引按照“idx_字段名称 [字段名称]”进⾏命名。例如 idx_age_name。 唯⼀索引按照“uniq 字段名称 [_字段名称]”进行命名。例如 uniq_age_name。
5. 组合索引建议包含所有字段名，过⻓的字段名可以采⽤缩写形式。例如 idx_age_name_add。
6. 表必须有主键，推荐使用 UNSIGNED ⾃增列作为主键。[FAQ-5-01]
7. 唯一键由 3 个以下字段组成，并且字段都是整型时，可使⽤唯⼀键作为主键。其他情况下，建议使⽤⾃增列或发号器作主键。
8. 禁⽌冗余索引。[FAQ-5-02]
9. 禁止重复索引。[FAQ-5-03]
10. 禁止使⽤外键。
11. 联表查询时，JOIN 列的数据类型必须相同，并且要建立索引。
12. 不在低基数列上建立索引，例如“性别”。[FAQ-5-04]
13. 选择区分度⼤的列建立索引。组合索引中，区分度⼤的字段放在最前。
14. 对字符串使用前缀索引，前缀索引长度不超过 8 个字符。
15. 不对过⻓的 VARCHAR 字段建⽴索引。建议优先考虑前缀索引，或添加 CRC32 或 MD5 伪列并建⽴索引。
16. 合理创建联合索引，(a，b，c) 相当于 (a)、(a，b)、(a，b，c)。
17. 合理使用覆盖索引减少 IO，避免排序。[FAQ-5-05]

6. SQL设计规范：

1. 使⽤ prepared statement，可以提升性能并避免 SQL 注入。
2. ⽤ IN 代替 OR。SQL 语句中 IN 包含的值不应过多，应少于 1000 个。[FAQ-6-01]
3. 禁⽌隐式转换。数值类型禁止加引号；字符串类型必须加引号。
4. 避免使用 JOIN 和⼦查询。必要时推荐用 JOIN 代替子查询。
5. 避免在 MySQL 中进⾏数学运算和函数运算。
6. 减少与数据库交互次数，尽量采用批量 SQL 语句。[FAQ-6-02]
7. 拆分复杂 SQL 为多个小 SQL，避免⼤事务。[FAQ-6-03]
8. 获取⼤量数据时，建议分批次获取数据，每次获取数据少于 5000 条，结果集应⼩于 1M。
9. ⽤ UNION ALL 代替 UNION。[FAQ-6-04]
10. 统计⾏数用 COUNT(*)。
11. SELECT 只获取必要的字段，禁⽌使用 SELECT *。[FAQ-6-05]
12. SQL 中避免出现 now()、rand()、sysdate()、current_user() 等不确定结果的函数。[FAQ-6-06]
13. INSERT 语句必须指定字段列表，禁⽌使用 INSERT INTO TABLE VAULES()。
14. 禁⽌单条 SQL 语句同时更新多个表。
15. 避免使⽤存储过程、触发器、视图、⾃定义函数等。[FAQ-6-07]
16. 建议使用合理的分页方式以提⾼分页效率。[FAQ-6-08]
17. 禁止在主库上执⾏后台管理和统计类功能的 QUERY，必要时申请统计类从库。
18. 程序应有捕获 SQL 异常的处理机制，必要时通过 rollback 显式回滚。
19. 重要 SQL 必须被索引：update、delete 的 where 条件列、order by、group by、distinct 字段、多表 join 字段。
20. 禁止使用 % 前导查询，例如：like “%abc”，⽆法利⽤到索引。
21. 禁止使⽤负向查询，例如：not in、!=、<>、not like。
22. 使⽤ EXPLAIN 判断 SQL 语句是否合理使用索引，尽量避免 extra 列出现：Using File Sort、Using Temporary 等。
23. 禁⽌使⽤ order by rand()。[FAQ-6-09]
24. 禁止使用 INSERT INTO SELECT 和 UPDATE a,b 等多表关联的 DML 语句格式。
25. 禁止使用 SELECT LOCK IN SHARE MODE 和 SELECT FOR UPDATE 的预先占用锁资源的语句格式。

7. 操作行为规范：

1. 表结构变更必须通知 DBA 进⾏审核。
2. 禁⽌有 SUPER 权限的应⽤程序账号存在。[FAQ-7-01]
3. 禁⽌有 DDL、DCL 权限的应⽤程序账号存在。
4. 重⼤项⽬的数据库⽅案选型和设计必须提前通知 DBA 参与。
5. 批量导⼊、导出数据必须通过 DBA 审核，并在执行过程中观察服务。
6. 批量更新数据，如 UPDATE、DELETE 操作，必须 DBA 进⾏审核，并在执⾏过程中观察服务。
7. 产品出现非数据库导致的故障时，如被攻击，必须及时通 DBA，便于维护服务稳定。
8. 业务部⻔程序出现 BUG 等影响数据库服务的问题，必须及时通知 DBA，便于维护服务稳定。
9. 业务部⻔推⼴活动或上线新功能，必须提前通知 DBA 进行服务和访问量评估，并留出必要时间以便 DBA 完成扩容。
10. 出现业务部门⼈为误操作导致数据丢失，需要恢复数据的，必须第一时间通知 DBA，并提供准确时间点、误操作语句等重要线索。
11. 提交线上建表改表需求，必须详细注明涉及到的所有 SQL 语句 (包括 SELECT、INSERT、DELETE、UPDATE)，便于 DBA 进行审核和优化。[FAQ-7-02]
12. 对同⼀个表的多次 ALTER 操作必须合并为⼀次操作。[FAQ-7-03]
13. 不要在 MySQL 数据库中存放业务逻辑。[FAQ-7-04]

8. 数据库账号规范：

1. 业务程序必须实现读写分离，读写账号以后缀 “_w” 命名，只读账号以后缀 “_r” 命名。
2. 程序账号权限遵循最小化授权原则，只能是 SELECT、INSERT、UPDATE、DELETE，除非特殊情况可以授予 CREATE 和 DROP 权限。
3. 开发以及其他业务人员的账号，例如 zhangsan01，权限为 SELECT 且只允许访问申请的库表。
4. 不同后端平台以及运维程序，需要使用不同的数据库账号，例如备份程序采用 mysqlbackup 命名，主从同步账号采用 replication 命名等。
5. 数据库授权采用白名单 IP 方式，除非特殊情况否则不能授予带 % 的 IP 范围。
6. 数据库授权粒度为库级别，除非特殊情况可以授权到表级别；MySQL 的系统库如 mysql、sys 等不对外授权。
7. 数据库中不允许有名字为 “dba” 的账号，初始化数据库后需要将该用户删除。
8. 所有数据库账号的密码均不能为空，密码采用 16 位大小写字母和数字的组合。
9. 数据库账号 / 密码不允许在邮件、企业微信群、家信群中公开，一经发现，DBA 有权立即进行重置。
10. 相同数据库账号的权限必须保持一致；主从数据库的账号授权保持一致。

9. FAQ 和附录：

【FAQ-1-01】 库名、表名、字段名必须使⽤小写字母，并采⽤下划线分割。 a)MySQL 有配置参数 lower_case_table_names，不可动态更改，linux 系统默认为 0，即库表名以实际情况存储，⼤小写敏感。如果是 1，以⼩写存储，⼤小写不敏感。如果是 2，以实际情况存储，但以小写⽐较。 b) 如果⼤小写混合使用，可能存在 abc，Abc，ABC 等多个表共存，容易导致混乱。 c) 字段名显⽰区分⼤⼩写，但实际使用不区分，即不可以建立两个名字⼀样但大小写不一样的字段。 d) 为了统⼀规范，库名、表名、字段名使⽤⼩写字母。

【FAQ-1-02】 库名、表名、字段名禁止超过 32 个字符。 库名、表名、字段名⽀持最多 64 个字符，但为了统一规范、易于辨识以及减少传输量，禁⽌超过 32 个字符。

【FAQ-1-03】 库名、表名、字段名禁止使用 MySQL 保留字。 当库名、表名、字段名等属性含有保留字时，SQL 语句必须⽤反引号引用属性名称，这将使得 SQL 语句书写、SHELL 脚本中变量的转义等变得非常复杂。

【FAQ-2-01】 使⽤用 InnoDB 存储引擎。InnoDB 引擎是 MySQL5.5 版本以后的默认引擘，⽀持事务、行级锁，有更好的数据恢复能力、更好的并发性能，同时对多核、⼤内存、SSD 等硬件⽀持更好，⽀持数据热备份等，因此 InnoDB 相比 MyISAM 有明显优势。

【FAQ-2-02】 库表字符集使用 UTF8MB4，特殊情况可以使用 UTF8 等其他字符集。 a)UTF8 字符集存储汉字占用 3 个字节，UTF8MB4 字符集存储汉子占用 4 个字节，存储英⽂字符均只占⽤一个字节。 b)UTF8 和 UTF8MB4 统一而且通用，不会出现转码出现乱码风险。 c)EMOJ 等表情符号的存储需求，只能使用 UTF8MB4 字符集。 d) 不同字符集的字段关联时无法使用索引。

【FAQ-3-01】 禁⽌使⽤分区表。分区表对分区键有严格要求；分区表在表变大后，执⾏行 DDL、SHARDING、单表恢复等都变得更加困难。因此禁止使⽤分区表，并建议业务端手动 SHARDING。

【FAQ-3-02】 将⼤字段、访问频率低的字段拆分到单独的表中存储，分离冷热数据。有利于有效利用缓存，防止读入⽆用的冷数据，较少磁盘 IO，同时保证热数据常驻内存提高缓存命中率。

【FAQ-3-03】 采⽤合适的分库分表策略。例如千库十表、⼗库百表等。采用合适的分库分表策略，有利于业务发展后期快速对数据库进⾏⽔平拆分，同时分库可以有效利用 MySQL 的多线程并行复制特性。

【FAQ-4-01】 建议使用 UNSIGNED 存储⾮负数值。同样的字节数，⾮负存储的数值范围更大。如 TINYINT 有符号为 -128~127，无符号为 0~255。

【FAQ-4-02】 建议使用 INT UNSIGNED 存储 IPV4。 ⽤ UNSINGED INT 存储 IP 地址占用 4 字节，CHAR(15) 则占用 15 字节。另外，计算机处理整数类型比字符串类型快。使用 INT UNSIGNED ⽽不是 CHAR(15) 来存储 IPV4 地址，通过 MySQL 函数 inet_ntoa 和 inet_aton 来进行转化。IPv6 地址⽬前没有转化函数，需要使用 DECIMAL 或两个 BIGINT 来存储。例如:

Shell

SELECT INET_ATON('209.207.224.40'); 3520061480 SELECT INET_NTOA(3520061480); 209.207.224.40

【FAQ-4-03】 INT 类型固定占用 4 字节存储，例如 INT(4)仅代表显示字符宽度为 4 位，不代表存储长度。数值类型括号后面的数字只是表示宽度而跟存储范围没有关系，比如 INT(3)默认显示 3 位，空格补齐，超出时正常显示。python、java 等客户端不具备这个功能。

【FAQ-4-04】 强烈建议使用 TINYINT 来代替 ENUM 类型。ENUM 类型在需要修改或增加枚举值时，需要在线 DDL，成本较高；ENUM 列值如果含有数字类型，可能会引起默认值混淆。【ENUM 类型说明】

【FAQ-4-05】 禁⽌在数据库中存储明⽂密码。采用加密字符串存储密码，并保证密码不可解密，同时采用随机字符串加密保证密码安全。防⽌数据库数据被公司内部人员或黑客获取后，采用字典攻击等方式暴⼒破解用户密码。

【FAQ-4-06】 使⽤用 VARBINARY 存储⼤小写敏感的变⻓字符串或二进制内容。VARBINARY 默认区分⼤小写，没有字符集概念，速度快。

【FAQ-4-07】 区分使⽤ DATETIME 和 TIMESTAMP。存储年使⽤ YEAR 类型。存储⽇期使⽤用 DATE 类型。存储时间 (精确到秒甚至微妙) 建议使用 TIMESTAMP 类型。 DATETIME 和 TIMESTAMP 都可以精确到秒，优先选择 TIMESTAMP（可精确到微妙），因为 TIMESTAMP 只有 4 个字节，⽽ DATETIME8 个字节。同时 TIMESTAMP 具有⾃动赋值以及自动更新的特性。【TIMESTAMP 字段类型初始化】

【FAQ-4-08】 所有字段均定义为 NOT NULL。 a)对表的每⼀行，每个为 NULL 的列都需要额外的空间来标识。 b)B+ 树索引不会存储 NULL 值，所以如果索引字段可以为 NULL，索引效率会下降。 c)建议用 0、特殊值或空串代替 NULL 值。

【FAQ-5-01】 表必须有主键，推荐使⽤ UNSIGNED 自增列作为主键。表没有主键，INNODB 会默认设置隐藏的主键列；没有主键的表在定位数据行的时候非常困难，也会降低基于行复制的效率。

【FAQ-5-02】 禁⽌冗余索引。索引是双刃剑，会增加维护负担，增⼤ IO 压力。(a,b,c)、(a,b)，后者为冗余索引。可以利⽤前缀索引来达到加速目的，减轻维护负担。

【FAQ-5-03】 禁⽌重复索引。 primary key a;uniq index a; 重复索引增加维护负担、占⽤磁盘空间，同时没有任何益处。

【FAQ-5-04】 不在低基数列上建⽴索引，例如“性别”。大部分场景下，低基数列上建立索引的精确查找，相对于不建⽴索引的全表扫描没有任何优势，⽽且增⼤了 IO 负担。

【FAQ-5-05】 合理使用覆盖索引减少 IO，避免排序。覆盖索引能从索引中获取需要的所有字段，从⽽避免回表进行⼆次查找，节省 IO。InnoDB 存储引擎中，secondary index(⾮主键索引，又称为辅助索引、⼆级索引)没有直接存储行地址，⽽是存储主键值。如果⽤户需要查询 secondary index 中所不包含的数据列，则需要先通过 secondary index 查找到主键值，然后再通过主键查询到其他数据列，因此需要查询两次。覆盖索引则可以在一个索引中获取所有需要的数据，因此效率较高。主键查询是天然的覆盖索引。例如 SELECT email，uid FROM user_email WHERE uid=xx，如果 uid 不是主键，适当时候可以将索引添加为 index(uid，email)，以获得性能提升。

【FAQ-6-01】 ⽤ IN 代替 OR。SQL 语句中 IN 包含的值不应过多，应少于 1000 个。IN 是范围查找，MySQL 内部会对 IN 的列表值进行排序后查找，⽐ OR 效率更⾼。

【FAQ-6-02】 减少与数据库交互次数，尽量采用批量 SQL 语句。使⽤下面的语句来减少和 DB 的交互次数:

• a)INSERT … ON DUPLICATE KEY UPDATE
• b)REPLACE INTO
• c)INSERT IGNORE
• d)INSERT INTO VALUES()

【FAQ-6-03】 拆分复杂 SQL 为多个小 SQL，避免大事务。 简单的 SQL 容易使用到 MySQL 的 QUERY CACHE；减少锁表时间特别是 MyISAM；可以使用多核 CPU。

【FAQ-6-04】 ⽤ UNION ALL 代替 UNION。 UNION ALL 不需要对结果集再进行排序。

【FAQ-6-05】 SELECT 只获取必要的字段，禁⽌使⽤ SELECT *。 减少网络带宽消耗；能有效利用覆盖索引；表结构变更对程序基本⽆影响。

【FAQ-6-06】 SQL 中避免出现 now()、rand()、sysdate()、current_user()等不确定结果的函数。 语句级复制场景下，引起主从数据不一致；不确定值的函数，产⽣的 SQL 语句⽆法利用 QUERY CACHE。

【FAQ-6-07】 避免使⽤存储过程、触发器、视图、自定义函数等。 这些⾼级特性有性能问题，以及未知 BUG 较多。业务逻辑放到数据库会造成数据库的 DDL、SCALE OUT、SHARDING 等变得更加困难。

【FAQ-6-08】 建议使用合理的分页⽅式以提⾼分⻚效率。 假如有类似下⾯分⻚语句:

Shell

SELECT * FROM table ORDER BY TIME DESC LIMIT 10000,10;

这种分页方式会导致⼤量的 IO，因为 MySQL 使用的是提前读取策略。 推荐分页⽅式:

Shell

SELECT * FROM table WHERE TIME<last_TIME ORDER BY TIME DESC LIMIT 10; SELECT * FROM table inner JOIN (SELECT id FROM table ORDER BY TIME LIMIT 10000，10) as t USING(id);

【FAQ-6-09】 禁止使⽤ order by rand()。 order by rand() 会为表增加⼀个伪列，然后⽤ rand() 函数为每一行数据计算出 rand() 值，然后基于该行排序，这通常都会生成磁盘上的临时表，因此效率非常低。建议先使用 rand() 函数获得随机的主键值，然后通过主键获取数据。

【FAQ-7-01】 禁⽌有 SUPER 权限的应用程序账号存在。 安全第⼀。SUPER 权限会导致 read only 失效，导致较多诡异问题而且很难追踪。

【FAQ-7-02】 提交线上建表改表需求，必须详细注明涉及到的所有 SQL 语句 (包括 SELECT、INSERT、DELETE、UPDATE)，便于 DBA 进⾏审核和优化。 并不只是 SELECT 语句需要⽤到索引。UPDATE、DELETE 都需要先定位到数据才能执行变更。因此需要业务提供所有的 SQL 语句便于 DBA 审核。

【FAQ-7-03】 对同⼀个表的多次 ALTER 操作必须合并为一次操作。 MySQL 对表的修改绝大部分操作都需要锁表并重建表，而锁表则会对线上业务造成影响。为减少这种影响，必须把对表的多次 ALTER 操作合并为⼀次操作。例如，要给表 t 增加⼀个字段 b，同时给已有的字段 aa 建⽴索引，通常的做法分为两步:

Shell

alter table t add column b varchar(10); 然后增加索引: alter table t add index idx_aa(aa);

正确的做法是:

Shell

alter table t add column b varchar(10),add index idx_aa(aa);

【FAQ-7-04】 不要在MySQL数据库中存放业务逻辑。数据库是有状态的服务，变更复杂而且速度慢，如果把业务逻辑放到数据库中，将会限制业务的快速发展。建议把业务逻辑提前，放到前端或中间逻辑层，⽽把数据库作为存储层，实现逻辑与存储的分离。

附录1 数据库建表语句示例：

Shell

CREATE TABLE `house_showing_house` ( `id` bigint(10) unsigned NOT NULL AUTO_INCREMENT, `showing_record_id` int(10) unsigned NOT NULL COMMENT '带看表主键', `house_code` char(15) NOT NULL COMMENT '房源SE编号', `agent_code` int(10) unsigned NOT NULL COMMENT '经纪人SE编号', `agent_ucid` int(10) unsigned NOT NULL COMMENT '经纪人UC编号', `agent_name` varchar(64) NOT NULL COMMENT '经纪人姓名', `customer_code` varbinary(16) NOT NULL COMMENT '客户编号, 如果有的话', `customer_ucid` int(10) unsigned NOT NULL COMMENT '客户UC编号', `customer_name` varchar(32) NOT NULL COMMENT '客户姓名', `hdic_city_id` smallint(5) unsigned NOT NULL COMMENT '楼盘字典 城市ID', `hdic_resblock_id` int(10) unsigned NOT NULL COMMENT '楼盘字典 楼盘ID', `see_time` datetime NOT NULL DEFAULT '1990-01-01 00:00:00' COMMENT '带看时间', `appid` int(11) NOT NULL COMMENT '信息来源', `app_pkid` bigint(20) NOT NULL COMMENT 'app的主键', `satisfaction` tinyint(1) unsigned NOT NULL COMMENT '客户是否满意', `reciprocal_frame` tinyint(4) unsigned DEFAULT '0' COMMENT '1看的本户型，2看的反户型，3看的本房', `feedback` varchar(512) NOT NULL COMMENT '反馈内容', `status` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '状态值', `audit_status` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '审核状态：0待审，1通过，2拒绝', `create_time` timestamp NOT NULL DEFAULT '1970-01-01 08:00:01', `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `uniq_customer_code` (`customer_code`), KEY `idx_customer_ucid` (`customer_ucid`), KEY `idx_appid_pkid` (`appid`,`app_pkid`), KEY `idx_customer_name_4` (`customer_name(4)`), KEY `idx_update_time` (`update_time`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='房源带看信息表';

喜欢(1) 打赏