共計 3375 個字符，預計需要花費 9 分鐘才能閱讀完成。

自動寫代碼機器人，免費開通

丸趣 TV 小編給大家分享一下 Schema 與數據類型優化的示例，希望大家閱讀完這篇文章之后都有所收獲，下面讓我們一起去探討吧！

4.2MySQL schema 設計中的陷阱 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 會更好：

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 常量，如果移除的是被用到的常量、查詢返回空字符串

基本技術為想要的表結果創建新的 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 喜歡簡單（好恰、我也是）

最好避免使用 bit

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

盡量使用整型定義標識列

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

應該盡可能避免使用 null 值，除非真實數據模型中有確切需要

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

注意可變長字符串，其在臨時表和排序時可能導致悲觀的按 max 長度分配內存

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

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

范式是好的，但反范式有時也是必要的；預先計算、緩存或生成匯總表也可獲很大好處

alter table 大部分情況會鎖表且重建整張表（讓人痛苦）本章提供了一些有風險的方法，

看完了這篇文章，相信你對“Schema 與數據類型優化的示例”有了一定的了解，如果想了解更多相關知識，歡迎關注丸趣 TV 行業資訊頻道，感謝各位的閱讀！