經由過程實例熟悉MySQL中前綴索引的用法

經由過程實例熟悉MySQL中前綴索引的用法。本站提示廣大學習愛好者：（經由過程實例熟悉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: 0
root@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: 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)

用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 bytes
drop 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 bytes
drop 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);