mysql前缀索引 默认长度_如何确定前缀索引的长度?

mysql前缀索引 默认长度_如何确定前缀索引的长度?为什么需要前缀索引问题我们在对一张表里的某个字段或者多个字段建立索引的时候,是否遇到过这个问题。Specifiedkey’uniq_code’wastoolong;maxkeylengthis767bytes.表结构如下:createtable`t_account`(`id`BIGINT(20)UNSIGNEDNOTNULLauto_incrementCOMM…

大家好,又见面了,我是你们的朋友全栈君。

为什么需要前缀索引

问题

我们在对一张表里的某个字段或者多个字段建立索引的时候,是否遇到过这个问题。

Specified key ‘uniq_code’ was too long; max key length is 767 bytes.

表结构如下:

create table `t_account`(

`id` BIGINT(20) UNSIGNED NOT NULL auto_increment COMMENT ‘自增ID’,

`date` varchar(50) NOT NULL DEFAULT ” COMMENT ‘日期’,

`nick_name` varchar(50) NOT NULL DEFAULT ” COMMENT ‘昵称’,

`account` varchar(50) NOT NULL DEFAULT ” COMMENT ‘账号’,

`city` varchar(100) NOT NULL DEFAULT ” COMMENT ‘城市’,

PRIMARY KEY (`id`),

UNIQUE KEY `uniq_code` (`nick_name`,`account`,`city`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=’Test’;

复制代码

原因

在MySQL5.6里默认 innodb_large_prefix=0 限制单列索引长度不能超过767bytes。

在MySQL5.7里默认 innodb_large_prefix=1 解除了767bytes长度限制,但是单列索引长度最大还是不能超过3072bytes。

至于为什么是767字节,是依赖于具体的存储引擎实现的,找了官方文档,也没说为啥。 https://dev.mysql.com/doc/refman/8.0/en/create-index.html

varchar(n)占用几个字节跟字符集有关系:

字符类型若为gbk,每个字符占用2个字节,

字符类型若为utf8,每个字符最多占用3个字节,

字符类型若为utf8mb4,每个字符最多占用4个字节

复制代码

这里我设置的编码为utf8mb4编码,一个字符是占了4个字节,而我创建的索引50+50+100=200字符,总共就是800字节,所以超出了长度。

所以我们经常会见到把字段设置成varchar(255)长度的,在utf8字符集下这个是最大不超过767bytes的长度了,但是并不是一定要设置成varchar(255),还是要根据业务设置每个字段的长度,太长了也不利于我们建立联合索引。

解决办法

可以直接去改字段的长度,或者说,把索引的字段取消掉一些,但是这样改对表本身是不友好的。

通过限定字段的前n个字符为索引,可以通过衡量实际的业务中数据中的长度来取具体的值。

UNIQUE KEY `uniq_code` (`nick_name`(20),`account`(20),`city`(20))

复制代码

表示三个字段取前20字符作为唯一索引,这样的话就是长度就不会超出,这个就是我们说的前缀索引

修改单个索引的最大长度

修改索引限制长度需要在my.ini配置文件中添加以下内容,并重启:

#修改单列索引字节长度为767的限制,单列索引的长度变为3072

innodb_large_prefix=1

但是开启该参数后还需要开启表的动态存储或压缩:

系统变量innodb_file_format为Barracuda

ROW_FORMAT为DYNAMIC或COMPRESSED

复制代码

如何确定前缀索引的长度

上面我们说到可以通过前缀索引来解决索引长度超出限制的问题,但是我们改如何确定索引字段取多长的前缀才合适呢?

这里我们可以通过计算选择性来确定前缀索引的选择性,计算方法如下

全列选择性:

SELECT COUNT(DISTINCT column_name) / COUNT(*) FROM table_name;

某一长度前缀的选择性:

SELECT COUNT(DISTINCT LEFT(column_name, prefix_length)) / COUNT(*) FROM table_name;

当前缀的选择性越接近全列选择性的时候,索引效果越好。

前缀索引的优缺点

占用空间小且快

无法使用前缀索引做 ORDER BY 和 GROUP BY

无法使用前缀索引做覆盖扫描

有可能增加扫描行数

比如身份证加索引,可以加哈希索引或者倒序存储后加前缀索引。

再谈联合索引的创建

当我们不确定在一张表上建立的联合索引应该以哪个字段作为第一列时,上面的创建规则同样适用。

下面这个例子就是在建立customer_id,staff_id的联合索引时进行判断,最终选择(customer_id,staff_id)这样的组合。

# staff_id_selectivity: 0.0001

# customer_id_selectivity: 0.0373

# COUNT(*): 16049

# 通过结果发现,customer_id 的选择性更高,所以应该选择 customer_id 作为联合索引的第一列

SELECT

COUNT(DISTINCT staff_id)/COUNT(*) as staff_id_selectivity,

COUNT(DISTINCT customer_id)/COUNT(*) as customer_id_selectivity,

COUNT(*)

FROM payment

复制代码

所以说

当索引选择性越接近全列选择性的时候,索引效果越好。

也就是用此字段创建索引时,它在这个表的数据里区分度更加明显。

参考

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/143845.html原文链接:https://javaforall.cn

【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛

【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

(0)


相关推荐

  • 两向量的夹角_两向量垂直的夹角是多少

    两向量的夹角_两向量垂直的夹角是多少计算两个向量间的夹角

  • 最大似然估计,最大后验估计,贝叶斯估计联系与区别

    最大似然估计,最大后验估计,贝叶斯估计联系与区别1.什么是参数在机器学习中,我们经常使用一个模型来描述生成观察数据的过程。例如,我们可以使用一个随机森林模型来分类客户是否会取消订阅服务(称为流失建模),或者我们可以用线性模型根据公司的广告支出来预测公司的收入(这是一个线性回归的例子)。每个模型都包含自己的一组参数,这些参数最终定义了模型本身。我们可以把线性模型写成y=mx+c的形式。在广告预测收入的例子中,x可以表示广告支…

    2022年10月19日
  • 格雷码与二进制的转换[通俗易懂]

    格雷码与二进制的转换[通俗易懂]一、什么是格雷码?格雷码,又叫循环二进制码或反射二进制码,格雷码是我们在工程中常会遇到的一种编码方式,它的基本的特点就是任意两个相邻的代码只有一位二进制数不同,这点在下面会详细讲解到。格雷码的基本特点就是任意两个相邻的代码只有一位二进制数不同,这点很重要。常用的二进制数与格雷码间的转换关系如下表:二、二进制格雷码与自然二进制码的互换1、二进制码转换成二进制格雷码  二进制码转换成二进制格雷码,

    2022年10月17日
  • 校园网络总体规划设计方案_校园网的使用与规划

    校园网络总体规划设计方案_校园网的使用与规划目录一、项目概述3二、设计要求4(一)网络部分的总体要求4(二)系统部分的总体要求4(三)网络安全设计4三、设计原则6四、设计目标6五、网络拓扑结构设计7(一)布线结构需求7(二)拓扑结构的选择7六、网络技术选择9(一)VLAN技术9(二)IP通信技术10(三)使用适应性的路由协议10(四)访问控制技术11七、网络设备选型原则12(一)交换机的选择12(二)路由器的选择13(三)防火墙14(四)服…

  • DataGridview获取选中行数「建议收藏」

    DataGridview获取选中行数「建议收藏」DataGridvie获取选中行数代码:dataGridView1.CurrentRow.Index//获取选中行数使用Messbox.Show()弹窗:

  • openid什么意思_openId

    openid什么意思_openId在使用IdentityServer作IdentityProvider的时候,我们在NetCore的ConfigureServices((IServiceCollectionservices))方

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号