【MySQL資料庫】第四章解讀：Schema與資料類型最佳化（下）

4.2MySQL schema設計中的陷阱

因為mysql實現機制導致了一些特定錯誤，如何避免，慢慢道來：

1、太多的列

MySQL儲存引擎api工作時需要在伺服器層和儲存引擎層通過行緩衝格式拷貝資料，然後在伺服器層將緩衝內容解碼成各個列，從行緩衝中將編碼過的列轉換成行資料的操作代價高，myisam定長行與伺服器行結構正好匹配，不需要轉換；但是變長行結構 InnoDB的行結構總是需要轉換，轉換代價依賴於列的數量。

2、太多的關聯

實體-屬性-值EAV：糟糕的設計模式，mysql限制了每個關聯操作最多隻能有61張表，但EAV資料庫需許多自關聯；一個粗略的經驗法則，如果希望查詢執行得快速且並發性好，單個查詢最好在12個表內做關聯；

3、防止過度使用枚舉

注意防止過度使用枚舉；使用外部索引鍵關聯到字典表或尋找表尋找具體的值，在mysql中，需要在枚舉列表中添加值時，要做一次alter table；MySQL5.0更早alter table阻塞操作，5.1更新版本中，不是在列表末尾增加值也會一樣需要alter table

4、非此發明not invent here的null

建議存空值可以用0、特殊值、Null 字元串代替，盡量不要null；但是不要走極端，在某些情境下、使用null會更好：

create table ……（//全0 （不可能的日期）會導致很多問題    dt datetime not null default '0000-00-00 00:00:00'    ……）

MySQL會在索引中儲存null值，Oracle不會

4.3範式與反範式

4.3.1優缺點

1、範式化的更新操作更快

2、當資料較好地範式化時，很少有重複資料，只需要修改更少的資料

3、範式化的表更小，可更好地放到記憶體裡，執行操作更快

4、很少冗餘資料，檢索列表資料時更少需要distinct、group by語句

缺點：

需要關聯，有代價且可能使索引無效

4.3.2反範式的優點和缺點

避免關聯，資料比記憶體大可能比關聯要快很多（避免了隨機I/O）

4.4緩衝表和匯總表

緩衝表：

對最佳化搜尋和檢索查詢語句很有效，

儲存那些可以較簡單地從其他表擷取資料（每次擷取速度比較慢）的表

匯總表：儲存使用group by語句彙總資料的表

使用時決定是即時維護資料還是定期重建，定期重建：節省資源、片段少、順序組織的索引（高效）

重建時，保證資料在操作時依然可用，通過“影子表”來實現，影子表：一張在真實表背後建立的表，在完成建表操作後，可通過原子的重新命名操作切換影子表和原表

4.4.1物化視圖

預先計算並存在磁碟上的表，可通過各種策略重新整理和更新，mysql不原生支援，可使用Justin Swanhart工具flexviews實現：

flexviews組成：

• 變更資料抓取，讀取伺服器二進位日誌且解析相關行的變更

• 一系列可以協助 建立和管理 視圖 的定義 的 預存程序

• 一些可應用變更到 資料庫中的物化視圖 的工具

flexviews通過提取對源表的更改，可增量地重新計算物化視圖的內容：不需要查詢未經處理資料（高效）

4.4.2計數器表

計數器表：緩衝一個使用者朋友數、檔案下載次數等，推薦建立一張獨立的表格儲存體計數器，避免查詢快取失效；

更新加事務，只能串列執行，為了更高的並發性，可將計數器儲存在多行，每次隨機選一行更新，要統計結果時，彙總查詢；（這個我讀了兩三邊，可能比較笨吧，就是同一個計數器儲存多分，每次選其中一個更新，最後求和，好像還不是很好理解哈，多讀幾遍吧）

4.5加快alter table 操作的速度

mysql大部分修改表結構是：用新的結果建立空表、從舊錶中查出all資料插入新表，刪除舊錶

mysql5.1及更新包含一些類型的“線上”操作的支援，整個過程不需要全鎖表，最新版的InnoDB(MySQL5.5和更新版本中唯一的InnoDB)支援通過排序來建索引，建索引更快且緊湊的布局；

一般而言，大部分alter table導致mysql服務中斷，對常見情境，使用的技巧：

1、先在一台不提供服務的機器上執行alter table操作，然後和提取服務的主庫進行切換

2、影子拷貝，用要求的表結構建立張和源表無關的新表，通過重新命名、刪表交換兩張表（上有）

不是all的alter table都引起表重建，理論上可跳過建立表的步驟：列預設值實際上存在表的.frm檔案中，so可直接修改這個檔案不需要改動表本身，但mysql還沒有採用這種最佳化方法，all的modify column將導致表重建；

alter column：通frm檔案改變列預設值：alter table容許使用alter column、modify column change column修改列，三種操作不一樣;

alter table sakila.film alter column rental_duration set default 5;

4.5.1隻修改frm檔案

mysql有時在沒有必要的時候也重建表，如果願冒一些風險，可做些其他類型的修改而不用重建表：下面操作可能不能正常工作，先備份資料

下面操作不需要重建表：

1、移除一個列的auto_increment

2、增加、移除、更改enum和set常量，如果移除的是被用到的常量、查詢返回Null 字元串

基本技術為想要的表結果建立新的frm檔案，然後用它替換掉已經存在的那張表的frm檔案：

1、建立一張有相同結構的空表，進行所需的修改

2、執行flush tables with read lock：關閉all正在使用的表且禁止任何錶被開啟

3、交換frm檔案

4、執行unlock tables釋放第2步的讀鎖

樣本略

4.5.2快速建立myISAM索引

1、為高效地載入資料到MyISAM表，常用技巧：先禁用索引、載入資料、重啟索引：因為構建索引的工作延遲到資料載入後，此時可通過排序構建索引，快且使得索引樹的片段更少、更緊湊

但是對唯一索引無效（disable keys），myisam會在記憶體中構造唯一索引且為載入的每一行檢查唯一性，一旦索引大小超過有效記憶體、載入操作會越來越慢；

2、在現代版InnoDB中，有個類似技巧：先刪除all非唯一索引，然後增加新的列，最後重建刪除掉的索引（依賴於innodb快速線上索引建立功能）Percona server可自動完成這些操作；

3、像前alter table 的駭客方法來加速這個操作，但需多做些工作且承擔風險，這對從備份中載入資料很有用，如already know all data is effective ，and no need to do the unique check

• 用需要的表結構建立一張表，不包括索引（如用load data file 且載入的表是空的，myisam可排序建索引）

• 載入資料到表中以構建MYD檔案

• 按需要的結構建立另外一張空表，這次要包含索引，會建立.frm .MYI檔案

• 獲讀鎖並重新整理表

• 重新命名第二張表的frm檔案 MYI，讓mysql認為這是第一張表的檔案

• 釋放讀鎖

• 使用repair table來重建表的索引，該操作會通過排序來構建all索引、包括唯一索引

4.6總結

良好的schema設計原則是普通使用的，但mysql有自己的實現細節要注意，概括來說：儘可能保持任何東西小而簡單總是好的；mysql喜歡簡單（好恰、我也是）

1. 最好避免使用bit

2. 使用小而簡單的合適類型；

3. 盡量使用整型定義識別欄位

4. 避免過度設計，比如會導致極複雜查詢的schema設計，或很多列；

5. 應該儘可能避免使用null值，除非真實資料模型中有確切需要

6. 盡量使用相同的類型儲存相似、相關的值，特別是關聯條件中使用的列

7. 注意可變長字串，其在暫存資料表和排序時可能導致悲觀的按max長度分配記憶體

8. 避免使用遺棄的特性，如指定浮點數的精度，或整數的顯示寬度

9. 小心使用enum和set，雖然他們用起來很方便，但不要濫用，有時會變陷阱

10. 範式是好的，但反範式有時也是必要的；預先計算、緩衝或產生匯總表也可獲很大好處

11. alter table 大部分情況會鎖表且重建整張表（讓人痛苦）本章提供了一些有風險的方法，大部分情境必須使用其他更常規的方法

相關文章：

【MySQL資料庫】第三章解讀：伺服器效能剖析（上）

【MySQL資料庫】第三章解讀：伺服器效能剖析 （下）