【面试】数据库进阶篇（一）索引

版本更新历史：
V1.0：2020-03-18
V2.0：2021-09-19

一、索引是什么？

1、索引是什么（*10）

作用：索引 ~ 表 <==> 目录~书本，加快数据访问效率。

2、优点、缺点（*3）

优点：加快数据检索速度，加快表连接速度。
缺点：占用物理空间，创建与维护索引需要时间。

二、索引怎么实现？（*8）

1、不同索引底层&特点&适用场景（*8）

常见模型	特点	适用场景
哈希表	区间查询很慢	等值查询，如Memcached（NoSQL）引擎
有序数组	等值与范围查询效率高，但更新慢	静态存储引擎
搜索树	查询更新效率【O(logn)】高；二叉变N叉能少走磁盘	InnoDB（底层用是B+树）

2、AVL树、B树和B+树（*4）的结构与优缺点（*2）

AVL树（自平衡二叉树，Balanced Binary Tree）：基于二分查找形成的搜索树，重点是左右子树高度差<2。
B树：B树与B-tree是同一种树。B树全名平衡多路查找树。

AVL VS B树：B树是多叉树，应用到数据库中，可以充分利用磁盘块原理（如每个块大小为4K，每次IO进行数据读取时，可一次性读取同一个磁盘块数据节点大小可以限制在磁盘块大小范围），减少查找次数，加快访问速度。

B+树：B树的升级版，特点在于非叶子节点不保存记录指针（数据），只进行数据索引，使得每个非叶子节点能保存的关键字大大增加。

追问1：为什么用（B VS）B+树，应用场景？

I、B+树特点
1）层级更少；非叶子节点存储关键字更多，树的层级更少，所以查询更快；
2）查询速度稳定：B+所有关键字数据地址都存在叶子节点上，所以每次查找次数都相同，所以比B树更稳定；
3）天然具备排序功能：B+树所有叶子节点数据都成了一个有序链表，在查询大小区间数据更方便，数据紧密性很高，缓存命中率也会比B树高；
4）全节点遍历更快：B+树只需要遍历所有叶子节点即可，而不需要像B树对每一层进行遍历，这有利于数据库做全表扫描。

II、B树优点：如果经常访问数据离根节点很近，因为B树非叶子节点本身会存有关键字其数据地址，所以这种数据检索会比B+树快。

三、实现后的样子？

1、索引分类（*6）

• 按叶节点存放内容
  主键 / 聚集（簇） 索引：索引包含主键，叶子节点存放整行记录。
  非主键/非聚集（簇）/二级/普通 索引：索引不含主键，叶子节点是主键值。

// 每一个索引在InnoDB对应一颗B+树。所有数据都存储在B+树。
mysql> create table T(
id int primary key, 
k int not null, 
name varchar(16),
index (k)) engine=InnoDB;

回表 ：从普通索引树找到主键，再到主键索引树上找到记录。
- 主键索引：`select * from T where ID = 500`，只需要搜索ID这颗B+树。
- 普通索引：`select * from T where k = 5`，先搜索k索引树，得到ID=500，再到ID索引树搜索一次。

• 按字段多少分类
  单列索引：只存一个字段。
  联合索引：多个字段，按最左匹配原则进行搜索。

四、索引怎么用？

4.1 SQL查询响应流程

……

4.2 索引失效场景？

1、索引失效场景和原因？（*6）

1. or ：查询条件包含or，除非全部列都是索引

2. 相等
   1）等号。不是 =，无法进行值相等比较，如is null/!=/<>/not in/索引列运算（加上函数或符号+-*/）。
   2）类型。字段类型是字符串，条件没加引号。
   3）编码。左右连接字段编码格式不一样。

3. 最左前缀
   1）like查询以%开头。
   2）查询条件不是联合索引的第一个列；

4. 性能。MySQL估计全表扫描比使用索引快。

4.3 各种索引适用场景？

1、MySQL索引建立原则（*3）

• （a）空间占用
  在满足需求的情况下，尽量选用占用空间小的，这样既能降低成本，也能提高性能。具体比如主键尽量选用数值型、长度小的，自增主键往往就可以。

• （b）最左匹配&减少回表次数
  按字段定义顺序，从左到右进行搜索。最左匹配可能是最左边的N个字段或字符。

追问1：联合索引如何安排字段顺序？

评估标准是索引复用能力，考虑是否可以通过调整顺序来减少维护索引数量或者占用空间。
例2（占用空间）： 需要同时维护（a，b）、（b）两个索引，谁小谁单独建立。具体如name，age，name字段比age大，建议创建（name，age）与（age）

2、怎么通过最左前缀原则定位记录，那些不符合最左前缀部分，会怎样？

举例：SQL语句：select * from tuser where name like '张%' and age=10 and ismale=1;
分析：该语句在搜索索引树时，只能用“张”，找到第一个满足条件的记录ID3.

然后呢？（索引下推-MySQL5.6开始）
判断其他条件是否满足，然后从ID3一个个回表，而5.6引入索引下推优化后，可以在索引遍历过程中，对索引包含字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

上面两图的区别在于，InnoDB在（name，age）索引内部就判断了age是否等于10，对于不等于10的记录，直接判断并跳过，把回表次数从4次降低到2次。

4.4 索引具体使用？

0、MySQL存储引擎（*7）及区别（*8）

特点	MyISAM	InnoDB	MEMORY
事务安全 & 外键	N	Y	N
锁机制	表锁	行锁	表锁
-	-	-	-
优势	访问速度快，适合以SELECT、INSERT为主应用	支持外键、事务完整性、并发一致性等	内存存放，默认hash索引，可快速定位记录
缺点	事务完整性、并发性不适合		一旦服务关闭，表中数据丢掉。安全性低
场景	Web、数据仓库	计费、财务系统	更新不频繁的小表（要确保数据库异常可以恢复）

1、索引使用过程。

……

2、索引实战场景

……

三、参考

1、Mysql索引会失效的几种情况分析
2、后端程序员必备：索引失效的十大杂症
3、索引失效的情况
4、# MySQL索引失效总结
5、MySQL高级 之 索引失效与优化详解
6、SQL CREATE INDEX 语句
7、面试/笔试第三弹 —— 数据库面试问题集锦
8、面试官问你B树和B+树，就把这篇文章丢给他
9、平衡二叉树、B树、B+树、B*树 理解其中一种你就都明白了