[Mysql技術內幕]Innodb儲存引擎

豎橫山發表於2021-05-19
MySql儲存引擎

簡要說說不同的儲存引擎區別,主要說說INNODB

一. 其他引擎

MyISAM

  • 不支援事務,表鎖,支援全文索引
  • Mysql5.5.8版本以前是預設儲存引擎,之後被無情拋棄
  • 儲存引擎表由MYD,MYI組成,MYD用來存放資料,MYI存索引
  • 可以使用myisampack工具進一步壓縮資料檔案,使用哈夫曼編碼靜態演算法來壓縮資料
  • Mysql5.0以前表容量只支援4GB,之後為256TB
  • 只快取索引檔案,檔案的快取交給作業系統自身完成

NDB

  • 是一個叢集儲存引擎,資料全部存在記憶體中,主鍵查詢速度極快,JOIN操作查詢速度很慢

Memory

  • 將表中的資料存放在記憶體中,如果資料庫重啟或崩潰,資料將丟失
  • 非常適合儲存臨時資料表,以及經緯度表
  • 預設使用雜湊索引,而不是B+Tree索引
  • 只支援表鎖,併發效能較差,不支援TEXT BLOB
  • varchar是按照char方式儲存,浪費記憶體

Archive

  • 只支援INSERT SELECT操作
  • 使用zlib演算法將資料行進行壓縮後儲存,壓縮比1:10
  • 非常適合儲存歸檔資料,如日誌資訊

Federated

  • 不存放資料
  • 指向遠端MYSQL資料庫的某個表

Maria

  • 升級版的MyISAM 支援了行鎖,MVCC,事務,BLOB

各引擎比較

特徵 MyISAM BDB Memory InnoDB Archive NDB
容量限制 無 無 有 64TB NO YES
事務 √ √
鎖粒度 表 頁 表 行 行 行
MVCC √ √ √
地理空間 √
BTREE索引 √ √ √ √ √
雜湊索引 √ √ √
全文索引 √ √
聚簇索引 √
資料快取 √ √ √
索引快取 √ √ √ √
壓縮資料 √ √
加密資料 √ √ √ √ √ √
儲存成本 低 低 無 高 非常低 低
記憶體成本 低 低 中等 高 低 高
批量插入速度 高 高 高 低 非常高 高
叢集 √
主從複製 √ √ √ √ √ √
外來鍵 √
備份/定點恢復 √ √ √ √ √ √
查詢快取 √ √ √ √ √ √
更新統計資訊 √ √ √ √ √ √

二. INNODB

執行緒模型

Innodb是多執行緒模型

  • Master Thread:主執行緒,負責緩衝池非同步重新整理到磁碟,髒頁重新整理,合併插入緩衝,UNDO頁回收
  • IO Thread: insert buffer,log IO thread,4個write,4個read
  • Purge Thread: 回收undo頁,支援多個縣城
  • Page Cleaner Thread: 髒頁重新整理,從Master獨立出來,提高查詢效能

緩衝池

[Mysql技術內幕]儲存引擎

//我隨便找了個預設是0.5G
SHOW VARIABLES LIKE 'innodb_buffer_pool_size'
  • 可以快取索引頁,資料頁(前兩個佔大部分),undo頁,插入緩衝,自適應雜湊索引,鎖資訊,資料字典資訊
  • 允許多個緩衝池例項,每個頁根據雜湊值平均分配到不同緩衝池,增加併發處理能力,
    //預設8
SHOW VARIABLES LIKE 'innodb_buffer_pool_instances'
//檢視緩衝池狀態
SELECT * FROM information_schema.INNODB_BUFFER_POOL_STATS;
  • LRU演算法管理緩衝池,頻繁使用的頁在最前面,使用少的在尾部,緩衝池滿了之後,優先釋放尾部資料
  • InnoDB對LRU演算法進行了優化,加入了innodb_old_blocks_pct中間點概念,新的資料會插入LRU列表的37/100處,新的資料不一定是熱點資料,如果讓一次性的,資料量非常大的資料放在LRU頭部沖走熱點資料就很不爽,可以適當調節這個值以適應熱點資料.
    show VARIABLES like 'innodb_old_blocks_pct'    //返回37
  • 檢視緩衝池狀態
    SHOW ENGINE INNODB STATUS
###################################
Dictionary memory allocated 8904297
Buffer pool size   32767#共有32767個頁
Free buffers       1024    #空閒區頁的數量
Database pages     31564#LRU列表中頁的數量
Old database pages 11631
Modified db pages  0  #髒頁頁數
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 50762857, #LRU列表頁一定要前端的次數
not young 711064607##LRU列表頁一定要尾部的次數
  • redo log buffer 重做日誌緩衝區
    重做日誌的定義:事務提交的時候,先寫重做日誌,再去修改頁,如果發生當機,可以通過重做日誌修復資料
    show VARIABLES like 'innodb_log_buffer_size'    //預設8M

Check Point 技術

  • 將緩衝區的髒頁刷回磁碟
  • 有點複雜 大概就是緩衝區和磁碟同步資料的一種技術 當機也能恢復那種
  • 有好多種情況觸發這個東西

Master Thread 主執行緒工作方式

  • 主迴圈
  • 後臺迴圈
  • 重新整理迴圈
  • 暫停迴圈

關鍵特性

  • 插入緩衝 Insert Buffer
    對於非聚簇索引進行插入和更新操作時,不是每一次都直接插入索引頁中,而是先判斷操作的非聚簇索引頁是否在緩衝池中,若在,則直接插入,若不在則放到Insert Buffer中,延時刷入磁碟,大大的增加插入更新操作的效能
    唯一索引不會插入Insert Buffer
    Master Thread定時把緩衝區資料刷回磁碟
  • 兩次寫
    doublewrite buffer 避免當機造成的資料丟失問題
  • 自適應雜湊索引
    雜湊索引的時間複雜度是O(1),而B+Tree索引一般為3-4層,
    如果觀察到建立雜湊索引可以帶來速度提升,則全自動建立雜湊索引,稱之為自適應雜湊索引,預設開啟
    雜湊索引只允許等值搜尋查詢,範圍查詢是不能使用雜湊索引的
  • 非同步IO Aysnchronous IO 即AIO
    如果一條SQL需要掃描多個索引頁,也就是進行多次IO操作,AIO進行IO Merge操作,將多個IO合併成1個IO,提高效能
  • 重新整理鄰接頁
    當重新整理一個髒頁到磁碟時,順手把隔壁的髒頁也重新整理了,
    預設開啟,如果磁碟是固態硬碟則建議設定為0關閉此特性
    show variables like 'innodb_flush_neighbors'

    啟動,關閉,恢復

    show variables like 'innodb_fast_shutdown'//0
    0 表示關閉資料庫時要完成所有的full purge,merge insert buffer,所有髒頁刷回磁碟,最後再關閉
    相當於7仔店關門前,要做大掃除.
    1 不需要full purge,merge insert buffer,但是髒頁要刷回磁碟,最後再關閉
    2 將日誌寫入日誌檔案,下次啟動MYSQL時再恢復 (RECOVERY),相當於有急事,拉閘關門明早再說
    當機也會觸發RECOVERY
    show variables like 'innodb_force_recovery'//0
    恢復的等級
    0 進行所有的恢復操作,如果恢復不了,報錯寫入日誌
    3 不進行事務回滾
    6 不進行任何回滾
    假如事務執行了幾十萬條,COMMIT之前當機了,那麼開機要對這幾十萬條資料進行回滾,可能要幾個小時說不定,這個時候就要設定innodb_force_recovery等級為3,才能光速開機,然後再重新處理這幾十萬條資料

日誌檔案

錯誤日誌

當MYSQL無法正常啟動時,來檢視這個檔案,有時會有警告,可能還能得到一些優化的幫助

show variables like 'log_error'

慢查詢日誌 slow log

##全域性修改
set global slow_query_log='ON'; //開啟慢查詢
set global long_query_time = 1; //設定閾值,大於1秒記錄
set global log_output = 'TABLE' //預設是FILE,改成表好查一點,如果要被日誌系統收集,建議還是預設FILE,設定完之後可以在mysql.slow_log查到日誌
set global log_queries_not_using_indexes = 'ON'//不使用索引的記錄到慢查詢,開啟之後資料量有點大,而且slow_log表裡面貌似沒有欄位標誌出是因為沒走索引而記錄
set global log_throttle_queries_not_using_indexes ##每分鐘允許記錄到slow_log的且未使用索引的SQL語句次數

mysql.slow_log 預設使用CSV引擎,可以改成MyISAM提高查詢效能

查詢日誌

set global general_log = 'ON'  //預設關閉, 開啟之後記錄所有SQL 對效能有影響
set global log_output = 'TABLE' //慢查詢日誌和查詢日誌共用log_output,設定完之後可以在mysql.general_log查到日誌

二進位制日誌 binary log

除了SELECT和SHOW操作,其他對資料庫執行更改的所有操作,開啟binlog會造成1%的效能損失,但是好處多多,主要有以下幾種作用

  • 恢復 可以進行point-in-time恢復
  • 複製 主從複製
  • 審計 判斷是否被注入攻擊
    相關配置引數
  • max_binlog_size
    單個binlog的最大值,超過該值會產生新的二進位制日誌檔案,字尾名+1,並記錄到.index檔案,預設1G
  • binlog_cache_size
    預設大小32KB,二進位制日誌佔用緩衝區的大小,每個執行緒開啟一個事務的時候,都會在緩衝區分配32KB,如果設定太大,同一時間多個事務發起的時候,非常消耗緩衝區的記憶體,如果設定太小,溢位的日誌將會寫到臨時檔案中
  • sync_binlog
    二進位制日誌預設先寫到緩衝區,sync_binlog = N表示每寫緩衝N次刷入磁碟的二進位制檔案中,如果使用InnoDB進行主從複製,想保證萬無一失就把這個值設定為1,設定為1也是有問題的,比如一個事務COMMIT之前,二進位制已經寫入的磁碟中,然後當機,重啟之後事務回滾,但是不會回滾二進位制日誌,這個問題可以通過innodb_support_xa=1來解決
  • binlog-do-db
    決定哪些庫需要儲存二進位制日誌
  • binlog-ignore-db
    決定哪些庫不需要儲存二進位制日誌
  • log-slave-update
    從庫不會將從主庫取得的二進位制日誌寫入自己的二進位制日誌中,除非這個值等於1,設定為1就可以實現Master=>slave=>slave 的架構
  • binlog_format
    statement 記錄sql語句
    row 記錄每一行被修改的記錄 對磁碟空間要求大 傳輸binlog網路開銷也大 推薦使用這個,為資料庫恢復和複製帶來更好的可靠性
    mixed 上面兩種都有

表結構空間檔案

  • .frm 儲存表結構和檢視檔案 (MYSQL8已經去除了frm檔案)
  • .ibd 獨立表的資料檔案
  • ib_logfile0 & ib_logfile1 InnoDB重做日誌檔案

    重做日誌 redo log

    binlog VS redolog, binlog是mysql層的日誌,redolog是innodb儲存引擎層的日誌,記錄的是每個頁的更改物理情況

索引組織表

  • 如果沒有定義主鍵,Innodb會選建表時候定義的第一個非空單列唯一索引當主鍵
    SELECT _rowid FROM `table`    //查詢主鍵列的值

表空間 段 區 頁 行

  • 表空間
    如果啟用了innodb_file_per_table,每張表內的資料單獨放到一個表空間,存放資料,索引和插入緩衝bitmap頁,其他undo資訊,插入緩衝索引頁,事務資訊,二次寫緩衝還是放外面的共享表(ibdata1)中
  • 段
    常見有資料段,索引段,回滾段
  • 區
    連續頁組成的空間,每個區大小都是1MB
  • 頁
    頁可以設定成16kb 8kb 4kb 2kb
    資料頁,undo頁,系統頁,事務資料頁,插入緩衝點陣圖頁,插入緩衝空閒列表頁,未壓縮的二進位制BLOB,已壓縮的BLOB
  • 行
    每頁只能存7992行記錄

行記錄格式

  • compact redundant
    原始的行記錄格式,過時了
  • compressed dynamic
    MYSQL5.7之後預設的記錄格式是dynamic
    compact處理blob,text時,只存前面的768個位元組,剩下資料另外找頁放
    dynamic處理blob,text時,資料行只存放指標,實際資料放在off page中
    compressed是dynamic 的zlib演算法壓縮版 用cpu算力換磁碟容量變小,據說能節省40%的空間

char 行結構儲存

char指的是字元長度,非位元組長度
在InnoDB儲存引擎內部,不同編碼下的字元佔用的位元組不同,英文佔用一個位元組,utf-8最長佔用四個位元組,所以對char(10)來說,可以儲存10個英文字母,也可以儲存10箇中文漢字,所以char和varchar的實際行儲存基本是沒有區別的

InnoDB資料頁結構

  • File Header 38位元組
    FIL_PAGE_OFFSET:表空間頁的偏移值,假如表空間1G,大小為16KB,那麼總共有65536個頁,FIL_PAGE_OFFSET表示該頁在所有頁中的位置
    FIL_PAGE_PREV:當前頁的上一個頁
    FIL_PAGE_NEXT:當前頁的下一個頁
    FIL_PAGE_TYPE:InnoDB引擎頁的型別,可以是葉節點,undolog,索引節點,insert buffer等
    FIL_PAGE_ARCH_LOG_NO_OR_SPACE_ID:屬於哪個表空間
  • Page Header
    記錄資料頁的狀態資訊
  • Infimun和Supermum Records
    限定記錄的邊界
  • User Records
    資料部分
  • Free Space
    空閒部分
  • Page Directory
    頁的相對位置
  • File Trailer
    檢測頁是否已經完整地寫入磁碟

讀書遇到瓶頸 先去惡補其他知識回來再戰

本文持續更新修正

本作品採用《CC 協議》，轉載必須註明作者和本文連結

相關文章