[TOC] ## 索引組織表 根據主鍵順序存放的這種存儲方式的表稱為索引組織表。 選擇主鍵或者創建主鍵： 第一個非空唯一索引（Unique NOT NULL）為主鍵。如果沒有則自動創建一個6字節大小的指針。 ***** ## 邏輯存儲結構 InnoDB邏輯存儲結構：所有數據都被邏輯地存放在一個空間中,稱為表空間。表空間又由段（）、區（extent）、頁（page）組成。  ### 表空間 邏輯存儲結構的最高層，所有數據保存在表空間中。 InnoDB存儲引擎默認有一個表共享空間ibdata1，所有數據放在這個表空間內。 如果啟用參數innodb_file_per_table，則每張表數據可以單獨放到一個表空間內。 啟用之后：數據、索引、插入緩沖Bitmap頁存放在表空間。其他類數據，如回滾（undo）信息等放在原來的共享表空間中。 ### 段 表空間由各個段組成，常見的段有數據段、索引段、回滾段等。 ### 區 區有連續頁組成的空間，在任何情況下每個區的大小都為1MB。為了頁的連續性，InnoDB存儲引擎一次申請4~5個區。引擎頁的大小為16KB，即一個區中一共有64個連續頁。 InnoDB1.2.x 參數innodb_page_size，通過該參數設置默認頁的大小4K、8k。 ### 頁 頁也稱為塊，頁是磁盤管理的最小單位。默認每個頁的大小16KB。若innodb_page_size設置完成，不可以再次進行修改，除非通過mysqldump 導入和導出操作來產生新的庫。 常見頁類型： （1）數據頁 （2） undo 頁 （3）系統頁 （4）事務數據頁 （5）插入緩沖位圖頁 （6）插入緩沖空閑列表頁 （7）未壓縮的二進制大對象頁 （8）壓縮的二進制大對象頁 ### 行 InnoDB存儲引擎是面向列的（row -oriented），也就說數據是按行進行存放的。 ***** ## InnoDB行記錄格式 InnoDB記錄是以行的形式存儲的。由Compact 和 Redundant 兩種格式存放記錄數據。 ### Compact 行記錄格式 一個頁面存放的行數據越多,其性能就越高。  由圖觀察 Compact行記錄格式的首部是一個非NULL變長字段長度列表，并且按照列順序逆序放置的其長度： （1）列長度小于255字節，用1表示； （2）大于255字節用2字節表示； 變長字段的長度最大不能超過2字節。  在Compact 行記錄格式NULL不占用存儲空間。 ### Redundant 行記錄格式 Redundant 是Mysql5.0版本之前的行記錄存儲方式  redundant的存儲格式為 首部是一個字段長度偏移列表（每個字段占用的字節長度及其相應的位移），同樣是按照列的順序逆序放置，當列的長度小于255字節，用1字節表示，若大于 255個字節，用2字節表>示。第二個部分為記錄頭信息（record header），不同與compact行格式，它的行格式固定占用6個字節，最后的部分就是實際存儲的每個列的數據，NULL不占該部分任何數據，但是 char中如果有NULL值則需要占用相應的字節，另外注意,每行數據除了用戶定義的列外，還有兩個隱藏列，事務ID（6字節），會滾指針列（7字節）， 若INNODB表沒有定義,Primay key,那么每行會增加一個6字節的rowid，如果有，怎有4個字節的索引字段。  ### 行溢出數據 VARCHAR最大長度65535字節 但是InnoDB有其他的開開銷。在SQL_MODE嚴格模式實際測試發現VARCHAR類型的最大長度為65532。***這里的65532是字節不是字符` 在utf-8最大的只有21845字節 而在GBK是32767字節。*** 如果沒有則會報一個警告：warning則會自動轉換成TEXT， 一般情況下，InnoDB存儲引擎的數據都是存放在頁類型為B-tree node中，但是發送溢出時，數據存放在也類型為Uncompress BLOB頁中。 ### Compressed 和 Dynamic 行記錄格式 InnoDB 1.0.x開始引入新的文件格式，以前Compact 和 Redundant 格式稱為Antelope文件格式，新文件格式稱為Barracuda文件格式。Barracuda文件格式擁有兩種新的行記錄格式：Compressed 和 Dynamic。  Compressed 行記錄格式的另一個功能是：存儲在其中的行數據會議zlib的算法進行壓縮。因此對BLOB、TEXT、VARCHAR這類的大長度數據進行很好的存儲. ### CHAR 的行結構存儲 CHAR是存儲固定長度的字符類型。 MySQL4.1版本開始 CHR（N）中的N指的是字符的長度,在不同的字符集下,CHAR類型列內部存儲都可能不是定長的數據。 ***** ## InnoDB 數據頁結構 頁是InnoDB存儲引擎管理數據庫的最小磁盤單位。頁類型為B-tree node的頁，存放的即是表中行的實際數據了。 InnoDB數據頁由以下七個部分組成，如圖所示： File Header（文件頭）。 Page Header（頁頭）。 Infimun+Supremum Records。 User Records（用戶記錄，即行記錄）。 Free Space（空閑空間）。 Page Directory（頁目錄）。 File Trailer（文件結尾信息）。 __File Header、Page Header、File Trailer的大小是固定的，用來標示該頁的一些信息，如Checksum、數據所在索引層等。__ 其余部分為實際的行記錄存儲空間，因此大小是動態的。其余部分為實際的行記錄存儲空間，因此大小是動態的。  ### File Header File Header用來記錄頁的一些頭信息，由如下8個部分組成，共占用38個字節，如表   ### Page Header 接著File Header部分的是Page Header，用來記錄數據頁的狀態信息，由以下14個部分組成，共占用56個字節。  ### Infimun+Supremum Records __在InnoDB存儲引擎中，每個數據頁中有兩個虛擬的行記錄，用來限定記錄的邊界。Infimum記錄是比該頁中任何主鍵值都要小的值，Supremum指比任何可能大的值還要大的值。這兩個值在頁創建時被建立，并且在任何情況下不會被刪除。__ 在Compact行格式和Redundant行格式下，兩者占用的字節數各不相同。  ### User Records與FreeSpace User Records即實際存儲行記錄的內容。再次強調，InnoDB存儲引擎表總是B+樹索引組織的。 Free Space指的就是空閑空間，同樣也是個鏈表數據結構。當一條記錄被刪除后，該空間會被加入空閑鏈表中。 ### Page Directory __Page Directory（頁目錄）中存放了記錄的相對位置（注意，這里存放的是頁相對位置，而不是偏移量），有些時候這些記錄指針稱為Slots（槽）或者目錄槽（Directory Slots）。__ 與其他數據庫系統不同的是，InnoDB并不是每個記錄擁有一個槽，InnoDB存儲引擎的槽是一個稀疏目錄（sparse directory），即一個槽中可能屬于（belong to）多個記錄，最少屬于4條記錄，最多屬于8條記錄。 Slots中記錄按照鍵順序存放，這樣可以利用二叉查找迅速找到記錄的指針。假設我們有（'i'，'d'，'c'，'b'，'e'，'g'，'l'，'h'，'f'，'j'，'k'，'a'），同時假設一個槽中包含4條記錄，則Slots中的記錄可能是（'a'，'e'，'i'）。 由于InnoDB存儲引擎中Slots是稀疏目錄，二叉查找的結果只是一個粗略的結果，所以InnoDB必須通過recorder header中的next_record來繼續查找相關記錄。同時，slots很好地解釋了recorder header中的n_owned值的含義，即還有多少記錄需要查找，因為這些記錄并不包括在slots中。 需要牢記的是，__B+樹索引本身并不能找到具體的一條記錄，B+樹索引能找到只是該記錄所在的頁。數據庫把頁載入內存，然后通過Page Directory再進行二叉查找。只不過二叉查找的時間復雜度很低，同時內存中的查找很快，因此通常我們忽略了這部分查找所用的時間。__ ### File Trailer 為了保證頁能夠完整地寫入磁盤（如可能發生的寫入過程中磁盤損壞、機器宕機等原因），InnoDB存儲引擎的頁中設置了File Trailer部分。File Trailer只有一個FIL_PAGE_END_LSN部分，占用8個字節。前4個字節代表該頁的checksum值，最后4個字節和File Header中的FIL_PAGE_LSN相同。通過這兩個值來和File Header中的FIL_PAGE_SPACE_OR_CHKSUM和FIL_PAGE_LSN值進行比較，看是否一致（checksum的比較需要通過InnoDB的checksum函數來進行比較，不是簡單的等值比較），以此來保證頁的完整性（not corrupted）。 ## InnoDB數據頁結構示例分析 首先我們建立一張表，并導入一定量的數據： ~~~ drop table if exists t; mysql -> create table t (a int unsigned not null auto_increment，b char(10),primary key(a))ENGINE=InnoDB CHARSET=UTF-8; mysql ->delimiter$$ 　　->create procedure load_t(count int unsigned) 　->begin 　　　　->set@c=0; 　　　　　　->while@c＜count do 　　　　　　　　->insert into t select null,repeat(char(97+rand()*26),10); 　　　　　　　　->set@c=@c+1; 　　　　　　->end while; 　　　　->end; ->$$ mysql ->delimiter; mysql ->call load_t(100); mysql ->select * from t limit 10; ~~~ 接著我們用工具`py_innodb_page_info`來分析`t.ibd, py_innodb_page_info.py -v t.ibd`  看到第四個頁（page offset 3）是數據頁，通過hexdump來分析t.ibd文件，打開整理得到的十六進制文件，數據頁在0x0000c000（16K*3=0xc000）處開始： 先來分析前面File Header的38個字節： 52 1b 24 00數據頁的Checksum值。 00 00 00 03頁的偏移量，從0開始。 ff ff ff ff前一個頁，因為只有當前一個數據頁，所以這里為0xffffffff。 ff ff ff ff下一個頁，因為只有當前一個數據頁，所以這里為0xffffffff。 00 00 00 0a 6a e0 ac 93頁的LSN。 45 bf頁類型，0x45bf代表數據頁。 00 00 00 00 00 00 00這里暫時不管該值。 00 00 00 dc表空間的SPACE ID。 先不急著看下面的Page Header部分，我們來看File Trailer部分。因為File Trailer通過比較File Header部分來保證頁寫入的完整性。 95 ae 5d 39 Checksum值，該值通過checksum函數和File Header部分的checksum值進行比較。 6a e0 ac 93注意到該值和File Header部分頁的LSN后4個值相等。 接著我們來分析56個字節的Page Header部分，對于數據頁而言，Page Header部分保存了該頁中行記錄的大量細節信息。分析后可得： Page Header（56 bytes）： PAGE_N_DIR_SLOTS=0x001a PAGE_HEAP_TOP=0x0dc0 PAGE_N_HEAP=0x8066 PAGE_FREE=0x0000 PAGE_GARBAGE=0x0000 PAGE_LAST_INSERT=0x0da5 PAGE_DIRECTION=0x0002 PAGE_N_DIRECTION=0x0063 PAGE_N_RECS=0x0064 PAGE_MAX_TRX_ID=0x0000000000000000 PAGE_LEVEL=00 00 PAGE_INDEX_ID=0x00000000000001ba PAGE_BTR_SEG_LEAF=0x000000dc0000000200f2 PAGE_BTR_SEG_TOP=0x000000dc000000020032 PAGE_N_DIR_SLOTS=0x001a，代表Page Directory有26個槽，每個槽占用2個字節。 我們可以從0x0000ffc4到0x0000fff7找到如下內容： ~~~ 0000ffc0 00 00 00 00 00 70 0d 1d 0c 95 0c 0d 0b 85 0a fd|……p…… 0000ffd0 0a 75 09 ed 09 65 08 dd 08 55 07 cd 07 45 06 bd|.u……e……U……E.. 0000ffe0 06 35 05 ad 05 25 04 9d 04 15 03 8d 03 05 02 7d|.5……%……} 0000fff0 01 f5 01 6d 00 e5 00 63 95 ae 5d 39 6a e0 ac 93|……m……c..]9j…… ~~~ PAGE_HEAP_TOP=0x0dc0代表空閑空間開始位置的偏移量，即0xc000+0x0dc0=0xcdc0處開始，我們觀察這個位置的情況，可以發現這的確是最后一行的結束，接下去的部分都是空閑空間了： ~~~ 0000cdb0 00 00 00 2d 01 10 70 70 70 70 70 70 70 70 70 70|……-..pppppppppp 0000cdc0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00|…… 0000cdd0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00|…… 0000cde0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00|…… ~~~ PAGE_N_HEAP=0x8066，當行記錄格式為Compact時，初始值為0x0802，當行格式為Redundant時，初始值是2。其實這些值表示頁初始時就已經有Infinimun和Supremum的偽記錄行，0x8066-0x8002=0x64，代表該頁中實際的記錄有100條記錄。 PAGE_FREE=0x0000代表刪除的記錄數，因為這里我們沒有進行過刪除操作，所以這里的值為0。 PAGE_GARBAGE=0x0000，代表刪除的記錄字節為0，同樣因為我們沒有進行過刪除操作，所以這里的值依然為0。 PAGE_LAST_INSERT=0x0da5，表示頁最后插入的位置的偏移量，即最后的插入位置應該在0xc0000+0x0da5=0xcda5，查看該位置： ~~~ 0000cda0 00 03 28 f2 cb 00 00 00 64 00 00 00 51 6e 4e 80|..（……d……QnN. 0000cdb0 00 00 00 2d 01 10 70 70 70 70 70 70 70 70 70 70|……-..pppppppppp 0000cdc0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00|…… ~~~ 可以看到，最后這的確是最后插入a列值為100的行記錄，但是這次直接指向了行記錄的內容，而不是指向行記錄的變長字段長度的列表位置。 PAGE_DIRECTION=0x0002，因為我們是通過自增長的方式進行行記錄的插入，所以PAGE_DIRECTION的方向是向右。 PAGE_N_DIRECTION=0x0063，表示一個方向連續插入記錄的數量，因為我們是以自增長的方式插入了100條記錄，因此該值為99。 PAGE_N_RECS=0x0064，表示該頁的行記錄數為100，注意該值與PAGE_N_HEAP的比較，PAGE_N_HEAP包含兩個偽行記錄，并且是通過有符號的方式記錄的，因此值為0x8066。 PAGE_LEVEL=0x00，代表該頁為葉子節點。因為數據量目前較少，因此當前B+樹索引只有一層。B+數葉子層總是為0x00。 PAGE_INDEX_ID=0x00000000000001ba，索引ID。 上面就是數據頁的Page Header部分了，接下去就是存放的行記錄了，前面提到過InnoDB存儲引擎有2個偽記錄行，用來限定行記錄的邊界，我們接著往下看： ~~~ 0000c050 00 02 00 f2 00 00 00 dc 00 00 00 02 00 32 01 00|……2.. 0000c060 02 00 1c 69 6e 66 69 6d 75 6d 00 05 00 0b 00 00|……infimum…… 0000c070 73 75 70 72 65 6d 75 6d 0a 00 00 00 10 00 22 00|supremum……". ~~~ 觀察0xc05E到0xc077，這里存放的就是這兩個偽行記錄，InnoDB存儲引擎設置偽行只有一個列，且類型是Char（8）。偽行記錄的讀取方式和一般的行記錄并無不同，我們整理后可以得到如下的結果： ~~~ #Infimum偽行記錄 00 02 00 1c/*recorder header*/ 6e 66 69 6d 75 6d 00/*只有一個列的偽行記錄，記錄內容就是Infimum（多了一個0x00字節） */ #Supremum偽行記錄 00 0b 00 00/*recorder header*/ 75 70 72 65 6d 75 6d/*只有一個列的偽行記錄，記錄內容就是Supremum*/ ~~~ 我們來分析infimum行記錄的recorder header部分，最后2個字節位00 1c表示下一個記錄的位置的偏移量，即當前行記錄內容的位置0xc063+0x001c，得到0xc07f。0xc07f應該很熟悉了，我們前面的分析的行記錄結構都是從這個位置開始。我們來看一下： ~~~ 0000c070 73 75 70 72 65 6d 75 6d 0a 00 00 00 10 00 22 00|supremum……". 0000c080 00 00 01 00 00 00 51 6d eb 80 00 00 00 2d 01 10|……Qm……-.. 0000c090 64 64 64 64 64 64 64 64 64 64 0a 00 00 00 18 00|dddddddddd…… 0000c0a0 22 00 00 00 02 00 00 00 51 6d ec 80 00 00 00 2d|"……Qm……- 可以看到這就是第一條實際行記錄內容的位置了，如果整理后可以得到： /*第一條行記錄*/ 00 00 01/*因為我們建表時設定了主鍵，這里ROWID即位列a的值1*/ 00 00 51 6d eb/*Transaction ID*/ 00 00 00 2d 01 10/*Roll Pointer*/ 64 64 64 64 64 64 64 64 64/*b列的值'aaaaaaaaaa'*/ ~~~ 這和我們查表得到的數據是一致的：select a,b,hex(b) from t order by a limit 1; 通過recorder header最后2個字節記錄的下一行記錄的偏移量，我們就可以得到該頁中所有的行記錄；通過page header的PAGE_PREV，PAGE_NEXT就可以知道上一個頁和下個頁的位置。這樣，我們就能讀到整張表所有的行記錄數據。 最后我們來分析Page Directory，前面我們已經提到了從0x0000ffc4到0x0000fff7是當前頁的Page Directory，如下： ~~~ 0000ffc0 00 00 00 00 00 70 0d 1d 0c 95 0c 0d 0b 85 0a fd|……p…… 0000ffd0 0a 75 09 ed 09 65 08 dd 08 55 07 cd 07 45 06 bd|.u……e……U……E.. 0000ffe0 06 35 05 ad 05 25 04 9d 04 15 03 8d 03 05 02 7d|.5……%……} 0000fff0 01 f5 01 6d 00 e5 00 63 95 ae 5d 39 6a e0 ac 93|……m……c..]9j…… ~~~ 需要注意的是，Page Directory是逆序存放的，每個槽2個字節。因此我們可以看到：00 63是最初行的相對位置，即0xc063；0070就是最后一行記錄的相對位置，即0xc070。我們發現，這就是前面我們分析的infimum和supremum的偽行記錄。Page Directory槽中的數據都是按照主鍵的順序存放，因此找具體的行就需要通過部分進行。前面已經提到，InnoDB存儲引擎的槽是稀疏的，還需通過recorder header的n_owned進行進一步的判斷。如，我們要找主鍵a為5的記錄，通過二叉查找Page Directory的槽，我們找到記錄的相對位置在00 e5處，找到行記錄的實際位置0xc0e5： ~~~ 0000c0e0 04 00 28 00 22 00 00 00 04 00 00 00 51 6d ee 80|..（."……Qm.. 0000c0f0 00 00 00 2d 01 10 69 69 69 69 69 69 69 69 69 69|……-..iiiiiiiiii 0000c100 0a 00 00 00 30 00 22 00 00 00 05 00 00 00 51 6d|……0."……Qm 0000c110 ef 80 00 00 00 2d 01 10 6e 6e 6e 6e 6e 6e 6e 6e|……-..nnnnnnnn 0000c120 6e 6e 0a 00 00 00 38 00 22 00 00 00 06 00 00 00|nn……8."…… 0000c130 51 6d f0 80 00 00 00 2d 01 10 71 71 71 71 71 71|Qm……-..qqqqqq 0000c140 71 71 71 71 0a 00 00 00 40 00 22 00 00 00 07 00|qqqq……@."…… ~~~ 可以看到第一行的記錄是4不是我們要找的5，但是我們看前面的5個字節的recordheader，04 00 28 00 22，找到4～8位表示n_owned值的部分，該值為4，表示該記錄有4個記錄，因此還需要進一步查找。通過recorder和ader最后2個字節的偏移量0x0022，找到下一條記錄的位置0xc107，這才是我們要找的主鍵為5的記錄。 ***** ## Named File Formats 機制 隨著InnoDB存儲引擎的發展，新的頁數據結構有時用來支持新的功能特性。比如前面提到的InnoDB Plugin，提供了新的頁數據結構來支持表壓縮功能，完全溢出的（Off page）大變長字符類型字段的存儲。這些新的頁數據結構和之前版本的頁并不兼容。因此從InnoDB Plugin版本開始，InnoDB存儲引擎通過Named File Formats機制來解決不同版本下頁結構兼容性的問題。 InnoDB Plugin將1.0.x之前版本的文件格式（file format）定義為Antelope，將這個版本支持的文件格式定義為Barracuda。新的文件格式總是包含于之前的版本的頁格式。下圖顯示了Barracuda文件格式和Antelope文件格式之間的關系，Antelope文件格式有Compact和Redudant的行格式，Barracuda文件格式即包括了Antelope所有的文件格式，另外新加入了前面我們已經提到過的Compressed何Dynamic行格式。  在InnoDB Plugin的官方手冊中提到，未來版本的InnoDB存儲引擎還將引入的新的文件格式，文件格式的名稱取自動物的名字（這個學Apple？），并按照字母排序進行命名。翻閱源代碼，發現目前已經定義好的文件格式有： ~~~ /**List of animal names representing file format.*/ static const char*file_format_name_map[]={ "Antelope"， "Barracuda"， "Cheetah"， "Dragon"， "Elk"， "Fox"， "Gazelle"， "Hornet"， "Impala"， "Jaguar"， "Kangaroo"， "Leopard"， "Moose"， "Nautilus"， "Ocelot"， "Porpoise"， "Quail"， "Rabbit"， "Shark"， "Tiger"， "Urchin"， "Viper"， "Whale"， "Xenops"， "Yak"， "Zebra" }； ~~~ 參數innodb_file_format用來指定文件格式，可以通過下面的方式查看當前所使用的InnoDB存儲引擎的文件格式： `show variables like 'version'\G` `show variables like 'innodb_version'\G` `show variables like 'innodb_file_format'\G` `show variables like 'innodb_file_format_check'\G` 參數innodb_file_format_check用來檢測當前InnoDB存儲引擎文件格式的支持度，該值默認為ON，如果出現不支持的文件格式，你可能在錯誤日志文件中看到類似如下的錯誤： ~~~ InnoDB：Warning：the system tablespace is in a file format that this version doesn't support ~~~ ***** ## 約束 ### 數據完整性 關系型數據庫系統和文件系統的一個不同點是，關系數據庫本身能保證存儲數據的完整性，不需要應用程序的控制，而文件系統一般需要在程序端進行控制。幾乎所有的關系型數據庫都提供了約束（constraint）機制，約束提供了一條強大而簡易的途徑來保證數據庫中的數據完整性，數據完整性有三種形式： 1.實體完整性　保證表中有一個主鍵。在InnoDB存儲引擎表中，我們可以通過定義Primary Key或者Unique Key約束來保證實體的完整性。或者我們還可以通過編寫一個觸發器來保證數據完整性。 2.域完整性　保證數據的值滿足特定的條件。在InnoDB存儲引擎表中，域完整性可以通過以下幾種途徑來保證：選擇合適的數據類型可以確保一個數據值滿足特定條件，外鍵（Foreign Key）約束，編寫觸發器，還可以考慮用DEFAULT約束作為強制域完整性的一個方面。 3.參照完整性　保證兩張表之間的關系。InnoDB存儲引擎支持外鍵，因此允許用戶定義外鍵以強制參照完整性，也可以通過編寫觸發器以強制執行。 對于InnoDB存儲引擎而言，提供了五種約束：Primary Key，Unique Key，Foreign Key，Default，NOT NULL ### 約束的創建和查找 約束的創建可以采用以下兩個方式： （1）表建立時就進行約束定義 （2）利用ALTER TABLE 命令來進行創建約束 對于Unique Key的約束，我們還可以通過Create Unique Index來進行建立。對于主鍵約束而言，其默認約束名為PRIMARY KEY。而對于Unique Key約束而言，默認約束名和列名一樣，當然可以人為的指定一個名字。對于Foreign Key約束，似乎會有一個比較神秘的默認名稱。下面是一個簡單的創建表的語句，表上有一個主鍵和一個唯一鍵： `create table u (id int,name varchar(20),id_card char(18),primary key(id),unique key(name));` `select constraint_name,constraint_type from information_schema.TABLE_CONSTRAINTS where table_schema='mytest' and table_name='u'\G;` ~~~ ***************************1.row*************************** constraint_name：PRIMARY constraint_type：PRIMARY KEY ***************************2.row*************************** constraint_name：name constraint_type：FOREIGN KEY ~~~ 可以看到約束名就如之前所說的，主鍵的約束名為PRIMARY，唯一索引的默認約束名與列名各相同。當然用戶還可以通過 ALTER TABLE 來創建約束，并且可以定義用戶所希望的約束名，如下: `ALTER TABLE u ADD UNIQUE KET uk_id_card (id_card);` 再通過:`select constraint_name,constraint_type from information_schema.TABLE_CONSTRAINTS where table_schema='mytest' and table_name='u'\G;` 查詢信息  Foreign Key約束： 創建表p： `CREATE TABLE p id INT,u_id INT, PRIMARY KEY (id),FOREIGN KEY (u_id) REFERENCES p(id);` `select constraint_name,constraint_type from information_schema.TABLE_CONSTRAINTS where table_schema='mytest' and table_name='p'\G;` ~~~ ***************************1.row*************************** constraint_name：PRIMARY constraint_type：PRIMARY KEY ***************************2.row*************************** constraint_name：p_ibfk_1 constraint_type：FOREIGN KEY ~~~ 這里我們通過information_schema架構下的表TABLE_CONSTRAINTS來查看當前MySQL庫下所有的約束。對于Foreign Key的約束的定義，我們還可以通過查看表REFERENTIAL_CONSTRAINTS，并且可以詳細地了解外鍵的屬性，如： `select * from information_schema.REFERENTIAL_CONSTRAINTS where constraint_schema='mytest'\G;` ~~~ ***************************1.row*************************** CONSTRAINT_CATALOG：NULL CONSTRAINT_SCHEMA：test2 CONSTRAINT_NAME：p_ibfk_1 UNIQUE_CONSTRAINT_CATALOG：NULL UNIQUE_CONSTRAINT_SCHEMA：test2 UNIQUE_CONSTRAINT_NAME：PRIMARY MATCH_OPTION：NONE UPDATE_RULE：RESTRICT DELETE_RULE：RESTRICT TABLE_NAME：p REFERENCED_TABLE_NAME：p ~~~ ### 約束與索引的區別 約束是一個邏輯的概念,用來保證數據的完整性,而索引是一個數據結構,既有邏輯上的概念,在數據庫還代表著物理存儲的方式。 ### 對錯誤數據的約束 默認情況下，MySQL數據庫允許非法或者不正確數據的插入或更新，或者內部將其轉化為一個合法的值，如對于NOT NULL的字段插入一個NULL值，會將其更改為0再進行插入，因此本身沒有對數據的正確性進行約束。 ### ENUM和 SET約束 MySQL數據庫不支持傳統的CHECK約束,但是通過ENUM 和SET 類型可以解決部分的約束請求。 `create table a (id int not null,date date not null);` `insert into a select NULL,'2009-02-30';` `show warnings;` ~~~ ***************************1.row*************************** Level：Warning Code：1048 Message：Column'id'cannot be null ***************************2.row*************************** Level：Warning Code：1265 Message：Data truncated for column'date' at row 1 select * from a; +----+-------------+ |id|date |0|0000-00-00 +----+-------------+ ~~~ 對于NOT NULL的列我插入了一個NULL值，同時插入了一個非法日期'2009-02-30'，MySQL都沒有報錯，而是顯示了警告（warning）。__如果我們想約束對于非法數據的插入或更新，MySQL是提示報錯而不是警告，那么我們應該設置參數sql_mode__，用來嚴格審核輸入的參數，如： `set sql_mode='strict_trans_tables'; ` ~~~ insert into a select NULL,'2009-02-30'; ERROR 1048（23000）：Column'id'cannot be null insert into a select 1,'2009-02-30'; ERROR 1292（22007）：Incorrect date value：'2009-02-30'for column'date'at row 1 ~~~ 這次對非法的輸入值進行了約束，但是只限于對離散數值的約束，對于傳統CHECK約束支持的連續值的范圍約束或更復雜的約束，ENUM和SET類型還是無能為力，這時用戶需要通過觸發器實現對于值域的約束。 ### 觸發器與約束 完整性約束通常也可以使用觸發器來實現，觸發器的作用是在INSERT、DELETE和UPDATE命令之前或之后自動調用SQL命令或者存儲過程。MySQL 5.0對于觸發器的實現還不是非常完善，限制比較多；而從MySQL 5.1開始，觸發器已經相對穩定，功能也較之前有了大幅的提高。 創建觸發器的命令是CREATE TRIGGER，只有具備Super權限的MySQL用戶才可以執行這條命令： ~~~ CREATE [DEFINER={user|CURRENT_USER}] TRIGGER trigger_name BEFORE|AFTER INSERT|UPDATE|DELETE ON tbl_name FOR EACH ROW trigger_stmt ~~~ 最多可以為一個表建立5個觸發器，即分別為INSERT、UPDATE、DELETE的BEFORE和AFTER各定義一個。BEFORE和AFTER代表觸發器發生的時間，表示是在每行操作的之前發生還是之后發生。當前MySQL只支持FOR EACH ROW的觸發方式，即按每行記錄進行觸發，不支持如DB2的FOR EACH STATEMENT的觸發方式。 通過觸發器，我們可以實現MySQL數據庫本身并不支持的一些特性，如對于傳統CHECK約束的支持、物化視圖、高級復制、審計等特性。這里我們先關注觸發器對于約束的支持。 我們考慮用戶消費表，每次用戶購買一樣物品后其金額都是減的，若這時有不懷好意的人做了類似減去一個負值的操作，這樣的話用戶的錢沒減少反而會不斷地增加。 ~~~ create table usercash(userid int,cash int unsigned not null); insert into usercash select 1,1000; update usercash set cash=cash-(-20) where userid=1; ~~~ 對于數據庫來說，上述的內容沒有任何問題，都可以正常運行，不會報錯。但是從業務的邏輯上來說，這是錯誤的，消費總是應該減去一個正值，而不是負值。因此這時如果通過觸發器來約束這個邏輯行為的話，可以如下操作： ~~~ create table usercash_err_log( 　　userid int not null, 　　old_cash int unsigned not null, 　　new_cash int unsigned not null, 　　user varchar(30), 　　time datetime); delimiter$$ create trigger tgr_usercash_update before update on usercash 　　for each row 　　begin 　　　　if new.cash-old.cash＞0 then 　　　　　　insert into usercash_err_log select old.userid，old.cash，new.cash，user（），now（）； 　　　　　　set new.cash=old.cash； 　　　　end if； 　　end; $$ delete from usercash; insert into usercash select 1,1000; update usercash set cash=cash-(-20) where userid=1; select * from usercash; +--------+-------+ |userid|cash |1|1000 +--------+-------+ select * from usercash_err_log; +--------+------------+------------+------------------- |userid|old_cash|new_cash|user|time |1|1000|1020|root@localhost|2009-11-06 11：49：49 +--------+------------+------------+------------------ ~~~ 我們創建了一張表用來記錄錯誤數值更新的日志，首先判斷新舊值之間的差值，正常情況下消費總是減的，因此新值應該總是小于原來的值，因此對于大于原值的數據，我們判斷為非法的輸入，將cash值設定為原來的值。 ### 外鍵約束 外鍵用來保證參照完整性，MySQL默認的MyISAM存儲引擎本身并不支持外鍵，對于外鍵的定義只是起到一個注釋的作用。InnoDB存儲引擎則完整支持外鍵約束。外鍵的定義如下： ~~~ [CONSTRAINT[symbol]] FOREIGN KEY [index_name](index_col_name,……) REFERENCES tbl_name (index_col_name,……) [ON DELETE reference_option] [ON UPDATE reference_option] reference_option: RESTRICT|CASCADE|SET NULL|NO ACTION ~~~ 我們可以在CREATE TABLE時就添加外鍵，也可以在表創建后通過ALTER TABLE命令來添加。 一個簡單的外鍵的創建示例如下： ~~~ CREATE TABLE parent( 　　id INT NOT NULL, 　　PRIMARY KEY(id) )ENGINE=INNODB； CREATE TABLE child( 　　id INT, 　　parent_id INT, 　　index par_ind(parent_id), 　　FOREIGN KEY(parent_id) REFERENCES parent(id) )ENGINE=INNODB； ~~~ 一般來說，我們稱被引用的表為父表，另一個引用的表為子表。外鍵定義為，ON DELETE和ON UPDATE表示父表做DELETE和UPDATE操作時子表所做的操作。可定義的子表操作有： （1）CASCADE：當父表發生DELETE或UPDATE操作時，相應的子表中的數據也被DELETE或UPDATE。 （2）SET NULL：當父表發生DELETE或UPDATE操作時，相應的子表中的數據被更新為NULL值。當然，子表中相對應的列必須允許NULL值。 （3）NO ACTION：當父表發生DELETE或UPDATE操作時，拋出錯誤，不允許這類操作發生。 （4）RESTRICT：當父表發生DELETE或UPDATE操作時，拋出錯誤，不允許這類操作發生。如果定義外鍵時沒有指定ON （5）DELETE或ON UPDATE，這就是默認的外鍵設置。在Oracle中，有一種稱為延時檢查（deferred check）的外鍵約束，而目前MySQL的約束都是即時檢查（immediate check）的，因此從上面的定義可以看出，在MySQL數據庫中NO ACTION和RESTRICT的功能是相同的。 在Oracle數據庫中，外鍵通常被人忽視的地方是，對于建立外鍵的列，一定不要忘記給這個列加上一個索引。而InnoDB存儲引擎在外鍵建立時會自動地對該列加一個索引，這和Microsoft SQL Server數據庫的做法一樣。因此可以很好地避免外鍵列上無索引而導致的死鎖問題的產生。 對于參照完整性約束，外鍵能起到一個非常好的作用。但是對于數據的導入操作，外鍵往往導致大量時間花費在外鍵約束的檢查上，因為MySQL的外鍵是即時檢查的，因此導入的每一行都會進行外鍵檢查。但是我們可以在導入過程中忽視外鍵的檢查，如： ~~~ SET foreign_key_checks=0; LOAD DATA…… SET foreign_key_checks=1; ~~~ ***** ## 視圖 視圖（View）是一個命名的虛表，它由一個查詢來定義，可以當做表使用。與持久表（permanent table）不同的是，視圖中的數據沒有物理表現形式。 ### 視圖的作用 視圖在數據庫中發揮著重要的作用。視圖的主要用途之一是被用做一個抽象裝置，特別是對于一些應用程序，程序本身不需要關心基表（base table）的結構，只需要按照視圖定義來獲取數據或者更新數據，因此，視圖同時在一定程度上起到一個安全層的作用。 MySQL從5.0版本開始支持視圖，創建視圖的語法如下： ~~~ 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] ~~~ 雖然視圖是基于基表的一個虛擬表，但是我們可以對某些視圖進行更新操作，其實就是通過視圖的定義來更新基本表，我們稱可以進行更新操作的視圖為可更新視圖（updatable view）。視圖定義中的WITH CHECK OPTION就是指對于可更新的視圖，更新的值是否需要檢查。 我們先看個例子： `create table t(id int);` `create view v_t as select * from t where t＜10;` ERROR 1054（42S22）：Unknown column't'in'where clause' create view v_t as select * from t where id＜10; insert into v_t select 20; select * from v_t； 我們創建了一個id＜10的視圖，但是往里插入了id為20的值，插入操作并沒有報錯，但是我們查詢視圖還是沒有能查到數據。 接著我們更改一下視圖的定義，加上WITH CHECK OPTION： `alter view v_t as select * from t where id＜10 with check option;` insert into v_t select 20; ERROR 1369（HY000）：CHECK OPTION failed'mytest.v_t' 這次MySQL數據庫會對更新視圖插入的數據進行檢查，對于不滿足視圖定義條件的，將會拋出一個異常，不允許數據的更新。 MysQL DBA一個常用的命令是show tables，會顯示出當前數據庫下的表，視圖是虛表，同樣被作為表而顯示出來， 我們來看前面的例子：`show tables;` show tables命令把表t和視圖v_t都顯示出來了。如果我們只想查看當前數據庫下的基表，可以通過information_schema架構下的TABLE表來查詢，并搜索表類型為BASE TABLE的表，如： select * from information_schema.TABLES where table_type='BASE TABLE' and table_schema=database(); 要想查看視圖的一些元數據（meta data），可以訪問information_schema架構下的VIEWS表，該表給出了視圖的詳細信息，包括視圖定義者（definer）、定義內容、是否是可更新視圖、字符集等。如我們查詢VIEWS表，可得： `select * from information_schema.VIEWS where table_schema=database();` ### 物化視圖. Oracle數據庫支持物化視圖--改視圖不是基于基表的虛表,而是根據基表實際存在的實表，即物化視圖的數據存儲在非易失的存儲設備上。物化視圖的好處是對于一些復雜的統計類查詢能夠直接查出結果。該視圖也稱為索引視圖。 在Oracle數據庫創建方式： （1）BUILD IMMEDIATE 是默認的創建方式，在創建物化視圖的時候就生成數據。 （2）而BUILD DEFERRED 則在創建物化視圖時不生成數據，以后根據需要再生成數據。 查詢重寫是指當對物化視圖的基表進行查詢時，數據庫會自動判斷能否通過查詢物化視圖來直接得到最終結果，如果可以，則避免聚集或者鏈接等這類較為復雜的SQL操作，直接從已經計算好的物化視圖中得到所需的數據。 物化視圖刷新是值當基表發生了DML操作后,物化視圖何時采用哪種方式和基表進行同步。刷新模式有兩種： （1）ON DEMAND 意味著物化視圖在用戶需要的時候進行刷新， （2） ON COMMIT 意味著物化視圖對基表的DML操作提交的同時進行刷新。 刷新方法有四種： （1）FAST 刷新采用增量刷新，只刷新自上次刷新以后進行的修改。 （2）COMPLETE刷新是對整個物化視圖進行完全刷新。 （3）FORCE刷新則數據庫在刷新是會去判斷是否可以進行快速刷新，如果可以采用FAST 否則采用COMPLETE的方式。 （4）NEVER是指物化視圖不進行任何刷新。 MYSQL數據庫本身不支持物化視圖。但是用戶可以通過機制來實現物化視圖功能。通過ON DEMAND的物化視圖例如： 創建表 ~~~ MySQL [qiushibaike]> 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; Query OK, 0 rows affected (1.72 sec) ~~~ 插入數據: ~~~ MySQL [qiushibaike]> insert into Orders VALUES -> (null,'CPU',135.5,1), -> (null,'Memory',48.2,3), -> (null,'CPU',125.6,3), -> (null,'CPU',105.3,4) -> ; ~~~ 查詢數據: ~~~ MySQL [qiushibaike]> select * from Orders\G; *************************** 1. row *************************** order_id: 1 product_name: CPU price: 135.50 amount: 1 *************************** 2. row *************************** order_id: 2 product_name: Memory price: 48.20 amount: 3 *************************** 3. row *************************** order_id: 3 product_name: CPU price: 125.60 amount: 3 *************************** 4. row *************************** order_id: 4 product_name: CPU price: 105.30 amount: 4 4 rows in set (0.01 sec) ERROR: No query specified ~~~ 接著建立一張物化視圖的基表，用來統計每件物品的信息，如： ~~~ MySQL [qiushibaike]> 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_cut int not null, -> unique index(product_name) -> ); MySQL [qiushibaike]> insert into Orders_MV -> select product_name, -> SUM(price),SUM(amount),AVG(price),COUNT(*) -> FROM Orders -> GROUP BY product_name; Query OK, 2 rows affected (0.45 sec) Records: 2 Duplicates: 0 Warnings: 0 MySQL [qiushibaike]> select * from Orders_MV\G; *************************** 1. row *************************** product_name: CPU price_sum: 366.40 amount_sum: 8 price_avg: 122.133 orders_cut: 3 *************************** 2. row *************************** product_name: Memory price_sum: 48.20 amount_sum: 3 price_avg: 48.2 orders_cut: 1 2 rows in set (0.05 sec) ERROR: No query specified ~~~ 通過以上的方式就實現ON DEMAND的物化視 但是 __每次如果數據更新 都要先清空Orders_MV視圖表 在插入數據__ 。當然這是COMPLETE,要實現FAST的方式 需要記住上次統計時order_id的位置。 但是,如果要實現ON COMMIT 的物化視圖，就不像上面這么簡單了。在Oracle數據庫中是通過物化視圖日志實現的，顯然MySQL數據庫沒有這個日志，不過可以通過觸發器同樣達到這個目的。如下： ~~~ 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_sum,0), ifnull(amount_sum,0),ifnull(price_avg,0),ifnull(orders_cut,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 ; ~~~ 上述代碼創建了一個insert觸發器,每次insert操作都會重新統計表Orders_MV中的數據.接著運行一下的查找查詢數據; `insert into Orders values (NULL,'SSD',299,3);` ~~~ MySQL [qiushibaike]> select * from Orders_MV\G *************************** 1. row *************************** product_name: CPU price_sum: 366.40 amount_sum: 8 price_avg: 122.133 orders_cut: 3 *************************** 2. row *************************** product_name: Memory price_sum: 48.20 amount_sum: 3 price_avg: 48.2 orders_cut: 1 *************************** 3. row *************************** product_name: SSD price_sum: 299.00 amount_sum: 3 price_avg: 299 orders_cut: 1 3 rows in set (0.00 sec) ~~~ 還需要update和delect 觸發器 ***** ## 分區表 ### 分區概述 查詢分區是否啟動`show variables like '%partition%'\G;`或者`SHOW PLUGINS\G`  分區主要用于數據庫高可用性的管理。并不是啟動分區，數據庫就會運行得更快。 分區類型 （1）RANGE分區：行數據基于屬于一個給定連續區間的列值被放入分區。MySQL5.5開始支持RANGE COLUMNS的分區. （2）LIST分區：和RANGE分區類型，只是LIST分區面向的是離散的值。MySQL5.5開始支持LIST COLUMNS的分區. （3）HASH分區：根據用戶自定義的表達式的返回值進行分區，返回值不能為負數。 （4）KET分區：根據MySQL數據庫提供的哈希函數來進行分區。 無聊創建何種類型的分區，如果表中存在主鍵或者唯一索引時，分區列必須是唯一索引的一個組成部分。 建表：唯一索引允許為空。分區列只要是唯一索引的一個組成部分，不需要整個唯一索引列都是分區列。 如果沒有主鍵可以指定任意一個列進行分區列。 #### RANGE分區 第一種類型是RANGE分區，也是最常用的一種分區類型。 下面的CREATE TABLE語句創建了一個id列的區間分區表。當id小于10時，數據插入p0分區。當id大于等于10小于20時，插入p1分區： create table t(id int) engine=innodb 　　partition by range(id)( 　　partition p0 values less than(10), 　　partition p1 values less than(20)); 查看表在磁盤上的物理文件，啟用分區之后，表不再由一個ibd文件組成了，而是由建立分區時的各個分區ibd文件組成，如下所示的t#P#p0.ibd，t#P#p1.ibd： system ls -lh /usr/local/mysql/data/test2/t* -rw-rw----1 mysql mysql 8.4K 7月31 14：11/usr/local/mysql/data/test2/t.frm -rw-rw----1 mysql mysql 28 7月31 14：11/usr/local/mysql/data/test2/t.par -rw-rw----1 mysql mysql 96K 7月31 14：12/usr/local/mysql/data/test2/t#P#p0.ibd -rw-rw----1 mysql mysql 96K 7月31 14：12/usr/local/mysql/data/test2/t#P#p1.ibd 接著插入如下數據： insert into t select 9; insert into t select 10; insert into t select 15; 因為表t根據列id進行分區，因此數據是根據id列的值的范圍存放在不同的物理文件中的. 可以通過查詢information_schema架構下的PARTITIONS表來查看每個分區的具體信息： select * from information_schema.PARTITIONS where table_schema=database() and table_name='t'\G; TABLE_ROWS列反映了每個分區中記錄的數量。由于之前向表中插入了9、10、15三條記錄，因此可以看到，當前分區p0中有1條記錄、分區p1中有2條記錄。PARTITION_METHOD表示分區的類型，這里顯示的是RANGE。 對于表t，因為我們定義了分區，因此對于插入的值應該嚴格遵守分區的定義，當插入一個不在分區中定義的值時，MySQL數據庫會拋出一個異常。 如下所示，我們向表t中插入30這個值： insert into t select 30; ERROR 1526（HY000）：Table has no partition for value 30 對于上述問題，我們可以對分區添加一個MAXVALUE值的分區。MAXVALUE可以理解為正無窮，因此所有大于等于20并且小于MAXVALUE的值放入p2分區： alter table t add partition(partition p2 values less than maxvalue); insert into t select 30; RANGE分區主要用于日期列的分區，如對于銷售類的表，可以根據年來分區存放銷售記錄，如以下所示的分區表sales： create table sales( 　　money int unsigned not null, 　　date datetime )engine=innodb partition by range(YEAR(date))( partition p2008 values less than (2009), partition p2009 values less than (2010), partition p2010 values less than (2011) ); insert into sales select 100,'2008-01-01'; insert into sales select 100,'2008-02-01'; insert into sales select 200,'2008-01-02'; insert into sales select 100,'2009-03-01'; insert into sales select 200,'2010-03-01'; 這樣創建的好處是，便于對sales這張表的管理。如果我們要刪除2008年的數據，就不需要執行DELETE FROM sales WHERE date＞='2008-01-01'and date＜'2009-01-01'，而只需刪除2008年數據所在的分區即可： alter table sales drop partition p2008; 這樣創建的另一個好處是，可以加快某些查詢的操作。如果我們只需要查詢2008年整年的銷售額： explain partitions select * from sales where date＞='2008-01-01' and date＜='2008-12-31'\G; 通過EXPLAIN PARTITION命令我們可以發現，在上述語句中，SQL優化器只需要去搜索p2008這個分區，而不會去搜索所有的分區，因此大大提高了執行的速度。 需要注意的是，如果執行下列語句，結果是一樣的，但是優化器的選擇又會不同了： explain partitions select * from sales where date＞='2008-01-01' and date＜'2009-01-01'\G; 這次條件改為date＜'2009-01-01'而不是date＜='2008-12-31'時，優化器會選擇搜索p2008和p2009兩個分區，這是我們不希望看到的。因此對于啟用分區，你應該根據分區的特性來編寫最優的SQL語句。 對于sales這張分區表，我曾看到過另一種分區函數，設計者的原意是想可以按照每年每月來進行分區，如： ~~~~ create table sales( 　　money int unsigned not null, 　　date datetime )engine=innodb partition by range(YEAR(date)*100+MONTH(date))( partition p201001 values less than (201002), partition p201002 values less than (201003), partition p201003 values less than (201004) ); ~~~~ 但是在執行SQL語句時開發人員會發現，優化器不會根據分區進行選擇，即使他們編寫的SQL語句已經符合了分區的要求，如： `explain partitions select * from sales where date＞='2010-01-01' and date＜='2010-01-31'\G；` ~~~~ ***************************1.row*************************** id：1 select_type：SIMPLE table：sales partitions：p201001，p201002，p201003 type：ALL possible_keys：NULL key：NULL key_len：NULL ref：NULL rows：4 Extra：Using where row in set（0.00 sec） ~~~~ 可以看到優化對分區p201001、p201002、p201003都進行了搜索。產生這個問題的主要原因是，對于RANGE分區的查詢，優化器只能對YEAR()、TO_DAYS()、TO_SECONDS()、UNIX_TIMESTAMP()這類函數進行優化選擇，因此對于上述的要求，需要將分區函數改為TO_DAYS，如： ~~~~ create table sales( 　　money int unsigned not null, 　　date datetime )engine=innodb partition by range(to_days(date))( partition p201001 values less than(to_days('2010-02-01')), partition p201002 values less than(to_days('2010-03-01')), partition p201003 values less than(to_days('2010-04-01')) ); ~~~~ 這時再進行相同類型的查詢，優化器就可以對特定的分區進行查詢了： `explain partitions select * from sales where date＞='2010-01-01' and date＜='2010-01-31'\G;` ***** #### LIST分區 LIST分區和RANGE分區非常相似，只是分區列的值是離散的，而非連續的。如： ~~~~ create table t( 　　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) ); ~~~~ 不同于RANGE分區中定義的VALUES LESS THAN語句，LIST分區使用VALUES IN，所以每個分區的值是離散的，只能是定義的值。如我們往表中插入一些數據： ~~~~ insert into t select 1,1; insert into t select 1,2; insert into t select 1,3; insert into t select 1,4; select table_name,partition_name,table_rows from information_schema.PARTITIONS where table_name='t' and table_schema=database(); ~~~~ 如果插入的值不在分區的定義中，MySQL數據庫同樣會拋出異常： `insert into t select 1,10;` ERROR 1526（HY000）：Table has no partition for value 10 在用INSERT插入多個行數據的過程中遇到分區未定義的值時，MyISAM和InnoDB存儲引擎的處理完全不同。MyISAM引擎會將之前的行數據都插入，但之后的數據不會被插入。而InnoDB存儲引擎將其視為一個事務，因此沒有任何數據插入。 先對MyISAM存儲引擎進行演示，如： ~~~~ create table t( 　　a int, 　　b int )engine=myisam partition by list(b)( partition p0 values in (1,3,5,7,9), partition p1 values in (0,2,4,6,8) ); insert into t values(1,2),(2,4),(6,10),(5,3); ERROR 1526（HY000）：Table has no partition for value 10 select * from t; ~~~~ 可以看到對于插入的（6，10）記錄沒有成功，但是之前的（1，2），（2，4）記錄都已經插入成功了。 而同一張表，存儲引擎換成InnoDB，則結果完全不同： ~~~~ truncate table t; alter table t engine=innodb; insert into t values(1,2),(2,4),(6,10),(5,3); ERROR 1526（HY000）：Table has no partition for value 10 select * from t; Empty set（0.00 sec） ~~~~ 可以看到同樣在插入(6,10)記錄是報錯，但是沒有任何一條記錄被插入表t中。因此在使用分區時，也需要對不同存儲引擎支持的事務特性進行考慮。 #### HASH分區 HASH分區的目的是將數據均勻地分布到預先定義的各個分區中，保證各分區的數據數量大致都是一樣的。在RANGE和LIST分區中，必須明確指定一個給定的列值或列值集合應該保存在哪個分區中；而在HASH分區中，MySQL自動完成這些工作，你所要做的只是基于將要被哈希的列值指定一個列值或表達式，以及指定被分區的表將要被分割成的分區數量。 要使用HASH分區來分割一個表，要在CREATE TABLE語句上添加一個“PARTITION BY HASH(expr)”子句，其中“expr”是一個返回一個整數的表達式。它可以僅僅是字段類型為MySQL整型的一列的名字。此外，你很可能需要在后面再添加一個“PARTITIONS num”子句，其中num是一個非負的整數，它表示表將要被分割成分區的數量。如果沒有包括一個PARTITIONS子句，那么分區的數量將默認為1。 下面的例子創建了一個HASH分區的表t，按日期列b進行分區： ~~~~ create table t_hash( 　　a int, 　　b datetime )engine=innodb partition by hash(YEAR(b)) partitions 4; ~~~~ 如果將一個列b為2010-04-01這個記錄插入表t_hash中，那么保存該條記錄的分區確定如下。 ~~~~ MOD(YEAR('2010-04-01'),4) =MOD(2010,4) =2 ~~~~ 因此會放入分區2中，我們可以按如下方法來驗證： `insert into t_hash select 1,'2010-04-01';` `select table_name,partition_name,table_rows from information_schema. PARTITIONS where table_schema=database() and table_name='t_hash';` 可以看到p2分區有1條記錄。當然這個例子中并不能把數據均勻地分布到各個分區中，因為分區是按照YEAR函數，因此這個值本身可以視為是離散的。如果對于連續的值進行HASH分區，如自增長的主鍵，則可以很好地將數據進行平均分布。 MySQL數據庫還支持一種稱為LINEAR HASH的分區，它使用一個更加復雜的算法來確定新行插入已經分區的表中的位置。它的語法和HASH分區的語法相似，只是將關鍵字HASH改為LINEAR HASH。下面創建一個LINEAR HASH的分區表t_linear_hash，它和之前的表t_hash相似，只是分區類型不同： ~~~~ create table t_linear_hash( 　　a int, 　　b datetime )engine=innodb partition by linear hash(year(b)) partition by 4; ~~~~ 同樣插入‘2010-04-01’的記錄，這次MySQL數據庫根據以下的方法來進行分區的判斷： （1）取大于分區數量4的下一個2的冪值V，V=POWER(2,CEILING(LOG(2,num)))=4; （2）所在分區N=YEAR('2010-04-01')&(V-1)=2。 雖然還是在分區2，但是計算的方法和之前的HASH分區完全不同。接著進行插入實際數據的驗證： `insert into t_linear_hash select 1,'2010-04-01';` `select table_name,partition_name,table_rows from information_schema.PARTITIONS where table_schema=database() and table_name='t_linear_hash';` LINEAR HASH分區的優點在于，增加、刪除、合并和拆分分區將變得更加快捷，這有利于處理含有大量數據的表；它的缺點在于，與使用HASH分區得到的數據分布相比，各個分區間數據的分布可能不大均衡。 #### KEY分區 KEY分區和HASH分區相似；不同在于，HASH分區使用用戶定義的函數進行分區，KEY分區使用MySQL數據庫提供的函數進行分區。NDB Cluster引擎使用MD5函數來分區，對于其他存儲引擎，MySQL數據庫使用其內部的哈希函數，這些函數是基于與PASSWORD()一樣的運算法則。如： ~~~~ create table t_key( 　　a int, 　　b datetime )engine=innodb partition by key(b) partitions 4; ~~~~ 在KEY分區中使用關鍵字LINEAR，和在HASH分區中具有同樣的作用，分區的編號是通過2的冪（powers-of-two）算法得到的，而不是通過模數算法。 #### COLUMNS分區 RANGE、LIST、HASH和KEY這四種分區中，分區的條件必須是整型（interger），如果不是整型，那應該需要通過函數將其轉化為整型，如YEAR()、TO_DAYS()、MONTH()等函數。MySQL數據庫5.5版本開始支持COLUMNS分區，可視為RANGE分區和LIST分區的一種進化。COLUMNS分區可以直接使用非整型的數據進行分區，分區根據類型直接比較而得，不需要轉化為整型。其次，RANGE COLUMNS分區可以對多個列的值進行分區。 COLUMNS分區支持以下的數據類型： 所有的整型類型，如INT、SMALLINT、TINYINT、BIGINT。FLOAT和DECIMAL則不予支持。 日期類型，如DATE和DATETIME。其余的日期類型不予支持。 字符串類型，如CHAR、VARCHAR、BINARY和VARBINARY。BLOB和TEXT類型不予支持。 對于日期類型的分區，我們不再需要YEAR()和TO_DAYS()函數了，而直接可以使用COLUMNS，如： ~~~~ create table t_columns_range( 　　a int, 　　b datetime )engine=innodb PARTITION BY RANGE COLUMNS(b)( partition p0 values less than('2009-01-01'), partition p1 values less than('2010-01-01') ); ~~~~ 同樣，可以直接使用字符串的分區： ~~~~ CREATE TABLE customers_1( 　　first_name VARCHAR(25), 　　last_name VARCHAR(25), 　　street_1 VARCHAR(30), 　　street_2 VARCHAR(30), 　　city VARCHAR(15), 　　renewal DATE ) PARTITION BY LIST COLUMNS(city)( PARTITION pRegion_1 VALUES IN ('Oskarshamn','H?gsby','M?nster?as'), PARTITION pRegion_2 VALUES IN ('Vimmerby','Hultsfred','V?stervik'), PARTITION pRegion_3 VALUES IN ('N?ssj?','Eksj?','Vetlanda'), PARTITION pRegion_4 VALUES IN ('Uppvidinge','Alvesta','V?xjo') ); 對于RANGE COLUMNS分區，可以使用多個列進行分區，如： CREATE TABLE rcx( 　　a INT, 　　b INT, 　　c CHAR(3), 　　d INT ) PARTITION BY RANGE COLUMNS(a,d,c)( PARTITION p0 VALUES LESS THAN(5,10,'ggg'), PARTITION p1 VALUES LESS THAN(10,20,'mmmm'), PARTITION p2 VALUES LESS THAN(15,30,'sss'), PARTITION p3 VALUES LESS THAN(MAXVALUE,MAXVALUE,MAXVALUE) ); ~~~~ MySQL數據庫版本5.5.0開始支持COLUMNS分區，對于之前的RANGE和LIST分區，我們應該可以用RANGE COLUMNS和LIST COLUMNS分區進行很好的代替。 ### 子分區 子分區（subpartitioning）是在分區的基礎上再進行分區，有時也稱這種分區為復合分區(composite partitioning)。MySQL數據庫允許在RANGE和LIST的分區上再進行HASH或者是KEY的子分區，如： ~~~~ CREATE TABLE ts(a INT,b DATE)engine=innodb PARTITION BY RANGE(YEAR(b)) SUBPARTITION BY HASH(TO_DAYS(b)) SUBPARTITIONS 2( PARTITION p0 VALUES LESS THAN (1990), PARTITION p1 VALUES LESS THAN (2000), PARTITION p2 VALUES LESS THAN MAXVALUE ); system ls -lh /usr/local/mysql/data/test2/ts* -rw-rw----1 mysql mysql 8.4K Aug 1 15：50/usr/local/mysql/data/test2/ts.frm -rw-rw----1 mysql mysql 96 Aug 1 15：50/usr/local/mysql/data/test2/ts.par -rw-rw----1 mysql mysql 96K Aug 1 15：50/usr/local/mysql/data/test2/ts#P#p0#SP#p0sp0.ibd -rw-rw----1 mysql mysql 96K Aug 1 15：50/usr/local/mysql/data/test2/ts#P#p0#SP#p0sp1.ibd -rw-rw----1 mysql mysql 96K Aug 1 15：50/usr/local/mysql/data/test2/ts#P#p1#SP#p1sp0.ibd -rw-rw----1 mysql mysql 96K Aug 1 15：50/usr/local/mysql/data/test2/ts#P#p1#SP#p1sp1.ibd -rw-rw----1 mysql mysql 96K Aug 1 15：50/usr/local/mysql/data/test2/ts#P#p2#SP#p2sp0.ibd -rw-rw----1 mysql mysql 96K Aug 1 15：50/usr/local/mysql/data/test2/ts#P#p2#SP#p2sp1.ibd ~~~~ 表ts先根據b列進行了RANGE分區，然后又再進行了一次HASH分區，所以分區的數量應該為（3×2=）6個，這通過查看物理磁盤上的文件也可以得到證實。 我們也可以通過使用SUBPARTITION語法來顯式指出各個子分區的名稱，同樣對上述的ts表： ~~~~ CREATE TABLE ts(a INT,b DATE) PARTITION BY RANGE(YEAR(b)) SUBPARTITION BY HASH(TO_DAYS(b))( PARTITION p0 VALUES LESS THAN(1990)( SUBPARTITION s0, SUBPARTITION s1 ), PARTITION p1 VALUES LESS THAN(2000)( SUBPARTITION s2, SUBPARTITION s3 ), PARTITION p2 VALUES LESS THAN MAXVALUE( SUBPARTITION s4, SUBPARTITION s5 ) ); ~~~~ 子分區的建立需要注意以下幾個問題： 每個子分區的數量必須相同。 如果在一個分區表上的任何分區上使用SUBPARTITION來明確定義任何子分區，那么就必須定義所有的子分區。 因此下面的創建語句是錯誤的。 ~~~~ CREATE TABLE ts(a INT,b DATE) PARTITION BY RANGE(YEAR(b)) SUBPARTITION BY HASH(TO_DAYS(b))( PARTITION p0 VALUES LESS THAN(1990)( SUBPARTITION s0, SUBPARTITION s1 ), PARTITION p1 VALUES LESS THAN(2000), PARTITION p2 VALUES LESS THAN MAXVALUE( SUBPARTITION s2, SUBPARTITION s3 ) ); ERROR 1064（42000）：Wrong number of subpartitions defined，mismatch with previous setting near' PARTITION p2 VALUES LESS THAN MAXVALUE( SUBPARTITION s2, SUBPARTITION s3 ) ~~~~ 　　3.每個SUBPARTITION子句必須包括子分區的一個名稱。 　　4.在每個分區內，子分區的名稱必須是唯一的。 因此下面的創建語句是錯誤的。 ~~~~ CREATE TABLE ts(a INT,b DATE) 　　PARTITION BY RANGE(YEAR(b)) 　　SUBPARTITION BY HASH(TO_DAYS(b))( 　　PARTITION p0 VALUES LESS THAN(1990)( 　　SUBPARTITION s0, 　　SUBPARTITION s1 ), PARTITION p1 VALUES LESS THAN(2000)( SUBPARTITION s0, SUBPARTITION s1 ), PARTITION p2 VALUES LESS THAN MAXVALUE( SUBPARTITION s0, SUBPARTITION s1 ) ); ERROR 1517（HY000）：Duplicate partition name s0 ~~~~ 子分區可以用于特別大的表，在多個磁盤間分別分配數據和索引。假設有6個磁盤，分別為/disk0、/disk1、/disk2等。現在考慮下面的例子： ~~~~ CREATE TABLE ts(a INT,b DATE) ENGINE=MYISAM PARTITION BY RANGE(YEAR(b)) SUBPARTITION BY HASH(TO_DAYS(b))( PARTITION p0 VALUES LESS THAN(2000)( SUBPARTITION s0 DATA DIRECTORY='/disk0/data' INDEX DIRECTORY='/disk0/idx', SUBPARTITION s1 DATA DIRECTORY='/disk1/data' INDEX DIRECTORY='/disk1/idx' ), PARTITION p1 VALUES LESS THAN(2010)( SUBPARTITION s2 DATA DIRECTORY='/disk2/data' INDEX DIRECTORY='/disk2/idx', SUBPARTITION s3 DATA DIRECTORY='/disk3/data' INDEX DIRECTORY='/disk3/idx' ), PARTITION p2 VALUES LESS THAN MAXVALUE( SUBPARTITION s4 DATA DIRECTORY='/disk4/data' INDEX DIRECTORY='/disk4/idx', SUBPARTITION s5 DATA DIRECTORY='/disk5/data' INDEX DIRECTORY='/disk5/idx' ) ); ~~~~ 但是InnoDB存儲引擎會忽略DATA DIRECTORY和INDEX DIRECTORY語法，因此上述分區表的數據和索引文件分開放置對其是無效的：   ### 分區中的NULL值 MySQL數據庫允許對NULL值做分區，但是處理的方法和Oracle數據庫完全不同。MYSQL數據庫的分區總是把NULL值視為小于任何一個非NULL值，這和MySQL數據庫中對于NULL的ORDER BY的排序是一樣的。因此對于不同的分區類型，MySQL數據庫對于NULL值的處理是不一樣的。 對于RANGE分區，如果對于分區列插入了NULL值，則MySQL數據庫會將該值放入最左邊的分區（這和Oracle數據庫完全不同，Oracle數據庫會將NULL值放入MAXVALUE分區中）。例如： ~~~~ create table t_range( 　　a int, 　　b int )engine=innodb partition by range(b)( partition p0 values less than(10), partition p1 values less than(20), partition p2 values less than maxvalue ); ~~~~ 接著往表中插入（1，1）和（1，NULL）兩條數據，并觀察每個分區中記錄的數量： `insert into t_range select 1,1;` `insert into t_range select 1,NULL;` `select * from t_range\G;` `select table_name,partition_name,table_rows from information_schema.PARTITIONS where table_schema=database() and table_name='t_range'\G;` 可以看到兩條數據都放入了p0分區，也就是說明了RANGE分區下，NULL值會放入最左邊的分區中。另外需要注意的是，如果刪除p0這個分區，你刪除的是小于10的記錄，并且還有NULL值的記錄，這點非常重要。 LIST分區下要使用NULL值，則必須顯式地指出哪個分區中放入NULL值，否則會報錯，如： ~~~~ create table t_list( 　　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) ); insert into t_list select 1,NULL; ERROR 1526（HY000）：Table has no partition for value NULL ~~~~ 若p0分區允許NULL值，則插入不會報錯： ~~~~ create table t_list( 　　a int, 　　b int)engine=innodb partition by list(b)( partition p0 values in (1,3,5,7,9,NULL), partition p1 values in (0,2,4,6,8) ); insert into t_list select 1,NULL; select table_name,partition_name,table_rows from information_schema.PARTITIONS where table_schema=database() and table_name='t_list'; ~~~~ HASH和KEY分區對于NULL的處理方式，和RANGE分區、LIST分區不一樣。任何分區函數都會將含有NULL值的記錄返回為0。如： ~~~~ create table t_hash( 　　a int, 　　b int)engine=innodb partition by hash(b) partitions 4; ~~~~ `insert into t_hash select 1,0;` `insert into t_hash select 1,NULL;` `select table_name,partition_name,table_rows from information_schema.PARTITIONS where table_schema=database() and table_name='t_hash';` ~~~~ ***************************1.row*************************** table_name：t_hash partition_name：p0 table_rows：2 ~~~~ ### 分區和性能 數據庫的應用分為兩類： 一類是OLTP（在線事務處理），如博客、電子商務、網絡游戲等； 一類是OLAP（在線分析處理），如數據倉庫、數據集市。 在一個實際的應用環境中，可能既有OLTP的應用，也有OLAP的應用。如網絡游戲中，玩家操作的游戲數據庫應用就是OLTP的，但是游戲廠商可能需要對游戲產生的日志進行分析，通過分析得到的結果來更好地服務于游戲、預測玩家的行為等，而這卻是OLAP的應用。 對于OLAP的應用，分區的確可以很好地提高查詢的性能，因為OLAP應用的大多數查詢需要頻繁地掃描一張很大的表。假設有一張1億行的表，其中有一個時間戳屬性列。你的查詢需要從這張表中獲取一年的數據。如果按時間戳進行分區，則只需要掃描相應的分區即可。 對于OLTP的應用，分區應該非常小心。在這種應用下，不可能會獲取一張大表中10%的數據，大部分都是通過索引返回幾條記錄即可。而根據B+樹索引的原理可知，對于一張大表，一般的B+樹需要2～3次的磁盤IO（到現在我都沒看到過4層的B+樹索引）。因此B+樹可以很好地完成操作，不需要分區的幫助，并且設計不好的分區會帶來嚴重的性能問題。 很多開發團隊會認為含有1000萬行的表是一張非常巨大的表，所以他們往往會選擇采用分區，如對主鍵做10個HASH的分區，這樣每個分區就只有100萬行的數據了，因此查詢應該變得更快了，如SELECT * FROM TABLE WHERE PK=@pk。但是有沒有考慮過這樣一個問題：100萬行和1000萬行的數據本身構成的B+樹的層次都是一樣的，可能都是2層？那么上述走主鍵分區的索引并不會帶來性能的提高。是的，即使1000萬行的B+樹的高度是3，100萬行的B+樹的高度是2，那么上述走主鍵分區的索引可以避免1次IO，從而提高查詢的效率。嗯，這沒問題，但是這張表只有主鍵索引，而沒有任何其他的列需要查詢？如果還有類似如下的語句SQL：SELECT * FROM TABLE WHERE KEY=@key，這時對于KEY的查詢需要掃描所有的10個分區，即使每個分區的查詢開銷為2次IO，則一共需要20次IO。而對于原來單表的設計，對于KEY的查詢還是2～3次IO。 如下表Profile，根據主鍵ID進行了HASH分區，HASH分區的數量為10，表Profile有接近1000萬行的數據： ~~~~ CREATE TABLE 'Profile'( 　　'id' int(11) NOT NULL AUTO_INCREMENT, 　　'nickname' varchar(20) NOT NULL DEFAULT'', 　　'password' varchar(32) NOT NULL DEFAULT'', 　　'sex' char(1)NOT NULL DEFAULT'', 　　'rdate' date NOT NULL DEFAULT '0000-00-00', 　　PRIMARY KEY('id'), 　　KEY 'nickname' ('nickname') ）ENGINE=InnoDB partition by hash(id) partitions 10； select count（nickname）from Profile； count（1）：9999248 ~~~~ 因為是根據HASH分區的，因此每個區分的記錄數大致是相同的，即數據分布比較均勻： ~~~~ select table_name，partition_name，table_rows from information_schema.PARTITIONS where table_schema=database() and table_name='Profile'; ~~~~ 注意：即使是根據自增長主鍵進行的HASH分區，也不能保證分區數據的均勻。因為插入的自增長ID并非總是連續的，如果該主鍵值因為某種原因被回滾了，則該值將不會再次被自動使用。 如果進行主鍵的查詢，可以發現分區的確是有意義的： `explain partitions select * from Profile where id=1\G;` 可以發現只尋找了p1分區。 但是對于表Profile中nickname列索引的查詢，EXPLAIN PARTITIONS則會得到如下的結果： `explain partitions select * from Profile where nickname='david'\G;` 可以看到，MySQL數據庫會搜索所有分區，因此查詢速度會慢很多，比較上述的語句： `select * from Profile where nickname='david'\G;` 上述簡單的索引查找語句竟然需要1.05秒，這顯然是因為搜索所有分區的關系，實際的IO執行了20～30次，在未分區的同樣結構和大小的表上執行上述SQL語句，只需要0.26秒。 因此對于使用InnoDB存儲引擎作為OLTP應用的表，在使用分區時應該十分小心，設計時要確認數據的訪問模式，否則在OLTP應用下分區可能不僅不會帶來查詢速度的提高，反而可能會使你的應用執行得更慢。 ### 表與分區交換數據 MySQL5.6 開始支持 ALTER TABLE *** EXCGABGE PARTITION 語法。該語法允許分區或者子分區的數據與另一個非分區的表中的數據進行交換。如果非分區表中的數據為空，那么相當于將分區中的數據移動到非分區表中。若分區表沒有數據這將外部數據移動到分區表中。 ALTER TABLE *** EXCGABGE PARTITION 語法，必須滿足條件： （1）要交換的表需和分區表有著相同的表結構，但是表不能含有分區 （2）在非分區表中數據必須在交換分區定義內 （3）被交換的表中不能含有外鍵，或者其他的表含有對該表的外鍵引用 （4）用戶除了需要alter、insert和 create 權限外，還需要DROP的權限 （5）使用該語句是，不會觸發交換表和被交換表上的觸發器 （6）AUTO_INCREMENT 列將被重置 列如: `create table e ( id int not null , fname varchar(30), lname varchar(30) ) partition by range (id) ( partition p0 values less than (50),partition p1 values less than (100),partition p2 values less than (150),partition p3 values less than (MAXVALUE));` ~~~~ MySQL [庫名]> insert into e values -> (1669,'jim','smith'), -> (337,'mary','jones'), -> (16,'frank','white'), -> (2005,"linda",'black'); ~~~~ e2與e結構一樣 但是e2不能分區 `create table e2 like e;` 刪除分區:` alter table e2 remove partitioning; ` 觀察分區表的數據: `SELECT PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';`  將e的分區p0的數據移動到表e2中: `alter table e exchange partition p0 with table e2;` 再查詢數據: `SELECT PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';`  查詢e2的數據 `select * from e2;`