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

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中文網其他相關文章！