2 数据库结构优化

  1. 云栖社区>
  2. 博客>
  3. 正文

2 数据库结构优化

javaedge 2018-04-06 23:05:00 浏览597
展开阅读全文

2.1 数据库结构优化的目的

  • 减少数据冗余
  • 尽量避免数据维护中出现更新,插入和删除异常
    • 插入异常
      如果表中的某个实体随着另一个实体而存在
      先看一个表结构


      img_4958f35f9598b72e42f4c511d82cd68b.png
      为学号,课程名称列定义主键约束,即一个学生只能选相同的课程一次

      看看数据


      img_41057077e2bb25f461f9dcd8286a9546.png
      存在数据冗余

      插入一门新课试试
      img_62a9399578b7b85b4fda25c032aed54e.png
      由于主键约束的存在,在没有学生选择这门课时无法将新课程插入到表中
    • 更新异常
      如果更改表中的某个实体的单独属性时,需要对多行进行更新


      img_2bd9839206afb91a2719825ba96d2631.png
      更新了2行数据,数据越多,同时更新的也就越多,可看出和数据冗余有很大联系
    • 删除异常
      如果删除某一个实体会导致其他实体的消失
  • 节约数据库存储空间
  • 提高查询效率

2.2 数据库结构设计的步骤

img_1631fc596dfdb152b353b632cf4ca869.png

img_3e6327e5b56bc700f7d8c50594fb3eb5.png

img_312ccf8a96b030f3e45f0103ed66e68b.png

img_9065c30cd2c0652a927b2b0596064de8.png

2.3 数据库设计范式

img_fb812f03ba952a2bd18b0e0e32523a13.png

img_3260792a6658ef4f0b144351f41bc9f5.png
有时需要反范式

img_46236bbb9e15418366567d1d7c52df3c.png

img_5617a438c21e5799b9b3039a97095831.png
符合第一范式

img_4b7881456d9ab634f3ee92064dd1b7dc.png

img_e3e5695798afbe87efd556f157cfa5fc.png
将上个表拆成两个表,即符合第二范式

img_6969ce4e950e016e539df706c35ddc4f.png

上面的study学生信息表,学号可以确定学院,而学院地址又与学院有关系,所以学院地址和学号传递依赖关系,所以对其拆分符合第三范式


img_851ed32654b6e2b79acf893d136b69da.png
学生表数据

img_5f03590ec8026d4c00f1e8d81b73cf43.png
学生信息表

img_d9e61e425f90a9243c697a43e51b025c.png
学院信息表

2.4 数据库需求分析及逻辑设计实例

img_3380585b03a330cdf07ea971817636b3.png
需求说明

img_4c3bee218e81952a84beed40a09b2ec2.png
需求分析及逻辑设计,设定用户名为主键

img_0c0d732d5329a3b1c5f21d94250d67e2.png
分析

img_e08853020c264a550b1c13da3368d19c.png
一本书可能在多个分类中,所以联合主键商品名称和分类名称,不符合第二范式,所以拆分

img_3fd84277a591b968f15762346c8b89cb.png
只有一个主键,满足三范式

img_dbd149b4bf8ee1639beb2b1cc7aca8af.png
需要拆分

img_5d75c74c998fae9264e40f47a044feb0.png

实际性能

img_b552573a01053da89b00540115ea92a2.png

img_1659ef9605e9ba98ecc75935dedaf0d5.png

img_98257a826d267e1ad6bbf4b63370674c.png

2.5 反范式化(空间换时间)

img_598642f98adc04bc80b16fe9d951a780.png

对前面的案例进行反范式化改造


img_4265e23550a6b54eedc70e1b29803efa.png
想查一个商品的信息,必须同时关联这三张表,而第三张表更是几乎每次查询都会用到确认某分类下某商品

img_91514e9f5688ff7bb64b4652af7b0a1f.png
所以对商品信息表反范式化即增加分类名称的冗余数据,为避免插入异常,保留分类信息表,这样不会发生丢失分类信息情况

img_65b0d2b60c27bbb295ad5c6ccdeb574e.png
订单表变换

2.6 反范式化后查询

img_20007ca09498c71b2b0456a5957d5b8e.png
只需查订单表,不再需要对四张表查询

img_8a43276084ab3c039c6c20017ffedbc9.png
由于冗余了用户手机号,商品价格在订单表和订单商品表冗余处理

img_3434a49dca0c6fe8c388e4069dec1963.png

2.7 范式和反范式优缺点

2.7.1范式化

优点

img_a2f1550d95800f97e9c1a4549fa98fe4.png
优点看起来很完美,提高了写操作但是损失了读操作性能

缺点

img_08e7b7aca13003d039108e11aaf67d11.png

2.7.2反范式化

优点

img_eb203450e1f8dbb021b4ee7d8a5eed9f.png

缺点

img_2b9b8559105c18272d4011c8b574b777.png
image.png

2.8 物理设计

2.8.1概念

img_c6e824fdc5a3fcc5e40db4f9d3ca7a4e.png

2.8.2定义数据库,表,字段的命名规范

img_2aecbc2bcaaa2d5cd7df2c11109fd375.png

2.8.3选择合适存储引擎

img_f79a5d705630297ad989e8a70c52f9e9.png

2.8.4为字段选择合适的数据类型

img_8651aaf63b4719658fbddebc63eb72af.png
原则

2.8.4.1整数类型

img_e131ecbc6cd32844c6eb02069a6bb4ea.png

2.8.4.2实数类型

img_f0f7a326e05a855025b1210bd127bab8.png

img_cf4aa2e1abd8704b36f777fe9f8eac3e.png
示例表

img_eb3cb6def80a2dc2aaf8a23b284a3202.png
示例表数据

img_36a7cf9ccba4d18d62643dd031ead493.png
看出计算不准确

img_9c9e07df706178a6e18d5a20009ce836.png
一种精确地实数类型

img_a2d294bbe6abd724cdbf77cacdab95f9.png

img_1cdf6e40d18651e34d399d8c70dfe9b5.png
看出计算精确

2.8.4.3字符串类型

2.8.4.3.1 varchar

定义的宽度是字符单位,存储才是字节单位


img_57773c28ec6ee1a5c756869d08214216.png
varchar类型存储特点

img_93492b4f10e0f52b4e689c703a1b96f8.png
varchar长度选择

img_c1dc2b75a37b5bb43a97cd6f656c4926.png
适用场景
2.8.4.3.2 char
img_f4578848df53089c9deada100872923d.png
存储特点

img_0d2a5cce4194bbee1792a58787e933c0.png
适用场景,身份证性别等

2.8.4.4日期类型

2.8.4.4.1DATETIME
img_73b4668fe7ae1e85540ccb6e01cd1081.png
最通用时间类型
2.8.4.4.2TIMESTAMP
img_65d880fd4a3522c8f606f592eb04b6a9.png

img_069de0318d129bca07fd3b56604579e0.png
2.8.4.4.3时区问题
img_7958bcb8047bb18aedaefad332dcf4c0.png
设置时区-第十时区

img_71c3168d91eaaf30af403b7577830300.png
建表

img_fe59172d3b3da28bb8cddf4369adf072.png
TIMESTAMP时间随时区变化
2.8.4.4.4微秒问题
img_59c54a2c65a86c685d0c877a95c335a1.png

img_3483697cc1ec36a191a7683b089ee96b.png
表数据
2.8.4.4.5自动更新问题
img_8c23016db120cd9aae6977b7d7e5298c.png
建表

img_b25dd82b5af0456460a67f9fc32b10c9.png
只有第二列自动更新时间,默认只有第一个TIMESTAMP列自动更新
2.8.4.4.6
img_04772484876e93b78969432eda1a6b37.png
MySQL5.7前存储时间可选方式

MySQL5.7后date,time类型横空出世


img_d98e5826d4ebac31cd08b7e3396477bc.png

img_b6afd58861eb03b7f13857a4a4bd0613.png
image.png

img_7e731655766975799f9f3cf2eeebafdc.png
2.8.4.4.7注意事项!!!
img_adf09463fb2679184c7eb228d3d4246a.png

img_757787fe38bbe32718098a7c5e4d25e1.png
后者不仅实质是int,而且也是时间戳
2.8.4.4.物理设计总结
img_4b9575c39cc929e50cfea9644c0d0d48.png

img_eceb1027cc934579ca109999a3ff51cd.png

img_04e2a9c58d7a19b0a4e2ea93f0a809cc.png
第三条以前两条为基准

网友评论

登录后评论
0/500
评论
javaedge
+ 关注