如何提高数据库的性能？ [英] How to increase the performance of a Database?

问题描述

我在我公司设计了几次数据库。为了提高数据库的性能，我只寻找规范化和索引。


如果要求提高数据库的性能，该数据库具有大约250个表和一些包含数百万条记录的表，你会寻找什么不同的东西？

I have designed databases several times in my company. To increase the performance of the database, I look for Normalisation and Indexing only.

If you were asked to increase the performance of a database which has approx 250 tables and some tables with millions of records, what different things you would look for?

提前感谢。

推荐答案

优化逻辑设计

逻辑级别是关于查询和表格本身的结构。尝试最大化这第一。目标是在逻辑级别访问尽可能少的数据。

The logical level is about the structure of the query and tables themselves. Try to maximize this first. The goal is to access as few data as possible at the logical level.

• 拥有最高效的SQL查询


• 设计支持应用程序需要的逻辑模式（例如列的类型等）


• 设计权衡以支持某些用例比其他


• 关系约束


• 规范化

• Have the most efficient SQL queries
• Design a logical schema that support the application's need (e.g. type of the columns, etc.)
• Design trade-off to support some use case better than other
• Relational constraints
• Normalization

设计

物理级别处理非逻辑性考虑，例如索引类型，表的参数等。目标是优化IO总是瓶颈。调整每个表，以适应它的需要。小表可以加载永久加载到DBMS缓存中，表的写入速率低可以有不同的设置比表高刷新率占用更少的磁盘空间等。根据查询，可以使用不同的索引等。

The physical level deals with non-logical consideration, such as type of indexes, parameters of the tables, etc. Goal is to optimize the IO which is always the bottleneck. Tune each table to fit it's need. Small table can be loaded permanently loaded in the DBMS cache, table with low write rate can have different settings than table with high update rate to take less disk spaces, etc. Depending on the queries, different index can be used, etc. You can denormalized data transparently with materialized views, etc.

• 表paremeters（分配大小等）




• 分区


• 反规范化

• Tables paremeters (allocation size, etc.)
• Indexes (combined, types, etc.)
• System-wide parameters (cache size, etc.)
• Partitioning
• Denormalization

尝试首先改进逻辑设计，然后改进物理设计。

Try first to improve the logical design, then the physical design. (The boundary between both is however vague, so we can argue about my categorization).

优化维护

数据库必须正确操作才能保持尽可能高效。这包括可能影响效果的几个维护tak，例如

Database must be operated correctly to stay as efficient as possible. This include a few mainteanance taks that can have impact on the perofrmance, e.g.

• 保持统计信息最新


• 定期重新排序关键表格


• 磁碟维护


• 所有系统内容都拥有摇滚的服务器

• Keep statistics up to date
• Re-sequence critical tables periodically
• Disk maintenance
• All the system stuff to have a server that rocks

这篇关于如何提高数据库的性能？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！