Things you can't do in MySQL

Written in the preceding words: Always remember the importance of disaster recovery after the catastrophe, always after the loss, it is remembered that someone reminded

1, do not do the database operation: CPU calculation must be moved to the business layer
2, control single-table data volume: Single-table record control in 1000w
3. Number of control columns: Number of fields controlled within 20
4. Balance paradigm and redundancy: the paradigm design for improving efficiency, redundant data
5, reject 3 B: Deny big SQL, big things, large quantities

Data Sheet Design

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, use int instead of char (15) to store IP
8. Use enum or set first, ' Sex ' enum (' F ', ' M ')
9, avoid using null fields

Null field is difficult to query the index of the optimized null field requires additional space the compound index of the null field is invalid bad case: ' name ' char (+) default null ' age ' int not nullgood case: ' age ' int not nu ll default 0

10, less use Text/blob,varchar performance will be much higher than the text; I can't avoid blobs, please.
11, not in the database to save pictures

Index class

12. Careful and rational use of the index

Improve the query, slow the update index must not be more the better (can not add, must add) overwrite the number of records too much does not fit the index, such as "gender"

13. Character fields must be prefixed with index
14, do not do the index column operation, bad Case:select ID where the age +1 = 10;
15, InnoDB primary key recommended to use the self-increment column

Primary key establish clustered index primary key should not be modified string should not be a master key if you do not specify a primary key, InnoDB uses a unique and non-null value index instead

16, do not use foreign keys, please ensure the constraints by the program

SQL class

17, SQL statement as simple as possible, a SQL can only be in a CPU operation, large statement to split small statements, reduce lock time, a large SQL can block the entire library
18, simple transaction, transaction time as short as possible, bad case: Upload picture transaction
19, avoid using trig/func, trigger, function not, client program instead
20, without select *, consumption of cpu,io, memory, bandwidth, this program 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 recommended 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 rewrite 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, to avoid negative%
24. Use COUNT (*) with caution
25. Ibid.
26. 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. Batch Update
33. New Energy analysis tools
Show profile;
Mysqlsla;
Mysqldumpslow;
Explain
Show slow log;
Show Processlist;
Show Query_response_time (Percona)

Things you can't do in MySQL