编程知识 cdmana.com

MySQL specification that Java developers must know

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 .


Make a schedule


【 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


Reading :

  • 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


Reading :
  • 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 .


【 Suggest 】:⑫ Watch out for automatically generated Schema, Suggest all of Schema Write by hand


Reading : Don't trust too much for some database clients .

SQL Statute


【 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

Reading :
  • Additional columns are read that are not needed CPU、IO、NET Consume .

  • Coverage indexes cannot be effectively utilized .


【 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


Reading : 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


Reading : 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


Reading : You can choose according to the situation UNION ALL Instead of OR.

【 mandatory 】:⑦ Not allowed % The fuzzy query at the beginning


Reading : 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 .


Index specifications


【 Suggest 】:① Avoid frequent updates 、 A separate index is built on the column with low discrimination


Reading : 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


Reading : 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


Reading : 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


Reading : 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 .


Reading :
  • 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 .


【 Suggest 】:⑧ stay Where Columns indexed in cannot be part of an expression , It can't be an argument to a function


Reading : 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


Reading : 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


Reading : 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 .


Reference material :
  • 《High.Performance.MySQL.3rd.Edition》

  • 《 Alibaba java Development Manual 》


author : Mine

edit : Tao Jialong

Source :https://juejin.im/post/6871969929365553165


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 support@oschina.cn Delete .
Participation of this paper “OSC Source creation plan ”, You are welcome to join us , share .

版权声明
本文为[leechence]所创,转载请带上原文链接,感谢

Scroll to Top