MySQL普通索引和唯一索引到底什么区别?(上)

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用系列 2核4GB
简介: MySQL普通索引和唯一索引到底什么区别?(上)

1 概念区分

普通索引 V.S 唯一索引

普通索引可重复,唯一索引和主键一样不能重复。

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

主键 V.S 唯一索引

主键保证DB的每一行都是唯一、不重复,比如身份证,学号等,不重复。

唯一索引的作用跟主键一样。

不同的是,在一张表里面只能有一个主键,主键不能为空,但唯一索引可以有多个。唯一索引可以有一条记录为null。

比如学生表:

  • 在学校,一般用学号做主键,身份证号作为唯一索引
  • 在教育局,就把身份证号弄成主键,学号作为唯一索引

所以选谁做主键,取决于业务需求。

2 案例

某居民系统,每人有唯一身份证号。若系统要按身份证号查姓名:

select name from CUser where id_card = 'ooxx';

估计你会在id_card建索引。但id_card字段较大,不推荐做主键。于是现在有如下选择:


在id_card创建唯一索引

创建一个普通索引

假定业务代码已经确保不会写入重复身份证号,这两个选择逻辑上都是正确的。

性能优化角度考虑,选择唯一索引还是普通索引呢?


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


InnoDB索引结构

image.png

接下来分析性能。

3 查询性能

select id from T where k=4

通过B+树从root开始层序遍历到叶节点,数据页内部通过二分搜索:


普通索引

查找到满足条件的第一个记录(4,400)后,需查找下个记录,直到碰到第一个不满足k=4的记录

唯一索引

由于索引具备唯一性,查到第一个满足条件的,就会停止搜索

看起来性能差距很小。


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


所以普通索引,多了一次“查找和判断下一条记录”的操作,即一次指针寻找和一次计算。

若k=4记录恰为该数据页的最后一个记录,则此时要取下个记录,还得读取下个数据页。

对整型字段,一个数据页可存近千个key,因此这种情况概率其实也很低。因此计算平均性能差异时,可认为该操作成本对现在CPU开销忽略不计。

4 更新性能

往表中插入一个新记录(4,400),InnoDB会有什么反应?

这要看该记录要更新的目标页是否在内存:

在内存

  • 普通索引
    找到3和5之间的位置,插入值,结束。
  • 唯一索引
    找到3和5之间的位置,判断到没有冲突,插入值,结束。只是一个判断的差别,耗费微小CPU时间。

不在内存

  • 唯一索引
    将数据页读入内存,判断到没有冲突,插入值,结束。
  • 普通索引
    将更新记录在change buffer,结束。
  • 将数据从磁盘读入内存涉及随机I/O访问,是DB里成本最高的操作之一。而change buffer可以减少随机磁盘访问,所以更新性能提升明显。

5 索引选择的最佳实践

普通索引、唯一索引在查询性能上无差别,主要考虑更新性能。所以,推荐尽量选择普通索引。

若所有更新后面,都紧跟对该记录的查询,那就该关闭change buffer。其它情况下,change buffer都能提升更新性能。

普通索引和change buffer的配合使用,对于数据量大的表的更新优化还是明显的。


在使用机械硬盘时,change buffer的收益也很大。


所以,当你有个类似“历史数据”的库,并且出于成本考虑用机械硬盘,应该关注这些表里的索引,尽量使用普通索引,把change buffer 开大,确保“历史数据”表的数据写性能。

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
1月前
|
缓存 关系型数据库 MySQL
MySQL索引策略与查询性能调优实战
在实际应用中,需要根据具体的业务需求和查询模式,综合运用索引策略和查询性能调优方法,不断地测试和优化,以提高MySQL数据库的查询性能。
164 66
|
6天前
|
SQL 关系型数据库 MySQL
深入解析MySQL的EXPLAIN:指标详解与索引优化
MySQL 中的 `EXPLAIN` 语句用于分析和优化 SQL 查询,帮助你了解查询优化器的执行计划。本文详细介绍了 `EXPLAIN` 输出的各项指标,如 `id`、`select_type`、`table`、`type`、`key` 等,并提供了如何利用这些指标优化索引结构和 SQL 语句的具体方法。通过实战案例,展示了如何通过创建合适索引和调整查询语句来提升查询性能。
55 9
|
17天前
|
存储 SQL 关系型数据库
mysql 的ReLog和BinLog区别
MySQL中的重做日志和二进制日志是确保数据库稳定性和可靠性的关键组件。重做日志主要用于事务的持久性和原子性,通过记录数据页的物理修改信息来恢复未提交的事务;而二进制日志记录SQL语句的逻辑变化,支持数据复制、恢复和审计。两者在写入时机、存储方式及配置参数等方面存在显著差异。
|
10天前
|
缓存 关系型数据库 MySQL
MySQL 索引优化以及慢查询优化
通过本文的介绍,希望您能够深入理解MySQL索引优化和慢查询优化的方法,并在实际应用中灵活运用这些技术,提升数据库的整体性能。
51 18
|
3天前
|
存储 Oracle 关系型数据库
索引在手,查询无忧:MySQL索引简介
MySQL 是一款广泛使用的关系型数据库管理系统,在2024年5月的DB-Engines排名中得分1084,仅次于Oracle。本文介绍MySQL索引的工作原理和类型,包括B+Tree、Hash、Full-text索引,以及主键、唯一、普通索引等,帮助开发者优化查询性能。索引类似于图书馆的分类系统,能快速定位数据行,极大提高检索效率。
27 8
|
9天前
|
缓存 关系型数据库 MySQL
MySQL 索引优化以及慢查询优化
通过本文的介绍,希望您能够深入理解MySQL索引优化和慢查询优化的方法,并在实际应用中灵活运用这些技术,提升数据库的整体性能。
17 7
|
8天前
|
缓存 关系型数据库 MySQL
MySQL 索引优化与慢查询优化:原理与实践
通过本文的介绍,希望您能够深入理解MySQL索引优化与慢查询优化的原理和实践方法,并在实际项目中灵活运用这些技术,提升数据库的整体性能。
32 5
|
12天前
|
存储 关系型数据库 MySQL
Mysql索引:深入理解InnoDb聚集索引与MyisAm非聚集索引
通过本文的介绍,希望您能深入理解InnoDB聚集索引与MyISAM非聚集索引的概念、结构和应用场景,从而在实际工作中灵活运用这些知识,优化数据库性能。
66 7
|
18天前
|
存储 关系型数据库 MySQL
MySQL引擎InnoDB和MyISAM的区别?
InnoDB是MySQL默认的事务型存储引擎,支持事务、行级锁、MVCC、在线热备份等特性,主索引为聚簇索引,适用于高并发、高可靠性的场景。MyISAM设计简单,支持压缩表、空间索引,但不支持事务和行级锁,适合读多写少、不要求事务的场景。
50 9
|
28天前
|
关系型数据库 MySQL Java
MySQL索引优化与Java应用实践
【11月更文挑战第25天】在大数据量和高并发的业务场景下,MySQL数据库的索引优化是提升查询性能的关键。本文将深入探讨MySQL索引的多种类型、优化策略及其在Java应用中的实践,通过历史背景、业务场景、底层原理的介绍,并结合Java示例代码,帮助Java架构师更好地理解并应用这些技术。
27 2