当前位置:网站首页>MySQL Cluster Mode and application scenario
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 .
analysis :
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
Ali canal
版权声明
本文为[dengk2013]所创,转载请带上原文链接,感谢
https://yzsam.com/2022/04/202204231537380354.html
边栏推荐
- Connect PHP to MSSQL via PDO ODBC
- What is CNAs certification? What are the software evaluation centers recognized by CNAs?
- What role does the software performance test report play? How much is the third-party test report charged?
- 计算某字符出现次数
- 深度学习调参的技巧
- MySQL InnoDB transaction
- php类与对象
- MySQL query library size
- For examination
- Detailed explanation of kubernetes (XI) -- label and label selector
猜你喜欢
Detailed explanation of kubernetes (XI) -- label and label selector
cadence SPB17.4 - Active Class and Subclass
重定向和请求转发详解
Machine learning - logistic regression
考试考试自用
T2 icloud calendar cannot be synchronized
What if the server is poisoned? How does the server prevent virus intrusion?
Special analysis of China's digital technology in 2022
多生成树MSTP的配置
Sword finger offer (2) -- for Huawei
随机推荐
Connectez PHP à MySQL via aodbc
Mysql database explanation (10)
推荐搜索 常用评价指标
使用 Bitnami PostgreSQL Docker 镜像快速设置流复制集群
Precautions for use of dispatching system
大型互联网为什么禁止ip直连
Basic concepts of website construction and management
Today's sleep quality record 76 points
php函数
一刷314-剑指 Offer 09. 用两个栈实现队列(e)
网站建设与管理的基本概念
Mysql database explanation (VII)
自主作业智慧农场创新论坛
What is CNAs certification? What are the software evaluation centers recognized by CNAs?
基础贪心总结
CAP定理
Educational Codeforces Round 127 A-E题解
YML references other variables
[backtrader source code analysis 18] Yahoo Py code comments and analysis (boring, interested in the code, you can refer to)
Use of common pod controller of kubernetes