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

ゞ浴缸里的玫瑰 2022-10-24 13:18 94阅读 0赞

今天在测试环境中加一个索引时候发现一警告 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;

\+—-+————-+————-+——+—————+——+———+——+——–+———————————+

\+—-+————-+————-+——+—————+——+———+——+——–+———————————+

\+—-+————-+————-+——+—————+——+———+——+——–+———————————+

1 row in set (0.00 sec);

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

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 MySQL字符串前缀索引详解

相关《MySQL实战45讲》——学习笔记11 “字符串加索引、前缀索引“

相关 MySQL 索引最左前缀原则

相关 mysql 长字符串如何建立索引前缀索引

相关 MySQL中的前缀索引

相关 mysql前缀索引使用_通过实例认识MySQL中前缀索引的用法

相关 MySQL高性能的索引策略(1)：前缀索引、索引选择性、联合索引、覆盖索引

相关 Oracle 局部前缀索引和局部非前缀索引

相关 MySQL创建前缀索引和索引选择性

相关 MySQL 覆盖索引、最左前缀原则、索引下推