摘要：本文介紹了有關數據表的優化技巧，主要內容有，選擇表的類型，打開盡量少的表，鎖定表與查詢速度的關系以及如何優化表以達到提高查詢速度的目的。由於數據的錄入和清除操作，很容易在表的數據文件重產生碎片，對於擁有大型表的數據庫，應該經常使用 myisamchk維護程序來清除碎片，優化表時要選擇一個好的時機，盡量在沒有用戶訪問時優化表。
選擇合適的表的類型，防止數據文件中產生碎塊，同樣可以大大優化檢索的速度，是數據庫的性能最大化。
選擇一種表類型
用MySQL，當前(版本 3 .23.5)你能從一個速度觀點在4可用表的格式之間選擇。
·靜態MyISAM
這種格式是最簡單且最安全的格式，它也是在磁盤格式最快的。速度來自於數據能在磁盤上被找到的難易方式。當所定有一個索引和靜態格式的東西時，它很簡單，只是行長度乘以行數量。而且在掃描一張表時，用每次磁盤讀取來讀入常數個記錄是很容易的。安全性來自於如果當寫入一個靜態MyISAM文件時，你的計算機崩潰，myisamchk能很容易指出每行在哪兒開始和結束，因此它通常能回收所有記錄，除了部分被寫入的那個。注意，在MySQL中，所有索引總能被重建。
·動態MyISAM
這種格式有點復雜，因為每一行必須有一個頭說明它有多長。當一個記錄在更改時變長時，它也可以在多於一個位置上結束。你能使用 OPTIMIZE table或myisamchk整理一張表。如果你在同一個表中有象某些VARCHAR或BLOB列那樣存取/改變的靜態數據，將動態列移入另外一個表以避免碎片可能是一個好主意。
·壓縮MyISAM
這是一個只讀類型，用可選的myisampack工具生成。
·內存(HEAP 堆）
這種表格式對小型/中型查找表十分有用。對拷貝/創建一個常用的查找表(用聯結)到一個(也許臨時)HEAP表有可能加快多個表聯結。假定我們想要做下列聯結，用同樣數據可能要幾倍時間。
SELECT tab1.a, tab3.a FROM tab1, tab2, tab3 WHERE tab1.a = tab2.a and tab2.a = tab3.a and tab2.c != 0;
為了加速它，我們可用tab2和tab3的聯結創建一張臨時表，因為用相同列( tab1.a )查找。這裡是創建該表和結果選擇的命令。
CREATE TEMPORARY TABLE test TYPE=HEAP
SELECT
tab2.a as a2, tab3.a as a3
FROM
tab2, tab3
WHERE
tab2.a = tab3.a and c = 0;
SELECT tab1.a, test.a3 from tab1, test where tab1.a = test.a1;
SELECT tab1.b, test.a3 from tab1, test where tab1.a = test.a1 and something;
靜態(定長)表的特點
這是缺省格式。它用在表不包含VARCHAR、BLOB或TEXT列時候。
所有的CHAR、NUMERIC和DECIMAL列充填到列寬度。
非常快。
容易緩沖。
容易在崩潰後重建，因為記錄位於固定的位置。
不必被重新組織(用myisamchk)，除非一個巨量的記錄被刪除並且你想要歸還空閒磁盤空間給操作系統。
通常比動態表需要更多的磁盤空間。
動態表的特點
如果表包含任何VARCHAR、BLOB或TEXT列，使用該格式。
所有字符串列是動態的(除了那些長度不到4的列)。
每個記錄前置一個位圖，對字符串列指出哪個列是空的('')，或對數字列哪個是零(這不同於包含NULL值的列)。如果字符串列在刪除尾部空白後有零長度，或數字列有零值，它在位圖中標記並且不保存到磁盤上。非空字符串存儲為一個長度字節加字符串內容。
通常比定長表占更多的磁盤空間。
每個記錄僅使用所需的空間。如果一個記錄變得更大，它按需要被切開多段，這導致記錄碎片。
如果你與超過行長度的信息更新行，行將被分段。在這種情況中，你可能必須時時運行myisamchk -r以使性能更好。使用myisamchk -ei tbl_name做一些統計。
在崩潰後不容易重建，因為一個記錄可以是分很多段並且一個連接(碎片)可以丟失。
對動態尺寸記錄的期望行長度是：
3 + (number of columns + 7) / 8 + (number of char columns) + packed size of numeric columns + length of strings + (number of NULL columns + 7) / 8
對每個連接有6個字節的懲罰。無論何時更改引起記錄的增大，一個動態記錄被鏈接。每個新鏈接將至少是20個字節，因此下一增大將可能在同一鏈連中。如果不是，將有另外一個鏈接。你可以用myisamchk -ed檢查有多少鏈接。所有的鏈接可以用 myisamchk -r 刪除。
壓縮表的特點
一張用myisampack實用程序制作的只讀表。所有具有MySQL擴展電子郵件支持的客戶可以為其內部使用保留一個myisampack拷貝。
解壓縮代碼存在於所有MySQL分發，以便甚至沒有myisampack的客戶能讀取用myisampack壓縮的表。
占據很小的磁盤空間，使磁盤使用量減到最小。
每個記錄被單獨壓縮(很小的存取開銷)。對一個記錄的頭是定長的(1-3 字節)，取決於表中最大的記錄。每列以不同方式被壓縮。一些壓縮類型是：
通常對每列有一張不同的哈夫曼表。
後綴空白壓縮。
前綴空白壓縮。
用值0的數字使用1位存儲。
如果整數列的值有一個小范圍，列使用最小的可能類型來存儲。例如，如果所有的值在0到255的范圍，一個BIGINT列(8個字節)可以作為一個TINYINT列(1字節)存儲。
如果列僅有可能值的一個小集合，列類型被變換到ENUM。
列可以使用上面的壓縮方法的組合。
能處理定長或動態長度的記錄，然而不能處理BLOB或TEXT列。
能用myisamchk解壓縮。
MySQL能支持不同的索引類型，但是一般的類型是ISAM。這是一個B樹索引並且你能粗略地為索引文件計算大小為(key_length+4)*0.67，在所有的鍵上的總和。（這是對最壞情況，當所有鍵以排序順序被插入時。）
字符串索引是空白壓縮的。如果第一個索引部分是一個字符串，它也將壓縮前綴。如果字符串列有很多尾部空白或是一個總不能用到全長的VARCHAR列，空白壓縮使索引文件更小。如果很多字符串有相同的前綴，前綴壓縮是有幫助的。
MySQL內存表的特點
堆表僅存在於內存中，因此如果mysqld被關掉或崩潰，它們將丟失，但是因為它們是很快，不管怎樣它們是有用的。
MySQL內部的HEAP表使用沒有溢出區的100%動態哈希並且沒有與刪除有關的問題。
你只能通過使用在堆表中的一個索引的用等式存取東西(通常用=操作符)。
堆表的缺點是：
·你要為你想要同時使用的所有堆表需要足夠的額外內存。
·你不能在索引的一個部分上搜索。
·你不能順序搜索下一個條目(即使用這個索引做一個ORDER BY)。
·MySQL也不能算出在2個值之間大概有多少行。這被優化器使用來決定使用哪個索引，但是在另一方面甚至不需要磁盤尋道。
數據庫表的數量的問題
在同一個數據庫中創建大量數據庫表的缺點是,如果你在一個目錄中有許多文件，打開、關閉和創建操作將會很慢。如果你執行在許多不同表上的 SELECT語句，當表緩存滿時，將有一點開銷，因為對每個必須打開的表，另外一個必須被關閉。你可以通過使表緩沖更大些來減少這個開銷。
為什麼有這麼多打開的表？
當你運行mysqladmin status時，你將看見象這樣的一些東西：
Uptime: 426 Running threads: 1 Questions: 11082 Reloads: 1 Open tables: 12
如果你僅有6個表，這可能有點令人困惑。
MySQL是多線程的，因此它可以同時在同一個表上有許多詢問。為了是2個線程在同一個文件上有不同狀態的問題減到最小，表由每個並發進程獨立地打開。這為數據文件消耗一些內存和一個額外的文件描述符。索引文件描述符在所有線程之間共享。
數據庫表級鎖定的問題
前面的內容主要將精力集中在使個別的查詢更快上。MySQL 還允許影響語句的調度特性，這樣會使來自幾個客戶機的查詢更好地協作，從而單個客戶機不會被鎖定太長的時間。更改調度特性還能保證特定的查詢處理得更快。我們先來看一下 MySQL 的缺省調度策略，然後來看看為改變這個策略可使用什麼樣的選項。出於討論的目的，假設執行檢索（SELECT）的客戶機程序為讀取程序。執行修改表操作（DELETE，INSERT，REPLACE 或 UPDATE）的另一個客戶機程序為寫入程序。
MySQL 的基本調度策略可總結如下：
·寫入請求應按其到達的次序進行處理。
·寫入具有比讀取更高的優先權。
1、對此一個主要的問題如下：
·一個客戶發出一個花很長時間運行的SELECT。
·然後其他客戶在一個使用的表上發出一個UPDATE；這個客戶將等待直到SELECT完成。
·另一個客戶在同一個表上發出另一個SELECT語句；因為UPDATE比SELECT有更高的優先級，該SELECT將等待UPDATE的完成。它也將等待第一個SELECT完成！
對這個問題的一些可能的解決方案是：
·試著使SELECT語句運行得更快；你可能必須創建一些摘要(summary)表做到這點。
·用--low-priority-updates啟動mysqld。這將給所有更新(修改)一個表的語句以比SELECT語句低的優先級。在這種情況下，在先前情形的最後的SELECT語句將在INSERT語句前執行。
·你可以用LOW_PRIORITY屬性給與一個特定的INSERT、UPDATE或DELETE語句較低優先級。
·為max_write_lock_count指定一個低值來啟動mysqld使得在一定數量的WRITE鎖定後給出READ鎖定。
·通過使用SQL命令：SET SQL_LOW_PRIORITY_UPDATES=1，你可從一個特定線程指定所有的更改應該由用低優先級完成。見SET OPTION句法。
·你可以用HIGH_PRIORITY屬性指明一個特定SELECT是很重要的。見SELECT句法。
·如果你有關於INSERT結合SELECT的問題，切換到使用新的MyISAM表，因為它們支持並發的SELECT和INSERT。
·如果你主要混合INSERT和SELECT語句，DELAYED屬性的INSERT將可能解決你的問題。INSERT句法。
·如果你有關於SELECT和DELETE的問題，LIMIT選項的DELETE可以幫助你。見DELETE句法。
2、INSERT DELAYED 在客戶機方的作用
如果其他客戶機可能執行冗長的 SELECT 語句，而且您不希望等待插入完成，此時 INSERT DELAYED 很有用。發布 INSERT DELAYED 的客戶機可以更快地繼續執行，因為服務器只是簡單地將要插入的行插入。
不過應該對正常的 INSERT 和 INSERT DELAYED 性能之間的差異有所認識。如果 INSERT DELAYED 存在語法錯誤，則向客戶機發出一個錯誤，如果正常，便不發出信息。例如，在此語句返回時，不能相信所取得的 AUTO_INCREMENT 值。也得不到惟一索引上的重復數目的計數。之所以這樣是因為此插入操作在實際的插入完成前返回了一個狀態。其他還表示，如果 INSERT DELAYED 語句的行在等待插入中被排隊，並且服務器崩潰或被終止（用 kill -9），那麼這些行將丟失。正常的 TERM 終止不會這樣，服務器會在退出前將這些行插入。
在表鎖的幫助下實現調度策略。客戶機程序無論何時要訪問表，都必須首先獲得該表的鎖。可以直接用 LOCK TABLES 來完成這項工作，但一般服務器的鎖管理器會在需要時自動獲得鎖。在客戶機結束對表的處理時，可釋放表上的鎖。直接獲得的鎖可用 UNLOCK TABLES 釋放，但服務器也會自動釋放它所獲得的鎖。
執行寫操作的客戶機必須對表具有獨占訪問的鎖。在寫操作進行中，由於正在對表進行數據記錄的刪除、增加或更改，所以該表處於不一致狀態，而且該表上的索引也可能需要作相應的更新。如果表處於不斷變化中，此時允許其他客戶機訪問該表會出問題。讓兩個客戶機同時寫同一個表顯然不好，因為這樣會很快使該表不可用。允許客戶機讀不斷變化的表也不是件好事，因為可能在讀該表的那一刻正好正在對它進行更改，其結果是不正確的。
執行讀取操作的客戶機必須有一把防止其他客戶機寫該表的鎖，以保證讀表的過程中表不出現變化。不過，該鎖無需對讀取操作提供獨占訪問。此鎖還允許其他客戶機同時對表進行讀取。讀取不會更改表，所有沒必要阻止其它客戶機對該表進行讀取。
MySQL 允許借助幾個查詢限修飾符對其調度策略施加影響。其中之一是 DELETE、INSERT、LOAD DATA、REPLACE 和 UPDATE 語句的 LOW_PRIORITY 關鍵字。另一個是 SELECT 語句的 HIGH_PRIORITY 關鍵字。第三個是 INSERT 和 REPLACE 語句的 DELAYED 關鍵字。
LOW_PRIORITY 關鍵字按如下影響調度。一般情況下，如果某個表的寫入操作在表正被讀取時到達，寫入程序被阻塞，直到讀取程序完成，因為一旦某個查詢開始，就不能中斷。如果另一讀取請求在寫入程序等待時到達，此讀取程序也被阻塞，因為缺省的調度策略為寫入程序具有比讀取程序高的優先級。在第一個讀取程序結束時，寫入程序繼續，在此寫入程序結束時，第二個讀取程序開始。
如果寫入請求為 LOW_PRIORITY 的請求，則不將該寫入操作視為具有比讀取操作優先級高的操作。在此情形下，如果第二個讀取請求在寫入程序等待時到達，則讓第二個讀取操作排在等待的寫入操作之前。僅當沒有其他讀取請求時，才允許寫入程序執行。這種調度的更改從理論上說，其含義為 LOW_PRIORITY 寫入可能會永遠被阻塞。當正在處理前面的讀取請求時，只要另一個讀取請求到達，這個新的請求允許排在 LOW_PRIORITY 寫入之前。
SELECT 查詢的 HIGH_PRIORITY 關鍵字作用類似。它使 SELECT 插在正在等待的寫入操作之前，即使該寫入操作具有正常的優先級。
INSERT 的 DELAYED 修飾符作用如下，在表的一個 INSERT DELAYED 請求到達時，服務器將相應的行放入一個隊列，並立即返回一個狀態到客戶機程序，以便該客戶機程序可以繼續執行，即使這些行尚未插入表中。如果讀取程序正在對表進行讀取，那麼隊列中的行掛起。在沒有讀取時，服務器開始開始插入延遲行隊列中的行。服務器不時地停下來看看是否有新的讀取請求到達，並進行等待。如果是這樣，延遲行隊列將掛起，並允許讀取程序繼續。在沒有其他的讀取操作時，服務器再次開始插入延遲行。這個過程一直進行到延遲行隊列空為止。
此調度修飾符並非出現在所有 MySQL 版本中。下面的表列出了這些修飾符和支持這些修飾符的 MySQL 版本。可利用此表來判斷所使用的 MySQL 版本具有什麼樣的功能：
語句類型 開始出現的版本
DELETE LOW_PRIOrITY3.22.5 INSERT LOW+PRIOrITY3.22.5 INSERT DELAYED3.22.15 LOAD DATA LOW_PRIORITY3.23.0 LOCK TABLES ... LOW_PRIORITY3.22.8 REPLACE LOW_PRIORITY3.22.5 REPLACE DELAYED3.22.15 SELECT ... HIGH_PRIORITY3.22.9 UPDATE LOW_PRIORITY3.22.5 SET SQL_LOW_PRIORITY_UPDATES3.22.5
對表進行優化
對表的長期使用，由於記錄的刪除與插入，會在表的數據文件產生碎片，下面的幾種情況尤其會有產生碎片的效果：
·使用VARCHAR類型
頻繁使用可變長列的結果就是使表非常容易產生碎片，如果空間允許，盡量采用定長類型CHAR。
·使用類型BLOB和TEXT
特別是進行大量的 DELETE 或 UPDATE 操作時更是如此。刪除 BLOB 會在表中留下一個大空白，在以後將需用一個記錄或可能是不同大小的多個記錄來填充。
·刪除了一個表的大部分，這同樣會在表中留下大量空白。
為了消除表的碎片對性能的影響，需要對表進行優化。在第七章我們介紹了優化表的方法：
1、使用SQL語句OPTIMIZE
OPTIMIZE TABLE tbl_name
2、使用修復程序myisamchk或isamchk
