Mysql index length tips innodb and myisam Engine

The mysql index length tips innodb and myisam engines fail to create tables in the production environment due to issues such as the developer's lack of understanding or neglect of indexes and different versions. Summarized the 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 DEFAUL T charset = utf8; ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes innodb Single Column index length cannot exceed 767 bytes, and the Union Index Limit is 3072 bytes. In the composite index for creating innodb, if the length of each column exceeds 767 bytes, the creation fails. The myisam engine creates 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 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. We can see that the length of the index in a single column of myisam, 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 1 character 2 bytes for extension: the length of the innodb composite index is 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 ). The single-column index limit is 767, which is caused by 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 ):