Database Tuning Tutorial (11) Design a beautiful table

Four, design a beautiful table

The previous chapter describes how to speed up our query statements by indexing, which lets us go back to the initial stage of database development and build a table. Talk about how to design a beautiful watch.

1. Meet the three paradigms

1NF: The column of the table is atomic, non-decomposition, that is, column information, can not be decomposed, as long as the database is a relational database (Mysql/oracle/db2/informix/sysbase/sqlserver), automatically meet 1NF

Classification of databases

Relational database: Mysql/oracle/db2/informix/sysbase/sql server

Non-relational database: Object-oriented or collection

Document-oriented database: MongoDB

2NF: The record in the table is unique and satisfies 2NF, usually we design a primary key to implement. In general, the primary key must be set in the table, and generally does not contain business logic and can be set to self-grow.

3NF: There is no redundant data in the table, that is, the information of the table, if it can be deduced, it should not be a separate design of a field to store.

Redundancy can sometimes be tolerated in order to reduce the number of queries.

2. Do not have too many field numbers

A table with too large a field will cause the scan to be larger when queried, even if you do not query the corresponding field.

So, if a table is a field, the amount of information is large, but we seldom query, we can consider to put these fields, separate into a table, this way is called vertical segmentation

3. Select the appropriate field type

Select the most applicable field property when creating the table.

In general, the smaller the table in the database, the faster the query executed on it. Therefore, in order to achieve better performance when creating a table, we can set the width of the fields in the table as small as possible

As an example,

11-digit Mobile phone number

There are two types of stores that can store

Bigint, char (11). int type Max 2147483647 is not saved

Considering that bigint accounts for 20 bytes, and char (11) accounts for 11*2=22 (GBK) or 11*3=33 (utf-8), using bigint is the best.

If you find that the table's field settings are not reasonable enough to modify when you create the table?

4. Modify the table structure

If you find that the field settings for a table are not reasonable enough to modify, there are two ways to change it.

Traditional methods

First use show createtable to see how the field was created

Then you can modify it as needed.

ALTER TABLE Patent_data modify Mingcheng varchar ($) DEFAULT NULL COMMENT ' name ';

Quick Method (simple, rough and efficient)

Create a structure that is the empty table you need, turn off MySQL, and replace the frm file of the original table with the frm file that copies the newly generated table directly in the folder

Note:

The frm file is in the/data/database under the MySQL installation directory

At the end of this chapter, the next chapter will discuss how to optimize SQL statements.

Database Tuning Tutorial (11) Design a beautiful table