【笔记】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 来存储。例如:

【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】
建议使用合理的分页⽅式以提⾼分⻚效率。 假如有类似下⾯分⻚语句:

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

【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 建⽴索引,通常的做法分为两步:

正确的做法是:

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

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

喜欢(1) 打赏

评论抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏

博主在线

博主在线

  • 扫描二维码,微信联系 扫描二维码,微信联系