4.2MySQL schema設(shè)計中的陷阱

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

1、太多的列

MySQL存儲引擎api工作時需要在服務器層和存儲引擎層通過行緩沖格式拷貝數(shù)據(jù)，然后在服務器層將緩沖內(nèi)容解碼成各個列，從行緩沖中將編碼過的列轉(zhuǎn)換成行數(shù)據(jù)的操作代價高，myisam定長行與服務器行結(jié)構(gòu)正好匹配，不需要轉(zhuǎn)換；但是變長行結(jié)構(gòu) InnoDB的行結(jié)構(gòu)總是需要轉(zhuǎn)換，轉(zhuǎn)換代價依賴于列的數(shù)量。

2、太多的關(guān)聯(lián)

實體-屬性-值EAV：糟糕的設(shè)計模式，mysql限制了每個關(guān)聯(lián)操作最多只能有61張表，但EAV數(shù)據(jù)庫需許多自關(guān)聯(lián)；一個粗略的經(jīng)驗法則，如果希望查詢執(zhí)行得快速且并發(fā)性好，單個查詢最好在12個表內(nèi)做關(guān)聯(lián)；

3、防止過度使用枚舉

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

4、非此發(fā)明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優(yōu)缺點

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

2、當數(shù)據(jù)較好地范式化時，很少有重復數(shù)據(jù)，只需要修改更少的數(shù)據(jù)

3、范式化的表更小，可更好地放到內(nèi)存里，執(zhí)行操作更快

4、很少冗余數(shù)據(jù)，檢索列表數(shù)據(jù)時更少需要distinct、group by語句

缺點：

需要關(guān)聯(lián)，有代價且可能使索引無效

4.3.2反范式的優(yōu)點和缺點

避免關(guān)聯(lián)，數(shù)據(jù)比內(nèi)存大可能比關(guān)聯(lián)要快很多（避免了隨機I/O）

4.4緩存表和匯總表

緩存表：

對優(yōu)化搜索和檢索查詢語句很有效，

存儲那些可以較簡單地從其他表獲取數(shù)據(jù)（每次獲取速度比較慢）的表

匯總表：保存使用group by語句聚合數(shù)據(jù)的表

使用時決定是實時維護數(shù)據(jù)還是定期重建，定期重建：節(jié)省資源、碎片少、順序組織的索引（高效）

重建時，保證數(shù)據(jù)在操作時依然可用，通過“影子表”來實現(xiàn)，影子表：一張在真實表背后創(chuàng)建的表，在完成建表操作后，可通過原子的重命名操作切換影子表和原表

4.4.1物化視圖

預先計算并存在磁盤上的表，可通過各種策略刷新和更新，mysql不原生支持，可使用Justin Swanhart工具flexviews實現(xiàn)：

flexviews組成：

• 變更數(shù)據(jù)抓取，讀取服務器二進制日志且解析相關(guān)行的變更

• 一系列可以幫助 創(chuàng)建和管理 視圖 的定義 的 存儲過程

• 一些可應用變更到 數(shù)據(jù)庫中的物化視圖 的工具

flexviews通過提取對源表的更改，可增量地重新計算物化視圖的內(nèi)容：不需要查詢原始數(shù)據(jù)（高效）

4.4.2計數(shù)器表

計數(shù)器表：緩存一個用戶朋友數(shù)、文件下載次數(shù)等，推薦創(chuàng)建一張獨立的表存儲計數(shù)器，避免查詢緩存失效；

更新加事務，只能串行執(zhí)行，為了更高的并發(fā)性，可將計數(shù)器保存在多行，每次隨機選一行更新，要統(tǒng)計結(jié)果時，聚合查詢；（這個我讀了兩三邊，可能比較笨吧，就是同一個計數(shù)器保存多分，每次選其中一個更新，最后求和，好像還不是很好理解哈，多讀幾遍吧）

4.5加快alter table 操作的速度

mysql大部分修改表結(jié)構(gòu)是：用新的結(jié)果創(chuàng)建空表、從舊表中查出all數(shù)據(jù)插入新表，刪除舊表

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

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

1、先在一臺不提供服務的機器上執(zhí)行alter table操作，然后和提取服務的主庫進行切換

2、影子拷貝，用要求的表結(jié)構(gòu)創(chuàng)建張和源表無關(guān)的新表，通過重命名、刪表交換兩張表（上有）

不是all的alter table都引起表重建，理論上可跳過創(chuàng)建表的步驟：列默認值實際上存在表的.frm文件中，so可直接修改這個文件不需要改動表本身，但mysql還沒有采用這種優(yōu)化方法，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有時在沒有必要的時候也重建表，如果愿冒一些風險，可做些其他類型的修改而不用重建表：下面操作可能不能正常工作，先備份數(shù)據(jù)

下面操作不需要重建表：

1、移除一個列的auto_increment

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

基本技術(shù)為想要的表結(jié)果創(chuàng)建新的frm文件，然后用它替換掉已經(jīng)存在的那張表的frm文件：

1、創(chuàng)建一張有相同結(jié)構(gòu)的空表，進行所需的修改

2、執(zhí)行flush tables with read lock：關(guān)閉all正在使用的表且禁止任何表被打開

3、交換frm文件

4、執(zhí)行unlock tables釋放第2步的讀鎖

示例略

4.5.2快速創(chuàng)建myISAM索引

1、為高效地載入數(shù)據(jù)到MyISAM表，常用技巧：先禁用索引、載入數(shù)據(jù)、重啟索引：因為構(gòu)建索引的工作延遲到數(shù)據(jù)載入后，此時可通過排序構(gòu)建索引，快且使得索引樹的碎片更少、更緊湊

但是對唯一索引無效（disable keys），myisam會在內(nèi)存中構(gòu)造唯一索引且為載入的每一行檢查唯一性，一旦索引大小超過有效內(nèi)存、載入操作會越來越慢；

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

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

• 用需要的表結(jié)構(gòu)創(chuàng)建一張表，不包括索引（如用load data file 且載入的表是空的，myisam可排序建索引）

• 載入數(shù)據(jù)到表中以構(gòu)建MYD文件

• 按需要的結(jié)構(gòu)創(chuàng)建另外一張空表，這次要包含索引，會創(chuàng)建.frm .MYI文件

• 獲讀鎖并刷新表

• 重命名第二張表的frm文件 MYI，讓mysql認為這是第一張表的文件

• 釋放讀鎖

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

4.6總結(jié)

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

1. 最好避免使用bit

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

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

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

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

6. 盡量使用相同的類型存儲相似、相關(guān)的值，特別是關(guān)聯(lián)條件中使用的列

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

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

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

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

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

相關(guān)文章：

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

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

以上就是【MySQL數(shù)據(jù)庫】第四章解讀：Schema與數(shù)據(jù)類型優(yōu)化（下）的詳細內(nèi)容，更多請關(guān)注php中文網(wǎng)其它相關(guān)文章！