《高性能MySQL》讀書筆記（上）

目錄

MySQL的架構

MySQL中的鎖

MySQL中的事務

事務特性

隔離級别

事務日志

多版本并發控制MVCC

影響MySQL性能的實體因素

InnoDB緩沖池

MySQL常用的資料類型以及優化

字元串類型  

日期和時間類型

資料辨別符

MySQL的架構

預設情況下，每個用戶端連接配接都會在伺服器程序中擁有一個線程，該連接配接的查詢隻會在這個單獨的線程中執行，該線程駐留在一個核心或者是CPU上，伺服器維護了一個緩存區，用來存放已經就緒的線程，是以不需要為每個新的連接配接建立或者是銷毀線程。

MySQL中的鎖

MySQL的鎖的粒度：

• 行鎖：并發大，系統開銷大（比如：核心态和使用者态之間的頻繁轉換）
• 表鎖：并發小，系統開銷小。

MySQL的讀寫鎖：

• 讀鎖：讀鎖也稱為共享鎖：并發通路共享資源的時候不進行阻塞，MySQL中的查詢語句是讀鎖，在查詢語句後面添加 for share /for update 可以為查詢語句加寫鎖。
• 寫鎖：也稱為排他鎖，并發通路共享資源時會出現阻塞，MySQL中增，删，改語句都是自帶寫鎖的。

注意：增删改是隐式上鎖，查詢語句要上鎖需要顯式上鎖。

死鎖：

死鎖是指兩個或者是多個事務互相持有和請求相同資源上的鎖，産生了循環依賴。

INnoDB目前處理死鎖的方式是将持有最少行級排他鎖的事務進行復原。實際上，InnoDB存儲引擎會幫我們先檢測鎖的情況，如果檢測到死鎖，那麼會立刻傳回一個錯誤的資訊。

一旦發生死鎖，如果不復原其中的一個事務，就無法打破死鎖。

MySQL中的事務

事務特性

MySQL事務：（MySQL中隻有INnoDB引擎才支援事務）

事務就是一組SQL語句，作為一個工作機關以原子方式進行處理，作為事務的一組語句，要麼全部執行成功，要麼全部執行失敗。

事務的四大特性：

• 原子性：一個事務必須被視為不可分割的工作單元，整個事務的所有操作要麼全部送出成功，要麼全部失敗復原。
• 一緻性：可以簡單的了解為守恒，資料庫總是從一個一緻性狀态轉換到下一個一緻的狀态。比如兩個賬戶進行轉賬，那麼轉賬前後這兩個賬戶裡面的總錢數都是一緻的。
• 隔離性：隔離性最常見的一種情況就是，一個事務所做的修改在最終送出以前，對其他事務都是不可見的。
• 持久性：事務一旦成功送出，那麼事務所做的修改就會被永久的儲存在資料庫中。

隔離級别

MySQL預設的隔離級别是可重複讀。

• 讀未送出（READUNCOMMITTED）：在事務中可以檢視到其他事務中還沒有送出的修改。這個級别的隔離一般很少使用。
• 讀已送出（READ COMMITTED）：一個事務可以看到其他事務在它開始之後送出的修改，在該事務送出之前，其所做的任何修改對其他事務都是不可見的。（這個級别仍然會出現不可重複讀，不可重複讀：同一事務中兩次執行相同SQL語句，可能會看到不同的資料結果）
• 可重複讀（REPEATABLE READ）：解決了讀已送出隔離級别的不可重複讀問題，保證了在同一個事務中多次讀取相同行資料結果是一樣的。但是不能解決幻讀問題。（幻讀：指的是當某個事務在讀取範圍内的記錄的時候，另一個事務又在該範圍内插入了新的記錄，當之前的事務再次讀取某個範圍内的記錄是，産生了幻行。）
• 可串行化（SERIALIZABLE）：前制事務按序執行，相當于加鎖阻塞，是以可能會導緻大量逾時和鎖競争的相關問題出現。在實際生産環境中也很少用到這個隔離級别。

隔離級别	是否出現髒讀	是否出現不可重複讀	是否出現幻讀
讀未送出	是	是	是
讀已送出	否	是	是
可重複讀	否	否	是
可串行化	否	否	否

事務日志

事務日志可以提高事務的效率，存儲引擎隻需要更改記憶體中的資料副本，而不是每次修改磁盤中的表，然後再把更改的記錄寫入事務中，事務日志會被持久化存在磁盤上。

因為事務日志采用的是追加寫的操作，是在硬碟中一小塊區域内的順序IO，而不是随機IO，是以寫入事務日志是一種相對比較快的操作，最後會有一個背景程序在某個時間去更新磁盤中的表。（記錄檔順序寫入磁盤一次，然後更新磁盤中的表一次，會操作兩次磁盤）

多版本并發控制MVCC

多版本并發控制（MVCC）：可以了解為行級鎖的一種變種，但是mvcc在很多情況下避免了加鎖操作，是以開銷更低。

簡要了解mvcc的思想和一些設計：MVCC的工作原理是使用資料在某個時間節點的快照來實作的；

意味着，無論事務運作多長時間，都可以看到資料的一緻視圖；也意味着不同僚務可以在同一時間看到同一張表的不同資料。

MySQL就是通過MVCC解決了幻讀的問題。

每個存儲引擎實作MVCC的方式都是不同的，InnoDB通過為每個事務在啟動時配置設定一個【事務ID】來實作MVCC，該ID在事務首次讀取任何資料的時候配置設定（隻讀事務ID永遠為0），後面每次操作資料都會影響這個事務ID的值，所有在下次來讀取的時候就可以通過循環來比較這個事務ID，進而傳回對應的視圖（視圖的存在類似一個連結清單，儲存不同時間的資料）。

注意：MVCC隻适用于【重複讀】和【讀已送出】這兩個事務的隔離級别。

InnoDB預設為可重複讀隔離級别，并且通過間隙鎖政策來防止在這個隔離級别上的幻讀：InnoDB不隻鎖定在查詢中涉及到的行，還會對索引結構中的間隙進行鎖定，以防止幻行被插入。

影響MySQL性能的實體因素

MySQL伺服器的性能受限于整個系統最薄弱的環節，承載MySQL伺服器的作業系統和硬體是最主要的限制因素。最常見的幾個就是：磁盤空間大小，可用記憶體，CPU，網絡，以及磁盤的材質（涉及到IO，是以能用固态硬碟那就盡量使用固态硬碟）。

為MySQL伺服器配置大記憶體，并不是為了在記憶體中儲存大量的資料，而是為了減少磁盤IO次數，磁盤IO通路資料比直接通路記憶體中的資料要慢幾個數量級。

MySQL的讀取，寫入，緩存：如果有足夠大的記憶體，是可以大幅度的減少磁盤IO的次數，因為如果資料都可以裝到記憶體的話，那麼伺服器一旦完成資料的緩存預熱，那麼每次讀取資料都是一次的緩存命中，在這種情況下，仍然會從記憶體中進行邏輯的讀取，但是不會從磁盤中進行實體的讀取資料。而且，寫入也是可以在記憶體中執行的，但是資料最後必然是會被寫入磁盤進行持久化的，也就是說，緩存可以延遲寫操作，但緩存不能像消除讀操作那樣消除寫操作的磁盤IO。

事實上，緩存的存在除了運作延遲寫操作外，還可以允許寫操作與其他方式組合起來一起使用。

• 多次寫操作，一次重新整理（這種設計不僅可以減少IO次數，還可以把寫入磁盤的随機IO變成順序IO）
  • 一個資料片段可以在記憶體中被多次更改，而無須每一次都将新值寫入磁盤。當資料最終被重新整理到磁盤時，自上次實體寫入以來發生的所有修改都将被持久化。
• IO合并
  • 許多不同的資料片段可以在記憶體中被修改，這些修改可以被收集在一起，是以實體寫可以作為單個磁盤操作來執行。

寫操作可以從緩存中收益，可以将随機IO轉變成順序IO。

記憶體與交換：

當作業系統因為沒有足夠的記憶體來容納虛拟記憶體時而将一些虛拟記憶體寫入磁盤時，就會發生交換。寫操作是縮短磁盤的整體壽命的，當然我們也可以關閉交換，這樣就可以完全消除交換帶來的負面影響，但是需要承擔因為記憶體耗盡導緻程序被終止的情況。

InnoDB緩沖池

InnoDB緩沖池不僅緩存索引，還緩存行資料，自适應哈希索引，更改緩沖區，鎖和其他内部結構等，InnoDB還是用緩沖池來實作延遲寫操作，進而可以将多個寫操作合并在一起并按順序執行。InnoDB嚴重依賴緩沖池，是以應該確定為其配置設定足夠的記憶體。

當然，大型的緩沖區也會帶來一些其他問題，比如更長的關閉時間和預熱時間，而且如果緩沖池中存在許多髒頁（被修改過的資料，還沒被同步到磁盤中，就是說記憶體中的資料與磁盤中的資料不一緻），那麼InnoDB可能需要很長時間才能關閉，因為它會在關閉的時候将髒頁寫到資料檔案中。

InnoDB預設使用同一個背景線程來重新整理髒頁，以及合并寫操作并按順序執行以提高效率，當髒頁的百分比超過設定的門檻值時，InnoDB會盡可能快的重新整理頁面，以降低髒頁的數量。

事務日志：InnoDB使用日志來降低送出事務的成本，它不會在每個事務送出時将緩沖池重新整理到磁盤，而是将事務記錄到日志中（使用追加的方式來記錄這個日志，避免了随機IO），使用這個事務日志，InnoDB可以将随機磁盤轉換為順序IO。InnoDB最終必須還是要将更改的資料寫入資料檔案，因為日志的大小是固定的，采取的是循環寫入的方式：當到達日志末尾的時候，它會環繞到日志的開頭，如果日志記錄中包含更改且尚未應用于資料檔案的操作，則會到值無法覆寫日志記錄，因為這将删除以送出事務的唯一永久記錄。

事務日志是使用連續的磁盤空間來進行記錄的，InnoDB引擎中，在事務送出的時候，必須先将該事務的所有事務日志寫入到磁盤上的redoLog File和undoLog File 中進行持久化。

日志緩沖區：InnoDB修改資料時會将修改記錄寫入到日志緩沖區，并将其儲存在記憶體中，當緩沖區滿了，事務送出時，或者每秒一次（這三個條件以先滿足為準），InnoDB會将緩沖區重新整理到磁盤上的日志檔案中。與InnoDB的普通資料相比，日志的條目非常緊湊。

InnoDB如何重新整理日志緩沖區：

使用互斥鎖鎖定緩沖區，将其重新整理到所需的位置，如何将剩餘的條目移動到緩沖區的前面，當釋放互斥鎖時，可能會有多個事務準備重新整理其日志條目，InnoDB使用了一組送出特性，可以在單次IO操作将一組日志全部送出。日志緩沖區必須被重新整理到持久存儲中，以確定送出的事務完全的持久。

InnoDB_flush_log_at_trx_commit可以用來控制日志緩沖區的重新整理位置和重新整理頻率：

• 0：每秒定時将日志緩沖區寫入日志檔案，并且重新整理日志檔案，但在事務送出時不做任何操作。
• 1：每次事務送出時，将日志緩沖區寫入日志檔案，并将其重新整理到持久存儲中，這是預設的設定（也是最安全的設定）；
• 2：每次事務送出時都将日志緩沖區寫入日志檔案，但不執行重新整理。InnoDB按計劃每秒重新整理一次。與0設定最主要的差別是，如果隻是MySQL程序崩潰，設定2不會丢失任何事務，但是，如果整個伺服器崩潰或者是斷電，仍然可能丢失事務。

注意：在大多數作業系統中，将緩沖區寫入日志隻是将資料從InnoDB的記憶體緩沖區移動到作業系統的緩存中，依然還是在記憶體中，它實際上不會将資料寫入到持久存儲，是以，如果發生崩潰或者是斷電，設定為0和2通常會導緻最多一秒的資料丢失，因為資料可能隻存在作業系統的緩存中。

MySQL常用的資料類型以及優化

字元串類型  

可變字元串varchar：

• varchar用于存儲可變長度的字元串，比固定長度的類型更節省空間，因為它僅使用必要的空間。

  varchar需要額外使用1或者是2個位元組來記錄字元串的長度，如果列的最大長度小于或者是等于255位元組，則隻使用一個位元組表示，否則使用兩個位元組進行表示。比如varchar(10)需要11個位元組的存儲空間，而varchar(1000)需要1002個位元組的存儲空間。

  注意：由于行是可變長度，在更新資料的時候可能會增長，這會導緻額外的工作，如果行的增長使原來位置無法容納跟多内容，則具體的處理行為取決于使用的存儲引擎。例如，innodb可能會需要分割頁面來容納行。

下面這些情況使用varchar是比較好的：

• 字元串的最大長度遠大于平均長度；
• 列的更新很少，可以避免記憶體碎片的産生。

固定字元串char：

• char是固定的長度，MySQL總是為定義的字元串長度配置設定足夠的空間，當存儲char值時，MySQL會删除所有尾随的空格；

char适用于存儲非常短的字元串，或者是适用于所有值的長度幾乎相同的情況下。固定長度不容易産生記憶體碎片。

日期和時間類型

DATETIME：這種類型可以儲存大範圍的數值，從1000年到9999年，精度為1微秒。它以YYYYMMDDHHMMSS格式存儲壓縮壓縮成整數的日期和時間，且與時區無關。但是需要8個位元組進行存儲。

TIMESTAMP（時間戳）：存儲自1970年1月1日格林尼治标準時間午夜以來經過的秒數，隻需要4個位元組就可，是以範圍要比datatime要小得多，隻能表示1970年到2038年1月19日，時間戳的時間依賴于時區。

資料辨別符

資料辨別符：一般來說，辨別符是資料行的唯一辨別。比如我們最常見的ID就是最常見的辨別符。辨別符可能是主鍵中的部分或者是全部。

為辨別符選擇好資料類型後，要確定在所有相關的表中使用的是相同的資料類型，否則在進行多表聯查的時候就可能會出問題（辨別符應該與聯接表中的對應列的資料類型保持一緻）。

• 整數類型：整數通常是辨別符的最佳選擇，因為他們的速度快，并且可以自動遞增。AUTO_INCREMENT是一個列屬性，可以為新的行自動的生成一個整數類型的值。但是這種類型的辨別符也有缺點：整數類型可能存在整數意外耗盡的情況，進而導緻伺服器停機，是以如果選擇了整數類型的資料作位辨別符，那麼應該確定選擇合适預期資料增長的整數大小。
• 字元串類型：如果可能，應該盡可能的避免使用字元串類型作為辨別符的資料類型，因為它們非常消耗空間，并且通常比整數類型慢，特别是在有索引的情況下。

另外，對于完全随機的字元串要特别小心，如MD5(),SHA1()或者是UUID()生成的字元串，這些函數生成的新值會任意分布在很大的空間内，這會減慢insert和某些類型的select查詢的速度。

• 因為插入的值會寫到索引的随機位置，是以會使得INSERT查詢變慢，這會導緻頁分裂，磁盤随機通路，以及對聚簇存儲引擎産生聚簇索引碎片。
• select查詢也會變慢，因為邏輯上相鄰的行（指的是記憶體中的資料）會廣泛分布在磁盤和記憶體中。
• 對于所有類型的查詢，随機值都會導緻緩存的性能低下，因為它們會破壞引用的局部性，而這正是緩存的工作原理。

MySQL會對null進行索引，但是oracle不會。