第四章 表 （學習筆記）

　　1. InnoDB邏輯存盤結構

　　表是根據主鍵順序組織存放的，這種存盤方式的表稱為索引組織表，

　　所有資料被邏輯的存放在一個空間中，稱為表空間（tablespace），表空間又由段（segment）、區（extent）、頁（page）等組成，

　　1.1 表空間

　　默認情況下，InnoDB存盤引擎有一個共享表空間ibdata1，即所有資料存放在這個表空間內，如果用戶啟用了引數innodb_file_per_table，則每張表內的資料可以單獨放到一個表空間內，單獨的表空間記憶體放的是資料、索引和插入緩沖Bitmap頁，其他的資料，如回滾（undo）資訊、插入緩沖索引頁、系統事務資訊、二次寫緩沖（double write buffer）等還是存放在原來的共享表空間內，

　　1.2 段

　　資料段為B+樹的葉子節點，索引段為B+樹的非索引節點，回滾段在第七章涉及，

　　1.3 區

　　區是由連續頁組成的空間，每個區的大小為1MB，為了保證區中頁的連續性，InnoDB存盤引擎一次從磁盤申請4-5個區，默認情況下，InnoDB存盤引擎頁的大小為16KB，即一個區一共有64個連續的頁，InnoDB 1.2.x新增了引數innodb_pages_size，通過該引數可以將頁設定為4K, 8K，

　　在每個段開始時，先用32個頁大小的碎片頁來存放資料，在使用完這些頁之后才是64個連續頁的申請，這是因為，對于一些小表，或者undo這類段，可以在開始時申請較少的空間，節省磁盤容量的開銷，

　　1.4 頁

　　頁是InnoDB磁盤管理的最小單位，InnoDB中常見的頁型別：

• 資料頁（B-Tree Node）
• undo頁（undo log page）
• 系統頁（System Page）
• 事務資料頁（Transaction System Page）
• 插入緩沖位圖頁（Insert Buffer Bitmap）
• 插入緩沖空閑串列頁（Insert Buffer Free List）
• 未壓縮的二進制大物件頁（Uncompressed BLOB Page）
• 壓縮的二進制大物件頁（Compressed BLOB Page）

　　1.5 行

　　InnoDB存盤引擎中，資料是按行進行存放的，每個頁最多允許存放16KB / 2 -200 行記錄，

　　2. InnoDB行記錄格式

　　2.1 Compact和Redundant 行記錄格式 參考博客：https://www.cnblogs.com/wilburxu/p/9435818.html

　　2.2 行溢位資料

　　MySQL官方手冊中定義的長度65535，單位為位元組，指的是一行中所有VARCHAR列的長度總和，如果超出，則無法創建，

　　有這樣一個問題，InnoDB存盤引擎的頁為16KB，即16384位元組，怎么存放65532位元組呢？

　　行資料只保存了前768位元組的前綴資料，之后是偏移量，指向行溢位頁，即BLOB page，

 　　2.3 Compressed 和 Dynamic 行記錄格式　　

　　InnoDB 1. 0.x 版本開始引入了新的檔案格式（file format，用戶可以理解為新的頁格式），以前支持的 Compact 和 Redundant 格式稱為 Antelope 檔案格式，新的檔案格式稱為 Barracuda 檔案格式，Barracuda 檔案格式下擁有兩種新的行記錄格式：Compressed 和 Dynamic，新的兩種記錄格式對于存放在 BLOB 中的資料采用了完全的行溢位的方式，如圖所示，在資料頁中只存放 20 個位元組的指標，實際的資料都存放在 Off Page 中，而之前的 Compact 和 Redundant 兩種格式會存放 768 個前綴位元組，　　

　　Compressed 行記錄格式的另一個功能就是，存盤在其中的行資料會以 zlib 的演算法進行壓縮，因此對于 BLOB、TEXT、VARCHAR 這類大長度型別的資料能夠進行非常有效的存盤，

　　3. InnoDB資料頁結構

　　該部分參考了博客： https://www.huaweicloud.com/articles/81e3e782d140e68cfea255e90be83889.html

　　下圖是InnoDB資料頁結構示意圖，以及各部分的簡單說明，

 　　每當我們插入一條記錄，都會從Free Space部分申請一個記錄大小的空間劃分到User Records部分，當Free Space部分的空間全部被User Records部分替代掉之后，也就意味著這個頁使用完了，如果還有新的記錄插入的話，就需要去申請新的頁了，這個程序的圖示如下：

　　下面兩張圖顯示的是一條記錄被洗掉的程序，　　

　　delete_mask 這個屬性標記著當前記錄是否被洗掉，這些被洗掉的記錄之所以不立即從磁盤上移除，是因為移除它們之后把其他的記錄在磁盤上重新排列需要性能消耗，所以只是打一個洗掉標記而已，所有被洗掉掉的記錄都會組成一個所謂的垃圾鏈表，在這個鏈表中的記錄占用的空間稱之為所謂的可重用空間，之后如果有新記錄插入到表中的話，可能把這些被洗掉的記錄占用的存盤空間覆寫掉，

　　min_rec_mask B+樹的每層非葉子節點中的最小記錄都會添加該標記，min_rec_mask值都是0，意味著它們都不是B+樹的非葉子節點中的最小記錄，

　　n_owned 在頁目錄分組時使用，每個組的最后一條記錄（也就是組內最大的那條記錄）的頭資訊中的n_owned屬性表示該記錄擁有多少條記錄，也就是該組內共有幾條記錄，

　　heap_no 這個屬性表示當前記錄在本頁中的位置，從圖中可以看出來，我們插入的4條記錄在本頁中的位置分別是：2、3、4、5，heap_no值為0和1的記錄，稱為偽記錄或者虛擬記錄，這兩個偽記錄一個代表最小記錄，一個代表最大記錄，　　

　　record_type 這個屬性表示當前記錄的型別，一共有4種型別的記錄，0表示普通記錄，1表示B+樹非葉節點記錄，2表示最小記錄，3表示最大記錄，

　　next_record 它表示從當前記錄的真實資料到下一條記錄的真實資料的地址偏移量，比方說第一條記錄的next_record值為32，意味著從第一條記錄的真實資料的地址處向后找32個位元組便是下一條記錄的真實資料，下一條記錄指得并不是按照我們插入順序的下一條記錄，而是按照主鍵值由小到大的順序的下一條記錄，而且規定 Infimum記錄（也就是最小記錄） 的下一條記錄就是本頁中主鍵值最小的用戶記錄，而本頁中主鍵值最大的用戶記錄的下一條記錄就是 Supremum記錄（也就是最大記錄）

　　Page Directory（頁目錄）　　

　　下圖是頁目錄的示意圖，如果根據主鍵值查找頁中某條記錄呢？　　

1. 通過二分法確定該記錄所在的槽，并找到該槽所在分組中主鍵值最小的那條記錄，

2. 通過記錄的next_record屬性遍歷該槽所在的組中的各個記錄，

　　4. Named File Formats機制

　　InnoDB存盤通過Named File Formats機制解決不同版本下頁結構兼容性問題，

　　5. 約束

　　推薦閱讀博客： https://blog.csdn.net/w_linux/article/details/79655073

 　　5.1 資料完整性

　　約束用來保證資料庫中資料的完整性，完整性有以下三種形式：

• 物體完整性保證表中有一個主鍵   用戶可以通過定義primary key 或 unique key約束來保證物體的完整性
• 域完整性保證每列的值滿足特定的條件  可以通過以下途徑保證：

　　1） 選擇合適的資料型別確保一個資料值滿足特定條件

　　2） 外鍵約束

　　3） 撰寫觸發器

　　4） DEFAULT 約束

• 參照完整性保證兩張表之間的關系  通過定義外鍵或撰寫觸發器以強制執行

　　InnoDB存盤引擎提供以下幾種約束：

• primary key
• unique key
• foreign key
• default
• not null

　　5.2 約束的創建和查找

　　約束的創建可以有以下兩種方式：

• 表建立時就進行約束定義
• 利用ALTER TABLE命令來進行約束創建

　　5.3 約束和索引的區別

　　的確，當用戶創建了唯一索引就創建了唯一約束，但是，約束是一個邏輯概念，是為了保證資料完整性，索引是一個資料結構，是為了提高查詢效率，

　　5.4 對錯誤資料的約束（NOT NULL）

　　在某些默認設定下，MySQL資料庫允許非法的或不正確的資料的插入或更新，通過設定引數sql_mode = 'STRICT_TRANS_TABLES' 對輸入的值進行約束，

　　5.5 ENUM 和 SET 約束

　　MySQL資料庫不支持傳統的CHECK約束，但是通過ENUM和SET可以解區域分這樣的約束需求，如下代碼所示：

　　但是ENUM只能對離散數值進行約束，對于傳統CHECK約束支持的連續值的范圍約束或更復雜的約束，需要通過觸發器來實作對于值域的約束，

　　5.6 觸發器與約束

　　觸發器的作用是在執行INSERT、DELETE和UPDATE命令之前或之后自動呼叫SQL命令或存盤程序，MySQL目前支持FOR EACH ROW的觸發方式，通過觸發器，用戶可以實作MySQL資料庫本身并不支持的一些特性，如對于傳統CHECK約束的支持、物化視圖、高級復制和審計等特性，下面代碼展示觸發器對約束的支持，

　　首先創建了一張usercah_err_log來記錄錯誤數值更新的日志，然后創建觸發器，判斷新、舊值之間的差值，大于原值的資料會被判斷為非法的輸入，將cash設定為原來的值，并將非法資料更新到usercash_err_log，

　　5.7 外鍵約束

　　MyISAM存盤引擎本身并不支持外鍵，對于外鍵的定義只起到一個注釋的作用，而InnoDB存盤引擎則完整支持外鍵約束，

　　一般來說，被參考的表稱為父表，參考的表稱為子表，可定義的子表操作有：

• CASCADE    當父表發生DELETE或UPDATE操作時，對相應的子表資料也進行UPDATE 或DELETE操作
• SET NULL     當父表發生DELETE或UPDATE操作時，對相應的子表資料更新為NULL
• NO ACTION  當父表發生DELETE或UPDATE操作時，拋出錯誤，不允許這類操作發生
• RESTRICT    當父表發生DELETE或UPDATE操作時，拋出錯誤，不允許這類操作發生

　　可以通過設定 foreign_key_checks = 0 在資料匯入程序中忽視外鍵檢查，

　　6. 視圖

　　6.1 視圖的作用

 　　在MySQL資料庫中，視圖（view）是一個命名的虛表，由一個SQL查詢來定義，可以當做表使用，與持久表不同的是，視圖中的資料沒有實際的物理存盤，

　　視圖的創建

CREATE [OR REPLACE]   
　　[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]  
　　[DEFINER = { user | CURRENT_USER }]  
　　[SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name [(column_list)]  
AS select_statement  
　　[WITH [CASCADED | LOCAL] CHECK OPTION]

　　視圖的優點：

• 簡單：對于一些應用程式，程式本身不需要關心基表的結構，只需要按照視圖定義來取資料或更新資料
• 安全：使用視圖的用戶只能訪問他們被允許查詢的結果集，對表的權限管理并不能限制到某個行某個列，但是通過視圖就可以簡單的實作
• 資料獨立：一旦視圖的結構確定了，可以屏蔽表結構變化對用戶的影響，源表增加列對視圖沒有影響；源表修改列名，則可以通過修改視圖來解決，不會造成對訪問者的影響

　　對視圖進行DML操作更新視圖時，更新的是其背后的基表，但是包含下列內容之一，視圖不能進行DML操作：

• select子句中包含distinct
• select子句中包含組函式
• select陳述句中包含group by子句
• select陳述句中包含order by子句
• select陳述句中包含union 、union all等集合運算子
• where子句中包含相關子查詢
• from子句中包含多個表
• 如果視圖中有計算列，則不能更新
• 如果基表中有某個具有非空約束的列未出現在視圖定義中，則不能做insert操作

　　6.2 物化視圖

　　Oracle資料庫支持物化視圖——根據基表實際存在的實表，即物化視圖的資料存盤在非易失的存盤設備上，物化視圖可以用于預先計算保存多表的鏈接（JOIN）或聚集（GROUP BY）等耗時較多的SQL操作結果，這樣，在執行復雜查詢時，就可以避免進行這些耗時的操作，從而快速得到結果，物化視圖有兩種重繪方式：

• ON DEMAND  在用戶需要時進行重繪
• ON COMMIT　物化視圖在對基表的DML操作提交的同時進行重繪

　　MySQL本身并不支持物化視圖，但是可以通過一些方法來部分實作物化視圖的功能，

　　實作 ON DEMAND 功能：

　　有如下訂單表，記錄了用戶采購電腦設備的資訊　　

CREATE TABLE Orders
(
order_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
product_name  VARCHAR(30) NOT NULL,
price DECIMAL(8, 2) NOT NULL,
amount  SMALLINT   NOT NULL,
PRIMARY KEY  (order_id)
)ENGINE = InnoDB


INSERT INTO Orders VALUES
(NULL, 'CPU', 135.5, 1),
(NULL, 'Memory', 48.2, 3),
(NULL, 'CPU', 125.6, 3),
(NULL, 'CPU', 105.3, 4);

　　接著創建一張物化視圖的基表，用來統計每件物品的資訊

CREATE TABLE Orders_MV(
product_name   VARCHAR(30)       NOT NULL,
price_sum      DECIMAL(8,2)      NOT NULL,
amount_sum     INT               NOT NULL,
price_avg      FLOAT             NOT NULL,
orders_cnt     INT               NOT NULL,
UNIQUE   INDEX  (product_name);


INSERT  INTO Orders_MV
SELECT  product_name, SUM(price), SUM(amount), AVG(price), 
             COUNT(*) 
            FROM Orders
            GROUP BY product_name;

　　通過以上方式，用戶就擁有了一個統計資訊的物化視圖，如果要實作ON DEMAND功能，只需要把表清空，重新匯入資料即可，

　　如果要實作ON COMMIT 的物化視圖，需要借助觸發器來達到目的，需要對表Orders建立一個觸發器： 

DELIMITER  $$


CREATE  TRIGGER  tgr_Orders_insert
AFTER  INSERT ON Orders
FOR EACH ROW
BEGIN
    SET @old_price_sum = 0;
    SET @old_amount_sum = 0;
    SET @old_price_avg = 0;
    SET @old_orders_cnt = 0;

    SELECT IFNULL(price_num, 0), IFNULL(amount_sum, 0), IFNULL(price_avg, 0), IFNULL(order_cnt, 0)
    FROM Orders_MV
    WHERE product_name = NEW.product_name
    INTO @old_price_sum, @old_amount_sum, @old_price_avg, @old_orders_cnt;
    
    SET @new_price_sum = @old_price_sum + NEW.price;
    SET @new_amount_sum = @old_amount_sum + NEW.amount;
    SET @new_orders_cnt = @old_orders_cnt +1;
    SET @new_price_avg = @new_price_sum / @new_orders_cnt;

    REPLACE INTO Orders_MV
    VALUES(NEW.product_name, @new_price_sum, @new_amount_sum, @new_price_avg, @new_orders_cnt);    
    END
    $$

    DELIMITER;

　　由于MySQL資料庫本身并不支持物化視圖，因此對于物化視圖支持的查詢重寫功能就顯得無能為力了，用戶只能在應用程式端做一些控制　　

　　7. 磁區表

 　　7.1 磁區概述

　　MyISAM, InnoaDB, NDB等存盤引擎都支持磁區功能，磁區的程序是將一個表或索引分解為多個更小，更可管理的部分，就訪問資料庫的應用而言，邏輯上講，只有一個表或一個索引，但是物理上這個表或索引可能由數十個物理磁區組成，每個磁區都是獨立的物件，可以獨自處理，也可以作為一個更大物件的一部分進行處理，MySQL資料庫支持以下幾種型別的磁區：

• RANGE磁區：行資料基于一個給定連續區間的列值被放入磁區
• LIST磁區：和RANGE磁區類似，只是LIST磁區面向的是離散的值
• HASH磁區：根據用戶自定義的運算式的回傳值來進行磁區
• KEY磁區：根據MySQL資料庫提供的哈希函式來進行磁區

　　不論創建何種型別的磁區，如果表中存在主鍵或唯一索引時，磁區列必須是唯一索引的一個組成部分，下面創建磁區的SQL陳述句會產生錯誤，

　　7.2.1 RANGE 磁區

　　下面的SQL陳述句根據id列來創建磁區表，id小于10時，資料插入P0，大于等于10，小于20時，插入P1磁區，

　　查看表在磁盤上的物理檔案，啟用磁區之后，表不再由一個ibd檔案組成了，而是由各個磁區ibd檔案組成，如下面的p0.ibd, p1.ibd.

　　RANGE磁區主要用于日期列的磁區，例如對于銷售類的表，可以根據年份來磁區存放銷售記錄，這種創建方式便于對sales表的管理：

• 可以通過alter table sales drop partition p2008 直接將2008年資料所在磁區洗掉
• 加快某些查詢操作

EXPLAIN PARTITIONS
SELECT * FROM sales
WHERE date>='2008-01-01' AND date<='2008-12-31'

　　SQL優化器只會去搜索P2008這個磁區，而不是搜索所有的磁區——稱為Partition Pruning（磁區修剪），查詢速度會大幅提升，

　　優化器只能對YEAR(), TO_DAYS(), TO_SECONDS(), UNIX_TIMESTAMP()這類函式進行優化選擇，　　

　　7.2.2 LIST磁區

　　list磁區中的值是離散的，示例代碼如下：

　　用INSERT插入多行資料的程序中遇到磁區未定義的值時，MyISAM會將之前滿足要求的行資料都插入，不滿足要求的行資料即之后的不會插入，InnoDB將其視為一個事務，因此沒有任何資料會被插入，

　　7.2.3 COLUMNS磁區

　　前面介紹的RANGE, LIST, HASH和KEY磁區中，資料必須是整型，如果不是整型，需要通過YEAR(), TO_DAYS(), MONTH()等函式化為整型，COLUMNS磁區可以直接使用非整型的資料進行磁區，磁區資料型別直接比較而得，示例代碼如下：

　　7.2.4 子磁區

　　子磁區是在磁區的基礎上在進行磁區，MySQL資料庫允許在RANGE和LIST的磁區上再進行HASH 或 KEY的子磁區，

　　7.2.5 磁區中的NULL 值

　　對于RANGE磁區，如果向磁區列插入了NULL值，則MySQL資料庫會將該值放入最左邊的磁區，

　　對于LIST磁區，需要顯式地指出那個磁區中放入NULL值，

　　HASH 和 KEY 磁區中，任何磁區函式都會將含有NULL值地記錄回傳為0.

　　7.3 磁區和性能

　　資料庫的應用分為兩類：一類是OLTP（在線事務處理），如blog，電子商務，網路游戲等；另一類是OLAP（在線分析處理），如資料倉庫，資料集市，在一個實際的應用環境中，可能既有OLTP應用，也有OLAP應用，如網路游戲中，玩家操作的游戲資料庫應用就是OLTP的，但是游戲廠商需要對游戲產生的日志進行分析，通過分析得到的結果來更好地服務于游戲，預測玩家的行為等，而這是OLAP的應用，

　　對于OLAP應用，磁區可以提高查詢的性能，因為，OLAP應用大多數查詢需要頻繁掃描一張很大的表，假設有一張1億行的表，其中有一個時間戳屬性列，用戶需要查詢表獲取一年的資料，如果按時間戳進行磁區，只需要掃描相應的磁區即可，

　　對于OLTP應用，通常不可能會獲取一張大表中10%的資料，大部分都是通過索引回傳幾條記錄，對于一張大表，一般的B+樹需要2~3次的磁盤IO，

　　如果對主鍵進行查詢磁區是有意義的，

　　如果用其他索引進行查詢，查詢開銷則大的多，對于KEY的查詢需要掃描所有的10個磁區，每個磁區的查詢開銷為2次IO，一共需要20次IO，

標籤：其他

上一篇：第三章 檔案（學習筆記）

下一篇：第六章 鎖（學習筆記）