MySQL Cluster Mode and application scenario
2022-04-23 15:39:00 【dengk2013】
Flow saving assistant ：
Single library mode ： One mysql The database carries all relevant data .
Read write separation cluster mode ： Add an intermediate layer on the original basis , It forms a read-write separated cluster with the back-end data set . Overall infrastructure ： The original main library is derived from the word library 1, Word stock 2,
utilize mysql The original master-slave synchronization mechanism （ That is to say ：binlog Log synchronization ）, Reproduce the data changes of the master database in the slave database , Ensure data synchronization . The main library is generally used for write processing ,
Read from library . details ： If you operate directly on the main database, you cannot complete the read-write separation , You need to allocate sharded middleware at the front end （ Ali mycat, JD.COM ShardingSphere）,
The middleware passes curd request , To decide which library to handle .MHA Middleware to achieve high availability （ namely ： The main server is broken ,MHA Middleware can promote a table from the master server ）.
All node data is kept synchronized . It is suitable for reading more and writing less , A single watch is no more than ten million Internet applications .
Sub database and sub table （ Fragmentation ） Cluster pattern ： One mysql When the database doesn't hold . Divide the data of the database into different node databases （ namely ： The data of the node database is combined into a complete data body ）.
Middleware is needed for routing .（ Yes sql To analyze , Send the request to the corresponding database , The process of distributing requests is called routing ）. No high availability .
Why do big factories make vertical tables ?
There are too many fields in a table. You need to make a vertical table .
What is a horizontal scale ？
Split data in behavioral units （ Range method ,hash Law ）. characteristic ： All tables have exactly the same structure . Used to solve the storage problem of large amount of data .
What is a vertical sub table ？
Split the table into columns 2 More than one small watch , Get data through primary foreign key Association .
Why do you do this ？
Need to know mysql Of InnoDB Processing engine .
Row data is called ：row
The basic unit of management data is called page ：page; The default size of each page ：16k
The unit in which the page is saved is called the area ：Extent.
Relationship ： The area consists of consecutive pages , A page consists of consecutive lines .1024/16=64( namely ： One 1M There are 64 A page )
InnoDB1.0 New features after , Compress pages .
Compress pages ： Compress the underlying data , Make the actual size smaller than the logical size .
In the process of retrieving data across pages , The efficiency of compression and decompression is low . At table design time , Store as many rows of data as possible in the page , Reduce cross page retrieval , Add in page search .
1 The row data is 1K,1 page 16K, namely 1 page 16 Data ,1 Billion data needs 625 Ten thousand pages
After vertical paging ,1 The row data is 64 byte （1K=1024 byte ）, namely 1 page 256 Data ,1 Billion data needs 40 Ten thousand pages . The data after paging is based on id And so on .
By splitting important fields into small tables , Let each page hold more rows of data , After page reduction , Reduce the data scanning range , Achieve the purpose of improving execution efficiency .
Vertical tabulation conditions ：
1. The data of a single table reaches ten million
2. The field is over 20 individual , And contains vachar,CLOB,BLOB Etc
The field is enlarged according to the table ：
Watch ： Data query 、 Fields required for sorting ; Small fields for high frequency access
The big table ： Low frequency access field ; Large field
Self incrementing primary key is not applicable in distributed environment .
Since self incrementing primary keys must be continuous , Therefore, the segmentation is carried out according to the range method ,ID The number of has been fixed . Cannot dynamically expand . Will produce “ Tail hot spot ” effect .
Tail hot spot ： That is, after slicing according to the range method , The previous slice has stored data , The pressure of the last slice is very high .
Hash Slicing is more efficient .
Use UUID Replace self incrementing primary key ？ Can not be 、
Involving the underlying mechanism of the database ：
1.uuid, The only disorder . Disorder causes index rearrangement . When the primary keys are in order ,B+ The tree only needs to be appended to the original data .
How to solve ？ Distributed and orderly primary key generation algorithm ？
Snowflake algorithm （SnowFlake）, Twitter .
structure ： Sign bit （1bit）+ Time stamp （41bit）+ machine ID（10bit）+ Sequence （12bit）
Usage method ： Call directly JAR package
Snowflake algorithm needs to pay attention to the impact of time callback . May appear id The possibility of repetition
Detailed explanation of MySQL connection query
Detailed explanation of kubernetes (XI) -- label and label selector
cadence SPB17. 4 - Active Class and Subclass
Openstack command operation
Detailed explanation of redirection and request forwarding
Today's sleep quality record 76 points
What role does the software performance test report play? How much is the third-party test report charged?
Codejock Suite Pro v20.3.0
[leetcode daily question] install fence
Openstack command operation
Advantages, disadvantages and selection of activation function
What exactly does the distributed core principle analysis that fascinates Alibaba P8? I was surprised after reading it