MySQL usage Specification

MySQL usage specification 1. Core Specification www.2cto.com 1. database operations are not required, such as md5 (), order by rand () 2. control the data volume of a single table a) a single table with a pure int value of no more than 1000 w B) a single table with a char value of no more than 500 w c) a single database with no more than-Tables 3. the number of table fields is small, while the number of fields is fine. a) influencing factors I. IO efficiency ii. full table traversal iii. quick table repair iv. increase concurrency v. alter table fast B) the maximum number of fields in a single table is 20-50. reject large SQL statements, large transactions, large batches 2. Field standards www.2cto.com 1. use the numeric field type a) TINYINT (1 Byte) B) SMALLINT (2B) c) MEDIUMINT (3B) d) INT (4B), BIGINT (8B) e) FLOAT (4B), DOUBLE (8B) f) DECIMAL (M, D) 2. use numeric fields as much as possible, not characters String type a) more efficient B) Faster query c) less space occupied 3. enum or set a) Enum occupies 1 byte, and is converted to numerical operation B) Set as node order, up to 8 bytes c) single quotation marks are required for comparison. avoid using NULL field a) difficult to query and optimize B) adding an index to the NULL column requires extra space c) If NULL is included, the index is invalid. use less or split TEXT/BLOB a) the processing performance of the Text type is much lower than that of varchar B. If necessary, split the data to a separate table 6. do not store images in the database. 3. Index specifications. 1. exercise caution when adding an index. a) Improve the query, slow down the update, and the index is not more or better. B. Try not to add an index without adding it. It is best not to exceed the number of fields by 20%. the character field must be indexed with a prefix. a) the differentiation of Mysq is equal to the growth of 3. does not perform mathematical operations or function operations on index columns. a) index B cannot be used. 4. Full table scan. auto-increment column or global id as the innodb primary key 5. try not to use foreign key) Foreign keys can save development workload B) additional overhead c) Row-by-Row Operations d) can reach other tables, meaning lock e) High concurrency easy deadlock 4. SQL specification 1. reject large SQL statements and split them into multiple simple SQL statements. a) a higher cache hit rate for simple SQL statements. B. Reduce the lock table time, especially for myisam c. Use multiple CPUs. 2. keep transactions and db connections short and concise a) Principle of use: out-of-the-box, close once used B) Put transaction-independent operations out of the transaction, reduce the occupation of lock resources c) use multiple short transactions instead of long transactions without disrupting consistency. avoid using stored procedures, triggers, and functions as much as possible. select * is not required. Only the required data column 5 is used. change or to in a) Or efficiency: O (n) B) In efficiency: O (log n) c) WHEN N is large, or will be much slower, we recommend that n of in be less than 200 6. if different fields exist, rewrite or to union 7. avoid negative query and % prefix fuzzy a) Not ,! =, <>, Etc. B) if the index cannot be used for prefix fuzzy search, the entire table will be scanned 8. reduce count a) Count (1) = count (*) full table scan B) Real-time Data: Use memcache, two-way update, baseline for early morning run c) non-real-time statistics: use a separate statistical table as far as possible, and calculate 9 on a regular basis. limt efficient paging a) a larger Limit offset slows down B) recommended paging: select * from table where id> id limit 10; 10. union all rather than union a) If deduplication is not required for the result, union all is used, and union has the deduplicated overhead 11. break down the link to ensure high concurrency a) High concurrency db is not recommended to join more than two tables 12. group by remove sort a) the Group by implementation includes: grouping, sorting B) No sorting: order by null c) Specific sorting: group by desc 13. make sure that the column values of the same data type are compared as much as possible. a) the value column in the character type comparison I. convert to dual-precision ii at the same time. b) character column and value type comparison I. entire character column to numeric value ii. you do not need to use the index to query 14. load data a) Batch loading is faster than a single row without refreshing the cache B) No index is faster than index loading c) Try not to insert select 15. decompress batch update 16. common db detection commands a) Show profile B) Mysqlsla c) Mysqldumpslow d) Show slow log e) Show processlist f) Show query_response_time (percona) g) explain 17. try to use connection instead of subquery 18. unified Character Set: utf8, proofreading rule: utf8_general_ci