【系统分析师】- 案例 -数据库特训

目录

1、规范化与逆规范化

2、数据库视图

3、数据库索引

4、SQL优化

5、数据库分区

6、分布式数据库

7、NoSql

8、读写分离（主从复制）

9、缓存一致性

10、云数据库

11、主题数据库

12、数据同步

1、规范化与逆规范化

规范化：

优点：

1. 解决数据库中数据的插入、删除、修改异常等问题
2. 减少数据冗余
3. 确保数据的一致性和完整性
4. 提高数据安全性

缺点：

1. 可能由于拆分的表过多，从而导致查询性能较低
2. 连接查询较多，增加sql复杂性

技术手段:

1. 第一范式 ：属性不可再分
2. 第二范式：消除非主属性对候选键的部分依赖
3. 第三范式：消除非属性对候选键的传递依赖
4. BC范式：每个依赖的左侧决定因素都包含某个候选键

逆规范化：

优点：

1. 增加了冗余列，使得查询sql简化，增加了查询效率
2. 增加了派生列，提搞了统计效率

缺点:

1. 数据冗余可能导致在插入、更新，删除时，数据不一致问题
2. 冗余数据，会消耗额外的存储空间
3. 更新数据时，需要同时更新冗余数据，会降低操作效率
4. 增加程序复杂性，需要编写复杂的数据同步程序来同步冗余数据

缺点解决方案：

数据不一致问题解决方案：

1. 触发器数据同步
2. 应用程序数据同步

技术手段：

1. 增加冗余列：常用于减少连接查询
2. 增加派生列：常用于减少计算，比如，订单中商品数量和单价存在，派生出订单总价字段
3. 水平分割表：根据某个属性值按照一定规则（散列、取余）进行水平分割数据记录到相同结构的多张表
4. 垂直分割表：将主键与表中的部分列作为一个表，主键与其他列最为另一张表
5. 重新组表：将拆分过度的表重新组合为一张表

2、数据库视图

优点：

1. 视图能简化用户操作
2. 对数据库重构提供了一定的逻辑独立性，数据库修改表结构，视图使用者，无需修改
3. 保护数据的机密性，比如用户表中有身份证，手机号信息，但我们指向对外提供用户名和邮箱，可以创建一个用户名和邮箱的视图
4. 灵活的控制数据的可见性，可以提供不同视图供不同的用户使用

物化视图：将视图的内容存储起来，随原始表数据发生变化，同步更新

3、数据库索引

优点：提高查询效率

缺点：降低数据修改、删除效率、需要额外的空间存储索引文件

过多索引问题：

1. 过多索引会占用大量的存储空间
2. 更新语句会引发索引更新，降低更新操作效率
3. 会导致查询优化器压力增加，评估的组合增多
4. 聚集索引更新会导致非聚集索引同步更新，降低了处理效率

索引使用建议

1、需要对建立的索引进行实际的测试，因为索引的使用是数据库优化器决定的。

其他点，围绕“过多索引问题”进行罗列

4、SQL优化

1. 建立物化视图，避免多表连接查询
2. 查询时，只查询需要的属性列
3. 对常用的查询列建立索引
4. 以不相干的子查询替代相干子查询
5. 经常提交COMMIT ,尽早释放