MySQL develops 36 military records

Written in the previous words:

It is always after the disaster that the importance of disaster tolerance is remembered;

Always after the loss, just remember that there was a reminder.

(i) Core military

(1) Do not operate the database

CPU computing must be moved to the business layer;

(2) control the amount of single-table data

int no more than 1000w, including Char is not more than 500w;

Reasonable sub-table;

Limit the number of single-Library tables within 300;

(3) Number of control columns

Field few but good, the number of fields suggested within 20;

(4) Balance paradigm and redundancy

efficiency is preferred;

often sacrifice paradigm;

(5) Reject 3B

Reject large SQL statement: Big SQL

Reject big things: Big transaction

Reject Mass: Big Batch

(ii) Field class military

(6) Use a good value type

tinyint (1Byte)

smallint (2Byte)

Mediumint (3Byte)

Int (4Byte)

BigInt (8Byte)

Bad Case:int (1)/int (11)

(7) Conversion of characters to numbers

storing IP with int instead of char (15)

(8) preferential use of enum or set

For example: ' Sex ' enum (' F ', ' M ')

(9) Avoid using null fields

Null fields are difficult to query for optimization;

The index of the null field requires additional space;

Invalid compound index for null field;

Bad case:

' Name ' char (+) default null

' Age ' int is not NULL

Good case:

' Age ' int is not NULL default 0

(10) Less use of Text/blob

The performance of varchar is much higher than that of text;

Can not avoid the blob, please split the table;

(11) Do not store pictures in the database

I can't understand that!

But this is Go's experience, begging detail!.

(iii) Index class

(12) Careful and rational use of the index

Improve the query, slow down the update;

Index must not be more the better (can not add, to add the must add);

Overwrite the number of record strips too much does not fit the index, such as "gender";

(13) Character fields must have prefix index

(14) Do not perform column operations in the index

！！！ Not only the index, can not do the column operation!!!

Bad case:

Select id where age +1 = 10;

(INNODB) The primary key is recommended to use the self-increment column;

The primary key establishes the clustered index;

The primary key should not be modified;

String should not be the master key;

If you do not specify a primary key, InnoDB uses a unique and non-null value index instead;

(16) No foreign keys

Please ensure the constraint by the program;

(iv) SQL class

SQL statements as simple as possible

A SQL can only operate on one CPU;

The large statement splits the small statement, reduces the lock time;

A large SQL can block the entire library;

(18) Simple transaction

The transaction time is as short as possible;

Bad case:

Uploading picture transactions

(19) Avoid using Trig/func

Triggers, functions not used;

client programs instead;

(20) No SELECT *

Consume Cpu,io, memory, bandwidth;

This kind of procedure is not extensible;

(21) or overwrite as in ()

The efficiency of or is n level;

In message when log (n) level;

In the number of proposed control within 200;

Select ID from t where phone= ' 159′or phone= ' 136′;

=

Select ID from the t where phone in (' 159′, ' 136′);

(22) or overwrite to Union

MySQL index merge is retarded.

Select ID from t where phone = ' 159′or name = ' John ';

=

Select ID from t where phone= ' 159′

Union

Select ID from t where name= ' Jonh '

(23) Avoid negative%

(24) Use COUNT (*) with caution

(25) Ibid.

(+) Limit efficient paging

The greater the limit, the less efficient

Select ID from t limit 10000, 10;

=

Select ID from t where ID > 10000 limit 10;

(27) Use union ALL instead of union

The Union has a go-to-weight overhead

(28) Less connection join

(29) Use GROUP by

Group

automatic sorting;

(30) Please use the same type comparison

(31) Using the Load Data guide

Load data is about 20 times times faster than insert;

(32) Break up batch Update

(33) Performance analysis tools

Show profile;

Mysqlsla;

Mysqldumpslow;

Explain

Show slow log;

Show Processlist;

Show Query_response_time (Percona);

MySQL develops 36 military records