MySQL common sub database and sub table scheme, are here!

Manong's breakthrough 2020-11-09 19:10:58
mysql common sub database sub


One 、 Database bottleneck

Whether it's IO bottleneck , still CPU bottleneck , Eventually, the number of active connections to the database will increase , Furthermore, the threshold of the number of active connections that the database can carry is approached or even reached . In the business Service Come and see , Few or no database connections available . Now you can imagine ( Concurrency 、 throughput 、 collapse ).

1、IO bottleneck
The first one is : Disk read IO bottleneck , Too much hot data , The database cache can't hold , A large number of IO, Reduce query speed -> Sub database and vertical sub table .
The second kind : The Internet IO bottleneck , Too much data requested , The network bandwidth is not enough -> sub-treasury .

2、CPU bottleneck
The first one is :SQL problem , Such as SQL Contained in the join,group by,order by, Non index field condition query, etc , increase CPU The operation of operations -> SQL Optimize , Build the right index , In the business Service Layer for Business Computing .

The second kind : The amount of data in a single table is too large , Too many rows scanned during query ,SQL Low efficiency ,CPU First bottleneck -> Horizontal sub table .

Two 、 Sub database and sub table

1、 Horizontal sub database
 Insert picture description here
Concept : Based on fields , According to a certain strategy (hash、range etc. ), Split data from one library into multiple libraries .

result :
The structure of each library is the same ;
The data in each library is different , There is no intersection ;
The union of all libraries is full data ;

scene : The absolute concurrency of the system is up , It is difficult to solve the problem fundamentally by dividing tables , And there is no obvious business ownership to divide the database vertically .

analysis : There are more libraries ,io and cpu Of course, the pressure of .

2、 Horizontal sub table
 Insert picture description here
Concept : Based on fields , According to a certain strategy (hash、range etc. ), Split the data in one table into multiple tables .

result :
Every table has the same structure ;
The data for each table is different , There is no intersection ;
The union of all tables is full data ;

scene : The absolute concurrent amount of the system is not up , Just too much data in a single table , Affected SQL efficiency , It's aggravating CPU burden , So that it becomes a bottleneck .

analysis : There is less data in the table , A single SQL High execution efficiency , Nature lightens CPU The burden of .

3、 Vertical sub database
 Insert picture description here
Concept : Based on the table , According to the business ownership , Split different tables into different libraries .

result :
The structure of each library is different ;
The data of each library is different , There is no intersection ;
The union of all libraries is full data ;
scene : The absolute concurrency of the system is up , And separate business modules can be abstracted .

analysis : To this step , Basically, it can be serviced . for example , Some common configuration tables with the development of business 、 There are more and more dictionaries , In this case, you can detach these tables into a separate library , It can even serve . There's more , With the development of business, a set of business model has been hatched , In this case, you can split the related tables into a separate library , It can even serve .

4、 Vertical sub table
 Insert picture description here
Concept : Based on fields , According to the activity of the field , Split the fields in the table into different tables ( Main table and extended table ) in .

result :
Every watch has a different structure ;
Each table has different data , Generally speaking , At least one column intersection for each table's fields , It's usually a primary key , Used to correlate data ;
The union of all tables is full data ;

scene : The absolute concurrent amount of the system is not up , There are not many records in the table , But there are many fields , And hot data and non hot data are together , Large storage space required for single row data . So that the number of data rows in the database cache is reduced , When querying, it will read the disk data and generate a large number of random reads IO, produce IO bottleneck .

analysis : You can use the list page and the details page to help you understand . The splitting principle of vertical tables is to split hot data ( Data that may be redundant and often queried together ) Put it together as the main table , Non hot data together as extension table . So more hot data can be cached , And that reduces random reading IO. After the demolition , To get all the data, you need to associate two tables to get the data . But remember , Never use it join, because join Not only will it increase CPU Burden and will say that two tables are coupled together ( Must be on a database instance ). Linked data , Should be in business Service Layer to do article , Get the data of the main table and the extended table respectively, and then associate them with the associated fields to get all the data .

3、 ... and 、 Sub database and sub table tool

sharding-sphere:jar, Formerly known as sharding-jdbc;
TDDL:jar,Taobao Distribute Data Layer;
Mycat: middleware .
notes : Advantages and disadvantages of tools , Please do your own research , Website and community priority .

Four 、 Steps of sub database and sub table

According to capacity ( Current capacity and growth ) Number of evaluation sub databases or sub tables -> choose key( uniform )-> Table rules (hash or range etc. )-> perform ( General double writing )-> Capacity expansion ( Minimize data movement ).

5、 ... and 、 The problem of sub database and sub table

1、 Not partition key Query questions for
Based on horizontal sub base and sub table , Split strategy is common hash Law .
Except for partition key There is only one non partition key Query as a condition

Mapping method
 Insert picture description here

Gene method
 Insert picture description here
notes : When writing , Genetic generation user_id, Pictured . About xbit gene , For example, to divide 8 A watch ,23=8, so x take 3, namely 3bit gene . according to user_id When querying, the module can be directly routed to the corresponding sub database or sub table . according to user_name When inquiring , Through the first user_name_code Generating function generation user_name_code Then the module is routed to the corresponding sub database or sub table .id Generate common snowflake Algorithm .
Except for partition key More than one non partition key Query as a condition

Mapping method
 Insert picture description here
Redundancy method
 Insert picture description here
notes : according to order_id or buyer_id Route to on query db_o_buyer In the library , according to seller_id Route to on query db_o_seller In the library . I feel like putting the cart before the horse ! Is there any other good way ? Change the technology stack ?

Backstage except partition key And all kinds of non partition key Combined condition query
NoSQL Law
 Insert picture description here
Redundancy method
 Insert picture description here
2、 Not partition key Cross database and cross table paging query
Based on horizontal sub base and sub table , Split strategy is common hash Law .
notes : use NoSQL Law solve (ES etc. ).

3、 Capacity expansion
Based on horizontal sub base and sub table , Split strategy is common hash Law .

Horizontal expansion warehouse ( Upgrade from library method )
 Insert picture description here
notes : The expansion is multiple .

Horizontal expansion table ( Double write transfer )
 Insert picture description here
First step :( Synchronous double write ) Modify application configuration and code , Plus double writing , Deploy ; The second step :( Synchronous double write ) Copy the old data in the old database to the new database ; The third step :( Synchronous double write ) Proofread the old data in the new database according to the old database ; Step four :( Synchronous double write ) Modify application configuration and code , Remove double write , Deploy ;
notes : Double writing is a general solution .

6、 ... and 、 Summary of sub database and sub table

Sub database and sub table , First of all, we need to know where the bottleneck is , Then we can split it reasonably ( Sub database or sub table ? Horizontal or vertical ? Several ?). And cannot be split for the sake of database and table splitting .

choose key Very important , It is necessary to consider the even split , We should also consider the non partition key Query for .
As long as it can meet the demand , The simpler the split rule, the better .

7、 ... and 、 Example of sub database and sub table
Example GitHub Address :https://github.com/littlecharacter4s/study-sharding

from : The characters of the village
source :www.cnblogs.com/littlecharacter/p/9342129.htm

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

  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课程百度云