mysql MySQL数据架构设计

起因：在工作中常常要用到mysql，平常只是对数据库crud，并没有认真的了解过她，sql语句也只是会一些最基本的，和常用的，一些不常用的都要去网上百度，即决定学习一下mysql，来了解她，虽然开发很多都是黑盒，但追本溯源总是我们想要的。

1.数据库架构设计

做架构到底是在做什什么？
抽象能力
抽象-->具象

• 逻辑设计：
  1、具体内容：设计数据库的⼀一个逻辑结构，与具体的DBMS⽆无关，主要反映业务逻辑
  2、设计步骤：⽤用关系模型
  3、使⽤用⼯工具来模型化：E-R图
      矩形：实体对象 1:m，n:m，1:1
      椭圆：属性
      线：关系的连接
      菱形：关系
  实体关系模型
      通过表格实现：字段名，类型，长度，约束
      实体的实例化和泛化
  至少满足3NF
• 物理理设计
  对具体数据库进⾏行行选型：oracle，mysql 表的字段及存储结构
• 实际工作中：都是并行的

1.1.数据库命名规范

• 所有数据库对象名称：小写加下花线分割
  MySQL对象名称在默认情况下是大小写敏感
  MySQL的对像其实都是⼀一个文件，而linux文件名是大小写敏感
  Dbname / dbname，MyTable / mytable
  开发非常麻烦

• 所有MySQL数据库对象名称禁止使用MySQL保留关键字
  一定要提前准备一份对应版本的关键字表
  建表的时候没问题，但SQL查询就挂了了

• 所有的数据库对象名称：见名知义，但最长不要超过32个字符（不要中英文混合） 所有临时表命名：tmp_tablename_20191215

• 所有的备份表：bak_tablename_20191215

• 索引：idx_ pk_

• 所有存储类型相同的列名以及长度必须保持一致
  order：product_title 50
  erp_instock: product_title 50

1.2.数据库设计规范

• 正常情况下建议使⽤用innoDB，v5.6版本后默认都是innoDB

• 字符集
  UTF-8
  统一字符集避免乱码
  UTF-8的字符集是一个汉字3个字节：varchar(255) UTF-8 255*3=765字节

• 加入注释

• 控制单表的数据量大小：行
  对于日志数据，进行归档
  对于业务数据进行分库分表

• 分区表谨慎使用

• 控制表宽度
  虽然表没有⾏行行限制，但列列最多4096
  如果列多了，占用内存和I/O会非常大

• 禁止在表中建立预留字段：varchar_column，order_second_no，remark，memo
  varchar类型
  违背上面的命名规则
  时间久了，不看业务代码，完全是魔鬼字段

• 禁止在数据库里存放图片、文件、二进制文件
  如果要用blob、Text存大文件，select colums....
  如何避免select * ，外键表单独放单文件

• 禁止对线上环境进行压力测试
  会产生大量的垃圾数据和日志文件

• 禁止从开发环境、测试环境连接生产数据库

1.3.数据库索引设计规范

• 单张表索引数量量建议不不超过5个，如果列列多可以适当增加
  索引过多：SQL在进行优化器评估的时候会有更大的开销
  绝对不允许给表的每一列都建立索引
• 每个innodb表都必须有一个主键，innoDB表就是一个索引组织表
  表数据的实际存储顺序只能有一种，innoDB是按照主键进行存放的
  如果没有主键，mysql会优先选择第一个非空唯一索引来做主键
  如果上面这个没有，mysql会自动⽣生成一个36个字节的主键，但性能不好
  不能使用更新频繁的列和联合索引做主键，主键不断变，数据的存放顺序就会不断变化
  不要使用UUID、MD5、HASH等做主键，不能保证这些值是按顺序增长的。如果⽣生成较小的字符串就会导致不断变化数据存储的位置，影响I/O性能
• 要在哪些列上建立索引：没有最好只有最适合
  explain
  where 后
  join的连接lie
  筛选项最大的放在索引做左侧
• 避免建立冗余和重复索引
• 对于频繁查询的数据列列，优先考虑使用覆盖索引
• 尽量避免加入外键约束
  因为外键写入的时候会降低存储效率
  但要给这些关联字段加索引

1..4. 数据库字段设计规范

• 优先选择符合存储需要的最小数据类型
  INT来存放时间戳
  varchar(255) '长袖衬衫'
• 避免使用TEXT，BLOB数据类型
  如果非要使用可以单独拉出来做关联表
  这两个类型上没有默认值
• 避免使用ENUM数据类型
  修改则需要使用ALTER语句
• 尽可能把所有列列定义为NOT NULL
  如果为NULL，索引需要额外的空间来保存
• 日期格式尽量不要用字符串保存
  不能用日期函数进行计算和比较
  用字符串占用的空间更更多
  1970-01-01 00:00:00-2038-01-19 03:14:07
  int来保存：4294967295
  财务相关的数据用Decimal类型来进行计算

1.5. 数据库SQL开发规范

• 在程序中使用PrepareStatement，#{}
  降低词法和语法分析器器的重复执行
  防止SQL注入
• 合理理和充分的利用表上的索引
  避免前后%
  使用left join或not exists来优化not in（not in无法使用索引）
• 程序连接不同数据库使用不同的账号，禁止跨库操作
  应用A---B 应用D---C：跨库访问最好调用业务层
  如果账号被注入，也只注入⼀一个库
• 禁止使用select *（但是依旧这么做）
• 禁止使用不含列列名的insert into tableName values('','','');
• 避免使用子查询，可以把子查询优化为join操作
  子查询的结果集无法使用索引
  子查询会产生临时表操作，如果查询量大则会严重影响效率
• 避免使用join关联太多表
  ⼤大查询拆小查询，由我们的程序来去做关联和合并
  进行表数据冗余
  有一定的转换
• 减少同数据库的交互次数
• 使⽤用in代替or，in能⽤用索引，or用不上
• 禁⽌止用order by rand()
• where中不要对列进行函数计数：列无法使用索引了
• UNION ALL和UNION
  如果我们的数据明显不重复，就使用UNION ALL
  程序=数据结构+算法

1.6. 数据库操作行为规范

• 大批量的数据操作会严重造成数据延迟
  数据分批执行
  v5.7 format_binlog：row
• 对大表结构的操作会导致锁表
  对于大表的操作：pt-online-schema-change （PERCONA）
  原理
  创建新表结构
  复制旧表数据到新表
  在原表上加入触发器确保数据同步
  所有操作进行完毕后对原表进入一个很短的时间锁
  把原表名进行修改，再改新表名
  删除原表名
  原⼦子性操作分割进行了
• 禁止对普通用户授予super权限
• 对于程序遵循最小权限原则

不要以为每天把功能完成了就行了，这种思想是要不得的，互勉~！