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會更好：

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喜歡簡單（好恰、我也是）

1. 最好避免使用bit

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

3. 盡量使用整型定義標識列

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

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

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

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

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

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

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

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

相關文章：

【MySQL數據庫】第三章解讀：服務器性能剖析（上）

【MySQL數據庫】第三章解讀：服務器性能剖析 （下）

以上就是【MySQL數據庫】第四章解讀：Schema與數據類型優化（下）的詳細內容，更多請關注php中文網其它相關文章！