MySQL specification that Java developers must know

leechence 2020-11-10 19:10:13
mysql specification java developers 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]所创,转载请带上原文链接,感谢

  1. 【计算机网络 12(1),尚学堂马士兵Java视频教程
  2. 【程序猿历程,史上最全的Java面试题集锦在这里
  3. 【程序猿历程(1),Javaweb视频教程百度云
  4. Notes on MySQL 45 lectures (1-7)
  5. [computer network 12 (1), Shang Xuetang Ma soldier java video tutorial
  6. The most complete collection of Java interview questions in history is here
  7. [process of program ape (1), JavaWeb video tutorial, baidu cloud
  8. Notes on MySQL 45 lectures (1-7)
  9. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  10. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  11. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  12. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  13. 【递归,Java传智播客笔记
  14. [recursion, Java intelligence podcast notes
  15. [adhere to painting for 386 days] the beginning of spring of 24 solar terms
  16. K8S系列第八篇(Service、EndPoints以及高可用kubeadm部署)
  17. K8s Series Part 8 (service, endpoints and high availability kubeadm deployment)
  18. 【重识 HTML (3),350道Java面试真题分享
  19. 【重识 HTML (2),Java并发编程必会的多线程你竟然还不会
  20. 【重识 HTML (1),二本Java小菜鸟4面字节跳动被秒成渣渣
  21. [re recognize HTML (3) and share 350 real Java interview questions
  22. [re recognize HTML (2). Multithreading is a must for Java Concurrent Programming. How dare you not
  23. [re recognize HTML (1), two Java rookies' 4-sided bytes beat and become slag in seconds
  24. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  25. RPC 1: how to develop RPC framework from scratch
  26. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  27. RPC 1: how to develop RPC framework from scratch
  28. 一次性捋清楚吧,对乱糟糟的,Spring事务扩展机制
  29. 一文彻底弄懂如何选择抽象类还是接口,连续四年百度Java岗必问面试题
  30. Redis常用命令
  31. 一双拖鞋引发的血案,狂神说Java系列笔记
  32. 一、mysql基础安装
  33. 一位程序员的独白:尽管我一生坎坷,Java框架面试基础
  34. Clear it all at once. For the messy, spring transaction extension mechanism
  35. A thorough understanding of how to choose abstract classes or interfaces, baidu Java post must ask interview questions for four consecutive years
  36. Redis common commands
  37. A pair of slippers triggered the murder, crazy God said java series notes
  38. 1、 MySQL basic installation
  39. Monologue of a programmer: despite my ups and downs in my life, Java framework is the foundation of interview
  40. 【大厂面试】三面三问Spring循环依赖,请一定要把这篇看完(建议收藏)
  41. 一线互联网企业中,springboot入门项目
  42. 一篇文带你入门SSM框架Spring开发,帮你快速拿Offer
  43. 【面试资料】Java全集、微服务、大数据、数据结构与算法、机器学习知识最全总结,283页pdf
  44. 【leetcode刷题】24.数组中重复的数字——Java版
  45. 【leetcode刷题】23.对称二叉树——Java版
  46. 【leetcode刷题】22.二叉树的中序遍历——Java版
  47. 【leetcode刷题】21.三数之和——Java版
  48. 【leetcode刷题】20.最长回文子串——Java版
  49. 【leetcode刷题】19.回文链表——Java版
  50. 【leetcode刷题】18.反转链表——Java版
  51. 【leetcode刷题】17.相交链表——Java&python版
  52. 【leetcode刷题】16.环形链表——Java版
  53. 【leetcode刷题】15.汉明距离——Java版
  54. 【leetcode刷题】14.找到所有数组中消失的数字——Java版
  55. 【leetcode刷题】13.比特位计数——Java版
  56. oracle控制用户权限命令
  57. 三年Java开发,继阿里,鲁班二期Java架构师
  58. Oracle必须要启动的服务
  59. 万字长文!深入剖析HashMap,Java基础笔试题大全带答案
  60. 一问Kafka就心慌?我却凭着这份,图灵学院vip课程百度云