当前位置:网站首页>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
边栏推荐
猜你喜欢
Krpano panorama vtour folder and tour
网站压测工具Apache-ab,webbench,Apache-Jemeter
ICE -- 源码分析
Detailed explanation of kubernetes (IX) -- actual combat of creating pod with resource allocation list
Mysql database explanation (8)
大型互联网为什么禁止ip直连
Mumu, go all the way
MySQL InnoDB transaction
Wechat applet customer service access to send and receive messages
时序模型:长短期记忆网络(LSTM)
随机推荐
移动app软件测试工具有哪些?第三方软件测评小编分享
PHP 的运算符
KNN, kmeans and GMM
字符串排序
php类与对象
PHP PDO ODBC将一个文件夹的文件装载到MySQL数据库BLOB列,并将BLOB列下载到另一个文件夹
负载均衡器
WPS品牌再升级专注国内,另两款国产软件低调出国门,却遭禁令
多级缓存使用
重定向和请求转发详解
Educational Codeforces Round 127 A-E题解
[leetcode daily question] install fence
软件性能测试报告起着什么作用?第三方测试报告如何收费?
通过 PDO ODBC 将 PHP 连接到 MySQL
推荐搜索 常用评价指标
c语言---指针进阶
码住收藏▏软件测试报告模板范文来了
Node. JS ODBC connection PostgreSQL
木木一路走好呀
Explanation 2 of redis database (redis high availability, persistence and performance management)