MySQL database compiled with the power of the primitive 32

This morning, I spit up the Oracle SQL performance optimization of the 40 military, many of the rules are also applicable to MySQL, the results of this evening found this article-the exhaustion of the power of the MySQL database compiled 32 rules , And I have the same wonderful, of course, the difference is that this is a 12 years experience in Java development veteran of the written, and its insights and understanding certainly more profound than mine, thanks to predecessors, is reproduced as follows:

--------------------------------------------------------------Split Line-----------------------------------------------------

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.

Core military

1, not in the database to do the operation
CPU compute 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 business: Big transaction
Reject Mass: Big Batch

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. Convert characters to numbers

storing IP with int instead of char (15)

8. Use enum or set preferentially

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  not NULLGood Case:' Age' int  not NULL default 0

10, less use Text/blob

The performance of varchar is much higher than that of text;
Can not avoid the blob, please split the table;

11, not in the database to save pictures

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 be prefixed with index

14, do not do column operation index

 Case : Select where + 1 = ten;

15, InnoDB primary key 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;

SQL class

17. 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;

19. Avoid using Trig/func

Triggers, functions not used;
client programs instead;

20. Do not 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  from where phone='159or phone='136 = Selectfrom where in ('159, '136);

22, or rewrite to union

MySQL index merge is retarded.

Select  from where = '159or= = = =+Selectfromwhere phone ='159UnionSelectfromwhere name =' Jonh '

23, to avoid negative%

24. Use COUNT (*) with caution

25. Limit Efficient Paging

The greater the limit, the less efficient

Select  from 10000 Ten ; = Select  from where > 10000 ten;

26. Use UNION ALL instead of union

The Union has a go-to-weight overhead

27. Less connection Join

28, less use GROUP by

Group
automatic sorting;

29, please use the same type comparison

30. Using the Load Data guide

Load data is about 20 times times faster than insert;

31. Batch Update

32. New Energy analysis tools

Log ; show Processlist;show query_response_time (Percona);

MySQL database compiled with the power of the primitive 32