MySQL database optimized database structure optimization

Data structure optimization

One, select the format of the data type

The choice of data type, focusing on the appropriate two words, how to determine whether the selected data type is appropriate?
1. Use the smallest data type that can save your data.
2, using a simple data type, int is more simple than the varchar type in MySQL processing;
3, use NOT NULL to define the field as far as possible;
4, try to use less text type, non-use when it is best to consider the sub-table.
Storing time types using int type
From_unixtime (), Unix_timestamp () timestamp conversion
Using bigint to store IP addresses
Inet_aton (' 192.168.132.1 '), Inet_nton ()

Second, the normalization and optimization of the table, the inverse normalization of the optimization

Three, the vertical split of the table:
The so-called vertical split, that is, a lot of columns of the original table is split into multiple tables, which solves the table width problem;
The principle of splitting:
1. Place infrequently used fields in a single table.
2. Place large characters in a single table.
3. Put together the fields that you frequently use together.

Iv. horizontal split of the table
The horizontal split of the table is to solve the problem that the data volume of the single table is too large, and the horizontal split table structure is consistent.

MySQL database optimized database structure optimization