如何设计数据库能优化查询速度

Question

请教一个数据库设计相关的问题。为一个中型公司设计数据仓库，架构是从Staging databases -> Central OLTP Database -> Central OLAP Database, 最终在OLAP数据库上运行报表。所有的数据库用的都是SQL Server 2012。

设计1.尽量放主子表，保证主表和自标的Key都是数字。
主表： Account
字段：ID， AccountNumber, AccountName, AccountStatuSID, AccountPaymentTypeID ...........
A1 1010011 AN1 1 1
A2 1010012 AN2 3 2

子表1：AccountStatue
字段： AccountStatusID， AccountStatusName
1 Active
2 Inactive
3 Pending

子表2： AccountPaymentType
字段：AccountPaymentTypeID, AccountPaymentTypeName
1 Debit
2 Credit

其他子表就不一一举例了

设计2. 就一个主表,直接显示Name
主表： Account
字段：ID， AccountNumber, AccountName, AccountStatuS, AccountPaymentType ...........
A1 1010011 AN1 Active Debit
A2 1010012 AN2 Pending Credit

设计3. 就一个主表,显示ID和Name。报表运行时Where条件用ID
主表： Account
字段：ID， AccountNumber, AccountName, AccountStatuSID, AccountStatuS, AccountPaymentTypeID, AccountPaymentType ...........
A1 1010011 AN1 1 Active 1 Debit
A2 1010012 AN2 3 Pending 2 Credit

如果基于这种架构，如何设计OLAP数据库能得到最好的数据查询性能哪,并且易于ETL过程的Troubleshooting？ 数据量几十G，几百G或几个T各适用哪个设计？

Answer 1

以下回答参考 皆我百晓生、券券喵儿 等免费微信小程序相关内容作答，并由本人整理回复。

要提高查询性能并简化ETL流程的调试，可以考虑以下几点：

1. 索引：

   • 在可能的情况下，确保使用适当的索引来加快查询速度。
   • 对于经常用于查询的关键字段（如账户编号、状态等），创建索引以减少读取时间。

2. 分区：

   • 如果你的数据很大，且经常处理大量的小批数据，可以考虑将数据按某些属性进行分片（分区）。
   • 这样可以在查询大量数据时提供更好的性能。

3. 事务性更新：

   • 在事务性更新时，避免频繁地更改表结构或删除不必要的行。
   • 使用存储过程来执行复杂的操作，而不是直接在表上修改。

4. 多版本控制：

   • 对于那些经常被更改的数据项，使用多版本控制技术可以保持历史记录，有助于快速恢复到旧的状态。

5. 负载均衡：

   • 确保数据库实例具有足够的资源（例如CPU、内存、磁盘空间）以应对高并发请求。

6. 数据库模式设计：

   • 根据业务需求调整表的设计，比如是否需要额外的列来支持特定的查询或者分析任务。
   • 考虑使用合适的索引类型（如B-Tree索引而非Hash索引）以提高查询效率。

7. 性能监控：

   • 定期检查数据库的性能指标，包括查询响应时间、CPU使用率、内存使用情况等，以便及时发现潜在的问题。

8. 数据压缩与归档：

   • 对于非常大的数据集，考虑定期压缩存储数据，以节省磁盘空间。
   • 设置归档机制来保存日志文件，这样即使发生故障，也可以通过备份恢复数据。

9. 使用OLAP工具：

   • 配合使用OLAP工具可以帮助更有效地管理和分析大型数据集，减少手动工作量，并帮助更快地定位问题。

根据你提到的系统规模，你可以选择不同的设计方式。对于几十GB至几百GB的数据，设计2中的方法可能是最佳选择，因为它只需要维护一个主表，而不需要过多的子表。而对于更大的数据集，设计3可能会更有优势，因为这允许你灵活地组织数据，同时也能利用多版本控制和事务性更新等功能来提高性能。

至于ETL流程的Troubleshooting，可以根据具体情况进行相应的调试。例如，查看查询计划日志以了解查询的详细信息，以及使用性能监视器来监控查询的执行时间和资源消耗。

总的来说，设计应基于对业务需求的理解，以及对当前系统能力的评估，从而找到最合适的解决方案。