Database Performance Tuning Strategy __ Database

Data indicates that the maximum waiting time that a user can withstand is 8 seconds.
I have seen a list page of a product before, 40 seconds or so to load out, almost no optimization measures.
No indexes, no caching mechanisms, no SQL optimizations (SQL statements are long, and various left join tables are associated).
There are many database optimization strategies, in the early stage of design, it is very important to build a good data structure for later performance optimization. Because the database structure is the cornerstone of the system, the basic play is not good, using a variety of optimization strategies, can not achieve a very perfect effect. One: Normalization and anti-normalization

Everyone has heard: The three major paradigms of database design.
1. First paradigm (ensure that each column remains atomic)
The first paradigm is the most basic paradigm. If all the field values in a database table are atomic values that are not decomposable, the database table satisfies the first normal form.

2. Second paradigm (make sure that each column in the table is related to the primary key)
The second paradigm is more advanced on the basis of the first paradigm. Second paradigm needs to ensure that each column in a database table is related to a primary key, not just a part of a primary key (primarily for a federated primary key). This means that in a database table, only one data can be saved in a table, and multiple data cannot be saved in the same database table.

3. Third paradigm (ensure that each column is directly related to the primary key column, not indirectly)
The third paradigm needs to ensure that each column of data in the datasheet is directly related to the primary key, not indirectly.

There is no best design, only the most suitable design, so don't pay too much attention to the theory. Three paradigms can be used as a basic basis, not mechanically.
The most time-consuming operation in database operations is IO processing, where most database operations spend more than 90% of their time on Io reading and writing. Therefore, as much as possible to reduce IO read and write, you can greatly improve the performance of database operations. Second: Optimization strategy:

When designing a table, you should also consider normalizing some tables in the following ways:

One is to split the table.
Split table can be divided into horizontal partition table and vertical split table two kinds:
Horizontal segmentation is to split a table into multiple tables, which can increase the query speed of each table, but the query, update to select a different table, when the statistics to summarize multiple tables, so the application is more complex.
Vertical segmentation is a table with a lot of columns, and if some columns are accessed much more frequently than others, you can use the primary key and the columns as a table, and the primary key and other columns as a different table. By reducing the width of the columns, increasing the number of rows per page of data, one I/O can scan more rows, thereby increasing the speed of accessing each table. However, because of the multiple table joins, it should be used when querying or updating columns in different partitioned tables in a relatively small amount of time.

The second is to keep redundant columns. When two or more tables often need to be connected in a query, you can add several redundant columns to one of the tables to avoid too many connections between tables, which are typically used in cases where redundant columns of data are infrequently changed.

The third is to increase the derived columns. Derived columns are computed from other columns in the table, and adding derived columns can reduce statistical operations, which can greatly shorten the computation time when the data is aggregated.

In database design, data should be organized in two categories: frequently accessed data and frequently modified data.
For frequently accessed but infrequently modified data, internal design should be physically not normalized.
For frequently modified but infrequently accessed data, internal design should be physically normalized.
It is sometimes necessary to base the normalized tables on the logical database design, and then physically normalize the data according to the needs of the entire application system.
Both the specification and the anti-norm are the constraints based on the actual operational basis, which are not meaningful either. Only by combining the two rationally can we complement each other and play our respective advantages.

Properly split
There are times when we might want to map a complete object to a database table, which is good for application development, but sometimes it can lead to significant performance problems.

When there are large characters in our table that resemble TEXT or are of great varchar type, if we don't need this field for most of our access to this table, we should break it down into separate tables to reduce the amount of storage space used for common data. One obvious benefit of this is that the number of data bars that can be stored in each block can be greatly increased, reducing both the number of physical IO and the cache hit rate in memory significantly.

Moderately redundant
Why do we need redundancy? This does not increase the size of each piece of data, reducing the number of records per block can be stored?
Indeed, this will increase the size of each record and reduce the number of data that can be stored in each record, but we still have to do this in some scenarios:
1. Independent small sections that are frequently referenced and can only be obtained by means of a Join of 2 (or more) large tables.
2. Such a scenario, because each join is just a small segment of the value, the join to the record is large, resulting in a large number of unnecessary IO, can be in space in exchange for time to optimize the way. However, redundancy requires ensuring that data consistency is not compromised, and that redundant fields are updated as well. Third: Other skills:

1: Field type optimization
The following optimization recommendations for field types apply primarily to scenarios where the number of records is larger and the volume of data is large, as the refinement of data type settings can lead to increased maintenance costs, and excessive optimization may also cause other problems:

(1) Number type
It is not necessary to use double, not just a question of storage length, but also a question of accuracy. Similarly, a fixed-precision decimal is not recommended for use in decimal.
It is not necessary to use double, not just a question of storage length, but also a question of accuracy. Similarly, fixed-precision decimals are not recommended for use in decimal
(2) Character type
It is not necessary to use the TEXT data type, which is handled in a way that determines that his performance is less than char or varchar type. Fixed-length fields, it is recommended to use the CHAR type, the indefinite length of the field as far as possible to use VARCHAR, and only set the appropriate maximum length, rather than very arbitrary to a large maximum length limit, because of different length range, MySQL will also have the same storage processing.

(3) Time type
Use the timestamp type as much as possible because its storage space requires only half of the DATETIME type. For data types that only need to be accurate to one day, it is recommended that you use the date type because his storage space requires only 3 bytes, less than timestamp. It is not recommended to store the value of a UNIX timestamp through the int type class, because it is too intuitive to cause unnecessary trouble with maintenance and does not bring any benefits.

2: Reasonable use of the index

3: Caching mechanisms

4: Use explain to make your select query clearer

5: Use limit 1 to get a unique line

6: Try to avoid the SELECT * command

7: Use enum instead of varchar

8: Use not NULL as much as possible
The NULL type is special, and SQL is difficult to optimize. Although the MySQL null type differs from Oracle's null, it goes into the index, but if it is a composite index, then this null-type field can greatly affect the efficiency of the entire index. In addition, the processing of NULL in the index is also special and takes up additional storage space.

Many people think that NULL will save some space, so try to get NULL to save Io, but most of the time it is counterproductive, although there may be some savings in space, but there are many other optimization problems, not only to save Io, but to increase the amount of SQL Io. So try to ensure that the default value is not NULL, but also a good table structure design optimization habits.