MySQL从入门到精通（四）

数据库的规范设计

1. 数据库架构设计的步骤

• 逻辑设计

  上来就 create table

  先做E-R图

  设计的时候要满足第三范式

  • 1NF:字段不可再分，一个实体只做一件事
  • 2NF:有主键，非主键字段依赖主键
  • 3NF:非主键字段不能互相依赖，取消掉传递依赖

  实际应用中，我们会主动打破第三范式，提升查询效率

• 物理设计

  设计数据库的物理结构，根据数据库的逻辑结构来选定RDBMS（比如：Oracle，MySQL等）并设计和试试数据库的存储结果，存取方式
  将数据库结构和数据落库

2. 数据库命名

• 数据库名称：小写加下划线
• 数据库名称禁止使用保留字
• 见名知意
• 临时表：tmp_tablename_20200620
• 备份表：bak_tablename_20200620
• 所有存储相同的列名和类型长度必须一致

3. 数据库设计规范

• 尽量使用innoDB，v5.6以后innoDB已经是默认引擎
• 字符集统一UTF-8（varchar(255) UTF8 255*3=765个字节）
• 一定要给列加注释
• 控制一个单表的数据量大小（单表数据超过5kw时久需要开始考虑分库分表）
• 控制表的宽度，列限制为4096
• 禁止在表中建立预留字段：ext_float_1,ext_char_2（表已经有数据了，新加字段设置为可以为空并没有默认值）
• 禁止在数据库中存放图片，文件，二进制流
  • 不得不存：将内容数据和文件流程数据分开（外键表）需要使用时再关联
  • select * 就会将大字段带出，浪费内存、I/O
• 禁止对线上数据库进行压测
  • 会产生大量的垃圾数据和日志文件
• 禁止从开发环境连接测试或生产数据库

4. 数据库索引设计规范

• 单张表的索引数量建议不超过5个，如果列比较多可以酌情增加

• 每个innodb表都应该有个主键，innodb是一个索引组织表

  • 表数据的存放都是按照主键顺序来的
  • 如果没有主键，mysql会优先选择一个非空唯一索引来做排序
  • 如果非空唯一索引都没有，mysql会自己生成一个36字节的主键，但性能不好
  • 不要使用UUID，MD5，HASH等字符串做主键，建议使用增长序列来做主键

• 组合索引的字段匹配是自左向右

  • 一般将区分度最高的列放在组合索引最左侧
  • 将字段长度小的放最左侧
  • 最频繁的放最左侧

• 避免建立冗余和重复索引（index(a,b,c) index(b,c) index(a)）

• 尽量避免使用外键约束

5. 数据库字段设计规范

• 优先选择符合存储的最小数据类型
• 避免使用TEXT、BLOB类型
• 避免ENUM类型：修改枚举类型值需要alter语句
• 尽量将列定义为NOT NULL
• 日期格式建议使用timestamp或int来保存

6. 数据库的开发规范

• 程序连接数据库的SQL一定使用Preparement

  • 降低词法和语法分析的重复执行
  • 防止SQL注入

• 索引使用尽量避免前后%

• 使用join或exists来优化in操作

• 不同的应用访问数据库用不同的账号

• 禁止使用不含列名的insert

• 避免子查询（子查询结果集无法使用索引）

• 避免使用JOIN连接过多的表，阿里手册建议不要超3张表

• 减少数据库的交互次数

7. 数据库操作行为规范

• 超100w行的批量写操作，分批进行
• 禁止为程序用户授予super权限
  • grant all privileges
  • 授权的时候遵循权限最小原则
  • 当数据库连接慢，MySQL会给super留一个保留连接