Day3 MySQL

摘要

数据库设计同样是项目进行过程中的一个重要的部分。本文主要研究数据库设计的规约和方法。

背景

了解数据库设计规约的目的：主要为了方便SDE之间的沟通交流，并且为了防止踩了某些坑。

设计规约与踩坑

规约

表、字段的命名规则

1. 小写字母，数字，下划线

2. 禁止使用数字开头

3. 不使用复数名词

4. 禁用保留字

5. 是与否概念的字段，必须使用 is_XXX 的方式命名

表中必备三个字段

id

create_time

update_time

表名称规约

1. 表的命名最好是遵循“业务名称_表”

2. 库名与应用名称尽量一致

3. 需及时更新自动注释

索引命名规约

主键使用 pk_字段名；

唯一索引使用 uk_字段名；

非唯一索引名为 idx_字段名；

踩坑

数据类型

1. 小数类型为 decimal

2. 货币数据使用最小货币单位，数据类型为 bigint

3. 字符串长度几乎相等时，使用 char。char 会自动补空格。

4. 当字符串长度不相等时，使用 varchar。varchar 长度不要超过 5000。超过 5000 的选择 text

表名称规约

1. 字段允许适当冗余，但必须考虑数据一致。（为了提高查询效率，不用关联别的表来查询名称）

2. 单表行数超过500W活单表超过2GB，推荐分库分表。（分库分表的时候，会增加系统的复杂度）

索引规约

索引的出现是为了提高查询效率。其实质是一种数据结构。按照这种数据结构，能够使得查询过程有一定的方向性。

索引的分类

存储形式分类：

1. 聚簇索引。

2. 非聚簇索引。

innoDB 中，除了主键是聚簇索引，其它都是费聚簇索引

数据约束：

1. 主键索引

2. 唯一索引

3. 非唯一索引

索引数量：

1. 单列索引

2. 组合索引

innoDB 可创建的索引：

1. 主键索引

2. 唯一索引（具有唯一性的字段都应该建立唯一索引。如果字段的组合具有唯一性，也应该对组合创建唯一索引）

3. 普通索引 （非唯一索引）

索引的数据结构

现在常用的索引数据结构为 b+tree

各种索引数据结构的比较如下：

哈希索引：无法进行范围索引。只能进行范围查询。

二叉查找树具有极端情况，所以采用 平衡二叉查找树。

平衡二叉查找树能够保证左右两个子树层级最多相差1.

平衡二叉查找树需要的 IO 次数多，因此采用 btree。

在叶子节点中怎么查询？节点读取到内存中之后，直接遍历即可。

btree 对范围查询支持不好。采用 b+ tree。

b+ tree ：中间节点不存数据，所有数据都存在叶子节点中。

优点：树的高度降低；叶子节点中构成双向链表。

索引创建规约

必有：主键索引；唯一索引。（不论是否会影响查询效率）来保证唯一性。

对table 进行增删改时，同样需要维护索引。

SQL 规约

SQL 语句要能用到索引。

1. 注意字段类型。防止隐式数据类型转换导致索引失效。当 varchar 与数值对比时，会导致索引效果失效。

2. 利用覆盖索引避免回表。回表：通过辅助索引找到主键。根据主键找到目标值。覆盖索引：索引的字段正好是你想查询的字段。

3. 利用有序性。如果有排序或者 order by 时，要注意使用索引的有序性。

4. 禁止模糊查询。

5. 禁止三个表做 join

6. 不要用 count(列名) 或 count(常量) 代替 count(*)

7. count(distinct col) 计算该列除 NULL 之外的不重复行数。

8. 当某一列的值全为 NULL 时，count(col) 返回结果为 0，但 sum(col) 的返回结果为 Null

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

10. 进制使用存储过程，存储过程难以调试和扩展，更没有移植性。

11. 数据订正时，要先 select，避免出现误删数据，确认无误才能更新语句。

12. 只要涉及多个表，都需要再列名钱加表的别名（或表名）进行限定

13. SQL语句中表的别名前加 as，并且以 t1,t2,t3 的顺序依次命名

14. in 后边的集合元素数量控制在 1000 个之内

ORM 映射规约

1. 在表查询中，一律不要用 * 作为查询字段的列表

2. POJO 类的布尔属性不能加 is，而数据库字段必须加 “is_"

3. 查询返回结果都需要使用 ResultMap 映射

4. 不要使用 ${} 以免出现 SQL 注入

5. 不要使用 MyBatis 自带的 queryForList 方法

6. 不允许直接使用 HashMap 与 HashTable 接收结果集

7. 更新数据表记录时，必须同时更新 update_time

8. 不要写一个大而全的数据更新接口

数据库设计大三泛式

第一范式

每列属性不可拆分。

目的：为了能够增加查询的维度。将属性拆成细小的单元。

第二范式

表中的每列都和主键相关。

目的：为了去除冗余信息，让每个磁盘块能尽量多地存储数据。

第三范式

每列都和主键列直接相关，而不是间接相关。

目的：为了去除冗余信息，让每个磁盘块能尽量多地存储数据。

结论

数据库设计前提还是要多参考业务逻辑。每个泛式的理解，需要根据大量的例子来喂出来。

需要解决的问题：

1. MySQL 调优

2. 泛式理解