前言

我只是創建個索引而已，沒想到還這些遇到個詭異的問題！

話說你平時創建mysql時遇到過以下創建索引報錯的情況嗎？

報錯提示如下

Specified key was too long; max key length is 767 bytes
//需要添加索引的字段的長度太長,超過了767字節

??什么？索引長度限制？我從沒遇到過啊，MySQL還會對索引鍵的長度有限制？

是的，一般都不會遇到，不過這個問題我一個同事就遇到了！

他用同樣表結構和加索引的語法，都是相同的，但是一個測試環境和一個線上MySQL版本，一個執行成功一個執行失敗！

事故現場：

同事A的迭代開發測試完了，準備將環境放到預發布了，在添加索引的時候居然創建索引報錯了，瞬間一臉郁悶，我測試過得了啊！

是的，沒錯，雖然測過了，但是因為版本不同，在為數據庫字段類型(varchar(255))創建索引的時候，指定的字段有可能會超過了存儲引擎默認的長度！

當然你會問，為啥要為varchar(255)創建索引啊，這個就不多追問了！

不過剛好趁對這個問題有印象，今天可以聊聊MySQL單表對索引限制的知識點!

不同存儲引擎索引長度

既然MySQL 對每個單表中所創建的索引長度是有限制，我們先看下不同的存儲引擎下對表的限制是什么樣的。

圖片

MyISAM

? 單列索引：最大長度不能超過 1000 bytes，否則會報警，但是創建成功，最終創建的是前綴索引（取前333個字符）。

? 聯合索引：索引長度和不能超過 1000 bytes，否則會報錯，創建失敗

InnoDB

? 單列索引：超過 767 bytes的，給出warning，最終索引創建成功，取前綴索引（取前 255 字符）

? 聯合索引：各列長度不超過 767 字節 ，如果有超過 767 bytes 的，則給出報警，索引最后創建成功，但是對于超過 767 字節的列取前綴索引，與索引列順序無關，總和不得超過 3072 ，否則失敗，無法創建

默認情況下utf編碼一個字符占三個字節，也就是說在InnoDB引擎中，4個varchar(255)字段就把單表索引長度給占滿了哦！

3072 / 767 約等于 4， 而varchar(255)，在utf8編碼下是765字節，因此四個varchar(255)字段就快超過綜合3072了

MySQL版本對索引長度限制

圖片

MySQL5.5 版本：引入了 innodb_large_prefix，用來禁用大型前綴索引，以便與不支持大索引鍵前綴的早期版本的 InnoDB 兼容

開啟 innodb_large_prefix 可以使單索引的長度限制達到 3072 字節（但是聯合索引總長度限制還是 3072 字節），禁用時單索引的長度限制為 767 字節
MySQL5.7： MySQL5.5版本與MySQL5.6 版本，innodb_large_prefix 是默認關閉的，在 MySQL5.7 及以上版本則默認開啟
MySQL8.0 版本：innodb_large_prefix 已被移除，從版本 8.0 開始，索引長度限制由行格式決定

 若行格式為 DYNAMIC 或 COMPRESSED 時，限制值為 3072，而行格式 REDUNDANT 或 COMPACT 時，限制值為 767。

MySQL在5.7及以上版本在InnoDB引擎中默認行格式是 DYNAMIC，所以限制長度為3072字節。

字符集對索引長度影響

了解完存儲引擎和不同MySQL版本對索引長度的限制，我們以InnoDB引擎為例，看MySQL不同的字符集對索引長度有啥影響。

未開啟 innodb_large_prefix

圖片

未開啟 innodb_large_prefix

MySQL5.7默認開啟了innodb_large_prefix，或者更高版本，比如MySQL8

圖片

UTF8編碼占用3個字節，能表示除了表情符之外的其他字符，UFT8mb4占用4個字節，既能表示漢字也能表示表情符。

解決方案

針對這個問題，一般來說可以考慮一下幾種方式去處理

? 將varchar(255)字段改成更小的字符長度類型

? 如果是MySQL5.5版本與MySQL5.6 版本，可以啟用innodb_large_prefix參數,來使得單個索引字段的長度突破767

? 這種大字段類型可以考慮前綴索引

畢竟varchar(255)這種長度的類型作為索引相對來說并不是那么合適！