Use an instance to understand the usage of the MySQL prefix index.

Use an instance to understand the usage of the MySQL prefix index.

A warning was reported when I added an index in the test environment today.

root@test 07:57:52>alter table article drop index ind_article_url;Query OK, 144384 rows affected (16.29 sec)Records: 144384 Duplicates: 0 Warnings: 0root@test 07:58:40>alter table article add index ind_article_url(url);Query OK, 144384 rows affected, 1 warning (19.52 sec)Records: 144384 Duplicates: 0 Warnings: 0root@test 07:59:23>show warnings;+———+——+———————————————————+| Level  | Code | Message                         |+———+——+———————————————————+| Warning | 1071 | Specified key was too long; max key length is 767 bytes |+———+——+———————————————————+1 row in set (0.00 sec)

Use show create table article to view the index and table structure information:

'Url' varchar (512) default NULL comment' external link url ',...... KEY 'ind _ article_url '('url' (383 ))..... Default charset = gbk ...... Drop table test; create table test (test varchar (767) primary key) charset = latin5;

-Successful
Not tested next, in different character sets:

drop table test;create table test(test varchar(768) primary key)charset=latin5;

-Error
-

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytesdrop table test;create table test(test varchar(383) primary key)charset=GBK;

-Successful

drop table test;create table test(test varchar(384) primary key)charset=GBK;

-Error
-

 ERROR 1071 (42000): Specified key was too long; max key length is 767 bytesdrop table test;create table test(test varchar(255) primary key)charset=UTF8;

-Successful

drop table test;create table test(test varchar(256) primary key)charset=UTF8;

-Error
-

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

MySQL varchar indexes only support fields of no more than 768 bytes or 768/2 = 384 double bytes or 768/3 = 256 three bytes.
While GBK is double byte, UTF-8 is three byte.
The reason for the above is that my character set is GBK dual-byte, And the url is 512 characters and 1024 bytes. Therefore, a warning is reported when the string index limit is exceeded, mysql creates a 383 (766 bytes) prefix index by default.
We know that small indexes not only play a major role in space storage, memory reduction, and performance improvement, we need to make a wise choice when calculating the length of prefix indexes, how wise?
Full index column selection:

root@test 08:10:35>select count(distinct(url))/count(*) from article;+——————————-+| count(distinct(url))/count(*) |+——————————-+|            0.0750 |+——————————-+

The prefix columns of various lengths are calculated selectively:

root@test 08:16:41>select count(distinct left(url,76))/count(*) url_76,-> count(distinct left(url,77))/count(*) url_77,-> count(distinct left(url,78))/count(*) url_78,-> count(distinct left(url,79))/count(*) url_79,-> count(distinct left(url,80))/count(*) url_80,-> count(distinct left(url,81))/count(*) url_81,-> count(distinct left(url,82))/count(*) url_82,-> count(distinct left(url,83))/count(*) url_83,-> count(distinct left(url,84))/count(*) url_84,-> count(distinct left(url,85))/count(*) url_85-> from article;+——–+——–+——–+——–+——–+——–+——–+——–+——–+——–+| url_76 | url_77 | url_78 | url_79 | url_80 | url_81 | url_82 | url_83 | url_84 | url_85 |+——–+——–+——–+——–+——–+——–+——–+——–+——–+——–+| 0.0747 | 0.0748 | 0.0749 | 0.0749 | 0.0749 | 0.0749 | 0.0749 | 0.0749 | 0.0749 | 0.0750 |+——–+——–+——–+——–+——–+——–+——–+——–+——–+——–+1 row in set (1.82 sec)

We can see that when the length of 85 is selected, the prefix column's selectivity is equivalent to the full column's selectivity:
Alter table article add index ind_article_url (url (85) without selecting 383 bytes as the prefix;
However, the prefix index is still insufficient, that is, in the query statement, order by and group by cannot use the prefix index.

root@test 08:49:24>explain select id,url,deleted from article group by url;+—-+————-+————-+——+—————+——+———+——+——–+———————————+| id | select_type | table    | type | possible_keys | key | key_len | ref | rows  | Extra              |+—-+————-+————-+——+—————+——+———+——+——–+———————————+| 1 | SIMPLE   | article | ALL | NULL     | NULL | NULL  | NULL | 139844 | Using temporary; Using filesort |+—-+————-+————-+——+—————+——+———+——+——–+———————————+1 row in set (0.00 sec);