Shulou(Shulou.com)06/01 Report--

Examples of this article describes the MYSQL database table structure optimization method. Share it for your reference, as follows:

Select the appropriate data type

Use the smallest data type that can hold your data

Use simple data types. Int is simpler than varchar in mysql processing

3. Use not null definition fields as much as possible

4, try to use text type as little as possible, it is best to consider sub-tables when it is not used

Use int to store date and time, 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 address, use INET_ATON()[convert IP format to int],INET_NTOA()[convert int format to normal IP format] two functions 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, that is, it requires that there are no non-key field pairs in the data table. Any candidate key field

The transfer function dependence of is consistent with the third normal form.

Tables that do not meet the requirements of the third normal form have the following problems:

1. Data redundancy: (classification, classification description) for each commodity will be recorded

2. Abnormal insertion/update/deletion of data

Paradigm operation:

Denormalization refers to the query efficiency and consideration of the original table in line with the third normal form of appropriate redundancy, in order to optimize the query efficiency, denormalization is a space for time operation.

Example:

Denormalize a table

De-normalization query 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 carried out according to the following principles:

1. Store infrequently used fields separately in a table.

2. Store large fields independently in a table.

Put together fields that are often used together.

horizontal split

Horizontal splitting of tables is to solve the problem of excessive data volume in a single table. The structure of each table in a horizontally split table is completely consistent.

Common horizontal splitting methods:

1. hash customer_id, if it is to be split into 5 tables, use mod(customer_id,5) to extract 0-4 values

2. Store data in different tables for different hashIDs

More about MySQL related content interested readers can view this site topic: MySQL query skills, MySQL common function collection, MySQL log operation skills collection, MySQL transaction operation skills collection, MySQL storage process skills collection and MySQL database lock related skills collection

I hope this article is helpful for MySQL database.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.