MySQL面试题--最全面-索引_mysql索引面试题-优快云博客

在InnoDB存储引擎中，我们只需要根据主键值对聚簇索引进行一次查找就能找到对应的记录，而在MyISAM中却需要进行一次回表操作，意味着MyISAM中建立的索引相当于全部都是二级索引。
InnoDB的数据文件本身就是索引文件，而MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。
- MyISAM的表在磁盘上存储在以下文件中： *.sdi（描述表结构）、*.MYD（数据），*.MYI（索引）
- InnoDB的表在磁盘上存储在以下文件中： .ibd（表结构、索引和数据都存在一起）
InnoDB的非聚簇索引data域存储相应记录主键的值，而MyISAM索引记录的是地址。换句话说，InnoDB的所有非聚簇索引都引用主键作为data域。
MyISAM的回表操作是十分快速的，因为是拿着地址偏移量直接到文件中取数据的，反观InnoDB是通过获取主键之后再去聚簇索引里找记录，虽然说也不慢，但还是比不上直接用地址去访问。
InnoDB要求表必须有主键（ MyISAM可以没有）。如果没有显式指定，则MySQL系统会自动选择一个可以非空且唯一标识数据记录的列作为主键。如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型。

InnoDB的辅助索引data域存储相应记录主键的值而不是地址。
InnoDB的数据文件本身就是主索引文件。
MyISAM的索引和数据是分开存储的。

3.一个表中如果没有创建索引，那么还会创建B+树吗？

会

如果我们在创建表时没有创建索引，那么系统会自动为我们生成一个B+树。

如果在创建表的时候指定了主键，比如ID，那么B+树就会以该主键去创建它的叶子节点索引。

如果没有指定主键，那么系统会默认为我们生成一个B+树，使用隐式的row_ID作为主键。这个row ID是系统自动生成的，只供MySQL的内部使用。

4.说一下B+树索引实现原理（数据结构）

B+树索引实现原理主要基于B+树这种数据结构，它是为磁盘或其他直接访问辅助设备而设计的一种平衡的多路查找树。在数据库和文件系统中，B+树被用作索引结构，以加快数据的查找速度。

以下是B+树索引实现原理的详细说明：

数据结构特点：
- 多路平衡：B+树允许每个节点有多个子节点，且所有叶子节点都位于同一层，这有助于保持树的平衡，从而确保查询性能的稳定。
- 节点结构：非叶子节点（内部节点）存储键值和指向子节点的指针，但不存储实际的数据记录。叶子节点存储键值、数据记录的指针以及指向下一个叶子节点的指针。这种设计使得范围查询更加高效。
- 磁盘友好：由于B+树的节点大小与磁盘块大小相近，因此可以充分利用磁盘的I/O操作，减少磁盘访问次数。
索引构建：
- 当向B+树中插入新数据时，会按照键值的大小在树中找到合适的位置进行插入。如果需要，会进行节点的分裂以保持树的平衡。
- 删除数据时，同样需要维护树的平衡，可能涉及节点的合并操作。
查询操作：
- 从根节点开始，根据键值在内部节点中进行查找，沿着指针指向的子节点继续查找，直到到达叶子节点。
- 在叶子节点中，找到与查询键值匹配的记录，或者确定该键值不存在于树中。
- 由于叶子节点之间通过指针相连，范围查询可以通过遍历叶子节点来实现，非常高效。
更新操作：
- 当数据记录发生更新时，B+树索引需要同步更新。如果更新涉及键值的变化，可能需要在树中进行相应的插入或删除操作，以保持索引的一致性。
优化：
- 为了进一步提高性能，B+树索引还可以采用一些优化技术，如预取技术、缓存机制等，以减少磁盘I/O次数，提高查询速度。

案例演示：

假设有一个表index_demo，表中有2个INT类型的列，1个CHAR(1)类型的列，c1列为主键：

 CREATE TABLE index_demo(c1 INT,c2 INT,c3 CHAR(1),PRIMARY KEY(c1)) ;

index_demo表的简化的行格式示意图如下：

我们只在示意图里展示记录的这几个部分：

record_type：表示记录的类型， 0是普通记录、 2是最小记录、 3 是最大记录、1是B+树非叶子节点记录。
next_record：表示下一条记录的相对位置，我们用箭头来表明下一条记录。
各个列的值：这里只记录在 index_demo 表中的三个列，分别是 c1 、 c2 和 c3 。
其他信息：除了上述3种信息以外的所有信息，包括其他隐藏列的值以及记录的额外信息。

将其他信息项暂时去掉并把它竖起来的效果就是这样：

把一些记录放到页里的示意图就是（这里一页就是一个磁盘块，代表一次IO）：

MySQL InnoDB的默认的页大小是16KB，因此数据存储在磁盘中，可能会占用多个数据页。如果各个页中的记录没有规律，我们就不得不依次遍历所有的数据页。如果我们想快速的定位到需要查找的记录在哪些数据页中，我们可以这样做：

下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值
给所有的页建立目录项

以页28为例，它对应目录项2 ，这个目录项中包含着该页的页号28以及该页中用户记录的最小主键值 5。我们只需要把几个目录项在物理存储器上连续存储（比如：数组），就可以实现根据主键值快速查找某条记录的功能了。比如：查找主键值为 20 的记录，具体查找过程分两步：

先从目录项中根据二分法快速确定出主键值为20的记录在目录项3中（因为 12 ≤ 20 < 209 ），对应页9。
再到页9中根据二分法快速定位到主键值为 20 的用户记录。

至此，针对数据页做的简易目录就搞定了。这个目录有一个别名，称为索引 。

InnoDB中的索引方案

我们新分配一个编号为30的页来专门存储目录项记录，页10、28、9、20专门存储用户记录：

目录项记录和普通的用户记录的不同点：

目录项记录的 record_type 值是1，而普通用户记录的 record_type 值是0。
目录项记录只有主键值和页的编号两个列，而普通的用户记录的列是用户自己定义的，包含很多列，另外还有InnoDB自己添加的隐藏列。

现在查找主键值为 20 的记录，具体查找过程分两步：

先到页30中通过二分法快速定位到对应目录项，因为 12 ≤ 20 < 209 ，就是页9。
再到页9中根据二分法快速定位到主键值为 20 的用户记录。

更复杂的情况如下：

我们生成了一个存储更高级目录项的页33 ，这个页中的两条记录分别代表页30和页32，如果用户记录的主键值在 [1, 320) 之间，则到页30中查找更详细的目录项记录，如果主键值不小于320 的话，就到页32中查找更详细的目录项记录。这个数据结构，它的名称是 B+树。

5.聚簇索引与非聚簇索引b+树实现有什么区别？

聚簇索引

特点：

索引和数据保存在同一个B+树中
页内的记录是按照主键的大小顺序排成一个单向链表 。
页和页之间也是根据页中记录的主键的大小顺序排成一个双向链表 。
非叶子节点存储的是记录的主键+页号。
叶子节点存储的是完整的用户记录。

优点：

数据访问更快，因为索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快。
聚簇索引对于主键的排序查找和范围查找速度非常快。
按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库可以从更少的数据块中提取数据，节省了大量的IO操作 。

缺点：

插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键。
更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。

限制：

只有InnoDB引擎支持聚簇索引，MyISAM不支持聚簇索引。
由于数据的物理存储排序方式只能有一种，所以每个MySQL的表只能有一个聚簇索引。
如果没有为表定义主键，InnoDB会选择非空的唯一索引列代替。如果没有这样的列，InnoDB会隐式的定义一个主键作为聚簇索引。
为了充分利用聚簇索引的聚簇特性，InnoDB中表的主键应选择有序的id，不建议使用无序的id，比如UUID、MD5、HASH、字符串作为主键，无法保证数据的顺序增长。

非聚簇索引

（二级索引、辅助索引）

聚簇索引，只能在搜索条件是主键值时才发挥作用，因为B+树中的数据都是按照主键进行排序的，如果我们想以别的列作为搜索条件，那么需要创建非聚簇索引。

例如，以c2列作为搜索条件，那么需要使用c2列创建一棵B+树，如下所示：

这个B+树与聚簇索引有几处不同：

页内的记录是按照从c2列的大小顺序排成一个单向链表 。
页和页之间也是根据页中记录的c2列的大小顺序排成一个双向链表 。
非叶子节点存储的是记录的c2列+页号。
叶子节点存储的并不是完整的用户记录，而只是c2列+主键这两个列的值。

一张表可以有多个非聚簇索引：

6.说一下B+树中聚簇索引的查找（匹配）逻辑

在B+树中，聚簇索引的查找逻辑如下：

从根节点开始查找：与B+树中的其他查找操作一样，聚簇索引的查找也是从B+树的根节点开始的。
根据主键定位到叶子节点：在B+树中，非叶子节点存储了键值和指向子节点的指针。查找过程中，会根据主键的值在非叶子节点中进行比较，并沿着指针指向的子节点继续查找，直到到达叶子节点。
在叶子节点中查找数据记录：叶子节点存储了主键、数据记录的指针以及指向下一个叶子节点的指针。当查找到达叶子节点时，会根据主键的值在叶子节点中进行匹配。如果找到了匹配的主键，那么就可以通过该叶子节点中存储的数据记录指针定位到实际的数据记录。
范围查询优化：由于聚簇索引将数据记录与主键物理地存储在一起，因此在进行范围查询时，可以通过遍历叶子节点中的指针来高效地获取满足条件的数据记录。这种顺序访问的特性使得聚簇索引在范围查询方面具有优势。

需要注意的是，聚簇索引的查找性能与B+树的平衡状态有关。如果B+树的高度过高或者节点分裂过多，可能会导致查找性能下降。因此，在实际应用中，需要根据数据的分布和查询需求来合理地构建和维护聚簇索引。

7.说一下B+树中非聚簇索引的查找（匹配）逻辑

B+树中非聚簇索引的查找（匹配）逻辑主要涉及到两个步骤：首先在B+树中定位到相应的叶子节点，然后再根据叶子节点中存储的信息去访问实际的数据记录。以下是详细的查找逻辑：

定位叶子节点：
- 从B+树的根节点开始，根据非聚簇索引的键值（通常是某个列的值或者多个列的组合）在B+树中进行查找。
- 在查找过程中，会沿着B+树的内部节点（非叶子节点）向下遍历，根据键值的大小比较来确定进入哪个子树。
- 最终，会定位到包含目标键值的叶子节点。这个过程类似于二分查找，每次比较都会缩小查找范围，直到找到匹配的叶子节点或者确定不存在匹配的键值。
访问数据记录：
- 当定位到叶子节点后，需要根据叶子节点中存储的信息去访问实际的数据记录。
- 与聚簇索引不同，非聚簇索引的叶子节点并不直接存储数据记录本身，而是存储了指向数据记录的指针或者主键值。
- 如果是指针，则可以直接根据指针去访问存储数据记录的物理位置；如果是主键值，则需要根据主键值去聚簇索引中查找对应的数据记录。
- 在访问数据记录时，可能还需要进行额外的磁盘I/O操作，因为数据记录可能并不与叶子节点连续存储在一起。

需要注意的是，非聚簇索引的查找性能受到多个因素的影响，包括B+树的高度、节点的分裂情况、磁盘I/O操作的效率等。此外，由于非聚簇索引需要额外的存储空间来存储索引信息和指针或主键值，因此在使用非聚簇索引时需要权衡其带来的查询性能提升和存储空间的开销。

8.平衡二叉树，红黑树，B树和B+树的区别是什么？都有哪些应用场景？

平衡二叉树

基础数据结构
左右平衡
高度差大于1会自旋
每个节点记录一个数据

平衡二叉树（AVL）

AVL树全称G.M. Adelson-Velsky和E.M. Landis，这是两个人的人名。

平衡二叉树也叫平衡二叉搜索树（Self-balancing binary search tree）又被称为AVL树，可以保证查询效率较高。

具有以下特点：

它是一棵空树或它的左右两个子树的高度差的绝对值不超过1
并且左右两个子树都是一棵平衡二叉树。

AVL的生成演示：AVL Tree Visualzation

AVL的问题

众所周知，IO操作的效率很低，在大量数据存储中，查询时我们不能一下子将所有数据加载到内存中，只能逐节点加载（一个节点一次IO）。如果我们利用二叉树作为索引结构，那么磁盘的IO次数和索引树的高度是相关的。平衡二叉树由于树深度过大而造成磁盘IO读写过于频繁，进而导致效率低下。

为了提高查询效率，就需要减少磁盘IO数。为了减少磁盘IO的次数，就需要尽量降低树的高度 ，需要把原来“瘦高”的树结构变的“矮胖”，树的每层的分叉越多越好。针对同样的数据，如果我们把二叉树改成三叉树：

上面的例子中，我们将二叉树变成了三叉树，降低了树的高度。如果能够在一个节点中存放更多的数据，我们还可以进一步减少节点的数量，从而进一步降低树的高度。这就是多叉树。

普通树的问题

左子树全部为空，从形式上看，更像一个单链表，不能发挥BST的优势。
解决方案：平衡二叉树(AVL)

红黑树

hashmap存储
两次旋转达到平衡
分为红黑节点

在这个棵严格的平台树上又进化为“红黑树”{是一个非严格的平衡树左子树与右子树的高度差不能超过1}，红黑树的长子树只要不超过短子树的两倍即可！

当再次插入7的时候，这棵树就会发生旋转

B+ 树和 B 树的差异：

B+树中非叶子节点的关键字也会同时存在在子节点中，并且是在子节点中所有关键字的最大值（或最小）。
B+树中非叶子节点仅用于索引，不保存数据记录，跟记录有关的信息都放在叶子节点中。而B树中，非叶子节点既保存索引，也保存数据记录。
B+树中所有关键字都在叶子节点出现，叶子节点构成一个有序链表，而且叶子节点本身按照关键字的大小从小到大顺序链接。

9.一个b+树中大概能存放多少条索引记录？

真实环境中一个页存放的记录数量是非常大的（默认16KB），假设指针与键值忽略不计（或看做10个字节），数据占 1 kb 的空间：
如果B+树只有1层，也就是只有1个用于存放用户记录的节点，最多能存放 16 条记录。
如果B+树有2层，最多能存放 1600×16=25600 条记录。
如果B+树有3层，最多能存放 1600×1600×16=40960000 条记录。
如果存储千万级别的数据，只需要三层就够了

B+树的非叶子节点不存储用户记录，只存储目录记录，相对B树每个节点可以存储更多的记录，树的高度会更矮胖，IO次数也会更少。

10.使用B+树存储的索引crud执行效率如何？

B+树作为数据库索引结构，在CRUD操作中表现出较高的执行效率。然而，需要注意的是，索引的性能还受到其他因素的影响，如数据库的设计、查询的复杂性、硬件性能等。因此，在实际应用中，需要根据具体情况来选择合适的索引策略和优化方法。

Create（创建）：
- 当向数据库表中插入新数据时，B+树索引也需要相应地更新。B+树通过分裂节点来保持平衡，并确保新插入的数据能够被高效地索引。虽然插入操作可能会涉及到节点的分裂和索引的更新，但由于B+树的高度通常较低（特别是当数据量很大时），这些操作通常可以较快地完成。
Read（读取）：
- 读取操作是B+树索引最擅长的操作之一。由于B+树中的所有数据都存储在叶子节点中，并且叶子节点之间通过指针相连，因此范围查询和点查询都可以非常高效地执行。数据库系统可以通过从根节点开始，沿着B+树结构快速定位到包含所需数据的叶子节点。
- 此外，由于B+树的高度相对较低，查找过程中需要访问的节点数量较少，从而减少了磁盘I/O操作的次数，进一步提高了读取操作的效率。
Update（更新）：
- 更新操作通常涉及查找要更新的数据记录的位置，然后修改该记录的值。由于B+树索引可以快速定位到数据记录的位置，因此更新操作通常可以较快地完成。但是，如果更新涉及到主键的修改，那么索引结构也需要相应地更新，这可能会涉及到更多的操作。
- 在某些情况下，如果更新的数据量很大或者更新模式具有特定的规律，可能还需要对B+树索引进行重建或优化，以维持其性能。
Delete（删除）：
- 删除操作同样需要利用B+树索引来快速定位到要删除的数据记录。一旦找到记录，就可以从B+树中删除相应的键值对。如果删除操作导致节点变得过于稀疏，B+树可能会进行合并操作来保持树的平衡。
- 删除操作可能会对B+树的结构产生一定的影响，但由于B+树的高度较低，这些影响通常不会太大。此外，定期的索引维护和优化操作可以帮助保持B+树索引的性能。

11.什么是自适应哈希索引？

自适应哈希索引是Innodb引擎的一个特殊功能，当它注意到某些索引值被使用的非常频繁时，会在内存中基于B-Tree所有之上再创建一个哈希索引，这就让B-Tree索引也具有哈希索引的一些优点，比如快速哈希查找。这是一个完全自动的内部行为，用户无法控制或配置。

使用命令：

 SHOW ENGINE INNODB STATUS \G ;

查看INSERT BUFFER AND ADAPTIVE HASH INDEX；

12.什么是2-3树 2-3-4树？

多叉树（multiway tree）允许每个节点可以有更多的数据项和更多的子节点。2-3树，2-3-4树就是多叉树，多叉树通过重新组织节点，减少节点数量，增加分叉，减少树的高度，能对二叉树进行优化。

2-3树 下面2-3树就是一颗多叉树

2-3树具有如下特点：

2-3树的所有叶子节点都在同一层。
有两个子节点的节点叫二节点，二节点要么没有子节点，要么有两个子节点。
有三个子节点的节点叫三节点，三节点要么没有子节点，要么有三个子节点。
2-3树是由二节点和三节点构成的树。
对于三节点的子树的值大小仍然遵守 BST 二叉排序树的规则。

2-3-4树：

13.为什么官方建议使用自增长主键作为索引？说一下自增主键和字符串类型主键的区别和影响

自增主键能够维持底层数据顺序写入
读取可以由b+树的二分查找定位
支持范围查找，范围数据自带顺序

字符串无法完成以上操作

14.使用int自增主键后最大id是10，删除id 10和9，再添加一条记录，最后添加的id是几？删除后重启mysql然后添加一条记录最后id是几？

删除之后

如果重启，会从最大的id开始递增
如果没重启，会延续删除之前最大的id开始递增

15.索引的优缺点是什么？

优点

聚簇（主键）索引：

顺序读写
范围快速查找
范围查找自带顺序

非聚簇索引：

条件查询避免全表扫描scan
范围，排序，分组查询返回行id，排序分组后，再回表查询完整数据，有可能利用顺序读写
覆盖索引不需要回表操作

索引的代价

索引是个好东西，可不能乱建，它在空间和时间上都会有消耗：

空间上的代价

每建立一个索引都要为它建立一棵B+树，每一棵B+树的每一个节点都是一个数据页，一个页默认会占用 16KB 的存储空间，一棵很大的B+树由许多数据页组成，那就是很大的一片存储空间。

时间上的代价

每次对表中的数据进行增、删、改操作时，都需要去修改各个B+树索引。而增、删、改操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行一些记录移位、页面分裂、页面回收等操作来维护好节点和记录的排序。如果我们建了许多索引，每个索引对应的B+树都要进行相关的维护操作，会给性能拖后腿。

B 树和 B+ 树都可以作为索引的数据结构，在 MySQL 中采用的是 B+ 树。

但B树和B+树各有自己的应用场景，不能说B+树完全比B树好，反之亦然。

16.使用索引一定能提升效率吗？

使用索引并不一定能提升效率，这取决于多种因素。索引确实能够加速数据的检索速度，但同时也会带来一些额外的开销。以下是关于使用索引对效率影响的一些关键点：

查询选择性：索引对于具有高选择性的查询（即返回结果集很小的查询）非常有效。然而，对于低选择性的查询（返回大量数据的查询），索引可能并不总是能提高性能，因为数据库系统仍然需要读取和处理大量的数据行。
插入、更新和删除的开销：索引会增加插入、更新和删除操作的开销，因为每次数据变动时，相关的索引也需要进行相应的更新。如果表中的数据变动非常频繁，而查询性能并不是瓶颈，那么过多的索引可能会降低整体的性能。
索引维护：索引需要定期进行维护，以确保其性能和准确性。这包括重建或重新组织索引，以及处理索引碎片等问题。这些维护操作可能会消耗系统资源，并可能影响正常的数据库操作。
覆盖索引：如果一个查询只需要访问索引中的信息，而不需要访问实际的数据行（即索引覆盖了查询），那么这种查询通常会非常快。但是，如果查询需要访问的数据不在索引中，那么数据库系统还需要回表查找数据，这可能会降低查询性能。
硬件和存储：索引的性能也受到硬件和存储系统的影响。例如，如果磁盘I/O成为瓶颈，那么即使使用了索引，查询性能也可能无法显著提升。
查询优化器的选择：数据库系统的查询优化器会根据统计信息和查询条件选择是否使用索引。有时，即使存在可用的索引，优化器也可能选择不使用它，而是选择全表扫描或其他更高效的执行计划。

17.如果是大段文本内容，如何创建（优化）索引？

 B 树和 B+ 树都可以作为索引的数据结构，**在 MySQL 中采用的是 B+ 树。**

第一种方式是分表存储，然后创建索引

第二是使用es为大文本创建索引

18.什么是聚簇索引？

（参考05）

聚簇索引是一种特殊的索引结构，在关系型数据库中用于重新组织磁盘上的实际数据，使数据按照指定的一个或多个列的值进行排序。由于聚簇索引的索引页面指针指向数据页面，使用聚簇索引查找数据通常比使用非聚簇索引更快。每张表只能建一个聚簇索引，并且建聚簇索引需要至少相当于该表120%的附加空间，以存放该表的副本和索引中间页。

聚簇索引的底层原理是将数据行按照聚簇索引的键值顺序进行排序，并且在索引叶子节点中存储完整的数据行。这种结构使得基于聚簇索引的查询更加高效，因为可以通过一次索引查找获得完整的数据行，减少了磁盘的随机读取。

聚簇索引的优点包括提高查询性能、减少磁盘空间的使用以及支持范围查询。然而，它也有一些缺点，比如数据的插入和更新操作可能较慢，因为数据行的物理位置可能需要重新排序。此外，聚簇索引对于主键的排序查找和范围查找速度非常快，但如果插入数据时不按照主键的顺序进行，可能会出现页分裂，严重影响性能。

19.一个表中可以有多个（非）聚簇索引吗？

聚簇索引只能有一个

非聚簇索引可以有多个

20.聚簇索引与非聚集索引的特点是什么？

（参考05）

21.CRUD时聚簇索引与非聚簇索引的区别是什么？

聚簇索引插入新值比采用非聚簇索引插入新值的速度要慢很多，因为插入要保证主键不能重复
聚簇索引范围，排序查找效率高，因为是有序的
非聚簇索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据

22.非聚簇索引为什么不存数据地址值而存储主键？

因为聚簇索引中有时会引发分页操作、重排操作数据有可能会移动

23.什么是回表操作？

回表操作是指在数据库查询过程中，当数据库引擎无法直接从索引中获取所需的数据，需要通过索引查找到数据行的位置，然后再去实际的数据表中获取完整的数据行的过程。这个过程称为回表操作。回表操作会增加额外的IO开销和查询时间，因为需要额外的步骤去获取完整的数据行。通常情况下，回表操作发生在查询语句中需要返回的列不在覆盖索引中的情况下。为了减少回表操作的发生，可以使用覆盖索引或者调整查询语句的列选择。

例：id age name sex

age -> index

select * from user where age >20 ;

第一次取回id，第二次（回表）根据id拿到完整数据。

24.什么是覆盖索引？

覆盖索引是指在数据库表上建立的一种特殊索引，包含了查询所需的所有列，而不仅仅是用于筛选和排序的列。当查询语句中的列都包含在覆盖索引中时，数据库引擎可以直接从索引中获取数据，而不需要再去访问实际的数据行，从而提高查询性能和减少IO开销。覆盖索引通常用于查询中只需要返回索引列的情况，可以有效地减少数据库的查询时间。

例：id age name sex

age -> index

select * from user where age >20 ;

第一次取回id，第二次（回表）根据id拿到完整数据

age,name -> index

select age from user where age >20 and name like"张%" ;

覆盖索引不会回表查询，查询效率也是比较高的。

25.非聚集索引一定回表查询吗?

不一定，只要b+树中包含的字段（创建索引的字段），覆盖（包含）想要select 的字段，那么就不会回表查询了。

26.为什么要回表查询？直接存储数据不可以吗？

为了控制非聚簇索引的大小

27.如果把一个 InnoDB 表的主键删掉，是不是就没有主键，就没办法进行回表查询了？

不是，InnoDB会生成rowid辅助回表查询

28.什么是联合索引（组合索引/复合索引）

在数据库表上同时对多个列建立索引。当查询语句中涉及到组合索引或复合索引中的多个列时，数据库引擎可以利用这个索引来快速定位到符合条件的数据行。

为c2和c3列建立联合索引，如下所示：

c2，c3 - > index

c3,c2 -> index

where c3=?

全职匹配

最左前缀

29.复合索引创建时字段顺序不一样使用效果一样吗？

在创建复合索引时，字段的顺序是会影响索引的效果的。具体来说，复合索引中字段的顺序会影响索引的覆盖性和查询效率。

覆盖性：当查询语句中的列顺序与复合索引中的列顺序完全一致时，可以利用覆盖索引的优势，避免回表操作，从而提高查询效率。
查询效率：复合索引中字段的顺序也会影响查询的效率。通常情况下，将查询频率高、区分度高的列放在复合索引的前面，可以更好地利用索引的优势，提高查询效率。

因此，虽然复合索引中字段的顺序不同可能会对索引的效果产生影响，但具体的影响效果还是要结合具体的查询场景和数据特点来决定。在创建复合索引时，需要根据实际情况选择合适的字段顺序，以达到最佳的查询性能。

30.什么是唯一索引？

随表一起创建索引：

单独建创索引：

31.唯一索引是否影响性能？

唯一索引在某些情况下可能会对性能产生一定的影响，但通常情况下，这种影响是可以忽略的。唯一索引的主要作用是确保列或列组的数值在表中是唯一的，这可以防止出现重复数据。在插入、更新和删除数据时，数据库引擎需要检查唯一索引，以确保数据的唯一性。

因为唯一索引需要额外的检查和维护操作，所以在数据的写入操作（如插入、更新、删除）时可能会稍微影响性能，特别是在大量数据的情况下。然而，在大多数情况下，这种影响是可以忽略的，而且唯一索引可以提高数据的完整性和查询效率。

总的来说，唯一索引对性能的影响是较小的，而其带来的数据完整性和查询效率的提升通常是值得的。在设计数据库时，根据具体的需求和场景，合理使用唯一索引可以提高系统的性能和数据质量。

32.什么时候使用唯一索引？

业务需求唯一字段的时候，一般不考虑性能问题

【强制】业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。说明：不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的；另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。

33.什么时候适合创建索引，什么时候不适合创建索引？

适合创建索引

频繁作为where条件语句查询字段
关联字段需要建立索引
排序字段可以建立索引
分组字段可以建立索引(因为分组前提是排序)
统计字段可以建立索引（如.count(),max()）

不适合创建索引

频繁更新的字段不适合建立索引
where，分组，排序中用不到的字段不必要建立索引
可以确定表数据非常少不需要建立索引
参与mysql函数计算的列不适合建索引

创建索引时避免有如下极端误解：

1）宁滥勿缺。认为一个查询就需要建一个索引。

2）宁缺勿滥。认为索引会消耗空间、严重拖慢更新和新增速度。

3）抵制惟一索引。认为业务的惟一性一律需要在应用层通过“先查后插”方式解决。

34.什么是索引下推？

5.6之前的版本是没有索引下推这个优化的

Using index condition：叫作 Index Condition Pushdown Optimization （索引下推优化）

如果没有索引下推（ICP），那么MySQL在存储引擎层找到满足content1 > 'z'条件的第一条二级索引记录。主键值进行回表，返回完整的记录给server层，server层再判断其他的搜索条件是否成立。如果成立则保留该记录，否则跳过该记录，然后向存储引擎层要下一条记录。
如果使用了索引下推（ICP），那么MySQL在存储引擎层找到满足content1 > 'z'条件的第一条二级索引记录。不着急执行回表，而是在这条记录上先判断一下所有关于idx_content1索引中包含的条件是否成立，也就是content1 > 'z' AND content1 LIKE '%a'是否成立。如果这些条件不成立，则直接跳过该二级索引记录，去找下一条二级索引记录；如果这些条件成立，则执行回表操作，返回完整的记录给server层。

总结：

未开启索引下推：

根据筛选条件在索引树中筛选第一个条件
获得结果集后回表操作
进行其他条件筛选
再次回表查询

开启索引下推：在条件查询时，当前索引树如果满足全部筛选条件，可以在当前树中完成全部筛选过滤，得到比较小的结果集再进行回表操作

35.有哪些情况会导致索引失效？

计算、函数导致索引失效

 -- 显示查询分析
 EXPLAIN SELECT * FROM emp WHERE emp.name  LIKE 'abc%';
 EXPLAIN SELECT * FROM emp WHERE LEFT(emp.name,3) = 'abc'; --索引失效

LIKE以%，_ 开头索引失效

拓展：Alibaba《Java开发手册》

【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。

 EXPLAIN SELECT * FROM emp WHERE name LIKE '%ab%'; --索引失效

不等于(!= 或者<>)索引失效

 EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.name = 'abc' ;
 EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.name <> 'abc' ; --索引失效

IS NOT NULL 失效和 IS NULL

 EXPLAIN SELECT * FROM emp WHERE emp.name IS NULL;
 EXPLAIN SELECT * FROM emp WHERE emp.name IS NOT NULL; --索引失效

注意：当数据库中的数据的索引列的NULL值达到比较高的比例的时候，即使在IS NOT NULL 的情况下 MySQL的查询优化器会选择使用索引，此时type的值是range（范围查询）

 -- 将 id>20000 的数据的 name 值改为 NULL
 UPDATE emp SET `name` = NULL WHERE `id` > 20000;
 
 -- 执行查询分析，可以发现 IS NOT NULL 使用了索引
 -- 具体多少条记录的值为NULL可以使索引在IS NOT NULL的情况下生效，由查询优化器的算法决定
 EXPLAIN SELECT * FROM emp WHERE emp.name IS NOT NULL

类型转换导致索引失效

 EXPLAIN SELECT * FROM emp WHERE name='123'; 
 EXPLAIN SELECT * FROM emp WHERE name= 123; --索引失效

复合索引未用左列字段失效
如果mysql觉得全表扫描更快时（数据少）;

36.为什么LIKE以%开头索引会失效？

当使用LIKE操作符进行模糊查询时，如果通配符%出现在搜索字符串的开头，即'%xxx'，那么数据库索引通常会失效。这是因为通配符%在搜索字符串的开头会导致数据库无法有效地利用索引进行搜索。

数据库索引通常是按照排序顺序存储的，如果搜索字符串的开头包含通配符%，数据库引擎无法确定索引的排序顺序，无法有效地使用索引进行搜索。在这种情况下，数据库引擎通常会放弃使用索引，而是进行全表扫描来找到匹配的数据，这会导致查询效率大大降低。

为了避免索引失效，可以考虑使用其他方法来优化模糊查询，比如使用全文搜索引擎或者将数据预处理为适合索引的形式。另外，可以考虑使用反向索引或者倒序存储数据来避免在搜索字符串开头使用通配符%时索引失效的问题。例：

id,name,age

name 创建索引

select * from user where name like '%明'

type=all

select name,id from user where name like '%明'

type=index

张明

(name,age)

其实并不会完全失效，覆盖索引下会出现type=index，表示遍历了索引树，再回表查询，

覆盖索引没有生效的时会直接type=all

没有高效使用索引是因为字符串索引会逐个转换成accii码，生成b+树时按首个字符串顺序排序，类似复合索引未用左列字段失效一样，跳过开始部分也就无法使用生成的b+树了

37.一个表有多个索引的时候，能否手动选择使用哪个索引？

不可用手动直接干预，只能通过mysql优化器自动选择

38.如何查看一个表的索引？

39.能否查看到索引选择的逻辑？是否使用过optimizer_trace？

40.多个索引优先级是如何匹配的？

主键（唯一索引）匹配
全值匹配（单值匹配）
最左前缀匹配
范围匹配
索引扫描
全表扫描

一般性建议

Ø 对于单键索引，尽量选择过滤性更好的索引（例如：手机号，邮件，身份证）

Ø 在选择组合索引的时候，过滤性最好的字段在索引字段顺序中，位置越靠前越好。

Ø 选择组合索引时，尽量包含where中更多字段的索引

Ø 组合索引出现范围查询时，尽量把这个字段放在索引次序的最后面

Ø 尽量避免造成索引失效的情况

41.使用Order By时能否通过索引排序？

在使用ORDER BY子句进行排序时，数据库引擎通常可以通过索引来加快排序的过程，但并不是所有情况下都能通过索引排序。具体取决于以下几个因素：

是否使用了覆盖索引：如果查询中包含在索引中的列，并且这些列是ORDER BY子句中指定的列，那么数据库引擎可以使用覆盖索引来避免对实际数据行的访问，从而通过索引完成排序操作。
索引的排序顺序：如果索引的排序顺序与ORDER BY子句一致，那么数据库引擎可以直接使用索引进行排序。如果排序顺序不一致，数据库引擎可能需要额外的排序操作，导致无法通过索引排序。
索引的选择性：索引的选择性指索引中不同值的数量与总行数的比率。如果索引的选择性较低，即索引中的值重复性较高，数据库引擎可能会选择不使用索引进行排序。

42.通过索引排序内部流程是什么？

当数据库引擎需要通过索引来排序数据时，通常会遵循以下内部流程：

查询优化器选择使用索引排序：在执行查询之前，查询优化器会评估是否可以使用索引来排序数据。如果查询中包含ORDER BY子句，并且索引可以支持排序操作，优化器会选择使用索引排序。
访问索引并获取排序结果：数据库引擎会通过索引访问数据，并按照索引中的排序顺序获取数据。如果使用覆盖索引，可以直接通过索引获取排序结果，而不需要访问实际的数据行。
排序操作：如果索引无法完全满足排序需求（比如索引的排序顺序与ORDER BY子句不一致），数据库引擎可能会进行额外的排序操作。这时，数据库引擎会将获取的数据进行排序，以满足ORDER BY子句的要求。
返回排序结果：最终，数据库引擎会返回排序后的结果集给用户。如果排序操作完全通过索引完成，可以提高查询性能；如果需要额外的排序操作，可能会增加一定的开销。

通过索引排序的内部流程包括选择使用索引、访问索引获取数据、排序操作和返回排序结果。通过合理设计索引和查询语句，可以提高排序操作的效率和性能。

43.什么是双路排序和单路排序

单路排序：一次取出所有字段进行排序，内存不够用的时候会使用磁盘

双路排序：取出排序字段进行排序，排序完成后再次回表查询所需要的其他字段

果不在索引列上，filesort有两种算法： mysql就要启动双路排序和单路排序

双路排序（慢）

Select id,age,name from stu order by name;

Ø MySQL 4.1之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据，读取行指针和order by列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出

Ø 从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。

Ø 取一批数据，要对磁盘进行两次扫描，众所周知，I\O是很耗时的，所以在mysql4.1之后，出现了第二种改进的算法，就是单路排序。

单路排序（快）

从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间，因为它把每一行都保存在内存中了。

结论及引申出的问题

但是用单路有问题

在sort_buffer中，单路比多路要多占用很多空间，因为单路是把所有字段都取出, 所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序（创建tmp文件，多路合并），排完再取sort_buffer容量大小，再排……从而多次I/O。

单路本来想省一次I/O操作，反而导致了大量的I/O操作，反而得不偿失。

优化策略

Ø 增大sort_buffer_size参数的设置

Ø 增大max_length_for_sort_data参数的设置

Ø 减少select 后面的查询的字段。禁止使用select *

提高Order By的速度

1. Order by时select * 是一个大忌。只Query需要的字段，这点非常重要。在这里的影响是：

l 当Query的字段大小总和小于max_length_for_sort_data 而且排序字段不是 TEXT|BLOB 类型时，会用改进后的算法——单路排序，否则用老算法——多路排序。

l 两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O，但是用单路排序算法的风险会更大一些，所以要提高sort_buffer_size。

2. 尝试提高 sort_buffer_size

l 不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程（connection）的 1M-8M之间调整。 MySQL5.7和8.0，InnoDB存储引擎默认值是1048576字节，1MB。

SHOW VARIABLES LIKE '%sort_buffer_size%';

3. 尝试提高 max_length_for_sort_data

l 提高这个参数，会增加用改进算法的概率。

SHOW VARIABLES LIKE '%max_length_for_sort_data%';

#5.7默认1024字节

#8.0默认4096字节

l 但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率。如果需要返回的列的总长度大于max_length_for_sort_data，使用双路算法，否则使用单路算法。1024-8192字节之间调整

44.group by 分组和order by在索引使用上有什么区别？

group by 使用索引的原则几乎跟order by一致，唯一区别：

group by 先排序再分组，遵照索引建的最佳左前缀法则
group by没有过滤条件，也可以用上索引。Order By 必须有过滤条件才能使用上索引。

45.如果表中有字段为null，又被经常查询该不该给这个字段创建索引？

应该创建索引，使用的时候尽量使用is null判断。

IS NOT NULL 失效和 IS NULL

 EXPLAIN SELECT * FROM emp WHERE emp.name IS NULL;
 EXPLAIN SELECT * FROM emp WHERE emp.name IS NOT NULL; --索引失效

 -- 将 id>20000 的数据的 name 值改为 NULL
 UPDATE emp SET `name` = NULL WHERE `id` > 20000;
 
 -- 执行查询分析，可以发现 IS NOT NULL 使用了索引
 -- 具体多少条记录的值为NULL可以使索引在IS NOT NULL的情况下生效，由查询优化器的算法决定
 EXPLAIN SELECT * FROM emp WHERE emp.name IS NOT NULL