前言

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

話說你平時(shí)創(chuàng)建mysql時(shí)遇到過以下創(chuàng)建索引報(bào)錯的情況嗎？

報(bào)錯提示如下

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

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

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

他用同樣表結(jié)構(gòu)和加索引的語法，都是相同的，但是一個測試環(huán)境和一個線上MySQL版本，一個執(zhí)行成功一個執(zhí)行失??！

事故現(xiàn)場：

同事A的迭代開發(fā)測試完了，準(zhǔn)備將環(huán)境放到預(yù)發(fā)布了，在添加索引的時(shí)候居然創(chuàng)建索引報(bào)錯了，瞬間一臉郁悶，我測試過得了?。?/p>

是的，沒錯，雖然測過了，但是因?yàn)榘姹静煌?，在為?shù)據(jù)庫字段類型(varchar(255))創(chuàng)建索引的時(shí)候，指定的字段有可能會超過了存儲引擎默認(rèn)的長度！

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

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

不同存儲引擎索引長度

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

圖片

MyISAM

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

? 聯(lián)合索引：索引長度和不能超過 1000 bytes，否則會報(bào)錯，創(chuàng)建失敗

InnoDB

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

? 聯(lián)合索引：各列長度不超過 767 字節(jié) ，如果有超過 767 bytes 的，則給出報(bào)警，索引最后創(chuàng)建成功，但是對于超過 767 字節(jié)的列取前綴索引，與索引列順序無關(guān)，總和不得超過 3072 ，否則失敗，無法創(chuàng)建

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

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

MySQL版本對索引長度限制

圖片

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

開啟 innodb_large_prefix 可以使單索引的長度限制達(dá)到 3072 字節(jié)（但是聯(lián)合索引總長度限制還是 3072 字節(jié)），禁用時(shí)單索引的長度限制為 767 字節(jié)
MySQL5.7： MySQL5.5版本與MySQL5.6 版本，innodb_large_prefix 是默認(rèn)關(guān)閉的，在 MySQL5.7 及以上版本則默認(rèn)開啟
MySQL8.0 版本：innodb_large_prefix 已被移除，從版本 8.0 開始，索引長度限制由行格式?jīng)Q定

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

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

字符集對索引長度影響

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

未開啟 innodb_large_prefix

圖片

未開啟 innodb_large_prefix

MySQL5.7默認(rèn)開啟了innodb_large_prefix，或者更高版本，比如MySQL8

圖片

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

解決方案

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

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

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

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

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