数据库设计及日常使用规范-数据库设计

以下内容来源于公司DBA的分享，做个记录，便于日后自己翻阅，也分享出来共同学习，如果有不合理的或不完善，也请指出。

表和列命名规则

• 表和列全部使用小写。
• 表名称推荐以业务线开头，后跟子系统，下划线分割。
• 列的命名禁止使用数据库或系统的保留字，比如type、order、number、limit、date、from、where等。
• 敏感字段的命名方式，手机号统一使用mobile,不同业务的手机号加前缀，比如alipay_mobile。身份证使用id_number。登录名使用user_name 。帐号相关使用account,比如alipay_account，pay_account。

建表原则

• 无特殊需求字符集使用utf8。
• 存储引擎使用innodb。
• 表上自增长ID做主键。
• 单表字段数上限控制在20-50之间。
• Text及大字段类型单独存在一张表中，和业务表分离。
• 不同的业务创建不同的表，避免在一个表中存所有的字段。
• 经常使用的字段存在同一张表中，不常用的字段单独存放在另一张表中。
• 使用enum代替varchar，Enum实际存的tinyint。
• 表中字段尽量都是NOT NULL，根据需要设置DEFAULT值。

表维护

• 新增字段时，添加到表的最后，禁业添加到某列之前或之后(after、before)。
• 同一表上增加或修改多个字段时，合并成一条并更语句。

例：
    alter table table_a add a ..;
    alter table table_a add b ..;
合并：
    alter table table_a 
    add a ..
    add b .. ;

用好数值类型

• tinyint(占用1字节)、smallint(占用2字节)、mediumint(占用3字节)、int(占用4字节)、bigint(占用8字节)。
• 根据最大值范围选择合理的类型。如果不需要存负数，就用无符号unsigned。
• 存储精确浮点数必须使用 DECIMAL 替代 FLOAT 和 DOUBLE。例：decimal(10,2)
• 选择性很少的状态status、type等字段使用tinyint。
• 不同的数值类型占用字节数及取值范围。

用好日期类型

• 常用的三种类型Datetime(占用5字节)、date(占用3字节)、timestamp(占用4字节)
• 建议使用timestamp,如不需要时间信息，使用date。

下图为日期类型所占用字节数

尽可能的使用数值代替字符串

• 使用数值：占用空间小、查询更快、高效。

例如：IP字段转换成数值存储 int unsigned
            使用函数 inet_aton () 和 inet_ntoa()

目前表中有text字段

如果没有特殊需求，禁止使用select * 。需要那些信息就select 那些字段，否则，会造成大量的资源浪费，造成网络间传输大量信息。

创建索引

• 索引命名ind_表名_列名组合
• 唯一性索引uni_表名_列名组合
• 重复值较多的列，不适合建索引。
• 索引创建过多，影响insert 和 update 速度，控制好索引数量。
• 创建索引时咨询DBA。

程序端

• 程序端禁止使用set语句，包括set names、set sql_mode等
• 禁业连接主库进行大的统计和查询，如有统计需求，联系DBA，商量解决。

外键

• 好处：保证数据的一致性。
• 缺点：需要额外的锁，高并发环境下，性能会下降。会对父记录加一个共享读锁

数据库日常操作

• 添加数据库和帐户统一由DBA来做。
• 创建表和添加字段统一由DBA来做。
• 如有大批量的插入或更新数据，提前邮件告知，和DBA商量解决办法，以免造成主库的阻塞和从库的大量延迟。禁止在没有提前通知的情况下，大批量的插入或更新数据。
• 有推广大促等导致数据库流量增加的活动,请提前通知DBA。
• 主库上禁业跑大统计及查询，如有特殊需求，请提前通知DBA。