標籤：blog 監控 時機 二次 ffffff 結果 通過 根據 sql
一、mysql架構
mysql是一個單進程多線程架構的資料庫。
二、儲存引擎
InnoDB：
- 支援事務
- 行鎖
- 讀操作無鎖
- 4種隔離等級，預設為repeatable
- 自適應hash索引
- 每張表的儲存都是按主鍵的順序記性存放
- 支援全文索引（InnoDB1.2.x - mysql5.6）
- 支援MVCC（多版本並發控制）實現高並發
MyISAM：
三、InnoDB體系架構
1、後台線程
- Master Thread
- 負責將緩衝池中的資料非同步重新整理到磁碟，保證資料的一致性
- IO Thread
- Purge Thread
- 回收已經使用並分配的undo頁（事務提交後，其所使用的undolog不再需要）
2、記憶體池
- 緩衝池（一塊記憶體地區）
- InnoDB基於磁碟儲存，將記錄按照頁的方式進行管理（由於基於磁碟，速度較慢，所以需要引入緩衝池提高效能）
- 讀取頁：先從緩衝池擷取，緩衝池沒有，才會從磁碟擷取
- 修改頁：先寫重做日誌緩衝，再修改緩衝池中的頁，然後以一定的頻率重新整理到磁碟（Checkpoint機制），在還沒有重新整理到磁碟之前，該頁被稱為髒頁
- innodb_buffer_pool_size設定大小
- 存放對象：索引頁、資料頁、自適應hash索引和lock資訊
- 緩衝池可以配置多個（innodb_buffer_pool_instances），每個頁根據hash值平均分配到不同的緩衝池執行個體中，用於減少資料庫內部資源競爭
- LRU List
- 將最新的頁放在隊列前端，最近最少使用的放在尾端，當緩衝池不夠用時，將尾端的頁刪除出緩衝池（如果此頁是髒頁，會先重新整理到磁碟）。innodb採用的是midpoint技術進行LRU，具體參看《MySQL技術內幕 InnoDB儲存引擎》
- Flush List
- 重做日誌redolog緩衝
- 為了防止髒頁在重新整理到磁碟時宕機，必須先redolog，再修改頁；
- 資料庫發生宕機時，通過redolog完成資料的恢複（ACID-D持久性）
- 預設大小8M，通過innodb_log_buffer_size
- 將redolog緩衝重新整理到redolog檔案中的時機
- master會將redolog緩衝每隔1s重新整理到redolog檔案中
- 每個事物提交
- redolog緩衝池剩餘空間小於1/2
- Checkpoint
- 緩衝池不夠用時，將髒頁重新整理到磁碟
- 資料庫宕機時，只需要重做Checkpoint之後的日誌，縮短資料庫的恢復
- redolog不可用時，將髒頁重新整理到磁碟
四、InnoDB邏輯儲存結構
1、資料表空間
- 預設情況下，只有一個資料表空間ibdata1，所有資料存放在這個空間內
- 如果啟用了innodb_file_per_table，則每張表內的資料可以單獨放到一個資料表空間內
- 每個資料表空間只存放資料、索引和InsertBuffer Bitmap頁，其他資料還在ibdata1中
2、Segment段（InnoDB引擎自己控制）
- 資料區段：B+ tree的葉子節點
- 索引段：B+ tree的非葉子節點
- 復原段
3、Extent區
- 每個區的大小為1M，頁大小為16KB，即一個區一共有64個連續的頁（區的大小不可調節，頁可以）
4、Page頁
- InnoDB磁碟管理的最小單位
- 預設每個頁大小為16KB，可以通過innodb_page_size來設定（4/8/16K）
- 每個頁最多存放7992行資料
5、Row行
五、索引
1、hash索引
- 定位元據只需要一次尋找，O(1)
- 自適應hash索引：InnoDB會監控對錶上各個索引頁的查詢，如果觀察到建立hash索引可以帶來速度提升，則建立hash索引（即InnoDB會自動的根據訪問頻率和模式來自動的為某些熱點頁建立hash索引）
- 預設是開啟的
- 只可用於等值查詢，不可用於範圍查詢
2、B+樹索引
- 樹的高度一般為2~4層，需要2~4次查詢（100w和1000w行資料，如果B+ tree都是3層，那麼查詢效率是一樣的）
- B+樹索引能查到的是資料行所在的頁
- 包含叢集索引和輔助索引
3、叢集索引
- 即主鍵索引
- 葉子節點存放的是行記錄資料所在的頁，而頁中的每一行都是完整的行（葉子節點也被稱為資料頁）
- 針對範圍查詢也比較快
叢集索引圖：
其中，根節點部分的Key:80000001代表主鍵為1；Pointer:0004代表指向資料頁的頁號（即第4頁）；
資料頁節點的的PageOffset:0004代表第4頁，其中儲存的資料是完整的每一行。
4、輔助索引
- 葉子節點存放的也是行記錄資料所在的頁，但還是頁中存放的不是完整的行，而是僅僅是一對key-value和一個指標，該指標指向相應行資料的叢集索引的主鍵
- 假設輔助索引樹高3層，叢集索引樹為3層，那麼根據輔助索引尋找資料，需要先經過3次IO找到主鍵，再經過3次IO找到行做在的資料頁
- 針對輔助索引的插入和更新操作：輔助索引頁如果在緩衝池中，則插入；若不在，則點放到InsertBuffer對象中，之後在以一定的平率進行InsertBuffer和輔助索引頁子節點的合并
輔助索引圖：
其中，idx_c表示對第c列做了索引；idx_c中的Key:7fffffff代表c列的一個值，其實是-1；idx_c中的Pointer:80000001代表該行的主鍵是80000001，即1；下面的就是叢集索引部分。
5、聯合索引（多列索引）
- 左邊匹配原則（如果索引為(a,b)，則where a=x可以用到索引，但是b=x用不到，如果是覆蓋索引有可能會用到）
6、覆蓋索引
- 從輔助索引中直接擷取記錄
- 對於統計操作，例如count(1)，有可能聯合索引，右邊也會匹配（最佳化器自己會做），因為count(1)操作不需要擷取整行的詳細資料，所以不需要去叢集索引的葉子節點去擷取資料，直接在輔助索引樹中就完成了操作
- select username from xxx where username=‘lisi‘，如果username是輔助索引，那麼整個查詢在輔助索引樹上就可以完成，因為輔助索引樹上雖然沒有儲存完整的行，但是儲存著<username,lisi>這個key-value對；如果select username, age from xxx where username=‘lisi‘，那麼就要走叢集索引了
六、鎖
1、latch
- 保證並發線程操作臨界資源的正確性
- 自旋鎖，自旋指定的次數後，若還沒擷取到鎖，則進入等待狀態，等待被喚醒
2、lock
- 事務鎖，鎖定的可能是表、頁或行
- 釋放點：事務commit或rollback
- 兩種標準的行級鎖
- 共用鎖定：S lock，事務T1擷取了r行的S鎖，事務T2也可以擷取r行的S鎖
- 獨佔鎖定：X lock，事務T1擷取了r行的S鎖，事務T2就不能擷取r行的X鎖；事務T1擷取了r行的X鎖，事務T2就不能擷取r行的X/S鎖
七、事務
1、隔離等級
- 讀不提交
- 讀並且提交
- 可避免髒讀：一個事務讀到另一個事務沒有提交的資料，如果另一個事務發生復原，第一個事務讀到的資料就是垃圾資料
- 可重複讀
- 會有幻讀，InnoDB通過Next-Key Lock解決了
- 幻讀：指兩次執行同一條 select 語句會出現不同的結果，第二次讀會增加一資料行，並沒有說這兩次執行是在同一個事務中。使用表鎖即可避免。
- 可避免不可重複讀取：在同一個事務中兩條一模一樣的 select 語句的執行結果的比較。如果前後執行的結果一樣，則是可重複讀；如果前後的結果可以不一樣，則是不可重複讀取。通常是發生了update。增加讀取時的共用鎖定（禁止修改）即可避免。
- 預設的交易隔離等級
- 序列化
《mysql技術內幕 InnoDB儲存引擎（第二版）》閱讀筆記
