202_mysql_innodb_3_表空間_共享表空間

System Tablespace(共享表空間)

存儲方式 ibdata1~ibdataN, 5.5版本默認的表空間類型。 ## ibdata1共享表空間在各個版本的變化 5.5版本： 系統相關:（全局）數據字典信息（表基本結構信息、狀態、系統參數、屬性..），UNDO回滾信息(記錄撤銷操作)、Double Write buffer信息、 臨時表信息、change buffer 用戶數據： 表數據行、表的索引數據 5.6版本：共享表空間只存儲于系統數據，把用戶數據獨立了。 系統相關：（全局）數據字典信息、UNDO回滾信息、Double Write信息、臨時表信息、change buffer 5.7版本：在5.6基礎上，把臨時表獨立出來，UNDO也可以設定為獨立 系統相關：（全局）數據字典信息、UNDO回滾信息、Double Write信息、change buffer 8.0.19版本：在5.7的基礎上將UNDO回滾信息默認獨立，數據字典不再集中存儲了 系統相關：Double Write信息、change buffer 8.0.20版本:在之前版本基礎上，獨立 Double Write信息

查看共享表空間命令

select @@innodb_data_file_path;

select @@innodb_autoextend_increment;

參數用途：ibdata1文件，默認初始大小12M，不夠用會自動擴展，默認每次擴 展64M

配置文件設定為和實際大小一致： innodb_data_file_path=ibdata1:76M;ibdata2:100M;ibdata3:100M:autoextend 5.7 中建議：設置共享表空間2-3個，大小建議512M或者1G，最后一個定制為 自動擴展。 8.0 中建議：設置1-2個就ok，大小建議512M或者1G

一?系統表空間 表空間 ID 是0

系統表空間的結構和獨立表空間基本類似，只不過由于整個 Mysql 進程只有一個系統表空間，

在系統表空間中會額外記錄一些有關整個系統信息的頁面，所以會比獨立表空間多出一些記錄這些信息的頁面。

系統表空間的整體結構

系統表空間與獨立表空間的一個非常明顯的不同之處就是在表空間開頭有許多記錄整個系統屬性的頁面，示意圖如下：

可以看到，系統表空間和獨立表空間的前三個頁面(頁號分別為0、1、2，類型分別是 FSP_HDR、IBUF_BITMAP、INODE)的類型是一致的，

1.1 只是頁號3 ~ 7的頁面是系統表空間特有的

頁號

頁面類型

英文描述

描述

3

SYS

Insert Buffer Header

存儲 Insert Buffer 的頭部信息

4

INDEX

Insert Buffer Root

存儲 Insert Buffer 的根頁面

5

TRX_SYS

Trasction System

事務系統的相關信息

6

SYS

First Rollback Segment

第一個回滾段的頁面

7

SYS

Data Dictionary Header

數據字典頭部信息

1.2 DWB（double write bufffer）

除了這個記錄系統屬性的頁面外，系統表空間的 extent 1 和 extent 2這兩個區，也就是頁號從64 ~ 191這128個頁面被稱為 Doublewrite buffer，也就是雙寫緩沖區

作用： Mysql，最小IO單元page（16KB），OS中最小的IO單元是block（4KB） 為了防止出現以下問題： mysqld process crash in the middle of a page write DWB每次1M，2次寫完。數據頁再刷盤。

1.3?InnoDB 數據字典

insert數據為例, 先要找到表聚簇索引和所有二級索引對應的根頁面是哪個表空間的哪個頁面，然后把記錄插入對應索引的 B+樹中

InnoDB 存儲引擎特意定義了一些列的內部系統表來記錄這些元數據

表名

描述

SYS_TABLES

整個 InnoDB 存儲引擎中所有的表的信息

SYS_COLUMNS

整個InnoDB存儲引擎中所有的列的信息

SYS_INDEXES

整個InnoDB存儲引擎中所有的索引的信息

SYS_FIELDS

整個InnoDB存儲引擎中所有的索引對應的列的信息

SYS_FOREIGN

整個InnoDB存儲引擎中所有的外鍵的信息

SYS_FOREIGN_COLS

整個InnoDB存儲引擎中所有的外鍵對應的列的信息

SYS_TABLESPACES

整個InnoDB存儲引擎中所有的表空間信息

SYS_DATAFILES

整個InnoDB存儲引擎中所有的表空間對應文件系統的文件路徑信息

SYS_VIRTUAL

整個InnoDB存儲引擎中所有的虛擬生成列的信息

1.4 Data Dictionary Header 頁面

InnoDB 的設計者又拿出一個固定的頁面來記錄這4個表的聚簇索引和二級索引對應的 B+樹的位置，

這個頁面就是頁號為7的頁面，類型為 SYS，記錄了 Data Dictionary Header，也就是數據字典的頭部信息

名稱

中文名

占用空間大小

簡單描述

File Header

文件頭部

38字節

頁的一些通用信息

Data Dictionary Header

數據字典頭部信息

56字節

記錄一些基本系統表的根頁面位置以及 InnoDB 存儲引擎的一些全局信息

Segment Header

段頭部信息

10字節

記錄本頁面所在段對應的 INODE Entry 位置信息

Empty Space

尚未使用空間

16272字節

用于頁結構的填充，沒啥實際意義

File Trailer

文件尾部

8字節

校驗頁是否完整

Data Dictionary Header部分的各個字段

Max Row ID:如果我們不顯示的為表定義主鍵，而且表中沒有 UNIQUE 索引，那么 InnoDB存儲引擎會默認生成一個名為 row_id 的列為主鍵。因為它是主鍵，所以每條記錄的 row_id 列的值不能重復。原則上只要一個表中的 row_id 列不重復就可以了，也就是說表 a 和表 b 擁有一樣的 row_id 列也沒啥關系，不過不過 InnoDB 只提供了這個 Max Row ID 字段，不論哪個擁有 row_id 列的表插入一條記錄時，該記錄的 row_id 列的值就是 Max Row ID 對應的值，然后再把Max Row ID對應的值加1，也就是說這個Max Row ID是全局共享的。 Max Table ID:InnoDB存儲引擎中的所有的表都對應一個唯一的 ID，每次新建一個表時，就會把本字段的值作為該表的 ID，然后自增本字段的值。 Max Index ID:InnoDB存儲引擎中的所有的索引都對應一個唯一的 ID，每次新建一個索引時，就會把本字段的值作為該索引的 ID，然后自增本字段的值。 Max Space ID:InnoDB存儲引擎中的所有的表空間都對應一個唯一的 ID，每次新建一個表空間時，就會把本字段的值作為該表空間的 ID，然后自增本字段的值。 Mix ID Low(Unused)：這個字段沒啥用，忽略。 Root of SYS_TABLES clust index：本字段代表 SYS_TABLES 表聚簇索引的根頁面的頁號。 Root of SYS_TABLE_IDS sec index：本字段代表 SYS_TABLES 表 ID 列建立的二級索引的根頁面的頁號。 Root of SYS_COLUMNS clust index：本字段代表 SYS_COLUMNS 表聚簇索引的根頁面的頁號。 Root of SYS_INDEXES clust index：本字段代表 SYS_INDEXES 表聚簇索引的跟頁面的頁號。 Root of SYS_FIELDS clust index：本字段代表 SYS_FIELDS 表聚簇索引的根頁面的頁號。

information_schema 系統數據庫

需要注意的一點是，用戶是不能直接訪問InnoDB的這些內部系統表的，除非直接去解析系統表空間對應文件系統上的文件。

InnoDB的設計者考慮到查看這些表的內容可能有助于大家分析文件，所以在系統數據庫information_schema中提供了一些以INNODB_SYS頭的表

5.7

mysql> use information_schema; mysql> show tables like 'INNODB_SYS%';

8.0

show tables like 'INNODB_%';

INNODB_COLUMNS

INNODB_FIELDS

INNODB_TABLES

INNODB_INDEXES

二?Innodb統計數據如何收集

統計數據的存儲方式 1 永久性存儲統計數據(磁盤) 2 非永久存儲(內存)

show variables like "%innodb_stats_persistent" # 控制存儲方式 on 代表磁盤永久存儲

Innodb 在存儲表和索引時候 還會維護這些數據的統計信息

show tables from mysql like "innodb%stats"

innodb_table_stats 存儲表的統計數據，每條記錄對應一個表統計數據 n_rows 記錄數（估算值 從聚簇索引取出幾個葉子節點數據頁, 算出平均記錄數, 再乘以全部葉子節點的數量） clustered_index_size 表的聚簇索引占的頁面數量 sum_of_other_index_sizes 表的其它索引占用的頁面數量

innodb_index_stats 存儲索引的統計數據 index_name 索引名 stat_name 針對這個所用用到統計項名稱 stat_value 對應的統計項的值 sample_size 為生成統計數據而采樣的頁面數量 stat_description 用來描述該統計項的信息 統計項概述 n_leaf_pages 表示該索引的葉子節點占用多少頁面 size 該索引占多少頁面（包括分配給葉子節點段或非葉子節點段但暫未使用的） n_diff_pfxNN : 對應索引列不重復的值；NN 可以被 01,02等代替

定期更新統計信息

1 自動 開啟 innodb_stats_auto_recalc

2 手動 ?analyze table? table_name

非永久存儲(內存) null 處理

Innodb_stats_method

show variables like "%Innodb_stats_method"

nulls_equal? 認為所有的null值都相等, 如果索引上null值很多, 優化器可能會選擇不適用索引進行訪問

nulls_unequal null值不相等

nulls_igored? 把null 值忽略

MySQL

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。