大家好,又见面了,我是全栈君。
❝ 说说你对 MySQL 索引的理解? 数据库索引的原理,为什么要用 B+树,为什么不用二叉树? 聚集索引与非聚集索引的区别? InnoDB引擎中的索引策略,了解过吗? 创建索引的方式有哪些? 聚簇索引/非聚簇索引,mysql索引底层实现,为什么不用B-tree,为什么不用hash,叶子结点存放的是数据还是指向数据的内存地址,使用索引需要注意的几个地方?
左边的数据表,一共有两列七条记录,最左边的是数据记录的物理地址。为了加快Col2的查找,可以维护一个右边所示的二叉查找树,每个节点分别包含索引键值,和一个指向对应数据记录物理地址的指针,这样就可以运用二叉查找在一定的复杂度内获取到对应的数据,从而快速检索出符合条件的记录。
CREATE [UNIQUE] INDEX indexName ON mytable(username(length));
如果是CHAR,VARCHAR类型,length可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定 length。
ALTER table tableName ADD [UNIQUE] INDEX indexName(columnName)
DROP INDEX [indexName] ON mytable;
SHOW INDEX FROM table_name\G
–可以通过添加 \G 来格式化输出信息。
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list):
该语句添加一个主键,这意味着索引值必须是唯一的,且不能为NULL。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list
这条语句创建索引的值必须是唯一的(除了NULL外,NULL可能会出现多次)。
ALTER TABLE tbl_name ADD INDEX index_name (column_list)
添加普通索引,索引值可出现多次。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)
该语句指定了索引为 FULLTEXT ,用于全文索引。
❝ 为什么MySQL 索引中用B+tree,不用B-tree 或者其他树,为什么不用 Hash 索引 聚簇索引/非聚簇索引,MySQL 索引底层实现,叶子结点存放的是数据还是指向数据的内存地址,使用索引需要注意的几个地方? 使用索引查询一定能提高查询的性能吗?为什么?
首先要明白索引(index)是在存储引擎(storage engine)层面实现的,而不是server层面。不是所有的存储引擎都支持所有的索引类型。即使多个存储引擎支持某一索引类型,它们的实现和行为也可能有所差别。
MyISAM 和 InnoDB 存储引擎,都使用 B+Tree的数据结构,它相对与 B-Tree结构,所有的数据都存放在叶子节点上,且把叶子节点通过指针连接到一起,形成了一条数据链表,以加快相邻数据的检索效率。
先了解下 B-Tree 和 B+Tree 的区别(下节补充)
MyISAM引擎的索引文件和数据文件是分离的。MyISAM引擎索引结构的叶子节点的数据域,存放的并不是实际的数据记录,而是数据记录的地址。索引文件与数据文件分离,这样的索引称为”非聚簇索引“。MyISAM的主索引与辅助索引区别并不大,只是主键索引不能有重复的关键字。
在MyISAM中,索引(含叶子节点)存放在单独的.myi文件中,叶子节点存放的是数据的物理地址偏移量(通过偏移量访问就是随机访问,速度很快)。
主索引是指主键索引,键值不可能重复;辅助索引则是普通索引,键值可能重复。
通过索引查找数据的流程:先从索引文件中查找到索引节点,从中拿到数据的文件指针,再到数据文件中通过文件指针定位了具体的数据。辅助索引类似。
InnoDB引擎索引结构的叶子节点的数据域,存放的就是实际的数据记录(对于主索引,此处会存放表中所有的数据记录;对于辅助索引此处会引用主键,检索的时候通过主键到主键索引中找到对应数据行),或者说,InnoDB的数据文件本身就是主键索引文件,这样的索引被称为“聚簇索引”,一个表只能有一个聚簇索引。
我们知道InnoDB索引是聚集索引,它的索引和数据是存入同一个.idb文件中的,因此它的索引结构是在同一个树节点中同时存放索引和数据,如下图中最底层的叶子节点有三行数据,对应于数据表中的id、stu_id、name数据项。
在Innodb中,索引分叶子节点和非叶子节点,非叶子节点就像新华字典的目录,单独存放在索引段中,叶子节点则是顺序排列的,在数据段中。Innodb的数据文件可以按照表来切分(只需要开启innodb_file_per_table)
,切分后存放在xxx.ibd
中,默认不切分,存放在xxx.ibdata
中。
这次我们以示例中学生表中的name列建立辅助索引,它的索引结构跟主键索引的结构有很大差别,在最底层的叶子结点有两行数据,第一行的字符串是辅助索引,按照ASCII码进行排序,第二行的整数是主键的值。
这就意味着,对name列进行条件搜索,需要两个步骤:
① 在辅助索引上检索name,到达其叶子节点获取对应的主键;
② 使用主键在主索引上再进行对应的检索操作
这也就是所谓的“回表查询”
InnoDB 索引结构需要注意的点
正如我们上面介绍 InnoDB 存储结构,索引与数据是共同存储的,不管是主键索引还是辅助索引,在查找时都是通过先查找到索引节点才能拿到相对应的数据,如果我们在设计表结构时没有显式指定索引列的话,MySQL 会从表中选择数据不重复的列建立索引,如果没有符合的列,则 MySQL 自动为 InnoDB 表生成一个隐含字段作为主键,并且这个字段长度为6个字节,类型为整型。
❝ 那为什么推荐使用整型自增主键而不是选择UUID?
❝ 为什么非主键索引结构叶子节点存储的是主键值?
保证数据一致性和节省存储空间,可以这么理解:商城系统订单表会存储一个用户ID作为关联外键,而不推荐存储完整的用户信息,因为当我们用户表中的信息(真实名称、手机号、收货地址···)修改后,不需要再次维护订单表的用户数据,同时也节省了存储空间。
空间索引是MyISAM的一种特殊索引类型,主要用于地理空间数据类型
❝ 为什么Mysql索引要用B+树不是B树?
用B+树不用B树考虑的是IO对性能的影响,B树的每个节点都存储数据,而B+树只有叶子节点才存储数据,所以查找相同数据量的情况下,B树的高度更高,IO更频繁。数据库索引是存储在磁盘上的,当数据量大时,就不能把整个索引全部加载到内存了,只能逐一加载每一个磁盘页(对应索引树的节点)。其中在MySQL底层对B+树进行进一步优化:在叶子节点中是双向链表,且在链表的头结点和尾节点也是循环指向的。
❝ 面试官:为何不采用Hash方式?
因为Hash索引底层是哈希表,哈希表是一种以key-value存储数据的结构,所以多个数据在存储关系上是完全没有任何顺序关系的,所以,对于区间查询是无法直接通过索引查询的,就需要全表扫描。所以,哈希索引只适用于等值查询的场景。而B+ Tree是一种多路平衡查询树,所以他的节点是天然有序的(左子节点小于父节点、父节点小于右子节点),所以对于范围查询的时候不需要做全表扫描。
哈希索引不支持多列联合索引的最左匹配规则,如果有大量重复键值得情况下,哈希索引的效率会很低,因为存在哈希碰撞问题。
覆盖索引(Covering Index),或者叫索引覆盖, 也就是平时所说的不需要回表操作
发布者:全栈程序员栈长,转载请注明出处:https://javaforall.cn/111644.html原文链接:https://javaforall.cn
扫码关注腾讯云开发者
领取腾讯云代金券
Copyright © 2013 - 2025 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号:粤B2-20090059 深公网安备号 44030502008569
腾讯云计算(北京)有限责任公司 京ICP证150476号 | 京ICP备11018762号 | 京公网安备号11010802020287
Copyright © 2013 - 2025 Tencent Cloud.
All Rights Reserved. 腾讯云 版权所有