数据库面试题之索引

提前预知

MySQL常见的面试题类型

MySQL中常见的面试题就是：索引，事务，数据库引擎的差别，数据库中的锁，本篇博客就索引的面试题来介绍一下

B+Tree和B-Tree

B+树索引是B+树在数据库中的一种实现，是最常见也是数据库中使用最为频繁的一种索引。

B+树中的B代表平衡（balance），而不是二叉（binary），因为B+树是从最早的平衡二叉树演化而来的。

关于二叉树(BT)，二叉查找树(BST)、平衡二叉树(AVL），红黑树(RBT)，不懂得可以百度一下，这里就不做介绍了。

B-Tree

B-树是多路平衡查找树，相对于平衡二叉树，对父结点的直接子结点个数不再仅限于2，可以指定m个(自定义)，这样可以在树的深度不大量增加的前提下，保存更多的结点。

B-Tree是为磁盘等外存储设备设计的一种平衡查找树，因此在讲B-Tree之前先了解下磁盘的相关知识。

系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。

InnoDB存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位，InnoDB存储引擎中默认每个页的大小为16KB，可通过参数innodb_page_size将页的大小设置为4K、8K、16K，在MySQL中可通过如下命令查看页的大小：

mysql> show variables like 'innodb_page_size';

而系统一个磁盘块的存储空间往往没有这么大，因此InnoDB每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小16KB。

InnoDB在把磁盘数据读入到磁盘时会以页为基本单位，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O次数，提高查询效率。

B-Tree特点：

• a)树的每个结点最多有m(自定义)子结点;

• b)若根结点不是叶子结点，则至少有两个子结点;

• c) 除根结点外的所有非叶子结点，至少有m/2上取整个子结点;

• d)父结点下的最左边子树所有结点的值均小于父结点的最小值，最右边子树所有结点的值均大于父结点的最大值，其余中间子树所有结点的值则介于指针的父结点两边的值;

• e)所有叶子结点都在同一层;

注意：所有结点均带有值，这点要和B+Tree区别一下

B-Tree中的每个节点根据实际情况可以包含大量的关键字信息和分支，如下图所示为一个3阶的B-Tree：

每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址，两个关键字划分成的三个范围域对应三个指针指向的子树的数据的范围域。

以根节点为例，关键字为20和35，P1指针指向的子树的数据范围为小于20，P2指针指向的子树的数据范围为20~35，P3指针指向的子树的数据范围为大于35。

如果查找关键字25：

• 根据根节点找到磁盘块1，读入内存，【磁盘I/O操作第1次】， 比较关键字25在区间（20,35），找到磁盘块1的指针P2。

• 根据P2指针找到磁盘块3，读入内存，【磁盘I/O操作第2次】，比较关键字25在区间（20,26），找到磁盘块3的指针P1。

• 根据P1指针找到磁盘块7，读入内存，【磁盘I/O操作第3次】，在磁盘块7中的关键字列表中找到关键字25。

分析上面过程，发现需要3次磁盘I/O操作，和3次内存查找操作。由于内存中的关键字是一个有序表结构，可以利用二分法查找提高效率。而3次磁盘I/O操作是影响整个B-Tree查找效率的决定因素。真实的情况是，3层的b-树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

B+Tree

B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，MySQL的InnoDB存储引擎就是用B+Tree实现其索引结构

B+树相对于B-树，只有叶子节点才有值，父结点只起索引查找的作用，同时所叶子结点也也构成了一条有序的链表。

特点：

• a)有m个子结点的父结点就有m个关键字;

• b)所有叶子结点包含了所有关键字(值)，且构成由小到大的有序链表;

• c) 所有非叶子结点起索引作用，结点仅包含子树所有结点的最大值;

• d)所有叶子结点都在同一层;

注意：叶子结点包含了所有的关键字（值）。

从B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值，而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率，在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

什么是索引它可以做什么？

对于数据库来说，一般都是查询比较多，插入和更新的比较少，因此对数据库查询语句的优化显得尤其重要，索引就使用来优化数据库查询操作的，使用索引可以将数据库的查询性能提高几个数量级。

索引在mySql中也叫作 ：键

索引的目的和原理？

索引的目的在于提高查询效率，与我们查阅图书所用的目录是一个道理：先定位到章，然后定位到该章下的一个小节，然后找到页数.

本质都是：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。

索引的数据结构？

我们可以在创建索引的时候，为其指定索引类型，分两类

• hash类型的索引：查询单条快，范围查询慢（就是通过hash算法来定位）
• btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，innoDB默认支持它）

不同的存储引擎支持的索引类型也不一样

• InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引
• MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
• Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
• NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
• Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；

总上，一般采用innoDB数据库引擎比较好，支持事务，支持B-tree，Full-text索引。

索引的分类？

1.普通索引index ：加速查找

2.唯一索引

• 主键索引（primary key ）：加速查找+约束（不为空且唯一）
• 唯一索引（unique）：加速查找+约束 （唯一）

3.联合索引

• primary key(id,name)：联合主键索引
• unique(id,name)：联合唯一索引
• index(id,name)：联合普通索引

4.全文索引fulltext :用于搜索很长一篇文章的时候，效果最好。

5.空间索引spatial :了解就好，几乎不用

什么是聚集索引和非聚集索引以及它们的区别

聚集（clustered）索引，也叫聚簇索引

定义：数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。

聚集索引是我们常用的一种索引，该索引中键值的逻辑顺序决定了表中相应行的物理顺序，我们叶子结点直接对应的实际数据，当索引值唯一（unique）时，使用聚集索引查找特定的行效率很高。

例如，使用唯一店员 ID 列 emp_id 查找特定雇员的最快速的方法，是在 emp_id 列上创建聚集索引或 PRIMARY KEY 约束，其实，自增主键就是一个标准的聚集索引。

当某列满足两个条件时，我们可以创建聚集索引：

• 数据存储有序（如自增）
• key值应当唯一

单单从定义来看是不是显得有点抽象，我们把数据库的表类比成新华字典，字典正文的排列是有序的从a开始到z结束，字典的正文部分本身就是一个大的目录，不需要再去查其他目录来找到我们需要找的文字，我们把这种正文内容本身就按照一定规则排列（有序）的目录称为聚集索引。

聚集索引像字典，字典按字母顺序排列数据，有序。在聚集索引中，索引包含指向数据存储的块而不是数据存储地址的指针，注意和非聚集索引（Normal）做区分。

非聚集（unclustered）索引

定义：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。

非聚集索引就是索引类型为Normal的普通索引，前面提到的B+Tree所有关键字存储在叶子节点，但不存储真正的data，叶子结点存的是一个指向磁盘data的指针，需要到磁盘数据页中取。

非聚集索引的数据存储在一个位置，索引存储在另一位置，由于数据和非聚集索引是分开存储的，因此在一个表中可以有多个非聚集索引。

聚集索引 和 非聚集索引的区别：

• 单表中只能有一个聚集索引，而非聚集索引单表可以存在多个。

• 聚集索引，索引中键值的逻辑顺序决定了表中相应行的物理顺序，非聚集索引，索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，通过索引中叶子结点里面那个指向真正数据的指针（数据的物理地址）来找到磁盘中对应的数据。

• 索引是通过树的数据结构来描述的，我们可以这么理解聚簇索引和非聚簇索引，聚簇索引的叶子节点就是数据节，非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。

• 聚集索引，物理存储按照索引排序

• 非聚集索引，物理存储不按照索引排序；

注意：

• 聚簇索引首先并不是一种索引类型，而是一种数据存储方式，我的理解是，是否为聚簇索引实际上指的就是b+树的具体实现方式，也就是每个节点的data域里面到底放什么东西，究竟放的是具体的数据，还是指向数据的指针？

• InnoDB存储引擎的主键使用的是聚簇索引，而非主键使用的称作辅助索引、二次索引。

• 而MyISAM存储引擎无论主键，还是非主键使用的索引都是一样的非聚簇索引。

• 聚簇索引，有主键时，被索引的列是主键列，如果没有主键，会选择一个唯一的非空索引代替，如果也没有这样的索引，那么会隐式定义一个主键来作为聚簇索引，因此，也可以说聚簇索引就是按照表的主键构造的一个b+树，同时叶子节点里面存储了表的行数据，也正因为只有主键才能使用，因此，一张表只有一个聚簇索引。

• 非主键使用：辅助索引、二次索引，把二次索引也归类为非聚簇索引，如果以data域是否直接保存数据来划分的话，确实可以这么说，但是这里还是把它称作辅助索引，因为这样不容易引起混淆，这指的是InnoDB存储引擎对于非主键列的索引，在他的b+树的叶子节点的data域保存了行的主键值，因此这种索引方式都需要二次查找，也就是说先通过辅助索引首先找到的主键值，再通过主键值去聚簇索引中查找对应的行数据。

聚集索引一定比非聚集索引性能优么？

不一定

什么情况下索引会失效？

1、尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引进行全表扫描，如下：

SELECT * FROM tab_test WHERE username LIKE '%aismall%'

优化方式：尽量在字段后面使用模糊查询，(前面不使用%号)

SELECT * FROM tab_test WHERE username LIKE 'aismall%'

如果需求是要在前面使用模糊查询?
	1、使用MySQL内置函数INSTR(str,substr) 来匹配，作用类似于java中的indexOf()，查询字符串出现的角标位置。
	2、使用FullText全文索引，用match against 检索
	3、数据量较大的情况，建议引用ElasticSearch、solr，亿级数据量检索速度秒级
	4、当表数据量较少（几千条儿那种），别整花里胡哨的，直接用like ‘%xx%’。

2、尽量避免使用 or，会导致数据库引擎放弃索引进行全表扫描，如下：

SELECT * FROM tab_test WHERE id=1 OR id=3

优化方式：可以用union代替or。如下：

SELECT * FROM tab_test WHERE id=1
UNION
SELECT * FROM rab_test WHERE id=3 

3、尽量避免进行null值的判断，会导致数据库引擎放弃索引进行全表扫描，如下：

SELECT * FROM tab_test WHERE score IS NULL

优化方式：可以给字段添加默认值0，对0值进行判断。如下：

SELECT * FROM tab_test WHERE score = 0

4、尽量避免在where条件中等号的左侧进行表达式、函数操作，会导致数据库引擎放弃索引进行全表扫描。

-- 全表扫描
SELECT * FROM tab_test WHERE score/10 = 9

优化方式：可以将表达式、函数操作移动到等号右侧。如下：

-- 走索引
SELECT * FROM tab_test WHERE score = 10*9

5、当数据量大时，避免使用where 1=1的条件。通常为了方便拼装查询条件，我们会默认使用该条件，数据库引擎会放弃索引进行全表扫描。如下：

SELECT username, age, sex FROM tab_test WHERE 1=1

优化方式：用代码拼装sql时进行判断，没 where 条件就去掉 where，有where条件就加 and

WHERE 1=1与WHERE 1=0什么意思？
	where 1=1的应用，不是什么高级的应用，也不是所谓的智能化的构造，仅仅只是为了满足多条件查询页面中不确定的各种因素而采用的一种
	构造一条正确能运行的动态SQL语句的一种方法。
	在多条件查询时进行拼接，可以使用WHERE 1=1来避免后面条件不满足导致的SQL语句出错，因为WHERE 1=1始终返回TRUE
	WHERE 1=0，始终返回FALSE，所以查询结果为表结构，没有数据

6、查询条件不要用 <> 或者 !=

使用索引列作为条件进行查询时，需要避免使用`<>`或者`!=`等判断条件。如确实业务需要，使用到不等于符号，需要在
重新评估索引建立，避免在此字段上建立索引，改由查询条件中其他索引字段代替。

7、where条件仅包含复合索引非前置列，如下：

复合（联合）索引包含key_part1，key_part2，key_part3三列，但SQL语句没有包含索引前置列key_part1，按照MySQL联合索引的最左匹配原则，不会走联合索引。

select col1 from tab_test where key_part2=1 and key_part3=2

8、隐式类型转换造成不使用索引

如下SQL语句由于索引对列类型为varchar，但给定的值为数值，涉及隐式类型转换，造成不能正确走索引。、

select col1 from tab_test where col_varchar=123; 

9、order by 条件要与where中条件一致，否则order by不会利用索引进行排序

-- 不走age索引
SELECT * FROM tab_test order by age;

优化方式：ORDER BY 和WHERE 条件一致

-- 走age索引
SELECT * FROM tab_test WHERE age>0 order by age;

对于上面的语句，数据库的处理顺序是：
	第一步：根据where条件和统计信息生成执行计划，得到数据。
	第二步：将得到的数据排序。当执行处理数据（order by）时，数据库会先查看第一步的执行计划，看order by 的字段是否在执行计划中
	利用了索引。如果是，则可以利用索引顺序而直接取得已经排好序的数据。如果不是，则重新进行排序操作。
	第三步：返回排序后的数据。当order by 中的字段出现在where条件中时，才会利用索引而不再二次排序，更准确的说，order by 中的字
	段在执行计划中利用了索引时，不用排序操作。

  这个结论不仅对order by有效，对其他需要排序的操作也有效。比如group by 、union 、distinct等。

什么是最左前缀原则？

通常我们在建立联合索引的时候，相信建立过索引的同学们会发现，无论是Oracle还是 MySQL 都会让我们选择索引的顺序，比如我们想在a,b,c三个字段上建立一个联合索引，我们可以选择自己想要的优先级，(a、b、c)，或是 (b、a、c) 或者是(c、a、b) 等顺序。

为什么数据库会让我们选择字段的顺序呢？不都是三个字段的联合索引么？这里就引出了数据库索引的最重要的原则之一，最左匹配原则。

比如索引abc_index:(a,b,c)是a,b,c三个字段的联合索引，下列sql执行时都无法命中索引abc_index

select * from table where c = '1';

select * from table where b ='1' and c ='2';

以下三种情况却会走索引:

select * from table where a = '1';

select * from table where a = '1' and b = '2';

select * from table where a = '1' and b = '2'  and c='3';

索引abc_index:(a,b,c)，只会在where条件中带有(a)、(a,b)、(a,b,c)的三种类型的查询中使用

其实当where条件只有(a,c)时也会走，但是只走a字段索引，不会走c字段。

最左匹配原则的原理

• MySQL 建立多列索引（联合索引）有最左匹配的原则，即最左优先：

• 如果有一个 2 列的索引 (a, b)，则已经对 (a)、(a, b) 上建立了索引；

• 如果有一个 3 列索引 (a, b, c)，则已经对 (a)、(a, b)、(a, b, c) 上建立了索引；

注意：

当对索引中所有列通过=或 IN 进行精确匹配时，索引都可以被用到。

• 1、如果建的索引顺序是 (a, b)，查询语句 where b = 1 AND a =2;也会走索引

  理论上索引对顺序是敏感的，但是由于 MySQL 的查询优化器会自动调整 where 子句的条件顺序以使用适合的索引，所以 MySQL 不存在 where 子句的顺序问题而造成索引失效,当然了，SQL书写的好习惯要保持，这也能让其他同事更好地理解你的SQL。

• 2、还有一个特殊情况说明下，下面这种类型的SQL， a 与 b 会走索引，c不会走。

  select * from LOL where a = 1 and b > 2  and c=3;
  

  对于上面这种类型的sql语句，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，在a、b走完索引后，c已经是无序了，所以c就没法走索引，优化器会认为还不如全表扫描c字段来的快，所以只使用了(a,b)两个索引，影响了执行效率。

  其实，这种场景可以通过修改索引顺序为 abc_index:(a,c,b)，就可以使三个索引字段都用到索引，建议小伙伴们不要有问题就想着新增索引，浪费资源还增加服务器压力。

综上，如果通过调整顺序，就可以解决问题或少维护一个索引，那么这个顺序往往就是我们DBA人员需要优先考虑采用的。

参考：
https://blog.csdn.net/qq_39390545/article/details/119112228?spm=1001.2014.3001.5501
https://www.cnblogs.com/bypp/p/7755307.html
https://blog.csdn.net/qq_35590091/article/details/106716519