共計 3759 個字符，預計需要花費 10 分鐘才能閱讀完成。
本篇內容介紹了“怎么理解并掌握 mysql 的表”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓丸趣 TV 小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！
一. 索引組織表
如果在創建表時沒有定義主鍵，則 innodb 存儲引擎會按如下方式選擇或創建主鍵
- 首先判斷表中是否有非空的唯一索引，如果有則該列即為主鍵，當表中有多個非空唯一索引時，innodb 存儲引擎將選擇建表時第一個定義的非空唯一索引為主鍵。
- 如果不符合上述條件，innodb 存儲引擎自動創建一個 6 字節大小的指針。
點擊 (此處) 折疊或打開
由于 b 為允許為 null 的唯一索引，所以該表的主鍵為 d
create table wwj.z(
a int not null,
b int null,
c int not null,
d int not null,
unique key (b),
unique key (d),
unique key (c)
);
insert into wwj.z select 1,2,3,4;
insert into wwj.z select 5,6,7,8;
insert into wwj.z select 9,10,11,12;
mysql select a,b,c,d,_rowid from wwj.z;
+—+——+—-+—-+——–+
| a | b | c | d | _rowid |
+—+——+—-+—-+——–+
| 1 | 2 | 3 | 4 | 4 |
| 5 | 6 | 7 | 8 | 8 |
| 9 | 10 | 11 | 12 | 12 |
+—+——+—-+—-+——–+
二.innodb 邏輯存儲結構
1. 表空間
默認為所有數據都存放在共享表空間中。如果開啟參數 innodb_file_per_table 之后，則每張表的數據將會放在單獨的表空間中。
如果啟用了 innodb_file_per_table，則每張表的表空間內存放的只是數據，索引，和插入緩沖的 bitmap 頁。其他類的數據，如插入緩沖索引頁，系統事務信息，二次寫緩沖，還是存放在原來的共享表空間中。
2. 段
表空間是由各個段組成的，常見的段有數據段，索引段，回滾段等。
段的管理都是由引擎自身所完成，DBA 不能也沒有必要對其進行控制。
3. 區
區是由連續頁組成的空間，在任何情況下每個區的大小都為 1m。
為了保證區中頁的連續性，innodb 存儲引擎一次從磁盤申請 4~5 個區。默認情況 innodb 存儲引擎頁的大小為 16kb，即一個區中一共有 64 個連續的頁。
當用戶啟用參數 innodb_file_er_table 后，創建的表默認大小是 96kb，而不是區的默認大小 1m。這是因為在每個段開始時，先用 32 個頁大小的碎片頁來存放數據，使用完這些碎片頁后，才是 64 個連續頁的申請。這樣做的目的是，對于一些小表，或者是 undo 這類的段，可以在開始時申請較少的空間，節省磁盤容量的開銷。
4. 頁
innodb 磁盤管理的最小單位
常見的頁類型有:
數據頁
undo 頁
系統頁
事務數據頁
插入緩沖位圖頁
插入緩沖空閑列表頁
未壓縮的二進制大對象頁
壓縮的二進制大對象頁
5. 行
三.innodb 行記錄格式
1.compact 行記錄格式
5.0 以后引入，其設計目的是高效的存儲數據，簡單來說，一個頁中存放的行數據越多，其性能越高。
2.redundant
5.0 之前的行記錄存儲格式
3. 行溢出數據
行存放 varchar 類型的數據最大長度和為 65532 字節。
建表時 varchar(N)表示的是字符長度。
例如: 下面建表語句將會報錯
create table haha22(
a varchar(65532)
) charset=utf8 engine=innodb;
ERROR 1074 (42000): Column length too big for column a (max = 21845); use BLOB or TEXT instead
下面語句可以正常執行
create table haha22(
a varchar(1)
) charset=utf8 engine=innodb;
insert into wwj.haha22 values (陽
行溢出數據存儲圖:
那么多長的 varchar 是保存在單個數據頁中的，從多長開始又會保存在 blob 頁呢？
可以這樣進行思考:innodb 存儲引擎表是索引組織的，即 B +Tree 結構，這樣每個頁中至少應該有兩條行記錄，因此如果頁中只能存放下一條記錄，那么 innodb 存儲引擎會自動將行數據存放到溢出頁中。
經過實驗發現當行的長度為 8098 字節時，可以在頁中放入兩條記錄，并且不用擴展至 blob 頁。
對于 TEXT 和 BLOB 數據類型也是如此，如果在數據頁中能存放至少 2 條記錄，則將不會將記錄擴展至 blob 頁。
4. 新的文件格式 barracuda
該文件格式下擁有兩種新的行記錄格式:compressed 和 dynamic
新的記錄格式對于存放在 blob 中的數據采用了完全的行溢出的方式，在數據頁中只存放 20 個字節的指針，實際數據都存放在 off page 中，而之前的 compact 和 redundant 兩種格式會存放 768 個前綴字節。
圖
compressed 行記錄格式的另一個功能就是，存儲在其中的行數據會以 zlib 的算法進行壓縮。因此對于 blob，text，varchar 這類大長度類型的數據能夠進行非常有效的存儲。
參數 innodb_file_format 用來指定文件格式
四. 約束
約束類型
primary key
unique key
foreign key
default
not null
五. 分區表
1.mysql 支持以下幾種類型的分區:
range 分區
list 分區
hash 分區
key 分區
* 如果表中存在主鍵或唯一索引時，分區列必須是唯一索引的一個組成部分. 如果建表時沒有指定主鍵，唯一索引，可以指定任何一個列為分區列。
* mysql 分區表中的索引都是局部分區索引，不支持全局索引
2. 分區類型
(1).range 分區
create table wwj.sales(
money int,
date1 datetime
) engine=innodb
partition by range(year(date1)) (
partition p2008 values less than (year( 2009-01-01)),
partition p2009 values less than (year( 2010-01-01)),
partition p2010 values less than (year( 2011-01-01))
);
優化器只能對 year(),to_days(),to_seconds(),unix_timestamp()這類函數進行優化選擇
(2).list 分區
create table wwj.tt(
a int,
b int
) engine=innodb
partition by list(b) (
partition p0 values in (1,3,5,7,9),
partition p1 values in (0,2,4,6,8)
);
(3).hash 分區
目的是將數據均勻的分布到預先定義的各個分區中，保證各分區的數據量大致都是一樣的
– 普通 hash 分區
create table wwj.qqq(
a int,
b datetime
) engine=innodb
partition by hash (year(b))
partitions 4;
分配記錄的算法如下:
mod(year( 2010-04-01),4) = mod(2010,4) =2
因此記錄會放在 p2 分區中
–linear hash 分區
create table wwj.qqq(
a int,
b datetime
) engine=innodb
partition by hash (year(b))
partitions 4;
分配記錄的算法如下:
取大于分區數量 4 的下一個 2 的冪值 V V=POWER(2,CEILING(LOG(2,num)))=4
所在分區 N =YEAR(2010-04-01) (V-1)=2
因此記錄會放在 p2 分區中
linear hash 分區的優點在于，增加，刪除，合并，拆分分區將變得更加快捷，有利于處理含有大量數據的表。
缺點在于數據分布可能不太均衡。
(4).key 分區
和 hash 分區相似，不同之處在于 hash 分區使用用戶定義的函數進行分區，key 分區使用 mysql 數據庫提供的函數進行分區。
create table wwj.www(
a int,
b datetime
) engine=innodb
partition by key (b)
partitions 4;
(5).columns 分區
前面的 4 種分區中，分區的條件是: 數據必須是整形，如果不是整形，則需要通過函數將其轉化為整形。
columns 分區可以直接使用非整形的數據進行分區，分區根據類型直接比較而得，不需要轉化為整形，并且支持多個列的值進行分區。
columns 支持以下數據類型:
int,smallint,tinyint,bigint,date,datetime,char,varchar,binary,varbinary
“怎么理解并掌握 mysql 的表”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注丸趣 TV 網站，丸趣 TV 小編將為大家輸出更多高質量的實用文章！
