Analysis of MySQL index length restriction principle

Time:2020-6-18

This article mainly introduces the analysis of MySQL index length restriction principle. The example code is introduced in detail in this article, which has certain reference learning value for your study or work. You can refer to the following for friends who need it

Indexes

Textfield does not support indexing

MySQL has restrictions on the length of index fields

The length of each index column of InnoDB engine is limited to 767 bytes, and the sum of all index columns cannot be greater than 3072 bytes

The length of each index column of MyISAM engine is limited to 1000 bytes, and the sum of all index columns cannot be greater than 1000 bytes

The maximum length of varchar refers to the character length. If the database character set is UTF-8, one character takes up 3 bytes. Therefore, under the UTF-8 character set, the length of a single column index created by the InnoDB engine cannot exceed 255 characters

Different MySQL versions lead to different index length restrictions

In MySQL 5.5, InnoDB is introduced_ large_ Prefix, used to disable large prefix indexing for compatibility with previous versions of InnoDB that do not support large index key prefixes

Enable InnoDB_ large_ Prefix can limit the length of a single index to 3072 bytes (but the total length of a joint index is 3072 bytes). When disabled, the length of a single index is limited to 767 bytes

In MySQL 5.5 and MySQL 5.6, InnoDB_ large_ Prefix is turned off by default, and it is turned on by default in MySQL 5.7 and above

In MySQL 8.0, InnoDB_ large_ Prefix has been removed

This is why I can create an index of 1024 characters (3072 bytes under utf8 character set) on my own machine (MySQL 8.0), but not on the server (MySQL 5.5)

Script to test index length limit:


use test;
drop table if exists test_index_len;
create table 
test_index_len(long_char varchar(1025) primary key) ENGINE=InnoDB charset=utf8;

use test;
drop table if exists test_index_len;
create table 
test_index_len(
  long_char varchar(24),
  origin_str varchar(1000),
  key test_index(long_char, origin_str)) ENGINE=InnoDB charset=utf8;

The above is the whole content of this article. I hope it will help you in your study, and I hope you can support developepaer more.

Recommended Today

Application of tree

Application of tree Storage structure of tree Parental representation (sequential storage) Definition: in each node, the “pointer” (position subscript) pointing to the parents is saved. The root node is fixedly stored in 0, – 1 indicates that there are no parents. Add: add directly without following the logical order. Delete: ① set the pointer to […]