I. Establishment of the table of norms1, the expression is the concept of the field, you must use the Is_xxx method named, the data type is unsigned tinyint (1 means yes, 0 means no). 2, table name, field name must use lowercase letters or numbers, prohibit the beginning of the number, prohibit two underscores only the number of the middle. Database field names are expensive to modify because they cannot be pre-published, so field names need to be considered carefully. 3. The table name does not use plural nouns. 4. The primary key index name is the Pk_ field name, the unique index name is the Uk_ field name, and the normal index name is the IDX_ field name. 5, decimal type is decimal, prohibit the use of float and double (the problem of loss of precision) 6, if the stored string length is almost equal, use char fixed length string type. 7, table must be three fields: ID, gmt_create, gmt_modified.Note: Where the ID must be the primary key, the type is unsigned bigint, the single table is self-increment, the step is 1. The types of gmt_create,gmt_modified are all date_time types. 8. The fields allow for proper redundancy to improve query performance, but data consistency must be considered. Redundant fields should follow:1) fields that are not frequently modified. 2) Not a varchar super-long field, not a text field. Positive Example: the product category name uses the high frequency, the field length is short, the name basically is invariable, may in the related table redundant storage class name, avoids the correlation query9, the number of single-table rows more than 5 million lines or a single table capacity of more than 2GB, it is recommended to carry out the Sub-Library sub-table. Second, the index of the Statute 1, the business has a unique attribute of the field, even if the combination of multiple fields, you must build a unique index. 2, more than three tables prohibit join. Fields that require joins must have an absolute consistency in the data type, and a multi-table associated query guarantees that the associated field needs to be indexed. 3. When an index is established on a varchar field, the index length must be specified, it is not necessary to index the whole field, and the length of the index is determined according to the actual text sensitivity.Note: The length and sensitivity of the index is a pair of contradictions, generally for the string type of data, the length of the index of 20, the degree of differentiation will be as high as 90%, you can use the count (distinct left (column name, index length))/count (*) of the sensitivity to determine. 4, page search is strictly prohibited left fuzzy or all fuzzy, if necessary, please go search engine to solve.Description: The index file has the leftmost prefix match attribute of B-tree and cannot be used if the left value is not determined. 5, the use of delayed correlation or sub-query optimization of ultra-multi-page paging scene.Description: MySQL does not skip the offset line, but instead takes the Offset+n line, then returns the row of the previous offset, returning N rows, when offset is particularly large, the efficiency is very low, either control the total number of pages returned, or the number of pages exceeding a certain threshold to SQL Rewrite. A positive example: first quickly locate the ID segment you want to get, and then associate it with:Select a.* from Table 1 A, (select ID from table 1 where condition LIMIT 100000,20) b where A.id=b.id 6, SQL performance optimization goal: at least to reach the range level, the requirement is the ref level, if it can be consts best.Description:1) There is a maximum of one matching row (primary key or unique index) in the Consts single table, and the data can be read at the optimization stage. 2) ref refers to the use of a normal index. 3) range retrieves the index. 7. When building a combined index, the highest degree of differentiation is at the far left.Example: If where a= and b=?, a column is close to the unique value, then only the single-built Idx_a index is needed .8. If there is an order by scenario, be aware of the order of the indexes. The last field of the order by is part of the combined index, and is placed at the end of the index composition order, avoiding file_sort and affecting query performance.Example: where a= and b=? order by C; Index: A_b_cInverse: There is scope lookup in the index, then the index order can not be exploited, such as: where a>10 order by B; Index a_b cannot be sorted. 9, using the overlay index to query operations, avoid back to the table. Note: If a book needs to know What chapter one is heading, will it open the corresponding page of Chapter one? Directory browsing is good, this directory is the role of the overlay index.
Positive example: The kind of index that can be indexed: Primary key index, unique index, normal index, and overwrite index is an effect of a query, with The result of explain, the extra column appears: using index.
C. SQL statement 1, do not use COUNT (column name) or count (constant) to replace count (*), COUNT (*) is the syntax of the standard statistic row number defined by SQL92, is not related to the database, is null and non-null independent of 2, COUNT (distinct col Calculates the number of distinct rows except null for the column, and note that count (distinct col1, col2) returns to 0 if one of the columns is all NULL, even if the other column has a different value. 3. When the value of a column is all null, the return result of Count (Col) is 0, but the return result of sum (COL) is null, so use SUM () to be aware of the NPE problem.A positive example: the NPE problem of sum can be avoided by using the following method: SELECT IF (ISNULL (SUM (g)), 0,sum (g)) from table;4, when writing paging query logic in code, if count is 0 should return directly, avoid the subsequent paging statement. 5, prohibit the use of stored procedures, stored procedures are difficult to debug and expand, but not portability.
MySQL Database protocol
