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)


相关推荐

  • Ubuntu 16.04 下安装VMware Tools(三行命令搞定,亲测好使)

    Ubuntu 16.04 下安装VMware Tools(三行命令搞定,亲测好使)Ubuntu16.04下安装VMwareTools(三行命令搞定,亲测好使):第一行命令:sudoapt-getupgrate第二行命令:sudoapt-getinstallopen-vm-tools-desktop-y第三行命令:sudoreboot如果觉得好使,请点赞;…

  • 《Android学习指南》文件夹

    《Android学习指南》文件夹

    2021年11月14日
  • 【NOIp】NOIp2008

    【NOIp】NOIp2008NOIp2008T1笨小猴标签:STL用一个map存字母到数字(出现次数)的映射由于数据范围很小,可以不用线性筛直接${\sqrt{n}}$即可code1#include<bi

  • 大数据分析系统[通俗易懂]

    大数据分析系统[通俗易懂]1. 概念、分类数据分析系统的主要功能是从众多外部系统中,采集相关的业务数据,集中存储到系统的数据库中。系统内部对所有的原始数据通过一系列处理转换之后,存储到数据仓库的基础库中;然后,通过业务需要进行一系列的数据转换到相应的数据集市,供其他上层数据应用组件进行专题分析或者展示。根据数据的流转流程,一般会有以下几个模块:数据收集(采集)、数据存储、数据计算、数据分析、数据展示等等。当然也会有…

  • 虚拟机VMware 运行不了系统的常见2个问题

    虚拟机VMware 运行不了系统的常见2个问题

  • bridge桥接模式_透明桥模式

    bridge桥接模式_透明桥模式bridge模式动机案例要点总结笔记动机由于某些类型的固有的实现逻辑,使得他们具有两个变化维度,乃至多个维度的变换如何应对这种”多维度的变化“?如何利用面向对象技术来是使得类型可以轻松地沿着两个乃至多个方向变换而不引入额外地复杂度?案例PC端和Mobile端平台和业务分离朴素class Messager{ public: virtual void Login(string username,string password) = 0; virtual void SendM

发表回复

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

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