MySQL InnoDB引擎索引長度受限怎麼辦？

標籤：

大家應該知道InnoDB單列索引長度不能超過767bytes，聯合索引還有一個限制是長度不能超過3072。

1. mysql> CREATE TABLE `tb` (  
2.     ->   `a` varchar(255) DEFAULT NULL,  
3.     ->   `b` varchar(255) DEFAULT NULL,  
4.     ->   `c` varchar(255) DEFAULT NULL,  
5.     ->   `d` varchar(255) DEFAULT NULL,  
6.     ->   `e` varchar(255) DEFAULT NULL,  
7.     ->   KEY `a` (`a`,`b`,`c`,`d`,`e`)  
8.     -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;  
9. ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes 

可以看到，由於每個欄位佔用255*3, 因此這個索引的大小是3825>3072，報錯。

為什麼3072

         我們知道InnoDB一個page的預設大小是16k。由於是Btree組織，要求葉子節點上一個page至少要包含兩條記錄（否則就退化鏈表了）。

         所以一個記錄最多不能超過8k。

        又由於InnoDB的聚簇索引結構，一個二級索引要包含主鍵索引，因此每個單個索引不能超過4k （極端情況，pk和某個二級索引都達到這個限制）。

         由於需要預留和輔助空間，扣掉後不能超過3500，取個“整數”就是(1024*3)。 

單列索引限制

         上面有提到單列索引限制767，起因是256×3-1。這個3是字元最大佔用空間（utf8）。但是在5.5以後，開始支援4個位元組的uutf8。255×4>767, 於是增加了一個參數叫做 innodb_large_prefix。

         這個參數預設值是OFF。當改為ON時，允許列索引最大達到3072。

可以看到預設行為是建表成功，報一個warning，並且將長度階段為255。

注意要生效需要加row_format=compressed或者dynamic  。

如果確實需要在單個很大的列上建立索引，或者需要在多個很大的列上建立聯合索引，而又超過了索引的長度限制，解決辦法是在建索引時限制索引prefix的大小：

例如：create index yarn_app_result_i4 on yarn_app_result (flow_exec_id(100), another_column(50));

這樣，在建立索引時就會限制使用的每個列的最大長度。如上的例子中，在建立聯合索引時，最多使用列flow_exec_id中前100個字元建立索引，最多使用another_column中前

50個字元建立索引。這樣子，就可以避免索引長度過大的問題。

最後，我想說一句。我們在設計資料庫時，最好不要在一個可能包含很長字串的列上建立索引，尤其是當這個列中的字串都很長時。如果在這類列上建立了索引，那麼在建立索引時以及根據索引查詢時，都會浪費很多時間在計算和儲存上。有經驗的設計人員應該不會這樣設計資料庫。

作者簡介：屈世超，對高並發系統設計開發感興趣，現專註於大資料開發工作。曾任職小米科技公司服務端後台開發工程師，現擔任EverString資料平台組進階開發工程師。

MySQL InnoDB引擎索引長度受限怎麼辦？