MySQL实战45讲 11

11 | 怎么给字符串字段加索引?

Q:如何在邮箱这样的字段上建立合理的索引?

用户表的定义:

create table SUser(
ID bigint unsigned primary key,
email varchar(64),
...

)engine=innodb;

由于您将使用邮箱登录,因此业务代码中必须出现类似以下内容的语句:

[En]

Because you are going to log in using a mailbox, a statement similar to this must appear in the business code:

select f1, f2 from SUser where email='xxx';

如果 email 这个字段上没有索引,那么这个语句就只能做全表扫描。

A:

前缀索引

MySQL 是支持前缀索引的,也就是说,可以定义 字符串的一部分作为索引。

默认情况下,如果创建索引的语句没有指定前缀长度,则索引将包含整个字符串。

[En]

By default, if the statement in which you create the index does not specify a prefix length, the index will contain the entire string.

alter table SUser add index index1(email);

alter table SUser add index index2(email(6));

第一个语句创建的 index1 索引里面,包含了每个记录的整个字符串;

而第二个语句创建的 index2 索引里面,对于每个记录都是只取前 6 个字节。

数据结构和存储方面的差异:

[En]

Differences in data structure and storage:

MySQL实战45讲 11

MySQL实战45讲 11

由于 email(6) 这个索引结构中每个邮箱字段都只取前 6 个字节(即:zhangs),所以 占用的空间会更小,这就是使用前缀索引的优势。

与此同时,损失在于它可能会增加额外的记录扫描次数。

[En]

At the same time, the loss is that it may increase the number of additional record scans.

执行上面的select时:

如果使用的是 index1(即 email 整个字符串的索引结构),执行顺序是这样的:

  1. 从 index1 索引树找到满足索引值是’zhangssxyz@xxx.com’的这条记录,取得 ID2 的值;
  2. 到主键上查到主键值是 ID2 的行,判断 email 的值是正确的,将这行记录加入结果集;
  3. 取 index1 索引树上刚刚查到的位置的下一条记录,发现 已经不满足 email=’zhangssxyz@xxx.com’的条件了,循环结束。

在此过程中,您只需要返回到主键检索数据一次,因此系统认为只扫描了一行。

[En]

In this process, you only need to go back to the primary key to retrieve the data once, so the system thinks that only one row has been scanned.

如果使用的是 index2(即 email(6) 索引结构),执行顺序是这样的:

  1. 从 index2 索引树找到满足索引值是’zhangs’的记录,找到的第一个是 ID1;
  2. 到主键上查到主键值是 ID1 的行,判断出 email 的值不是’zhangssxyz@xxx.com’,这行记录丢弃;
  3. 取 index2 上刚刚查到的位置的下一条记录,发现仍然是’zhangs’,取出 ID2,再到 ID 索引上取整行然后判断,这次值对了,将这行记录加入结果集;
  4. 重复上一步, 直到在 idxe2 上取到的值不是’zhangs’时,循环结束

因此,使用前缀索引可能会导致查询语句读取数据的次数更多。

[En]

Therefore, the use of prefix indexes may result in more times for query statements to read data.

使用多长的前缀呢?

在建立指数时,要注意分化程度,分化程度越高越好。因为分化程度越高,重复键值就越少。

[En]

When establishing the index, we should pay attention to the degree of differentiation, and the higher the degree of differentiation, the better. Because the higher the degree of differentiation, the fewer repeated key values.

以通过 统计索引上有多少个不同的值来判断要使用多长的前缀。

首先计算出此列中有多少个不同的值:

[En]

First figure out how many different values there are on this column:

select count(distinct email) as L from SUser;

然后,选择不同长度的前缀以查看此值:

[En]

Then, select the prefixes of different lengths to look at this value:

select
  count(distinct left(email,4))as L4,
  count(distinct left(email,5))as L5,
  count(distinct left(email,6))as L6,
  count(distinct left(email,7))as L7,
from SUser;

前缀索引对覆盖索引的影响

select id,email from SUser where email='zhangssxyz@xxx.com';

使用index1,由于通过email可以直接找到id,即覆盖索引,不需要回表。

使用index2(即 email(6) 索引结构)的话,就不得不回到 ID 索引再去判断 email 字段的值。

即使你将 index2 的定义修改为 email(18) 的前缀索引,这时候 虽然 index2 已经包含了所有的信息,但 InnoDB 还是要回到 id 索引再查一下,因为系统并 不确定前缀索引的定义是否截断了完整信息

前缀索引就用不上覆盖索引对查询性能的优化

其他方式

Q:遇到前缀的区分度不够好的情况时,要怎么办?例如身份证号码?

我希望它不仅能占用更少的空间,还能达到同样的查询效率。

[En]

I hope it can not only occupy less space, but also achieve the same query efficiency.

A:

第一种方式是使用倒序存储。如果你存储身份证号的时候把它倒过来存,每次查询的时候,你可以这么写

select field_list from t where id_card = reverse('input_id_card_string');

由于身份证号的最后 6 位没有地址码这样的重复逻辑,所以最后这 6 位很可能就提供了足够的区分度

第二种方式是使用 hash 字段。你可以 在表上再创建一个整数字段,来保存身份证的校验码,同时 在这个字段上创建索引

alter table t add id_card_crc int unsigned, add index(id_card_crc);

然后每次插入新记录的时候,都同时用 crc32() 这个函数得到校验码填到这个新字段。

crc32 – 计网数据库中常使用 返回字符串的 32 位循环冗余校验值。

由于校验码可能存在冲突,也就是说两个不同的身份证号通过 crc32() 函数得到的结果可能是相同的,所以你的查询语句 where 部分要判断 id_card 的值是否精确相同

使用倒序存储和使用 hash 字段这两种方法的异同点

同样的事情是不支持范围查询。

[En]

The same thing is that range queries are not supported.

倒序存储的字段上创建的索引是按照倒序字符串的方式排序的,已经 没有办法利用索引方式查出身份证号码在 [ID_X, ID_Y] 的所有市民。同样地,hash 字段的方式也只能支持 等值查询

差异主要体现在以下三个方面:

[En]

The differences are mainly reflected in the following three aspects:

  1. 从占用的额外空间来看,倒序存储方式在主键索引上,不会消耗额外的存储空间,而 hash 字段方法需要增加一个字段
  2. 在 CPU 消耗方面,倒序方式 每次写和读的时候,都需要 额外调用一次 reverse 函数,而 hash 字段的方式需要 额外调用一次 crc32() 函数
  3. 从查询效率上看, 使用 hash 字段方式的查询性能相对更稳定一些。因为 crc32 算出来的值虽然有冲突的概率,但是概率非常小,可以认为每次查询的平均扫描行数接近 1。而倒序存储方式毕竟还是用的前缀索引的方式,也就是说还是会增加扫描行数。

总结

  1. 直接创建完整索引,这样可能 比较占用空间
  2. 创建前缀索引,节省空间,但会增加查询扫描次数,并且 不能使用覆盖索引
  3. 倒序存储,再创建前缀索引,用于绕过字符串本身前缀的区分度不够的问题;
  4. 创建 hash 字段索引,查询性能稳定,有额外的存储和计算消耗,跟第三种方式一样,都 不支持范围扫描

Q:如果你在维护一个学校的学生信息数据库,学生登录名的统一格式是” 学号 @gmail.com

学号规则为:15位,其中前三位为城市号,第四至第六位为校号,第七至第十位为入学年份,后五位为顺序号。

[En]

The rule of the student number is: fifteen digits, of which the first three are the city number, the fourth to the sixth is the school number, the seventh to the tenth is the year of admission, and the last five digits are sequential numbers.

当系统登录时,学生需要输入他们的登录名和密码,并验证他们是正确的,然后才能继续使用系统。如果只考虑登录身份验证的行为,如何设计登录名的索引?

[En]

When the system logs in, students are required to enter their login name and password and verify that they are correct before they can continue to use the system. If you only consider the behavior of login authentication, how do you design the index of the login name?

A:

因为维护的只是 一个学校的,因此前面 6 位(其中,前三位是所在城市编号、第四到第六位是学校编号)其实是 固定的,邮箱后缀都是 @gamil.com,因此可以 只存入学年份加顺序编号,它们的长度是 9 位。

而其实在此基础上,可以用 数字类型来存这 9 位数字。比如 201100001, 这样只需要占 4 个字节。其实这个就是一种 hash,只是它用了最简单的转换规则: 字符串转数字的规则,而刚好我们设定的这个背景,可以保证这个转换后结果的唯一性。

Original: https://www.cnblogs.com/ydssx7/p/16512781.html
Author: ydssx
Title: MySQL实战45讲 11

原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/505164/

转载文章受原作者版权保护。转载请注明原作者出处!

(0)

大家都在看

  • 关系型、非关系型数据库存储选型盘点大全

    工作中总是遇到数据存储相关的 Bug 工单,新需求开发设计中也多多少少会有数据模型设计和存储相关的问题。经过几次存储方案设计选型和讨论后发现需要有更全面的思考框架。 日常开发中常用…

    数据库 2023年6月14日
    0169
  • centos下安装jdk8和maven

    创建目录 mkdir -p/usr/local/java/ 网盘拉去JDK和Maven 链接:https://pan.baidu.com/s/1GgJk8ji0r-tjGAj_ea…

    数据库 2023年6月6日
    0120
  • 上传jar包到私有仓库nexus3

    上传Jar包到私有仓库Nexus3 官方文档 上传组件(Jar包) POST /service/rest/v1/components 官方例子: curl -v -u admin:…

    数据库 2023年6月9日
    0121
  • 为什么我选择MySQL Workbench・一

    一、官方 官方提供的工具必然有其优势。 MySQL Workbench有两个版本,社区版和商业版。社区版是免费的。 二、第一个选择 使用MySQL之前用的是SQL Server而微…

    数据库 2023年6月9日
    0114
  • VMware Workstation 与 Device/Credential Guard 不兼容

    问题: 在确定已经禁用 Hyper-V 功能的前提下,启动虚拟机还是报错 ” VMware Workstation 与 Device/Credential Guard …

    数据库 2023年6月14日
    0128
  • MySQL并行复制(MTS)原理(完整版)

    MySQL 5.6并行复制架构 MySQL 5.7并行复制原理 Master 组提交(group commit) 支持并行复制的GTID slave LOGICAL_CLOCK(由…

    数据库 2023年6月16日
    0135
  • 数据库操作命令

    MySQL Key Command 清屏 (滚屏 ) 登录 切换数据库 查看数据库 查看表 查看表结构 退出 帮助 Key Command 查看可用字符集 查看默认字符集 设置默认…

    数据库 2023年5月24日
    0121
  • MySQL学习笔记-day01

    SQL,全称 Structured Query Language, SQL用来和数据库打交道,完成和数据库的通信, SQL是一套标准。(90%以上的 SQL都是通用的)。 SQL:…

    数据库 2023年5月24日
    0127
  • mysql的安装和下载

    1、 MySQL 下载地址为: MySQL 下载,这里下载的是mysql的msi安装文件,选择下面的470.2M的版本,点击download进行下载。 2、在下载页面直接选择: N…

    数据库 2023年5月24日
    0138
  • 绿色安装MySQL5.7版本—-配置my.ini文件注意事项

    简述绿色安装MySQL5.7版本以及配置my.ini文件注意事项 前言 由于前段时间电脑重装,虽然很多软件不在C盘,但是由于很多注册表以及关联文件被删除,很多软件还需要重新配置甚至…

    数据库 2023年6月16日
    0148
  • RadonDB MySQL Kubernetes 2.2.0 发布!

    摘要 RadonDB MySQL Kubernetes v2.2.0 于近日发布!该版本开始支持 MySQL 8.0,备份功能优化,并全面提升高可用稳定性。社区同步发起&#8221…

    数据库 2023年5月24日
    0159
  • Socket 编码入门

    很多同行朋友大概率学过 socket 通信相关知识, send, recv, listen, connect, accept 等等组合, 变幻 无穷 … 相关的介绍书籍…

    数据库 2023年6月9日
    0110
  • Java 中的异常处理机制

    本篇文章主要介绍了 Java 中的异常 如何处理函数抛出的异常 处理异常的原则 异常处理时,性能开销大的地方 Java 语言在设计之初就提供了相对完善的异常处理机制。 我们首先介绍…

    数据库 2023年6月11日
    0161
  • 工程师成长阶段感悟

    从 2013 年陆续开始做软件研发工作, 去过不少公司, 做过一些类型项目, 桌面开发, web 开发, 手游开发, 端游开发, 棋牌, 视频云服务, 电商. 刚毕业那会在国企, …

    数据库 2023年6月9日
    0132
  • Spring Boot整合Spring Data连接postgreSQL完成简单的CRUD操作

    导入jpa依赖和postgresql依赖: 编写pojo层数据库实体类Company: 编写接口: 编写测试类: 基本使用教程 Original: https://www.cnbl…

    数据库 2023年6月6日
    0131
  • 部署前后端为独立的 Docker 节点

    在『服务器部署 Vue 和 Django 项目的全记录』一文中,介绍了在服务器中使用 Nginx 部署前后端项目的过程。然而,当 Web 应用流量增多时,需要考虑负载均衡、流量分发…

    数据库 2023年6月14日
    0156
亲爱的 Coder【最近整理,可免费获取】👉 最新必读书单  | 👏 面试题下载  | 🌎 免费的AI知识星球