当前位置:   article > 正文

不会设计数据库?一篇文章搞懂_程序员开发数据库不会设计

程序员开发数据库不会设计
  • 逻辑设计

    • 1、具体内容:设计数据库的一个逻辑结构,与具体的DBMS无关,主要反映业务逻辑
    • 2、设计步骤:用关系模型
    • 3、使用工具来模型化:E-R图
    • 4、实体关系模型
      • 通过表格实现:字段名,类型,长度,约束
      • 实体的实例化和泛化
    • 5、要满足3NF
  • 物理设计

    • 对具体数据库进行选型:oracle,mysql
    • 表的字段及存储结构

    实际工作中逻辑设计和物理设计是并行的。

1 数据库命名规范

  • 所有数据库对象名称:小写加下划线分割
    • MySQL对象名称在默认情况下是大小写敏感
    • MySQL的对象其实都是一个文件,而Linux文件名是大小写敏感
  • 所有MySQL数据库对象名称禁止使用MySQL保留关键字
  • 所有的数据库对象名称:见名知义,但最长不要超过32个字符(不要中英文混合)
  • 所有临时表命名:tmp_tablename_20191215
  • 所有备份表:bak_tablename_20191215
  • 索引:idx_pk_
  • 所有存储类型相同的列名以及长度必须保持一致

2 数据库设计规范

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

  • 字符集统一UTF-8 (varchar(255) UTF-8 255*3=765字节)

  • 一定 要给列加注释

  • 控制一个单表的数量大小:行

    • 对于日志数据,进行归档
    • 对于业务数据进行分库分表
  • 控制表宽度

    • 虽然表没有限制,但列最多4096
    • 如果列多了,占用内存和I/O会非常大
  • 禁止在表中建立预留字段:ext_float_1,ext_char_2

  • 禁止在数据库中存放图片,文件,二进制流

    • 不得不存:将内容数据和文件流程数据分开(外键表)需要使用时再关联
    • select* 就会将大量字段带出,浪费内存,I/O
  • 禁止对线上数据进行压测

    • 会产生大量的垃圾数据和日志文件
  • 禁止从开发环境连接测试或生产数据库

3 数据库索引设计规范

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

4 数据字段设计规范

  • 优先选择符合存储需要的最小数据类型
    • INT来存放时间戳
  • 避免使用TEXT,BLOB数据类型
    • 如果非要使用可以单独拿出来做关联表
    • 这两个类型没有默认值
  • 避免使用ENUM数据类型
    • 修改则需要使用ALTER语句
  • 尽可能把所有列定义为NOT NULL
    • 如果为NULL,索引需要额外的空间来保存
  • 日期格式尽量不要用字符串保存
    • 不能使用日期函数进行计算和比较
    • 用字符串占用的空间更多
  • 财务相关的数据用Decimal类型来进行计算

5 数据库SQL开发规范

  • 在程序中使用PrepareStatement,#{}

    • 降低词法和语法分析器的重复执行
    • 防止SQL注入
  • 合理和充分的利用表上的索引

    • 避免前后%
    • 使用left join或not exists来优化not in(not in无法使用索引)
  • 程序连接不同数据库使用不同的账号,禁止跨库操作

  • 禁止使用select *

  • 禁止使用不含列名的insert into tableName values(‘’,“,”);

  • 避免使用子查询,可以把子查询优化为join操作

    • 子查询的结果集无法使用索引
    • 子查询会产生临时表操作,如果查询量大则会严重影响效率
  • 避免使用jion关联太多表

    • 大查询拆小查询,由我们的程序来去做关联和合并
    • 进行表数据冗余
    • 有一定的转换
  • 减少同数据库的交换次数

  • 使用in代替or,in能使用索引,or用不上

  • 禁止用order by rand()

  • where中不要对列进行函数计数:列无法使用索引了

  • UNION ALL和UNION

    • 如果我们的数据明显不重复,就施一公UNION ALL

6 数据库操作行为规范

  • 大批量的数据操作会严重造成数据延迟(分批进行)
  • 对大表结构的操作会导致锁表
    • 对于大表的操作:pt-online-schema-change(PERCONA)
    • 原理
      • 创建新表结构
      • 复制旧表数据到新表
      • 在原表上加入触发器确保数据同步
      • 所有操作进行完毕后对原表进入一个很短的时间锁
      • 把原表表名进行修改,再改新表名
      • 删除原表名
  • 禁止对普通用户授予super权限
    • grant all privileges
    • 授权的时候遵循最小原则
    • 当数据库连接慢,MySQL会给super留一个保留连接
本文内容由网友自发贡献,转载请注明出处:【wpsshop博客】
推荐阅读
相关标签
  

闽ICP备14008679号