MySQL面试题-索引篇

这篇具有很好参考价值的文章主要介绍了MySQL面试题-索引篇。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

目录

1.什么是索引

2.索引常见的数据结构?

3.MySQL有哪些索引?

4.什么是B+树?为什么B+树成为主要的SQL数据库的索引实现?

5.聚簇索引和非聚簇索引?

6.什么是页分裂

7.Mysql为何建议使用自增id作主键

 8.什么叫回表?

9.什么是覆盖索引?

10.什么是最左前缀原则?

11.什么是索引下推

12.正确使用索引的一些建议


1.什么是索引

MySQL的索引是一种数据结构,可以用于加快数据库中数据的查询速度。索引是基于表中一个或多个列的值排序的快速查找数据结构,可以大大提高查询效率。MySQL支持多种类型的索引,如B-tree索引、哈希索引、全文索引等。

索引可以在表创建时定义,也可以在表已经创建后通过ALTER TABLE语句来添加。通常情况下,应该将索引定义在经常用于查询的列上,以提高查询效率。不过,需要注意索引会占用额外的存储空间,并且对数据的插入、更新和删除操作也会产生额外的开销。因此,在使用索引时需要权衡使用索引的利弊,选

可以把MySQL中的索引比作一本字典。假设你要查找一个单词,如果你从头开始依次翻阅每一页,直到找到目标单词,这样的速度显然会非常慢。但是如果你使用字典的索引,可以先翻到字典的索引页,然后根据字母顺序快速找到目标单词所在的页数,这样可以大大加快查找的速度。择适当的索引类型和索引列,以提高查询性能。

2.索引常见的数据结构?

  1. B-tree索引:B-tree是一种平衡树,它可以用于高效地查找数据,B-tree索引是MySQL中最常见的索引类型。在B-tree索引中,每个节点包含多个键值,可以快速定位到满足查询条件的数据行。

  2. B+tree索引:B+tree是一种变种的B-tree,它可以用于高效地查询和范围查询。在B+tree索引中,只有叶子节点包含数据,其它节点只包含键值信息。

  3. 哈希索引:哈希索引是一种将索引列的值转换为哈希值的索引结构,可以用于快速定位到指定的数据行。哈希索引适用于等值查询,但不支持范围查询和排序。

  4. 全文索引:全文索引可以用于对文本类型的数据进行全文搜索,它可以提高对文本类型数据的查询效率。在MySQL中,可以使用全文索引来查找包含某些关键词的文本。

3.MySQL有哪些索引?

按照应用维度,常见的 MySQL 索引包括以下几类:

  1. 主键索引(Primary Key Index):主键索引是一种特殊的唯一索引,用于保证表中每一行数据的唯一性,也可以加速根据主键进行的查询操作。

  2. 唯一索引(Unique Index):唯一索引用于保证表中某一列或某几列的唯一性,也可以加速根据这些列进行的查询操作。

  3. 普通索引(Index):普通索引也称作非唯一索引,用于加速根据索引列进行的查询操作。与唯一索引不同的是,普通索引允许索引列中存在重复的值。

  4. 全文索引(Fulltext Index):全文索引用于对表中某一文本字段进行全文检索,常用于搜索引擎等应用场景。

  5. 组合索引(Composite Index):组合索引是将多个索引列组合成一个索引,可以加速根据这些列的组合进行的查询操作。组合索引的建立需要根据查询的具体情况和列的选择进行权衡和优化。

4.什么是B+树?为什么B+树成为主要的SQL数据库的索引实现?

B+树是一种基于B树的数据结构,它的特点是在内部节点不存储数据,只存储索引的值,而所有数据都保存在叶子节点。

在 B+ Tree 中,一个节点中的 key 从左到右非递减排列,如果某个指针的左右相邻 key 分别是 keyi 和 keyi+1,且不为 null,则该指针指向节点的所有 key 大于等于 keyi 且小于等于 keyi+1。

mysql索引面试题,mysql,面试复习,mysql,数据库,java,面试

同时,B+树还具有以下几个特点:

  1. 所有叶子节点都是按照顺序连接在一起的,可以很方便地遍历整棵树的所有叶子节点,也方便基于范围的查询操作。

  2. 所有数据记录的指针都保存在叶子节点上,因此在进行数据查询时,只需要搜索一次B+树就可以找到所有需要的数据记录。

  3. B+树的内部节点只存储索引的值,而不存储数据记录的指针,因此B+树的内部节点可以存储更多的索引,从而减少树的高度,提高查询效率。

  4. B+树的高度很低,因为每个节点存储的索引值比B树要多,所以B+树相对于B树来说,可以更好地利用磁盘预读特性,从而减少I/O操作的次数。

由于B+树具有以上优点,因此它成为了主要的SQL数据库的索引实现。

5.聚簇索引和非聚簇索引?

聚簇索引:

是将数据按照索引顺序存储在磁盘上的一种索引结构,通常是在主键或唯一键上建立的。因为聚簇索引直接存储数据,所以通过聚簇索引查询数据时,可以减少磁盘I/O操作的次数,提高查询效率。

非聚簇索引:

则是将索引和数据分开存储的一种索引结构,它的叶子节点存储的是索引字段和指向数据行的指针,而数据行则是按照主键顺序存储在磁盘上的。因为非聚簇索引和数据是分开存储的,所以在查询时需要先通过索引找到对应的数据行的主键,然后再通过主键查找对应的数据,因此会增加磁盘I/O操作的次数,相对于聚簇索引来说,查询效率更低。

需要注意的是,InnoDB存储引擎中的主键索引是聚簇索引,而非主键索引则是非聚簇索引。另外,如果一个表没有主键或唯一键,InnoDB会选择一个唯一非空索引作为聚簇索引。如果表中没有合适的索引可以作为聚簇索引,InnoDB会创建一个隐藏的聚簇索引来存储数据。

举个栗子:

假设,我们有一个主键列为ID的表,表中有字段k,并且在k上有索引。

这个表的建表语句是:

mysql> create table T(
id int primary key, 
k int not null, 
name varchar(16),
index (k))engine=InnoDB;

 表中R1~R5的(ID,k)值分别为(100,1)、(200,2)、(300,3)、(500,5)和(600,6),两棵树的示例示意图如下。

mysql索引面试题,mysql,面试复习,mysql,数据库,java,面试

从图中不难看出,根据叶子节点的内容,索引类型分为主键索引和非主键索引。

主键索引的叶子节点存的是整行数据。在InnoDB里,主键索引也被称为聚簇索引(clustered index)。

非主键索引的叶子节点内容是主键的值。在InnoDB里,非主键索引也被称为二级索引(secondary index)。

6.什么是页分裂

在B+树索引结构中,页是存储索引数据的基本单位。当一个页已经存储了最大数量的索引记录,再往该页插入新的索引记录时,该页需要将其中一部分记录移动到新的页中,这个过程就叫做页分裂。页分裂的目的是为了维持B+树的平衡性,避免某个页中记录过多或者过少,影响索引的效率和查询性能。

页分裂会受影响。

除了性能外,页分裂操作还影响数据页的利用率。原本放在一个页的数据,现在分到两个页中,整体空间利用率降低大约50%。

当然有分裂就有合并。当相邻两个页由于删除了数据,利用率很低之后,会将数据页做合并。合并的过程,可以认为是分裂过程的逆过程。

如果采用非自增性的索引,就有可能导致频繁的页分裂。

7.Mysql为何建议使用自增id作主键

MySQL建议使用自增ID作为主键的原因如下:

  1. 避免主键重复:使用自增ID作为主键可以避免主键重复的问题,因为每次插入新记录时,MySQL会自动分配一个新的ID,保证主键唯一性。

  2. 提高查询性能:自增ID作为主键可以提高查询性能,因为B+树索引会按照主键排序,自增ID的值是递增的,可以降低页分裂和页合并的频率,减少B+树索引的维护成本,从而提高查询效率。

  3. 减少索引占用空间:使用自增ID作为主键可以减少索引占用的空间,因为整型ID通常只需要占用4个字节的存储空间,而使用其他类型的主键,如字符串等,则会占用更多的存储空间。

需要注意的是,如果表中没有明显的自然主键,可以使用自增ID作为主键。但如果有自然主键,如身份证号、手机号等唯一标识符,则应该使用自然主键作为主键,以避免数据冗余和不一致。

 8.什么叫回表?

回表(Covering Index)是指查询数据时,如果需要从辅助索引中查询列数据,而不是直接从主键索引或者聚簇索引中获取,就需要回到原始的表中再查找对应的数据,这个过程就被称为回表。

在MySQL中,辅助索引只存储了索引列的值和指向对应主键索引的指针,因此如果查询的列不在辅助索引中,就需要通过回表从原始的表中获取对应数据。这会增加I/O的操作,降低查询效率。

为了避免回表的操作,我们可以使用覆盖索引(Covering Index),即在辅助索引上包含所有需要查询的列,这样在查询时就可以直接从索引中获取所需要的数据,而不需要回到原始的表中进行查找。这样可以大大提高查询效率。

9.什么是覆盖索引?

覆盖索引(Covering Index)是指辅助索引包含了所有需要查询的字段,查询时可以直接从辅助索引中获取数据而无需回表到主键索引或聚簇索引所在的数据页中查找数据。

覆盖索引的优点在于可以减少回表的操作,避免了不必要的IO操作,提高了查询效率,特别是在数据量大的情况下,优势更加明显。

由于覆盖索引可以减少树的搜索次数,显著提升查询性能,所以使用覆盖索引是一个常用的性能优化手段。

例如:select ID from T where k between 3 and 5这时只需要查ID的值,而ID的值已经在k索引树上了,因此可以直接提供查询结果,不需要回表。

通过联合索引实现覆盖索引的目的,例如在一个市民信息表上,将身份证号和名字建立联合索引,那么通过身份证号查名字可以直接覆盖,不需要回表

10.什么是最左前缀原则?

最左前缀原则是指,在使用复合索引进行查询时,如果查询条件中只使用了复合索引的左边一部分列,那么索引只能用于查找第一个列,无法用于查找后面的列。这个原则也被称为“最左匹配原则”。这个原则的应用非常广泛,特别是在联合索引中。通过遵循最左前缀原则,可以尽可能地利用索引,提高查询效率。

查询的时候:

最左前缀可以是联合索引的最左N个字段,也可以是字符串索引的最左M个字符。
(a,b)这个联合索引,单独用a查也可以,where a like ‘xxx%’也可以走索引,就不需要单独在a上建立索引了

设计索引的时候,也可以考虑:

(a,b)(b)和(b,a)(a)可以覆盖单独查和联合查的所有场景,这时考虑的原则就是空间,看(a),(b)哪个占用空间更小,使用哪个

11.什么是索引下推

索引下推(Index Condition Pushdown,简称ICP)是 MySQL 的一种优化技术,用于优化查询语句的性能。在传统的查询执行过程中,MySQL 会先扫描索引获取匹配行的主键,然后再根据主键到数据表中查找行数据。而索引下推则是在索引上完成查询,并尽可能减少扫描数据行的数量,从而提高查询性能。

具体来说,当 MySQL 执行一条 SELECT 语句时,如果该语句中的 WHERE 条件包含一个或多个索引列,MySQL 就会尝试将这些条件下推到存储引擎中进行处理。这意味着 MySQL 在扫描索引时,会同时评估 WHERE 条件,只有符合条件的索引记录才会被返回,而不是扫描所有的索引记录。这样就可以减少存储引擎需要扫描的数据行数,从而提高查询效率。

例:

select * from tuser where name like '张%' and age=10 and ismale=1;

InnoDB在(name,age)索引内部就判断了age是否等于10,不用回表

需要注意的是,索引下推仅适用于使用非覆盖索引的查询语句,即查询语句需要返回表中的列,而不仅仅是索引列。如果查询语句使用的是覆盖索引,则不会进行索引下推优化。

12.正确使用索引的一些建议

  1. 使用合适的数据类型:为了减少索引的大小,应该尽可能使用短的数据类型。例如,如果只需要存储年份,可以使用 SMALLINT 数据类型,而不是 INT 或 BIGINT 数据类型。

  2. 精简索引数量:索引越多,查询速度越慢,因为查询需要扫描更多的索引。因此,应该仅在必要时使用索引,避免创建过多的索引。

  3. 选择合适的索引类型:不同类型的索引适用于不同的查询场景。因此,应该根据查询的特点选择合适的索引类型,例如 B+ 树索引或哈希索引。

  4. 避免过长的索引:索引长度越长,索引越大,查询速度就越慢。因此,应该避免创建过长的索引,例如超过 100 个字符。

  5. 考虑索引列的顺序:按照查询条件的顺序创建索引,可以最大限度地利用索引的优势。这是因为索引在执行查询时只能使用索引的最左前缀。

  6. 避免在索引列上进行函数操作:在索引列上进行函数操作会使索引失效,无法利用索引进行查询。因此,应该避免在索引列上进行函数操作,例如在索引列上使用函数 LOWER() 或 UPPER()。

  7. 使用覆盖索引:如果查询只需要返回索引列,可以使用覆盖索引。覆盖索引只需要扫描索引,而不需要扫描数据行,可以加快查询速度。

  8. 避免使用 NOT IN 和 OR 条件:使用 NOT IN 和 OR 条件会导致 MySQL 无法使用索引进行查询,应该尽可能避免使用这些条件。

  9. 定期维护索引:定期维护索引可以提高查询性能。例如,可以删除不必要的索引,重新生成索引统计信息等。文章来源地址https://www.toymoban.com/news/detail-713589.html

到了这里,关于MySQL面试题-索引篇的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • MySQL数据库:索引

            索引是一种特殊的文件,包含着对数据表里所有记录的引用指针。可以对表中的一列或多列创建索引,并指定索引的类型,各类索引有各自的数据结构实现。         相当于是给数据库中的数据建立了一个目录,通过目录可以知道数据所在位置,然后到指定位置

    2023年04月17日
    浏览(74)
  • mysql 数据库 期末复习题库

    一、选择题 第 1 章 数据库系统概述 1 . DBS 是(   A )的简写。 A. 数据库系统   B. 数据库管理系统    C. 数据库    D. 操作系统 2.DBMS 是 (  B   ) 的简写 A. 数据库系统   B. 数据库管理系统    C. 数据库    D. 数据 3.DB 、 DBMS 和 DBS 之间的关系是   (  C  ) A. DB 包含 DBMS 和

    2024年02月08日
    浏览(47)
  • MySQL数据库唯一索引

    创建索引是指在某个表的一列或多列上建立一个索引,以便提高对表的访问速度。创建索引有3种方式,分别是1.创建表的时候创建索引、2.在已经存在的表上创建索引和使用3.ALTER TABLE语句来创建索引。 本文福利, 莬 费领取Qt开发学习资料包、技术视频,内容包括(C++语言基

    2024年02月08日
    浏览(61)
  • MySQL数据库索引机制

    MySQL是一款有客户端和服务端的网络应用,mysql是它的客户端,mysqld是它的服务端。服务端本质就是一个进程,它存在于内存当中。而我们存储在MySQL中的数据是保存在磁盘上的,当我们对MySQL中数据进行增删查改操作时,不可能是直接在磁盘上进行操作,而是将对应的数据加

    2024年02月12日
    浏览(72)
  • 简单认识MySQL数据库索引

    提示:文章写完后,目录可以自动生成,如何生成可参考右边的帮助文档 ●索引是一个排序的列表,在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址(类似于C语言的链表通过指针指向数据记录的内存地址)。 ●使用索引后可以不用扫描全表来定位某行的

    2024年02月16日
    浏览(65)
  • MySQL数据库索引的数据结构

    数据库索引的功能就是让查找更加的高效,所以索引的数据结构应该是能够加速查找的数据结构。 MySQL的innoDB存储引擎的索引的数据结构就是多叉搜索树中的b+树,这可以说是为索引量身定做的一个数据结构。 首先,索引可以通过主键,unique修饰创建,也可以直接使用sql语句

    2024年02月10日
    浏览(53)
  • Mysql数据库基础知识总复习

    小亭子正在努力的学习编程,接下来将开启javaEE的学习~~ 分享的文章都是学习的笔记和感悟,如有不妥之处希望大佬们批评指正~~ 同时如果本文对你有帮助的话,烦请点赞关注支持一波, 感激不尽~~ 目录 前言 数据库基础知识 数据,数据库,数据库管理系统,数据库系统 数据

    2024年02月02日
    浏览(54)
  • 【MySql系列】深入解析数据库索引

    MySQL索引是数据库中一个关键的概念,它可以极大地提高查询性能,加快数据检索速度。但是,要充分发挥索引的作用,需要深入理解它们的工作原理和使用方式。 在本文中,我们将深入解析MySQL索引,探讨它们的重要性、类型、创建、维护以及最佳实践。 在数据库中,索引

    2024年02月08日
    浏览(71)
  • 【MySQL数据库 | 第十七篇】索引以及索引结构介绍

    目录 前言: 索引简介:  索引结构:           二叉树索引结构         Tree(普通二叉树)         B-Tree(多路平衡查找树)         B+Tree          哈希索引数据结构 总结: 在实际生活中,我们对SQL语句进行优化实际上有很大一部分都是对索引进行优化,因此对索引

    2024年02月09日
    浏览(69)
  • MySQL数据库索引的种类、创建、删除

    目录 一:MySQL 索引 1、MySQL 索引介绍 2、 索引的作用  3、索引的副作用 4、 创建索引的原则依据  二、索引的分类和创建 1、 普通索引 (1) 直接创建索引 (2) 修改表方式创建 (3) 创建表的时候指定索引 2、 唯一索引 (1) 直接创建唯一索引 (2) 修改表方式创建

    2024年02月09日
    浏览(175)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包