這篇文章主要通過實例來介紹MySQL中的前綴索引,包括前綴在實際使用中需要考慮到的長度問題等,需要的朋友可以參考下

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

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

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

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

– 成功

接下來未測試，在不同的字符集：

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

– 錯誤

1. ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes 
2. drop table test; 
3. create table test(test varchar(383) primary key)charset=GBK; 

– 成功

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

– 錯誤

–

1. ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes 
2. drop table test; 
3. create table test(test varchar(255) primary key)charset=UTF8; 

– 成功

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

– 錯誤

–

1. 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字節)長度的前綴索引。

我們知道小的索引大小不僅對空間存儲，內存的降低和性能的提升有重大作用，那么在計算前綴索引的長度的時候，需要我們做出明智的選擇，怎么明智?

全索引列的選擇性：

1. root@test 08:10:35>select count(distinct(url))/count(*) from article; 
2. +——————————-+ 
3. | count(distinct(url))/count(*) | 
4. +——————————-+ 
5. | 0.0750 | 
6. +——————————-+ 

對各種長度的前綴列計算其選擇性：

1. root@test 08:16:41>select count(distinct left(url,76))/count(*) url_76, 
2. -> count(distinct left(url,77))/count(*) url_77, 
3. -> count(distinct left(url,78))/count(*) url_78, 
4. -> count(distinct left(url,79))/count(*) url_79, 
5. -> count(distinct left(url,80))/count(*) url_80, 
6. -> count(distinct left(url,81))/count(*) url_81, 
7. -> count(distinct left(url,82))/count(*) url_82, 
8. -> count(distinct left(url,83))/count(*) url_83, 
9. -> count(distinct left(url,84))/count(*) url_84, 
10. -> count(distinct left(url,85))/count(*) url_85 
11. -> from article; 
12. +——–+——–+——–+——–+——–+——–+——–+——–+——–+——–+ 
13. | url_76 | url_77 | url_78 | url_79 | url_80 | url_81 | url_82 | url_83 | url_84 | url_85 | 
14. +——–+——–+——–+——–+——–+——–+——–+——–+——–+——–+ 
15. | 0.0747 | 0.0748 | 0.0749 | 0.0749 | 0.0749 | 0.0749 | 0.0749 | 0.0749 | 0.0749 | 0.0750 | 
16. +——–+——–+——–+——–+——–+——–+——–+——–+——–+——–+ 
17. 1 row in set (1.82 sec) 

我們看到選擇85的長度的時候，該前綴列的選擇性和全列的選擇性相當了：

alter table article add index ind_article_url(url(85)),而不必選擇383個字節作為前綴;

但是前綴索引還是有一點不足的地方，就是在查詢語句中order by 和group by不能使用到前綴索引

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