2023年7月27日发(作者：)

数据库物理设计的方法论

方法一：根据数据库逻辑设计，找到相应数据库产品的实现方法。

对于同一套数据库逻辑设计，不同的数据库产品有不同的实现方法，下面的表格列出了不同数据库产品的实现技术。

逻辑架构

对称多处理器(SMP)

Sharing Nothing架构(多分区数据库)

Share Disk架构(共享磁盘)

Sharing Nothing + Share Disk

实现技术

基本所有商业数据库都支持

DB2 DPF 、TeraData、Greenplum、Netezza

DB2 pureScale、Oracle RAC

ExaData

方法二：合理设计数据库对象

合理设计对象是数据库的逻辑概念，其设计的好坏除了决定系统功能，有时还会决定系统的性能，其设计主要包括模型的设计，表类型的选择，索引的设计，分区设计，表空间的设计等。

在模型的设计上，我们通常采用第三范式来进行数据库对象的模型设计，但是有时为了性能考虑，我们通常会做一些退化，比如设计一些冗余字段或者多表合并来加快系统的查询性能。例如对于有大量数据的销售流水表，完全按照第三范式来做的话会有一个外键关联客户信息，一个外键关联产品信息。

CUSTOMERSSALESoooooSALES_IDAMOUNTTIMECUST_ID PKPROD_ID PK

但是如果系统在运行一段时间后，这三张表的数据变的非常多，那么连接查询的性能将会变得很差，我们通常的做法是再设计一张横向表，将客户信息和产品信息全都放到销售流水表里去，这样应避免了关联查询。

T_SALESooooooooooSALES_IDAMOUNTTIMECUST_IDCUST_NAMEGENDERAGEPROD_IDPROD_NAMEPROD_CATEGORY

...表通常分为很多种，除了基本表外，还有临时表、多维表、分区表、范围集群表、物化视图表等。不同的表有不同的用途，如果错误使用的话，也会对性能有比较大的影响。比如在不需要物化视图的地方使用的物化视图表，那么会对源表的增删改性能造成影响。 合理的使用正确的索引是提高系统执行效率的关键因素，对索引的使用需要注意以下一些问题：

1) 注意LIKE运算符。

2) 注意NULL值。

3) 优化复合索引。

4) 注意索引的相关参数。

5) 注意索引与谓词的关系。

6) 根据查询所使用的列表建立索引。

7) 根据条件语句中的谓词的选择度创建索引。

8) 避免在建有索引的列上使用函数。

9) 在那些需要被排序的列上创建索引。

10) 合理使用include关键词创建索引。

11) 指定索引的排序属性。

数据库的页大小也是在数据库设计的时候就应该确定的，否则一旦实施就很难更改。对于执行随机行读写操作的OLTP(联机事务处理)应用程序，通常最好使用较小的页大小，这样不需要的行浪费的缓冲池空间就会较少。对于一次访问大量连续的OLAP(联机分析处理)应用程序，页大小大一些比较好，这样就能减少读取特定数目的行所需要的I/O请求数，更大的页大小也可以允许您减少索引中的级别数。总之，在满足以上条件的情况下，交易系统使用较小的页更适合，仓储系统使用较大的页更适合。

对表空间的选择，通常的数据库都包含系统管理的表空间和数据库管理的表空间两种类型。系统管理表空间由操作系统的文件系统管理器分配和管理空间，管理灵活但是性能很差。数据库管理的空间由数据库管理程序控制存储空间，表空间容器可使用文件系统或裸设备，虽然管理复杂，但是在性能上会有很大的提升。

方法三：合理设计存储

方法四：优化数据库参数，减少资源竞争

优化配置数据库的参数，包括各种缓存池的大小，内存区的配置，刷新脏页的策略，锁的策略等。虽然各个数据库都不相同，但是所有的出发点都是为了通过数据库参数，降低物理读的次数和发生资源竞争的可能性。

性能评审关注的性能指标：

1) 耗时最长的SQL的平均执行时间。

2) 数据库的平均CPU利用率。

3) CPU的执行时间、IO等待的时间和锁等待时间。

4) 平均I/O响应时间。

5) 支持峰值IOPS数和MPS数。

6) 物理读和逻辑读的百分比。

7) 有效索引读的百分比。

8) 有效行读的百分比。

性能优化策略

1、数据库、表创建方式的优化

在我们设计概念模型时需要结合业务需求，设计出合理的对象关系和优化的模型结构，在设计物理模型时应该充分考虑创建库表基本策略。

1) 建数据的日志方式

No Logging：不能进行事务处理。

Buffered Log：共享缓存满即刷新写入磁盘。

Unbuffered Log：当一个交易完成时即刷新写入磁盘。

2) 对数据库表空间分配的优化

3) 采用表分片(fragmentation)技术

4) 建表时对表的记录锁方式根据应用处理的不同区别对待。

5) 其他一些创建数据库表应遵循的原则

2、建立索引的策略

1) 建立索引的代价

虽然索引可以很大地提高选择提高选择性查询的性能，但维护这些索引是需要付出代价的。

 储存开销：建立索引将占用许多页的数据空间。

 处理开销：对一个索引表的数据进行编辑(如，INSERT、DELETE、UPDATE)时都要同时更新索引。

2) 索引策略的优化与原则

 避免调度重复率字段建立索引。

 对同一表不要建立过多的索引。

 建索引的字段的大小尽量小，复合索引尽量少用。

 建聚类索引，减少索引文件碎片，以加快检索速度。

 在经常进行连接，但是没有指定为外键的字段上建立索引，而不经常连接的字段则由优化器自动生成索引。

 Where条件子句经常用到的字段上加索引。

 在频繁进行排序或分组(即进行group by或order by操作)的字段上建立索引。

 限制在频繁update的表上建索引的数目。

 对大批量update操作(如load数据)，首先drop index，再update；然后再建索引，能提高性能。

 数据量小的表是否建索引影响不大，一般不要对记录小于200的小表建立索引，因为从使用索引得到的速度不能抵消在表上打开和检索索引所需的时间。  用永久索引代替自动索引。

 建primary key。

3、查询语句的优化

程序设计中的一个著名定律是20%的代码用去了80%的时间，在数据库应用程序中也同样如此。源代码的优化对数据库系统性能的提升收效有限。一个好的查询计划往往可以使程序性能提高数十倍。下面是查询语句的优化建议：

 合理使用索引：where子句中变量顺序应与索引字段顺序相同。

 将最具有限制性的条件放在前面，大值在前，小值在后。

 避免采用MATCHES和LIKE通配符匹配查询。

 避免相关子查询。

一个字段的标签同时在主查询和where子句中的查询中出现，那么很可能当主查询中的字段值改变后，子查询必须重新查询一次。查询嵌套层次越多，效率越低，因此应当尽量避免子查询。

 避免或简化排序

应当或避免对大型表进行重复的排序。

 对于大数据量的求和应避免使用单一的sum命令处理，可采用group by方式与其结合，有时其效率可提高几倍甚至百倍。

 避免会引起磁盘读写的rowid操作。在where子句中或select语句中，用rowid要产生磁盘读写，是一个物理过程，会影响性能。

 使用临时表加速查询。

把表的一个子集进行排序并创建临时表，有时能加速查询。它有助于避免多重排序操作，而且在其他方面还能简化优化器的工作。

但要注意：临时表创建后不会反映主表的修改。在主表中数据频繁修改的情况下，注意不要丢失数据。

数据库高级管理

建立数据库深度健康检查系统

业务系统1业务系统1业务系统1报表趋势图健康检查系统数据收集子系统数据分析子系统报表子系统指标扩展子系统数据库历史数据库历史分析子系统