1.為什么要分庫分表（設計高并發系統的時候，數據庫層面該如何設計）？用過哪些分庫分表中間件？不同的分庫分表中間件都有什么優點和缺點？
2.現在有一個未分庫分表的系統，未來要分庫分表，如何設計才可以讓系統從未分庫分表動態切換到分庫分表上
3.如何設計可以動態擴容縮容的分庫分表方案？
4.分庫分表之后，id主鍵如何處理？
5.mysql的主從同步與讀寫分離
（1）如何實現mysql的讀寫分離？
（2）MySQL主從復制原理的是啥？
（3）mysql主從同步延時問題
6.mysql什么時候創建索引
7.mysql索引底層數據結構與算法

?

1.為什么要分庫分表（設計高并發系統的時候，數據庫層面該如何設計）？用過哪些分庫分表中間件？不同的分庫分表中間件都有什么優點和缺點？
你們具體是如何對數據庫如何進行垂直拆分或水平拆分的？

實際上這是跟著你的公司業務發展走的，你公司業務發展越好，用戶就越多，數據量越大，請求量越大，那你單個數據庫一定扛不住。
比如你單表都幾千萬數據了，你確定你能抗住么？絕對不行，單表數據量太大，會極大影響你的sql執行的性能，到了后面你的sql可能就跑的很慢了。一般來說，就以我的經驗來看，單表到幾百萬的時候，性能就會相對差一些了，你就得分表了。
分表是啥意思？就是把一個表的數據放到多個表中，然后查詢的時候你就查一個表。比如按照用戶id來分表，將一個用戶的數據就放在一個表中。然后操作的時候你對一個用戶就操作那個表就好了。這樣可以控制每個表的數據量在可控的范圍內，比如每個表就固定在200萬以內。
分庫是啥意思？就是你一個庫一般我們經驗而言，最多支撐到并發2000，一定要擴容了，而且一個健康的單庫并發值你最好保持在每秒1000左右，不要太大。那么你可以將一個庫的數據拆分到多個庫中，訪問的時候就訪問一個庫好了。

sharding-jdbc這種client層方案的優點在于不用部署，運維成本低，不需要代理層的二次轉發請求，性能很高，但是如果遇到升級啥的需要各個系統都重新升級版本再發布，各個系統都需要耦合sharding-jdbc的依賴；
mycat這種proxy層方案的缺點在于需要部署，自己及運維一套中間件，運維成本高，但是好處在于對于各個項目是透明的，如果遇到升級之類的都是自己中間件那里搞就行了。
通常來說，這兩個方案其實都可以選用，但是我個人建議中小型公司選用sharding-jdbc，client層方案輕便，而且維護成本低，不需要額外增派人手，而且中小型公司系統復雜度會低一些，項目也沒那么多；
但是中大型公司最好還是選用mycat這類proxy層方案，因為可能大公司系統和項目非常多，團隊很大，人員充足，那么最好是專門弄個人來研究和維護mycat，然后大量項目直接透明使用即可。

如何對數據庫如何進行垂直拆分或水平拆分的？
水平拆分的意思，就是把一個表的數據給弄到多個庫的多個表里去，但是每個庫的表結構都一樣，只不過每個庫表放的數據是不同的，所有庫表的數據加起來就是全部數據。水平拆分的意義，就是將數據均勻放更多的庫里，然后用多個庫來抗更高的并發，還有就是用多個庫的存儲容量來進行擴容。
垂直拆分的意思，就是把一個有很多字段的表給拆分成多個表，或者是多個庫上去。每個庫表的結構都不一樣，每個庫表都包含部分字段。一般來說，會將較少的訪問頻率很高的字段放到一個表里去，然后將較多的訪問頻率很低的字段放到另外一個表里去。因為數據庫是有緩存的，你訪問頻率高的行字段越少，就可以在緩存里緩存更多的行，性能就越好。這個一般在表層面做的較多一些。
這個其實挺常見的，不一定我說，大家很多同學可能自己都做過，把一個大表拆開，訂單表、訂單支付表、訂單商品表。
還有表層面的拆分，就是分表，將一個表變成N個表，就是讓每個表的數據量控制在一定范圍內，保證SQL的性能。否則單表數據量越大，SQL性能就越差。一般是200萬行左右，不要太多，但是也得看具體你怎么操作，也可能是500萬，或者是100萬。你的SQL越復雜，就最好讓單表行數越少。
好了，無論是分庫了還是分表了，上面說的那些數據庫中間件都是可以支持的。就是基本上那些中間件可以做到你分庫分表之后，中間件可以根據你指定的某個字段值，比如說userid，自動路由到對應的庫上去，然后再自動路由到對應的表里去。
你就得考慮一下，你的項目里該如何分庫分表？一般來說，垂直拆分，你可以在表層面來做，對一些字段特別多的表做一下拆分；水平拆分，你可以說是并發承載不了，或者是數據量太大，容量承載不了，你給拆了，按什么字段來拆，你自己想好；分表，你考慮一下，你如果哪怕是拆到每個庫里去，并發和容量都ok了，但是每個庫的表還是太大了，那么你就分表，將這個表分開，保證每個表的數據量并不是很大。
而且這兒還有兩種分庫分表的方式，一種是按照range來分，就是每個庫一段連續的數據，這個一般是按比如時間范圍來的，但是這種一般較少用，因為很容易產生熱點問題，大量的流量都打在最新的數據上了；或者是按照某個字段hash一下均勻分散，這個較為常用。
range來分，好處在于說，后面擴容的時候，就很容易，因為你只要預備好，給每個月都準備一個庫就可以了，到了一個新的月份的時候，自然而然，就會寫新的庫了；缺點，但是大部分的請求，都是訪問最新的數據。實際生產用range，要看場景，你的用戶不是僅僅訪問最新的數據，而是均勻的訪問現在的數據以及歷史的數據
hash分法，好處在于說，可以平均分配沒給庫的數據量和請求壓力；壞處在于說擴容起來比較麻煩，會有一個數據遷移的這么一個過程

2.現在有一個未分庫分表的系統，未來要分庫分表，如何設計才可以讓系統從未分庫分表動態切換到分庫分表上
雙寫遷移方案
這個是我們常用的一種遷移方案，比較靠譜一些，不用停機，不用看北京凌晨4點的風景
簡單來說，就是在線上系統里面，之前所有寫庫的地方，增刪改操作，都除了對老庫增刪改，都加上對新庫的增刪改，這就是所謂雙寫，同時寫倆庫，老庫和新庫。
然后系統部署之后，新庫數據差太遠，用之前說的導數工具，跑起來讀老庫數據寫新庫，寫的時候要根據gmt_modified這類字段判斷這條數據最后修改的時間，除非是讀出來的數據在新庫里沒有，或者是比新庫的數據新才會寫。
接著導完一輪之后，有可能數據還是存在不一致，那么就程序自動做一輪校驗，比對新老庫每個表的每條數據，接著如果有不一樣的，就針對那些不一樣的，從老庫讀數據再次寫。反復循環，直到兩個庫每個表的數據都完全一致為止。
接著當數據完全一致了，就ok了，基于僅僅使用分庫分表的最新代碼，重新部署一次，不就僅僅基于分庫分表在操作了么，還沒有幾個小時的停機時間，很穩。所以現在基本玩兒數據遷移之類的，都是這么干了。

3.如何設計可以動態擴容縮容的分庫分表方案？
（1）選擇一個數據庫中間件，調研、學習、測試
（2）設計你的分庫分表的一個方案，你要分成多少個庫，每個庫分成多少個表，3個庫每個庫4個表
（3）基于選擇好的數據庫中間件，以及在測試環境建立好的分庫分表的環境，然后測試一下能否正常進行分庫分表的讀寫
（4）完成單庫單表到分庫分表的遷移，雙寫方案
（5）線上系統開始基于分庫分表對外提供服務
（6）擴容了，擴容成6個庫，每個庫需要12個表，你怎么來增加更多庫和表呢？

一開始上來就是32個庫，每個庫32個表，1024張表
我可以告訴各位同學說，這個分法，第一，基本上國內的互聯網肯定都是夠用了，第二，無論是并發支撐還是數據量支撐都沒問題
每個庫正常承載的寫入并發量是1000，那么32個庫就可以承載32 * 1000 = 32000的寫并發，如果每個庫承載1500的寫并發，32 * 1500 = 48000的寫并發，接近5萬/s的寫入并發，前面再加一個MQ，削峰，每秒寫入MQ 8萬條數據，每秒消費5萬條數據。
有些除非是國內排名非常靠前的這些公司，他們的最核心的系統的數據庫，可能會出現幾百臺數據庫的這么一個規模，128個庫，256個庫，512個庫
1024張表，假設每個表放500萬數據，在MySQL里可以放50億條數據
每秒的5萬寫并發，總共50億條數據，對于國內大部分的互聯網公司來說，其實一般來說都夠了
談分庫分表的擴容，第一次分庫分表，就一次性給他分個夠，32個庫，1024張表，可能對大部分的中小型互聯網公司來說，已經可以支撐好幾年了
一個實踐是利用32 * 32來分庫分表，即分為32個庫，每個庫里一個表分為32張表。一共就是1024張表。根據某個id先根據32取模路由到庫，再根據32取模路由到庫里的表。
剛開始的時候，這個庫可能就是邏輯庫，建在一個數據庫上的，就是一個mysql服務器可能建了n個庫，比如16個庫。后面如果要拆分，就是不斷在庫和mysql服務器之間做遷移就可以了。然后系統配合改一下配置即可。
比如說最多可以擴展到32個數據庫服務器，每個數據庫服務器是一個庫。如果還是不夠？最多可以擴展到1024個數據庫服務器，每個數據庫服務器上面一個庫一個表。因為最多是1024個表么。
這么搞，是不用自己寫代碼做數據遷移的，都交給dba來搞好了，但是dba確實是需要做一些庫表遷移的工作，但是總比你自己寫代碼，抽數據導數據來的效率高得多了。
哪怕是要減少庫的數量，也很簡單，其實說白了就是按倍數縮容就可以了，然后修改一下路由規則。
對2 ^ n取模
orderId 模 32 = 庫
orderId / 32 模 32 = 表
259 3 8
1189 5 5
352 0 11
4593 17 15

1、設定好幾臺數據庫服務器，每臺服務器上幾個庫，每個庫多少個表，推薦是32庫 * 32表，對于大部分公司來說，可能幾年都夠了
2、路由的規則，orderId 模 32 = 庫，orderId / 32 模 32 = 表
3、擴容的時候，申請增加更多的數據庫服務器，裝好mysql，倍數擴容，4臺服務器，擴到8臺服務器，16臺服務器
4、由dba負責將原先數據庫服務器的庫，遷移到新的數據庫服務器上去，很多工具，庫遷移，比較便捷
5、我們這邊就是修改一下配置，調整遷移的庫所在數據庫服務器的地址
6、重新發布系統，上線，原先的路由規則變都不用變，直接可以基于2倍的數據庫服務器的資源，繼續進行線上系統的提供服務

4.分庫分表之后，id主鍵如何處理？
snowflake算法
twitter開源的分布式id生成算法，就是把一個64位的long型的id，1個bit是不用的，用其中的41 bit作為毫秒數，用10 bit作為工作機器id，12 bit作為序列號
1 bit：不用，為啥呢？因為二進制里第一個bit為如果是1，那么都是負數，但是我們生成的id都是正數，所以第一個bit統一都是0
41 bit：表示的是時間戳，單位是毫秒。41 bit可以表示的數字多達2^41 - 1，也就是可以標識2 ^ 41 - 1個毫秒值，換算成年就是表示69年的時間。
10 bit：記錄工作機器id，代表的是這個服務最多可以部署在2^10臺機器上哪，也就是1024臺機器。但是10 bit里5個bit代表機房id，5個bit代表機器id。意思就是最多代表2 ^ 5個機房（32個機房），每個機房里可以代表2 ^ 5個機器（32臺機器）。
12 bit：這個是用來記錄同一個毫秒內產生的不同id，12 bit可以代表的最大正整數是2 ^ 12 - 1 = 4096，也就是說可以用這個12bit代表的數字來區分同一個毫秒內的4096個不同的id
64位的long型的id，64位的long -> 二進制
0 | 0001100 10100010 10111110 10001001 01011100 00 | 10001 | 1 1001 | 0000 00000000
2018-01-01 10:00:00 -> 做了一些計算，再換算成一個二進制，41bit來放 -> 0001100 10100010 10111110 10001001 01011100 00
機房id，17 -> 換算成一個二進制 -> 10001
機器id，25 -> 換算成一個二進制 -> 11001
snowflake算法服務，會判斷一下，當前這個請求是否是，機房17的機器25，在2175/11/7 12:12:14時間點發送過來的第一個請求，如果是第一個請
假設，在2175/11/7 12:12:14時間里，機房17的機器25，發送了第二條消息，snowflake算法服務，會發現說機房17的機器25，在2175/11/7 12:12:14時間里，在這一毫秒，之前已經生成過一個id了，此時如果你同一個機房，同一個機器，在同一個毫秒內，再次要求生成一個id，此時我只能把加1
0 | 0001100 10100010 10111110 10001001 01011100 00 | 10001 | 1 1001 | 0000 00000001
比如我們來觀察上面的那個，就是一個典型的二進制的64位的id，換算成10進制就是910499571847892992。

怎么說呢，大概這個意思吧，就是說41 bit，就是當前毫秒單位的一個時間戳，就這意思；然后5 bit是你傳遞進來的一個機房id（但是最大只能是32以內），5 bit是你傳遞進來的機器id（但是最大只能是32以內），剩下的那個10 bit序列號，就是如果跟你上次生成id的時間還在一個毫秒內，那么會把順序給你累加，最多在4096個序號以內。
所以你自己利用這個工具類，自己搞一個服務，然后對每個機房的每個機器都初始化這么一個東西，剛開始這個機房的這個機器的序號就是0。然后每次接收到一個請求，說這個機房的這個機器要生成一個id，你就找到對應的Worker，生成。
他這個算法生成的時候，會把當前毫秒放到41 bit中，然后5 bit是機房id，5 bit是機器id，接著就是判斷上一次生成id的時間如果跟這次不一樣，序號就自動從0開始；要是上次的時間跟現在還是在一個毫秒內，他就把seq累加1，就是自動生成一個毫秒的不同的序號。
這個算法那，可以確保說每個機房每個機器每一毫秒，最多生成4096個不重復的id。
利用這個snowflake算法，你可以開發自己公司的服務，甚至對于機房id和機器id，反正給你預留了5 bit + 5 bit，你換成別的有業務含義的東西也可以的。
這個snowflake算法相對來說還是比較靠譜的，所以你要真是搞分布式id生成，如果是高并發啥的，那么用這個應該性能比較好，一般每秒幾萬并發的場景，也足夠你用了。

5.mysql的主從同步與讀寫分離
（1）如何實現mysql的讀寫分離？
其實很簡單，就是基于主從復制架構，簡單來說，就搞一個主庫，掛多個從庫，然后我們就單單只是寫主庫，然后主庫會自動把數據給同步到從庫上去。
（2）MySQL主從復制原理的是啥？
主庫將變更寫binlog日志，然后從庫連接到主庫之后，從庫有一個IO線程，將主庫的binlog日志拷貝到自己本地，寫入一個中繼日志中。接著從庫中有一個SQL線程會從中繼日志讀取binlog，然后執行binlog日志中的內容，也就是在自己本地再次執行一遍SQL，這樣就可以保證自己跟主庫的數據是一樣的。
這里有一個非常重要的一點，就是從庫同步主庫數據的過程是串行化的，也就是說主庫上并行的操作，在從庫上會串行執行。所以這就是一個非常重要的點了，由于從庫從主庫拷貝日志以及串行執行SQL的特點，在高并發場景下，從庫的數據一定會比主庫慢一些，是有延時的。所以經常出現，剛寫入主庫的數據可能是讀不到的，要過幾十毫秒，甚至幾百毫秒才能讀取到。
而且這里還有另外一個問題，就是如果主庫突然宕機，然后恰好數據還沒同步到從庫，那么有些數據可能在從庫上是沒有的，有些數據可能就丟失了。
所以mysql實際上在這一塊有兩個機制，一個是半同步復制，用來解決主庫數據丟失問題；一個是并行復制，用來解決主從同步延時問題。
這個所謂半同步復制，semi-sync復制，指的就是主庫寫入binlog日志之后，就會將強制此時立即將數據同步到從庫，從庫將日志寫入自己本地的relay log之后，接著會返回一個ack給主庫，主庫接收到至少一個從庫的ack之后才會認為寫操作完成了。

所謂并行復制，指的是從庫開啟多個線程，并行讀取relay log中不同庫的日志，然后并行重放不同庫的日志，這是庫級別的并行。
1）主從復制的原理
2）主從延遲問題產生的原因
3）主從復制的數據丟失問題，以及半同步復制的原理
4）并行復制的原理，多庫并發重放relay日志，緩解主從延遲問題

（3）mysql主從同步延時問題（精華）

線上確實處理過因為主從同步延時問題，導致的線上的bug，小型的生產事故
show status，Seconds_Behind_Master，你可以看到從庫復制主庫的數據落后了幾ms
其實這塊東西我們經常會碰到，就比如說用了mysql主從架構之后，可能會發現，剛寫入庫的數據結果沒查到，結果就完蛋了。。。。
所以實際上你要考慮好應該在什么場景下來用這個mysql主從同步，建議是一般在讀遠遠多于寫，而且讀的時候一般對數據時效性要求沒那么高的時候，用mysql主從同步
所以這個時候，我們可以考慮的一個事情就是，你可以用mysql的并行復制，但是問題是那是庫級別的并行，所以有時候作用不是很大
所以這個時候。。通常來說，我們會對于那種寫了之后立馬就要保證可以查到的場景，采用強制讀主庫的方式，這樣就可以保證你肯定的可以讀到數據了吧。其實用一些數據庫中間件是沒問題的。

一般來說，如果主從延遲較為嚴重
1、分庫，將一個主庫拆分為4個主庫，每個主庫的寫并發就500/s，此時主從延遲可以忽略不計
2、打開mysql支持的并行復制，多個庫并行復制，如果說某個庫的寫入并發就是特別高，單庫寫并發達到了2000/s，并行復制還是沒意義。28法則，很多時候比如說，就是少數的幾個訂單表，寫入了2000/s，其他幾十個表10/s。
3、重寫代碼，寫代碼的同學，要慎重，當時我們其實短期是讓那個同學重寫了一下代碼，插入數據之后，直接就更新，不要查詢
4、如果確實是存在必須先插入，立馬要求就查詢到，然后立馬就要反過來執行一些操作，對這個查詢設置直連主庫。
不推薦這種方法，你這么搞導致讀寫分離的意義就喪失了

6.mysql什么時候創建索引
1.索引是一套數據結構
2.優勢:查詢快/劣勢:降低更新表的速度,寫操作慢,查操作快,兩套數據
3.什么情況下創建索引
主鍵自動建立唯一索引
頻繁作為查詢條件的字段創建索引
外鍵關系創建索引
單鍵/組合索引的選擇問題,組合索引性價比更高
查詢中排序的字段,排序字段若通過索引去訪問將大大提高排序速度
查詢中統計或者分組字段 group by/order by

7.mysql索引底層數據結構與算法

索引的數據結構 二叉樹/HASH/BTREE
Btree 度(Degree)-節點的數據存儲個數 橫向變長,高度變少,節點查找是在內存里
一次IO是4K數據,節點的度就是4K數據
B+Tree 非葉子節點不存儲data,只存儲key,可以增大度
一般使用磁盤IO次數評價索引結構的優劣
myisam索引實現 存儲引擎是表級別
索引和數據是分離的 葉子節點存的是文件指針不是數據
主鍵索引/非主鍵索引/分開存儲的
innodb 主鍵索引 數據文件本身就是索引文件 葉子節點存儲就是數據
innodb必須要有主鍵 整型自增主鍵
非主鍵索引葉子節點存儲的是主鍵,并不是數據，需要查找2次才能找到數據
聯合索引的底層存儲結構同上

?

轉載于:https://www.cnblogs.com/muzinan110/p/11105739.html

總結

以上是生活随笔為你收集整理的mysql运维相关的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。