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

簡要說說不同的儲存引擎區別,主要說說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

各引擎比較

二. INNODB

執行緒模型

Innodb是多執行緒模型

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

緩衝池

//我隨便找了個預設是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 協議》，轉載必須註明作者和本文連結