Home > database, mysql, sql优化 > 认识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);
《mysql high performence》
Categories: database, mysql, sql优化 Tags:
  1. No comments yet.
  1. No trackbacks yet.
You must be logged in to post a comment.