Analysis of MySQL Index Length Limit Principle

  • 2021-12-21 05:18:05
  • OfStack

This article mainly introduces the MySQL index length limit principle analysis, in this article through the example code introduction is very detailed, to everybody's study or the work has 1 fixed reference study value, the need friend may refer to

Index

TextField does not support indexing

MySQL has a limit on the length of index fields

The length of each index column of the innodb engine is limited to 767 bytes (bytes), and the length of all constituent index columns cannot be greater than 3072 bytes

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

The maximum length of an varchar is the character length. If the database character set is utf-8, one character accounts for three bytes. Therefore, under the utf-8 character set, the single-column index created by the innodb engine cannot exceed 255 characters in length

Different mysql versions lead to different index length limits

In MySQL 5.5, innodb_large_prefix was introduced to disable large prefix indexes for compatibility with earlier versions of InnoDB that did not support large key prefixes

Turning on innodb_large_prefix can limit the length of a single index to 3072 bytes (but the total length of the joint index is still 3072 bytes), and when disabled, the length of a single index is limited to 767 bytes

innodb_large_prefix is turned off by default in MySQL 5.5 and MySQL 5.6, and on by default in MySQL 5.7 and above

In MySQL version 8.0, innodb_large_prefix has been removed

This is why I can create an index 1024 characters (3072 bytes under the utf8 character set) on my own machine (MySQL8.0), but not on the server (MySQL5.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;

Related articles: