(4) Optimization of database table design for MySQL optimization

Overview

During the database design process, users may often encounter problems such as: 1. How long is the field in the table appropriate for this setting? 2. Should all tables be designed according to the third paradigm?

optimizing the data type of a table 1. Syntax

Data types for tables see links:
http://blog.csdn.net/pursuing0my0dream/article/details/44729707

In MySQL, we can use the Function procedure analyse () to parse a table in a database, which can make recommendations for optimizing the data types of columns in a data table.

#语法select * from table_name procedure analyse();#或select * from table_name procedure analyse(element_num,max_memory);

The 2nd statement tells the function not to make recommendations for enum types that contain more than Element_num or max_memory bytes. Without these restrictions, the output information can be long, and the enum type definition is often difficult to read.

2. For example

SELECT * FROM c PROCEDURE ANALYSE(16,255);

Field_name for which field of the table, Optimal_fieldtype gives an optimization suggestion.

improve the efficiency of table access by splitting tables

Split the data table into two types of split: Vertical split, horizontal split

1. Vertical Split (sub-table)

That is, place the primary key and the columns of some data tables in a table, and then place the primary key and the columns of the other data tables in a table.
If some columns of a table are commonly used, and others are not used, you can split vertically. Vertical splitting can make data rows smaller, a data page can hold more data, and can reduce I/o times when querying. The disadvantage is that redundant columns need to be managed and join lookups are required when querying all data.

2. Horizontal Split (sub-table, partition)

That is, the columns in the data table are placed in a number of separate tables or partitions according to certain rules. To split the usage scene horizontally:

• The table is large, after the partition can reduce the query time to read the data and index pages, while reducing the number of index layers, improve query speed.
• The data in the table is independent, for example, the data in each region is recorded in the table, or the data of different periods, especially some data is commonly used, while others are not used.
• Data needs to be placed on multiple storage media.

Inverse Normalization

?&esmsp; in the database design, the higher the normalization, the more relations it produces, which leads directly to the more frequent join operations between tables and tables, and the join operation is a low performance operation that directly affects the speed of the query.
?? The advantage of anti-normalization is that it reduces the need for join operations and reduces the number of foreign keys and indexes. You can also reduce the number of tables, the corresponding problem is the possibility of data integrity issues. Speed up queries, but decrease the speed of modifications.

(4) Optimization of database table design for MySQL optimization