数据库设计与开发三大规范解读

👨‍🏫Author:陈虹君 Date: 2022-06-05 Release： V1.1

🚩无规矩不成方圆,无制度不成体系

数据库设计与开发是软件应用系统的根基，必须受到高度重视。培养良好的数据库设计与开发习惯，是一个合格软件工程师应该必备的基本素质。所以，数据库设计必须遵循一定的规则，良好的数据库设计与其系统应用设计是相辅相成的。好的数据库设计能够合理利用数据的存储空间、保证数据的完整性、减少数据冗余、方便进行数据库应用系统的开发、以及提高系统的使用性能。

✍吾日三省吾身,为人谋而不忠乎

SQL优化是一个任重而道远的事，有时候线上可能仅仅是一条很小的不起眼的SQL突然跑慢了，有可能引发业务系统雪崩，所以我们说SQL优化的重要性不言而喻，想学会SQL优化，须先学会SQL高效编写，想编写高效SQL你得遵循一定的规范，总结起来对于开发人员来讲，必须理解与遵循的三大规范：库表设计规范、索引设计规范、SQL 书写规范，三者相辅相成，缺一不可。

1.库表设计规范

📣表结构设计是应用软件开发基础底座

1.1重难点解读：

📣大数据量情况下，数据分片策略如何选择：【分区？OR 分表？OR 分库？】

👨‍🏫整体思想：分而治之

📣不同数据库类型建议分片策略：

（一）集中式数据库 ：分区，分表

（二）伪分布式数据库：分库，分表

（三）真分布式数据库：分区

①分区：

分区方式：范围，列表，哈希……以及它们的组合分区，大多数数据库都支持这三类分区方式，当然每类数据库都还有相应的其它扩展方式，不再一一描述。

🚩优点：
1.对应用访问透明，不影响应用业务逻辑
  --应用访问分区表与单表无任何差别
  --能够自动实现分区裁剪
2.提升数据高可用与数据可管理性
  --删除或交换部分数据高效快捷
  --分区高度自治，单个分区故障不影响其它分区数据
3.改善数据检索性能
  --能够自动实现分区裁剪，缩小检索范围，减少检索数据量
  --不同分区不同物理存储分布，平衡整体I/O性能

✍缺点：
1.初学者不易理解，语法规则相对复杂，分区键选择存在相应限制

②分表：

拆分方式：垂直拆分，水平拆分，垂直水平相结合。

🚩优点：
1.垂直拆分之后业务逻辑清晰，拆分规则也相对简单
2.水平拆分之后减少了单表的大小，能解决高并发情况下单表的性能瓶颈
3.相对分区来讲，分表让初学者容易理

✍缺点：
1.水平拆分之后，对应用访问不透明，需对应用进行数据路由改造
2.水平拆分规则相对复杂，须针对不同业务不同表设计不同的拆分规则
3.拆分规则设计不合理，会导致额外的JOIN，事务处理更复杂
4.相对来说扩容不方便，新增需重新分布数据

③分库：

拆分方式：垂直拆分，水平拆分，垂直水平相结合。

🚩优点：
1.降低节点的CPU,MEM,IO,NET负载，提升整体效率
2.水平拆分解决单库容量瓶颈，高并发性能瓶颈
3.垂直拆分解决业务数据耦合问题，满足微服务设计下的上层服务拆分原则
✍缺点：
1.引入分布式事务一致性问题、性能问题
2.数据聚合、数据路由等场景反而会存在性能损失问题

2.索引设计规范

📣索引设计是提升SQL效率最直接手段

2.1重难点解读：

1.索引整体设计原则：

🚩不要重复创建

能用复合索引的，就不要用单列索引，单列索引必须充分评估不要重复创建

🚩不要剑走偏锋

所有字段都创建单列索引 ，所有字段都纳入复合索引  ，这是两种不可取的极端做法

🚩保持索引有效

定期检查索引有效性，避免无效索引，占用磁盘空间，同时影响写入效率

2.索引整体创建策略：

📣创建单列索引的要素：

■频繁出现在where条件中的列

■用来与其它表进行连接的列

■有高的选择性和过滤性的列（高选择性的字段：如果很少的字段拥有相同值,即有很多唯一值,则选择性很好）

■Oracle与MySql在主键字段和UNIQUE字段上会自动建立唯一索引

   一般在查询数据量占总表数据量比例较小时才考虑使用索引定位数据，否则大量数据扫描还不如全表

📣创建复合索引的要素：

三要素：最左前缀原则、覆盖索引原则、高选择性条件联合
■适用于单独查询返回记录较多，而组合查询后返回记录较少的情况

​```
例如：where 学历=研究生 以上会返回不少的记录，where 工作=厨师 同样会返回不少的记录
	 那么这2个条件任意一个查询做索引，都不合适，但学历既是研究生且工作又是厨师的，返回记录就少之又少了，
	 这时候创建复合索引就非常合适
​```
■复合索引的组合顺序，要考虑单独的前缀查询情况（否则单独前缀查询的索引不能生效或者只能用到跳跃索引），我们应该让选择性好的做为前导列

​```
例如：我们创建object_id,object_type的联合索引时，要考虑是单独where object_id=xxx查询的多，
	 还是单独where object_type=xxx查询的多
​```
■仅等值无范围查询时，复合索引顺序不影响查询性能

​```
例如：where col1=xxx and col2=xxx，无论index(COL1,COL2)组合还是index(COL2,COL1)组合 ,都一样的效果）


​```

3.分区索引设计原则：

分区索引：全局Global与本地Local两种类型

设计原则可以参考如下：

3.SQL 书写规范

📣SQL规范书写是应用高效运行的基础

3.1重难点解读：

📣整体建议

补充强调一点：满足业务逻辑的场景下，分区表一定要带上分区键
1.整体风格统一，阅读理解清晰
2.宜简不宜繁（语句尽量简单，少做复杂运算……）
3.严格按照SQL书写规约执行，降低低级错误，提升SQL代码质量，减少SQL Review工作量
4.规则不要生搬硬套，规约之外受其它外在因数的影响，最终结果一切以SQL实际执行计划为准