The specification has been implemented in the whole backend for more than half a year , For the whole team, reduce inappropriate table building statements in the development phase 、 error SQL、 Wrong index has positive meaning , So share it for your reference .
The following is divided into table creation protocol 、SQL Statute 、 Index specification has three parts , Every clause in every part has a compulsion 、 Suggest two levels , When you refer to it , Weigh up according to the situation of your company .
【 mandatory 】：① The storage engine must use InnoDB
Reading ：InnoDB Support things 、 Row-level locks 、 Better concurrency ,CPU And memory cache page optimization for higher resource utilization .
【 mandatory 】：② Each table must have a primary key set ID, And this primary key ID Use auto increment primary key （ Try to be as short as possible to meet the needs ）, Except in the sub database and sub table environment
Reading ： because InnoDB The way you organize your data requires a primary key , And if it's the primary key ID It is monotonically increasing and can effectively improve the performance of insertion , Avoid too many page splits 、 Reduce table fragmentation and improve space utilization .
And in the sub database and sub table environment , The primary key values in each table need to be assigned uniformly , So as to avoid the duplication of primary keys in the whole logical table .
【 mandatory 】：③ You have to use utf8mb4 Character set
Reading ： stay MySQL Medium UTF-8 Is not “ real UTF-8”, and utf8mb4” It's real “UTF-8”.
【 mandatory 】：④ Database table 、 Table fields must be annotated in Chinese
Reading ： Don't be lazy .
【 mandatory 】：⑤ Library name 、 Table name 、 Field names are all lowercase , Underline style , No more than 32 Characters , You must know what you mean , Pinyin is not allowed to be used in English
Reading ： Appointment .
【 mandatory 】：⑥ The number of single table columns must be less than 30, If more than, you should consider splitting the table
Reading ： Too many columns in a single table make MySQL The server processes InnoDB The cost of mapping between returned data is too high .
【 mandatory 】：⑦ Foreign keys are not allowed , If there is a foreign key integrity constraint , Application control is required
Reading ： Foreign keys cause coupling between tables ,UPDATE And DELETE All operations involve the associated tables , Very affected SQL Performance of , It can even cause a deadlock .
【 mandatory 】：⑧ The field must be defined as NOT NULL And provide default values
NULL The column of / The index statistics / Value comparisons are more complex , Yes MySQL Harder to optimize .
NULL This type of MySQL Special handling is required internally , Increases the complexity of database processing records ; Under the same conditions , When there are more empty fields in the table , The processing performance of the database can be significantly reduced .
NULL Values require more storage space , Either in the table or in each row of the index NULL Each of the following columns requires additional space for identification .
【 mandatory 】：⑨ Disable reserved words , Such as DESC、RANGE、MARCH etc.
Reading ： Please refer to MySQL Official reserved word .
【 mandatory 】：⑩ If the stored string length is almost equal , Use CHAR Fixed length string type
Reading ： Can reduce space debris , Save storage space .
【 Suggest 】：
In some scenarios , Consider using TIMESTAMP Instead of DATETIME
【 Suggest 】：⑫ Watch out for automatically generated Schema, Suggest all of Schema Write by hand
Both types can express "yyyy-MM-dd HH:mm:ss" Format time ,TIMESTAMP Just occupy 4 The length of bytes , The range that can be stored is （1970-2038） year , In all time zones , The time shown is different .
and DATETIME Type take up 8 Bytes , Not sensitive to time zones , The range that can be stored is （1001-9999） year .
Don't trust too much for some database clients .
【 Suggest 】：① To make the most of the cache , Custom functions are not allowed 、 Storage function 、 User variables
Reading ： If the query contains any user-defined functions 、 Storage function 、 User variables 、 A temporary table 、MySQL The system table in the library , Its query results will not be cached .
Like functions NOW() perhaps CURRENT_DATE() Because of different query time , Return different query results .
【 mandatory 】：② Specify the desired columns in the query , Instead of using it directly “ *” Return all columns
【 mandatory 】：③ Implicit conversion of properties is not allowed
Reading ： Suppose we add an index to the phone number column , Then execute the following SQL What's going to happen ？
explain SELECT user_name FROM parent WHERE phone=13812345678; Obviously, the index doesn't work , Will scan the whole table .
【 Suggest 】：④ stay WHERE A function or expression is used on a property of a condition
MySQL This expression cannot be parsed automatically , The index cannot be used .
【 mandatory 】：
⑤ Foreign keys and cascading are prohibited , All foreign key concepts must be solved in the application layer
Foreign key and cascade update are suitable for single machine low concurrency , Not suitable for distributed 、 High concurrency cluster ; Cascading updates are strong blocking , There is a risk of a database update storm ; Foreign keys affect the insertion speed of the database .
【 Suggest 】：⑥ Should try to avoid in WHERE Used in clauses or As a condition of connection
You can choose according to the situation UNION ALL Instead of OR.
【 mandatory 】：⑦ Not allowed % The fuzzy query at the beginning
According to the leftmost prefix principle of index ,% Index cannot be used for the beginning fuzzy query , have access to ES To do a search .
【 Suggest 】：① Avoid frequent updates 、 A separate index is built on the column with low discrimination
The optimization effect of creating index separately for columns with low discrimination is very small , But more frequent updates will make the maintenance cost of the index higher .
【 mandatory 】：②JOIN More than five tables are not allowed . need JOIN Field of , Data types must be absolutely consistent ; When multi table associated query , Ensure that the associated field needs to have an index
Too many watches JOIN Will make MySQL The optimizer is more difficult to balance out “ The best ” Implementation plan of （ The possibility is the factorial of the table number ）, At the same time, pay attention to the types of associated fields 、 length 、 Whether the character encoding and so on are consistent .
【 mandatory 】：③ In a federated index , If the index discrimination of the first column is equal to 1, Then there is no need to build a federated index
Index data that can be fully located through the first column , So the back part of the federated index is not needed .
【 mandatory 】：④ When building a federated index , Fields with higher discrimination must be placed on the left
The more discriminative columns are on the left , It can effectively filter out useless data in the beginning . Improve the efficiency of indexing , Accordingly, we are in Mapper Written in SQL Of WHERE When there are more than one condition in a condition , You need to see if the current table has a ready-made federated index to use directly , Note that the order of the conditions should be consistent with the order of the index .
【 Suggest 】：⑤ Use overlay index to query , Avoid returning to your watch
Reading ： An overlay query is a query that only needs to be indexed to get what it needs DATA, There is no need to go back to the table again , So the efficiency is relatively high .
We are using EXPLAIN Result ,extra Columns appear ："using index". It's also important to emphasize that don't use “SELECT * ”, Otherwise, it is almost impossible to use the overlay index .
【 Suggest 】：⑥ In the longer VARCHAR Field , for example VARCHAR(100) When building an index on , Index length should be specified , There's no need to index all fields , Determine the index length according to the actual text differentiation
Reading ： Index length and differentiation are a pair of contradictions , Generally for string type data , If the length is 20 The index of , The distinction will be as high as 90% above , You can consider creating a length, such as 20 The index of , Instead of a full field index .
For example, you can use SELECT COUNT(DISTINCT LEFT(lesson_code, 20))/COUNT(*) FROM lesson; To make sure lesson_code Field character length is 20 When text distinguishes .
【 Suggest 】：⑦ If there is ORDER BY Scene , Note the use of index order
ORDER BY The last field is part of the federated index , And put it at the end of the index combination order , Avoid file_sort The situation of , Affect query performance .
【 Suggest 】：⑧ stay Where Columns indexed in cannot be part of an expression , It can't be an argument to a function
Suppose the query condition is WHERE a=? and b=? ORDER BY c; There is an index ：a_b_c, Then you can sort by index .
Counter example ： Range query is included in the query criteria , Then index order cannot be utilized , Such as ：WHERE a>10 ORDER BY b; Indexes a_b Cannot sort .
That is, an index has been added to a column , But if this column becomes part of the expression 、 Or the parameters of a function ,MySQL You can't parse this column alone , The index doesn't work either .
【 Suggest 】：⑨ We are Where When range query is used in the condition , The index can be used for at most one range condition , If there is more than one index, the index will not follow
MySQL Can use the left most first range query in multiple range conditions , But the following range query cannot be used .
【 Suggest 】：⑩ When multiple tables are externally joined , The types of associated fields between tables must be exactly the same
When two tables do Join when , If the field type is not exactly the same , Then indexing doesn't work , The exact consistency here includes, but is not limited to, field types 、 Field length 、 Character set 、Collection wait .
author ： Mine
edit ： Tao Jialong
More good articles, please pay attention to the official account
This article is from WeChat official account. - JAVA Developer class （leechence）.
If there is any infringement , Please contact the [email protected] Delete .
Participation of this paper “OSC Source creation plan ”, You are welcome to join us , share .