Java面试题之mysql

Mysql

持续更新中~

1. MySQL的索引原理是什么?什么是索引?以及索引的优缺点?

索引是一种数据结构,使用索引可以快速访问数据库表中的特定信息。索引的作用相当于图书的目录,可以根据目录中的页码快速找到所需的内容。索引是一个文件,是要占用物理空间的
优点

  • 通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。
  • 可以大大加快数据的检索速度,提高查询性能。
  • 可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。
  • 在使用分组和排序子句进行数据检索时,索引可以显著减少查询中分组和排序的时间。
  • 通过使用索引,可以在查询的过程中使用优化隐藏器,提高系统的性能。

缺点

  • 创建索引和维护索引需要耗费时间,这种时间随着数据量的增加而增加。
  • 索引需要占用物理空间,除了数据表占用的数据空间之外,每一个索引还需要占用一定的物理空间。
  • 当对表中的数据进行增加、删除和修改时,索引也需要动态维护,这可能会降低数据的维护速度。

索引的基本原理

  • 当执行一个查询时,MySQL首先会检查查询条件是否可以利用索引。如果可以,它会根据查询条件中的值在索引中进行查找。
  • MySQL从索引的根节点开始,根据键值比较,沿着树结构向下遍历,直到找到匹配的叶子节点。这个过程称为索引查找。
  • 一旦在叶子节点中找到匹配的键值,MySQL就可以获取与该键值相关联的指针或地址,然后直接定位到表中的实际数据记录。
  • 通过这种方式,MySQL避免了全表扫描,只访问了必要的索引节点和数据记录,从而大大提高了查询性能。

2. 解释一下B+树和B树的区别及各自定义?

叶子节点:没有子节点的节点;
非叶子节点:度不为0的节点,又称分支节点;
根节点:顶端的节点称为根节点;
一个节点下面最多两个节点;
度:结点拥有的子树数;
平衡二叉树(AVL树):满足二叉树特性基础,每个节点树高度差不超过1;
二叉查找树:左子节点小于当前节点的键值,右子节点大于当前节点的键值

为什么选择B+树实现索引

  • 磁盘I/O效率高:由于B+树的内部节点存储的是键值和指针,而不是实际的数据,所以每个节点可以存储更多的键值对。这意味着相同数量的数据可以用更少的节点来表示,减少了树的高度。减少了树的高度意味着查询时需要进行的磁盘I/O次数更少,从而提高了查询效率。
  • 有序访问性能好:B+树的内部节点构成有序链表,这意味着在范围查询时,可以通过顺序访问内部节点快速定位到所需数据,提高了查询效率。
  • 支持高度可扩展性:B+树的平衡性质保证了树的高度相对较低,因此即使在大数据量的情况下,树的高度也能保持在较小的范围内,使得索引查询的性能保持稳定。
  • 适合磁盘存储:MySQL作为关系型数据库,通常需要将数据持久化到磁盘上,而B+树的特性正好适合磁盘存储,因为它减少了磁盘I/O的次数,并且有利于顺序访问。
  • 支持范围查询:B+树的有序性质使得范围查询效率高,这在数据库中是非常常见的操作,例如在WHERE子句中使用BETWEEN操作符或者大于小于操作符。

B树

  • B树适合随机检索;
  • B树不稳定,最好是根节点就查到了,最坏查到叶子节点;
  • B树的每个节点都带有数据;

B+树

  • B+树所有的叶子节点包含了全部的数据;
  • B+树所有非叶子节点可以看成是索引节点,没有数据,只存键;
  • B+树每个叶子节点都带有指向下一个叶子节点的指针,形成有序链表;
  • B+树必须查询到叶子节点;

3. MyISAM索引和Innodb索引的区别?

  • Innodb是聚簇索引,MyISAM是非聚簇索引;
  • Innodb的主键索引的叶子节点存储着行数据,因此主键索引非常高效;
  • MyISAM索引的叶子节点存储的事行数据地址,需要再一次寻址才能得到数据;
  • Innodb非主键索引的叶子节点存储的是主键和其他带索引的列数据,因此查询时做到覆盖索引会非常高效。

4. 什么是聚簇索引?辅助索引?

聚簇索引:将数据存储和索引放到了一块,找到了索引就找到了数据(在一棵树上);
非聚簇索引:索引结构的叶子节点指向了数据的对应位置上(不在一棵树上)。
辅助索引:(也称为非主键索引或二级索引)

  • 辅助索引通常只存在于非聚集索引上,在InnoDB存储引擎中,辅助索引的叶子节点包含的不是数据行的完整信息,而是对应数据行的主键值。这是因为InnoDB的数据是按照主键的顺序存储的,也就是说,主键索引(也称为聚簇索引)决定了数据的物理存储顺序。
  • 当执行一个查询时,如果使用辅助索引来查找数据,InnoDB首先会查找辅助索引来找到相应的主键值,然后使用这个主键值去查找聚簇索引(即主键索引),最终定位到实际的数据行。这个过程需要两步,因此被称为“二次查找”。
  • 辅助索引不会存在于聚簇索引上。在InnoDB存储引擎中,聚簇索引并不是一种单独的索引类型,而是一种数据存储方式。聚簇索引的叶子节点存放着一整行的数据,而辅助索引(非聚簇索引)的叶子节点存储的是主键值,而不是数据的物理位置。辅助索引是在聚簇索引之上创建的,用于提高查询性能。由于辅助索引的存在不影响数据在聚簇索引中的组织,所以一张表可以有多个辅助索引。

总之,辅助索引和聚簇索引是两种不同的索引类型,它们在InnoDB存储引擎中有不同的结构和用途。辅助索引用于提高查询性能,而聚簇索引则决定了数据的物理存储顺序

5.非聚簇索引一定会回表查询么?

不一定,如果涉及到的查询语句所查询的字段是否全部命中了索引,如果全部命中了,就不必再回表查询。

6. 什么是回表操作?

通过索引查询找到主键的键值,在通过主键值查全表,就叫回表查询。

当执行查询时,如果查询条件包含在索引中,数据库可以直接利用索引定位到符合条件的数据行。然而,索引通常只包含查询条件所涉及的列和索引的键值,而不包含其他列的数据。

为了获取这些未包含在索引中的其他列的值,数据库需要进行回表操作。这意味着,根据索引中的键值,数据库需要再次访问主表(即包含所有列数据的表),以获取所需的完整行数据。

7. 什么是索引覆盖?

如果要查询的字段都建立过索引,那么索引会直接在索引表中查询而不会访问原始数据(否则只要有一个字段没有建立索引就会全表扫描),这就叫做索引覆盖。

因此我们需要尽可能在select后只写必要查询的字段,以增加索引覆盖的几率。

8.什么是短索引?

短索引(Short Index)是一种优化索引的方法,用于减少索引占用的存储空间和提高查询性能。在MySQL数据库中,短索引是指对字符串类型的列进行索引时,不使用整个字符串的长度,而是选择字符串的前N个字符来创建索引。这样可以减少索引的存储空间,并且由于索引长度变短,查询速度也可能会得到提高。

短索引的使用场景通常是在字符串类型的列中,这些列的数据值很长,但是前缀部分就足够区分不同的数据值。通过只索引字符串的前N个字符,可以避免对整个字符串进行索引,从而减少索引的存储空间。同时,由于查询时通常只需要匹配字符串的前缀部分,短索引也可以提高查询性能。

需要注意的是,短索引虽然可以减少存储空间和提高查询性能,但也会降低索引的选择性。索引的选择性是指不重复的索引值(基数)与数据表中的记录总数的比值。选择性越高,索引的过滤效果越好,查询性能也会更高。因此,在选择使用短索引时,需要权衡存储空间和查询性能与索引选择性的关系,选择最合适的索引策略。

9.索引的数据结构有哪几种?

哈希索引:底层的数据结构就是哈希表,只能用于对等比较,例如‘=’,是一次定位数据,所以检索效率高于b树索引。
B树索引:是mysql默认的算法,因为不仅可以用在=、>、<、<=、>=和between这些比较操作符上,还可以用于like操作符,只要查询不以通配符开头就可,比如‘jack%’可以,‘%jack’就不可以。

10. MySQL的索引的创建原则?MySQL不适合建立索引的情况?

  • 数据少的情况不适合建立索引;
  • 经常insert不适合建立索引;
  • 数据重复且平分,比如只有A、B两个值不适合建立索引;
  • 非空字段上建立索引;
  • 查询频繁的字段才适合建立索引;
  • 尽量的扩展索引,而不是新建索引;
  • 定义为text、image、bit的数据类型不要建立索引;

11. 使用索引一定能提高查询性能么?

不一定,索引是需要空间来存储的,也需要定期维护,而且每当有记录被修改、删除和增加的时候索引本身也会被修改。

12. MySQL的主键索引?联合索引?唯一索引?

主键索引:数据列不可以重复,不允许null值;
唯一索引:数据列不可以重复,允许为null值,一个表允许多个唯一索引存在;
联合索引:(复合索引)

  • 可以使用多个字段建立一个索引,在联合索引中,如果想要命中索引,需要按照建立索引时的字段顺序挨个使用,否则无法命中索引。
  • mysql使用索引的时候需要索引有序,假设现在建立了‘name,age,school’的联合索引,name索引的排序为:先按照name排序,再按age,school进行排序。
  • 注意事项:在建立联合索引时候,一般情况下,将需求查询频繁或者字段选择性搞的列放在前面。

13. MySQL的索引下推是啥意思?

是5.6之后提出的一个新特性,(Index Condition Pushdown,简称ICP),用于优化数据查询;

  • 假设,我们设置了一个联合索引name、age,语句是“name like “张%” and age = 20 and school = ‘大学’ ”;
  • 在5.6之前,只会走name的索引,name右边的都不看,拿到了主键值,再去回表操作,如果name条件找到了1000个,那就要回1000次表;
  • 在5.6之后,在取出索引同时会根据where条件直接过滤掉不满足条件记录的,减少回表次数,这就是索引下推,name like ‘张%’ age = 20,查出来之后,再对school进行回表判断就好了。
  • 使用ICP会把age的判断下推给存储引擎;
  • 不使用ICP会把age的判断给server进行;

需要注意的是,索引下推是否使用,以及在哪些场景下使用,取决于优化器的选择。优化器会根据查询的具体情况和数据分布等因素,判断使用索引下推是否可以提高查询效率。因此,在某些场景下,即使存在索引下推的条件,优化器也

  • 21
    点赞
  • 23
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值