【建議收藏】MySQL 三萬字精華總結(jié) —分區(qū)、分表、分庫和主從復制...
九、分區(qū)、分表、分庫
MySQL分區(qū)
一般情況下我們創(chuàng)建的表對應一組存儲文件，使用MyISAM存儲引擎時是一個.MYI和.MYD文件，使用Innodb存儲引擎時是一個.ibd和.frm（表結(jié)構(gòu)）文件。
當數(shù)據(jù)量較大時（一般千萬條記錄級別以上），MySQL的性能就會開始下降，這時我們就需要將數(shù)據(jù)分散到多組存儲文件，保證其單個文件的執(zhí)行效率
能干嘛
- 邏輯數(shù)據(jù)分割
- 提高單一的寫和讀應用速度
- 提高分區(qū)范圍讀查詢的速度
- 分割數(shù)據(jù)能夠有多個不同的物理文件路徑
- 高效的保存歷史數(shù)據(jù)
怎么玩
首先查看當前數(shù)據(jù)庫是否支持分區(qū)
MySQL5.6以及之前版本：
SHOW VARIABLES LIKE '%partition%';MySQL5.6：
show plugins;
分區(qū)類型及操作
RANGE分區(qū)：基于屬于一個給定連續(xù)區(qū)間的列值，把多行分配給分區(qū)。mysql將會根據(jù)指定的拆分策略，,把數(shù)據(jù)放在不同的表文件上。相當于在文件上,被拆成了小塊.但是,對外給客戶的感覺還是一張表，透明的。
按照 range 來分，就是每個庫一段連續(xù)的數(shù)據(jù)，這個一般是按比如時間范圍來的，比如交易表啊，銷售表啊等，可以根據(jù)年月來存放數(shù)據(jù)。可能會產(chǎn)生熱點問題，大量的流量都打在最新的數(shù)據(jù)上了。
range 來分，好處在于說，擴容的時候很簡單。
LIST分區(qū)：類似于按RANGE分區(qū)，每個分區(qū)必須明確定義。它們的主要區(qū)別在于，LIST分區(qū)中每個分區(qū)的定義和選擇是基于某列的值從屬于一個值列表集中的一個值，而RANGE分區(qū)是從屬于一個連續(xù)區(qū)間值的集合。
HASH分區(qū)：基于用戶定義的表達式的返回值來進行選擇的分區(qū)，該表達式使用將要插入到表中的這些行的列值進行計算。這個函數(shù)可以包含MySQL 中有效的、產(chǎn)生非負整數(shù)值的任何表達式。
hash 分發(fā)，好處在于說，可以平均分配每個庫的數(shù)據(jù)量和請求壓力；壞處在于說擴容起來比較麻煩，會有一個數(shù)據(jù)遷移的過程，之前的數(shù)據(jù)需要重新計算 hash 值重新分配到不同的庫或表
KEY分區(qū)：類似于按HASH分區(qū)，區(qū)別在于KEY分區(qū)只支持計算一列或多列，且MySQL服務(wù)器提供其自身的哈希函數(shù)。必須有一列或多列包含整數(shù)值。
看上去分區(qū)表很帥氣，為什么大部分互聯(lián)網(wǎng)還是更多的選擇自己分庫分表來水平擴展咧？
- 分區(qū)表，分區(qū)鍵設(shè)計不太靈活，如果不走分區(qū)鍵，很容易出現(xiàn)全表鎖
- 一旦數(shù)據(jù)并發(fā)量上來，如果在分區(qū)表實施關(guān)聯(lián)，就是一個災難
- 自己分庫分表，自己掌控業(yè)務(wù)場景與訪問模式，可控。分區(qū)表，研發(fā)寫了一個sql，都不確定mysql是怎么玩的，不太可控
?隨著業(yè)務(wù)的發(fā)展，業(yè)務(wù)越來越復雜，應用的模塊越來越多，總的數(shù)據(jù)量很大，高并發(fā)讀寫操作均超過單個數(shù)據(jù)庫服務(wù)器的處理能力怎么辦？
這個時候就出現(xiàn)了數(shù)據(jù)分片，數(shù)據(jù)分片指按照某個維度將存放在單一數(shù)據(jù)庫中的數(shù)據(jù)分散地存放至多個數(shù)據(jù)庫或表中。數(shù)據(jù)分片的有效手段就是對關(guān)系型數(shù)據(jù)庫進行分庫和分表。
區(qū)別于分區(qū)的是，分區(qū)一般都是放在單機里的，用的比較多的是時間范圍分區(qū)，方便歸檔。只不過分庫分表需要代碼實現(xiàn)，分區(qū)則是mysql內(nèi)部實現(xiàn)。分庫分表和分區(qū)并不沖突，可以結(jié)合使用。
?說說分庫與分表的設(shè)計
MySQL分表
分表有兩種分割方式，一種垂直拆分，另一種水平拆分。
垂直拆分
垂直分表，通常是按照業(yè)務(wù)功能的使用頻次，把主要的、熱門的字段放在一起做為主要表。然后把不常用的，按照各自的業(yè)務(wù)屬性進行聚集，拆分到不同的次要表中；主要表和次要表的關(guān)系一般都是一對一的。
水平拆分(數(shù)據(jù)分片)
單表的容量不超過500W，否則建議水平拆分。是把一個表復制成同樣表結(jié)構(gòu)的不同表，然后把數(shù)據(jù)按照一定的規(guī)則劃分，分別存儲到這些表中，從而保證單表的容量不會太大，提升性能；當然這些結(jié)構(gòu)一樣的表，可以放在一個或多個數(shù)據(jù)庫中。
水平分割的幾種方法：
- 使用MD5哈希，做法是對UID進行md5加密，然后取前幾位（我們這里取前兩位），然后就可以將不同的UID哈希到不同的用戶表（user_xx）中了。
- 還可根據(jù)時間放入不同的表，比如：article_201601，article_201602。
- 按熱度拆分，高點擊率的詞條生成各自的一張表，低熱度的詞條都放在一張大表里，待低熱度的詞條達到一定的貼數(shù)后，再把低熱度的表單獨拆分成一張表。
- 根據(jù)ID的值放入對應的表，第一個表user_0000，第二個100萬的用戶數(shù)據(jù)放在第二 個表user_0001中，隨用戶增加，直接添加用戶表就行了。
MySQL分庫
?為什么要分庫?
數(shù)據(jù)庫集群環(huán)境后都是多臺 slave，基本滿足了讀取操作; ?但是寫入或者說大數(shù)據(jù)、頻繁的寫入操作對master性能影響就比較大，這個時候，單庫并不能解決大規(guī)模并發(fā)寫入的問題，所以就會考慮分庫。
?分庫是什么？
一個庫里表太多了，導致了海量數(shù)據(jù)，系統(tǒng)性能下降，把原本存儲于一個庫的表拆分存儲到多個庫上， 通常是將表按照功能模塊、關(guān)系密切程度劃分出來，部署到不同庫上。
優(yōu)點：
減少增量數(shù)據(jù)寫入時的鎖對查詢的影響
由于單表數(shù)量下降，常見的查詢操作由于減少了需要掃描的記錄，使得單表單次查詢所需的檢索行數(shù)變少，減少了磁盤IO，時延變短
但是它無法解決單表數(shù)據(jù)量太大的問題
分庫分表后的難題
分布式事務(wù)的問題，數(shù)據(jù)的完整性和一致性問題。
數(shù)據(jù)操作維度問題：用戶、交易、訂單各個不同的維度，用戶查詢維度、產(chǎn)品數(shù)據(jù)分析維度的不同對比分析角度?？鐜炻?lián)合查詢的問題，可能需要兩次查詢 跨節(jié)點的count、order by、group by以及聚合函數(shù)問題，可能需要分別在各個節(jié)點上得到結(jié)果后在應用程序端進行合并 額外的數(shù)據(jù)管理負擔，如：訪問數(shù)據(jù)表的導航定位 額外的數(shù)據(jù)運算壓力，如：需要在多個節(jié)點執(zhí)行，然后再合并計算程序編碼開發(fā)難度提升，沒有太好的框架解決，更多依賴業(yè)務(wù)看如何分，如何合，是個難題。
?配主從，正經(jīng)公司的話，也不會讓 Javaer 去搞的，但還是要知道
十、主從復制
復制的基本原理
slave 會從 master 讀取 binlog 來進行數(shù)據(jù)同步
三個步驟
img
- master將改變記錄到二進制日志（binary log）。這些記錄過程叫做二進制日志事件，binary log events；
- salve 將 master 的 binary log events 拷貝到它的中繼日志（relay log）;
- slave 重做中繼日志中的事件，將改變應用到自己的數(shù)據(jù)庫中。MySQL 復制是異步且是串行化的。
復制的基本原則
- 每個 slave只有一個 master
- 每個 salve只能有一個唯一的服務(wù)器 ID
- 每個master可以有多個salve
復制的最大問題
- 延時
十一、其他問題
說一說三個范式
- 第一范式（1NF）：數(shù)據(jù)庫表中的字段都是單一屬性的，不可再分。這個單一屬性由基本類型構(gòu)成，包括整型、實數(shù)、字符型、邏輯型、日期型等。
- 第二范式（2NF）：數(shù)據(jù)庫表中不存在非關(guān)鍵字段對任一候選關(guān)鍵字段的部分函數(shù)依賴（部分函數(shù)依賴指的是存在組合關(guān)鍵字中的某些字段決定非關(guān)鍵字段的情況），也即所有非關(guān)鍵字段都完全依賴于任意一組候選關(guān)鍵字。
- 第三范式（3NF）：在第二范式的基礎(chǔ)上，數(shù)據(jù)表中如果不存在非關(guān)鍵字段對任一候選關(guān)鍵字段的傳遞函數(shù)依賴則符合第三范式。所謂傳遞函數(shù)依賴，指的是如 果存在"A → B → C"的決定關(guān)系，則C傳遞函數(shù)依賴于A。因此，滿足第三范式的數(shù)據(jù)庫表應該不存在如下依賴關(guān)系：關(guān)鍵字段 → 非關(guān)鍵字段 x → 非關(guān)鍵字段y
百萬級別或以上的數(shù)據(jù)如何刪除
關(guān)于索引：由于索引需要額外的維護成本，因為索引文件是單獨存在的文件,所以當我們對數(shù)據(jù)的增加,修改,刪除,都會產(chǎn)生額外的對索引文件的操作,這些操作需要消耗額外的IO,會降低增/改/刪的執(zhí)行效率。所以，在我們刪除數(shù)據(jù)庫百萬級別數(shù)據(jù)的時候，查詢MySQL官方手冊得知刪除數(shù)據(jù)的速度和創(chuàng)建的索引數(shù)量是成正比的。
- 所以我們想要刪除百萬數(shù)據(jù)的時候可以先刪除索引（此時大概耗時三分多鐘）
- 然后刪除其中無用數(shù)據(jù)（此過程需要不到兩分鐘）
- 刪除完成后重新創(chuàng)建索引(此時數(shù)據(jù)較少了)創(chuàng)建索引也非?？欤s十分鐘左右。
- 與之前的直接刪除絕對是要快速很多，更別說萬一刪除中斷,一切刪除會回滾。那更是坑了。