MySQL如何有效地建立基於 INNODB 引擎的表

標籤：

 2016-05-27 趙偉 資料庫開發人員

有使用者問我們為什麼下面這個建表語句會執行失敗，報錯是 "Row size too large ...."
下面我就以這個例子出發講一講使用mysql如何有效地建表。本文使用的圖片是從網路搜尋到的，不是我自己製作的，在此感謝圖片的作者和擁有者。

CREATE TABLE bad_table (
  col1 varchar(20) NOT NULL DEFAULT ‘000001‘,
  col2 varchar(100) DEFAULT NULL,
  col3 varchar(200) DEFAULT NULL,
  col4 varchar(100) DEFAULT NULL,
  col5 varchar(200) DEFAULT NULL,
  col6 varchar(6) DEFAULT NULL,
  col7 varchar(12) DEFAULT NULL,
  col8 varchar(10) DEFAULT NULL,
  col9 varchar(9) DEFAULT NULL,
  col10 varchar(3) DEFAULT NULL,
  col11 varchar(10) DEFAULT NULL,
  col12 varchar(2) DEFAULT NULL,
  col13 varchar(5) DEFAULT NULL,
  col14 varchar(1) DEFAULT NULL,
  col15 varchar(3) DEFAULT NULL,
  col16 decimal(24,6) DEFAULT NULL,
  col17 varchar(3) DEFAULT NULL,
  col18 decimal(24,6) DEFAULT NULL,
  col19 varchar(50) DEFAULT NULL,
  col20 varchar(12) DEFAULT NULL,
  col21 varchar(12) DEFAULT NULL,
  col22 varchar(1) DEFAULT NULL,
  col23 varchar(3) DEFAULT NULL,
  col24 varchar(3) DEFAULT NULL,
  col25 varchar(400) DEFAULT NULL,
  col26 int(11) DEFAULT NULL,
  col27 varchar(10) DEFAULT NULL,
  col28 varchar(1) DEFAULT NULL,
  col29 varchar(32) DEFAULT NULL,
  col30 varchar(400) DEFAULT NULL,
  col31 varchar(400) DEFAULT NULL,
  col32 varchar(400) DEFAULT NULL,
  col33 decimal(24,6) DEFAULT NULL,
  col34 varchar(1) DEFAULT NULL,
  col35 varchar(100) DEFAULT NULL,
  col36 varchar(32) DEFAULT NULL,
  col37 varchar(20) DEFAULT NULL,
  col38 varchar(32) DEFAULT NULL,
  col39 varchar(32) DEFAULT NULL,
  col40 varchar(32) DEFAULT NULL,
  col41 varchar(20) DEFAULT NULL,
  col42 varchar(3) DEFAULT NULL,
  col43 varchar(50) DEFAULT NULL,
  col44 varchar(3) DEFAULT NULL,
  col45 varchar(50) DEFAULT NULL,
  col46 varchar(300) DEFAULT NULL,
  col47 varchar(8) DEFAULT NULL,
  col48 varchar(8) DEFAULT NULL,
  col49 varchar(16) DEFAULT NULL,
  col50 varchar(2) DEFAULT NULL,
  col51 varchar(2) DEFAULT NULL,
  col52 varchar(2) DEFAULT NULL,
  col53 varchar(2) DEFAULT NULL,
  col54 varchar(2) DEFAULT NULL,
  col55 varchar(2) DEFAULT NULL,
  col56 varchar(3) DEFAULT NULL,
  col57 varchar(2) DEFAULT NULL,
  col58 varchar(2) DEFAULT NULL,
  col59 varchar(3) DEFAULT NULL,
  col60 varchar(4) DEFAULT NULL,
  col61 varchar(4) DEFAULT NULL,
  col62 varchar(2) DEFAULT NULL,
  col63 varchar(20) DEFAULT NULL,
  col64 varchar(32) DEFAULT NULL,
  col65 varchar(32) DEFAULT NULL,
  col66 varchar(200) DEFAULT NULL,
  col67 varchar(50) DEFAULT NULL,
  col68 varchar(2) DEFAULT NULL,
  col69 varchar(2) DEFAULT NULL,
  col70 varchar(2) DEFAULT NULL,
  col71 varchar(2) DEFAULT NULL,
  col72 varchar(20) DEFAULT NULL,
  col73 varchar(1) DEFAULT NULL,
  col74 varchar(20) DEFAULT NULL,
  col75 varchar(20) DEFAULT NULL,
  col76 varchar(2) DEFAULT NULL,
  col77 varchar(10) DEFAULT NULL,
  col78 varchar(32) DEFAULT NULL,
  col79 varchar(4) DEFAULT NULL,
  col80 varchar(1) DEFAULT NULL,
  col81 varchar(1) DEFAULT NULL,
  col82 varchar(32) DEFAULT NULL,
  PRIMARY KEY (col1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

這是一個來自於真實的客戶的真實的應用中的表，這裡隱去了客戶的所有資訊，表名和列名也都經過改造，去掉了關鍵資訊，而且去掉了所有的注釋資訊。這個表是一個定義不良的表，它有很多問題，本文就分析一下它的問題以及如何才能更好地建表。
這個表直觀看上去問題包括
1. 過多的列
2. 很多列是最大長度在8以內的varchar類型
3. 有一些最大長度在幾百的varchar列

首先，過多的列導致一行特別長，但是通常並不是每個欄位都會頻繁更新，這樣那些頻繁更新的欄位的更新開銷就會比較大，因為首先過大的行導致一個頁上面儲存的行很少，即使更改一行的一個位元組，也需要完整地讀取它所在的這整個頁面。那麼讀取一個頁面能夠命中的行就會比較少，就可能需要讀取更多的頁面來更新多個行，並且也需要寫盤更多的頁面。同時，還意味著記錄binlog和交易記錄的代價也會增大（可能通過最佳化可以部分地降低這部分代價）。並且任何一行的buffer命中率也會嚴重降低，對查詢和更新的效能都會降低。

同時，對於innodb來說這還意味著當表有很多行（假設是幾千萬行）的時候，b+樹的高度會比較高，導致搜尋時間變長。這裡作一個簡單的計算。假設這個表有1億行，innodb_page_size=4k, 由於innodb要求一頁最少儲存2行資料，那麼主表的b+樹最多會有5千萬個分葉節點頁面，假設平均每個內部節點頁面可以儲存200條記錄（每條記錄儲存 {索引key，下級節點指標} ），那麼也就是 200^h1 >= 5*10^7，可以知道樹的高度h1=4（單個根節點的高度是0），同時主表一共有大約5800萬個頁面。這裡假設innodb的b+樹有較好的自平衡機制，不會產生任何一個左右子樹高度差大於1的子樹。如果表的設計合理每個分葉節點頁面可以儲存一百行的話，那麼一共只有100萬個分葉節點頁面，這樣200^h2 >= 10^6，可以知道h2=3，同時主表一共有大約104萬個頁面。由此可見行的密度和命中率大幅增大，而且h2的樹的搜尋路徑會導致每次搜尋都平均多一次頁面讀取。

再做一個基於極端假設的計算：假設主鍵很長，以至於內節點頁面只能儲存10條記錄，那麼h1將會變成8，這個主表的b+樹將會有大約1000萬個內節點頁面，共約6000萬個頁面；h2會變成6，這個主表的b+樹將會有大約100萬個內節點頁面，共約200萬個頁面。後者的查詢和更新的效率會比前者高出很多。也就是說，任何一個索引的key都最好別太長，否則那個索引的搜尋路徑會比較長，而且buffer的效率（命中率）會降低。對於secondary index道理也類似，而且由於它們的索引行會儲存主鍵，所以主鍵的size也會影響到secondary index的效率。

最後，innodb對於索引key的儲存有一定的最佳化，包括合并同一個索引記錄上面重複的欄位，以及相鄰索引記錄中相同欄位不重複儲存等，會一定程度改進上面計算的結果，但是並不會有本質的改進。innodb的b+樹結構如，本文不在此展開贅述。


2. 根據原SQL語句的注釋可以知道，這些短的varchar欄位中，有下面這些是可以用更合適的類型的。
從儲存的空間效率來講，每個varchar短欄位（長度小於257）還需要額外1個位元組的空間，同時在mysqld內部的查詢處理階段，數實值型別的欄位的計算和處理也會比字串類型更高效一些。不過這些基本是微不足道的，差別不大。更重要的是使用的方便性方面，使用合適的類型在後續的開發工作中會有各種方便，這些數實值型別的存在肯定是有原因的，應該在合適的場合使用它們。

長度在4以內的這些欄位，屬於這麼幾種：
a. 用於儲存bool值，也就是儲存(二元狀態，是/否)。這種 應該使用bool 類型
b. 用於儲存類型值，也就是儲存（類型，分類，種類，層級，等級）等。 這種應該使用enum類型。有人擔心enum預先無法預知所有可能的枚舉值，但其實在alter table中可以on line方式（不需要copy table)修改列定義的方式增加更多的枚舉值的，而且在表格儲存體中枚舉值是作為數字來儲存的，每個欄位最多2個位元組。特別是在查詢，比較，匹配的時候是數字比較而不是字串，因而效率會提升很多。

c. 長度在12以內的欄位
不合理的欄位主要是用於儲存日期時間。應該使用datetime，確保方便和正確的計算和查詢日期和時間，以及正確地索引。特別是在支援時區的情況下，如果不使用date time類型簡直很難正確地計算。

3. 對於那些最大長度在幾百的varchar列，在dynamic格式下它們都是儲存在off page的，也就是不與資料行儲存在同一頁，而是儲存在特定的其他頁面中，這樣讀取這些欄位還需要訪問更多的資料頁。原理如，這裡不展開細講了。

 

事實上dynamic格式下，對於長度大於40位元組的欄位都是這樣儲存的。如果查詢要求這些列的時候，這類欄位越多，查詢執行效率越低，所以業務的查詢語句不要總是select*，特別是在表的列中有很多這種大欄位(varchar,blob,text)的時候，而是針對性地選擇需要的列，這樣可以避免innodb從無謂地取出這些欄位，要知道取出這些欄位每個行都可能需要增加多次磁碟IO操作，特別是它們的頁面儲存效率本來就比較低。在實際儲存一行的時候並不是所有這樣的欄位都會offpage儲存，而是從長到短依次取出做off page儲存，直到行的長度達標（也就是一頁存至少兩行）。這樣的話，行的長度還是可能會比較長，如果最大長度在40以上的varchar列比較多的話。innodb的頁與行的大致結構如：

本例中使用者遇到的問題是由於innodb_page_size=4k，所以這個表的最大可能的行長度超出了4k頁面允許的最大長度（略少於2k)。而解決的辦法包括：
1. 拆分表，以業務需求為基礎，按照ER模型的定義來拆分，同時最好把基本靜態列放在一個表中，經常被更新的放在另一個表中。
2. 如果業務需求要求必須在這個表中定義這麼多列，那麼把最大長度少於40位元組的（本例正好使用的是UTF8）列挑一些可以縮短的，縮短直到可以成功建表為止。

MySQL如何有效地建立基於 INNODB 引擎的表