Mysql index length: tipsinnodb and myisam engine _ MySQL

Mysql index length: tipsinnodb and myisam engine bitsCN.com

Mysql index length tips innodb and myisam engine

The failure to create a table in the production environment has caused some problems because developers do not have a deep understanding of the index or ignore it or have different versions. Summarized

Test Environment

Mysql> select version ();

+ ------------ +

| Version () |

+ ------------ +

| 5.5.31-log |

+ ------------ +

1 row in set (0.01 sec)

Innodb engine

Mysql> create table 'meta _ topic_scan '('domain' varchar (257) not null, 'topic _ name' varchar (200) not null, 'topic _ url' varchar (200) not null, 'topic _ PV' int (11) DEFAULT '0', 'topic _ uv' int (11) DEFAULT '0 ', primary key ('domain ', 'topic _ url') ENGINE = innodb default charset = utf8;

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

The length of a single index in innodb cannot exceed 767 bytes, and the size of a union index is 3072 bytes. In the composite index for creating innodb, if the length of each column exceeds 767 bytes, the creation fails;

Myisam engine

Create a composite index:

Mysql> create table 'meta _ topic_scan '(

-> 'Domain' varchar (200) not null,

-> 'Topic _ name' varchar (200) not null,

-> 'Topic _ url' varchar (200) not null,

-> 'Topic _ PV' int (11) DEFAULT '0 ',

-> 'Topic _ uv' int (11) DEFAULT '0 ',

-> Primary key ('domain ', 'topic _ name', 'topic _ url ')

->) ENGINE = myisam default charset = utf8;

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

Create a single column index:

Mysql> create table 'meta _ topic_scan '(

-> 'Domain' varchar (334) not null,

-> 'Topic _ name' varchar (200) not null,

-> 'Topic _ url' varchar (200) not null,

-> 'Topic _ PV' int (11) DEFAULT '0 ',

-> 'Topic _ uv' int (11) DEFAULT '0 ',

-> Primary key ('domain ')

->) ENGINE = myisam default charset = utf8;

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

Therefore, we can see that the index length of a single myisam column, the length of the created composite index, and the length cannot exceed 1000 bytes. Otherwise, an error is reported and creation fails.

In addition, different character sets occupy different bytes: latin occupies 1 bytes for one character, utf8 stores 3 bytes for one character, and gbk stores 2 bytes for one character

Extended: Why is the length of the innodb composite index 3072?

We know that the default size of a page in InnoDB is 16 kB. Because it is a B-tree organization, a page on a leaf node must contain at least two Records (otherwise, the linked list will be degraded ).

Therefore, a record cannot exceed 8 k at most.

Because of the cluster index structure of InnoDB, a secondary index must contain a primary key index, so each single index cannot exceed 4 k (in extreme cases, both primary keys and a secondary index reach this limit ).

Because the reserved space and auxiliary space are required, the value cannot exceed 3500 after the deduction. The "integer" is (1024*3 ).

Single column index restrictions

The above mentioned single-column index limit is 767, the cause is 256 × 3-1. This 3 is the maximum space occupied by characters (utf8 ). However, after 5.5, four bytes of uutf8 are supported. 255 × 4> 767, so a parameter named innodb_large_prefix is added.

The default value of this parameter is OFF. When you change to ON, the maximum number of column indexes allowed is 3072.

The following results (5.5 ):

BitsCN.com