mysql 联合索引 too long 错误

男娘i 2022-06-14 10:27 127阅读 0赞

If you’ve ever tried to add an index that includes a long varchar column to an InnoDB table in MySQL, you may have seen this error:

The character limit depends on the character set you use. For example if you use `latin1` then the largest column you can index is `varchar(767)` , but if you use `utf8` then the limit is`varchar(255)` . There is also a separate 3072 byte limit per index. The 767 byte limit is per column, so you can include multiple columns (each 767 bytes or smaller) up to 3072 total bytes per index, but no column longer than 767 bytes. (MyISAM is a little different. It has a 1000 byte index length limit, but no separate column length limit within that).

One workaround for these limits is to only index a prefix of the longer columns, but what if you want to index more than 767 bytes of a column in InnoDB?

In that case you should consider using [`innodb_large_prefix` ][innodb_large_prefix], which was introduced in MySQL 5.5.14 and allows you to include columns up to 3072 bytes long in InnoDB indexes. It does not affect the index limit, which is still 3072 bytes as quoted in [the manual][] :

The InnoDB internal maximum key length is 3500 bytes, but MySQL itself restricts this to 3072 bytes. This limit applies to the length of the combined index key in a multi-column index.

Read on for details and examples about [`innodb_large_prefix` ][innodb_large_prefix].

Here are a few pre-requisites for using [`innodb_large_prefix` ][innodb_large_prefix]:

*  At the database level you have to use [`innodb_file_format=BARRACUDA`][innodb_file_format_BARRACUDA]
 *  At the table level you have to use [`ROW_FORMAT=DYNAMIC` or `ROW_FORMAT=COMPRESSED`][ROW_FORMAT_DYNAMIC_or_ROW_FORMAT_COMPRESSED]

The default file format is still Antelope for backwards compatibility, and the default row format is COMPACT.

You can set both `innodb_file_format` and `innodb_large_prefix` dynamically, but you should also set them in my.cnf so they survive a restart.

Here’s an example. If I try to create this table with `innodb_large_prefix` disabled I get an error:

If I enable `innodb_large_prefix` I can create the table successfully:

The examples are similar for `latin1` , but I can use columns three times as long since it’s a single-byte character set.

<table style="max-width:100%; border-spacing:0px; width:682px; margin-bottom:1.5em; border-width:1px 1px 1px 0px; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-top-color:rgb(221,221,221); border-right-color:rgb(221,221,221); border-bottom-color:rgb(221,221,221); font-size:14px; line-height:1.3em; background-color:transparent"> 
 <tbody> 
 <tr style="line-height:1.3em"> 
 <td style="padding:8px; line-height:1.3em; vertical-align:top; border-top-width:0px; border-left-width:1px; border-left-style:solid; border-left-color:rgb(221,221,221); font-size:14px"> &nbsp;</td> 
 <td style="padding:8px; line-height:1.3em; vertical-align:top; border-top-width:0px; border-left-width:1px; border-left-style:solid; border-left-color:rgb(221,221,221); font-size:14px"> <pre style="padding:0.5em; font-family:Menlo,Monaco,Consolas,'Courier New',monospace; color:rgb(68,68,68); margin-top:0px; line-height:1.5em; word-break:break-all; word-wrap:break-word; border:none; overflow-x:auto; margin-bottom:0px!important; background-color:rgb(246,246,246)"><code style="padding:0px; font-family:Menlo,Monaco,Consolas,'Courier New',monospace; color:inherit; border:0px; white-space:pre-wrap; font-weight:600; background-color:transparent">mysql&gt; create table if not exists latin1_test ( -&gt; day date not null, -&gt; product_id int not null, -&gt; dimension1 varchar(1500) not null, -&gt; dimension2 varchar(1500) not null, -&gt; unique index unique_index (day, product_id, dimension1, dimension2) -&gt; ) ENGINE=InnoDB DEFAULT CHARSET=latin1; ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes mysql&gt; set global innodb_file_format = BARRACUDA; Query OK, 0 rows affected (0.00 sec) mysql&gt; set global innodb_large_prefix = ON; Query OK, 0 rows affected (0.00 sec) mysql&gt; create table if not exists latin1_test ( -&gt; day date not null, -&gt; product_id int not null, -&gt; dimension1 varchar(1500) not null, -&gt; dimension2 varchar(1500) not null, -&gt; unique index unique_index (day, product_id, dimension1, dimension2) -&gt; ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC; Query OK, 0 rows affected (0.02 sec)</code></pre> </td> 
 </tr> 
 </tbody> 
</table>

And here’s what happens if I try to create an index longer than 3072 bytes:

[innodb_large_prefix]: http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_large_prefix
[the manual]: http://dev.mysql.com/doc/refman/5.6/en/innodb-restrictions.html
[innodb_file_format_BARRACUDA]: http://dev.mysql.com/doc/innodb/1.1/en/innodb-other-changes-file-formats.html
[ROW_FORMAT_DYNAMIC_or_ROW_FORMAT_COMPRESSED]: http://dev.mysql.com/doc/refman/5.6/en/innodb-row-format-dynamic.html