(4) mysql Optimization-Database Table Design Optimization-mysql database

(4) mysql Optimization-Database Table Design Optimization-mysql database
Overview

During the database design process, users may frequently encounter the following problems: 1. How long should the field in the table be properly set? 2. Should all tables be designed in the third paradigm?

Optimize the table data type 1. Syntax

For the table data type, see the link:
Http://blog.csdn.net/pursuing0my0dream/article/details/44729707

In mysql, we can use the procedure analyze () function to analyze tables in the database. This function can provide optimization suggestions for the Data Types of columns in the data table.

# Syntax select * from table_name procedure analyze (); # or select * from table_name procedure analyze (element_num, max_memory );

The 2nd statements tell the function not to make suggestions for the enum type containing more than element_num or max_memory bytes. Without these restrictions, the output information may be long and The enum type definition is usually hard to read.

2. Example

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

Field_name provides optimization suggestions for the table field optimal_fieldtype.

Improve table access efficiency by splitting tables

Split data tables in two ways: Vertical Split and horizontal split

1. Vertical Split (Table sharding)

Put the primary key and columns of some data tables in one table, and then put the primary key and columns of other data tables in one table.
If some columns in a table are commonly used and others are not commonly used, Vertical Split can be used. Vertical Split can reduce the number of data rows. A data page can store more data and reduce the number of I/O queries. The disadvantage is that redundant columns need to be managed and joined to query all data.

2. Horizontal Split (Table sharding and partition)

That is, columns in a data table are placed in multiple independent tables or partitions according to certain rules. Use Cases of horizontal split:

• The table is very large. After partitioning, the data to be read and the number of index pages can be reduced during query. At the same time, the number of indexes is reduced and the query speed is improved.
• The data in the table is independent. For example, the table records data in different regions or data in different periods. In particular, some data is often used, while others are not.
• Data needs to be stored on multiple storage media.

Inverse Normalization

  & Esmsp; in database design, the higher the normalization, the more relations are generated, which directly leads to the more frequent join operations between tables, the join operation is a low-performance operation that directly affects the query speed.
The advantage of semi-normalization is that it reduces the need for join operations and the number of foreign keys and indexes. It can also reduce the number of tables, which may cause data integrity problems. The query speed is faster, but the modification speed is reduced.