面试被问到MySQL索引,别再说不了解了,看完这篇你可以说个不停

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 面试被问到MySQL索引,别再说不了解了,看完这篇你可以说个不停

@TOC

0.写在前面

文章中包含了:
1.什么是索引
2.索引的数据结构,以及各自的使用场景
3.为什么要设置主键自增?
4.基于主键索引和普通索引的查询有什么区别?
5.什么是回表
6.InnoDB 的索引模型

1.为什么要使用索引

索引的使用就是为了提高查询数据的效率,就像书的目录一样

2.常见的索引模型

哈希表
哈希表是一种以键值存储的数据结构,只需要通过key就可以找到对应的值
思路:把值放在数组里,用一个哈希函数把key换算成一个确定的位置,然后把value放在数组的这个位置
当多个key经过哈希函数换算,会出现同一个值,即碰撞,一般通过拉链表解决。

使用哈希表的缺陷是,经过哈希算法算出的位置是随机的,做区间查找就得将表全部扫描
所以哈希表这种结构就适合做等值查询的场景,比如Memcached及其它一些nosql引擎

有序数组

有序数组在等值查询和范围查询中的性能就非常优秀了
但是这也是仅仅对于查询来看,如果要更新的时候,就得挪动后面的记录,成本太高了

所以有序数组只适合用于静态存储索引,也就是那些不在会被修改的历史数据

二叉搜索树

二叉搜索树的特点是:每个节点的左儿子小于父节点,父节点又小于右儿子。这样查找时间复杂度是O(log(n))级别
树可以有二叉,也可以有多叉。多叉树就是每个节点有多个儿子,儿子之间的大小保证从左到右递增。

二叉树是搜索效率最高的,但是实际上大多数的数据库存储却并不使用二叉树。其原因是,索引不止存在内存中,还要写到磁盘上。为了让一个查询尽量少地读磁盘,就必须让查询过程访问尽量少的数据块。那么,我们就不应该使用二叉树,而是要使用“N叉”树。这里,“N叉”树中的“N”取决于数据块的大小。

InnoDB 的索引模型

在InnoDB中,表都是根据主键顺序以索引的形式存放的,这种存储方式的表称为索引组织表。又因为前面我们提到的,InnoDB使用了B+树索引模型,所以数据都是存储在B+树中的。每一个索引在InnoDB里面对应一棵B+树。

  • 假设有一个主键为id的表,字段grade上有索引
mysql> create table Student(
id int primary key, 
grade int not null, 
name varchar(16),
index (k))engine=InnoDB;

表中t1~t5的(ID,grade)值分别为(1,70)、(2,80)、(3,90)、(4,100)和(5,110),
此时两棵索引树的示例示意图如下。

从图中不难看出,根据叶子节点的内容,索引类型分为主键索引和非主键索引
主键索引的叶子节点存的是整行数据。在InnoDB里,主键索引也被称为聚簇索引(clustered index)。
非主键索引的叶子节点内容是主键的值。在InnoDB里,非主键索引也被称为二级索引(secondary index)。

那么基于主键索引和普通索引的查询有什么区别?

如果语句是select from T where ID=500,即主键查询方式,则只需要搜索ID这棵B+树;
如果语句是select
from T where k=5,即普通索引查询方式,则需要先搜索k索引树,得到ID的值为500,再到ID索引树搜索一次。这个过程称为回表。

也就是说,基于非主键索引的查询需要多扫描一棵索引树。因此,我们在应用中应该尽量使用主键查询。

3.索引维护

B+树为了维护索引有序性,在插入新值的时候需要做必要的维护。根据b+树的性质,当插入的值比之前的值都大时,只需在末尾加一个就行了,如果新插入的值,在之前插入值的中间,就相对麻烦了,需要逻辑上挪动后面的数据,空出位置。而更糟的情况是,如果最后一个值所在的数据页已经满了,根据B+树的算法,这时候需要申请一个新的数据页,然后挪动部分数据过去。这个过程称为页分裂。在这种情况下,性能自然会受影响。
除了性能外,页分裂操作还影响数据页的利用率。原本放在一个页的数据,现在分到两个页中,整体空间利用率降低大约50%。
当然有分裂就有合并。当相邻两个页由于删除了数据,利用率很低之后,会将数据页做合并。合并的过程,可以认为是分裂过程的逆过程。、

如果用主键自增
插入新记录的时候可以不指定ID的值,系统会获取当前ID最大值加1作为下一条记录的ID值。
也就是说,自增主键的插入数据模式,正符合了我们前面提到的递增插入的场景。每次插入一条新记录,都是追加操作,都不涉及到挪动其他记录,也不会触发叶子节点的分裂。
而有业务逻辑的字段做主键,则往往不容易保证有序插入,这样写数据成本相对较高。

4.回表?举例子。

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
15天前
|
存储 关系型数据库 MySQL
美团面试:MySQL为什么 不用 Docker部署?
45岁老架构师尼恩在读者交流群中分享了关于“MySQL为什么不推荐使用Docker部署”的深入分析。通过系统化的梳理,尼恩帮助读者理解为何大型MySQL数据库通常不使用Docker部署,主要涉及性能、管理复杂度和稳定性等方面的考量。文章详细解释了有状态容器的特点、Docker的资源隔离问题以及磁盘IO性能损耗,并提供了小型MySQL使用Docker的最佳实践。此外,尼恩还介绍了Share Nothing架构的优势及其应用场景,强调了配置管理和数据持久化的挑战。最后,尼恩建议读者参考《尼恩Java面试宝典PDF》以提升技术能力,更好地应对面试中的难题。
|
3天前
|
SQL 存储 关系型数据库
MySQL原理简介—9.MySQL索引原理
本文详细介绍了MySQL索引的设计与使用原则,涵盖磁盘数据页的存储结构、页分裂机制、主键索引设计及查询过程、聚簇索引和二级索引的原理、B+树索引的维护、联合索引的使用规则、SQL排序和分组时如何利用索引、回表查询对性能的影响以及索引覆盖的概念。此外还讨论了索引设计的案例,包括如何处理where筛选和order by排序之间的冲突、低基数字段的处理方式、范围查询字段的位置安排,以及通过辅助索引来优化特定查询场景。总结了设计索引的原则,如尽量包含where、order by、group by中的字段,选择离散度高的字段作为索引,限制索引数量,并针对频繁查询的低基数字段进行特殊处理等。
MySQL原理简介—9.MySQL索引原理
|
1天前
|
存储 关系型数据库 MySQL
MySQL底层概述—6.索引原理
本文详细回顾了:索引原理、二叉查找树、平衡二叉树(AVL树)、红黑树、B-Tree、B+Tree、Hash索引、聚簇索引与非聚簇索引。
MySQL底层概述—6.索引原理
|
1月前
|
SQL 存储 关系型数据库
MySQL秘籍之索引与查询优化实战指南
最左前缀原则。不冗余原则。最大选择性原则。所谓前缀索引,说白了就是对文本的前几个字符建立索引(具体是几个字符在建立索引时去指定),比如以产品名称的前 10 位来建索引,这样建立起来的索引更小,查询效率更快!
111 22
 MySQL秘籍之索引与查询优化实战指南
|
20天前
|
存储 关系型数据库 MySQL
MySQL索引学习笔记
本文深入探讨了MySQL数据库中慢查询分析的关键概念和技术手段。
|
23天前
|
存储 关系型数据库 MySQL
浅入浅出——MySQL索引
本文介绍了数据库索引的概念和各种索引结构,如哈希表、B+树、InnoDB引擎的索引运作原理等。还分享了覆盖索引、联合索引、最左前缀原则等优化技巧,以及如何避免索引误用,提高数据库性能。
|
1月前
|
存储 SQL 关系型数据库
MySQL 面试题
MySQL 的一些基础面试题
|
1月前
|
存储 关系型数据库 MySQL
MySQL中为什么要使用索引合并(Index Merge)?
通过这些内容的详细介绍和实际案例分析,希望能帮助您深入理解索引合并及其在MySQL中的
122 10
|
20天前
|
关系型数据库 MySQL 数据库连接
数据库连接工具连接mysql提示:“Host ‘172.23.0.1‘ is not allowed to connect to this MySQL server“
docker-compose部署mysql8服务后,连接时提示不允许连接问题解决
|
6天前
|
关系型数据库 MySQL 数据库
Docker Compose V2 安装常用数据库MySQL+Mongo
以上内容涵盖了使用 Docker Compose 安装和管理 MySQL 和 MongoDB 的详细步骤,希望对您有所帮助。
73 42