Shulou(Shulou.com)06/01 Report--

This article mainly introduces how to do if the Mysql index is too long. I hope I can supplement and update some knowledge for you. If you have any other questions you need to know, you can continue to follow my updated article in the industry information.

Mysql index is too long 1071-max key length is 767 byte

problem

Create table: Specified key was too long; max key length is 767 bytes

Reason

The database table is encoded by utf8, in which the column of varchar (255) has a unique key index.

By default, the index of a single column in mysql cannot exceed 767bits (there may be differences between different versions)

Therefore, under utf8 character encoding, 255cm 3 byte exceeds the limit.

Solve

1 using the innodb engine

2 enable the innodb_large_prefix option to extend the constraint to 3072byte

3 recreate the database

My.cnf configuration:

Default-storage-engine=INNODB

Innodb_large_prefix=on

In general, it is not recommended to use such a long index, which has a certain impact on performance.

This is the solution of the article on the Internet, but I did not modify it successfully.

Below I refer to some other articles and combine my own actions to determine what the problem is.

Some students asked about the index length of InnoDB, and I would like to say a few tips briefly.

With regard to 3072, we often encounter that the length of an InnoDB single-column index cannot exceed 767bytes. In fact, a federated index has a limit of 3072.

Sql Code Collection Code

Mysql > CREATE TABLE tb (

-> a varchar (255) DEFAULT NULL

-> b varchar (255) DEFAULT NULL

-> c varchar (255) DEFAULT NULL

-> d varchar (255) DEFAULT NULL

-> e varchar (255) DEFAULT NULL

-> KEY a (a _ r _ b _ r c _ r _ d _ e)

->) ENGINE=InnoDB DEFAULT CHARSET=utf8

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

As you can see, because each field occupies 255-inch 3, the size of the index is 3825 > 3072, with an error.

Why 3072?

We know that the default size of an InnoDB page is 16k. Because it is a Btree organization, a page on the leaf node is required to contain at least two records (otherwise the linked list will be degraded). So a record cannot exceed 8k at most. And because of InnoDB's clustered index structure, a secondary index should contain a primary key index, so each single index cannot exceed 4k (in extreme cases, competition and a secondary 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

The single-column index limit 767 is mentioned above, which is caused by 256 × 3-1. This 3 is the maximum character footprint (utf8). But after 5. 5, 4-byte uutf8 is supported. 255 × 4 > 767, so add a parameter called innodb_large_prefix. The default value of this parameter is OFF. When changed to ON, the maximum column index allowed is 3072. * * I made reference to the above article to make sure that the maximum 3072 is OK, so let's find a way to make it 3072. * I referred to another article and finally got some idea.

Create a table with a large varchar field, and index it, and find that MySQL reported an error:

ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.

The following is the construction table statement:

Create table piratebay (

SYS_ID int

FILE_NAME VARCHAR (200)

FILE_ID VARCHAR (30)

NUM1 VARCHAR (30)

NUM2 VARCHAR (30)

MAGNET_LINK VARCHAR (500)

PRIMARY KEY (sys_id)

KEY piratebay_n1 (FILE_NAME))

Engine=innodb

MySQL environment configuration:

Server version: 5.6.28-log MySQL Community Server (GPL)

Server characterset: utf8mb4

Db characterset: utf8mb4

Solution:

(1) View the relevant configuration and make the following settings

Innodb_large_prefix = ON

Innodb_file_format = Barracuda

Innodb_file_per_table = ON

(2) modify the table creation statement and add row_format=DYNAMIC

Create table piratebay (

SYS_ID int

FILE_NAME VARCHAR (200)

FILE_ID VARCHAR (30)

NUM1 VARCHAR (30)

NUM2 VARCHAR (30)

MAGNET_LINK VARCHAR (500)

PRIMARY KEY (sys_id)

KEY piratebay_n1 (FILE_NAME))

Engine=innodb row_format=dynamic

Reason:

MySQL index only supports 767 bytes, utf8mb4 each character occupies 4 bytes, so the maximum length of the index can only be 191 characters, that is, varchar (191), if you want to use larger fields, mysql needs to be set to support data compression, and modify the table attribute row_format = {DYNAMIC | COMPRESSED} you see, change the row_formatl type to these two modes.

Here is the process chart I made:

You can see the row_formatl type. Modify the type below.

CREATE TABLE test2 (id int (11) NOT NULL AUTO_INCREMENT, date varchar (25) DEFAULT NULL, sess_id varchar (25) DEFAULT NULL, keyword varchar (25) NOT NULL, url_n varchar (3) DEFAULT NULL, Signorn varchar (3) DEFAULT NULL, select_url varchar (25) DEFAULT NULL, UNIQUE KEY (id,keyword) ENGINE=innodb DEFAULT row_format=dynamic

Read the above about the Mysql index is too long how to do, I hope it can give you some help in practical application. Due to the limited space in this article, it is inevitable that there will be deficiencies and need to be supplemented. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.