通過執行個體認識MySQL中首碼索引的用法_Mysql

今天在測試環境中加一個索引時候發現一警告

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)

用show create table article查看索引以及表結構的資訊：

`URL` varchar(512) default NULL COMMENT ‘外鏈url',……KEY `ind_article_url` (`URL`(383))…..DEFAULT CHARSET=gbk……drop table test;create table test(test varchar(767) primary key)charset=latin5;

– 成功
接下來未測試，在不同的字元集：

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

– 錯誤
–

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;

– 成功

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

– 錯誤
–

 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;

– 成功

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

– 錯誤
–

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

MySQL的varchar索引只支援不超過768個位元組 或者 768/2=384個雙位元組 或者 768/3=256個三位元組的欄位
而 GBK是雙位元組的，UTF-8是三位元組的。
那麼上面出現的原因就明了，我的字元集是為GBK為雙位元組，而url為512個字元，1024個位元組，所以超過字串索引的限制，報出了警告，mysql預設建立了383（766位元組）長度的首碼索引。
我們知道小的索引大小不僅對空間儲存，記憶體的降低和效能的提升有重大作用，那麼在計算首碼索引的長度的時候，需要我們做出明智的選擇，怎麼明智？
全索引列的選擇性：

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

對各種長度的首碼列計算其選擇性：

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)

我們看到選擇85的長度的時候，該首碼列的選擇性和全列的選擇性相當了：
alter table article add index ind_article_url(url(85)),而不必選擇383個位元組作為首碼；
但是首碼索引還是有一點不足的地方，就是在查詢語句中order by 和group by不能使用到首碼索引

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