MySQL InnoDB Engine index length is limited what to do?

It should be known that the InnoDB Single-column index cannot exceed 767bytes, and a federated index has a limit of not more than 3072 length.

2. mysql> CREATE TABLE ' TB ' (
4. ' A ' varchar (255) DEFAULT NULL,
6. ' B ' varchar (255) DEFAULT NULL,
8. ' C ' varchar (255) DEFAULT NULL,
10. ' d ' varchar (255) DEFAULT NULL,
12. ' E ' varchar (255) DEFAULT NULL,
14. - KEY ' A ' (' A ', ' B ', ' C ', ' d ', ' e ')
16. )  Engine=innodb DEFAULT Charset=utf8;
18. ERROR 1071 (42000): Specified Key was too long; Max key length is 3072 bytes

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

Why 3072

We know InnoDB the default size of a page is 16k. Because it is a btree organization, the leaf node is required to have at least two records in the previous page (otherwise degenerate linked list).

So a record cannot exceed 8k at most.

Also due to the clustered index structure of InnoDB, a two-level index must contain a primary key index, so that each individual index cannot exceed 4k (in extreme cases, PK and a level two 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

A single-column index limit of 767 is mentioned above, resulting from 256x3-1. This 3 is the maximum character space (UTF8). However, after 5.5, the support for the 4-byte Uutf8 began. 255x4>767, a parameter called Innodb_large_prefix is added.

The default value for this parameter is off. When on, allows the column index to reach a maximum of 3072.

You can see that the default behavior is to build the table successfully, report a warning, and set the length stage to 255.

Note that you need to add row_format=compressed or dynamic to take effect.

If you do need to create an index on a single large column, or if you need to create a federated index on more than one large column, and you exceed the length limit of the index, the workaround is to limit the size of the index prefix when you build the index:

Example: Create INDEX YARN_APP_RESULT_I4 on Yarn_app_result (flow_exec_id (+), Another_column (50));

This limits the maximum length of each column that is used when the index is created. As in the example above, when you create a federated index, you use up to 100 characters in the column flow_exec_id to create the index, up to the top of the Another_column

50 characters to create an index. This way, you can avoid an issue where the index length is too large.

Finally, I would like to say a word. When designing a database, it is best not to create an index on a column that may contain very long strings, especially if the strings in this column are very long. If you create an index on such a column, you will waste a lot of time on calculations and storage when you create the index and when you query against the index. Experienced designers should not design the database in this way.

Author Profile: Cussaur, interested in high-concurrency system design and development, is now focusing on big data development efforts. served as a background development engineer of Xiaomi Technology company, now serves as Senior development engineer of Everstring data platform Group.

MySQL InnoDB Engine index length is limited what to do?