The optimization technology of MySQL Database

1. Rationalization of table design (follow 3NF) <3 paradigm >

• 1NF: Table columns are atomic, non-decomposed (column information cannot be decomposed, as long as the relational database automatically satisfies 1NF)
• 2NF: The records in the table are unique and meet 2NF (usually we design a primary key to implement)
• 3NF: Do not have redundant data in the table (the information in the table can be pulled out, it should not be a separate column)

In a one-to-many scenario, in order to improve efficiency, the field may be designed to improve efficiency in 1 (violation of 3NF)

2. Add the appropriate index (index) [four kinds: primary key, Unique, normal, full text]

(1) The primary key index is added: When a table is set as the primary key, the column is the key index

CREATE TABLE AAA

(id int unsigned primary key auto_increment,

Name varchar (+) NOT null default ');

The ID column is the primary key index.

If you do not specify a primary key index when you create the table, you can also add it after creation:

ALTER TABLE name add primary key (column name);

Example:

CREATE TABLE (ID int unsigned,name varchar (+) NOT null default ');

ALTER TABLE BBB add pirmary key (ID);

(2) Add a unique index:

CREATE TABLEPersons (id_pint  not NULL, LastNamevarchar(255) not NULL, FirstNamevarchar(255), Addressvarchar(255), Cityvarchar(255),UNIQUE(id_p))CREATE TABLEPersons (id_pint  not NULL UNIQUE, LastNamevarchar(255) not NULL, FirstNamevarchar(255), Addressvarchar(255), Cityvarchar(255))

(3) Ordinary index creation: the creation of a normal index is to create a table before creating a normal index.

For example: CREATE TABLE CCC (ID int unsigned,name varchar (32));

Create index index name on table (column)

(4) Creation of full-text indexing: Mainly for files, text inspection, such as articles, full-text index for the MyISAM engine is useful .

CREATE TABLE  intnot NULLUNIQUE  varchar ( text , Fulltext (title,body)) egin=MyISAM charset UTF8;

How do I use full-text indexing?

Error usage: Select *from articles where body like '%mysql% '; [This will not be used to full-text indexing]

Correct usage: Select *from articles where match (title,body) against (' database ');

Description

<1> fulltext indexes in MySQL are only valid for MyISAM.

<2> effective---->sphinx technology for English (Coreseek)

<3> How to use Match (field name) aginst (' keyword ')

<4> Full-Text indexing has a stop word, because in one text, creating an index is an infinite number, so for some common words and characters, it is not created, and these words are called stop words.

Summary: Query index command

The name of the DESC table table;

Show index (ES) from table name;

Show keys from table name;

3. Sub-table technology [horizontal split, vertical division]

(1) Horizontal split:

Example: QQ login form (assuming that the user has 500 million)

ID Name PW

1 AA AA

2 BB bb

If you validate 500 million of the data validation, this is too inefficient. Assuming that the data is stored in 3 tables, it is up to the id%3 to decide which table to put in and out. The structure of the three tables is the same as the large table.

(2) Vertical Division:

Some of the table's fields, when queried, do not care, but the amount of data is very large, we suggest to put these big pieces in a separate table, so as to improve efficiency,

However, you must remain associated with the original table.

4. Read-write (write operations including update, delete, add) separation

5. Stored Procedures (modular programming, can improve speed)

6. Configuration optimization for MySQL [Configure maximum concurrency, resize cache--->my.ini]

7.mysql Server hardware Upgrade

8. Periodically remove unwanted data and defragment it regularly.

　　　　　　

The optimization technology of MySQL Database