数据库开发规范

ゝ一纸荒年。 2022-06-07 13:55 195阅读 0赞

数据库开发规范

一、 表设计

二、 索引

三、 SQL语句

四、 散表

五、 线上数据库维护操作

一、 表设计

1.  库名、表名、字段名必须使用小写字母或者“\_”分割。
2.  库名、表名、字段名必须不超过12个字符。
3.  库名、表名、字段名见名知意。
4.  建议使用InnoDB存储引擎。
5.  存储精确浮点数必须使用DECIMAL替代FLOAT和DOUBLE。
6.  建议使用UNSIGNED存储非负数值。
7.  整形定义中不添加长度，比如使用INT，而不是INT(4)。
8.  使用短数据类型，比如取值范围为0-80时，使用TINYINT UNSIGNED。
9.  当只有‘0’，‘1’时不建议使用ENUM类型，使用TINYINT来代替。
10. 尽可能不使用TEXT、BLOB类型。
11. VARCHAR(N)，N表示的是字符数不是字节数，比如VARCHAR(255)，可以最大可存储255个汉字，需要根据实际的宽度来选择N。
12. VARCHAR(N)，N尽可能小，因为MySQL一个表中所有的VARCHAR字段最大长度是65535个字节，进行排序和创建临时表一类的内存操作时，会使用N的长度申请内存。
13. 存储年使用YEAR类型。
14. 存储日期使用DATE类型。
15. 存储时间（精确到秒）建议使用TIMESTAMP类型，因为TIMESTAMP使用4字节，DATETIME使用8个字节。
16. 建议字段定义为NOT NULL。
17. 将过大字段拆分到其他表中。
18. 禁止在数据库中使用VARBINARY、BLOB存储图片、文件等。
19. 表结构变更需要通知DBA审核。
20. 默认编码采用utf8格式
21. 所有表&列需有对应注释，以便后期维护
22. 表字段命名注意不要使用其保留字，否则会报错  
    二、 索引
23. 非唯一索引必须按照“idx\_表名称*字段名称\[*字段名\]”进行命名。
24. 唯一索引必须按照“uiq\_表名称*字段名称\[*字段名\]”进行命名。
25. 索引名称必须使用小写。
26. 索引中的字段数建议不超过5个。
27. 单张表的索引数量控制在5个以内。
28. 唯一键由3个以下字段组成，并且字段都是整形时，使用唯一键作为主键。
29. 没有唯一键，使用自增（或者通过发号器获取）id作为主键。
30. 唯一键不和主键重复。
31. 索引字段的顺序需要考虑字段值去重之后的个数，个数多的放在前面。
32. ORDER BY，GROUP BY，DISTINCT的字段需要添加在索引的后面。
33. UPDATE、DELETE语句需要根据WHERE条件添加索引。
34. 不建议使用%前缀模糊查询，例如LIKE “%weibo”。可以使用 LIKE ‘WEIBO%’
35. 合理创建联合索引（避免冗余），(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c)。
36. 合理利用覆盖索引。
37. SQL变更需要确认索引是否需要变更并通知DBA。  
    三、 SQL语句
38. SQL语句中IN包含的值不应过多。
39. UPDATE、DELETE语句不使用LIMIT。
40. WHERE条件中必须使用合适的类型，避免MySQL进行隐式类型转化。
41. SELECT语句只获取需要的字段。
42. SELECT、INSERT语句必须显式的指明字段名称，不使用SELECT \*，不使用INSERT INTO table()。
43. 使 用SELECT column\_name1, column\_name2 FROM table WHERE \[condition\]而不是SELECT column\_name1 FROM table WHERE \[condition\]和SELECT column\_name2 FROM table WHERE \[condition\]。
44. WHERE条件中的非等值条件（IN、BETWEEN、<、<=、>、>=）如果频繁使用，需要把相关的SQL发给DBA来进行分析看是否合理运用了 INDEX
45. 避免在SQL语句进行数学运算或者函数运算，容易将业务逻辑和DB耦合在一起。
46. 使用合理的SQL语句减少与数据库的交互次数。
47. 建议使用合理的分页方式以提高分页的效率。
48. 统计表中记录数时使用COUNT(\*)，而不是COUNT(字段)。
49. 避免使用复杂的SQL,复杂的SQL，将大的SQL拆分成多条简单SQL分步执行
50. 避免子查询方式，可以采用join代替  
    错误写法：select name from a where id in(select id from b)  
    正确写法：select a.name from a inner join b on a.id=b.id
51. 要左右连接的时候统一使用left join，避免right join，提高代码的可读性。  
    错误写法：select a.name from b right join a on a.id=b.id where b.id is null  
    正确写法：select a.name from a left join b on a.id=b.id where b.id is null

四、 散表

1.  每张表数据量建议控制在5000w以下
2.  可以结合使用hash、range、进行散表。
3.  使用时间散表，表名后缀必须使用特定格式，比如按日散表user\_20110209、按月散表user\_201102。
4.  MYSQL分区表（[http://dev.mysql.com/tech-resources/articles/mysql\_55\_partitioning.html][http_dev.mysql.com_tech-resources_articles_mysql_55_partitioning.html]）
5.  依据业务需求可以对表进行水平切分或者垂直切分  
    五、 线上数据库维护操作
6.  如果线上后台进行批量导入、导出数据需要通知DBA进行审查，并在执行过程中观察服务。
7.  批量更新数据，如update,delete 操作，需要DBA进行审查，并在执行过程中观察服务。
8.  产品出现非数据库平台运维导致的问题和故障时，请及时通知DBA，便于维护服务稳定。
9.  业务部门程序出现bug等影响数据库服务的问题,请及时通知DBA，便于维护服务稳定。
10. 业务部门推广活动，请提前通知DBA进行数据库服务评估及监控。
11. 如果出现业务部门人为误操作导致数据丢失，需要恢复数据，请在第一时间通知DBA，并提供准确时间，误操作语句等重要线索。
12. 原则上业务因更新时，不许对数据库进行DROP(表，列)及RENAME(表，列)操作但可以ADD。

线上数据库表结构即使部分表字段废弃，但也为了业务及数据恢复完整性，不应直接DROP 掉。

[http_dev.mysql.com_tech-resources_articles_mysql_55_partitioning.html]: http://dev.mysql.com/tech-resources/articles/mysql_55_partitioning.html