Some limitations of the "MySQL" index length

Some students asked InnoDB index length problem, simply say a few tips.

The length of the index created in each MySQL single table is limited and differs from the table under different storage engines.

• MyISAM table, single-column index, maximum length can not exceed bytes, otherwise it will alarm, but the creation is successful, eventually create a prefix index (take the first 333 characters).
• MyISAM table, combined index, index length and can not exceed bytes, otherwise will error, create failure;
• InnoDB table, single-column index, more than 767 bytes, gives warning, the final index creation succeeds, takes the prefix index (takes the first 255 characters).
• InnoDB table, combined index, the column length does not exceed 767 bytes, if there is more than 767 bytes, the alarm is given, the index was finally created successfully, but for more than 767 bytes of the column prefix index, regardless of the index column order, the sum must not exceed 3072, otherwise failed, cannot be created.

Test:

The authors have actually tested only the tables defined by the MySQL InnoDB engine, the UTF8 character set, MyISAM left to the reader

Version:

To create a new test table:

There are fields greater than 767 bytes in the composite index, generating alarms

Combined with the top two test results, you can see that the sum of the combined index length is greater than 767 bytes has no effect, when there is a field defined length greater than 767 bytes (1000*3), only alarm, but does not affect the creation, the super-long field takes the first 255 characters as a prefix index, and in combination, Does not relate to the order in which the fields appear in the composite index.

As you can see, because each field occupies 255*3, the size of this index is 3825>3072, error.

Why 3072

InnoDB The default size of a page is K. Because it is a btree organization, the leaf node is required to have a page at least two records (otherwise degenerate linked list). So a record cannot exceed 8 K. Also due to the InnoDB clustered index structure, a two-level index must contain a primary key index, so that each individual index cannot exceed 4 K (in extreme cases, PK and a two-level index reach this limit). Due to the need to reserve and auxiliary space, after deduction can not exceed 3500, take an "integer" Is (1024*3).

Single-column index limit

By default, the InnoDB engine single-field index has a maximum length of 767 bytes, and the prefix index has the same limit. When using the UTF-8 character set, each character is stored with 3 bytes, 767=256*3-1, and a problem is encountered when establishing a prefix index of more than 255 characters on a field of TEXT or VARCHAR type. As for why the character length is limited to 256, I guess to improve indexing efficiency, the varchar type needs extra bytes to keep its length information, and 256 to limit it to one byte. However, after 5.5, the support for the 4-byte Uutf8 began. 255x4>767, a parameter called InnoDBlargeprefix was added. The default value for this parameter is off, and when on, allows the column index to reach a maximum of 3072 bytes. Requires the row_format of the table to use compressed or dynamic.

How the main character sets are computed:

• Latin1 = 1 byte = 1 character
• Uft8 = 3 byte = 1 character
• GBK = 2 byte = 1 character

Risks associated with using a prefix index:

The index of the InnoDB restricts the maximum length of a single key to 767 bytes, and a prefix index of less than or equal to 767 bytes must be established over this length. In addition, BLOB and text type columns can only create prefix indexes. The prefix index can improve indexing speed and retrieval speed, but it is not possible to use the prefix index in the following cases:

1. Index Overlay Scan
2. Sort by index (order by, GROUP by)

Or on the test table above:

Author:bill

December 08, 2015

Reference

1. Some limitations of MySQL index length
2. Tips for InnoDB index length limits

Some limitations of the "MySQL" index length