返回顶部
首页 > 资讯 > 数据库 >MySQL 普通索引和唯一索引的区别详解
  • 640
分享到

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

MySQL普通索引MySQL唯一索引 2022-05-25 08:05:20 640人浏览 八月长安
摘要

1 概念区分 普通索引和唯一索引 普通索引可重复,唯一索引和主键一样不能重复。 唯一索引可作为数据的一个合法验证手段,例如学生表的身份证号码字段,我们人为规定该字段不得重复,那么就使用唯一索引。(一般

1 概念区分

普通索引可重复,唯一索引和主键一样不能重复。 唯一索引可作为数据的一个合法验证手段,例如学生表的身份证号码字段,我们人为规定该字段不得重复,那么就使用唯一索引。(一般设置学号字段为主键)

  • 主键和唯一索引

主键保证数据库里面的每一行都是唯一的,比如身份证,学号等,在表中要求唯一,不重复。唯一索引的作用跟主键的作用一样。 不同的是,在一张表里面只能有一个主键,主键不能为空,唯一索引可以有多个,唯一索引可以有一条记录为空,即保证跟别人不一样就行。 比如学生表,在学校里面一般用学号做主键,身份证则弄成唯一索引;而到了教育局,他们就把身份证号弄成主键,学号换成了唯一索引。 选谁做表的主键,要看实际应用,主键不能为空。

2 案例引入

某居民系统,每人有唯一身份证号。如果系统需要按身份证号查姓名,就会执行类似如下sql


select name from CUser where id_card = 'ooxx';

然后你肯定会在id_card字段建索引。但id_card字段较大,不推荐将其做主键。于是现有俩选择:

  1. 给id_card字段创建唯一索引
  2. 创建一个普通索引

假定业务代码已保证不会写入重复的身份证号,这两个选择逻辑上都正确。但从性能角度考虑,唯一索引还是普通索引呢?

再看如下案例:假设字段 k 上的值都不重复。

  • InnoDB的索引组织结构:

接下来分析性能。

3 查询性能


select id from T where k=4

通过B+树从树根开始层序遍历到叶节点,可认为数据页内部是通过二分法搜索。

  • 普通索引,查找到满足条件的第一个记录(4,400)后,需查找下个记录,直到碰到第一个不满足k=4的记录
  • 唯一索引,由于索引具备唯一性,查找到第一个满足条件的记录后,就会停止检索

看起来性能差距很微小。

InnoDB数据按数据页单位读写。即读一条记录时,并非将该一个记录从磁盘读出,而以页为单位,将其整体读入内存。

因此普通索引,要多做一次“查找和判断下一条记录”的操作,也就一次指针寻找和一次计算。 如果k=4记录恰为该数据页最后一个记录,那么要取下个记录,还得读取下个数据页,操作稍微复杂。 对整型字段,一个数据页可存近千key,因此这种情况概率其实也很低。因此计算平均性能差异时,可认为该操作成本对现在CPU开销忽略不计。

我们知道 Mysql 有 change buffer。

4 更新性能

现在来看往表中插入一个新记录(4,400),InnoDB会做什么?

需要区分该记录要更新的目标页是否在内存:

4.1 在内存

  • 唯一索引

找到3和5之间位置,判断到没有冲突,插入值,语句执行结束。

  • 普通索引

找到3和5之间位置,插入值,语句执行结束。

普通索引和唯一索引对更新语句性能影响的差别,只是一个判断,耗费微小CPU时间。

4.2 不在内存

  • 唯一索引

需将数据页读入内存,判断到没有冲突,插入值,语句执行结束。

  • 普通索引

将更新记录在change buffer,语句执行结束。

将数据从磁盘读入内存涉及随机io访问,是数据库里面成本最高操作之一。而change buffer减少随机磁盘访问,所以更新性能提升明显。

5 实践中的索引选择

普通索引和唯一索引究竟如何抉择?这两类索引在查询性能上没差别,主要考虑对更新性能影响。所以,推荐尽量选择普通索引。

如果所有更新后面,都紧跟对该记录的查询,那么该关闭change buffer。 而在其他情况下,change buffer都能提升更新性能。 普通索引和change buffer的配合使用,对于数据量大的表的更新优化还是很明显的。

在使用机械硬盘时,change buffer机制的收效非常显著。 所以,当你有一个类似“历史数据”的库,并且出于成本考虑用机械硬盘时,应该关注这些表里的索引,尽量使用普通索引,把change buffer 开大,确保“历史数据”表的数据写速度。

6 change buffer 和 redo log

WAL 提升性能的核心机制,也是尽量减少随机读写,这两个概念易混淆。 所以,这里我把它们放到了同一个流程里来说明区分。

6.1 插入流程


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

假设当前k索引树的状态,查找到位置后,k1所在数据页在内存(InnoDB buffer pool),k2数据页不在内存。

  • 带change buffer的更新流程图,图中两个箭头都是后台操作,不影响更新响应。

该更新做了如下操作:

  1. Page1在内存,直接更新内存
  2. Page2不在内存,就在change buffer区,缓存下“往Page2插一行记录”的信息
  3. 将前两个动作记入redo log

之后事务完成。执行该更新语句成本很低,只写两处内存,然后写一处磁盘(前两次操作合在一起写了一次磁盘),还是顺序写。

6.2 怎么处理之后的读请求?


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

读语句紧随更新语句,内存中的数据都还在,此时这俩读操作就与系统表空间和 redo log 无关。所以在图中就没画这俩。

  • 带change buffer的读过程

读Page1时,直接从内存返回。 WAL之后如果读数据,是不是一定要读盘,是不是一定要从redo log里面把数据更新以后才可以返回?其实不用。 看上图状态,虽然磁盘上还是之前数据,但这里直接从内存返回结果,结果正确。

要读Page2时,需把Page2从磁盘读入内存,然后应用change buffer里面的操作日志,生成一个正确版本并返回结果。 可见直到需读Page2时,该数据页才被读入内存。

所以,要简单对比这俩机制对更新性能影响

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

总结

由于唯一索引用不了change buffer的优化机制,因此如果业务可以接受,从性能角度,推荐优先考虑非唯一索引。

7.1 关于到底是否使用唯一索引

主要纠结在“业务可能无法确保”。本文前提是“业务代码已经保证不会写入重复数据”下,讨论性能问题。

如果业务不能保证,或者业务就是要求数据库来做约束,那么没得选,必须创建唯一索引。这种情况下,本文意义在于,如果碰上大量插入数据慢、内存命中率低时,多提供一个排查思路。
然后,在一些“归档库”的场景,可考虑使用唯一索引的。比如,线上数据只需保留半年,然后历史数据保存在归档库。此时,归档数据已是确保没有唯一键冲突。要提高归档效率,可考虑把表的唯一索引改普通索引。

7.2 如果某次写入使用change buffer,之后主机异常重启,是否会丢失change buffer的数据?

不会丢失。 虽然是只更新内存,但在事务提交时,我们把change buffer的操作也记录到redo log,所以崩溃恢复时,change buffer也能找回。

7.3 merge的过程是否会把数据直接写回磁盘?

merge执行流程

  1. 从磁盘读入数据页到内存(老版本数据页)
  2. 从change buffer找出该数据页的change buffer 记录(可能有多个),依次应用,得到新版数据页
  3. 写redo log

该redo log包含数据的变更和change buffer的变更

至此merge过程结束。 这时,数据页和内存中change buffer对应磁盘位置都尚未修改,是脏页,之后各自刷回自己物理数据,就是另外一过程。

问题思考

在构造第一个例子的过程,通过session A的配合,让session B删除数据后又重新插入一遍数据,然后就发现explain结果中,rows字段从10001变成37000多。 而如果没有session A的配合,只是单独执行delete from t 、call idata()、explain这三句话,会看到rows字段其实还是10000左右。这是什么原因呢?

如果没有复现,检查

  • 隔离级别是不是RR(Repeatable Read,可重复读)
  • 创建的表t是不是InnoDB引擎

为什么经过这个操作序列,explain的结果就不对了? delete 语句删掉了所有的数据,然后再通过call idata()插入了10万行数据,看上去是覆盖了原来10万行。 但是,session A开启了事务并没有提交,所以之前插入的10万行数据是不能删除的。这样,之前的数据每行数据都有两个版本,旧版本是delete之前数据,新版本是标记deleted的数据。 这样,索引a上的数据其实有两份。

然后你会说,不对啊,主键上的数据也不能删,那没有使用force index的语句,使用explain命令看到的扫描行数为什么还是100000左右?(潜台词,如果这个也翻倍,也许优化器还会认为选字段a作为索引更合适) 是的,不过这个是主键,主键是直接按照表的行数来估计的。而表的行数,优化器直接用的是show table status的值。 大家的机器如果IO能力比较差的话,做这个验证的时候,可以把innodb_flush_log_at_trx_commit sync_binlog 都设置成0。

以上就是mysql 普通索引和唯一索引的区别详解的详细内容,更多关于MySQL 普通索引和唯一索引的资料请关注自学编程网其它相关文章!

您可能感兴趣的文档:

--结束END--

本文标题: MySQL 普通索引和唯一索引的区别详解

本文链接: https://lsjlt.com/news/10194.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com    QQ/279061341

猜你喜欢
  • MySQL 普通索引和唯一索引的区别详解
    1 概念区分 普通索引和唯一索引 普通索引可重复,唯一索引和主键一样不能重复。 唯一索引可作为数据的一个合法验证手段,例如学生表的身份证号码字段,我们人为规定该字段不得重复,那么就使用唯一索引。(一般...
    99+
    2022-05-25
    MySQL 普通索引 MySQL 唯一索引
  • MySQL普通索引和唯一索引的深入讲解
    场景 1、维护一个市民系统,有一个字段为身份证号 2、业务代码能保证不会写入两个重复的身份证号(如果业务无法保证,可以依赖数据库的唯一索引来进行约束) 3、常用SQL查询语句:SELECT n...
    99+
    2024-04-02
  • MySQL唯一索引和普通索引选哪个?
    想象这样一个场景,在设计一张用户表时,每人的身份证号是唯一的,需要搜索。但由于身份证号字段较大,不好将其作为主键。在业务代码已经保证插入身份证唯一的情况下,可以选择建立唯一索引和普通索引,这时该如何选择呢?接下来,将...
    99+
    2022-05-28
    MySQL 唯一索引 MySQL 普通索引
  • 普通索引与唯一索引在MySQL 中有什么区别
    这篇文章给大家介绍普通索引与唯一索引在MySQL 中有什么区别,内容非常详细,感兴趣的小伙伴们可以参考借鉴,希望对大家能有所帮助。1 概念区分普通索引和唯一索引普通索引可重复,唯一索引和主键一样不能重复。 唯一索引可作为数据的一个合法验证手...
    99+
    2023-06-06
  • mysql联合索引和普通索引的区别
            MySQL中,联合索引和普通索引都是用于加速查询的索引类型。它们之间的区别在于索引的列数和列的顺序。         普通索引只对单个列进行索引,而联合索引则同时对多个列进行索引,这些列可以按照特定的顺序组合在一起。例如,可...
    99+
    2023-09-07
    mysql 数据库 java
  • 普通索引和唯一索引的执行过程
    这里普通索引和唯一索引的情况有所不同 查询过程 对于普通索引来说,查找到满足条件的第一个记录后,需要查找下一个记录, 直到碰到第一个不满足条件的记录。 对于唯一索引来说,由于索引定义了唯一性,查找到第一个满足条件的记录后,就会停止继续检索 ...
    99+
    2016-04-05
    普通索引和唯一索引的执行过程
  • MySQL---单列索引(包括普通索引、唯一索引、主键索引)、组合索引、全文索引。
    1. 索引 索引是通过某种算法,构建出一个数据模型,用于快速找出在某个列中有一特定值的行,不使用索 引,MySQL必须从第一条记录开始读完整个表,直到找出相关的行,表越大,查询数据所花费的 时间就越多,如果表中查询的列有一个索引,MySQL...
    99+
    2023-09-21
    数据库 mysql sql
  • Mysql 索引(三)—— 不同索引的创建方式(主键索引、普通索引、唯一键索引)
    了解了主键索引的底层原理,主键索引其实就是根据主键字段建立相关的数据结构(B+树),此后在使用主键字段作为条件查询时,会直接根据主键查找B+树的叶子结点。除了主键索引外,普通索引和唯一键索引也是如此,只不过普通索引要稍微绕一点,下面会具体介...
    99+
    2023-09-12
    mysql 数据库 java
  • mysql主键索引和普通索引之间有什么区别
    这篇文章主要介绍mysql主键索引和普通索引之间有什么区别,文中介绍的非常详细,具有一定的参考价值,感兴趣的小伙伴们一定要看完!索引用来快速地寻找那些具有特定值的记录,所有MySQL索引都以B-树的形式保存...
    99+
    2024-04-02
  • mysql唯一索引和主键的区别是什么
    本篇内容介绍了“mysql唯一索引和主键的区别是什么”的有关知识,在实际案例的操作过程中,不少人都会遇到这样的困境,接下来就让小编带领大家学习一下如何处理这些情况吧!希望大家仔细阅读,能够学有所成! ...
    99+
    2024-04-02
  • MySQL中的聚簇索引、非聚簇索引、联合索引和唯一索引详细介绍
    目录一、索引类型二、聚簇索引和非聚簇索引三、覆盖索引四、联合索引最左前缀匹配原则索引下推五、唯一索引一、索引类型 索引根据底层实现可分为B-Tree索引和哈希索引,大部分时候我们使用的都是B-Tree索引,因为它良好的性...
    99+
    2023-04-19
    MySQL索引 MySQL聚簇索引 MySQL非聚簇索引 MySQL联合索引 MySQL唯一索引
  • MySQL中B树索引和B+树索引的区别详解
    目录1. 多路搜索树2. B树-多路平衡搜索树3. B树索引4. B+树索引总结如果用树作为索引的数据结构,每查找一次数据就会从磁盘中读取树的一个节点,也就是一页,而二叉树的每个节点...
    99+
    2024-04-02
  • MySQL中复合索引和覆盖索引的区别详解
    目录前言准备复合索引覆盖索引总结前言准备 我们先准备一张表和几个字段,方便介绍覆盖索引和复合索引。 创建一个user表,表中有id、name、school、age字段。 字段名字段类型idintnamevarcharsc...
    99+
    2023-11-23
    MySQL 复合索引 MySQL 覆盖索引
  • MySQL中的聚簇索引、非聚簇索引、联合索引和唯一索引是什么
    今天小编给大家分享一下MySQL中的聚簇索引、非聚簇索引、联合索引和唯一索引是什么的相关知识点,内容详细,逻辑清晰,相信大部分人都还太了解这方面的知识,所以分享这篇文章给大家参考一下,希望大家阅读完这篇文章...
    99+
    2023-04-21
    mysql
  • MySQL的B+树索引和hash索引的区别
    简述一下索引: 索引是数据库表中一列或多列的值进行排序的一种数据结构;索引分为聚集索引和非聚集索引,聚集索引查询类似书的目录,快速定位查找的数据,非聚集索引查询一般需要再次回表查询一次,如果不使用索引就会进行全表扫描;还有可以进行多字段...
    99+
    2016-10-05
    MySQL的B+树索引和hash索引的区别
  • MySQL索引 VS ElasticSearch索引的区别
    这篇文章主要介绍了MySQL索引 VS ElasticSearch索引的区别,具有一定借鉴价值,需要的朋友可以参考下。希望大家阅读完这篇文章后大有收获。下面让小编带着大家一起了解一下。前言这段时间在维护产品...
    99+
    2024-04-02
  • MySQL聚簇索引和非聚簇索引的区别详情
    目录聚簇索引非聚簇索引总结前言: 在 mysql 默认引擎 InnoDB 中,索引大致可分为两类:聚簇索引和非聚簇索引,它们的区别也是常见的面试题,所以我们今天就来盘它们。 聚簇索引 聚簇索引(Clustered Ind...
    99+
    2022-06-14
    MySQL聚簇索引 MySQL非聚簇索引
  • MYSQL联合唯一索引
    https://blog.csdn.net/weistin/article/details/79698996...
    99+
    2019-08-28
    MYSQL联合唯一索引
  • MySQL单列索引和组合索引的区别
    这篇文章主要讲解了“MySQL单列索引和组合索引的区别”,文中的讲解内容简单清晰,易于学习与理解,下面请大家跟着小编的思路慢慢深入,一起来研究和学习“MySQL单列索引和组合索引的区别”吧!  MySQL单...
    99+
    2024-04-02
  • MySQL btree索引与hash索引区别
    在MySQL中,大多数索引(如 PRIMARY KEY,UNIQUE,INDEX和FULLTEXT)都是在BTREE中存储,但使用memory引擎可以选择BTREE索引或者HASH索引,两种不同类型的索引各自有其不同...
    99+
    2022-05-14
    MySQL btree索引 MySQL hash索引 MySQL 索引
软考高级职称资格查询
编程网,编程工程师的家园,是目前国内优秀的开源技术社区之一,形成了由开源软件库、代码分享、资讯、协作翻译、讨论区和博客等几大频道内容,为IT开发者提供了一个发现、使用、并交流开源技术的平台。
  • 官方手机版

  • 微信公众号

  • 商务合作