可以将文章内容翻译成中文,广告屏蔽插件可能会导致该功能失效(如失效,请关闭广告屏蔽插件后再试):
问题:
As I understood from this post, there are some scenarios where foreign keys can improve query performance.
I've heard the opposite claim though, that because of referential integrity checks, foreign keys can actually hurt query performance. Under which conditions (if at all) is this true?
1) The term query seems to be misleading. I am interested in all kinds of performance penalties.
2) Does anyone have any real-world numbers about the negative impact on INSERT, DELETE or UPDATE statements (I know it depends on the specific system, but nevertheless any kind of real-world measurements would be appreciated)?
回答1:
I'm assuming that for INSERT queries, constraints - including foreign key constraints - will slow performance somewhat. The database has to check that whatever you've told it to insert is something that your constraints allow it to insert.
For SELECT queries, foreign key constraints shouldn't make any changes to performance.
Since INSERTS are almost always very quick, the small amount of extra time won't be noticeable, except in fringe cases. (Building a several gigabyte database, you might want to disable constraints and then re-enable later, as long as you're sure the data is good.)
回答2:
if a foreign key is needed for referential integrity then the presence of the foreign key should form the baseline for performance
you might as well ask if a car can go faster if you don't put seats in - a well formed car includes seats as much as a well formed database includes foreign keys
回答3:
In theory, yes: data writes need to validate the constraints.
In practice, seldom: unless measured and proved otherwise, you can assume there is no performance impact. Overwhelmingly, performance problems occur due to other problems:
- bad schema design (missing indexes, bad clustered index choice)
- contention (blocking), again due to bad schema design (table scans guarantee lock conflicts)
- bad query design
On a well designed schema and good queries the cost of constraints will start to show up at very high throughput. When this happens, there are preventive measures.
My 2c: Never sacrifice correctness constraints for some elusive performance goals. In the very rare case when the constraints are indeed the problem there are measurements to show that's the case, and as the saying goes: if you have to ask how much it costs, you can't afford it. If you have to ask if constraints can be a problem, you can't remove them (no offence intended).
回答4:
For INSERT/UPDATE/DELETE the short answer is, "Yes". The database will need to check that the referential integrity is intact and the creation/modification is allowed. Or in DELETE's case, there may be some cascading to be done.
For SELECTs, it's actually quite the contrary. Foreign Keys have a secret added benefit of showing you exactly where you're most likely to be doing complex JOINs and have very commonly used fields. This makes the job of indexing much easier, and you can pretty much guarantee that all of your FK fields should be indexed. This makes SELECTs much faster.
回答5:
Foreign keys can cause inserts(or some updates) in child tables or deletes from parent tables to take longer. This is a good thing however as it means that it is making sure that the data integrity remains. There is no reason whatsoever to skip using foriegn keys unless you don't want to have useful data. You wil not normally notice much differnce unless you have many foreign keys realted to the same parent table or if you are inserting or deleting many records in one batch. Further, I have noticed, users are more tolerant of a couple of extra seconds in an insert or delete than they are in a select. Users are also not tolerant at all of unreliable data which is what you have without foreign key constraints.
You will need to index them to improve performance on select queries.
回答6:
Foreign key checking takes more time than most people think. A current test with Oracle 11g and a table with two foreign keys showed that the time for an insert of about 800.000 rows took 60 seconds with enabled foreign keys but only 20 seconds without foreign keys.
(The foreign key columns were indexed, of course)
Anyway, I agree with all the other posters, that integrity constraints are not an option, but the only way to keep data consistent. However, for imports, especially into empty tables, it could be an option to disable the foreign key for the time of the import, if time is critical.
回答7:
If foreign keys had any impact in that way, it would be on INSERTS. The database does the referential checking on foreign keys when records are created/modified, not SELECTed.
回答8:
Foreign keys will not adversley affect query performance in most cases, and are strongly recommended. By aiding normalization, you will eliminate redundant data, and if you follow up by adding underlying indexes (for the appropriate foreign key) you will get good performance on your queries.
Foreign-keys can help the query optimizer get the best query plans for a given query.
Foreign-key checking is a factor when you update your data (which is a separate consideration - I assume here your concern is query - unless by the word query you imply both).
回答9:
I believe the noted post pointed out that putting an index on FK fields improved performance, not simply that a FK relationship improved performance. The existence of a FK on a table should not have any effect on a SELECT query, unless JOIN operations are being done, at which point, the FK relationship AND index on FK fields would improve performance.
回答10:
If you're enforcing referential integrity, INSERTs, and UPDATEs that effect the FK field, will be slower. However, it's usually not much to worry about, especially as a lot of DBs are 80% read/20% write. It's also a price worth paying.
Creating an index on a foreign key is often beneficial, though obviously it how much depends on what SELECT statements you're running.
Generally, you need foreign keys due to normalisation (which avoids duplicate data and synchronisation problems). Normalise to the 3rd degree, and then after analysing real world performance can you consider de-normalising.
回答11:
Foreign keys slow down insertions and alterations, because each foreign key reference must be verified. Foreign keys can either not affect a selection, or make it go faster, depending on if the DBMS uses foreign key indexing.
Foreign keys have a complex effect on deletion. If you're deleting the thing that refers to the foreign key, it won't affect anything, but if what you're deleting is referenced by a foreign key in another row/table, then it will generally cause problems.
Foreign keys can cause a minor performance degradation in table creations and alterations.
Of course, this all assumes foreign key verification is in use.