MySQL database Protocol

MySQL database Protocol
I. Table creation conventions1. fields that express the concept of "yes" or "no" must be named in the form of is_xxx. The data type is unsigned tinyint (1 indicates yes, 0 indicates no ). 2. The table name and field name must contain lowercase letters or numbers. Do not start with a number. Do not use only numbers in the middle of two underscores. The modification of database Field names is costly because pre-release is not possible, so the field names must be carefully considered. 3. Table names do not use plural nouns. 4. The primary key index name is pk_field name, the unique index name is uk _ field name, and the common index name is idx _ field name. 5. the decimal type is decimal. float and double are not allowed. 6. If the length of the stored string is almost the same, the char fixed length string type is used. 7. The table has three required fields: id, gmt_create, and gmt_modified. Note: The id must be the primary key. The type is unsigned bigint, auto-increment in a single table, and the step size is 1. Gmt_create and gmt_modified are of the date_time type. 8. fields allow proper redundancy to improve query performance, but data consistency must be considered. Redundant fields should follow: 1) fields that are not frequently modified. 2) It is not a varchar extra-long field, not a text field. Positive example: the product category name is frequently used, the field length is short, and the name is basically the same. The redundant storage category name can be used in the associated table, avoid join query 9. If the number of rows in a single table exceeds 5 million or the size of a single table exceeds 2 GB, we recommend that you perform database/table sharding. Ii. Index specification 1. Fields with unique business characteristics, even a combination of multiple fields, must also build a unique index. 2. join is prohibited for more than three tables. Fields to be joined must be of the same data type. When performing multi-table join queries, ensure that the associated fields need to be indexed. 3. When creating an index on the varchar field, you must specify the index length. You do not need to create an index on the entire field. You can determine the index length based on the actual text discrimination. Note: The index length and differentiation are a pair of contradictions. Generally, for string-type data, the index length is 20, and the differentiation will be as high as 90% or more. You can use count (distinct left (column name, index length)/count (*) differentiation to determine. 4. Left or Full fuzzy search is strictly prohibited for page search. If necessary, use the search engine. Note: The index file has the leftmost prefix matching feature of B-Tree. If the value on the left is not determined, this index cannot be used. 5. Optimize the multi-page scenario by using latency association or subquery. Note: MySQL does not skip the offset line, but takes the offset + N rows, and then returns the offset line, and returns N rows. When the offset is very large, the efficiency is very low, either controlling the total number of returned pages or modifying the number of pages that exceed the specific threshold. Positive example: First quickly locate the id segment to be obtained, and then associate: SELECT. * FROM table 1 a, (select id from table 1 where condition LIMIT 100000,20) B where. id = B. id 6. SQL Performance optimization goals: at least the range level should be reached, which must be at the ref level. If it can be the best consts. Note: 1) a single consts table can have at most one matching row (primary key or unique index). Data can be read during the optimization phase. 2) ref refers to the use of normal indexes ). 3) range searches for the index range. 7. When you create a composite index, the highest degree of discrimination is on the leftmost. Positive example: If where a =? And B =?, Column a is almost similar to a unique value, so you only need to create an idx_a index. If order by exists, pay attention to the order of indexes. The final field of order by is part of the composite index, which is placed at the end of the composite order of the index to avoid the case of file_sort and affect the query performance. Positive example: where a =? And B =? Order by c; index: a_ B _c inverse example: if the index has a range, the index ORDER cannot be used, for example, WHERE a> 10 order BY B; the index a_ B cannot be sorted. 9. Overwrite indexes are used for query operations to avoid returning to the table. Note: If you need to know the title of Chapter 11th in a book, will the page corresponding to Chapter 11th be opened? Just browse the directory. This directory is used to overwrite the index.
Positive example: the types of indexes that can be created: primary key index, unique index, and common index. Overwriting index is a query effect. The extra column will display the following results using the explain results: using index.

Iii. SQL statement 1. Do not use count (column name) or count (constant) to replace count (*). count (*) is the syntax of the standard number of statistics rows defined by SQL92, it has nothing to do with the database. It has nothing to do with NULL and non-NULL. 2. count (distinct col) calculates the number of non-repeated rows in this column except NULL. Note that count (distinct col1, col2) if all columns are NULL, 0 is returned even if the other column has different values. 3. When the values of a column are all NULL, the return result of count (col) is 0, but the return result of sum (col) is NULL, so sum () is used () pay attention to NPE issues. Positive example: You can use the following method to avoid the NPE problem of sum: select if (ISNULL (SUM (g), 0, SUM (g) FROM table; 4. When writing paging query logic in the Code, if count is 0, return directly to avoid executing the following paging statement. 5. Prohibit the use of stored procedures. It is difficult to debug and expand stored procedures, and there is no portability.