高效能的MySQL（5）索引策略一壓縮，冗餘，重複，索引和鎖

一、壓縮索引

MyISAM使用首碼壓縮來減少索引的大小，預設只壓縮字串，但是通過設定也可以對整數做壓縮。

壓縮可以使用更少的空間，代價是某些操作可能更慢。特別是倒序的查詢，測試表明，對於CPU密集型的應用，查詢會很慢，特別是倒序。對於I/O密集型應用，查詢可能會不錯。

可以在CREATE TABLE的語句中指定PACK_KEYS參數來指定索引壓縮方式。

二、冗餘和重複索引

重複索引：指在相同的列上按照相同的順序建立的相同類型的索引，要盡量避免重複索引，除非在同一列上建立不同類型的索引來滿足不同的查詢需求。比方說 key(col) 和 fulltext key(col)。

冗餘索引：如果建立了索引（A，B），再建立索引（A）就是冗餘索引。因為索引（A，B）也可以當作（A）來使用（只針對B-Tree）。但是如果再建立索引（B，A）或者（B），則不是冗餘索引。或者類型不同，比方說雜湊，全文索引等。

冗餘索引通常發生在添加索引的時候，大多數情況下都不需要冗餘索引，而是盡量擴充已有的索引，除非擴充已有的索引會導致索引很大，從而影響其他使用索引的效能。

例如：在一個整數列的索引上擴充一個很長的varchar列的索引，效能可能就會急劇下降。但是增加一個新的索引，就會對增刪改操作影響很大，所以要平衡使用。

如何找到這寫索引，以便刪除，可以訪問INFORMATION_SCHEMA，或者一些現有的工具來定位。

有一個值得注意的地方：

對於InnoDB因為二級索引包換了主鍵，所以列（A）上的索引就相當與（A，ID），所以類似

where A=5 order by id 這樣的查詢，這個索引會很有用。但是擴充為索引（A，B）

之後，則實際變成了（A，B，ID），則order by就無法用到索引排序了。

三、索引和鎖

索引可以讓查詢鎖定更少的行，因為索引可以讓查詢不訪問那些不需要的行，那麼就會鎖定更少的行。這有2點好處：

1、減少鎖定行帶來的額外的開銷。

2、鎖定超過需要的行會增加鎖爭用和減少並發性。

InnoDB只有在訪問行的時候才會對其加鎖，而索引能減少InnoDB訪問的行數，從而減少鎖的數量。但這隻有當InnoDB在儲存引擎層能夠過濾掉所有不需要的行時才有效。如果索引無法過濾掉無效的行，那麼在InnoDB檢索到資料返回給伺服器層以後，MySQL伺服器才能應用WHERE子句進行過濾。這時候已經無法避免鎖定行了，InnoDB已經鎖定了所有返回的資料行。

舉例說明：表結構

CREATE TABLE `emp3` (  `id` int(11) NOT NULL DEFAULT '0',  `name` varchar(100) NOT NULL,  `job` varchar(100) NOT NULL,  `num1` int(10) DEFAULT NULL,  `num2` int(10) DEFAULT NULL,  `num3` int(10) DEFAULT NULL,  `job_num` int(10) DEFAULT NULL,  `d` date DEFAULT NULL,  PRIMARY KEY (`id`),  KEY `job_num` (`job_num`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

資料如下：

查看本欄目更多精彩內容：http://www.bianceng.cnhttp://www.bianceng.cn/database/MySQL/