【資料庫】MySQL鎖機制、熱備、分表

歡迎關注公眾號：【愛編碼】 如果有需要後臺回覆2019贈送1T的學習資料哦！！

注：本文大都來自網際網路，文字較多，基本是概念，若想深入瞭解，還需各位自己找文章研究。

表鎖和行鎖機制

表鎖（MyISAM和InnoDB）

表鎖的優勢：開銷小；加鎖快；無死鎖 表鎖的劣勢：鎖粒度大，發生鎖衝突的概率高，併發處理能力低 加鎖的方式：自動加鎖。查詢操作（SELECT），會自動給涉及的所有表加讀鎖，更新操作（UPDATE、DELETE、INSERT），會自動給涉及的表加寫鎖。也可以顯示加鎖：

共享讀鎖：lock table tableName read;
獨佔寫鎖：lock table tableName write;
批量解鎖：unlock tables;
複製程式碼

什麼場景下用表鎖

InnoDB預設採用行鎖，在未使用索引欄位查詢時升級為表鎖。 即便你在條件中使用了索引欄位，MySQL會根據自身的執行計劃，考慮是否使用索引(所以explain命令中會有possible_key 和 key)。 如果MySQL認為全表掃描效率更高，它就不會使用索引，這種情況下InnoDB將使用表鎖，而不是行鎖。 因此，在分析鎖衝突時，別忘了檢查SQL的執行計劃，以確認是否真正使用了索引。

第一種情況：全表更新：事務需要更新大部分或全部資料，且表又比較大。若使用行鎖，會導致事務執行效率低，從而可能造成其他事務長時間鎖等待和更多的鎖衝突。

第二種情況：多表查詢：事務涉及多個表，比較複雜的關聯查詢，很可能引起死鎖，造成大量事務回滾。這種情況若能一次性鎖定事務涉及的表，從而可以避免死鎖、減少資料庫因事務回滾帶來的開銷。

行鎖（InnoDB的行鎖）

行鎖的劣勢：開銷大；加鎖慢；會出現死鎖

行鎖的優勢：鎖的粒度小，發生鎖衝突的概率低；處理併發的能力強

加鎖的方式：自動加鎖。對於UPDATE、DELETE和INSERT語句，InnoDB會自動給涉及資料集加排他鎖；對於普通SELECT語句，InnoDB不會加任何鎖； 當然我們也可以顯示的加鎖：

共享鎖：select * from tableName where … + lock in share more
排他鎖：select * from tableName where … + for update
複製程式碼

行鎖優化

1 儘可能讓所有資料檢索都通過索引來完成，避免無索引行或索引失效導致行鎖升級為表鎖。

2 儘可能避免間隙鎖帶來的效能下降，減少或使用合理的檢索範圍。

3 儘可能減少事務的粒度，比如控制事務大小，而從減少鎖定資源量和時間長度，從而減少鎖的競爭等，提供效能。

4 儘可能低階別事務隔離，隔離級別越高，併發的處理能力越低。

InnoDB和MyISAM的最大不同點有兩個：

一，InnoDB支援事務(transaction)；

二，預設採用行級鎖。加鎖可以保證事務的一致性，可謂是有人(鎖)的地方，就有江湖(事務)；我們先簡單瞭解一下事務知識。

MySQL 事務

事務是由一組SQL語句組成的邏輯處理單元，事務具有ACID屬性。 原子性（Atomicity）：事務是一個原子操作單元。在當時原子是不可分割的最小元素，其對資料的修改，要麼全部成功，要麼全部都不成功。

一致性（Consistent）：事務開始到結束的時間段內，資料都必須保持一致狀態。

隔離性（Isolation）：資料庫系統提供一定的隔離機制，保證事務在不受外部併發操作影響的”獨立”環境執行。

永續性（Durable）：事務完成後，它對於資料的修改是永久性的，即使出現系統故障也能夠保持。

事務隔離級別

髒讀，不可重複讀，幻讀，其實都是資料庫讀一致性問題，必須由資料庫提供一定的事務隔離機制來解決。

更多精彩文章

• www.cnblogs.com/zyy1688/p/9…
• www.cnblogs.com/itdragon/p/…

雙機熱備

概念

雙機熱備

雙機熱備特指基於高可用系統中的兩臺伺服器的熱備（或高可用），因兩機高可用在國內使用較多，故得名雙機熱備。 從廣義上講，就是對於重要的服務，使用兩臺伺服器，互相備份，共同執行同一服務。當一臺伺服器出現故障時，可以由另一臺伺服器承擔服務任務，從而在不需要人工干預的情況下，自動保證系統能持續提供服務。

雙機熱備和備份的區別

熱備份指的是：High Available（HA）即高可用，而備份指的是Backup,即資料備份的一種，這是兩種不同的概念，應對的產品也是兩種功能上完全不同的產品。

熱備份主要保障業務的連續性，實現的方法是故障點的轉移。而備份，主要目的是為了防止資料丟失，而做的一份拷貝，所以備份強調的是資料恢復而不是應用的故障轉移。

雙機熱備分類

按工作中的切換方式分為： 主-備方式（Active-Standby方式）和雙主機方式（Active-Active方式）。

• 主-備方式即指的是一臺伺服器處於某種業務的啟用狀態（即Active狀態），另一臺伺服器處於該業務的備用狀態（即Standby狀態)。
• 雙主機方式即指兩種不同業務分別在兩臺伺服器上互為主備狀態（即Active-Standby和Standby-Active狀態）。

mysql 雙機熱備工作原理

簡單的說就是把 一個伺服器上執行過的sql語句在別的伺服器上也重複執行一遍， 這樣只要兩個資料庫的初態是一樣的，那麼它們就能一直同步。 當然這種複製和重複都是mysql自動實現的，我們只需要配置即可。 我們進一步詳細介紹原理的細節， 這有一張圖：

上圖中有兩個伺服器， 演示了從一個主伺服器（master） 把資料同步到從伺服器（slave）的過程。 這是一個主-從複製的例子。 主-主互相複製只是把上面的例子反過來再做一遍。 所以我們以這個例子介紹原理。 對於一個mysql伺服器， 一般有兩個執行緒來負責複製和被複制。當開啟複製之後。

• 1. 作為主伺服器Master，  會把自己的每一次改動都記錄到 二進位制日誌 Binarylog 中。 （從伺服器會負責來讀取這個log， 然後在自己那裡再執行一遍。）

• 2. 作為從伺服器Slave， 會用master上的賬號登陸到 master上， 讀取master的Binarylog,  寫入到自己的中繼日誌 Relaylog， 然後自己的sql執行緒會負責讀取這個中繼日誌，並執行一遍。  到這裡主伺服器上的更改就同步到從伺服器上了。 在mysql上可以檢視當前伺服器的主，從狀態。 其實就是當前伺服器的 Binary（作為主伺服器角色）狀態和位置。 以及其RelayLog（作為從伺服器）的複製進度。

mysql 雙機熱備實現

參考下面各位大神的配置吧，他們寫得太好了，太詳細了。我就收藏一下。

• yunnick.iteye.com/blog/184530…
• www.cnblogs.com/shuidao/p/3…
• www.cnblogs.com/fnlingnzb-l…

分庫分表

基本思想就要把一個資料庫切分成多個部分放到不同的資料庫(server)上，從而緩解單一資料庫的效能問題。

為什麼要分庫分表

當一張表的資料達到幾千萬時，你查詢一次所花的時間會變多，如果有聯合查詢的話，我想有可能會死在那兒了。分表的目的就在於此，減小資料庫的負擔，縮短查詢時間。

垂直切分和水平切分

垂直切分

一個資料庫由很多表的構成，每個表對應著不同的業務，垂直切分是指按照業務將表進行分類，分佈到不同的資料庫上面，這樣也就將資料或者說壓力分擔到不同的庫上面

優點：

    1. 拆分後業務清晰，拆分規則明確。
    2. 系統之間整合或擴充套件容易。
    3. 資料維護簡單。
複製程式碼

缺點：

    1. 部分業務表無法join，只能通過介面方式解決，提高了系統複雜度。
    2. 受每種業務不同的限制存在單庫效能瓶頸，不易資料擴充套件跟效能提高。
    3. 事務處理複雜。
複製程式碼

水平切分

相對於垂直拆分的區別是：垂直拆分是把不同的表拆到不同的資料庫中，而水平拆分是把同一個表拆到不同的資料庫中。

優點：

    1. 不存在單庫大資料，高併發的效能瓶頸。
    2. 對應用透明，應用端改造較少。     
    3. 按照合理拆分規則拆分，join操作基本避免跨庫。
    4. 提高了系統的穩定性跟負載能力。
複製程式碼

缺點：

    1. 拆分規則難以抽象。
    2. 分片事務一致性難以解決。
    3. 資料多次擴充套件難度跟維護量極大。
    4. 跨庫join效能較差。
複製程式碼

小結

兩張方式共同缺點

    1. 引入分散式事務的問題。
    2. 跨節點Join 的問題。
    3. 跨節點合併排序分頁問題。
複製程式碼

目前主要有兩種思路：

    A. **客戶端模式**，在每個應用程式模組中配置管理自己需要的一個（或者多個）資料來源，直接訪問各個 資料庫，在模組內完成資料的整合。 
    優點：相對簡單，無效能損耗。   
    缺點：不夠通用，資料庫連線的處理複雜，對業務不夠透明，處理複雜。
   B. 通過**中間代理層**來統一管理所有的資料來源，後端資料庫叢集對前端應用程式透明；   
    優點：通用，對應用透明，改造少。   
    缺點：實現難度大，有二次轉發效能損失
複製程式碼

Mycat分庫分表

Mycat的架構其實很好理解，Mycat是代理，Mycat後面就是物理資料庫。和Web伺服器的Nginx類似。對於使用者來說，訪問的都是Mycat，不會接觸到後端的資料庫。

常用的分庫分表中介軟體

簡單易用的元件：

• 噹噹sharding-jdbc
• 蘑菇街TSharding

強悍重量級的中介軟體：

• sharding
• TDDL Smart Client的方式（淘寶）
• Atlas(Qihoo 360)
• alibaba.cobar(是阿里巴巴（B2B）部門開發)
• MyCAT（基於阿里開源的Cobar產品而研發）
• Oceanus(58同城資料庫中介軟體)
• OneProxy(支付寶首席架構師樓方鑫開發)
• vitess（谷歌開發的資料庫中介軟體）

具體實現可以參考下面這些文章：

• www.cnblogs.com/joylee/p/75…
• www.mycat.io/
• github.com/MyCATApache

更多優質文章

• www.cnblogs.com/sunny3096/p…
• www.cnblogs.com/mfmdaoyou/p…
• www.cnblogs.com/jshen/p/768…
• www.cnblogs.com/firstdream/…

最後

如果對 Java、大資料感興趣請長按二維碼關注一波，我會努力帶給你們價值。覺得對你哪怕有一丁點幫助的請幫忙點個贊或者轉發哦。 關注公眾號【愛編碼】 ，回覆 2019 有相關資料哦。