MySQL Database structure optimization

1. Select the appropriate data type

First, select the data type that can save the smallest data type

Second, you can use a simple data type. int is simpler than varchar in MySQL processing

Third, use NOT NULL to define fields whenever possible

Four, to minimize the use of the TXT type, non-use time to consider the table.

V. Examples:

Use the int type to store date time, use From_unixtime (), Unix_timeseamp () two function conversions

　　

Mysql> SELECT from_unixtime (1234567890, '%y-%m-%d%h:%i:%s '); +------------------------------------------------+ | From_unixtime (1234567890, '%y-%m-%d%h:%i:%s ') |+------------------------------------------------+| 2009-02-14 07:31:30                            |+------------------------------------------------+1 row in Set (0.00 sec) Mysql> SELECT Unix_timestamp (2009-02-14-07:31:30); Error 1064 (42000): You have a error in your SQL syntax; Check the manual-corresponds to your MySQL server version for the right syntax-use-near ' 07:31:30 ' in line 1mysql > SELECT unix_timestamp (' 2009-02-14 07:31:30 '); +---------------------------------------+| Unix_timestamp (' 2009-02-14 07:31:30 ') |+---------------------------------------+|                            1234567890 |+---------------------------------------+1 row in Set (0.00 sec) mysql>

　　

Use bigint to store IP addresses, use Inet_aton (), Inet_ntoa () two function conversions

　　　　

Mysql> Select    Inet_aton (' 192.168.1.200 '); +----------------------------+| Inet_aton (' 192.168.1.200 ') |+---- ------------------------+|                 3232235976 |+----------------------------+1 row in Set (0.00 sec) mysql> Select Inet_ntoa (3232235976); +------------- ----------+| Inet_ntoa (3232235976) |+-----------------------+| 192.168.1.200         |+-----------------------+1 row in Set (0.00 sec)

2. Paradigm Optimization of the table

First, the standard of the paradigm design (in line with the requirements of the three paradigms)

A: In the first paradigm, each attribute cannot be split.

B: The second paradigm, which contains the primary key.

C: You cannot have a transitive dependency on a non-keyword, and you must rely on the primary key.

Paradigm can avoid data redundancy, reduce database space, and alleviate the hassle of maintaining data integrity.

Second, the counter-normalization of the table

The inverse paradigm of the table refers to the consideration of the efficiency of the query. The table that originally conforms to the third normal form properly increases redundancy and has reached the goal of optimizing the query. Inverse paradigm is an operation that takes space in exchange for time.

Example: If we are now working on a school's curriculum, there are now two tables, one is the student information student (A_ID,A_NAME,A_ADRESS,B_ID) table, one is the curriculum subject (B_id,b_subject) , now we need a message to lose the course name and name of the student who chooses each course:

The SQL statement is: Select B.b_id,b.b_subject,a_a_name from student A, subject B;

When the above data volume is not a long time, we do so to query no problem, when our two tables of data are in millions, we go to the above information, the problem arises, this query is hundreds of milliseconds, or even slower, such query efficiency simply can not meet our requirements for the speed of the page. We can do this by adding a redundant field to the curriculum-the student's name-so that we can achieve the same goal with the following query:

The SQL statement is: Select b_id,b_subject,a_name from subject B;

The results of this implementation will be much more efficient. You can try it with a SQL query.

3. Vertical splitting of the table

The vertical split of a standard means that a table with many columns is split into multiple tables, which solves the table's width problem. The principle of splitting is as follows:

A: Less common fields are placed in a table

B: Put the big print in a single table

C: Place frequently used fields in a table

4. Horizontal split of the table

The horizontal split of the table is to solve the problem of large amounts of data in the table, and each table structure of a horizontally split table is exactly the same.

First, according to the business attributes to split the table

If you want to query based on other criteria, the other conditions must be associated with the property, and the query condition must have this attribute if the business relationship is complicated. The algorithm of this sort of table is roughly modulo, hash,md5 and so on.

Second, according to time to split the table

When a table's relationship is more complex, it cannot be divided by a dimension. But there is a clear timeliness.

Third, according to the self-growth ID to split the table

This segmentation method is not to take the modulus, but to save the specified amount of data per table. If the amount of data is reached, it is stored in the new table. This gives you complete control over the amount of data per table. The relationship is very simple and can be used in case of timeliness.

Iv. how data is migrated

When some long ago the data, rarely again query. For example, we can only save the salary of this year. Historical data can be migrated to a Salary_old table to ensure that the data is not lost. But it can also be used to query.

MySQL Database structure optimization