Using innodb_large_prefix to avoid ERROR #1071,Specified key was too long; max key length is 1000 bytes


widgets Article

Using innodb_large_prefix to avoid ERROR 1071

       Single row index limit
above mentioned restrictions separate index 767, the cause is 256 × 3-1. This 3-character maximum space (utf8). But after 5.5, 4-byte start supports uutf8.255 × 4> 767, thus increasing a parameter called the innodb_large_prefix.
This parameter default is OFF. When to ON, if allowed to reach the maximum 3072 column index.
       3072 joint index
We know that InnoDB the default size of a page is 16k. Because it is Btree organization, it requires a page on the leaf node contains at least two records (otherwise degraded the list).
So a record maximum of 8k.
Also, because of the clustered index InnoDB structure, comprising a secondary index to the primary key index, each single index can not exceed 4K (extreme case, pk, and a secondary index has reached this limit).
Due to the need to reserve and auxiliary space, after deducting not more than 3500, to take a "integer" is (1024 * 3).
Referring specifically to Dinc Share:  http://www.mysqlops.com/2012/09/ ... 6%E7%9A%84tips.html
 

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:

 
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

The character limit depends on the character set you use. For example if you uselatin1 then the largest column you can index is varchar(767) , but if you useutf8 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 , 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 inthe 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 .

Here are a few pre-requisites for using innodb_large_prefix :

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_prefixdynamically, but you should also set them in my.cnf so they survive a restart.  innodb_file_format The default value Antelope,   innodb_large_prefixdynamically default is OFF.

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

 
mysql> create table if not exists utf8_test (
    ->   day date not null,
    ->   product_id int not null,
    ->   dimension1 varchar(500) character set utf8 collate utf8_bin not null,
    ->   dimension2 varchar(500) character set utf8 collate utf8_bin not null,
    ->   unique index unique_index (day, product_id, dimension1, dimension2)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

If I enable innodb_large_prefix I can create the table successfully:

 
mysql> set global innodb_file_format = BARRACUDA;
Query OK, 0 rows affected (0.00 sec)

mysql> set global innodb_large_prefix = ON;
Query OK, 0 rows affected (0.00 sec)

mysql> create table if not exists utf8_test (
    ->   day date not null,
    ->   product_id int not null,
    ->   dimension1 varchar(500) character set utf8 collate utf8_bin not null,
    ->   dimension2 varchar(500) character set utf8 collate utf8_bin not null,
    ->   unique index unique_index (day, product_id, dimension1, dimension2)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.02 sec)

/Etc/my.cnf added in two rows, the effect is the same: innodb_file_format = Barracuda, innodb_large_prefix = ON

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

 
mysql> create table if not exists latin1_test (
    ->   day date not null,
    ->   product_id int not null,
    ->   dimension1 varchar(1500) not null,
    ->   dimension2 varchar(1500) not null,
    ->   unique index unique_index (day, product_id, dimension1, dimension2)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

mysql> set global innodb_file_format = BARRACUDA;
Query OK, 0 rows affected (0.00 sec)

mysql> set global innodb_large_prefix = ON;
Query OK, 0 rows affected (0.00 sec)

mysql> create table if not exists latin1_test (
    ->   day date not null,
    ->   product_id int not null,
    ->   dimension1 varchar(1500) not null,
    ->   dimension2 varchar(1500) not null,
    ->   unique index unique_index (day, product_id, dimension1, dimension2)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.02 sec)

And here’s what happens if I try to create an index longer than 3072 bytes, 1500*3=4500>3072:

 
mysql> create table if not exists long_index_test (
    ->   day date not null,
    ->   product_id int not null,
    ->   dimension1 varchar(1500) not null,
    ->   dimension2 varchar(1500) not null,
    ->   dimension3 varchar(1500) not null,
    ->   unique index unique_index (day, product_id, dimension1, dimension2, dimension3)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes

As long as each row of each column is not greater than 3072 will not be given:

create table if not exists tmp1 (
     x0 varchar(3072) not null,
     x1 varchar(3072) not null,
     x2 varchar(3072) not null,
     x3 varchar(3072) not null,
     unique index unique_index (x3)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

Query OK, 0 rows affected (0.23 sec)

There is a column that is being given over 3072:

mysql> create table if not exists tmp1 (
    ->      x3 varchar(3073) not null,
    ->      unique index unique_index (x3)
    ->     ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes

 

 

 

 

Answer on stackoverflow:

Using DYNAMIC or COMPRESSED means that InnoDB stores varchar/text/blob fields that don't fit in the page completely off-page. But other than those columns, which then only count 20 bytes per column, the InnoDB row size limit has not changed; it's still limited to about 8000 bytes per row.

InnoDB only supports indexes of 767 bytes per column. You can raise this 3072 bytes by setting innodb_large_prefix=1 and using either DYNAMIC or COMPRESSED row format.

Using COMPRESSED row format does not make InnoDB support longer indexes.

Regarding performance, this is one of those cases where "it depends." Compression is generally a tradeoff between storage size and CPU load to compress and uncompress. It's true that this takes a bit more CPU to work with compressed data, but you have to keep in mind that database servers are typically waiting for I/O and have CPU resources to spare.

But not always -- if you do complex queries against data that is in the buffer pool, you may be constrained by CPU more than I/O. So it depends on many factors, like how well your data fits in RAM, the type of queries you run and how many queries per second, as well as hardware specs. Too many factors for anyone else to be able to answer for your application on your server. You'll just have to test it.


Re your comment:

One possibility is that the index is not fitting in the buffer pool. Performance degrades significantly if an index search needs to load pages and evict pages during every SELECT query. An EXPLAIN analysis can't tell you whether the index fits in the buffer pool.

I don't know how many columns or what data types of the columns in your index, but if you are indexing long varchar columns you should consider using prefix indexes (or decreasing the length of the columns).

You could also get more RAM and increase the size of the buffer pool.

 

 

 

 

Official doc 5.6

Limits on InnoDB Tables

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.

http://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html


The MyISAM Storage Engine

The maximum key length is 1000 bytes. This can also be changed by changing the source and recompiling. For the case of a key longer than 250 bytes, a larger key block size than the default of 1024 bytes is used.

http://dev.mysql.com/doc/refman/5.7/en/myisam-storage-engine.html


However your table is declared as InnoDB. So I don't know what to think

There's also a clue at the end of this old bug

If you need this you should really look at MySQL 5.5 and the innodb_large_prefix option that is available from 5.5.14 (July 2011) onwards because it probably does what you are looking for:

"Enable this option to allow index key prefixes longer than 767 bytes (up to 3072 bytes), for InnoDB tables that use the DYNAMIC and COMPRESSED row formats. (Creating such tables also requires the option values innodb_file_format=barracuda and innodb_file_per_table=true.) See Section 13.3.15, “Limits on InnoDB Tables” for the relevant maximums associated with index key prefixes under various settings.

 

 

 

 

 

Enabling Compression for a Table

Before creating a compressed table, make sure the innodb_file_per_table configuration option is enabled, and innodb_file_format is set to Barracuda. You can set these parameters in the MySQL configuration file my.cnf or my.ini, or with theSET statement without shutting down the MySQL server.

To enable compression for a table, you use the clauses ROW_FORMAT=COMPRESSEDKEY_BLOCK_SIZE, or both in a CREATE TABLE or ALTER TABLE statement.

To create a compressed table, you might use statements like these:

SET GLOBAL innodb_file_per_table=1;
SET GLOBAL innodb_file_format=Barracuda;
CREATE TABLE t1
 (c1 INT PRIMARY KEY) 
 ROW_FORMAT=COMPRESSED 
 KEY_BLOCK_SIZE=8;
  • If you specify ROW_FORMAT=COMPRESSED, you can omit KEY_BLOCK_SIZE; the default compressed page size of 8KB is used.

  • If you specify KEY_BLOCK_SIZE, you can omit ROW_FORMAT=COMPRESSED; compression is enabled automatically.

  • To determine the best value for KEY_BLOCK_SIZE, typically you create several copies of the same table with different values for this clause, then measure the size of the resulting .ibd files and see how well each performs with a realistic workload.

  • For additional performance-related configuration options, see Section 14.12.3, “Tuning Compression for InnoDB Tables”.

The default uncompressed size of InnoDB data pages is 16KB. Depending on the combination of option values, MySQL uses a page size of 1KB, 2KB, 4KB, 8KB, or 16KB for the .ibd file of the table. The actual compression algorithm is not affected by theKEY_BLOCK_SIZE value; the value determines how large each compressed chunk is, which in turn affects how many rows can be packed into each compressed page.

Setting KEY_BLOCK_SIZE=16 typically does not result in much compression, since the normal InnoDB page size is 16KB. This setting may still be useful for tables with many long BLOBVARCHAR or TEXT columns, because such values often do compress well, and might therefore require fewer overflow pages as described in Section 14.12.5, “How Compression Works for InnoDB Tables”.

All indexes of a table (including the clustered index) are compressed using the same page size, as specified in the CREATE TABLE or ALTER TABLE statement. Table attributes such as ROW_FORMAT and KEY_BLOCK_SIZE are not part of the CREATE INDEXsyntax, and are ignored if they are specified (although you see them in the output of the SHOW CREATE TABLE statement).

Restrictions on Compressed Tables

Because MySQL versions prior to 5.1 cannot process compressed tables, using compression requires specifying the configuration parameter innodb_file_format=Barracuda, to avoid accidentally introducing compatibility issues.

Table compression is also not available for the InnoDB system tablespace. The system tablespace (space 0, the ibdata* files) can contain user data, but it also contains internal system information, and therefore is never compressed. Thus, compression applies only to tables (and indexes) stored in their own tablespaces, that is, created with the innodb_file_per_table option enabled.

Compression applies to an entire table and all its associated indexes, not to individual rows, despite the clause name ROW_FORMAT.

 

 

MyISAM and InnoDB row format ROW_FORMAT

MyISAM row storage

MyISAM there 3 on seed row storage format: fixed/dynamic/compressed ;

Wherein fixed to the default format, only when the table does not include variable-length field (varchar/varbinary/blob/text) ) use, the each row is fixed, it is easy to obtain a specific location on the page row, access efficiency comparison high, but take up more disk space;

dynamic

Each line has a line head, comprising bitmap , for recording that is empty (NULL column is not empty ) ;

Compared to fixed , which has the following characteristics:

All string columns are dynamic memory, unless the length is less than 4 ;

If the length of the character type 0/ numeric type 0 will not take up storage space, the bitmap label, NULL values are not included;

If you want to update the line, it can easily lead to the expansion of the line linking subsequently fragmented, once the crash if the link is lost is more difficult to recover, fixed mode update does not produce debris;

 

compressed only by myisampack created and is read-only;

 

MyISAM index file contains a flag whether the base table record normally closed, if mysqld start specifies --myisam-recover-options , detect and automatically fix the table when the table is opened

 

 

 

InnoDB row storage

Innodb plugin newly introduced Barracuda pike, comprising compressed/dynamic two kinds row format, but before the compact/redundant system belonging antelope antelope;

 

Barracuda VS antelope

By the innodb_file_format( dynamic ) parameters determine the current value of the optional Antelope and Barracuda , the default is the former; to want this parameter to take effect,

Because the shared table space by default Antelope , so in order to use the Barracuda default values, you must also declare innodb_file_per_table ;

Innodb_file_format for controlling row format, global variables can be dynamically adjusted, 5.5 default still Antelope ;

 

Look below antelope format

 

Redundantline structure

Field length offset list

Information recording head

Column 1data

Column 2Data

….

 

Row header offset field length information, including variable and non-variable length, but also contains three hidden columns: RowID (when not using the primary key) / Transaction ID / Roll Point; containing only the compact becomes longer, saving space;

Redundancy row format is not NULL flag; for redundant format, varchar does not occupy space when Null, but the charis NULLneeds space , because it is not Null flag;

The recording head 6 bytes of information, more than one multi-byte compact;

For fixed-length char, if space is not still fill the entire field is NULL, and is varchar Null;

Recording header information, compared with the Compact, more boldface, deletion record_type

name

The length of the bit

Features

Deleted_flag

1

It has been deleted

Min_rec_flag

1

1 represents a minimum record the record previously defined

N_owned

4

The total number of records owned by the record

Heap_no

13

Sort records the index of the row

N_fields

10

Record the number of columns

1byte_offs_flag

1

List offset is 1 byte or 2 bytes

Next_recorder

16

The relative position of the next record

()

1

unknown

()

1

unknown

 

Create table test(t1 varchar(10), t2 varchar(10), t3 char(10),t4 varchar(10)) charset=latin1 row_format=redundant;

- The table has three columns variable length

Insert into test values(‘a’,’bb’,’bb’,’ccc’);

Use hexdump -C -v test.idb view its binary code

- the length of the offset list,

 

 

 

compactrow format

Field length offset list

NULLflag

Information recording head

Column 1data

Column 2Data

….

 

5.0 introduces

Storing the first line of variable length fields in the row length, 1 byte when the column is less than 255 bytes, greater than 255 and less than 2 bytes 65535; varchar therefore power maximum length of 16 to -12;

The second indicates whether the line has a NULL value, occupies 1 byte; NULL column data do not take up storage space;

Information recording head: 5 bytes Total 40bit, for linking adjacent recording text row-level locks

name

The length of the bit

Features

Deleted_flag

1

It has been deleted

Min_rec_flag

1

1 represents a minimum record the record previously defined

N_owned

4

The total number of records owned by the record

Heap_no

13

Sort records the index of the row

Record_type

3

Line Type  0 = Normal 1 = B + Node Pointer

Next_recorder

16

The relative position of the next record

()

1

unknown

()

1

unknown

 

In addition, there are two hidden fields per page:

DB_TRX_ID : 6 bytes, a record of recent transaction identifier

DB_ROLL_ID : 7 bytes, the rollback point logging

-- if no primary key, there will DB_ROW_ID : 6 bytes, contained in the clustered index

Create a table format of a compact row

Create table test(t1 varchar(10), t2 varchar(10), t3 char(10),t4 varchar(10)) row_format=compact;

- The table has three columns variable length

Insert into test values(‘a’,’bb’,’bb’,’ccc’);

Use hexdump -C -v test.idb view its binary code

first row

030201- variable-length field length list (reverse), the actual order of 010,203, which is t1, the actual length t2, t4 of

00-Null flag, the first row is not NULL

00 00 10 00 2c- the recording header information, byte 5, byte 4 points to a record next_recorder

00 00 00 2b 68 00-6 byte rowid, because there is no primary key

000000000605-- transaction ID, 6 bytes

80 000000320110- rollback pointer, 7 bytes

61 - Column 1

6262-- column 2

62 is 6,220,202,020,202,020 20 is - the column 3, char fills the remainder of the

636363-- column 4

 

The remaining columns of data, wherein t3 due to the fixed length, it will be filled up with 10 bytes;

second line

Insert into test values(‘d’,null,null,’fff’);

0301-- variable-length field length of the list, reverse

06-- Null flag, there is a NULL value, converted to binary 00000110, expressed as 2/3 null

……

64- Column 1 data

666666- 4 columns of data, and the first 2/3as NULLdoes not take up storage space

Note: For redundant format, varchar also does not take up space when Null, but take up space char is NULL, because it is not Null flag

 

 

Row-Overflow

Innodb table IOT , using a B+ number of types, so each page to be stored at least 2 rows of data, if the line is too large will produce an overflow line;

Theoretically mysql the varchar can store 65525 bytes, stronger than the oracle 's 4000 , but InnoDB its practical upper limit of 65532 , and the value form All varchar column sums length; for utf8 character set, a character representing 3 bytes, and the upper limit is reduced to 1/3 ;

If the force created varchar(65535) field in sql_mode is not restricted in a case, which will be converted implicitly to mediumtext ;

 

Whether varchar or blob/text , as long as a 16k page can only put 2 rows of data, the line should not overflow;

Once overflow line, the former field 768 bytes is still stored in the current page, the data is generally used B-tree Node page, and stored in the overflow line Uncompress Blob pages;

 

The barracuda using the overflow line completely, i.e. keeping only the first field is 20 bytes;

 

 

 

 

posted on 2016-08-09 21:22  xxxxxxxx1x2xxxxxxx   read ( ... ) Comments ( ...edit   collections

widgets Related Articles

widgets Contribution

This article is contributed by CNB and text available under CC-SA-4.0