服务器之家:专注于服务器技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|

服务器之家 - 数据库 - Mysql - MySQL唯一索引和普通索引选哪个?

MySQL唯一索引和普通索引选哪个?

2021-02-21 16:39以终为始 Mysql

这篇文章主要介绍了MySQL唯一索引和普通索引的优劣,帮助大家更好的理解和使用MySQL索引,感兴趣的朋友可以了解下

想象这样一个场景,在设计一张用户表时,每人的身份证号是唯一的,需要搜索。但由于身份证号字段较大,不好将其作为主键。在业务代码已经保证插入身份证唯一的情况下,可以选择建立唯一索引普通索引,这时该如何选择呢?接下来,将从查询和更新的执行过程进行分析。

查询过程

假设 k 是表 t 上的索引,在搜索 select id from t where k=5 时,会先从 k 这棵 B+ 的树根开始,按层搜索叶子节点,找到 k=5 的数据页,然后在数据页内容进行二分法定位。

对于普通索引,找到 k=5 的记录后,会继续向下查找一个,直到碰到第一个不是 5 的记录结束。

对于唯一索引,由于取值唯一,找到后直接停止。

由于 InnoDB 是按照数据页为单位(数据页默认 16 KB)进行读写的,在读取一条数据时,会将整个数据页整体读到内存。 在读入内存的数据页中,如果包含 k=5 的记录,在查询的情况下,唯一索引比普通索引多了一次查找和判断的过程,可以忽略。

如果 k=5 是当前数据页的最后一条,就需要在读取下一个数据页。但这发生的概率较低,也可以忽略。

所以总得来说,普通索引和唯一索引在查询的过程中差异不大。

change buffer

在分析唯一索引和普通索引的影响前,先来认识一下 change buffer 这个结构。

什么是 change buffer ?

在执行更新操作时,如果要更新的数据页在内存中就直接更新,否则的话,在不影响数据一致性的前提下,InnoDB 会将更新操作缓存在 change buffer 中,从而省去了从磁盘读取数据页的过程。在下次查询操作读取到恰好需要更新的数据页时,会将 change buffer 的更新语句执行,写入数据页。将操作应用到硬盘的过程叫 merge. 后台线程会定期 merge 或 数据库正常关闭时,也会进行 merge 操作。

merge 的执行流程:

  1. 从磁盘读入老版本数据页。
  2. 从 change buffer中找出和该数据页关联的记录,依次应用,得到新版数据页。
  3. 写 redo log,记录数据的变更和 change buffer 的变更。

change buffer 实际上是可以持久化到硬盘中的数据,也就是说在内存和硬盘上都 change buffer 的存在。change buffer 之前叫 insert buffer,开始只对 insert buffer 有优化,后来加上了对 delete 和 update 的支持,进而改名叫 change buffer。

可以看到,先将更新操作记录在 change buffer,减少了将磁盘数据页读取到内存的过程,语句的执行速度会有很明显的提升。同时,将数据读入内存,会占用 buffer pool 内存,所以减少读操作,还提高了内存使用率。

Buffer Pool 是内存中的一个区域,InnoDB 在访问表和索引数据时会在其中进行缓存。允许在内存中直接更新经常使用的数据,来加快处理速度。在一些专用的服务器上,会将 80% 的物理内存分为 buffer pool.

可以通过 innodb_change_buffer_max_size 来设置 change buffer 占用 buffer pool 的大小。

change buffer 应用场景?

如上面提到,change buffer 预先保存了更新记录,减少了读取数据页的过程,从而提高性能。也就是说如果 change buffer 中针对不同的数据页如果包含的更新记录越多,其实收益也就越大。

因此对于写多读少的业务(更新完立即查询)change buffer 发挥的作用也就越大。如常见的账单类,日志类等系统。

如果业务是更新完立即查询,虽然可以将更新记录放在 change buffer 中,但由于之后要马上查询数据页,所以会立即触发 merge 过程。这样随机访问 IO 次数并不会减少,反而增加了 change buffer 的维护代价,起到反效果。

更新过程

对于唯一索引来说,所有的更新操作都需要判断是否违反唯一性约束。所以必须把所需要的数据页读入内存,然后直接更新就可以,不需要使用 change buffer. 所以 change buffer 只对普通索引有用。

具体分析下,对于一张表插入一个新记录:

如果新记录要更新的数据页在内存中:

对于唯一索引,找到合适的位置,判断有没有冲突,插入值,语句结束。

对于普通索引:找到位置,插入值,语句结束。

所以数据页在内存时,唯一和普通索引就差一个判断的过程。可以忽略。

如果新记录要更新的数据页不在内存中:

对于唯一索引,将数据页读入内存,判断冲突,插入,语句结束。

对于普通索引,将语句记录在 change buffer 中,语句结束。

由于从磁盘到内存涉及随机 IO 访问,是数据库成本最高的操作之一。普通索引比唯一索引减少的读入操作,可以有很好的性能提升。

唯一或普通索引的选择

通过在查询和更新方面,两者的比较。我们知道,在查询过程中,除了极特殊情况,其实两者的差异并不大。

主要的差异是在更新过程中,要更新的数据页并不在内容中的情况。这时唯一索引,由于需要唯一性检查,不能利用 change buffer. 多了从磁盘到内容读取数据的过程,其中涉及随机 IO 的访问,相对来说效率就低了。

所以如果业务需要更新不错的性能,这时可以选用普通索引。当然一切都是建立在能保证数据准确性的前提下。

当如果更新后来紧接着查询操作,可以考虑关掉 change buffer. 其他的情况,change buffer 都能有很好的提升。

特别针对机械硬盘,change buffer 效果很显著。

redo log 和 change buffer 的比较

InnoDB 中 redo log 的出现使其具有了 crash-safe 的能力,同时还提高了效率,通过 WAL 先写日志,再写磁盘。

而 change buffer 是节省了从磁盘读入数据页到内存的随机IO过程。

下面通过一条插入语句来分析下两者间的关系:

?
1
mysql> insert into t(id,k) values(id1,k1),(id2,k2);

假设 k 为普通索引,k1 所插入的数据页在内存中, k2 不在。

MySQL唯一索引和普通索引选哪个?

执行插入操作时,主要涉及了图中这四部分的内容:

InnoDB buffer pool:内存区域

redo log:日志

system table space(ibdata1):系统表空间

data(t.idb): 数据表空间

innodb_file_per_table 开启时,表被创建在独立的表空间下,否则的话被创建在系统的表空间下。

执行过程如下:

  1. k1 所在的 page1 在内存中,直接更新内存
  2. k2 所在的 page2 不在内存中,记录在 change buffer.
  3. 将 k1 和 k2 的操作记录在 redo log.
  4. 提交事务。

可以看到这条更新语句(包括插入,删除,更新操作)执行成本很低,两次写入内存,1次顺序写入磁盘。虚线的操作,是后台操作,不影响响应时间。

再来看一条查询语句:

?
1
select * from t where k in (k1, k2)

假设读语句发生在更新语句不久,内存数据还在,此时读操作就和系统表空间和 redo log 无关。

MySQL唯一索引和普通索引选哪个?

执行过程:

  1. 读取 k1 所在的 page1,在内存中,直接返回。注意,并没有读磁盘上的数据,而且磁盘上的数据还有可能是之前的版本的。
  2. 读取 k2 所在的 page2,这时需要将 page2 从磁盘加载到内存,并应用 change buffer 的内容,然后返回正确的结果。从这里也能看出,change buffer 不适用于更新完立马去读的情况。

总结下 redo log 和 change buffer 的关系:

存储位置:change buffer 也会持久化在硬盘里,但保存在系统表空间 ibdata1 里。而 redo log 是单独的文件。

记录内容:change buffer 记录的是更新操作的内容,而 redo log 记录的是普通数据页的修改和 change buffer 的改动。

同步磁盘过程:同步内存中数据页的修改时通过 merge 操作进行的,而不是根据 redo log.

从更新的过程来看: redo log 将随机写磁盘的 IO 转换成了顺序写,而 change buffer 则是节省了随机读磁盘的 IO 消耗。

如果服务器异常掉电,会不会导致 change buffer 丢失?

并不会,因为 change buffer 中的数据已经被记录到 redo log 中,所以不会丢失。

由于 change buffer 一部分数据在磁盘,一部分在内存。对于在磁盘的数据已经 merge 所以不会丢失。
对于在内存中的数据:

  1. 如果 change buffer 写入,但 redo log 未提交,binlog 未提交,事务会回滚,这部分数据不存在。
  2. 如果 change buffer 写入,redo log 写入,binlog 写入,并已提交,不会丢失。从 redo log 直接恢复。
  3. 如果 change buffer 写入,redo log 写入但未 commit,binlog 写入,从 binlog 恢复 redo log 再恢复 change buffer.

参考资料

Buffer Pool

以上就是MySQL唯一索引和普通索引选哪个?的详细内容,更多关于MySQL唯一索引和普通索引的资料请关注服务器之家其它相关文章!

原文链接:https://www.cnblogs.com/michael9/p/12572895.html

延伸 · 阅读

精彩推荐
  • Mysqlmysql中url时区的陷阱该如何规避详解

    mysql中url时区的陷阱该如何规避详解

    最近在工作中发现一个问题,是关于mysql中url时区的,发现这个陷阱如果大家不注意可能都会遇到,所以给大家总结下,这篇文章主要给大家介绍了关于m...

    落叶飞逝的恋4302020-08-12
  • MysqlMySql如何实现远程登录MySql数据库过程解析

    MySql如何实现远程登录MySql数据库过程解析

    这篇文章主要介绍了MySql如何实现远程登录MySql数据库过程解析,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要...

    星之刃火5132021-01-24
  • Mysql解决MYSQL出现Can''t create/write to file ''#sql_5c0_0.MYD''的问题

    解决MYSQL出现Can''t create/write to file ''#sql_5c0_0.MYD''的问题

    今天在配置服务器的时候提示这个问题Can't create/write to file,原来是php.ini中设置的tmp目录不存在 ...

    jingxian4832020-01-06
  • MysqlMySQL数据库备份恢复实现代码

    MySQL数据库备份恢复实现代码

    这篇文章主要介绍了MySQL数据库备份恢复实现代码,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考...

    Hedger_Lee9722021-01-21
  • MysqlMAC 中mysql密码忘记解决办法

    MAC 中mysql密码忘记解决办法

    这篇文章主要介绍了MAC 中mysql密码忘记解决办法的相关资料,需要的朋友可以参考下 ...

    程序员小咖4422020-05-18
  • MysqlMySQL删除表数据的方法

    MySQL删除表数据的方法

    这篇文章主要介绍了MySQL删除表数据的方法,小编觉得还是挺不错的,这里给大家分享一下,需要的朋友可以参考。...

    孙华强2452020-08-14
  • Mysql详解Mysql命令大全(推荐)

    详解Mysql命令大全(推荐)

    本篇文章详细的介绍了Mysql命令,MySQL是一个关系型数据库管理系统,由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,一般中小型网站...

    宁静.致远3792020-07-04
  • Mysqlmysql表优化、分析、检查和修复的方法详解

    mysql表优化、分析、检查和修复的方法详解

    这篇文章主要介绍了mysql表优化、分析、检查和修复的方法,结合实例形式较为详细的分析了MySQL表进行优化,分析与修复等操作的各种常见命令与使用技巧...

    MYSQL教程网2072020-06-05