怎么理解并掌握mysql的表

本篇內容介紹了“怎么理解并掌握mysql的表”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！

一.索引組織表
如果在創建表時沒有定義主鍵，則innodb存儲引擎會按如下方式選擇或創建主鍵
-首先判斷表中是否有非空的唯一索引，如果有則該列即為主鍵，當表中有多個非空唯一索引時，innodb存儲引擎將選擇建表時第一個定義的非空唯一索引為主鍵。
-如果不符合上述條件，innodb存儲引擎自動創建一個6字節大小的指針。

點擊(此處)折疊或打開

1. 由于b為允許為null的唯一索引，所以該表的主鍵為d
   

2. create table wwj.z(
   

3.   a int not null,
   

4.   b int null,
   

5.   c int not null,
   

6.   d int not null,
   

7.   unique key (b),
   

8.   unique key (d),
   

9.   unique key (c)
   

10. );
    

11. insert into wwj.z select 1,2,3,4;
    

12. insert into wwj.z select 5,6,7,8;
    

13. insert into wwj.z select 9,10,11,12;
    

14. 
    

15. mysql> select a,b,c,d,_rowid from wwj.z;
    

16. +---+------+----+----+--------+
    

17. | a | b | c | d | _rowid |
    

18. +---+------+----+----+--------+
    

19. | 1 | 2 | 3 | 4 | 4 |
    

20. | 5 | 6 | 7 | 8 | 8 |
    

21. | 9 | 10 | 11 | 12 | 12 |
    

22. +---+------+----+----+--------+

二.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的表”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！