Java面试题之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进行；

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