有助於效率的類型選擇
使你的數據盡可能小
最基本的優化之一是使你的數據(和索引)在磁盤上(並且在內存中)占據的空間盡可能小這能給出巨大的改進因為磁盤讀入較快並且通常也用較少的主存儲器如果在更小的列上做索引索引也占據較少的資源
你能用下面的技術使表的性能更好並且使存儲空間最小
·盡可能地使用最有效(最小)的類型MySQL有很多節省磁盤空間和內存的專業化類型
·如果可能使表更小使用較小的整數類型例如MEDIUMINT經常比INT好一些
·如果可能聲明列為NOT NULL它使任何事情更快而且你為每列節省一位注意如果在你的應用程序中你確實需要NULL你應該毫無疑問使用它只是避免缺省地在所有列上有它
使用定長列不使用可變長列
這條准則對被經常修改從而容易產生碎片的表來說特別重要例如應該選擇 CHAR 列而不選擇 VARCHAR 列所要權衡的是使用定長列時表所占用的空間更多但如果能夠承擔這種空間的耗費使用定長行將比使用可變長的行處理快得多
將列定義為 NOT NULL
這樣處理更快所需空間更少而且有時還能簡化查詢因為不需要檢查是否存在特例 NULL
考慮使用 ENUM 列
如果有一個只含有限數目的特定值的列那麼應該考慮將其轉換為 ENUM 列ENUM 列的值可以更快地處理因為它們在內部是以數值表示的
有關BLOB和TEXT類型
使用BLOB和TEXT類型的優點
用 BLOB 存儲應用程序中包裝或未包裝的數據有可能使原來需要幾個檢索操作才能完成的數據檢索得以在單個檢索操作中完成而且還對存儲標准表結構不易表示的數據或隨時間變化的數據有幫助
使用BLOB和TEXT類型的可能弊端
另一方面BLOB 值也有自己的固有問題特別是在進行大量的 DELETE 或 UPDATE 操作時更是如此刪除 BLOB 會在表中留下一個大空白在以後將需用一個記錄或可能是不同大小的多個記錄來填充
除非有必要否則應避免檢索較大的 BLOB 或 TEXT 值例如除非肯定WHERE 子句能夠將結果恰好限制在所想要的行上否則 SELECT * 查詢不是一個好辦法這樣做可能會將非常大的 BLOB 值無目的地從網絡上拖過來這是存儲在另一列中的 BLOB 標識信息很有用的另一種情形可以搜索該列以確定想要的行然後從限定的行中檢索 BLOB 值
必要的准則
對容易產生碎片的表使用 OPTIMIZE TABLE
大量進行修改的表特別是那些含有可變長列的表容易產生碎片碎片不好因為它在存儲表的磁盤塊中產生不使用的空間隨著時間的增長必須讀取更多的塊才能取到有效的行從而降低了性能任意具有可變長行的表都存在這個問題但這個問題對 BLOB 列更為突出因為它們尺寸的變化非常大經常使用 OPTIMIZE TABLE 有助於保持性能不下降
使用多列索引
多列索引列有時很有用一種技術是根據其他列建立一個散列值並將其存儲在一個獨立的列中然後可通過搜索散列值找到行這只對精確匹配的查詢有效(散列值對具有諸如<或>=這樣的操作符的范圍搜索沒有用處)在MySQL 版及以上版本中散列值可利用 MD( ) 函數產生散列索引對 BLOB 列特別有用有一事要注意在 MySQL 以前的版本中不能索引 BLOB 類型甚至是在 或更新的版本中利用散列值作為標識值來查找 BLOB 值也比搜索 BLOB 列本身更快
將 BLOB 值隔離在一個獨立的表中
在某些情況下將 BLOB 列從表中移出放入另一個副表可能具有一定的意義條件是移出 BLOB 列後可將表轉換為定長行格式這樣會減少主表中的碎片而且能利用定長行的性能優勢
使用ANALYSE過程檢查表列
如果使用的是 MySQL 或更新的版本應該執行 PROCEDURE ANALYSE( )查看它所提供的關於表中列的信息
ANALYSE([max elements[max memory]])
它檢驗來自你的查詢的結果並返回結果的分析
max elements(缺省)是analyse將注意的每列不同值的最大數量這被ANALYSE用來檢查最佳的列類型是否應該是ENUM類型
max memory(缺省)是在analyse嘗試尋找所有不同值的時候應該分配給每列的最大內存量
SELECT FROM WHERE PROCEDURE ANALYSE([max elements[max memory]])
例如
mysql>SELECT * FROM student PROCEDURE ANALYSE();
mysql>SELECT * FROM student PROCEDURE ANALYSE();
相應輸出中有一列是關於表中每列的最佳列類型的建議第二個例子要求 PROCEDURE ANALYSE( ) 不要建議含有多於 個值或取多於 字節的 ENUM 類型(可根據需要更改這些值)如果沒有這樣的限制輸出可能會很長;ENUM 的定義也會很難閱讀
根據 PROCEDURE ANALYSE( ) 的輸出會發現可以對表進行更改以利用更有效的類型如果希望更改值類型使用 ALTER TABLE 語句即可
From:http://tw.wingwit.com/Article/program/MySQL/201311/29636.html
