The role of redundant fields in database performance optimization

What is a redundant field.

When designing a database, a field belongs to a table, but it appears in one or more tables at the same time, and is exactly the same as the meaning of the table it belongs to, then the field is a redundant field.

--These are the definitions I gave myself.

Whether the existence of redundant fields is good or bad. This is a difficult question to say. It may seem to anyone that this is a very crappy database design. Because in the field of database design, there is a mantras of the database design paradigm, this paradigm requires a clear logic of the database design, the relationship is clear, for example, "User nickname" Field "nickname" originally belong to the table "user", then, the "nickname" of the field should only belong to "User" table "nickname" field, so that when the user wants to modify the nickname, the program only need to modify the User.nickname the field on the line, see, very convenient. The problem, however, is that I store only the ID of the user in other datasheets (such as order Orders table), and I'm going to do it by using this ID as a nickname for the user. A common solution is to get the user's nickname by joining (join) and connecting two tables with the unique condition of the ID at the time of the query.

This is really no problem, and I have always felt that this is the best solution, expansion is convenient, when to update the user information, the program to modify the place is very small, but as the data in the database continues to increase, million, tens of millions, at the same time, the user table data must also be increasing, it may be 100,000 million. This time, you will find that two tables through the join to fetch data is very laborious, perhaps you only need to take a nickname this user nickname attribute, you have to go to the hundreds of thousands of user table for retrieval, its speed can be imagined.

This time, you can try to add nickname this field to orders this order form, the good thing is that when you want to use the order form to render an order list, the part of the user involved may not need to join the query. Of course, the advantage of the disadvantage, the disadvantage is that when you try to update the user information, you must remember the User Information table is currently updated in the field, which are redundant fields, which belong to the table, find them, and then add to your update section. This is the overhead of the program, the cost is in the developer's time. Whether it is worthwhile or not depends on the circumstances.

Therefore, to create a relational database design, we have two options: as far as possible to follow the paradigm theory of the statute, as little as possible redundant fields, so that the database design looks exquisite, elegant, people enchanted. Reasonable add redundant field this lubricant, reduce join, let database execution performance higher and faster.

Choose which one you want. If you are an aesthetic madman and have the deep pockets to use the first option, it doesn't matter that the short board of this program is not hopeless. For example, you can increase the server, starting from the database cluster, read and write separation, reading, you can spread the pressure on different database servers, so you can get good performance, but also to pay a lot of hardware costs and maintenance costs. Alternatively, you can set up caching services such as memcached on the front end of the database, reducing the number of reads and writes to the database, and can achieve the same effect. The problem is that you're sure you need a cache or something.

Of course, if you are like me, only a dozens of yuan per month to buy a VPS, may even be a virtual host, suggest or temporarily suppress your aesthetic desire, with me to choose the second option, unless you want your entire database has been only a few sporadic data