前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
社区首页 >专栏 >MySQL实战问题01 索引相关

MySQL实战问题01 索引相关

原创
作者头像
历久尝新
修改于 2020-06-01 03:31:50
修改于 2020-06-01 03:31:50
70700
代码可运行
举报
文章被收录于专栏:学而时习之学而时习之
运行总次数:0
代码可运行

1. 普通索引和唯一索引,应该怎么选择?

查询过程

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
select id from T where k=5
  • 对于普通索引来说,查找到满足条件的第一个记录 (5,500) 后,需要查找下一个记录,直到碰到第一个不满足 k=5 条件的记录
  • 对于唯一索引来说,由于索引定义了唯一性,查找到第一个满足条件的记录后,就会停止继续检索

查询过程使用普通索引和唯一索引带来的性能差距微乎其微

  • 因为innodb的数据是按数据页为单位来读写的, 每个数据页大小默认是16kb.
  • 找到 k=5 的记录的时候,普通索引相比唯一索引来说,只是多做的一次“查找和判断下一条记录”的操作,只需要一次指针寻找和一次计算
  • 若k=5 这个记录刚好是这个数据也的最后一条记录, 那么需要取下一个记录. 必须读取下一个数据页. 这个操作会复杂一些.

更新过程

什么是change buffer?

当更新一个数据页时, 若这个数据也在内存中, 就直接更新, 但是如果这个数据页不在内存中, 在不影响数据一致性的前提下. innodb会将这些更新操作缓存在change buffer中, 这样就不需要从磁盘中读入这个数据页了. 下次查询的时候, 将数据页读入内存, 然后执行change buff中与这个页相关的操作.

虽然叫做change buff, 实际上他是可持久化的数据. change buffer 在内存中有拷贝. 也会被写入到磁盘上.

将 change buffer 中的操作应用到原数据页,得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外,系统有后台线程会定期 merge。在数据库正常关闭(shutdown)的过程中,也会执行 merge 操作.

如果能够将更新操作先记录在 change buffer,减少读磁盘,语句的执行速度会得到明显的提升。而且,数据读入内存是需要占用 buffer pool 的,所以这种方式还能够避免占用内存,提高内存利用率.

什么条件下可以使用change buffer呢?

只有普通索引可以使用change buffer

change buffer 的使用场景

因为merge的时候是真正进行数据更新的时刻, 而change buffer的主要目的就是将记录的表更动作缓存下来,所以在一个数据也merge之前, change buffer 记录的变更越多, 收益越大

对于写多读少的业务, change buffer使用效果更好. eg 账单类 日志类系统.

反之一个业务更新后会立刻会做查询, 虽然更新会先记录会记录到change buffer 中, 但是由于查询会立刻触发merge过程. 这样随机访问io的次数不会减少, 反而增加了change buffer 的维护代价.

索引选择和实践

  • 普通索引和唯一索引在查询能力上基本是没有差别的. 主要考虑的更新性能的影响. 所以尽量选择普通索引.
  • 若更新后立马查询, 建议关闭change buffer

change buffer 和 redo log

简单对比两个机制在提升性能上的收益话. redo log 主要节省的是随机写磁盘的io消耗, 而change buffer 主要节省的是随机读磁盘的io消耗.

2. 如何给字符串加索引?

创建字符串索引的两种方法:

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
mysql> alter table SUser add index index1(email);
mysql> alter table SUser add index index2(email(6));

查看索引的命令是:

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
show index from TABLE;

两个索引的区别是:

  • index1, 包含了每个记录的整个字符串.
  • index2, 对于每个记录都是只取前6个字节.

由于index2索引结构中每个字符串只取前六个字节. 所以占用空间会小, 这是前缀索引的优势.

eg. 分别看看两个索引定义下, 查询是怎么执行的.

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
select id,name,email from SUser where email='zhangssxyz@xxx.com';

if use index1 (整个字符串):

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

这个过程中,只需要回主键索引取一次数据,所以系统认为只扫描了一行。

if use index2(前6个字符):

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

所以说使用前缀索引, 定义好长度, 就可以做到既节省空间, 又不用额外增加太多的查询成本.

计算列区分度.

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
mysql> select count(distinct email) as L from SUser;

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

前缀索引除可能会增加扫描行数, 影响到性能还有什么影响?

看个例子

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
select id,email from SUser where email='zhangssxyz@xxx.com';

相比之前的例子, 这个语句只要求返回id 和 email.

如果使用index1. 可以利用覆盖索引, 从index查到结果后, 直接返回, 不需要回表.

如果使用index2. 取到id之后, 还需要再回表判断email的值.

即使将index2的定义修改为email(18)的前缀索引. 还是会需要回表. 因为系统并不能确定前缀索引的定义是否截断

如果使用了前缀索引就用上覆盖索引对查询性能的优化了.

其他方式:

有什么方法可以既占用空间小, 也能达到相同的效率?

倒叙存储:

如果你存储身份证号的时候把它倒过来存,每次查询的时候,你可以这么写:

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
mysql> select field_list from t where id_card = reverse('input_id_card_string');

由于身份证号的最后 6 位没有地址码这样的重复逻辑,所以最后这 6 位很可能就提供了足够的区分度。当然了,实践中你不要忘记使用 count(distinct) 方法去做个验证。

哈希字段

以在表上再创建一个整数字段,来保存身份证的校验码,同时在这个字段上创建索引

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
mysql> alter table t add id_card_crc int unsigned, add index(id_card_crc);

然后每次插入新记录的时候,都同时用 crc32() 这个函数得到校验码填到这个新字段。由于校验码可能存在冲突,也就是说两个不同的身份证号通过 crc32() 函数得到的结果可能是相同的,所以你的查询语句 where 部分要判断 id_card 的值是否精确相同。

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'

索引的长度变成了 4 个字节,比原来小了很多。

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

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

原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。

如有侵权,请联系 cloudcommunity@tencent.com 删除。

原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。

如有侵权,请联系 cloudcommunity@tencent.com 删除。

评论
登录后参与评论
暂无评论
推荐阅读
编辑精选文章
换一批
MySQL深入学习第十一篇-怎么给字符串字段加索引?
现在,几乎所有的系统都支持邮箱登录,如何在邮箱这样的字段上建立合理的索引,是我们今天要讨论的问题。
越陌度阡
2020/11/26
1.9K0
MySQL深入学习第十一篇-怎么给字符串字段加索引?
一文搞懂MySQL前缀索引
通常在开发中我们需要定义字符串类型的字段,例如用户名或者用户邮箱等。 假设我们在维护一个用户登录系统,用户表的定义:
全栈程序员站长
2022/08/28
7300
一文搞懂MySQL前缀索引
MySQL 字符串索引优化方案
假设建立一个支持邮箱登录的用户表,对于邮件字段来说,可以有以下几种建立索引的方式:
凯哥Java
2022/12/16
6460
MySQL给字符串加索引
MySQL是支持前缀索引的,前缀索引的优势就是占用的空间小,这同时带来的损失是,可能会增加额外的记录扫描次数。
Michel_Rolle
2023/11/04
2.5K0
怎么给字符串加索引
如果 email 不建索引,那么就只能全表扫描,如果 email 这个字段是哪个没有索引,那么这个语句只能做全表扫描。
王小明_HIT
2020/09/22
1.8K0
mysql查询优化
很多传统型的公司的数据量都比较小,并且没有DBA这个岗位去对你开发项目中的sql进行一个最终的审核,导致开发的时候写了一些慢sql,数据量小的时候可能还好,一旦数据量上来了,查询效能极低,并且请求次数过多的话很可能会因为这一个慢sql把你整个系统拖垮,不能正常对外提供服务
炒香菇的书呆子
2022/04/14
1.3K0
Mysql如何给字符串添加索引(前缀索引)
在日常开发中,我们经常给字符串添加索引,那么给字段添加索引有什么技巧吗,我们看看下面的例子,我们给一个邮箱添加索引,应该如何添加呢
小土豆Yuki
2020/09/24
10K0
Mysql如何给字符串添加索引(前缀索引)
MySQL核心知识学习之路(5)
作为一个后端工程师,想必没有人没用过数据库,跟我一起复习一下MySQL吧,本文是我学习《MySQL实战45讲》的总结笔记的第五篇,总结了MySQL索引相关的实践使用问题。
Edison Zhou
2021/03/04
5540
MySQL核心知识学习之路(5)
超长字符串字段,前缀索引两宗罪
前缀索引并不是一个难理解的东西,但是这里面涉及到的一些细节,我相信很多同学都没有去深入了解过。
飞天小牛肉
2022/02/23
5680
超长字符串字段,前缀索引两宗罪
第10章_索引优化与查询优化
🧑个人简介:大家好,我是 shark-Gao,一个想要与大家共同进步的男人😉😉
程序员Leo
2023/08/02
4590
第10章_索引优化与查询优化
我用对了这些场景下的索引,技术总监夸我棒
生产上为了高效地查询数据库中的数据,我们常常会给表中的字段添加索引,大家是否有考虑过如何添加索引才能使索引更高效,考虑如下问题
kunge
2020/06/10
4060
MySQL普通索引和唯一索引到底什么区别?
普通索引可重复,唯一索引和主键一样不能重复。 唯一索引可作为数据的一个合法验证手段,例如学生表的身份证号码字段,我们人为规定该字段不得重复,那么就使用唯一索引。(一般设置学号字段为主键)
JavaEdge
2022/11/30
5970
MySQL普通索引和唯一索引到底什么区别?
MySQL实战之普通索引和唯一索引,应该怎么选择?
在之前的文章中,我们讲解了索引的基本概念,相信你已经了解了唯一索引和普通索引的区别。今天我们就来讨论一下,在不同的业务场景下,应该选择普通索引,还是唯一索引?
特特
2023/03/09
1.7K1
面试官:如何给字符串设计索引?
哈喽,好久没更新啦。因为最近在面试。用了两周时间准备,在 3 天之内拿了 5 个 offer,最后选择了广州某互联网行业独角兽 offer,昨天刚入职。这几天刚好整理下在面试中被问到有意思的问题,也借此机会跟大家分享下。
JavaFish
2021/07/05
6430
面试官:如何给字符串设计索引?
你确定分得清MySQL普通索引和唯一索引?
某居民系统,每人有唯一身份证号。如果系统需要按身份证号查姓名,就会执行类似如下SQL:
JavaEdge
2021/02/22
3K0
你确定分得清MySQL普通索引和唯一索引?
MySQL字符串索引创建方案
MySQL是支持前缀索引的,也就是说,你可以定义字符串的一部分作为索引。默认地,如果你创建索引的语句不指定前端长度,那么索引就会包含整个字符串。
关忆北.
2023/10/11
2360
MySQL字符串索引创建方案
你分得清MySQL普通索引和唯一索引了吗?
一个市民系统,每个人都有个唯一身份证号; 业务代码已保证不会写入两个重复的身份证号; 如果市民系统需要按照身份证号查姓名,就会执行类似SQL:
JavaEdge
2020/07/25
2.2K1
MySQL索引详解
一个数据页满了,按照B+Tree算法,新增加一个数据页,叫做页分裂,会导致性能下降。空间利用率降低大概50%。当相邻的两个数据页利用率很低的时候会做数据页合并,合并的过程是分裂过程的逆过程。
Michel_Rolle
2023/10/29
2.5K1
普通索引和唯一索引,应该怎么选择?
案例:假设你在维护一个市民系统,每个人都有一个唯一的身份证号,而且业务代码已经保证了不会写入两个重复的身份证号。如果市民系统需要按照身份证号查姓名,那该如何建索引?
用户7353950
2022/06/23
4340
普通索引和唯一索引,应该怎么选择?
MySQL深入学习第九篇-普通索引和唯一索引,应该怎么选择?
在前面的基础篇文章中,我给你介绍过索引的基本概念,相信你已经了解了唯一索引和普通索引的区别。今天我们就继续来谈谈,在不同的业务场景下,应该选择普通索引,还是唯一索引?
越陌度阡
2020/11/26
9912
MySQL深入学习第九篇-普通索引和唯一索引,应该怎么选择?
推荐阅读
相关推荐
MySQL深入学习第十一篇-怎么给字符串字段加索引?
更多 >
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档
查看详情【社区公告】 技术创作特训营有奖征文
本文部分代码块支持一键运行,欢迎体验
本文部分代码块支持一键运行,欢迎体验