Database optimization tutorial (11) Design a beautiful table and database Optimization

Database optimization tutorial (11) Design a beautiful table and database Optimization
4. Design a beautiful table

The previous chapter describes how to use indexes to accelerate our query statements. This chapter allows us to go back to the initial stage of database development and create tables. Let's talk about how to design a beautiful table.

1. satisfy the three paradigms

1NF: indicates that columns in a table are atomic and cannot be decomposed. That is, column information cannot be decomposed, as long as the database is a relational database (mysql/oracle/db2/informix/sysbase/sqlserver), it automatically meets 1NF

 

Database category

Relational Database: mysql/oracle/db2/informix/sysbase/SQL server

Non-Relational Database: Object-oriented or set

Document-oriented database: MongoDB

 

2NF: the record in the table is unique, and 2NF is satisfied. Generally, we design a primary key for implementation. In general, the primary key must be set in the table, which generally does not include the business logic and can be set to auto-increment.

 

3NF: No redundant data exists in the table. That is to say, if the table information can be deduced, a field should not be designed separately for storage.

Sometimes redundancy can be tolerated to reduce the number of queries.

 

2. The number of fields should not be too large.

The fields in a table are too large, which will increase the scanning range during the query, even if you do not query the corresponding fields.

Therefore, if a field in a table contains a large amount of information but is rarely queried, you can consider placing these fields into a table separately. This method is called vertical segmentation.

 

3. Select the appropriate field type

Select the most suitable field attribute when creating a table.

Generally, the smaller the table in the database, the faster the query will be executed on it. Therefore, when creating a table, we can set the field width in the table as small as possible to achieve better performance.

For example

11-digit mobile phone number

There are two types to store

Bigint, char (11 ). Int type up to 2147483647 non-existent

Considering that Bigint occupies 20 bytes while char (11) occupies 11*2 = 22 (gbk) or 11*3 = 33 (UTF-8), it is best to use bigInt.

 

After creating a table, how can I modify the table fields when they are not properly set?

4. Modify the table structure

If you find that the table's fields are not properly set, how can you modify them? There are two methods.

Traditional Method

First, use show createtable to view how to create a field

Then you can modify it as needed.

Alter table patent_data modify mingcheng varchar (500) default null comment 'name ';

Quick Method (simple, crude, and efficient)

Create an empty table with the required structure. Close mysql and copy the frm file of the newly generated table in the folder to replace the frm file of the original table.

Note:

The Frm file is located in the/data/Database Name directory under the mysql installation directory.

This chapter ends. The next chapter describes how to optimize SQL statements.