数据库基本要求

数据库建库：

• 基本命名原则

  • 使用有意义的英文词汇，词汇中间以下划线分隔。（不要用拼音）

  • 只能使用英文字母，数字，下划线，并以英文字母开头。

  • 库、表、字段全部采用小写，不要使用驼峰式命名。

  • 避免用ORACLE、MySQL的保留字，如desc，关键字如index。

  • 命名禁止超过32个字符，须见名之意，建议使用名词不是动词

  • 数据库，数据表一律使用前缀

  • 临时库、表名必须以tmp为前缀，并以日期为后缀

• • 备份库、表必须以bak为前缀，并以日期为后缀

• 表命名

  • 同一个模块的表尽可能使用相同的前缀，表名称尽可能表达含义。所有日志表均以 log_ 开头

• 字段命名

  • 表达其实际含义的英文单词或简写。布尔意义的字段以“is_”作为前缀，后接动词过去分词。

  • 各表之间相同意义的字段应同名。各表之间相同意义的字段，以去掉模块前缀的表名_字段名命名。

  • 外键字段用表名_字段名表示其关联关系。

  • 表的主键一般都约定成为id，自增类型，是别的表的外键均使用xxx_id的方式来表明

• 索引命名

  • 非唯一索引必须按照“idx_字段名称_字段名称[_字段名]”进行命名

  • 唯一索引必须按照“uniq_字段名称_字段名称[_字段名]”进行命名

• 约束命名

  • 主键约束：pk_表名称。

  • 唯一约束：uk_表名称_字段名。（应用中需要同时有唯一性检查逻辑。）

• 触发器命名

  • trg_表名_操作。

• 函数过程命名

  • 采用动词+名词的形式表达其含义。

• 序列命名

  • seq_表名

• 表设计规范

  • ID为主键&索引&自增

  • 索引数量控制，单张表中索引数量不超过5个，单个索引中的字段数不超过5个

  • 主键准则

    • 表必须有主键

    • 不使用更新频繁的列

    • 尽量不选择字符串列

    • 默认使用非空的唯一键

    • 建议选择自增或发号器

  • 禁止使用varchar类型作为主键语句设计

  • 表必须包含create_time和modify_time字段，即表必须包含记录创建时间和修改时间的字段

  • 表必须包含is_del，用来标示数据是否被删除，原则上数据库数据不允许物理删除

  • 用尽量少的存储空间来存数一个字段的数据

    • 能用int的就不用char或者varchar

    • 存储精确浮点数必须使用DECIMAL替代FLOAT和DOUBLE

    • 时间字段

      • 存储年使用YEAR类型

      • 存储日期使用DATE类型

      • 存储时间（精确到秒）建议使用TIMESTAMP类型，因为TIMESTAMP使用4字节，DATETIME使用8个字节

    • 尽可能不使用TEXT、BLOB类型

  • 有必要的字段都设置NOT NULL，并设置默认值

  • 禁止在数据库中存储明文密码

  • 所有的布尔值字段，如is_hot、is_deleted，都必须设置一个默认值，并设为0

  • 排序字段order_id在程序中默认使用降序排列

  • 整形定义中不添加长度，比如使用INT，而不是INT[4]

  • 索引数量控制，单张表中索引数量不超过5个，单个索引中的字段数不超过5个

如何使用TIMESTAMP的自动赋值属性？

• 将当前时间作为ts的默认值：ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP。

• 当行更新时，更新ts的值：ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP。

• 可以将1和2结合起来：ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP。

数据库建库

• 数据库权限

  • 正式环境账号严格控制

  • 开发只有正式环境的只读账号

• 数据库建库

  • tempdb不要在C盘

  • 数据文件&日志文件不放在C盘；且放在不同的盘

• 数据库备份

• 数据库性能优化

• 默认端口不要设置1433

• 一个项目一个数据库一个账号

行为规范

• 批量导入、导出数据必须提前通知DBA协助观察

• 禁止在线上从库执行后台管理和统计类查询

• 禁止有super权限的应用程序账号存在

• 产品出现非数据库导致的故障时及时通知DBA协助排查

• 推广活动或上线新功能必须提前通知DBA进行流量评估

• 数据库数据丢失，及时联系DBA进行恢复

• 对单表的多次alter操作必须合并为一次操作

• 不在MySQL数据库中存放业务逻辑

• 重大项目的数据库方案选型和设计必须提前通知DBA参与

• 对特别重要的库表，提前与DBA沟通确定维护和备份优先级

• 不在业务高峰期批量更新、查询数据库其他规范

• 提交线上建表改表需求，必须详细注明所有相关SQL语句

语句设计规范

1、使用预编译语句

• 只传参数，比传递SQL语句更高效

• 一次解析，多次使用

• 降低SQL注入概率

2、避免隐式转换

• 会导致索引失效

3、充分利用前缀索引

• 必须是最左前缀

• 不可能同时用到两个范围条件

• 尽量不使用%前导的查询，如like “%ab”

4、不使用负向查询，如not in/like

• 无法使用索引，导致全表扫描

• 全表扫描导致buffer pool利用率降低

5、避免使用存储过程、触发器、UDF、events等

• 让数据库做最擅长的事

• 降低业务耦合度，为sacle out、sharding留有余地

• 避开BUG

6、避免使用大表的JOIN

• MySQL最擅长的是单表的主键/二级索引查询

• JOIN消耗较多内存，产生临时表

7、避免在数据库中进行数学运算

7、减少与数据库的交互次数

8、合理的使用分页

• 限制分页展示的页数

• 只能点击上一页、下一页

• 采用延迟关联