MySQL中普通索引和唯一索引的区别详解

本篇文章介绍了mysql中普通索引和唯一索引的区别,讲解很详细,希望对学习mysql的朋友有帮助!

MySQL中普通索引和唯一索引的区别详解

MySQL中普通索引和唯一索引的区别详解

一、查询和更新上的区别

这两类索引在查询能力上是没差别的,主要考虑的是对更新性能的影响。建议尽量选择普通索引。

(免费学习视频教程推荐:mysql视频教程)

1.1 MySQL 的查询操作

■ 普通索引

查找到第一个满足条件的记录后,继续向后遍历,直到第一个不满足条件的记录。

■ 唯一索引

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

普通索引会多检索一次,几乎没有影响。因为 InnoDB 的数据是按照数据页为单位进行读写的,需要读取数据时,并不是直接从磁盘读取记录,而是先把数据页读到内存,再去数据页中检索。

一个数据页默认 16 KB,对于整型字段,一个数据页可以放近千个 key,除非要读取的数据在数据页的最后一条记录,就需要再读一个数据页,这种情况很少,对CPU的消耗基本可以忽略了。

因此说,在查询数据方面,普通索引和唯一索引没差别。

1.2 MySQL 的更新操作

更新操作并不是直接对磁盘中的数据进行更新,是先把数据页从磁盘读入内存,再更新数据页。

■ 普通索引

将数据页从磁盘读入内存,更新数据页。

■ 唯一索引

将数据页从磁盘读入内存,判断是否唯一,再更新数据页。

由于 MySQL 中有个 change buffer 的机制,会导致普通索引和唯一索引在更新上有一定的区别。

change buffer的作用是为了降低IO 操作,避免系统负载过高。change buffer将数据写入数据页的过程,叫做merge。

如果需要更新的数据页在内存中时,会直接更新数据页;如果数据不在内存中,会先将更新操作记入change buffer,当下一次读取数据页时,顺带merge到数据页中,change buffer也有定期merge策略。数据库正常关闭的过程中,也会触发merge。

对于唯一索引,更新前需要判断数据是否唯一(不能和表中数据重复),如果数据页在内存中,就可以直接判断并且更新,如果不在内存中,就需要去磁盘中读出来,判断一下是否唯一,是的话就更新。change buffer是用不到的。即使数据页不在内存中,还是要读出来。

change buffer 用的是 buffer pool 里的内存,因此不能无限增大。change buffer 的大小,可以通过参数 innodb_change_buffer_max_size 来动态设置。这个参数设置为 50 的时候,表示 change buffer 的大小最多只能占用 buffer pool 的 50%。

结论:唯一索引用不了change buffer,只有普通索引可以用。

二、change buffer 和 redo log的区别

2.1 change buffer 的适用场景

change buffer 的作用是降低更新操作的频率,缓存更新操作。这样会有一个缺点,就是更新不及时,对于读操作比较频繁的表,不建议使用 change buffer。

纳米搜索 纳米搜索

纳米搜索:360推出的新一代AI搜索引擎

纳米搜索 30 查看详情 纳米搜索

因为更新操作刚记录进change buffer中,就读取了该表,数据页被读到了内存中,数据马上就merge到数据页中了。这样不仅不会降低性能消耗,反而会增加维护change buffer的成本。

适用于写多读少的表。

2.2 change buffer 和 redo log 区别

我们举一个例子用来理解 redo log 和 change buffer。我们执行以下 SQL 语句:

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

假设,(id1,k1) 在数据页 Page 1 中,(id2,k2) 在数据页 Page 2 中。并且 Page 1 在内存中,Page 2 不在内存中。

执行过程如下:

直接向 Page 1 中写入 (id1,k1);

在change buffer 中记下”向 Page 2 中写入(id2,k2)”这条信息;

将以上两个动作记入redo log。

做完上面这些,事务就可以完成了。执行这条更新语句的成本很低,就是写了两处内存,然后写了一处磁盘(两次操作合在一起写了一次磁盘),而且还是顺序写的。

这条更新语句,涉及了四个部分:内存、redo log(ib_log_fileX)、 数据表空间(t.ibd)、系统表空间(ibdata1)。

1.png

如果要读数据的话,过程是怎样的?

mysql> select * from t where k in (k1, k2);

假设读操作在更新后不久,此时内存中还有 Page 1,没有 Page 2,那么读操作就和 redo log 以及 ibdata1 无关了。

从内存中获取到 Page 1 上的最新数据 (id1,k1);

将数据页 Page 2 读入内存,执行merge 操作,此时内存中的 Page 2 也有最新数据(id2,k2);

2.png

需要注意的是:

redo log中的数据,可能还没有 flush 到磁盘,磁盘中的 Page 1 和 Page 2 中并没有最新数据,但我们依然可以拿到最新数据(内存中的 Page 1 就是最新的,Page 2 虽然不是最新的,但是从磁盘读到内存中后,执行了merge操作,内存中的 Page 2 就是最新的了。)

如果此时 MySQL 异常宕机了,比如服务器异常掉电,change buffer 中的数据会不会丢?

change buffer 中的数据分为两部分,一部分是已经merge到ibdata1中的数据,这部分数据已经持久化,不会丢失。另一部分数据,还在 change buffer 中,没有merge 到ibdata1,分 3 种情况:

(1)change buffer 写入数据到内存,redo log 也已经写入(ib-log-filex),但是未 commit,binlog中也没有fsync到磁盘,这部分数据会丢失;

(2)change buffer 写入数据到内存,redo log 也已经写入(ib-log-filex),但是未 commit,binlog 已写入到磁盘,这部分不会多丢失,异常重启后会先从 binlog 恢复 redo log,再从 redo log 恢复 change buffer;

(3)change buffer 写入数据到内存,redo log 和 binlog 都已经fsync,直接从redo log 恢复,不会丢失。

redo log 主要节省的是随机写磁盘的 IO 消耗(转成顺序写),而 change buffer 主要节省的则是随机读磁盘的 IO 消耗

更多MySQL相关教程,请关注PHP中文网!

以上就是MySQL中普通索引和唯一索引的区别详解的详细内容,更多请关注创想鸟其它相关文章!

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。
如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 chuangxiangniao@163.com 举报,一经查实,本站将立刻删除。
发布者:程序猿,转转请注明出处:https://www.chuangxiangniao.com/p/365367.html

赞 (0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
继续学习仿制药
上一篇 2026年11月16日 21:26:11
如何在Java中实现职责明确的业务对象_以面向对象原则拆分逻辑
下一篇 2025年12月2日 06:32:38

相关推荐

  • 两种MySQL查询用户权限的方法

    介绍两种查看mysql用户权限的两种方法 1、 使用MySQL grants命令 mysql> show grants for username@localhost;+—————————————————————…

    2026年11月16日
    000
  • Mac下如何重置mysql的root密码

    Mac下如何重置mysql的root密码Mac下如何重置mysql的root密码Mac下如何重置mysql的root密码Mac下如何重置mysql的root密码

    我的mysql版本 mysql v5.7.9,旧版本请使用: UPDATE mysql.user SET Password=PASSWORD(‘新密码’) WHERE User=’root’; Mac OS X – 重置 MySQL Root密码 密码太多记不住??你是否忘记了Mac O…

    2026年11月16日 • 用户投稿
    100
  • MySQL主从配置详解

    ● 本打算买个云数据,为我的新项目做点安全保障。阿里云,腾讯云转了一圈,两个字太贵。不就数据有备份吗,既然这样那我不如自己来做备份。 ● 家里有2个树莓派直接把mysql备份到他们上就好了,网上有教程,这就开整。在segmentfault mysql 这么一搜好几篇,按照他们一说的那么一做,当然是不…

    2026年11月16日
    000
  • 如何在CentOS上进行数据备份

    centos服务器数据备份指南:确保数据安全 数据备份是维护CentOS服务器数据安全性的关键步骤。本文将介绍几种常用的备份方法、最佳实践以及一个示例备份脚本。 常用备份方法 rsync命令: rsync是一个强大的增量备份工具。命令rsync -avz –delete /path/to/impo…

    2026年11月16日
    000
  • 连接NAS里的MySQL数据库

    连接NAS里的MySQL数据库连接NAS里的MySQL数据库连接NAS里的MySQL数据库连接NAS里的MySQL数据库

    mysql作为开放源代码的数据库管理系统,任何人都可以在generalpubliclicense的许可下下载并根据个性化的需要对其进行修改。您可以在tos 中安装mysql 服务器,以便您通过mysql 高效的管理您的数据。tos 应用中心提供的mysql 服务器版本是mariadb,这是一个被广泛…

    2026年11月16日 • 用户投稿
    000
  • 利用mysql生成唯一序号

    在数据库分表或者程序自己需要唯一id的情况下,我们需要一个生成唯一id的方案。 可以编写一个综合时间和某些特征生成唯一id的程序,也可以考虑使用数据库里自增id的特性来实现这个需求,下面举个mysql的例子。 首先创建一个专门生成id的表,其中id字段是主键,replace_key字段为唯一键。 C…

    2026年11月16日
    100
  • 关于MySQL中SQL Mode的查看与设置

    mysql中sql mode的查看与设置 MySQL可以运行在不同的模式下,而且可以在不同的场景下运行不同的模式,这主要取决于系统变量 sql_mode 的值。本文主要介绍一下这个值的查看与设置,主要在Mac系统下。 对于每个模式的意义和作用,网上很容易找到,本文不做介绍。 按作用区域和时间可分为3…

    2026年11月16日
    100
  • mysql实现每个专业分数段统计人数

    mysql实现每个专业分数段统计人数mysql实现每个专业分数段统计人数mysql实现每个专业分数段统计人数mysql实现每个专业分数段统计人数

    我的表结构 student_info| id |name |profession|score||–|–|–|–||id|姓名|分数|专业| 按分数段统计 400到500人数,300到400人数 腾讯智影-AI数字人 基于AI数字人能力,实现7*24小时AI数字人直播带货,低成本实现直播业务快…

    2026年11月16日 • 用户投稿
    400
  • MySQL数据库事务的机制【总结】

    MySQL数据库事务的机制【总结】MySQL数据库事务的机制【总结】MySQL数据库事务的机制【总结】MySQL数据库事务的机制【总结】

    这几天面试多次被问到了数据库事务机制、隔离级别、乐观锁悲观锁类的问题,之前对这些只能说有所了解,有些概念还停留在记忆层面,没有理解,所以回答的不好。后面翻书学习了下,理解了一些东西,在此做一个记录。   BibiGPT-哔哔终结者 B站视频总结器-一键总结 音视频内容 28 查看详情 什么是事务? …

    2026年11月16日 • 用户投稿
    000
  • 关于MySQL涉及锁的问题详解

    如何并发的访问数据库呢?答案就是加锁。 推荐:《mysql视频教程》 接下来说一下,数据库的锁机制,数据库中都有哪些锁?   首先呢,锁是一种并发控制技术,锁是用来在多个用户同时访问同一个数据的时候保护数据的。 有2种基本的锁类型:   共享(S)锁:多个事务可封锁一个共享页;任何事务都不能修改该页…

    2026年11月16日
    100
  • [深度Liunx系统]安装数据库MySQL和MySQL workbench

    [深度Liunx系统]安装数据库MySQL和MySQL workbench[深度Liunx系统]安装数据库MySQL和MySQL workbench[深度Liunx系统]安装数据库MySQL和MySQL workbench[深度Liunx系统]安装数据库MySQL和MySQL workbench

    一.安装mysql 1.打开命令行 ALT+CTRL+T 2.输入sudo apt-get install mysql-server并按要求输入管理员密码 3.输入apt-get install mysql-client 4.输入sudo apt-get install libmysqlclient…

    2026年11月16日 • 用户投稿
    000
  • 路由器怎么连接_路由器与光猫网线连接方法教程

    路由器怎么连接_路由器与光猫网线连接方法教程路由器怎么连接_路由器与光猫网线连接方法教程路由器怎么连接_路由器与光猫网线连接方法教程路由器怎么连接_路由器与光猫网线连接方法教程

    光猫LAN口连路由器WAN口,通电后进入管理界面设置上网方式、Wi-Fi名称密码,避免IP冲突可改路由器LAN口IP或设为AP模式。 手机wifi优化工具:☞☞☞☞立即下载 电脑wifi修复工具:☞☞☞☞立即下载 路由器连接光猫,说白了,就是把光猫的局域网口(lan口)用网线插到路由器的广域网口(w…

    2026年11月16日 • 用户投稿
    100
  • 如何使用MySQL Sandbox快速部署mysql

    mysql sandbox是一个非常简单快捷部署mysql技术的一个工具套件,它可以让你在同一台机器上,更加快速的无干扰的去达到你的最终目的,比如,作为软件测试人员只是要测试软件系统的良好性不需要在mysql的安装上纠缠过多,不需要对mysql数据库有太多的经验;有的时候我们只是对新版本的一些特性感…

    2026年11月16日
    600
  • 关于一道mysql查询面试题的思考解决过程

    关于一道mysql查询面试题的思考解决过程关于一道mysql查询面试题的思考解决过程关于一道mysql查询面试题的思考解决过程关于一道mysql查询面试题的思考解决过程

    啰嗦(可跳过) 前几天面试,笔试时遇到这道题,读了几遍题目都是懵懵懂懂,“一个段时间内至少N天,这N天中每天的分数总和要大于M”,好绕,最后没有写对。 今天想起来这道题,写出了答案并进行了sql语句的验证。 推荐:《mysql视频教程》,《mysql面试题2020》 问题 某游戏使用mysql数据库…

    2026年11月16日 • 用户投稿
    200
  • 开发过程中mysql常见问题小结

    记录开发过程中遇到mysql相关的问题以及解决方法,长期更新。 远程连接问题 mysql默认连接只能给本地(localhost或者127.0.0.1),如果想让某个ip地址可以访问mysql,需要手动开启这个权限 方法: ● 进入终端 ● 分配新用户 grant all privileges on …

    2026年11月16日
    200
  • 如何使用 MySQL 模糊查询匹配长地址中的镇区?

    mysql 长地址模糊查询匹配镇区 在 mysql 中,通过模糊查询匹配长地址中的镇区时,可以使用 locate() 或 position() 函数。 问题说明: 给定如下表格: id town 1东镇2西镇3南镇4北镇 需要从表中查询出与以下地址中匹配的镇区:“北京市朝阳区东镇幸福小区6幢5楼40…

    2026年11月16日
    000
  • php在centos上的模块如何管理

    在centos上管理php模块,通常是指安装、启用、禁用和卸载php扩展模块。以下是一些常用的方法来管理php模块: 使用YUM(Yellowdog Updater, Modified)包管理器: 在CentOS上,可以使用YUM包管理器来安装和管理PHP扩展。首先,确保已经安装了EPEL(Extr…

    2026年11月16日
    000
  • laravel与thinkphp区别是什么

    laravel与thinkphp区别是什么laravel与thinkphp区别是什么laravel与thinkphp区别是什么laravel与thinkphp区别是什么

    1、渲染模版方式的不同 在Laravel框架里,使用return view()来渲染模版;而ThinkPHP里则使用了$this->display()的方式渲染模版。 2、在实际开发中我们常常遇到这样的问题,就是开发地点不固定。 这就造成了我们需要频繁的更改数据库配置,给开发工作造成了麻烦。T…

    2026年11月16日 • 用户投稿
    000
  • mysql特点是什么

    mysql特点是什么mysql特点是什么mysql特点是什么mysql特点是什么

    mysql是一个关系型数据库管理系统,由瑞典mysql ab 公司开发,属于 oracle 旗下产品。mysql 是最流行的关系型数据库管理系统之一,在 web 应用方面,mysql是最好的 rdbms (relational database management system,关系数据库管理系统…

    2026年11月16日 • 用户投稿
    000
  • mysql使用技巧之行类视图子查询

    mysql使用技巧之行类视图子查询mysql使用技巧之行类视图子查询mysql使用技巧之行类视图子查询mysql使用技巧之行类视图子查询

    查找描述信息中包括robot的电影对应的分类名称以及电影数目,而且还需要该分类对应电影数量>=5部 推荐:《mysql视频教程》 film表为电影表,category表为电影分类表,film_category表为电影表与电影分类表的中间表(多对多的中间表) film表 CREATE TABLE…

    2026年11月16日 • 用户投稿
    100

发表回复

登录后才能评论
关注微信