簡要說說不同的儲存引擎區別,主要說說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獨立出來,提高查詢效能
緩衝池
//我隨便找了個預設是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'
啟動,關閉,恢復
0 表示關閉資料庫時要完成所有的full purge,merge insert buffer,所有髒頁刷回磁碟,最後再關閉show variables like 'innodb_fast_shutdown'//0
相當於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 協議》,轉載必須註明作者和本文連結