Home > Database > Mysql Tutorial > 通过实例认识MySQL中前缀索引的用法_MySQL

通过实例认识MySQL中前缀索引的用法_MySQL

WBOY
Release: 2016-06-01 13:00:53
Original
958 people have browsed it

今天在测试环境中加一个索引时候发现一警告

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)
Copy after login

用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;
Copy after login

– 成功
接下来未测试,在不同的字符集:

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

– 错误

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;
Copy after login

– 成功

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

– 错误

 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;
Copy after login

– 成功

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

– 错误

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

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 |
+——————————-+
Copy after login

对各种长度的前缀列计算其选择性:

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)
Copy after login

我们看到选择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);
Copy after login

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template