[TOC] ### 選擇優化的數據類型 　 ### MySQL schema設計中的陷阱 ### 范式和反范式 范式和反范式: ``` 在范式花數據庫中，每個事實數據會出現并且只出現一次。 反范式化的數據庫中，信息是冗余的，可能會存儲在多個地方。 ``` #### 范式的優缺點 范式化帶來的好處: ``` 1. 范式化的更新操作通常比反范式化要塊。 2. 當數據較好地范式化時，就只有很少或者沒有重復數據，所以只需要修改更少的數據。 3. 范式化的表通常更小，可以更好的放在內存里，所以執行操作更快。 4. 很少有多余的數據意味著檢索列表數據時更少需要DISTINCT或者GROUP BY語句。　 ``` ### 緩存表和匯總表 #### 物化視圖 #### 計數器表 ### 加快ALTER TabLE操作的速度 MySQL的ALTER TABLE操作的性能對大表來說是個大問題。 ``` MySQL執行大部分修改表結構操作的方法是用新的結構創建一個空表，從舊表中查出所有數據插入新表，然后刪除舊表。 ``` #### 只修改.frm文件 大部分ALTER TABLE操作將導致MySQL服務中斷。 對常見的場景，使用的技巧有兩種: ``` 1. 先在一臺不提供服務的機器上執行ALTER TABLE操作，然后和提供服務的主庫進行切換。 2. “影子拷貝“的技巧是用要求的表結構創建一張和源表無關的新表，然后通過重命名和刪表操作交換兩張表。 ``` 修改或刪除一個列的默認值。假如要修改電影的默認租賃期限，從是哪天改到五天，很慢的方式是: ``` mysql> ALTER TABLE sakila.film ->MODIFY COLUMN rental_duration TINYINT(3) NOT NULL DEFAULT 5; ``` SHOW STATUS 顯示這個語句做列1000次讀和1000次插入操作。它拷貝了整張表到一張新表，甚至列的類型、大小和可否為NULL屬性都沒改變。 理論上列的默認值實際上存在表的.frm文件中，可以直接修改這個文件而不需要改動表的本身。然而MySQL還沒有采用這種優化的方法，所有的MODIFY COLUMN操作都將導致表重建。 很快的方法是通過ALTER COLUMN操作來改變列的默認值: ``` mysql> ALTER TABLE sakila.film -> ALTER COLUMN rental_duration SET DEFAULT 5; ``` 下面這些操作是有可能不需要重建表的: ``` 移除（不是增加）一個列的AUTO_INCREMENT屬性 增加、移除或更改ENUM和SET常量。如果移除的是已經有行數據用到其值的常量，查詢將會返回一個空字串值。 ``` 該技巧不是官方支持的，執行前請備份數據。 為想要的表結構創建一個新的.frm 文件，然后用它替換掉已經存在的那張表.frm文件: ``` 1. 創建一張有相同結構的空表，并進行所需要的修改（例如增加ENUM常量） 2. 執行FLUSH TABLES WITH READ LOCK。這將會關閉正在使用的表，并且禁止任何表被打開。 3. 交換.frm文件 4. 執行UNLOCK TABLES 來釋放第二步的讀鎖。 ``` #### 快速創建MyISAM索引 高效地載入數據到MyISAM表中，有一個常用的技巧是先禁用索引、載入數據，然后重新啟用索引： ``` mysql> ALTER TABLE test.load_data DISABLE KEYS; -- load the data mysql> ALTER TABLE test.load_data ENABLE KEYS; ``` 這個方法對唯一索引無效。DISABLE KEYS只對非唯一索引有效。 MyISAM會在內存中構造唯一索引，并且為載入的每一行檢查唯一性。一旦索引的大小超過列有效內存，載入操作就會變得越來越慢。 * InnoDB也有類似的技巧 ``` 這依賴InnoDB快速在線索引創建功能。 這個技巧是先刪除所有的非唯一索引，然后增加新的列，最后重新創建刪除掉的索引。Percona Server可以自動完成這些操作。 ``` * ALTER TABLE的方法來加速這個操作。當已經知道所有數據都是有效的并且沒有必要做唯一性檢查時: ``` 1.用需要的表結構創建一張表，但是不包括索引。 2. 載入數據到表中以構建.MYD文件 3. 按照需要的結構創建另外一張空表，這次要包含索引。這會創建需要的.frm和.MYI文件 4. 獲取讀鎖并刷新表 5. 重命名第二張表的.frm和.MYI文件，讓MySQL認為是第一張表的文件 6. 釋放讀鎖 7. 使用REPAIR TABLE來重建表的索引。該操作會通過排序來構建所有索引，包括唯一索引。 ``` ### 總結