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 databaseCPU computing must be moved to the business layer;(2) control the amount of single-table dataint 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 columnsfield Few but good, the number of fields suggested within 20;(4) Balance paradigm and redundancyefficiency is preferred;often sacrifice paradigm;(5) Reject 3Breject large SQL statement: Big SQLreject big things: Big transactionreject Mass: Big Batch (ii) field class military(6) Use a good value typetinyint (1Byte)smallint (2Byte)mediumint (3Byte)Int (4Byte)bigint (8Byte)Bad case:int (1)/int (one)(7) Conversion of characters to numbersstoring IP with int instead of char (15)(8) preferential use of enum or setFor example: ' Sex ' enum (' F ', ' M ')(9) Avoid using null fieldsnull 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 nullGood case:' age ' int is not NULL default 0(10) Less use of Text/blobthe 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 databaseI can't understand that! But this is Go's experience, begging detail!. (iii) Index class(12) careful and rational use of the indeximprove 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 = ten;(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 keysPlease ensure the constraint by the program; (iv) SQL classSQL statements as simple as possiblea 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 transactionThe transaction time is as short as possible;Bad case :Uploading picture Transactions(19) Avoid using Trig/functriggers, functions not used;client programs instead;(20) No SELECT *consume Cpu,io, memory, bandwidth;This kind of procedure is not extensible;(+) or rewritten 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′);() or rewrite to unionMySQL index merge is retarded.Select ID from t where phone = ' 159′or name = ' John ';=Select ID from t where phone= ' 159′UnionSelect ID from t where name= ' Jonh '(23) Avoid negative%(24) Use COUNT (*) with caution(25) Ibid .(+) limit efficient pagingthe greater the limit, the less efficientselect ID from t limit 10000, ten;=Select ID from t where ID > 10000 limit;(27) Use union ALL instead of unionThe union has a go-to-weight overhead(28) Less connection join(29) use Group byGroup;automatic sorting;(30) Please use the same type comparison(31) using the Load Data guideload data is about 20 times times faster than insert;(32) Break up batch update(33) New Energy analysis toolsshow profile;Mysqlsla;Mysqldumpslow;explain;show slow log;show Processlist;show Query_response_time (Percona);
Go MySQL develops 36 military records
