Detailed explanation of MYSQL database table structure optimization method

This article uses an example to illustrate the method of optimizing the MYSQL database table structure. Share with you for your reference, the details are as follows:

Choose the right data type

1. Use the smallest data type that can hold your data

2. Use simple data types. Int is easier to process in MySQL than varchar type.

3. Use not null to define fields as much as possible

4. Use text type as little as possible. If you have to use it, it is best to consider splitting the table

Use int to store date and time, and use FROM_UNIXTIME() [Convert int type timestamp to date and time format], UNIX_TIMESTAMP() [Convert date and time format to int type] to convert

Use bigint to store IP addresses, and use INET_ATON() [Convert IP format to int], INET_NTOA() [Convert int format to normal IP format] to convert

Normalization and denormalization of tables

Normalization refers to the specification of database design. The current normalization generally refers to the third design paradigm, which requires that there are no non-key fields in the data table for any candidate key fields.

The transfer function dependency conforms to the third normal form.

Tables that do not conform to the third normal form have the following problems:

1. Data redundancy: (classification, classification description) records will be kept for each product

2. Data insertion/update/deletion exceptions

Normalized operation:

Denormalization means to increase redundancy appropriately for tables that originally conform to the third normal form for the sake of query efficiency and considerations, so as to achieve the purpose of optimizing query efficiency. Denormalization is an operation that exchanges space for time.

example:

Denormalize the table

Denormalize order information:

Vertical split of table

Vertical splitting is to split the original table with many columns into multiple tables, which solves the problem of table width. Usually vertical splitting can be done according to the following principles:

1. Store infrequently used fields in a separate table.

2. Store large fields independently in a table.

3. Put fields that are often used together.

Horizontal Split

The purpose of horizontal splitting of tables is to solve the problem of too much data in a single table. The structure of each table in the horizontal split is exactly the same.

Commonly used horizontal splitting methods:

1. Perform a hash operation on customer_id. If you want to split it into 5 tables, use mod(custoneer_id,5) to extract 0-4 values.

2. Store data in different tables for different hashIDs

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL common functions summary", "MySQL log operation skills", "MySQL transaction operation skills summary", "MySQL stored procedure skills" and "MySQL database lock related skills summary"

I hope this article will be helpful to everyone's MySQL database design.