常用数据库设计规范总结

Bertha 。 2022-12-21 06:29 148阅读 0赞

**数据库设计规范：**

**一 命名规范：**  
1、所有数据库对象名称必须使用小写字母并用下划线分割  
2、所有数据库对象名称禁止使用MySQL保留关键字（如果表名中包含关键字查询时，需要将其用单引号括起来）

3、数据库对象的命名要做到见名识意，并且最好不超过32个字符  
4、临时表必须以tmp\_为前缀并以日期为后缀，备份表必须以bak\_为前缀并以日期（时间戳）为后缀  
5、所有存储相同数据的列名和列类型必须一致

**二：数据库基本设计规范**  
1、所有表必须使用Innodb存储引擎（MySQL5.5之前默认使用MyIsam，5.6以后默认的为Innodb）  
2、数据库和表的字符集统一使用UTF8(如果数据库中有存储 emoji 表情的需要，字符集需要采用 utf8mb4 字符集。)  
3、所有表和字段都需要添加注释（使用 comment 从句添加表和列的备注）  
4、尽量控制单表的数据量大小，建议控制在500万以为。  
5、谨慎使用MySQL分区表  
6、尽量做到冷热数据分离，减小表的宽度  
MySQL 限制每个表最多存储 4096 列，并且每一行数据的大小不能超过 65535 字节。  
7、禁止在表中建立预留字段  
8、禁止在数据库中存储图片，文件等大的二进制数据  
9、禁止在线上环境做压力测试  
10、禁止从开发环境，测试环境直接连接生成环境数据库

**三：数据库字段设计规范**

**1、优先选择符合存储需要的最小的数据类型**

原因： 列的字段越大，建立索引时所需要的空间也就越大，这样一页中所能存储的索引节点的数量也就越少，遍历时所需要的IO次数也就越多。

**2、避免使用TEXT,BLOB数据类型，最常见的TEXT类型可以存储64K的数据**  
a. 建议把 BLOB 或是 TEXT 列分离到单独的扩展表中  
b.TEXT 或 BLOB 类型只能使用前缀索引\*\*

**3、避免使用ENUM类型**  
ENUM 类型的 ORDER BY 操作效率低

**4、尽可能把所有列定义为NOT NULL**

原因：  
索引 NULL 列需要额外的空间来保存，所以要占用更多的空间  
进行比较和计算时要对 NULL 值做特别的处理

**5. 使用 TIMESTAMP(4 个字节) 或 DATETIME 类型 (8 个字节) 存储时间**

**经常会有人用字符串存储日期型的数据（不正确的做法）**

•缺点 1：无法用日期函数进行计算和比较 缺点 2：用字符串存储日期要占用更多的空间

**6. 同财务相关的金额类数据必须使用 decimal 类型**

•非精准浮点：float,double•精准浮点：decimal  
Decimal 类型为精准浮点数，在计算时不会丢失精度  
占用空间由定义的宽度决定，每 4 个字节可以存储 9 位数字，并且小数点要占用一个字节

**四：索引设计规范**  
**1、每个 Innodb 表必须有个主键**  
Innodb 是一种索引组织表：数据的存储的逻辑顺序和索引的顺序是相同的。每个表都可以有多个索引，但是表的存储顺序只能有一种。

Innodb 是按照主键索引的顺序来组织表的

•不要使用更新频繁的列作为主键，不适用多列主键（相当于联合索引）  
•不要使用 UUID,MD5,HASH,字符串列作为主键（无法保证数据的顺序增长）  
•主键建议使用自增 ID 值

**2、限制每张表上的索引数量,建议单张表索引不超过 5 个**

索引可以增加查询效率，但同样也会降低插入和更新的效率，甚至有些情况下会降低查询效率。  
因为 MySQL 优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，就会增加 MySQL 优化器生成执行计划的时间，同样会降低查询性能

**3、禁止给表中的每一列都建立单独的索引**

**4、常见的索引列建议**：

出现在SELECT 、UPDATE 、DELETE语句中的WHERE从句中的列  
包含在ORDER BY 、GROUP BY 、DISTINCT中的字段  
多表join 的关联列

**5.如何选择索引列的顺序**

建立索引的目的是：希望通过索引进行数据查找，减少随机 IO，增加查询性能 ，索引能过滤出越少的数据，则从磁盘中读入的数据也就越少。

•区分度最高的放在联合索引的最左侧（区分度=列中不同值的数量/列的总行数）  
•尽量把字段长度小的列放在联合索引的最左侧（因为字段长度越小，一页能存储的数据量越大，IO 性能也就越好）  
•使用最频繁的列放到联合索引的左侧（这样可以比较少的建立一些索引）

**6. 避免建立冗余索引和重复索引（增加了查询优化器生成执行计划的时间）**

•重复索引示例：primary key(id)、index(id)、unique index(id)  
•冗余索引示例：index(a,b,c)、index(a,b)、index(a)

**7. 对于频繁的查询优先考虑使用覆盖索引**

覆盖索引：就是包含了所有查询字段 (where,select,ordery by,group by 包含的字段) 的索引

**覆盖索引的好处：**  
避免Innodb 表进行索引的二次查询：Innodb是以聚集索引的顺序来存储的，对于Innodb来说，  
二级索引在叶子结点中所保存的是行的主键信息，如果使用二级索引查询数据的话，在查找到相应的键值后，还要通过主键进行二次查询才能获取我们真实所需要的数据。而在覆盖索引中，二级索引的键值中可以获取所有的数据，避免了对主键的二次查询，减少了IO 操作，提升了查询效率

可以把随机IO 变成顺序IO加快查询效率：由于覆盖索引是按照 键值的顺序存储的，对于IO密集型的范围查找来说，对比随机从磁盘读取每一行的数据IO要少的多，因此利用覆盖索引在访问时，可以把磁盘随机读取的IO转变成索引查找的顺序IO

**8、索引SET 规范**

尽量避免使用外键约束  
不建议使用外键约束，但一定要在表与表之间的关联键上建立索引  
外键可用于保证数据的参照完整性，但建议在业务端实现•  
外键会影响父表和子表的写操作从而降低性能

**五：数据库操作行为规范**

**1. 超 100 万行的批量写 (UPDATE,DELETE,INSERT) 操作,要分批多次进行操作**

**大批量操作可能会造成严重的主从延迟**  
主从环境中,大批量操作可能会造成严重的主从延迟，大批量的写操作一般都需要执行一定长的时间， 而只有当主库上执行完成后，才会在其他从库上执行，所以会造成主库与从库长时间的延迟情况  
**binlog 日志为 row 格式时会产生大量的日志**  
大批量写操作会产生大量日志，特别是对于 row 格式二进制数据而言，由于在 row 格式中会记录每一行数据的修改，我们一次修改的数据越多，产生的日志量也就会越多，日志的传输和恢复所需要的时间也就越长，这也是造成主从延迟的一个原因  
**避免产生大事务操作**  
大批量修改数据，一定是在一个事务中进行的，这就会造成表中大批量数据进行锁定，从而导致大量的阻塞，阻塞会对 MySQL 的性能产生非常大的影响。  
特别是长时间的阻塞会占满所有数据库的可用连接，这会使生产环境中的其他应用无法连接到数据库，因此一定要注意大批量写操作要进行分批

**2. 对于大表使用 pt-online-schema-change 修改表结构**

•避免大表修改产生的主从延迟•避免在对表字段进行修改时进行锁表

对大表数据结构的修改一定要谨慎，会造成严重的锁表操作，尤其是生产环境，是不能容忍的。

pt-online-schema-change 它会首先建立一个与原表结构相同的新表，并且在新表上进行表结构的修改，然后再把原表中的数据复制到新表中，并在原表中增加一些触发器。把原表中新增的数据也复制到新表中，在行所有数据复制完成之后，把新表命名成原表，并把原来的表删除掉。把原来一个 DDL 操作，分解成多个小的批次进行

**3. 禁止为程序使用的账号赋予 super 权限**

•当达到最大连接数限制时，还运行 1 个有 super 权限的用户连接•super 权限只能留给 DBA 处理问题的账号使用

**4. 对于程序连接数据库账号,遵循权限最小原则**

•程序使用数据库账号只能在一个 DB 下使用，不准跨库•程序使用的账号原则上不准有 drop 权限

**6. 禁止使用不含字段列表的 INSERT 语句**

如：

insert into values ('a','b','c');

应使用：

insert into t(c1,c2,c3) values ('a','b','c');

**六：SQL优化**

**1、避免数据类型的隐式转换**  
隐式转换会导致索引失效如:

select name,phone from customer where id = '111';

**2、充分利用表上已经存在的索引**

**3、禁止使用 SELECT \* 必须使用 SELECT <字段列表> 查询**

**4、 避免使用子查询，可以把子查询优化为 join 操作**  
通常子查询在 in 子句中，且子查询中为简单 SQL(不包含 union、group by、order by、limit 从句) 时,才可以把子查询转化为关联查询进行优化。  
**子查询性能差的原因：**  
子查询的结果集无法使用索引，通常子查询的结果集会被存储到临时表中，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响。特别是对于返回结果集比较大的子查询，其对查询性能的影响也就越大。  
由于子查询会产生大量的临时表也没有索引，所以会消耗过多的 CPU 和 IO 资源，产生大量的慢查询。

**5、避免使用 JOIN 关联太多的表**

**6、减少同数据库的交互次数**

**7、对应同一列进行 or 判断时，使用 in 代替 or**

in 的值不要超过 500 个，in 操作可以更有效的利用索引，or 大多数情况下很少能利用到索引

**8、在明显不会有重复值时使用 UNION ALL 而不是 UNION**

•UNION 会把两个结果集的所有数据放到临时表中后再进行去重操作•UNION ALL 不会再对结果集进行去重操作

**9、拆分复杂的大 SQL 为多个小 SQL**

•大 SQL 逻辑上比较复杂，需要占用大量 CPU 进行计算的 SQL•MySQL 中，一个 SQL 只能使用一个 CPU 进行计算•SQL 拆分后可以通过并行执行来提高处理效率

**10.优化groupBy 尽量在此之前把无用数据过滤掉，having效率较低**