【Java面试题】MySQL上篇（索引）

索引

1.索引的分类？

• 三个不同维度划分：
  • 功能分类：主键索引、唯一索引、普通索引、全文索引
  • 数据结构：B+树索引、哈希索引
  • 存储位置：聚簇索引、非聚簇索引

2.B树和B+树的区别？

2.1B树

1. 平衡二叉树：节点最多有两个子树；
2. B树：一棵多路平衡查找树，每个节点可以有多个子树（M 阶 B 树表示该树每个节点最多有 M 个子树）
3. 平衡二叉树每个节点只有一个数据和两个指向孩子的指针
4. B 树每个中间节点有 k-1 个关键字（可以理解为数据）和 k 个子树（ k介于阶数 M 和 M/2 之间，M/2 ⬆️向上取整）

5. 应用场景：文件系统和数据库系统中常用的B/B+ 树，他通过对每个节点存储个数的扩展，使得对连续的数据能够进行较快的定位和访问，能够有效减少查找时间，提高存储的空间局部性从而减少IO操作。他广泛用于文件系统及数据库中，如：

2.2B+树

1. 特点：

   1. 节点的子树数和关键字数相同（B 树是关键字数比子树数少一）；
   2. 叶子节点包含了全部数据，同时符合左小右大的顺序；
   3. 非叶子节点仅用作索引，它的关键字和子节点有重复元素

   

2. 优点：

   1. 层级更低，IO 次数更少
   2. 每次都需要查询到叶子节点，查询性能稳定
   3. 叶子节点形成有序链表，范围查询方便

3. 分类：

   • B+树索引又可分为聚簇索引和非聚簇索引
   • 聚簇索引的叶子节点存放的是整张表的行记录数据、非聚簇索引的叶子节点存放的是相遇行数据的指针地址

3.为什么使用索引会加快查询？

1. 有了索引，就可以直接跳到索引指示的数据位置，而不必扫描整张表，从而大大减少了磁盘 I/O 操作的次数。
2. MySQL 的 InnoDB 存储引擎默认使用 B+ 树来作为索引的数据结构，而 B+ 树的查询效率非常高，时间复杂度为 O(logN)。
3. 索引文件相较于数据库文件，体积小得多，查到索引之后再映射到数据库记录，查询效率就会高很多。

4.创建索引的注意点？

1. 选择合适的列作为索引

   • 经常作为 查询条件（WHERE 子句）、排序条件（ORDER BY 子句）、分组条件（GROUP BY 子句） 的列是建立索引的好候选。

   • 区分度低的字段，例如性别，不要建索引

   • 频繁更新的字段，不要作为主键或者索引

   • 不建议用无序的值(例如身份证、UUID )作为索引，当主键具有不确定性，会造成叶子节点频繁分裂，出现磁盘存储的碎片化

2. 避免过多的索引

   • 每个索引都需要占用额外的磁盘空间。

   • 更新表（INSERT、UPDATE、DELETE 操作）时，所有的索引都需要被更新。

   • 维护索引文件需要成本；还会导致页分裂，IO 次数增多。

3. 利用前缀索引和索引列的顺序

   • 对于字符串类型的列，可以考虑使用前缀索引来减少索引大小。

   • 在创建复合索引时，应该根据查询条件将最常用作过滤条件的列放在前面。

5.索引在哪些情况下会失效？

1. 运算：对索引列运算（如，+、-、*、/），索引失效。
2. 函数：在索引列上使用 mysql 的内置函数，索引失效。
3. 范围查询：索引字段上使用（!= 或者 < >，not in）时，可能会导致索引失效。
4. 使用%XXX左模糊查询失效，因为mysql是最左原则，使用XXX%右模糊查询是可以使用索引的，但是左模糊违背了最左原则所以不行
5. 字段类型不匹配导致的索引失效：where 条件字段类型与实际表中字段类型不匹配的时候，Mysql 会进行隐式的数据类型转换，而类型转换会使用到内置函数，导致在进行数据查询的时候并没有使用索引。
6. 查询条件包含 or，可能会导致索引失效：or 分割的条件，如果 or 左边的条件存在索引，而右边的条件没有索引，不走索引
7. 联合索引，查询时的条件列不是联合索引中的第一个列，索引失效。

6.聚簇索引和非聚簇索引的区别？

1. 聚簇索引直接将数据存储在 B+树的叶子节点中，而非聚簇索引的叶子节点存储的是指向数据行的指针。
2. 一个表只能有一个聚簇索引，但可以有多个非聚簇索引。
3. 聚簇索引改善了顺序访问的性能，但更新主键的成本较高；非聚簇索引适合快速插入和更新操作，但检索数据可能需要更多的磁盘 I/O。

7.回表查询是什么？

1. 在 InnoDB 存储引擎里，利用辅助索引查询，先通过辅助索引找到主键索引的键值
2. 再通过主键值查出主键索引里面没有符合要求的数据，它比基于主键索引的查询多扫描了一棵索引树，这个过程就叫回表。

8.什么是最左前缀原则/最左匹配原则？

1. 最左匹配原则：在 InnoDB 的联合索引中，查询的时候只有匹配了前一个/左边的值之后，才能匹配下一个。

2. 根据最左匹配原则，我们创建了一个组合索引，如 (a1,a2,a3)，相当于创建了（a1）、(a1,a2)和 (a1,a2,a3) 三个索引。

3. 为什么不从最左开始查，就无法匹配呢？

   比如有一个 user 表，我们给 name 和 age 建立了一个组合索引。

   ALTER TABLE user add INDEX comidx_name_phone (name,age);
   1

   

从这张图可以看出来，name 是有序的，age 是无序的。当 name 相等的时候， age 才是有序的。

这个时候我们使用 where name= ‘张三‘ and age = ‘20 ‘去查询数据的时候， B+Tree 会优先比较 name 来确定下一步应该搜索的方向，往左还是往右。如果 name 相同的时候再比较 age。但是如果查询条件没有 name，就不知道下一步应该查哪个 节点，因为建立搜索树的时候 name 是第一个比较因子，所以就没用上索引

9.什么是索引下推优化？

1. 索引下推(Index Condition Pushdown，简称ICP)，是MySQL5.6版本的新特性，它能减少回表查询次数，提高查询效率。
2. 如图为MySQL的大体架构：

3. MySQL服务层负责SQL语法解析、生成执行计划等，并调用存储引擎层去执行数据的存储和检索。

4. 索引下推的下推其实就是指将部分上层（服务层）负责的事情，交给了下层（引擎层）去处理。

5. 在没有使用ICP的情况下，MySQL的查询：

   • 存储引擎读取索引记录；
   • 根据索引中的主键值，定位并读取完整的行记录；
   • 存储引擎把记录交给Server层去检测该记录是否满足WHERE条件。

6. 使用ICP的情况下，查询过程：

   • 存储引擎读取索引记录 （不是完整的行记录）；
   • 判断 WHERE条件部分能否用索引中的列来做检查，条件不满足，则处理下一行索引记录；
   • 条件满足，使用索引中的主键去定位并读取完整的行记录（就是所谓的回表）；
   • 存储引擎把记录交给Server层，Server层检测该记录是否满足WHERE条件的其余部分。

7. 例子：例如一张表，建了一个联合索引（name, age），查询语句：select * from t_user where name like '张%' and age=10;，由于name使用了范围查询，根据最左匹配原则：

但是，使用了索引下推优化，把 where 的条件放到了引擎层执行，直接根据name like '张%' and age=10的条件进行过滤，减少了回表的次数。