mysql技术innodb存储引擎读后感_《Mysql技术内幕-InnoDB存储引擎》读书笔记 （一）...
@(Mysql)
官方數(shù)據(jù)庫
下載
導(dǎo)入/data/mysql57/bin/mysql --socket /data/mysql3306/mysql.socket -uroot -ppassword1 < empoo.sql
虛擬機(jī)啟動(dòng)
關(guān)閉iptables
開啟sshd
修改root密碼
mkdir /data
mount /dev/sda1 /data
一、Mysql存儲(chǔ)引擎
Mysql概念
數(shù)據(jù)庫。是文件的集合。這些文件保存數(shù)據(jù)庫的數(shù)據(jù)。
實(shí)例。是一個(gè)進(jìn)程，用于管理數(shù)據(jù)庫。
Mysql組成：
連接池組件
管理服務(wù)和工具組件
SQL接口組件
查詢分析器組件
優(yōu)化器組件
緩存組件
插件式存儲(chǔ)引擎
物理文件
存儲(chǔ)引擎
InnoDB
支持事務(wù)
行鎖設(shè)計(jì)
支持外鍵
支持MCVV，實(shí)現(xiàn)4種隔離級(jí)別
表的行按照主鍵順序存放
最常用的數(shù)據(jù)庫引擎。5.5后的默認(rèn)引擎
MyISAM
5.5前的默認(rèn)
不支持事務(wù)
表鎖設(shè)計(jì)
只緩存索引文件，不緩存數(shù)據(jù)文件
可以使用myisampack工具進(jìn)一步壓縮。但是壓縮后只讀。
NDB
集群數(shù)據(jù)庫
數(shù)據(jù)放在內(nèi)存
高可用和并發(fā)
Memory
數(shù)據(jù)放在內(nèi)存
Mysql臨時(shí)表會(huì)用這個(gè)引擎
不支持TEXT和BLOB字段。所以如果臨時(shí)表要用到這兩個(gè)字段，會(huì)使用MyISAM引擎，而該引擎不緩存數(shù)據(jù)在內(nèi)存，所以性能會(huì)有影響。
Archive
只支持INSERT SELECT操作
壓縮后存放
適用于高速插入和壓縮功能。例如日志的存儲(chǔ)
Federated
不存放數(shù)據(jù)，指向遠(yuǎn)程Mysql的表
Maria
用于替換MyISAM，
支持緩存和索引
行鎖設(shè)計(jì)
MVCC功能
連接數(shù)據(jù)庫的方式
TCP/IP
命令管道或共享內(nèi)存
UNIX 域套接字，就是--socket的參數(shù)。
二、InnoDB體系結(jié)構(gòu)
體系結(jié)構(gòu)由：
后臺(tái)線程
存儲(chǔ)引擎內(nèi)存池
文件
組成
后臺(tái)線程
Master Thread 。負(fù)責(zé)將緩沖池的數(shù)據(jù)異步刷新到磁盤，包括
臟頁的刷新
合并插入緩存(INSERT BUGGER)
UNDO頁的回收
IO Thread InnoDB大量使用異步IO(AIO)來處理請求，來提高數(shù)據(jù)庫性能。
有4類IO線程。
write
read
insert buffer
log
除了write和read，其他線程都是只有一個(gè)，通過show variables like 'innodb_%io_threads'來查看write和read的線程數(shù)
通過show engine innodb status來查看IO線程的情況
Purge Thread。事務(wù)被提交后，undolog就不需要了，PurgeThread用來回收已經(jīng)分配的undo頁
Page Cleaner Thread 1.2以后引入，用于回收臟頁。
內(nèi)存
緩沖池
Mysql會(huì)把數(shù)據(jù)存儲(chǔ)在硬盤，為了性能，引入了緩沖池，也就是一塊內(nèi)存區(qū)域。
Mysql的數(shù)據(jù)都是按頁來存儲(chǔ)的。
從硬盤讀取頁后，首先放到緩沖池。
修改數(shù)據(jù)后，會(huì)修改緩沖池的數(shù)據(jù)，然后定期同步到硬盤
通過show variables like 'innodb_buffer_pool_size'來查看緩沖池的大小，單位是字節(jié)。
緩沖池的數(shù)據(jù)類型有：
索引頁
數(shù)據(jù)頁
undo頁
插入緩沖頁，insert buffer
自適應(yīng)哈希索引 adaptive hash index
鎖信息(lock info)
數(shù)據(jù)字典信息(data dictionary)
Mysql支持多個(gè)緩沖池，通過show variables like 'innodb_buffer_pool_instances'來查看
LRU List 、Free List、FlushList
緩沖池是一個(gè)很大的內(nèi)存區(qū)域，存儲(chǔ)各種各樣的頁，頁的默認(rèn)大小是16KB。
緩沖池的數(shù)據(jù)頁由下面3個(gè)列表組成，列表的item都是頁。
LRU List 主要存儲(chǔ)數(shù)據(jù)頁
Free List 存儲(chǔ)空閑的頁
Flush List 存儲(chǔ)臟頁
LRU List
LRU List使用LRU(Lastest Recent Used)算法：
使用最頻繁的放在列表前端
使用最少的放在末端
當(dāng)緩沖池不夠的時(shí)候，優(yōu)先釋放末端的頁
新頁進(jìn)入緩沖池后，放在末端開始37%的位置，這個(gè)位置稱為midpoint。通過show variables like 'innodb_old_blocks_pct'來查看。
midpoint前的稱為new，是最活躍的數(shù)據(jù)
后的稱為old，是最不活躍的數(shù)據(jù)
如果緩沖池已滿，刪除列表末端的頁
頁從old升級(jí)為new，稱為page made yound
頁沒有從old升級(jí)為new(應(yīng)該指一直在old中，直至被刪除)，稱為page not made yound
Free List
數(shù)據(jù)庫啟動(dòng)時(shí)，由于緩沖池是空的，這時(shí)頁都存儲(chǔ)在Free列表中(注意Free列表中的頁都是沒有數(shù)據(jù)的，或者數(shù)據(jù)已沒有用)
當(dāng)需要放新的一頁到緩沖池：
* 首先查看Free列表是否有空閑的頁
* 如果有，使用
* 如果沒有。從LRU列表中刪除末端頁。
Flush List
當(dāng)數(shù)據(jù)被修改后，會(huì)直接寫重做日志和修改緩沖池?cái)?shù)據(jù)，然后直接返回事務(wù)執(zhí)行成功，這時(shí)候數(shù)據(jù)還沒有落到硬盤的。(如果這時(shí)候數(shù)據(jù)庫宕機(jī)，可以通過重做日志來恢復(fù)數(shù)據(jù))。
所以就會(huì)存在一種狀態(tài)，就是緩沖池的數(shù)據(jù)和硬盤的數(shù)據(jù)是不一致的。這時(shí)候緩沖池的這一頁稱為臟頁(注意是緩沖池的數(shù)據(jù)比硬盤新)。
臟頁就存放在Flush List中，臟頁也會(huì)存儲(chǔ)在LRU List中。
Mysql會(huì)定期把Flush List的臟頁同步到硬盤，這個(gè)操作叫Checkpoint。
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137428992
Dictionary memory allocated 102398
Buffer pool size 8191
Free buffers 7891
Database pages 300
Old database pages 0
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 266, created 34, written 36
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 300, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
通過show engine innodb status命令可以查看InnoDB的當(dāng)前狀態(tài)。
BUFFER POOL AND MEMORY 是緩沖池信息
Buffer pool size 8191 是緩沖池總大小，單位是頁數(shù)量
Free buffers 7891 Free List的大小
Database pages LRU List的大小
Old database pages LRU List中old部分的大小
Modified db pages Flush List的大小
LRU len: 300, unzip_LRU len: 0 LRU List的大小和壓縮數(shù)據(jù)列表的大小
Checkpoint
當(dāng)事務(wù)提交時(shí)：
寫重做日志
修改緩沖池的頁數(shù)據(jù)
定期執(zhí)行checkpoint，把緩沖池的臟頁刷新到硬盤
執(zhí)行checkpoint的時(shí)機(jī)：
Sharp Checkpoint 在數(shù)據(jù)庫關(guān)機(jī)的時(shí)候執(zhí)行。
Fuzzy Checkpoint。數(shù)據(jù)庫在運(yùn)行時(shí)定期執(zhí)行
Master Thread Checkpoint 由主線程執(zhí)行，每1秒或者10秒執(zhí)行一次
FLUSH_LRU_LIST Checkpoint
Async/Sync Flush Checkpoint
Dirty Page too much Checkpoint
Master Thread
Master Thread有多個(gè)循環(huán)：
主循環(huán)
后臺(tái)循環(huán)
刷新循環(huán)
暫停循環(huán)
主循環(huán)包含兩個(gè)循環(huán)：1秒和10秒(不一定準(zhǔn)確是1秒或者10秒，有可能有延遲)
1秒
日志緩沖刷新到硬盤，即使事務(wù)還沒有提交
如果前1秒的系統(tǒng)IO小于5，合并插入緩存(Insert Buffer)
如果緩沖池臟頁比例大于innodb_max_dirty_pages_pct參數(shù)，刷線100個(gè)臟頁到磁盤
如果當(dāng)前沒有用戶活動(dòng)，切換到后臺(tái)循環(huán)
10秒
如果前10秒的系統(tǒng)IO小于200，刷新100個(gè)臟頁到磁盤
合并最多5個(gè)插入緩沖
將日志緩沖刷新到磁盤
刪除無用的Undo頁
如果臟頁比例大于70%，刷新100個(gè)臟頁到磁盤，否則10個(gè)
問題：
插入緩沖是什么來的
為什么還要刷新日志緩沖到磁盤，不是已經(jīng)到磁盤了嗎？
Undo頁是什么來的
關(guān)鍵特性
插入緩沖
兩次寫
自適應(yīng)哈希索引
異步IO
刷新鄰接頁
插入緩沖
磁盤分為順序IO和隨機(jī)IO，例如要插入數(shù)據(jù)到磁盤的兩個(gè)地方A和B
順序IO是指A和B是磁盤中連續(xù)的，或者間隔較少的兩個(gè)位置。這樣磁盤通過尋址找到A位置后，可以快速地找到B位置。這樣插入AB和個(gè)位置的數(shù)據(jù)就會(huì)比較快，因?yàn)橹恍枰?次尋址操作。
隨機(jī)IO是指A和B兩個(gè)位置沒有關(guān)系。當(dāng)磁盤找到A位置后，需要再次通過尋址操作來尋址B。所以就會(huì)比較慢，因?yàn)樾枰?次尋址操作。
數(shù)據(jù)存儲(chǔ)
Mysql的數(shù)據(jù)存儲(chǔ)是根據(jù)主鍵來順序存儲(chǔ)的。所以在插入數(shù)據(jù)的時(shí)候：
如果主鍵是順序的，它們會(huì)存儲(chǔ)在順序的磁盤地址，這樣就是順序IO存儲(chǔ)。例如主鍵是1和2
如果主鍵是不存儲(chǔ)的，Mysql需要存儲(chǔ)在不同地方，這樣就是隨機(jī)IO。例如主鍵是abc和bcd
索引存儲(chǔ)
Mysql中主鍵本身也是索引，稱為主索引(Primary index)，其他索引稱為輔助索引(secondary index)
如果一個(gè)表中有輔助索引，在插入數(shù)據(jù)的時(shí)候，除了存儲(chǔ)數(shù)據(jù)，還需要建立索引。
如果插入兩條數(shù)據(jù)，主鍵是順序的，但是有一個(gè)索引的數(shù)據(jù)是不順序的，這樣也會(huì)產(chǎn)生隨機(jī)IO。Mysql的優(yōu)化方法是加入插入緩沖：也就是先把索引放在緩沖池中，定期刷新到磁盤。這樣可以減少隨機(jī)IO的次數(shù)。例如插入兩條數(shù)據(jù)，索引位置是相同的，如果分兩次刷新到磁盤，就需要兩次隨機(jī)IO。如果合并為一次，只需要一次隨機(jī)IO。
插入緩沖需要條件：
索引是輔助索引
索引不是唯一的。因?yàn)槿绻俏ㄒ坏?#xff0c;插入的時(shí)候Mysql需要一次隨機(jī)IO找到索引位置，看數(shù)據(jù)有沒有重復(fù)。這樣這次隨機(jī)IO就肯定需要的，所以使用插入緩沖來優(yōu)化就沒有意義了。
可以通過engine status命令來查看插入緩沖的狀態(tài)
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
seg size 是插入緩沖的大小
size 是已經(jīng)合并的頁數(shù)
free list 是空閑的頁數(shù)
merged operations 被合并的操作
插入緩沖是一棵B+樹。由非葉子節(jié)點(diǎn)和葉子節(jié)點(diǎn)組成：
非葉子節(jié)點(diǎn)
space 存儲(chǔ)spaceid是表的ID
marker 用于兼容舊版本
offset 頁所在的偏移量
葉子節(jié)點(diǎn)
sapce
marker
offset
metadata
具體數(shù)據(jù)
這樣存儲(chǔ)的好處是把同一個(gè)表，相鄰的索引(offset)放在一起。當(dāng)需要刷新緩沖數(shù)據(jù)到磁盤的時(shí)候，可以把一個(gè)表的N個(gè)索引合并在一起，通過一次隨機(jī)IO就能刷新好。提升刷新的效率。
合并插入緩沖(也就把緩沖刷到磁盤)時(shí)機(jī)：
輔助索引頁被讀取到緩沖池時(shí)。當(dāng)執(zhí)行select操作，需要用到對應(yīng)的索引時(shí)，Mysql會(huì)檢查插入緩沖是否有該表的該索引緩沖，如果有，立刻刷新到磁盤。因?yàn)槿绻凰?#xff0c;會(huì)影響查詢的準(zhǔn)確性。
Insert Buffer Bitmap 頁追蹤到索引頁已沒有可用空間時(shí)。緩沖沒有空間的時(shí)候，立刻刷新。
Master Thread。主循環(huán)會(huì)定期刷新。
兩次寫
兩次寫特性用于保證數(shù)據(jù)的可靠性。
當(dāng)Mysql刷新緩沖池的一個(gè)頁，16KB到磁盤，如果寫到4K的時(shí)候，宕機(jī)了，怎么辦？
可以通過重做日志來恢復(fù)。但是重做日志只會(huì)記錄在這一頁的哪個(gè)位置寫入內(nèi)容，例如在偏移量800寫入"aaaa"。如果這一頁都已經(jīng)損壞了，那重做日志也恢復(fù)不了。
Mysql的解決方法是通過兩次寫來解決
把緩沖池的一頁寫入到磁盤的步驟：
把磁盤的數(shù)據(jù)例如Page1讀入到內(nèi)存
把Page1保存到共享表空間
寫入緩沖池的數(shù)據(jù)到磁盤
其實(shí)就是在正式寫入前，先把頁的舊數(shù)據(jù)備份一次，當(dāng)寫失敗的時(shí)候，恢復(fù)的時(shí)候，從備份還原舊數(shù)據(jù)，然后再進(jìn)行新數(shù)據(jù)的寫入。
mysql> show global status like 'innodb_dblwr%'
-> ;
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Innodb_dblwr_pages_written | 42 |
| Innodb_dblwr_writes | 6 |
+----------------------------+-------+
Innodb_dblwr_pages_written 是雙寫寫了多少個(gè)頁
Innodb_dblwr_writes 是實(shí)際寫了多少次
一般這兩個(gè)數(shù)據(jù)是8:1(不知道為什么？是不是因?yàn)閭浞?個(gè)頁才會(huì)正式寫入一次到磁盤？)
自適應(yīng)哈希索引
又稱為AHI。
這個(gè)是Mysql通過觀察查詢情況，對應(yīng)熱點(diǎn)數(shù)據(jù)進(jìn)行自動(dòng)建立索引。
建立的是hash索引，存放在緩沖池，復(fù)雜度是O(1)，所以速度是非常快的。
例如我們查詢select * from table where name='a'
當(dāng)這個(gè)查詢進(jìn)行了超過100次，Mysql就會(huì)建立AHI
通過engine status可以查看當(dāng)前的哈希索引狀態(tài)
0.00 hash searches/s, 0.00 non-hash searches/s
hash searches/s是通過哈希索引查詢數(shù)，每秒
non-hash 是沒有通過哈希索引的查詢數(shù)，每秒
異步IO(AIO)
異步IO對應(yīng)的是同步IO。
異步IO的優(yōu)點(diǎn)是：
減少IO的等待時(shí)間，例如3次IO，只需要等1個(gè)IO時(shí)間
合并IO操作，減少隨機(jī)IO
例如我們要查詢3個(gè)頁，(space，page_no)分別是(8,6)，(8,7)，(10,9)
如果使用同步IO，我們需要發(fā)送3次IO請求，然后等待3次IO時(shí)間，明顯這是比較耗時(shí)的。
如果使用異步IO，我們一次性發(fā)送3個(gè)IO請求，然后等待IO結(jié)果。AIO發(fā)現(xiàn)前兩個(gè)IO是連續(xù)的，所以可以合并為從8,7開始取16*2KB數(shù)據(jù)，把3個(gè)IO合并為2個(gè)，然后只需要1次IO操作時(shí)間。
刷新鄰近頁
在刷新臟頁的時(shí)候，把相鄰的臟頁一起刷新。相鄰是指兩個(gè)頁在磁盤中屬于同一個(gè)區(qū)。底層就是減少隨機(jī)IO的次數(shù)。
啟動(dòng)關(guān)閉和恢復(fù)
innodb_fash_shutdown
0 數(shù)據(jù)庫關(guān)閉時(shí)只需full purge和merge insert buffser操作
1 不進(jìn)行上面的操作，但是會(huì)刷新緩沖池的臟頁回磁盤
2 不進(jìn)行上面的操作，下次啟動(dòng)時(shí)只需recovery
innodb_force_recovery
0 只需所有的恢復(fù)操作
1-6 進(jìn)行部分的恢復(fù)操作
三、文件
Mysql的文件分為：
參數(shù)文件，例如my.cnf
日志文件，包括各種日志，例如：
錯(cuò)誤日志
二進(jìn)制日志
慢查詢?nèi)罩?/p>
查詢?nèi)罩?/p>
socket文件，用于通過UNIX域套接字方式連接
pid文件，用于記錄Mysql的進(jìn)程ID
表結(jié)構(gòu)文件，存儲(chǔ)表的結(jié)構(gòu)
存儲(chǔ)引擎文件，存儲(chǔ)表的。
1. 參數(shù)文件
是Mysql啟動(dòng)時(shí)的配置文件，Mysql尋找路徑是：
[root@livedvd ~]# /data/mysql57/bin/mysql --help |grep my.cnf
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
也可以在執(zhí)行mysqld的時(shí)候，加參數(shù)--defaults-file=/data/mysql3306/my.cnf來指定這個(gè)文件。
配置文件使用鍵值對的形式進(jìn)行存儲(chǔ)，例如innodb_buffer_pool_size=1G。如果在配置文件找不到對應(yīng)的配置，Mysql會(huì)使用默認(rèn)的配置。
參數(shù)類型
動(dòng)態(tài)參數(shù)。可以在Mysql運(yùn)行期間進(jìn)行修改。
靜態(tài)參數(shù)。不可以在Mysql運(yùn)行期間進(jìn)行修改。
查看參數(shù)的方式：
show [global] variables like 'innodb_%'
查找infomation_schema庫下面的GLOBAL_VARIABLES表
修改參數(shù)：
SET [global | session] name=value 例如：
SET session read_buffer_size=2000000
SET [@@global. | @@session. | @@] name=value 例如
SET @@session.read_buffer_size=2000000
參數(shù)分為全局和當(dāng)前會(huì)話。修改參數(shù)后，并不會(huì)修改配置文件，所以下次啟動(dòng)，還是會(huì)是舊的參數(shù)。
2.日志文件
錯(cuò)誤日志
記錄Mysql的錯(cuò)誤信息和警告信息。
通過show variables like 'log_error' 來定位日志文件
慢查詢?nèi)罩?/p>
Mysql會(huì)把超過一定閾值的Sql記錄到慢查詢?nèi)罩尽?/p>
相關(guān)的參數(shù)有：
long_query_time 表示執(zhí)行時(shí)間超過多少秒(注意是大于，不是大于等于)，就記錄到慢查詢?nèi)罩尽?/p>
log_slow_queries 表示是否記錄慢查詢?nèi)罩尽ＴO(shè)置為ON，才會(huì)記錄慢查詢?nèi)罩尽?.7以后是slow_query_log
log_queries_not_using_indexes 是否記錄沒有使用索引的語句。設(shè)置為ON后，沒有使用索引的語句也會(huì)被記錄
log_throttle_queries_not_using_indexes 每分鐘記錄沒有使用索引的語句條數(shù)。這個(gè)參數(shù)避免打開log_queries_not_using_indexes 后導(dǎo)致慢查詢?nèi)罩具^多
log_output 日志輸出方式。默認(rèn)是FILE，可以設(shè)置為TABLE，日志會(huì)輸出到mysql庫的slow_log表。該表是CSV引擎。
long_query_io 超過多少邏輯IO的語句會(huì)記錄
slow_query_type 日志記錄方式
0 不記錄
1根據(jù)運(yùn)行時(shí)間記錄
根據(jù)邏輯IO次數(shù)記錄
根據(jù)運(yùn)行時(shí)間和邏輯IO記錄
slow_query_log_file 慢查詢?nèi)罩疚募奈恢?/p>
查詢分為邏輯IO和物理IO
提取慢查詢?nèi)罩?/p>
[root@livedvd ~]# /data/mysql57/bin/mysqldumpslow /data/mysql3306/data/slow.log -s t -r -n 10
Reading mysql slow query log from /data/mysql3306/data/slow.log
Count: 8 Time=0.07s (0s) Lock=0.00s (0s) Rows=1.0 (8), root[root]@localhost
select count(*) from employees where first_name='S'
[root@livedvd ~]#
mysqldumpslow 通過這個(gè)命令
-s表示排序 t表示按query time排序
-r表示倒序
-n 表示返回10條語句
通過--help查看更多用法，
也可以使用pt-query-digest工具
查詢?nèi)罩?/p>
會(huì)記錄所有的查詢信息。
不知道怎么打開，具體文件在哪里
二進(jìn)制日志
也就是binary log 也叫binlog。
記錄所有對數(shù)據(jù)庫的修改信息，用于：
恢復(fù)數(shù)據(jù)庫狀態(tài)到某個(gè)時(shí)間點(diǎn)(point-in-time)，需要配合冷備。
復(fù)制。主從復(fù)制
審計(jì)，查看是否有SQL注入
打開binlog日志
修改配置文件，加入配置
[mysqld]
log-bin=binlog
binlog_format=mixed # 日志格式
server-id=1 #節(jié)點(diǎn)的ID，可以設(shè)置為主庫是1，從庫是2，不能重復(fù)。
重啟mysql，show variables like 'log%';查看到log_bin=ON
表示打開了binlog日志
相關(guān)參數(shù)
binlog相關(guān)的參數(shù)有：
max_binlog_size 單個(gè)binlog日志的最大字節(jié)，大于這個(gè)字節(jié)就會(huì)寫入到新的文件。
binlog_cache_szie binlog緩沖區(qū)大小。當(dāng)事務(wù)沒有提交時(shí)，Mysql會(huì)把修改的內(nèi)容寫入到緩沖區(qū)，等commit的時(shí)候，寫入到binlog日志。當(dāng)緩沖區(qū)滿了的話，會(huì)寫入到臨時(shí)文件
binlog_cache_use binlog緩沖區(qū)使用次數(shù)
binlog_cache_disk_use 臨時(shí)文件使用次數(shù)
sync_binlog 。binlog的寫入，也會(huì)有緩沖區(qū)，如果設(shè)置這個(gè)參數(shù)=1，就不會(huì)緩沖。這樣可用性會(huì)較高，但是性能會(huì)較差
binlog-do-db 表示那些庫寫入到binlog
binlog-ignore-db 表示哪些庫不寫入binlog
log-slave-update 是否將從主復(fù)制來的修改，寫入到自己的binlog。默認(rèn)不寫，如果要配置master-slave-slave 就需要寫
binlog_format binlog的格式，可以選：
statement 記錄執(zhí)行的sql到binlog。優(yōu)點(diǎn)是省空間，缺點(diǎn)是對于一些隨機(jī)語句，可能會(huì)導(dǎo)致主從不一致
row 記錄修改的行到binlog 。缺點(diǎn)是費(fèi)空間
mixed 上面兩個(gè)混合。Mysql智能選擇格式
binlog日志查看
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000002 | 490 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
通過show master status;查看當(dāng)前的binlog日志的文件名和大小(Position)等信息
binlog的文件在datadir參數(shù)的目錄里面。
mysql> show binlog events in 'binlog.000002';
+---------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------+
| binlog.000002 | 490 | Anonymous_Gtid | 1 | 555 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000002 | 555 | Query | 1 | 634 | BEGIN |
| binlog.000002 | 634 | Query | 1 | 758 | use `employees`; update employees set first_name='kevinlu2' limit 1 |
| binlog.000002 | 758 | Xid | 1 | 789 | COMMIT /* xid=24 */ |
+---------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------+
11 rows in set (0.00 sec)
mysql>
通過命令show binlog events in 'binlog.000002'可以看到binlog的內(nèi)容，如果是statement格式，可以看到執(zhí)行的sql語句。
/data/mysql57/bin/mysqlbinlog -vv --start-position=0 binlog.000002
通過mysqlbinlog命令也可以查看binlog的信息。其中
-vv表示轉(zhuǎn)換row格式。如果不轉(zhuǎn)換，返回二進(jìn)制信息，看不懂。
可以看到轉(zhuǎn)換后，row格式的內(nèi)容是這樣的：
BINLOG '
2gr1XRMBAAAARAAAAG4BAAAAAHEAAAAAAAMACWVtcGxveWVlcwAJZW1wbG95ZWVzAAYDCg8P/goG
DgAQAPcBADPIzgc=
2gr1XR8BAAAAXQAAAMsBAAAAAHEAAAAAAAEAAgAG///AEScAACJDDwdrZXZpbmx1B0ZhY2VsbG8B
2oQPwBEnAAAiQw8Ia2V2aW5sdTEHRmFjZWxsbwHahA8LS+ze
'/*!*/;
### UPDATE `employees`.`employees`
### WHERE
### @1=10001 /* INT meta=0 nullable=0 is_null=0 */
### @2='1953:09:02' /* DATE meta=0 nullable=0 is_null=0 */
### @3='kevinlu' /* VARSTRING(14) meta=14 nullable=0 is_null=0 */
### @4='Facello' /* VARSTRING(16) meta=16 nullable=0 is_null=0 */
### @5=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @6='1986:06:26' /* DATE meta=0 nullable=0 is_null=0 */
### SET
### @1=10001 /* INT meta=0 nullable=0 is_null=0 */
### @2='1953:09:02' /* DATE meta=0 nullable=0 is_null=0 */
### @3='kevinlu1' /* VARSTRING(14) meta=14 nullable=0 is_null=0 */
### @4='Facello' /* VARSTRING(16) meta=16 nullable=0 is_null=0 */
### @5=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @6='1986:06:26' /* DATE meta=0 nullable=0 is_null=0 */
執(zhí)行的sql是useemployees; update employees set first_name='kevinlu1' limit 1，可以看到
即使只修改一個(gè)字段，但是row格式會(huì)把整行的所有字段都寫進(jìn)binglog。
從機(jī)復(fù)制的時(shí)候，應(yīng)該根據(jù)表的主鍵來匹配到對應(yīng)的行，然后使用binlog的數(shù)據(jù)覆蓋整行數(shù)據(jù)
3. 套接字文件
mysql> show variables like 'socket';
+---------------+------------------------------+
| Variable_name | Value |
+---------------+------------------------------+
| socket | /data/mysql3306/mysql.socket |
+---------------+------------------------------+
mysqld啟動(dòng)會(huì)，會(huì)生成一個(gè)socket文件，mysql連接本機(jī)的mysql的話，可以直接指定socket文件，這樣就不用輸入端口和host了。
連接方式：
/data/mysql57/bin/mysql --socket /data/mysql3306/mysql.socket -uroot -ppassword1
4. pid文件
參數(shù)名是pid_file
5. 表結(jié)構(gòu)定義文件
由于Mysql每個(gè)表都有自己的引擎類型，所以Mysql的存儲(chǔ)是以表為單位的。
每個(gè)表都會(huì)有一個(gè)xx.frm文件來描述表的定義。
frm文件在datadir里面的庫文件夾里面。
書上說是文本文件，但是測試發(fā)現(xiàn)5.7的mysql是二進(jìn)制文件。
6.InnoDB存儲(chǔ)引擎文件
表空間文件
表空間文件用于存儲(chǔ)表的數(shù)據(jù)，索引，插入緩沖等信息。
例如test庫的table1表的表空間文件就是/data/mysql3306/data/test/table1.ibd
相關(guān)參數(shù)
innodb_data_file_path 定義表空間文件的路徑，可以指定多個(gè)文件，這樣Mysql會(huì)分開存儲(chǔ)表的數(shù)據(jù)，如果路徑位于不同的磁盤，可以提升性能。
innodb_file_per_table 是否分開存儲(chǔ)表空間文件。如果等于ON，每個(gè)表一個(gè)ibd文件，否則整個(gè)庫的數(shù)據(jù)都存儲(chǔ)在一起，idbdata1.ibd。
重做日志文件
如果每次事務(wù)提交，Mysql都修改具體的數(shù)據(jù)文件，性能會(huì)比較差。優(yōu)化方法是先把修改點(diǎn)記錄到重做日志文件。然后定期把重做日志的內(nèi)容更新到具體的數(shù)據(jù)文件。這樣做的另一個(gè)好處是，當(dāng)事務(wù)提交后，由于已寫入到重做日志，所以Mysql宕機(jī)后的重啟，可以通過更新重做日志的內(nèi)容來實(shí)現(xiàn)事務(wù)不會(huì)丟失。
重做日志叫redo log file。位于data目錄下面的ib_logfile0和ib_logfile1。這兩個(gè)文件會(huì)循環(huán)寫入，例如先寫0，寫滿后寫1，寫滿后再寫0，不斷循環(huán)。
innodb_log_file_size單個(gè)日志文件的最大大小
innodb_log_files_in_group 日志文件的數(shù)據(jù)，默認(rèn)是2，也就是0和1
innodb_mirrored_log_groups 日志鏡像文件組的數(shù)量，默認(rèn)是1. 為了可用性，可以加多個(gè)重做日志的鏡像。
innodb_log_group_home_dir 日志文件的目錄，默認(rèn)是data目錄
重做日志的寫入也有個(gè)緩沖區(qū)。緩沖區(qū)寫入磁盤是按512字節(jié)寫入的，所以一定會(huì)成功(為什么？)
所以事務(wù)的流程
在事務(wù)的執(zhí)行過程中，不斷有信息寫入到重做日志，
在事務(wù)提交時(shí)，先寫二進(jìn)制文件，寫入成功后，返回提交成功
四、表
1. 索引組織表
InnoDB中，數(shù)據(jù)的存儲(chǔ)是根據(jù)主鍵來的，也就是主鍵相鄰的數(shù)據(jù)存儲(chǔ)在一起。
定義表時(shí)，
如果有主鍵
如果沒有主鍵
選擇一個(gè)唯一的非空的字段作為索引
如果沒有，自動(dòng)創(chuàng)建一個(gè)6字節(jié)的指針
所以在InnoDB看來，每個(gè)表都有主鍵。
2.InnoDB邏輯存儲(chǔ)結(jié)構(gòu)
所有的數(shù)據(jù)都存儲(chǔ)在idb文件中，這個(gè)稱為表空間(tablespace)
表空間由段(segment)，區(qū)(extent)，頁(page)組成。
表空間由3個(gè)段組成：
葉子節(jié)點(diǎn)段
非葉子節(jié)點(diǎn)段
回滾段
每個(gè)段由多個(gè)區(qū)組成
每個(gè)區(qū)由多個(gè)頁組成
一個(gè)頁存放一個(gè)或多個(gè)行
頁有時(shí)也稱為block
段
分為葉子段，非葉子段，回滾段
區(qū)
任何情況下，一個(gè)區(qū)的大小是1M
為了保證數(shù)據(jù)的連續(xù)性，Mysql一次會(huì)申請4-5個(gè)區(qū)的空間。
一般一個(gè)頁是16K，也就是一個(gè)區(qū)由64個(gè)頁
創(chuàng)建表示，Mysql為了節(jié)省空間，只會(huì)申請32個(gè)頁的空間，稱為碎片頁(fragment page)。后面才會(huì)一次申請64個(gè)頁。
[root@livedvd test]# py_innodb_page_info.py -v t1.ibd
page offset 00000000, page type
page offset 00000001, page type
page offset 00000002, page type
page offset 00000003, page type , page level <0000>
page offset 00000000, page type
page offset 00000000, page type
Total number of page: 6:
Freshly Allocated Page: 2
Insert Buffer Bitmap: 1
File Space Header: 1
B-tree Node: 1
File Segment inode: 1
每一個(gè)頁由自己的偏移量，也可以稱為頁的ID
每個(gè)頁都有類型，
這里一共6個(gè)頁，其中沒有使用的2個(gè)，已使用的4個(gè)，
Btree節(jié)點(diǎn)頁1個(gè)，level0表示這是葉子節(jié)點(diǎn)，如果等于1表示非葉子節(jié)點(diǎn)
Freshly Allocated Page表示還沒有使用的頁，
上面的是新建表后，插入2行7000長度varchar的行后的數(shù)據(jù)，一個(gè)字符的長度是1字節(jié)，所以7000就是7000字節(jié)，兩行就是差不多16k。所以一個(gè)頁就能把這2行數(shù)據(jù)存儲(chǔ)，所以上面只有一個(gè)btree的頁
如果再插入一行，就需要2個(gè)頁了，因?yàn)榇笥?個(gè)節(jié)點(diǎn)頁，就需要一個(gè)非葉子節(jié)點(diǎn)，
page offset 00000003, page type , page level <0001>
page offset 00000004, page type , page level <0000>
page offset 00000005, page type , page level <0000>
可以看到，有一個(gè)非葉子節(jié)點(diǎn)，2個(gè)葉子節(jié)點(diǎn)。
mysql> DELIMITER //
mysql> create procedure load_t1(count int unsigned) begin declare s int unsigned default 1;
-> declare c varchar(7000) default repeat('a',7000);
-> while s<= count DO
-> insert into t1 select NULL,c;
-> SET s=s+1
-> ;
-> END while;
-> end;
-> //
DELIMITER //表示修改結(jié)束符，從;修改為//
如果繼續(xù)插入數(shù)據(jù)，插入64行后，就需要32個(gè)頁，這時(shí)碎片頁就用完了，如果繼續(xù)插入，Mysql就會(huì)一次申請64個(gè)頁，也就是一個(gè)區(qū)，1M。
頁
默認(rèn)一頁是16K，可以修改innodb_page_size為4K，8K，16K。
頁類型有：
數(shù)據(jù)頁(B-tree Node)
undo頁(undo Log Page)
系統(tǒng)頁(System Page)
事務(wù)數(shù)據(jù)頁(Transaction system Page)
插入緩沖位圖頁(Insert Buffer BItmap)
插入緩沖空閑列表頁(Insert BUffer Free List)
未壓縮的二進(jìn)制大對象頁(Uncompresssed BLOB Page)
壓縮的二進(jìn)制大對象頁(compressed BLOB Page)
行
InnoDB的存儲(chǔ)是面向行的，也就是以行來組織存儲(chǔ)的
3. InnoDb行記錄格式
有4種格式：
Compact
Redundant主要用于兼容舊版本
Dynamic 和Compact一樣，不同點(diǎn)，書上說使用完全行溢出。(5.7的mysql實(shí)驗(yàn)發(fā)現(xiàn)并不是)
Compressed 和Dynamic一樣，不同點(diǎn)是對可變長度的數(shù)據(jù)進(jìn)行zlib壓縮，包括BLOG，TEXT，VARCHAR
通過show table status like 'employees'命令可以查看表的Row_format
Compact格式
一行數(shù)據(jù)的組成
變長字段長度列表。例如有3個(gè)可變長度的字段，長度分別為1,2,3，這里會(huì)存儲(chǔ)03 02 01(逆序存放,3個(gè)字段就存放3字節(jié))
如果列長度小于255，使用1字節(jié)表示
如果長度小于65535 ,使用2字節(jié)表示。
不允許有長度大于65535 的可變列，因?yàn)橐?guī)定varchar的最大長度是65535
NULL標(biāo)志位。用二進(jìn)制表示，1表示第N頁是NULL
記錄頭信息，5字節(jié)
1bit 未知
1bit 未知
1bit deleted_flag 該行是否刪除
1bit min_rec_flag
4bit n_owned 該記錄所在的槽擁有的記錄數(shù)
13bit heap_no 索引堆中該記錄的排序記錄
3bit record_type 記錄類型 000表示普通 001 表示B+書節(jié)點(diǎn)指針，010 鄙視Infimum，011表示Supremum 1xx表示保留
16bit next_record 下一條記錄的相對位置
列數(shù)據(jù)
有兩個(gè)固定列：
事務(wù)ID列 6字節(jié)
回滾指針列 7字節(jié)
如果沒有主鍵ID，會(huì)自動(dòng)加一列rowid 6字節(jié)
后面就是用戶自己的數(shù)據(jù)了
變長字段只會(huì)一定長度的數(shù)據(jù)，超出的部分會(huì)存儲(chǔ)到Uncompresssed BLOB Page，并在未超出的數(shù)據(jù)后記錄超出的部分存儲(chǔ)在哪個(gè)頁的哪個(gè)偏移量。這個(gè)稱為行溢出存儲(chǔ)。
為什么超出的部分需要存儲(chǔ)在其他地方？
因?yàn)榇鎯?chǔ)是B+樹形式的，如果一個(gè)頁只有一行數(shù)據(jù)，那邊B+樹就沒有意義了，就變成鏈表了。所以一頁必須存儲(chǔ)大于1行數(shù)據(jù)。
varchar類型，varchar(N)的N表示最大字符長度，官方說的varchar最大存儲(chǔ)是65535字節(jié)，長度和字節(jié)是不完全一樣的。
可以通過命令hexdump -C -v t3.ibd > t3.hex查看具體的ibd文件。
000101a0 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 |aaaaaaaaaaaaaaaa|
61表示一個(gè)字節(jié)。一行16字節(jié)。
char類型。InnoDB中，char類型存儲(chǔ)的不一定是定長的數(shù)據(jù)。
如果表的字符集是latin1，char存儲(chǔ)的是定長的數(shù)據(jù)，如果不夠長度，使用ox20來填充。
如果字符集是其他，例如GBK，UTF8，char相當(dāng)于varchar
4.InnoDB數(shù)據(jù)頁結(jié)構(gòu)
頁是Mysql最小的磁盤管理單位。
頁的組成：
File Header 文件頭 38字節(jié)
Page Header 頁頭 56字節(jié)
Infimun 和Supremum Records
User Records 用戶記錄
Free Space 空閑空間
Page Directory 頁目錄
File Trailer 文件結(jié)尾信息 8字節(jié)
File Header
FIL_PAGESPACE_OR_CHKSUM checksum值 4字節(jié)
FIL_PAGE_OFFSET 4字節(jié)頁在表空間中的偏移量，也就是第幾個(gè)頁
FIL_PAGE_PREV 前一個(gè)數(shù)據(jù)頁的偏移量
FIL_PAGE_NEXT 下一個(gè)頁的偏移量
FIL_PAGE_LSN 8字節(jié) LSN(log sequence number)
FIL_PAGE_TYPE 頁類型 2字節(jié)
45BF B+樹葉子節(jié)點(diǎn)
FIL_PAGE_FILE_FLUSH_LSN 8字節(jié)LSN值
FIL_PAGE_ARCH_LOG_NO_OR_SPACE_ID 4字節(jié)表示該頁屬于哪個(gè)表空間
Page Header
PAGE_N_DIR_SLOTS 2字節(jié) 在Page Directory 頁目錄中的Slot(槽)數(shù)
PAGE_HEAP_TOP 2字節(jié) 空閑空間堆中的最小位置
PAGE_N_HEAP 2字節(jié) 堆中的記錄數(shù)，也就是該頁中的記錄數(shù)
PAGE_FREE 2字節(jié) 可重用空間的首指針
PAGE_GARBAGE 2字節(jié) 已刪除的記錄數(shù)量
PAGE_LAST_INSERT 2字節(jié) 最后插入記錄的位置
PAGE_DIRECTION 2字節(jié)最后插入的方向
PAGE_N_DIRECTION 2字節(jié) 一個(gè)方向連續(xù)插入記錄的數(shù)量
PAGE_N_RECS 2字節(jié) 頁中記錄數(shù)量
PAGE_MAX_TRX_ID 8字節(jié) 修改當(dāng)前頁的最大事務(wù)ID，
PAGE_LEVEL 2字節(jié) 頁在索引樹中的位置，00表示葉節(jié)點(diǎn)
PAGE_INDEX_ID 8字節(jié) 索引ID
PAGE_BTR_SEG_LEAF 10字節(jié)
PAGE_BTR_SEG_TOP 10字節(jié)
Infinum和Supremum Record
每一頁中都有2行偽記錄，表示當(dāng)前頁最小值和最大值
User Record 和 Free Space
真正的數(shù)據(jù)，存儲(chǔ)方式看上面的行存儲(chǔ)描述。
Page Directory
B+Tree只能定位到具體的頁，定位后Mysql會(huì)把整個(gè)頁加載到內(nèi)存。然后通過頁的Page Directory找到具體的行。這個(gè)尋找方式的復(fù)雜度是二分查找。實(shí)現(xiàn)方式是通過Page Directory。由于這個(gè)操作是二分查找，而且在內(nèi)存中，所以速度很快。
一個(gè)頁里面會(huì)有很多個(gè)槽，一個(gè)槽有多行記錄，槽中的記錄的n_owned值記錄該槽擁有的記錄數(shù)，當(dāng)插入和刪除操作后，Mysql需要對槽進(jìn)行分裂或者平衡操作。
偽記錄 Infimum 的 n_owned 的值總是1
Supremum的n_owned的值是[1,8]
用戶記錄的n_owned的值是[4,8]
假如有主鍵為 1,2,3,4,5,6，每個(gè)槽有4行記錄。則槽的形式可能是：
1234 存放在一個(gè)槽1，槽1指向的記錄是1
56存放在一個(gè)槽2，槽2指向的是記錄5
File Trailer
這個(gè)用于和File Header的checksum檢查，檢測是否一頁數(shù)據(jù)完整，(已完整寫入到磁盤)。
8字節(jié)，前4字節(jié)等于Header的checksum后4字節(jié)等于LSN。
測試
創(chuàng)建一個(gè)新表，并插入3行數(shù)據(jù)
mysql> create table t5(id int unsigned not null auto_increment,b char(10),primary key (a) );
mysql> insert into t5 set b='aaaaaaaaaa';
mysql> insert into t5 set b='bbbbbbbbbb';
mysql> insert into t5 set b='cccccccccc';
使用py_innodb_page_info工具，查看到數(shù)據(jù)頁位于第4個(gè)頁，使用hexdump -C -v t5.ibd > t5.hex命令，查看表空間文件。第4個(gè)頁位于1610243的位置，16進(jìn)制是 c000。
內(nèi)容如下：
0000c000 36 a9 8f ef 00 00 00 03 ff ff ff ff ff ff ff ff |6...............|
0000c010 00 00 00 00 06 cd b7 6a 45 bf 00 00 00 00 00 00 |.......jE.......|
0000c020 00 00 00 00 00 2e 00 02 00 db 80 05 00 00 00 00 |................|
0000c030 00 c0 00 02 00 02 00 03 00 00 00 00 00 00 00 00 |................|
0000c040 00 00 00 00 00 00 00 00 00 42 00 00 00 2e 00 00 |.........B......|
0000c050 00 02 00 f2 00 00 00 2e 00 00 00 02 00 32 01 00 |.............2..|
0000c060 02 00 1b 69 6e 66 69 6d 75 6d 00 04 00 0b 00 00 |...infimum......|
0000c070 73 75 70 72 65 6d 75 6d 00 00 00 10 00 21 00 00 |supremum.....!..|
0000c080 00 01 00 00 00 00 0f 80 f4 00 00 02 03 01 10 61 |...............a|
0000c090 61 61 61 61 61 61 61 61 61 00 00 00 18 00 21 00 |aaaaaaaaa.....!.|
0000c0a0 00 00 02 00 00 00 00 0f 81 f5 00 00 02 04 01 10 |................|
0000c0b0 62 62 62 62 62 62 62 62 62 62 00 00 00 20 ff b0 |bbbbbbbbbb... ..|
0000c0c0 00 00 00 03 00 00 00 00 0f 86 f8 00 00 02 07 01 |................|
0000c0d0 10 63 63 63 63 63 63 63 63 63 63 00 00 00 00 00 |.cccccccccc.....|
0000c0e0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
0000c0f0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
后面的都是00來了，因?yàn)榭臻g未被使用，找到頁的末尾，內(nèi)容如下：
0000fff0 00 00 00 00 00 70 00 63 b7 5c cf 11 06 cd 89 e1 |.....p.c.\......|
數(shù)據(jù)解析(下面的數(shù)字都是十六進(jìn)制)：
FIL_PAGESPACE_OR_CHKSUM = b7 5c cf 11
FIL_PAGE_OFFSET = 00 00 00 03 表示這一頁在表空間的偏移位是3
FIL_PAGE_PREV = ff ff ff ff
FIL_PAGE_NEXT =ff ff ff ff 由于只有一個(gè)數(shù)據(jù)頁，前后頁的值都是空
FIL_PAGE_LSN = 00 00 00 00 06 cd 89 e1
FIL_PAGE_TYPE = 45 bf 表示這是個(gè)葉子頁
45BF B+樹葉子節(jié)點(diǎn)
FIL_PAGE_FILE_FLUSH_LSN 00 00 00 00 00 00 00 00
FIL_PAGE_ARCH_LOG_NO_OR_SPACE_ID = 00 00 00 2d
PAGE_N_DIR_SLOTS = 00 02 2個(gè)槽
PAGE_HEAP_TOP = 00 db 空閑空間的位置，這一頁的位置是c000 加上 00db就是 c0db，可以看到這個(gè)位置的數(shù)據(jù)都是0
PAGE_N_HEAP = 80 05 因?yàn)槭荂ompact格式，初始值是0x8002，所以 頁中的記錄數(shù)=8005-8002=3
PAGE_FREE =00 00 由于沒有刪除記錄，所以是0
PAGE_GARBAGE =00 00 由于沒有刪除記錄，所以是0
PAGE_LAST_INSERT = 00 c0 最后插入記錄的位置為c000+00c0 = c0c0
PAGE_DIRECTION =00 02
PAGE_N_DIRECTION =0 02
PAGE_N_RECS = 00 03 頁中記錄數(shù)量為3
PAGE_MAX_TRX_ID = 00 00 00 00 00 00 00 00
PAGE_LEVEL =00 00 葉子節(jié)點(diǎn)
PAGE_INDEX_ID = 00 00 00 00 00 00 00 41
上面是File Header 和Page Header
下面是兩個(gè)偽記錄
infimum：
01 00 02 00 1b 69 6e 66 69 6d 75 6d 00
supremum
04 00 0b 00 00 73 75 70 72 65 6d 75 6d
簽名是record header 5字節(jié)，偽記錄只有一列 char(8)內(nèi)容是supremum和infimum
從infimum的record的后兩字節(jié)00 1b，可以計(jì)算第一行的位置為c063+001b=c07e。c063是infimum record header之后第一字節(jié)的位置(也就是69的位置)。c07e是第一行的內(nèi)容開始的位置，而不是可變長度數(shù)據(jù)的位置
所以第一行的數(shù)據(jù)為
00 00 00 10 00 21 00 00
00 01 00 00 00 00 0f 80 f4 00 00 02 03 01 10 61
61 61 61 61 61 61 61 61 61
c07e是加粗字節(jié)的位置，前面5字節(jié)是record header，再前面1字節(jié)是NULL，沒有可變長度。
00 00 00 01是rowid，由于有組件所以這里是a字段的值，a字段是int類型，占4字節(jié)。
00 00 00 00 0f 80 是事務(wù)ID
f4 00 00 02 03 01 10 是回滾點(diǎn)
61 61 61 61 61 61 61 61 61 61是除主鍵外第一列的數(shù)據(jù)，是b字段，char10,10字節(jié)，全都是A，61的acii碼對應(yīng)就是a
同樣的方法，計(jì)算第一行的開始位置，等于c07e+0021=C09F
61 61 61 61 61 61 61 61 61 00 00 00 18 00 21 00
00 00 02 00 00 00 00 0f 81 f5 00 00 02 04 01 10
62 62 62 62 62 62 62 62 62 62
C09F是上面加粗的位置，同理向前6字節(jié)是null和record header
所以使用這個(gè)方法，就能找到頁中所有行。
通過Page Header的next和pre，找到前后的頁。這樣就能找到表中所有的數(shù)據(jù)。
Page Directory
槽是逆序存放的，一個(gè)槽占2字節(jié)。File Trailer往前推就是Page DIrectory。
也就是 00 70 00 63
這里有兩個(gè)槽，
第一個(gè)槽00 63，對應(yīng)的是0063+c000的位置，就是指向Infimum行的內(nèi)容(不是header)
第二個(gè)槽00 70，對應(yīng)的是C070，也就是supermum的行的內(nèi)容，然后找到supermum的頭04 00 0b 00 00，其中n_owned在第一個(gè)字節(jié)的后面4bit，轉(zhuǎn)換04為二進(jìn)制后是00000100，所以n_owned=0100，十進(jìn)制就是4。所以n_owned=4。表示這個(gè)槽有4行記錄，包括super行和其他3行用戶行。(其實(shí)直接取04的4也可以)
5 Named File Formats機(jī)制
現(xiàn)在InnoDB有4中文件格式，后面也會(huì)有更多
6 約束
數(shù)據(jù)完整性
InnoDB提供的約束有：
Primary Key 該字段的值需要唯一
Unique Key 該字段的值需要唯一
Foreign Key 該字段的值需要in另一個(gè)表的一個(gè)字段的所有取值
Default 字段設(shè)置默認(rèn)值
NOT NULL 字段不能為NULL
錯(cuò)誤值約束
如果輸入的值，和字段的數(shù)據(jù)類型不符合。例如字段是int類型，輸入了'a'，這就是錯(cuò)誤值。
對于錯(cuò)誤值，InnoDB有兩種做法：
當(dāng)sql_mode沒有STRICT_TRANS_TABLES，修改值為0，并報(bào)Warning
當(dāng)sql_mode有STRICT_TRANS_TABLES，報(bào)Error，拒絕寫入
通過show variables like 'sql_mode'來查看
觸發(fā)器約束
可以為一個(gè)表設(shè)置6個(gè)觸發(fā)器，分別為INSERT UPDATE DELETE 的BEFORE 和AFTER。表示插入，更新，刪除的前后。
mysql> create table cash (user_id int NOT NULL,cash INT NOT NULL);
Query OK, 0 rows affected (0.02 sec)
mysql> create table err_log(user_id int ,old_cash int,new_cash int,time datetime);
Query OK, 0 rows affected (0.02 sec)
mysql> delimiter $$
mysql> create trigger trg_usercash_update before update on cash
-> for each row
-> begin
-> if new.cash-old.cash > 0 then
-> insert into error_log
-> select old.user_id,old.cash,new.cash,NOW();
-> set new.cash=old.cash;
-> end if;
-> end;
-> $$
創(chuàng)建一個(gè)觸發(fā)器，當(dāng)cash表更新的時(shí)候，如果更新后cash字段大于更新前，拒絕更新，并寫一條日志
外鍵約束
在批量修改數(shù)據(jù)時(shí)，外鍵約束會(huì)導(dǎo)致大量的額外開銷，因?yàn)樾枰樵儗?yīng)的約束是否滿足。可以通過set foreign_key_checks=0來取消檢查
7.視圖
視圖是一個(gè)虛表，和持久表不同的時(shí)候，視圖中的數(shù)據(jù)沒有實(shí)際的物理存儲(chǔ)。
8.分區(qū)
Mysql支持的分區(qū)類型
RANGE 分區(qū)。例如小于10的放在分區(qū)1，小于20的放在分區(qū)2
LIST 分區(qū)，主鍵在1,2,5,6的放在分區(qū)1，主鍵在4,7的放在分區(qū)2
HASH 分區(qū)，按主鍵，計(jì)算hash值(怎么計(jì)算，由用戶提供，例如取模)，放在指定的分區(qū)，這樣可以均勻存放
KEY分區(qū)，和HASH分區(qū)類型，只是計(jì)算hash值使用Mysql指定的還是，和password還是一樣
COLUMNS分區(qū)，5.5后支持。上面的分區(qū)方法都是為每行計(jì)算一個(gè)int值，然后決定放在哪個(gè)分區(qū)。COLUMNS分區(qū)支持其他數(shù)據(jù)類型，例如float，datetime等。
子分區(qū)，可以在分區(qū)的基礎(chǔ)上再分區(qū)，又叫復(fù)合分區(qū)。
分區(qū)和性能
分區(qū)后的好處
分區(qū)后，不同分區(qū)存放在不同的文件。所以如果需要把一張表存放在不同的硬盤，就可以使用分區(qū)
對于分區(qū)的鍵進(jìn)行查詢，速度會(huì)有一定提升。
例如1kw的數(shù)據(jù)，需要根據(jù)key1來查詢，
如果使用索引，1kw數(shù)據(jù)可以能需要3層B+tree索引，就需要3次磁盤IO
如果使用key1分區(qū)，假如有10個(gè)分區(qū)，那么可能只需要2層索引，這樣可以減少一次IO
其實(shí)對查詢速度提升是有的，但是很有限。(可能使用索引也是2層，那就沒有優(yōu)化了)
刪除單個(gè)分區(qū)非常快。因?yàn)槭前捶謪^(qū)存放文件的，所以一個(gè)分區(qū)，就相當(dāng)于刪除一個(gè)ibd文件，速度非常快
分區(qū)的壞處
分區(qū)后。表中的索引在每個(gè)分區(qū)單獨(dú)管理。
假如表t 按key1分區(qū)10個(gè)，同時(shí)有索引idx_1(key2)，如果查詢時(shí)使用idx1索引，就需要到每個(gè)分區(qū)執(zhí)行索引查詢，這時(shí)候磁盤IO可能是2*10次。遠(yuǎn)大于不分區(qū)時(shí)2-3次磁盤IO
分區(qū)查詢的性能，可以通過explain命令來查看，有一個(gè)partitions列，展示本次查詢檢索了哪幾個(gè)分區(qū)
總的來說，使用分區(qū)要謹(jǐn)慎。
分區(qū)更適用于OLAP(在線分析處理)，這時(shí)候一般需要查詢大量數(shù)據(jù)
不適用于OLTP(在線事務(wù)處理)，這時(shí)候一般都是使用索引查詢幾條數(shù)據(jù)。
3
創(chuàng)建表create table t7 (a int ) partition by hash(a) partitions 4;，然后有4個(gè)ibd文件
t7.frm
t7#P#p0.ibd
t7#P#p1.ibd
t7#P#p2.ibd
t7#P#p3.ibd
總結(jié)
以上是生活随笔為你收集整理的mysql技术innodb存储引擎读后感_《Mysql技术内幕-InnoDB存储引擎》读书笔记 （一）...的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 小米 Redmi Pad 平板开始推送安
- 下一篇： 恋与制作人关卡掉落(汉典恋字的基本解释)