System Performance Tuning (2)-Database Design Optimization

1, Logic design standardization

The so-called logic design standardization is to make the database logic more reasonable. To put it bluntly, this is what we usually call the three paradigm. For details, refer to my previous article.

The following is a summary:

First paradigm: the atom cannot be further divided

Second paradigm: only dependent on primary keys

Third paradigm: do not rely on others

In fact, in the end, the three paradigms are convenient to search for and prevent redundancy. For example, in the first paradigm, the central sub-feature is the modularization of information to facilitate user queries. Imagine what else should the database do if the data is not easy to query? The second and third paradigms describe other non-primary key fields from different aspects. The second and third paradigms ensure no database redundancy. This greatly improves the efficiency of database addition and update.

There are five database paradigms. Here we will only introduce three. In actual projects, we find that there are few projects that truly comply with the three paradigm. Many projects have to make the database redundant to improve the search efficiency. The following describes in detail.

2. reasonable redundancy

Everything has two sides, and the three paradigms are no exception. It is good to achieve a balance. As mentioned in the previous article, the bottleneck of system performance is relative. The principle is as follows: Who affects the performance of the entire system will solve the problem. As mentioned above, the three paradigms improve the query efficiency of databases to a certain extent and reduce unnecessary redundancy, however, if a large amount of data requires a large number of joint queries, the query efficiency is affected by the three paradigm.

For example, for the name field in the User table, if the name field is required according to the three paradigm, it should be stored in other places in the User table with only the user ID. However, if you need the name field of the User table in the order table (Order) of another table, you have to associate the two tables, when the data volume is small, it is no problem to use the Combined Query Method. Once the data volume exceeds million or even larger, the impact of joint query on performance is apparent. In this case, the name field can be redundant in the order table. You need to modify the order table and user table in two places when changing the name of the user (usually few people modify it). Proper redundancy improves the efficiency.

The "abandon" Three paradigm leads to a certain amount of data redundancy, reducing the number of joint queries, but the ultimate goal is to improve efficiency.

3. Index Design

In the design phase, you can perform a preliminary index design based on the functional and performance requirements. here you need to design indexes based on the expected data volume and query, which may be different from actual use in the future.

You should change your mind about the selection of indexes:

A. Determine which tables need to add indexes based on the data volume. If the data volume is small, only the primary key is required.

B. Determine which fields need to be indexed Based on the Usage frequency, and select fields that are frequently used as join conditions, filtering conditions, aggregate queries, and sorting fields as index candidate fields.

C. Combine frequently-used fields to form a composite index. The field order of the composite index is the same as that of the primary key. You also need to put the most commonly used fields in front, put the fields with low repetition rate in front.

D. Do not add too many indexes to a table because the index affects the insert and update speeds.

Indexing is not only a matter of consideration when designing databases, but also a rare trick to solve performance bottlenecks during system maintenance. The article will detail the optimization of indexes in the later maintenance of the system.

Summary:

The system performance is improved to improve the system's execution speed and accurate data processing. The tuning of system performance is usually done when a system bottleneck is encountered. It is understandable that no one is a crowdsourced security testing, however, we can put the optimization process at the initial design stage based on our own or others' existing experience. When you get the requirement, consider the bottlenecks that the system will encounter (of course, do not design too much. The consequence of design too much is to increase the overhead of the system) then, we can effectively prevent the problem based on the estimated bottlenecks (such as reasonable indexes and efficient primary key design ). In short, performance optimization is never done before it encounters a bottleneck. it is wise to consider performance issues from the design stage.