前言

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

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

報(bào)錯(cuò)提示如下

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

??什么？索引長(zhǎng)度限制？我從沒(méi)遇到過(guò)啊，MySQL還會(huì)對(duì)索引鍵的長(zhǎng)度有限制？

是的，一般都不會(huì)遇到，不過(guò)這個(gè)問(wèn)題我一個(gè)同事就遇到了！

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

事故現(xiàn)場(chǎng)：

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

是的，沒(méi)錯(cuò)，雖然測(cè)過(guò)了，但是因?yàn)榘姹静煌跒閿?shù)據(jù)庫(kù)字段類型(varchar(255))創(chuàng)建索引的時(shí)候，指定的字段有可能會(huì)超過(guò)了存儲(chǔ)引擎默認(rèn)的長(zhǎng)度！

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

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

不同存儲(chǔ)引擎索引長(zhǎng)度

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

圖片

MyISAM

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

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

InnoDB

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

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

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

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

MySQL版本對(duì)索引長(zhǎng)度限制

圖片

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

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

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

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

字符集對(duì)索引長(zhǎng)度影響

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

未開(kāi)啟 innodb_large_prefix

圖片

未開(kāi)啟 innodb_large_prefix

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

圖片

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

解決方案

針對(duì)這個(gè)問(wèn)題，一般來(lái)說(shuō)可以考慮一下幾種方式去處理

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

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

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

畢竟varchar(255)這種長(zhǎng)度的類型作為索引相對(duì)來(lái)說(shuō)并不是那么合適！