Reprint Address: http://blog.itpub.net/22664653/viewspace-723506/written in front of the words: Always in the aftermath of the disaster, only remember the importance of disaster tolerance, always after eating a loss, just remember that someone had warned. (a) core military military (1) do not operate the database  CPU calculation must be moved to the business layer, (2) control of single-table data volume int type not more than 1000w, including Char is not more than 500w; reasonable sub-table; Limit the number of single-Library tables is less than 300, (3) The number of control columns field few but good, the number of fields is recommended within 20, (4) Balance paradigm and redundancy efficiency priority; often sacrifice paradigm; 5) Reject 3b reject large SQL statement: Big sql Reject big thing: large transaction reject mass: Big batch (ii) Field class military (6) Good value type tinyint (1Byte) smallint (2Byte) mediumint (3Byte) int (4Byte) bigint (8Byte) bad case:int (1)/int (11) (7) character conversion to digital using int instead of char (15) Storage IP (8) Prefer enum or set For example: ' Sex ' enum (' F ', ' M ') (9) Avoid using null fields null fields It is difficult to query the optimization; null field index requires additional space ; null field Compound index is invalid; bad case: ' name ' char (+) Default null ' age ' int not nu ll good case: ' age ' int not null default 0 (10) less text/blob varcharPerformance will be much higher than text; can not avoid blob, please open the table; (11) Do not store images in the database I can't understand that! But this is Go's experience, begging detail!. (iii) Index class military (12) Use index improve query, slow update; Index must not be the more the better (can not add, to add it must add); Overwrite the number of records too much does not fit the index, such as "gender"; (13) The character field must be prefixed with an index (14) Not indexed for column operations!!! Not only the index, can not do the column operation!!! Bad case: Select id where age +1 = ten; (InnoDB) The primary key is recommended to use the self-increment column; PRIMARY key to build a 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 will use a unique and non-null value index instead; (16) No foreign key Please ensure constraint by program; (iv) SQL class Military (+) SQL statements as simple as possible A SQL can only be in one CPU operation; LARGE statement split small statement, reduce lock time; A large SQL can block the entire library; (18) Simple transaction Transaction time as short as possible; bad case: uploading picture transactions (19) Avoid using trig/func triggers, functions without; Guest (20) Do not use Select * consume Cpu,io, memory, bandwidth; This program is not extensible; () or Overwrite to in () or efficiency is n level; in message when log (n) level; in is recommended to control within 200; Select ID from t where phone= ' 159′or Phon E= ' 136′;  => Select ID from t where phone in (' 159′, ' 136′);() or rewritten as union MySQL cable Draw merge very mentally select ID from t where phone = ' 159′or name = ' John '; => &NB Sp;select ID from t where phone= ' 159′ union select ID from t where name= ' Jonh ' (23) avoids Negative% (24) Use COUNT (*) (25) as above (+) limit efficient paging The higher the efficiency of the lower Select ID from t limit 10000, 10;   ; => Select ID from t where ID > 10000 limit 10; (27) Use UNION ALL instead of union Union has a de-heavy overhead (28) Less connection Joi N (29) use Group by Group; Auto Sort, (30) Use the same type comparison (31) using the Load Data Guide data Load data is about 20 times times faster than insert; ( 32) Break Up batch update (33) Profiling Tools Show profile; mysqlsla; mysqldumpslow; explain; Show Slow log; show processlist; show Query_response_time (Percona); accessories:
MySQL Development 36 military rule
