java面试题 --- MySQL②

1. 说说你对索引的认识？

• 索引是帮助 SQL 高效获取数据的一种数据结构。

2. 既然你说索引是一种数据结构，那它底层到底是什么呢？

• 索引底层数据结构主要有两种，B+ 树和 Hash 表，InnoDB 用的就是 B+ 树。

3. B+ 树索引和 Hash 索引有什么区别呢？

• B+ 树索引天然有序，左子节点小于父节点，右子节点大于父节点。叶子节点存放数据行或者主键，所有叶子节点之间形成一条链相互关联。因为 B+ 树有序，所以它不仅可以用于等值查询，还可以用于范围查询。由于数据存放在叶子节点，每次检索都要从父节点开始，检索的效率不是很高；
• Hash 索引用的是 Hash 表来存储，直接根据键值对进行映射，因此只能用于等值查询，且效率很高。它也没办法用索引来进行排序。

4. B+ 树叶子节点可以存放哪些东西？

• 可以存放数据行，也可以存放主键的值。存放数据行时，叫做聚簇索引，也叫主键索引，存放主键的值时，叫做非聚簇索引，也叫非主键索引。InnoDB 默认用的都是聚簇索引。

5. 聚簇索引和非聚簇索引在查询数据时有什么区别？

• 聚簇索引更快，因为叶子节点存放的是数据行，不需要回表查询。

6. 非聚簇索引一定要回表查询吗？

• 不一定，正常情况是查到拿到叶子节点存储的主键的值，然后再根据主键值去做回表查询，但如果是索引覆盖的情况，就不需要回表查询了。

7. 什么叫索引覆盖？

• 索引覆盖就是查询的字段都建了索引，数据可以直接从索引中获取，不需要查询数据表。

8. 使用索引有哪些优缺点？

• 优点：提高检索效率，提高排序的效率；
• 缺点：降低了写数据的效率，因为写数据的同时还要维护索引；索引也占用额外的空间。

9. 如果要对用户密码字段建索引，你打算建什么索引？

• 密码字段可以用前缀索引，比如用密码字段的前 10 位做索引，因为前 10 位的标识度也够高了。前缀索引可以节省空间。

10. 创建联合索引的时候怎么确定哪个字段放前面？

• 识别度最高的字段放到最前面，这样可以提高效率。

11. MySQL 5.6 开始对索引做了什么优化，了解吗？

• 做了索引下推的优化，索引下推可以减少回表查询的次数。比如建立了联合索引 index(name, age)，然后查询 select * from user where name like '张%' and age = 20，没有索引下推的话，MySQL 会把name like '张%'的数据都返回到 MySQL 服务端，服务端再去判断这些数据的 age 是否为 20；而有了索引下推的话，查询的时候会先判断 name like '张%' 的这些数据 age 是否为 20，不是的直接忽略。

12. 你认为哪些情况适合建索引？哪些情况不适合建索引？

• 频繁作为查询条件的字段、排序字段、统计和分组的字段以及与其他表关联的字段应该建立索引；
• 频繁更新的字段以及字段值大量重复的不适合建立索引。

13. 使用索引的时候你会遵循哪些原则？

• 全值匹配是最理想的状态，即查询条件字段都建立了索引且顺序和索引顺序一致；
• 遵循最左前缀法则，比如索引顺序是 A、B、C，那么查询条件一定要有 A，否则 B 和 C 都会失效；中间索引页不能断，如果查询条件是 A 和 C，少了 B，那么 C 也会失效；
• 索引列上不要做任何计算，计算将导致索引失效；
• 范围之后的列索引会失效，比如 name like '张% and age = 20'，那么 age 这个索引将失效；
• 写模糊查询时百分号要写右边，否则索引失效；
• 不要写 select *，用到什么列就查什么列，这样可以增加索引覆盖的机率；
• 不等于、is null、is not null、or 这些都会导致索引失效；
• 字符串的引号不能漏写，否则索引会失效。

辅助记忆，诗曰： 全值匹配我最爱， 最左前缀要遵守； 带头大哥不能死， 中间兄弟不能断； 索引列上少计算， 范围之后全失效； 模糊百分写最右， 覆盖索引不写星； 不等空值还有或， 索引失效要少用； 字符引号不可丢， 牢记以上就无忧。