DB design 1: There is 1 table
Create Table (id int primary key, name varchar(20), description varchar(10000));
DB design 2: There are 2 tables
Create Table1 (id int primary key, name varchar(20));
Create Table2 (id int primary key, description varchar(10000));
Note: each id must have a description associated with it. We don't query the description so often like name.
In the design 1, 1 simple query can get name & description, no need join but what if we have 1 million records, then will it be slow?
In the design 2, we need join so the database needs some searching & matching id --> this could be slow, but we don't query description often so it will be slow for sometime not all time.
So what is the better design in this scenario?
That's called vertical partitioning or "row splitting" and is no silver bullet (nothing is). You are not getting "better performance" you are just getting "different performance". Whether one set of performance characteristics is better than the other is a matter of engineering tradeoff and varies from one case to another.
In your case, 1 million rows will fit comfortably into DBMS cache on today's hardware, producing excellent performance. So unless some of the other reasons apply, keep it simple, in a single table.
And if its 1 billion rows (or 1 trillion or whatever number is too large for the memory standards of the day), keep in mind that if you have indexed your data correctly, the performance will remain excellent long after it became bigger than the cache.
Only in the most extreme of cases will you need to vertically partition the table for performance reasons - in which case you'll have to measure in your own environment with your own access patterns, and determine if it brings any performance benefit at all; and is it large enough to make up for the increased JOINing.
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加