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;