当前位置: 首页 > 编程笔记 >

MySQL普通索引和唯一索引的深入讲解

向修谨
2023-03-14
本文向大家介绍MySQL普通索引和唯一索引的深入讲解,包括了MySQL普通索引和唯一索引的深入讲解的使用技巧和注意事项,需要的朋友参考一下

场景

1、维护一个市民系统,有一个字段为身份证号

2、业务代码能保证不会写入两个重复的身份证号(如果业务无法保证,可以依赖数据库的唯一索引来进行约束)

3、常用SQL查询语句:SELECT name FROM CUser WHERE id_card = 'XXX'

4、建立索引

  • 身份证号比较大,不建议设置为主键
  • 从性能角度出发,选择普通索引还是唯一索引?

假设字段k上的值都不重复


查询过程

1、查询语句:SELECT id FROM T WHERE k=5

2、查询过程

  • 通过B+树从树根开始,按层搜索到叶子节点,即上图中右下角的数据页
  • 在数据页内部通过二分法来定位具体的记录

3、针对普通索引

  • 查找满足条件的第一个记录(5,500),然后查找下一个记录,直到找到第一个不满足k=5的记录

4、针对唯一索引

  • 由于索引定义了唯一性,查找到第一个满足条件的记录后,就会停止继续查找

性能差异

1、性能差异:微乎其微

2、InnoDB的数据是按照数据页为单位进行读写的,默认为16KB

3、当需要读取一条记录时,并不是将这个记录本身从磁盘读出来,而是以数据页为单位进行读取的

4、当找到k=5的记录时,它所在的数据页都已经在内存里了

5、对于普通索引而言,只需要多一次指针寻找和多一次计算 – CPU消耗很低

  • 如果k=5这个记录恰好是所在数据页的最后一个记录,那么如果要取下一个记录,就需要读取下一个数据页
  • 概率很低:对于整型字段索引,一个数据页(16KB,compact格式)可以存放大概745个值

change buffer

1、当需要更新一个数据页时,如果数据页在内存中就直接更新

2、如果这个数据页不在内存中,在不影响数据一致性的前提下

  • InnoDB会将这些更新操作缓存在change buffer
  • 不需要从磁盘读入这个数据页(随机读)
  • 在下次查询需要访问这个数据页的时候,将数据页读入内存
    然后执行change buffer中与这个数据页有关的操作(merge)

3、change buffer是可以持久化的数据,在内存中有拷贝,也会被写入到磁盘上

4、将更新操作先记录在channge buffer,减少随机读磁盘,提升语句的执行速度

5、另外数据页读入内存需要占用buffer pool,使用channge buffer能避免占用内存,提高内存利用率

6、change buffer用到是buffer pool里的内存,不能无限增大,控制参数innodb_change_buffer_max_size

# 默认25,最大50
mysql> SHOW VARIABLES LIKE '%innodb_change_buffer_max_size%';
+-------------------------------+-------+
| Variable_name   | Value |
+-------------------------------+-------+
| innodb_change_buffer_max_size | 25 |
+-------------------------------+-------+

merge

1、merge:将change buffer中的操作应用到原数据页

2、merge的执行过程

  • 从磁盘读入数据页到内存(老版本的数据页)
  • 从change buffer里找出这个数据页的change buffer记录(可能多个)
    然后依次执行,得到新版本的数据页
  • 写入redolog,包含内容:数据页的表更+change buffer的变更

3、merge执行完后,内存中的数据页和change buffer所对应的磁盘页都还没修改,属于脏页

  • 通过其他机制,脏页会被刷新到对应的物理磁盘页

4、触发时机

  • 访问这个数据页
  • 系统后台线程定期merge
  • 数据库正常关闭

使用条件

1、对于唯一索引来说,所有的更新操作需要先判断这个操作是否违反唯一性约束

2、唯一索引的更新无法使用change buffer,只有普通索引可以使用change buffer

  • 主键也是无法使用change buffer的
  • 例如要插入(4,400),必须先判断表中是否存在k=4的记录,这个判断的前提是将数据页读入内存
  • 既然数据页已经读入到了内存,直接更新内存中的数据页就好,无需再写change buffer

使用场景

1、一个数据页在merge之前,change buffer记录关于这个数据页的变更越多,收益越大

2、对于写多读少的业务,页面在写完后马上被访问的概率极低,此时change buffer的使用效果最好

  • 例如账单类、日志类的系统

3、如果一个业务的更新模式为:写入之后马上会做查询

  • 虽然更新操作被记录到change buffer,但之后马上查询,又会从磁盘读取数据页,触发merge过程
  • 没有减少随机读,反而增加了维护change buffer的代价

更新过程

插入(4,400)

目标页在内存中

  • 对于唯一索引来说,找到3~5之间的位置,判断没有冲突,插入这个值
  • 对于普通索引来说,找到3~5之间的位置,插入这个值
  • 性能差异:微乎其微

目标页不在内存中

1、对于唯一索引来说,需要将数据页读入内存,判断没有冲突,插入这个值

  • 磁盘随机读,成本很高

对于普通索引来说,将更新操作记录在change buffer即可

  • 减少了磁盘随机读,性能提升明显

索引选择

1、普通索引与唯一索引,在查询性能上并没有太大差异,主要考虑的是更新性能,推荐选择普通索引

2、建议关闭change buffer的场景

  • 如果所有的更新后面,都伴随着对这个记录的查询
  • 控制参数innodb_change_buffering
mysql> SHOW VARIABLES LIKE '%innodb_change_buffering%';
+-------------------------+-------+
| Variable_name   | Value |
+-------------------------+-------+
| innodb_change_buffering | all |
+-------------------------+-------+

# Valid Values (>= 5.5.4)
none / inserts / deletes / changes / purges / all

# Valid Values (<= 5.5.3)
none / inserts

# change buffer的前身是insert buffer,只能对insert操作进行优化

change buffer + redolog

更新过程

当前k树的状态:找到对应的位置后,k1所在的数据页Page 1在内存中,k2所在的数据页Page 2不在内存中

INSERT INTO t(id,k) VALUES (id1,k1),(id2,k2);


# 内存:buffer pool
# redolog:ib_logfileX
# 数据表空间:t.ibd
# 系统表空间:ibdata1

1、Page 1在内存中,直接更新内存

2、Page 2不在内存中,在changer buffer中记录:add (id2,k2) to Page 2

3、上述两个动作计入redolog(磁盘顺序写)

4、至此事务完成,执行更新语句的成本很低

  • 写两次内存+一次磁盘

5、由于在事务提交时,会把change buffer的操作记录也记录到redolog

  • 因此可以在崩溃恢复时,恢复change buffer

虚线为后台操作,不影响更新操作的响应时间

读过程

假设:读语句发生在更新语句后不久,内存中的数据都还在,与系统表空间(ibdata1)和redolog(ib_logfileX)无关

SELECT * FROM t WHERE k IN (k1,k2);


1、读Page 1,直接从内存返回(此时Page 1有可能还是脏页,并未真正落盘)

2、读Page 2,通过磁盘随机读将数据页读入内存,然后应用change buffer里面的操作日志(merge)

  • 生成一个正确的版本并返回

提升更新性能

1、redolog:节省随机写磁盘的IO消耗(顺序写)

2、change buffer:节省随机读磁盘的IO消耗

参考资料

《MySQL实战45讲》

总结

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,如果有疑问大家可以留言交流,谢谢大家对小牛知识库的支持。

 类似资料:
  • 本文向大家介绍唯一索引比普通索引快吗, 为什么?相关面试题,主要包含被问及唯一索引比普通索引快吗, 为什么?时的应答技巧和注意事项,需要的朋友参考一下 唯一索引不一定比普通索引快, 还可能慢. 查询时, 在未使用limit 1的情况下, 在匹配到一条数据后, 唯一索引即返回, 普通索引会继续匹配下一条数据, 发现不匹配后返回. 如此看来唯一索引少了一次匹配, 但实际上这个消耗微乎其微. 更新时,

  • CreateIndexes 根据struct中的tag来创建索引 CreateUniques 根据struct中的tag来创建唯一索引

  • 我创建下表: 我的问题是我是否需要这句话: ? 是否自动对创建索引?

  • 问题内容: 就性能而言,MySQL唯一索引和非唯一索引有什么区别? 假设我要在2列的组合上创建索引,并且该组合是唯一的,但是我创建了一个非唯一的索引。这会对MySQL使用的性能或内存产生重大影响吗? 同样的问题, 主 键和 唯一 索引之间有区别吗? 问题答案: UNIQUE和PRIMARY KEY是 约束 ,而不是索引。尽管大多数数据库通过使用索引来实现这些约束。除了索引之外,约束的额外开销也微不

  • 问题内容: 我有一个清单说。我想为每个唯一值分配一个特定的“索引”来获取。 这是我的代码: 事实证明这很慢。 具有1M个元素和100K个唯一元素。我也尝试过用lambda和sort进行地图操作,这没有帮助。这样做的理想方法是什么? 问题答案: 由于执行线性搜索,然后对中的每个元素执行线性搜索,因此导致代码变慢。因此,对于每1M个项目,您要进行(最多)100K个比较。 将一个值转换为另一个值的最快方

  • 本文向大家介绍MySQL批量插入和唯一索引问题的解决方法,包括了MySQL批量插入和唯一索引问题的解决方法的使用技巧和注意事项,需要的朋友参考一下 MySQL批量插入问题 在开发项目时,因为有一些旧系统的基础数据需要提前导入,所以我在导入时做了批量导入操作 ,但是因为MySQL中的一次可接受的SQL语句大小受限制所以我每次批量虽然只有500条,但依然无法插入,这个时候代码报错如下: nested