MySQL中索引的長度的限制

• 2019 年 10 月 4 日
• 筆記

參考： http://dinglin.iteye.com/blog/1681332

單列索引的長度的限制

    (5.6裏面默認不能超過767bytes，5.7不超過3072bytes)：

    起因是256×3-1=767。這個3是字符最大佔用空間（utf8）。但是在5.5以後，開始支持4個位元組的uutf8。255×4>767, 於是增加了一個參數叫做 innodb_large_prefix

    # 256的由來： 只是因為char最大是255，所以以前的程序員以為一個長度為255的index就夠用了，所以設置這個256.歷史遺留問題。   — by 阿里-丁奇

在MySQL5.6里默認 innodb_large_prefix=0 限制單列索引長度不能超過767bytes

   官網文檔：https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_large_prefix

注意：

在MySQL5.6裏面，設置了innodb_large_prefix=ON、innodb_file_format=barracuda、innodb_file_per_table=ON ，且Innodb表的存儲格式為 DYNAMIC 或 COMPRESSED，則前綴索引最多可包含3072個位元組，前綴索引也同樣適用。

 如下2例：

> create table t_DYNAMIC(  a int PRIMARY key not null AUTO_INCREMENT,  b varchar(3072))   character set=latin1   ROW_FORMAT=DYNAMIC ;  > alter table t_DYNAMIC add index idx_b(b); -- 成功    > create table t_COMPACT(  a int PRIMARY key not null AUTO_INCREMENT,  b varchar(1000))  character set=latin1 ROW_FORMAT=COMPACT ;  > alter table t_COMPACT add index idx_b(b(800));   -- 失敗，提示(1709, Index column size too large. The maximum column size is 767 bytes.)

在MySQL5.7里默認 innodb_large_prefix=1 解除了767bytes長度限制，但是單列索引長度最大還是不能超過3072bytes

聯合索引的長度的限制 (不能超過3072bytes)：

CREATE TABLE `tb` (    `a` varchar(255) DEFAULT NULL,    `b` varchar(255) DEFAULT NULL,    `c` varchar(255) DEFAULT NULL,    `d` varchar(255) DEFAULT NULL,    `e` varchar(255) DEFAULT NULL,    KEY `idx_a` (`a`,`b`,`c`,`d`,`e`)  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    上面這個建表語句會報錯： ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes

    因為它創建的聯合索引idx_a的長度為：255*3*5= 3825 bytes ，大於最大值 3072 bytes

 為什麼3072，原因如下：   

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

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

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

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