使用外键加速表连接 [英] Does using Foreign Key speed up table joins

问题描述

我认为我的标题很自我解释，但是我将概述我在下面做的事。

一个人连接到一个城镇。这个镇连接到一个县。该县连接到一个国家。一个国家有很多县。还有一个县的很多城镇。这就是说，许多人可以来自同一个城镇。

在我的数据库中，我有一个单独的人民，城镇，县和国家的表。要获得完整的用户记录，请左键单击记录集上的表。这是一个简单的版本，我作为一个单独的人散布在更多的桌子上。 FOREIGN键的使用会加快执行查询的速度吗？即使只是轻微的速度增加或更少的资源使用情况，我也会感兴趣。

我计划的规模相当大，所以执行时间资源的使用可能是有用的。至于从数据库返回的单个人，我有大概5-6个连接多个表。

编辑我想指出我使用主键和唯一键为每个表建立索引。此外，数据集可以在一个表（人）中快速添加数以百万计的行，因此性能是关键。 解析方案

外键不要直接加快查询的执行速度。他们确实有一个间接的影响，因为他们保证被引用的列被索引。这个索引会影响性能。

在描述问题的时候，所有的连接关系都应该包含一个表中的主键。结果查询应该是一个非常有效的执行。

我不担心查询的5或6个连接 - 除非你有非常大量的数据多于一个的表格有数百万行）。或者你是在一个严重的内存限制的环境。

I think my title is pretty self explainitory, however I will outline what I am doing below.

A person is connected to a town. This town is connected to a county. The county is connected to a country. There are many counties in one country. Also there are many towns in a county. That being said many people can be from the same town.

In my database I have a separate table for people, towns, counties and countries. To get a full user record I LEFT JOIN the tables on a record set. This is a simplied version of what I do as a singular person is spread across many more tables. Would the use of FOREIGN keys speed up the execution of my query? Even if it is only a slight speed increase or less resource usage happening I would be interested.

The scale at which I am planning for is quite large, so the execution time and resource usage could be useful. As on a single person coming back from the database I have roughly 5-6 joins for multiple tables.

edit for the answer so far I would like to point out I am indexing each table, using primary and unique keys. Also the data set could quickly add up to millions of rows in one table(people) so performance is key.

解决方案

Foreign keys do not directly speed up the execution of queries. They do have an indirect effect, because they guarantee that the referenced column is indexed. And the index will have an impact on performance.

As you describe the problem, all the join relationships should include the primary key on one of the tables. The resulting queries should be a very efficient in execution.

I would not worry about 5 or 6 joins for the queries -- unless you have a very large amount of data (more than one table with millions of rows). Or you are in a severely memory-constrained environment.

这篇关于使用外键加速表连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！