Windows Linux/Shell Nginx UNIX
MySQL Ms SQL Server MS Access MS DOS命令 Oracle SQLite MongoDB Redis SSDB Memcached Cassandra Hibernate MariaDB MyBatis Slick Spark Kubernetes neo4j TiDB PostgreSQL InfluxDB Istio IoTDB
用户登录
用户注册

分享至

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

  • 作者: 老娘真的信了你邪
  • 来源: 51数据库
  • 2020-08-17

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


 
1
2
3
4
5
6
7
8
9
10
11
12
13
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查看索引以及表结构的信息：


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

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


 
1
2
drop table test;
create table test(test varchar(768) primary key)charset=utf-8;

– 错误
–


 
1
2
3
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=utf-8;

– 成功


 
1
2
drop table test;
create table test(test varchar(384) primary key)charset=utf-8;

– 错误
–


 
1
2
3
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=utf-8;

– 成功


 
1
2
drop table test;
create table test(test varchar(256) primary key)charset=utf-8;

– 错误
–


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

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


 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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不能使用到前缀索引


 
1
2
3
4
5
6
7
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);


推荐阅读
热点文章

Copyright 2018 51数据库(www.51sjk.com)

软件
前端设计
程序设计
Java相关