关于mysql数据库的规范与表的设计

关于mysql数据库的规范

1. 建表约束

表达是与否概念的字段，必须使用 is_xxx 的方式命名，数据类型是 unsigned tinyint （1 表示是，0 表示否）。

举例:表达逻辑删除的字段名 is_deleted，1 表示删除，0 表示未删除。

表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字。数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑。

举例：aliyun_admin，rdc_config，level3_name

表名不使用复数名词。

禁用保留字，如 desc、range、match、delayed 等

主键索引名为 pk_字段名；唯一索引名为 uk_字段名；普通索引名则为 idx_字段名。

pk_ 即 primary key；uk_ 即 unique key；idx_ 即 index 的简称。

小数类型为 decimal，禁止使用 float 和 double。

如果存储的字符串长度几乎相等，使用 char 定长字符串类型。

varchar 是可变长字符串，不预先分配存储空间，长度不要超过 5000，如果存储长度大于此值，定义字段类型为 text，独立出来一张表，用主键来对应，避免影响其它字段索引效率。

表必备三字段：id, create_time, update_time。

其中 id 必为主键，类型为 bigint unsigned、单表时自增、步长为 1。create_time, update_time
的类型均为 datetime 类型，前者现在时表示主动式创建，后者过去分词表示被动式更新。

表的命名最好是遵循“业务名称_表的作用”。

单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表。

合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检索速度。

2. 索引规约

业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。

不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的；另外，
即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。

超过三个表禁止 join。需要 join 的字段，数据类型保持绝对一致；多表关联查询时， 保证被关联的字段需要有索引。

页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。

索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。

3. SQL 语句

不要使用 count(列名)或 count(常量)来替代 count(*)，count(*)是 SQL92 定义的标 准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。

count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。

当某一列的值全是 NULL 时，count(col)的返回结果为 0，但 sum(col)的返回结果为 NULL，因此使用 sum()时需注意 NPE 问题。

可以使用如下方式来避免 sum 的 NPE 问题：SELECT IFNULL(SUM(column), 0) FROM table;

使用 ISNULL()来判断是否为 NULL 值。

不得使用外键与级联，一切外键概念必须在应用层解决。

（概念解释）学生表中的 student_id 是主键，那么成绩表中的 student_id 则为外键。
如果更新学生表中的 student_id，同时触发成绩表中的 student_id 更新，即为级联更新。
外键与级联更新适用于单机低并发，不适合分布式、高并发集群；
级联更新是强阻塞，存在数据库更新风暴的风险；外键影响数据库的插入速度。

禁止使用存储过程，存储过程难以调试和扩展，更没有移植性。

SQL 语句中表的别名前加 as，并且以 t1、t2、t3、…的顺序依次命名。

1）别名可以是表的简称，或者是依照表在 SQL 语句中出现的顺序，以 t1、t2、t3 的方式命名。
2）别名前加 as 使别名更容易识别。
select t1.name from table_first as t1, table_second as t2 where t1.id=t2.id;

in 操作能避免则避免，若实在避免不了，需要仔细评估 in 后边的集合元素数量，控 制在 1000 个之内。

TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少，但 TRUNCATE 无事务且不触发 trigger，有可能造成事故，故不建议在开发代码中使用此语句。

4. ORM映射

在表查询中，一律不要使用 * 作为查询的字段列表，需要哪些字段必须明确写明。

1）增加查询分析器解析成本。
2）增减字段容易与 resultMap 配置不一致。
3）无用字段增加网络消耗，尤其是 text 类型的字段。

更新数据表记录时，必须同时更新记录对应的 update_time 字段值为当前时间。

关于数据库表设计

什么是数据库范式?

设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。
规范化目的是使结构更合理，消除存储异常，使数据冗余尽量小。便于插入、删除和更新。

是不是只要把所有的表都遵循第三范式，数据库的设计就是最优的呢?

这可不一定。范式越高意味着表的划分更细，一个数据库中需要的表也就越多，用户不得不将原本相关联的数据分摊到多个表中。当用户同时需要这些数据时只能采用连接表的形式将数据重新合并在一起。同时把多个表联接在一起的花费是巨大的，尤其是当需要连接的两张或者多张表数据非常庞大的时候，表连接操作几乎是一个噩梦，这严重地降低了系统运行性能。

在实际开发中应该注意什么

数据库设计三范式是理论上的，实践和理论有的时候有偏差。最终的目的都是为了满足客户的需求，有的时候会拿冗余换执行速度。因为在sql当中，表和表之间连接次数越多，效率越低。（笛卡尔积）有的时候可能会存在冗余，但是为了减少表的连接次数，这样做也是合理的，并且对于开发人员来说，sql语句的编写难度也会降低。

数据库的三大范式

第一范式：要求任何一张表必须有主键，每一个字段原子性不可再分。
第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库满足了第一范式。

第二范式：建立在第一范式的基础之上，要求所有非主键字段完全依赖主键，不要产生部分依赖。
说的是在满足第一范式的基础上，数据库表中不存在非关键字段对任一候选关键字段的部分函数依赖，也即所有非关键字段都完全依赖于任一组候选关键字。

第三范式：建立在第二范式的基础之上，要求所有非主键字段直接依赖主键，不要产生传递依赖。
数据库表中如果不存在非关键字段任一候选关键字段的传递函数依赖则符合第三范式，所谓传递函数依赖指的是如果存在"A–>B–>C"的决定关系，
则C传递函数依赖于A。也就是说表中的字段和主键直接对应不依靠其他中间字段，说白了就是，决定某字段值的必须是主键。

多表设计

一对一

• 分析
  • 人和身份证。一个人只有一个身份证，一个身份证只能对应一个人！
• 实现原则
  • 在任意一个表建立外键，去关联另外一个表的主键
• 适应场景
  • 当我们的表字段过多,不方便操作时,可以考虑把一个纵向表拆成两个表时使用

一对多

• 分析
  • 用户和订单。一个用户可以有多个订单！
  • 商品分类和商品。一个分类下可以有多个商品！
• 实现原则
  • 在多的一方，建立外键约束，来关联一的一方主键

多对多

• 分析
  • 学生和课程。一个学生可以选择多个课程，一个课程也可以被多个学生选择！
• 实现原则
  • 需要借助第三张表中间表，中间表至少包含两个列，这两个列作为中间表的外键，分别关联两张表的主键