数据库架构设计规范

数据库架构设计

做架构到底是在做什么？

抽象能力

抽象->具象

数据库架构设计分为以下三种：

• 逻辑设计： 

       1.具体内容设计数据库的一个逻辑结构。与具体的dbms无关，主要反映业务逻辑

       2.设计步骤:用关系模型

       3.使用工具来模型化： E-R图。   

•              矩形：实体对象,1:m,n:m

•              椭圆：属性

•               线：关系的连接

•               菱形：关系

       4.实体关系模型

•              通过表格实现：字段名，类型，长度，约束

•               实体的实例化和泛化

       5.至少满足3NF

            第一范式（1NF）：要求数据库表的每一列都是不可分割的原子数据项。

          第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）

          第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

 

• 物理设计：

           1. 对具体数据库进行选型：oracle、mysql、db2

           2. 表的字段及存储结构

           

• 实际工作中： 都是并行的，逻辑设计和物理设计不一定完全按照顺序进行，很多时候两者都同步进行的。

         3.1 数据库命名规范

• 所有数据库对象名称：小写/大写 加下划线分割

               mysql对象名称在默认情况下是大小写敏感

               mysql的对象其实都是一个文件，而linux文件名是大小写敏感                  

               不同的写法Dbname/ dbname, MyTable/mytable，会导致数据库表错误

               大小写不同布会导致开发非常麻烦

• 所有mysql数据库对象名称谨慎使用mysql保留关键字

               一定要提前准备一份对应版本的关键字标

               关键可能导致无法建表；或者建表的时候没问题，但sql查询就挂了

• 所有的数据库对象名称：见名知义，但最长不要超过32个字符

• 所有临时表命名：tmp_tablename_20191215

• 所有备份表：bak_tablename_20191215

• 索引命名开头:   idx_  pk_

• 所有存储类型相同的列名以及长度必须保持一致

              order: product_title

              erp_instock: product_title

 

         3.2数据库设计规范

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

• 字符集：

               utf-8格式

               统一字符集避免乱码

               utf-8的字符集是1个汉字占3个字节：varchar(255)  utf-8 255*3=765字节

• 表名和字段加入注释

• 控制单表的数据量大小：行列数量大小

               对于日志数据，进行归档

               对于业务数据进行分库分表

• 分区表需要谨慎使用

• 控制表宽度

               虽然表没有行限制，但是列最多4096

                如果列多了，占用内存和i/o会非常大

• 禁止在表中建立预留字段

               varchar类型占用空间大，影响效率

               违背上面的命名规则

               时间久了，不看业务代码，完全是魔鬼字段

• 禁止在数据库里存放图片、文件、二进制文件 

              如果要用blob、text存大文件，select columns...

              如何避免select *,外键表

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

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

         3.3数据库索引设计规范

• 单张表索引数量建议不超过5个，如果列多可以适当增加

               索引过多：sql在进行优化器评估的时候会有更大的开销

                绝对不允许给表的每一列都建立索引

• 每个innodb表都必须有一个主键，innodb表就是一个索引组合表

                 表数据的实际存储顺序只能有一种，innodb是按照主键进行存放的

                  如果没有主键，mysql会优先选择第一个非空唯一索引来做主键

                  如果上面这个没有，mysql会自动生成一个36字节的主键，但性能不好

                  不能使用更新频繁的列和联合索引做主键，主键不断变，数据的存放顺序就会不断变化

                  不要使用uuid、md5、hash等做主键，不能保证这些值是按顺序增长的。如果生成较小

                 的字符串就会导致不断变化数据存储的位置，影响i/o性能。

• 要在哪些列上建立索引：没有最好只有最适合

             explain

             where后

              join的连接列

              筛选项最大的放在索引最左侧

• 避免建立冗余和重复索引 

• 对于频繁查询的数据列，优先考虑使用覆盖索引

• 尽量避免加入外键约束

              因为外键写入的时候会降低存储效率

              但要给这些关联字段加索引

             

         3.4数据库字段设计规范

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

              一搬用int来存放时间戳 

              varchar(255)长度过长会消耗内存

•  避免使用text，blob数据类型

               如果非要使用可以单独拉出来做关联表

               这两个类型上没有默认值

• 避免使用ENUM数据类型 

               修改则需要使用ALTER语句

•  尽可能把所有列定义为not null 

               如果为null,索引需要额外的空间保存

•  日期格式尽量不要用字符串保存 

               不能用日期函数进行计算和比较

                用字符串占用的空间更多 

                timstamp 32位操作系统时间范围：      1970-01-01 00：00：00 到2038-01-19 03:14:07   

•    财务相关的数据用Decimal类型

         3.5数据库sql开发规范

• 在程序中使用PrepareStatement

            降低词法和语法分析器的重复执行 

            防止sql注入

• 合理和充分的利用表上的索引

            避免前后%

            使用left join或not exists来优化not in(not in 无法使用索引)

• 程序连接不用数据库使用不同的账号，禁止跨库操作

             跨库访问最好调用业务层：例如应用A用到数据库B和数据库C的数据：应用A--访问-->数据库B  ==接口访问==>应用D--访问-->数据库C

             如果账号被注入，也只注入一个库

• 禁止使用select * (但是依旧这么做)

• 禁止使用不含列名的insert  tableName values("","","");

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

              子查询的结果集无法使用索引

              子查询会产生临时表操作，如果查询量大则会严重影响效率 

• 避免使用join管理太多表，建议最多5个表

               大查询拆小查询，由我们的程序来去做关联和合并

               进行表数据冗余

• 减少同数据库的交互次数

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

• 禁止用order by rand()

• where中不要对列进行函数计算：否则列无法使用索引了 

• union all和union  (union对数据做了一次去重操作)

      如果我们的数据明显不重复，就使用union all

       程序=数据结构+算法

       3.6 数据库操作行为规范   

• 大批量的数据操作会严重造成主从延迟

                 数据分批执行，以免造成主从库数据延迟

                 v5.7 binlog_format：row方式下

• 对大表结构的操作会导致锁表

                  对于大表的操作，可以用：pt-online-schema-change(PERCONA)

                  原理：创建新表结构->复制就表数据到新表->在原表上加入触发器确保数据同步->所有操作进行完毕后对原表进入一个很短的时间锁-

                 >把原表名进行修改，再改新表->删除原表名->原子性操作分割进行

• 禁止对普通用户授予super权限

• 对于程序遵循最小权限原则