小白学习MySQL - 索引键长度限制的问题

末蓝、 2022-11-16 15:25 177阅读 0赞

最近在工作中，碰到了个很诡异的问题，需求是在两个MySQL数据库为同一张表增加一个二级索引(单键值字段(x varchar(500)))，表结构和加索引的语法，都是相同的，但是一个库执行成功了，一个执行失败了，提示错误如下，

> Specified key was too long; max key length is 767 bytes

从字面的意思看，是说指定键超长，而且上限是767字节。这是什么意思？MySQL中还会对索引键的长度有限制？

我们首先从《MySQL 5.6 Reference Manual》的"CREATE INDEX"章节看起，他指出如果是字符串类型的字段，可以指定字符串前多少位创建索引键值，而且键值前缀是存在上限的，在CREATE TABLE、ALTER TABLE、CREATE INDEX语句中，对于非二进制的字符串类型(CHAR、VARCHAR、TEXT)，前缀会按照字符个数计算，对二进制的字符串类型(BINARY、VARBINARY、BLOB)，前缀会按照字节个数计算，因此，当对非二进制的字符串列明确前缀长度的时候，需要考虑多字节字符集的因素，

![48a7a47a1229b88882add78fbbed15ac.png][]

P.S. https://dev.mysql.com/doc/refman/5.6/en/create-index.html

前缀的长度限制，是和存储引擎相关的。如果用的是InnoDB，前缀上限是767字节，当启用innodb\_large\_prefix时，上限可以达到3072字节。如果用的是MyISAM，前缀上限是1000字节。NDB存储引擎，则根本就不支持前缀这种形式。

之所以可以定义一个字段前缀作为键值，存储效率是考虑的一个因素，如果列名的前10个字符通常都是不同的，检索这10个字符创建的索引应该会比检索整个列作为索引的效率更高，使用列前缀作为索引会让索引树更小，不仅节省空间，还可能提高插入操作的速度。

再看一下《MySQL 5.7 Reference Manual》，相同章节中，多了这段描述，是说当使用CREATE INDEX时，如果指定的索引前缀长度超过了列定义的长度上限，则会出现以下两种场景，

*  非唯一索引，如果设置innodb\_strict\_mode=on，该操作就会抛出一个错误，禁止执行，如果设置innodb\_strict\_mode=off，则索引会自动按照列定义的长度上限进行创建，只会提示一个warning。
 *  唯一索引，无论设置innodb\_strict\_mode与否，都会提示错误，禁止执行，因为这可能导致非唯一的值插入的到表中，违反唯一性约束。

![c556ee26bc5896c2608d5ce846cdc403.png][]

P.S. https://dev.mysql.com/doc/refman/5.7/en/create-index.html

《MySQL 8.0 Reference Manual》的内容和5.7相同，不再展示。这个问题在5.6上测，innodb\_strict\_mode=off，依然会提示错误，说明在5.7以上，对这个问题的容忍度降低了，

create table t1(id varchar(10));
    
    
    alter table t1 add index idx_t1_01 (id(15));
    
    
    SQL 错误 [1089] [HY000]: Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys

再回到开始问题，一个库创建索引成功了，一个失败了，首先看下两个库的版本，确实不同，成功的是5.7，失败的是5.6.22。

我们先来看下5.7，开启了innodb\_large\_prefix，Row\_format是Dynamic，表定义的字符集utf8，因为要加索引的字段定义是varchar(500)，允许存储500个字符，utf8的一个字符是3个字节，500个字符就是1500个字节，从文档我们知道，因为设置了innodb\_large\_prefix，所以键值上限是3072个字节，1500<3072，加索引的操作，能正常执行，

再来看5.6，小版本号是5.6.22，未开启innodb\_large\_prefix，Row\_format是Compact，表定义的字符集utf8，因为要加索引的字段定义是varchar(500)，允许存储500个字符，utf8的一个字符是3个字节，500个字符就是1500个字节，从文档我们知道，未设置innodb\_large\_prefix，所以键值上限是767个字节，1500>767，加索引的操作，不能执行，

但是更奇怪的，碰巧我在5.6.44小版本进行测试，这个和5.6.22相同的操作过程，竟然能执行，只是提示了warning，

mysql> select version();
    +------------+
    | version()  |
    +------------+
    | 5.6.44-log |
    +------------+
    1 row in set (0.00 sec)
    
    
    mysql> create table t(id varchar(500))
    Query OK, 0 rows affected (0.08 sec)
    
    
    mysql> alter table t add index (id);
    Query OK, 0 rows affected, 1 warning (0.03 sec)
    Records: 0  Duplicates: 0  Warnings: 1
    
    
    mysql> show warnings;
    +---------+------+---------------------------------------------------------+
    | Level   | Code | Message                                                 |
    +---------+------+---------------------------------------------------------+
    | Warning | 1071 | Specified key was too long; max key length is 767 bytes |
    +---------+------+---------------------------------------------------------+
    1 row in set (0.00 sec)

但是通过客户端，能看到这个索引键的长度，限制为了255，按照计算，255\*3=765<767，这个应该是utf8字符集能支持字符个数上限，

![1eaaf148229fbd815bf2d193375e576b.png][]

P.S. 请教一下，如果从命令行，看索引键值长度，应该执行什么？

说明5.6.44对超过索引键值上限的情况，允许增加索引，但是会自动截取。

在5.6.22和5.7对超过索引键值上限的情况，直接禁止执行。

个人理解，对待这种超过索引键值上限的情况，禁止执行，是合理的，因为如果自动对索引前缀进行截取，很可能出现截取的部分无法进行区分，不能起到过滤效果了，即使设置了innodb\_strict\_mode，都是只提示warning，不是错误error，因此很可能5.6.44对这个场景的支持是个bug，或者以后的版本，关闭了这个特性。

如果这个问题在5.6.22下要执行成功，可能有几种方案，

1. 缩小字段长度，例如x varchar(500)，改为x varchar(255)。

2. 创建索引的时候，指定前缀长度，alter table ... add index ... (x(255))，能不能这么做，需要根据字段内容来决定。

3. 开启配置innodb\_large\_prefix，innodb\_file\_format改为Barracuda，row\_format改为Dynamic，限制就从767改为了3072。

P.S. 这几个参数都是全局改的，能不能改，有什么影响，可能还得评估下。

mysql> set global innodb_large_prefix=on;
    Query OK, 0 rows affected (0.00 sec)
    
    
    mysql> show variables like '%prefix%';
    +---------------------+-------+
    | Variable_name       | Value |
    +---------------------+-------+
    | innodb_large_prefix | ON    |
    +---------------------+-------+
    1 row in set (0.00 sec)
    
    
    mysql> set global innodb_file_format=Barracuda;
    Query OK, 0 rows affected (0.01 sec)
    
    
    mysql> show variables like '%innodb_file_format%';
    +--------------------------+-----------+
    | Variable_name            | Value     |
    +--------------------------+-----------+
    | innodb_file_format       | Barracuda |
    | innodb_file_format_check | ON        |
    | innodb_file_format_max   | Antelope  |
    +--------------------------+-----------+
    3 rows in set (0.00 sec)
    
    
    mysql> alter table t1 row_format=dynamic;
    Query OK, 0 rows affected (0.05 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    
    mysql> alter table t1 add index idx_t1_01(id);
    Query OK, 0 rows affected (0.01 sec)
    Records: 0  Duplicates: 0  Warnings: 0

对这个问题，从另一个角度，即使是767字节限制，换算成utf8，或者utf8mb4，至少是255或者191个字符的长度，一个单键值索引，如果达到这长度，就得考虑下索引字段的选择是否合理了，当然如果是几个字段组成的复合索引，达到这个长度，合理不合理，就得实际评估了。

MySQL刚开始接触，从这个案例，能体会到他的参数很多很碎，而且环环相扣，这真是得靠经验积累了。

小白学习MySQL，

《[小白学习MySQL - MySQL会不会受到“高水位”的影响？][MySQL - MySQL]》

《[小白学习MySQL - 数据库软件和初始化安装][MySQL -]》

《[小白学习MySQL - 闲聊聊][MySQL - 1]》

**近期更新的文章：**

《[积累一些SQL][SQL]》

《[创建主键的三种方式对指定索引表空间操作的纠正][Link 1]》

《[Oracle优化器的“短路”][Oracle]》

《[MySQL行转列的小需求][MySQL]》

《[Oracle的greatest和least函数][Oracle_greatest_least]》

《[我的股市生涯][Link 2]》

《[非Oracle Linux下Oracle 19c CDB数据库安装][Oracle Linux_Oracle 19c CDB]》

《[Redis和Sentinel的安装部署和配置][Redis_Sentinel]》

《[“火线”和“零线”][Link 3]》

《[通过索引提升SQL性能案例一则][SQL 1]》

《[如何手动添加jar包到maven本地库？][jar_maven]》

《[1元股权转让的一点思考][1]》

《[如何打造一个经常宕机的业务系统？][Link 4]》

《[Linux恢复误删文件的操作][Linux]》

《[Linux的scp指令使用场景][Linux_scp]》

《[Oracle处理IN的几种方式][Oracle_IN]》

《[如何搭建一支拖垮公司的技术团队？][Link 5]》

《[IP地址解析的规则][IP]》

《[MySQL的skip-grant-tables][MySQL_skip-grant-tables]》

《[国产数据库不平凡的一年][Link 6]》

《[Oracle要求顺序的top数据检索问题][Oracle_top]》

《[日常工作中碰到的几个技术问题][Link 7]》

《[了解一下sqlhc][sqlhc]》

《[Oracle的MD5函数介绍][Oracle_MD5]》

《[Oracle 19c的examples静默安装][Oracle 19c_examples]》

《[sqlplus登录缓慢的解决][sqlplus]》

《[VMWare 11安装RedHat Linux 7过程中碰到的坑][VMWare 11_RedHat Linux 7]》

《[COST值相同？是真是假？][COST]》

《[Oracle 11g的examples静默安装][Oracle 11g_examples]》

**文章分类和索引：**

《[公众号700篇文章分类和索引][700]》

[48a7a47a1229b88882add78fbbed15ac.png]: /images/20221022/3d192cdd31ec4d5f98acc866fed0be92.png
[c556ee26bc5896c2608d5ce846cdc403.png]: /images/20221022/e3cb50a1b5a84c5ab841d5cfa4ca2d57.png
[1eaaf148229fbd815bf2d193375e576b.png]: /images/20221022/403173dee19743c6b3eeffa863b6d31e.png
[MySQL - MySQL]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a485ddf52d936279d52b0e62463f699412cf886e9b51eaf48114e7561dec55ccc21189be&idx=1&mid=2247491958&scene=21&sn=6917dcc719cab7b5537194eed27b4ed2#wechat_redirect
[MySQL -]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a4c8ddf52dde849a70b34f2b62ff16593f81898cca7bc1fd7f0321588823016f0becaa18&idx=1&mid=2247491899&scene=21&sn=7affd185a81761eeaa5c3e143fc6f894#wechat_redirect
[MySQL - 1]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a4dcddf52dcad1708150cc6ceb101423eb5f4326ceff0f9dd53c05103bea76c1c5b7ccbc&idx=1&mid=2247491887&scene=21&sn=91485c182868ef19ad822c3ac9bf2ea9#wechat_redirect
[SQL]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a47cddf52d6a74b2c27077f15c3631b93e90641f229a3333fa8f7829bd7242c07032dcec&idx=1&mid=2247491983&scene=21&sn=4fdcc1c999d116d9d1939ca8a57f9e8a#wechat_redirect
[Link 1]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a475ddf52d63e6f63dcbe1e8e29edec27e7645162d2499f550befafc1686c0704f82f579&idx=1&mid=2247491974&scene=21&sn=614617190c55f9090f5124767a164e21#wechat_redirect
[Oracle]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a48fddf52d9999788838c494741b2d3f73b0b30e9cf3425c63ddfad149739e8ccaaf66d9&idx=1&mid=2247491964&scene=21&sn=01531283836cc00f996bd199bf9dd541#wechat_redirect
[MySQL]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a482ddf52d94fb3b283f9452a162ececef623aa4b86b2daa973729ccfff77af76b834671&idx=1&mid=2247491953&scene=21&sn=ced41f36822ad38a783363553da3df6d#wechat_redirect
[Oracle_greatest_least]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a49addf52d8c587878484b9818e5156867d3d8dace598b0da14a6e7ea2be00d2495e4510&idx=1&mid=2247491945&scene=21&sn=a8263a92dc9a32ac82d96c6a1fca6464#wechat_redirect
[Link 2]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a4aeddf52db865a96331d9a023f535d44393483a2d043c91eb1de4ef0d51c64264eb571e&idx=1&mid=2247491933&scene=21&sn=65fbd01000f8d350592c715f5f7c8779#wechat_redirect
[Oracle Linux_Oracle 19c CDB]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a4a3ddf52db52f85ca508e24fa60fe999ab2b73826fa79f8ea32df5aab88e36e44e267d8&idx=1&mid=2247491920&scene=21&sn=7db77f383482f0b45a094439d9af7dc7#wechat_redirect
[Redis_Sentinel]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a4c3ddf52dd5f4475241b627f5f1cf6aa602d15c94eec40795b600ff95bda09db52e108a&idx=1&mid=2247491888&scene=21&sn=f4133fb5ae9ce0018149e02c2c195b06#wechat_redirect
[Link 3]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a4ebddf52dfda1f8a857c0a1a527c7d7e663640b07ff8a67ad295e8c3218b87647981126&idx=1&mid=2247491864&scene=21&sn=e7cb2840e3ce2bab373f77f63d4b9a9d#wechat_redirect
[SQL 1]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a4fdddf52debb9cc3041f52085038f09b173444ada300999b058dc7ad19a9102fd1b5695&idx=1&mid=2247491854&scene=21&sn=d0f6880089bcbb4fe8ab7a658a460cd0#wechat_redirect
[jar_maven]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a4faddf52dec09683e7b533b56ed0430b99cda91ba3b41df893504d77690f68b8447c68a&idx=1&mid=2247491849&scene=21&sn=d502e38f03e1a222353544d1ce0aaaee#wechat_redirect
[1]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a4f6ddf52de04ccf3ab1219e1be19c9fa6a38cf8e3e74682d1f31b82cb1d8333692c1cce&idx=1&mid=2247491845&scene=21&sn=da9d31fd892da7e76671f0720721847e#wechat_redirect
[Link 4]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a51eddf52c085623fb43d61a2201e44477e04169098dd533076dbdf99377b931d671964d&idx=1&mid=2247491821&scene=21&sn=8169bff24df141481de7dfa370b91909#wechat_redirect
[Linux]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a514ddf52c02eb450efabec94a91e5f5e85ab76653cc4dd9d4cf87e8e15f766f5821f8b7&idx=1&mid=2247491815&scene=21&sn=d3954508c6e9ed2acc10a936aecc92b2#wechat_redirect
[Linux_scp]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a512ddf52c04b580ef998bcf292225635f04c743efee72fcc04fa0ffa8834e8f5351ea2f&idx=1&mid=2247491809&scene=21&sn=4b6e5ad829253bb6522f97c241f19d37#wechat_redirect
[Oracle_IN]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a528ddf52c3ed5a5613456abcaad5af328c5e8c59ed2fcf3a8ae21f0e87f2705545c80b6&idx=1&mid=2247491803&scene=21&sn=d40ee2f3334435f933b881e43389b716#wechat_redirect
[Link 5]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a527ddf52c310590250aea3a7f50e6ef0f3239a097a68a2f5506bae22bc618ce000e3440&idx=1&mid=2247491796&scene=21&sn=3fedb602e4d5acf03fc5460f288bf43c#wechat_redirect
[IP]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a53fddf52c29969fa924ed4644ebfabb1ce3d117443134060e4f7cc8f37e8177f5338700&idx=1&mid=2247491788&scene=21&sn=2790176fc4bd58abb337fd0ce786a190#wechat_redirect
[MySQL_skip-grant-tables]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a535ddf52c23f84ef003cdbadc868463c274971da2b6c79b5fe78660fc013b696c4f4339&idx=1&mid=2247491782&scene=21&sn=40c079eb9f84430e163ead1ee7b9fc85#wechat_redirect
[Link 6]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a549ddf52c5fb741cde2dad195f245871b0d7226a612ca326524d5af0968d674b64f0ac8&idx=1&mid=2247491770&scene=21&sn=d706bcda7de477177a15b14f64c99517#wechat_redirect
[Oracle_top]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a541ddf52c572d7c9c90b93b9f37674c07761a26b7b2566302f24fcb86636cae4e1c4cb8&idx=1&mid=2247491762&scene=21&sn=e92bdd8616515e6403c3ae3280142373#wechat_redirect
[Link 7]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a559ddf52c4f288da18bb70439b1e213a90f0587fdf2cd1d00c948f33f35be40de33bd05&idx=1&mid=2247491754&scene=21&sn=95533b540fe5809c9516468ad2f58c3e#wechat_redirect
[sqlhc]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a555ddf52c43093e82ff00b137c136c5aade5e902f84b523e609429a2de7bfacbd18ad67&idx=1&mid=2247491750&scene=21&sn=6ef9cc04a1a88217277d96072aa86fe2#wechat_redirect
[Oracle_MD5]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a57addf52c6c9d61af3dcf4f1ec9f6b93e51e6fd2d606c36fb8a243d70562c14cc525554&idx=1&mid=2247491721&scene=21&sn=8bdb65a2e11ef287f48d3d213642e4a8#wechat_redirect
[Oracle 19c_examples]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a588ddf52c9e16329e277f2dfdc74734b91bfde0770c545933fca6fb4d7cb224825169c4&idx=1&mid=2247491707&scene=21&sn=569f50f91b58c7cbcb652089e420ea11#wechat_redirect
[sqlplus]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a582ddf52c949d97894f8a2defe4b7fc21d58defa73dcae13f1cfe56d0cec41935b81c88&idx=1&mid=2247491697&scene=21&sn=ecb0e5cc2dc189be23279649b6aee945#wechat_redirect
[VMWare 11_RedHat Linux 7]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a593ddf52c85d1c3e8c3a8767e05f32dd38b61f3da14f3f8fd5521b60a6ca6fa081878c0&idx=1&mid=2247491680&scene=21&sn=27acee6fbe440c573488b4155314944d#wechat_redirect
[COST]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a5afddf52cb9bd94da2c5abd6f5bdcefad9035eacccf0fb96db79ff09ec8c38ae9f1941a&idx=1&mid=2247491676&scene=21&sn=4c9c289836ef2d1774136a88705960c7#wechat_redirect
[Oracle 11g_examples]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a5ccddf52cda2812771d563eea1db4c3b66f9891869920171bbe5650a45a2865bde0100e&idx=1&mid=2247491647&scene=21&sn=eca45fd1d7dffbf91ab199198adc1591#wechat_redirect
[700]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a5dfddf52cc949a5b8b316ee905927ff399713b34aec7d806c656e8ada09bfb562c039ca&idx=1&mid=2247491628&scene=21&sn=83bd320bd28da84501fada2e8d515e77#wechat_redirect