快捷導(dǎo)航

MySQL中數(shù)據(jù)類型相關(guān)的優(yōu)化辦法

更新時間：2015年07月08日 11:14:58 投稿：goldensun

這篇文章主要介紹了MySQL中數(shù)據(jù)類型相關(guān)的優(yōu)化辦法,包括使用多列索引等相關(guān)的優(yōu)化方法,需要的朋友可以參考下

有助于效率的類型選擇

1、使你的數(shù)據(jù)盡可能小

最基本的優(yōu)化之一是使你的數(shù)據(jù)(和索引)在磁盤上(并且在內(nèi)存中)占據(jù)的空間盡可能小。這能給出巨大的改進(jìn)，因為磁盤讀入較快并且通常也用較少的主存儲器。如果在更小的列上做索引，索引也占據(jù)較少的資源。

你能用下面的技術(shù)使表的性能更好并且使存儲空間最?。?/p>

·盡可能地使用最有效(最小)的類型。MySQL有很多節(jié)省磁盤空間和內(nèi)存的專業(yè)化類型。

·如果可能使表更小，使用較小的整數(shù)類型。例如，MEDIUMINT經(jīng)常比INT好一些。

·如果可能，聲明列為NOT NULL。它使任何事情更快而且你為每列節(jié)省一位。注意如果在你的應(yīng)用程序中你確實需要NULL，你應(yīng)該毫無疑問使用它，只是避免缺省地在所有列上有它。

2、使用定長列，不使用可變長列

這條準(zhǔn)則對被經(jīng)常修改，從而容易產(chǎn)生碎片的表來說特別重要。例如，應(yīng)該選擇 CHAR 列而不選擇 VARCHAR 列。所要權(quán)衡的是使用定長列時，表所占用的空間更多，但如果能夠承擔(dān)這種空間的耗費，使用定長行將比使用可變長的行處理快得多。

3、將列定義為 NOT NULL

這樣處理更快，所需空間更少。而且有時還能簡化查詢，因為不需要檢查是否存在特例 NULL。

4、考慮使用 ENUM 列

如果有一個只含有限數(shù)目的特定值的列，那么應(yīng)該考慮將其轉(zhuǎn)換為 ENUM 列。ENUM 列的值可以更快地處理，因為它們在內(nèi)部是以數(shù)值表示的。

有關(guān)BLOB和TEXT類型
1、使用BLOB和TEXT類型的優(yōu)點

用 BLOB 存儲應(yīng)用程序中包裝或未包裝的數(shù)據(jù)，有可能使原來需要幾個檢索操作才能完成的數(shù)據(jù)檢索得以在單個檢索操作中完成。而且還對存儲標(biāo)準(zhǔn)表結(jié)構(gòu)不易表示的數(shù)據(jù)或隨時間變化的數(shù)據(jù)有幫助。

2、使用BLOB和TEXT類型的可能弊端

另一方面，BLOB 值也有自己的固有問題，特別是在進(jìn)行大量的 DELETE 或 UPDATE 操作時更是如此。刪除 BLOB 會在表中留下一個大空白，在以后將需用一個記錄或可能是不同大小的多個記錄來填充。

除非有必要，否則應(yīng)避免檢索較大的 BLOB 或 TEXT 值。例如，除非肯定WHERE 子句能夠?qū)⒔Y(jié)果恰好限制在所想要的行上，否則 SELECT * 查詢不是一個好辦法。這樣做可能會將非常大的 BLOB 值無目的地從網(wǎng)絡(luò)上拖過來。這是存儲在另一列中的 BLOB 標(biāo)識信息很有用的另一種情形?？梢运阉髟摿幸源_定想要的行，然后從限定的行中檢索 BLOB 值。

3、必要的準(zhǔn)則

對容易產(chǎn)生碎片的表使用 OPTIMIZE TABLE
大量進(jìn)行修改的表，特別是那些含有可變長列的表，容易產(chǎn)生碎片。碎片不好，因為它在存儲表的磁盤塊中產(chǎn)生不使用的空間。隨著時間的增長，必須讀取更多的塊才能取到有效的行，從而降低了性能。任意具有可變長行的表都存在這個問題，但這個問題對 BLOB 列更為突出，因為它們尺寸的變化非常大。經(jīng)常使用 OPTIMIZE TABLE 有助于保持性能不下降。

使用多列索引

多列索引列有時很有用。一種技術(shù)是根據(jù)其他列建立一個散列值，并將其存儲在一個獨立的列中，然后可通過搜索散列值找到行。這只對精確匹配的查詢有效。（散列值對具有諸如“<”或“>=”這樣的操作符的范圍搜索沒有用處）。在MySQL 3.23版及以上版本中，散列值可利用 MD5( ) 函數(shù)產(chǎn)生。散列索引對 BLOB 列特別有用。有一事要注意，在 MySQL 3.23.2 以前的版本中，不能索引 BLOB 類型。甚至是在 3.23.2 或更新的版本中，利用散列值作為標(biāo)識值來查找 BLOB 值也比搜索 BLOB 列本身更快。

將 BLOB 值隔離在一個獨立的表中

在某些情況下，將 BLOB 列從表中移出放入另一個副表可能具有一定的意義，條件是移出 BLOB 列后可將表轉(zhuǎn)換為定長行格式。這樣會減少主表中的碎片，而且能利用定長行的性能優(yōu)勢。

使用ANALYSE過程檢查表列
如果使用的是 MySQL 3.23 或更新的版本，應(yīng)該執(zhí)行 PROCEDURE ANALYSE( )，查看它所提供的關(guān)于表中列的信息

ANALYSE([max elements,[max memory]])

它檢驗來自你的查詢的結(jié)果并返回結(jié)果的分析。

max elements（缺省256)是analyse將注意的每列不同值的最大數(shù)量。這被ANALYSE用來檢查最佳的列類型是否應(yīng)該是ENUM類型。

max memory（缺省8192)是在analyse嘗試尋找所有不同值的時候應(yīng)該分配給每列的最大內(nèi)存量。

SELECT ... FROM ... WHERE ... PROCEDURE ANALYSE([max elements,[max memory]])

例如：

mysql>SELECT * FROM student PROCEDURE ANALYSE();

mysql>SELECT * FROM student PROCEDURE ANALYSE(16,256);

相應(yīng)輸出中有一列是關(guān)于表中每列的最佳列類型的建議。第二個例子要求 PROCEDURE ANALYSE( ) 不要建議含有多于 16 個值或取多于 256 字節(jié)的 ENUM 類型（可根據(jù)需要更改這些值）。如果沒有這樣的限制，輸出可能會很長；ENUM 的定義也會很難閱讀。

根據(jù) PROCEDURE ANALYSE( ) 的輸出，會發(fā)現(xiàn)可以對表進(jìn)行更改以利用更有效的類型。如果希望更改值類型，使用 ALTER TABLE 語句即可。

您可能感興趣的文章: