数据库开发规范

数据库开发规范

一、 表设计

二、 索引

三、 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）

5. 依据业务需求可以对表进行水平切分或者垂直切分
   五、 线上数据库维护操作

6. 如果线上后台进行批量导入、导出数据需要通知DBA进行审查，并在执行过程中观察服务。

7. 批量更新数据，如update,delete 操作，需要DBA进行审查，并在执行过程中观察服务。

8. 产品出现非数据库平台运维导致的问题和故障时，请及时通知DBA，便于维护服务稳定。

9. 业务部门程序出现bug等影响数据库服务的问题,请及时通知DBA，便于维护服务稳定。

10. 业务部门推广活动，请提前通知DBA进行数据库服务评估及监控。

11. 如果出现业务部门人为误操作导致数据丢失，需要恢复数据，请在第一时间通知DBA，并提供准确时间，误操作语句等重要线索。

12. 原则上业务因更新时，不许对数据库进行DROP(表，列)及RENAME(表，列)操作但可以ADD。

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