通过实例认识MySQL中前缀索引的用法
今天在测试环境中加一个索引时候发现一警告
root@test07:57:52>altertablearticledropindexind_article_url; QueryOK,144384rowsaffected(16.29sec) Records:144384Duplicates:0Warnings:0 root@test07:58:40>altertablearticleaddindexind_article_url(url); QueryOK,144384rowsaffected,1warning(19.52sec) Records:144384Duplicates:0Warnings:0 root@test07:59:23>showwarnings; +———+——+———————————————————+ |Level|Code|Message| +———+——+———————————————————+ |Warning|1071|Specifiedkeywastoolong;maxkeylengthis767bytes| +———+——+———————————————————+ 1rowinset(0.00sec)
用showcreatetablearticle查看索引以及表结构的信息：
`URL`varchar(512)defaultNULLCOMMENT‘外链url', …… KEY`ind_article_url`(`URL`(383)) ….. DEFAULTCHARSET=gbk …… droptabletest; createtabletest(testvarchar(767)primarykey)charset=latin5;
–成功
接下来未测试，在不同的字符集：
droptabletest; createtabletest(testvarchar(768)primarykey)charset=latin5;
–错误
–
ERROR1071(42000):Specifiedkeywastoolong;maxkeylengthis767bytes droptabletest; createtabletest(testvarchar(383)primarykey)charset=GBK;
–成功
droptabletest; createtabletest(testvarchar(384)primarykey)charset=GBK;
–错误
–
ERROR1071(42000):Specifiedkeywastoolong;maxkeylengthis767bytes droptabletest; createtabletest(testvarchar(255)primarykey)charset=UTF8;
–成功
droptabletest; createtabletest(testvarchar(256)primarykey)charset=UTF8;
–错误
–
ERROR1071(42000):Specifiedkeywastoolong;maxkeylengthis767bytes
MySQL的varchar索引只支持不超过768个字节或者768/2=384个双字节或者768/3=256个三字节的字段
而GBK是双字节的，UTF-8是三字节的。
那么上面出现的原因就明了，我的字符集是为GBK为双字节，而url为512个字符，1024个字节，所以超过字符串索引的限制，报出了警告，mysql默认创建了383（766字节）长度的前缀索引。
我们知道小的索引大小不仅对空间存储，内存的降低和性能的提升有重大作用，那么在计算前缀索引的长度的时候，需要我们做出明智的选择，怎么明智？
全索引列的选择性：
root@test08:10:35>selectcount(distinct(url))/count(*)fromarticle; +——————————-+ |count(distinct(url))/count(*)| +——————————-+ |0.0750| +——————————-+
对各种长度的前缀列计算其选择性：
root@test08:16:41>selectcount(distinctleft(url,76))/count(*)url_76, ->count(distinctleft(url,77))/count(*)url_77, ->count(distinctleft(url,78))/count(*)url_78, ->count(distinctleft(url,79))/count(*)url_79, ->count(distinctleft(url,80))/count(*)url_80, ->count(distinctleft(url,81))/count(*)url_81, ->count(distinctleft(url,82))/count(*)url_82, ->count(distinctleft(url,83))/count(*)url_83, ->count(distinctleft(url,84))/count(*)url_84, ->count(distinctleft(url,85))/count(*)url_85 ->fromarticle; +——–+——–+——–+——–+——–+——–+——–+——–+——–+——–+ |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| +——–+——–+——–+——–+——–+——–+——–+——–+——–+——–+ 1rowinset(1.82sec)
我们看到选择85的长度的时候，该前缀列的选择性和全列的选择性相当了：
altertablearticleaddindexind_article_url(url(85)),而不必选择383个字节作为前缀；
但是前缀索引还是有一点不足的地方，就是在查询语句中orderby和groupby不能使用到前缀索引
root@test08:49:24>explainselectid,url,deletedfromarticlegroupbyurl; +—-+————-+————-+——+—————+——+———+——+——–+———————————+ |id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra| +—-+————-+————-+——+—————+——+———+——+——–+———————————+ |1|SIMPLE|article|ALL|NULL|NULL|NULL|NULL|139844|Usingtemporary;Usingfilesort| +—-+————-+————-+——+—————+——+———+——+——–+———————————+ 1rowinset(0.00sec);