Using the example to understand the usage of prefix index in Mysql _mysql

A warning was found today when an index was added to the test environment

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:0 root@test 07:58:40>alter
t Able Article add index Ind_article_url (URL);
Query OK, 144384 rows affected, 1 Warning (19.52 sec) records:144384 duplicates:0 warnings:0 root@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 index and table structure information:

' URL ' varchar () default NULL COMMENT ' outer chain url ',
...
KEY ' ind_article_url ' (' url ' (383)
..... DEFAULT CHARSET=GBK ...
drop table test;
CREATE TABLE test (test varchar (767) primary key) Charset=latin5;

– Success
Next not tested, 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 bytes
drop table test;
CREATE TABLE test (test varchar (383) primary key) CHARSET=GBK;

– Success

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 bytes
drop table test;
CREATE TABLE test (test varchar (255) primary key) Charset=utf8;

– Success

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's varchar index only supports fields that are no more than 768 bytes or 768/2=384 or 768/3=256 three-byte
The GBK is Double-byte, and the UTF-8 is three bytes.
So the reason for the above is clear, my character set is GBK for two bytes, and the URL is 512 characters, 1024 bytes, so more than the string index limit, reported a warning, MySQL default created 383 (766 bytes) Length of the prefix index.
We know that small index sizes are important not only for space storage, memory degradation, and performance improvement, so it's wise to choose wisely when calculating the length of the prefix index.
The selectivity of a fully indexed column:

Root@test 08:10:35>select count (Distinct (URL))/count (*) from article;
+ ——————————-+
| count (Distinct (URL))/count (*) |
+ ——————————-+
|            0.0750 |
+ ——————————-+

Compute the selectivity for the prefix columns of various lengths:

 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 ( 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) 

When we see the length of the selection 85, the selectivity of the prefix column is equal to the selectivity of the entire column:
ALTER TABLE article add index Ind_article_url (URL (85)) without having to select 383 bytes as a prefix;
But the prefix index is still a bit of a disadvantage, that is, in the query statement order BY and group by cannot be used to 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);