数据库速度优化：很少行的表或少行几行？ [英] Database speed optimization: few tables with many rows, or many tables with few rows?

问题描述

我有一个很大的疑问。

我们以一个数据库为例，为任何公司的订单。

假设这家公司每月生产约2000个订单，所以每年约24K的订单，他们不想删除任何订单，即使是5年（嘿，这是一个例子，数字不意味着任何东西）。

在具有良好的数据库查询速度的意义上，它更好地只有一个表，或者将更快地每年都有一个表

我的想法是每年为订单创建一张新表，称之为订单_2008，orders_2009等。

可以加快数据库查询的一个好主意？

通常使用的数据是当年的数据，所以有较少的行越多越好。
显然，当我同时搜索订单的所有表时，这会给出问题，因为我应该运行一些复杂的UNION ..但这种情况发生在正常的活动中是非常罕见的。

我认为最好有一个应用程序，95％的查询是快速的，剩下的有点慢，而不是总是很慢的应用程序。 >

我的实际数据库是在130个表，我的应用程序的新版本应该有大约200-220个表..其中大约40％将被复制每年。

任何建议？

编辑：RDBMS可能是Postgresql，也许（希望不会）Mysql

解决方案

较小的表格更快。

如果您有很少使用的历史记录，则将历史记录放入其他表格将会更快。

这是一个数据仓库 - 从历史数据中分离操作数据。

您可以从运行和负载运行定期提取到历史数据。所有的数据都被保留，它只是被隔离。

I have a big doubt.

Let's take as example a database for a whatever company's orders.

Let's say that this company make around 2000 orders per month, so, around 24K order per year, and they don't want to delete any orders, even if it's 5 years old (hey, this is an example, numbers don't mean anything).

In the meaning of have a good database query speed, its better have just one table, or will be faster having a table for every year?

My idea was to create a new table for the orders each year, calling such orders_2008, orders_2009, etc..

Can be a good idea to speed up db queries?

Usually the data that are used are those of the current year, so there are less lines the better is.. Obviously, this would give problems when I search in all the tables of the orders simultaneously, because should I will to run some complex UNION .. but this happens in the normal activities very rare.

I think is better to have an application that for 95% of the query is fast and the remaining somewhat slow, rather than an application that is always slow.

My actual database is on 130 tables, the new version of my application should have about 200-220 tables.. of which about 40% will be replicated annually.

Any suggestion?

EDIT: the RDBMS will be probably Postgresql, maybe (hope not) Mysql

解决方案

Smaller tables are faster. Period.

If you have history that is rarely used, then getting the history into other tables will be faster.

This is what a data warehouse is about -- separate operational data from historical data.

You can run a periodic extract from operational and a load to historical. All the data is kept, it's just segregated.

这篇关于数据库速度优化：很少行的表或少行几行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！