MySQL索引底层原理相关问题自总结（难度对标18K-25K薪资，已总结80+，持续更新中）

注：以下所有内容均为自己总结的笔记，涉及底层原理，难度对标18K-25K薪资，偏理论，不保证百分百准确性。

索引查找快速的原理？

创建索引的本质是排序，排好序之后再找数据就快了。
对于B+tree索引，B+tree对数据排序后采用多路查找思想的非线性查找方案，减少了大量的查询次数，从而避免多次磁盘io，进而快速找到结果。

为什么推荐用自增id做主键？

自增id直观，且不用刻意维护这个字段，减少工作量，还能避免主键更新引起的页分裂。
举例说明页分裂：数据是存在页上的，页1存储id为1、2、5的数据，如果没有设置自增，如果突然新增了id为3、4的数据，页1无剩余空间存储，就需要将页1数据进行拆分，页1存储id为1、2、3的数据，页2存储id为4、5的数据，分裂的目的是为了排序，排序的目的是为了方便查找。分裂需要消耗计算资源用于更改数据，这种非必要发生的操作就尽量避免。

什么是链表 ，在索引中起到了什么作用？

链表是一种线性数据结构，由节点组成，每个节点包含两部分：数据和指向相邻节点的指针。
链表分单向和双向。
单向：节点只有一个指针，指向下一个节点
双向：每个节点有两个指针，一个指向前一个节点，一个指向后一个节点。
根据对链表的操作，又可以分为队列和栈。
队列：先进先出（LPush->RPop，或Ppush->Rpop）。
栈：先进后出（LPush->LPop，或RPush->RPop）。

MySQL InnoDB引擎和MyISAM引擎，都用的B+tree算法作索引，在叶子节点，每个节点间使用向左或者向右的指针，来移动指针，这也是索引支持区间查询的原因，叶子节点间组成一个链表。

什么是B+tree？

是一种通过排序，方便查找的数据结构，特别是在数据库和文件系统的实现中广泛应用。它是一种平衡树的变体。
平衡性： 所有叶子节点都位于同一层，使得在树的高度方面达到平衡，从而保持高效的查找、插入和删除操作。
有序性： B+树的叶子节点按照键值大小顺序存储，使得范围查询变得更为高效。
多路搜索： 每个非叶子节点都有多个子节点，允许更多的分支，提高搜索效率。
适用于范围查询： 由于有序性和多路搜索的特性，B+树在范围查询方面表现优秀。
在数据库系统中，B+树常被用作索引结构，用于加速对数据库表的查询操作。其设计考虑了磁盘存储的特性，使得在磁盘上的读写操作更为高效。

B+tree比二叉树好在那里？

更加平衡：二叉树在遇到一组非混乱的数据集合下，树的层级会变的很高，意味着io的次数变多，B+tree避开了这个问题。
多路搜索：二叉树为双路搜索，B+tree为多路搜索，极大提高了搜索效率。
查找方便：无论是区间查找还是定值查找，B+tree都比二叉树查找方便，二叉树需要中序遍历才能得到有序序列。

Btree与B+tree区别？

Btree：非叶子节点存放数据，叶子节点无指针，支持区间查找。
B+tree：非叶子结点不存放数据，叶子节点有指针，支持更快速的区间查找。
正因为Btree非叶子节点存放数据，查询起来无法像B+tree一样叶子节点间依靠链表进行范围查询，所以区间查询效率低。
Btree做查找，需要在叶子节点和非叶子节点之间来回跳跃搜索，来回的跳跃，意味着需要更多的磁盘io，而B+tree只需要从非叶子节点到叶子节点即可，
所以稳定性不如B+tree。

为什么MySQL采用B+tree？

查询高效：因为B+tree采用多路非线性查找思想，降低树的层级，减少磁盘io。
支持区间查询：因为数据全在叶子节点上，每个节点之间有指针做关联。
算法稳定：不会向二叉搜索树那样，层级和数据有关，查找情况时好时坏。

页根页之间怎么关联？

双向链表。
双向链表因为有序，所以可以适用二分查找。
双向链表，链的是行中存储的元数据，这归行格式管理，行格式存储的其中一项叫做record_type，有4个值，0表示普通用户记录，1表示目录项纪录，2表示当前页的最小值，3表示当前页的最大值。
使用0和1区分是目录页还是数据页。使用2和3走索引时用于定位，进行区间或等值查找。

为什么数据在页上，页本身要加索引？

虽然一个页可以存储多条数据，但是在大数据情况下，一个页不够存就需要多个页，为了避免查找数据时数据不用对大量的页挨个遍历。与是也加上了B+tree用于查找。

B+tree 叶子节点之间怎么关联

双向的指针。

什么是聚簇索引？

在InnoDB引擎中，索引的叶子节点存储的是实际的数据行，数据即索引，索引即数据。
好处就是能带来快速的查询速度，通过索引就可以找到实实在在的数据。
一般一个表只能有一个聚簇索引，一般为主键，因为数据的排列就是按照索引来的，如果一个表中有多个聚簇索引，一是不知道二级索引参考哪个，二是太占空间。

MyISAM有聚簇索引吗？

没有。
查看MyISAM的二进制文件，有.MYI（存储索引）和.MYD(存储数据)后缀结尾的文件，他们的索引和数据是分开的，不符合数据即索引，索引即数据的特点。
MyISAM的叶子结点存储的是数据的位置信息。

MyISAM中B+tree的叶子节点存储的是数据的地址，也需要类似回表的操作，为什么性能也不慢？

因为寻址的性能也挺高的，如果速度慢，就不会这么主流了。

为什么InnoDB比MyISAM有更好的并发性能，是因为索引上有什么不同之处吗？

不是，并发性和不同引擎的索引没有太多相关性。
InnoDB有更好的并发性能，是因为它支持粒度更小的行级锁，并发情况下，事务用于保持数据一致性，锁是并发控制必备的机制。

为什么InnoDB不推荐用较长的数据做主键？

大数据情况下，InnoDB引擎创建的二级B+tree索引，叶子节点是主键，较长的主键，会占用更多的位置。
而MyISAM中B+tree的叶子节点，存储的是数据的位置。

如何区分一棵B+tree是不是聚簇索引？

看这颗树的叶子节点上，存储的是实实在在的数据，还是根据当前列关联的主键。

聚簇索引的优点？

把实实在在的数据当索引，不用回表，性能很高，因为通过索引找到的那条数据，就是所在行的数据。
InnoDB引擎，MySQL 默认情况下使用自增主键作为聚簇索引，这便是主键查询快的原因。

聚簇索引的缺点？

如果插入的数据不是自增的数字id，可能引起索引分裂，降低性能。
占用较大的空间。

为什么主键查询性能很高？

InnoDB引擎，MySQL 默认情况下使用自增主键作为聚簇索引，不用回表。这便是主键查询快的原因。
具有唯一索引，定值查找，查到后不必接着找。

为什么不建议用UUID作为主键？

避免索引分裂，影响插入数据时的性能问题。
必须要明白，索引的本质是排序，索引查找的本质是根据排好序的数据进行查找。
可能后生成的uuid，根据ASCII码字典排序，会排到先生成uuid的前面，插入新值，则需要重新排序，就要破坏掉原本的索引结构，这个过程将消耗时间和算力。

c3dc38e1-8db2-4e9f-9fe4-735e88facdb4，像是这种类型的数据叫做uuid。
一般有两个好处：

1. 在分布式环境下保证唯一性，因为够长且重复概率太低，否则，A模块的id=1，可能会与B模块的id=1混淆。
2. 黑客不容易猜到相邻的uuid是什么，就算程序有越权漏洞，也不会很难根据原ID猜测其它ID。

其次是uuid，占用空间比int类型更大，使得其它二级索引，存储主键时，占用更多的空间。

用自增id就不会出现索引分裂的情况吗？

不是的。
自增的有序id，只会减少插入数据时的分裂，当大数据时的新增引起的B+tree分层，或者对数据的的插入和删除操作，都可能为了局部重建索引，触发分裂操作。

什么是非聚簇索引？

非聚簇索引则将索引与实际数据行分开存储，索引的叶子节点存储的是当前索引值与主键（MyISAM则是当前索引值与行地址），不是所在行的数据。

什么是二级索引？

级索引通常指的是除了表的主键之外创建的额外索引。

什么是辅助索引？

一般是指非聚簇索引或者二级索引。

什么是回表？

InnoDB引擎，在非聚簇的B+tree索引上,树的叶子节点存储的是当前索引字段数据和主键的值，当前的叶子节点数据与主键做逻辑上的关联，而不是存储所在行的全部数据，所以需要根据主键，再次查询一遍数据，这个过程叫回表。
因内部多了一轮的查询流程，所以性能有所降低，所以能用主键查询的场景，就不要使用其它字段。

为什么不通过像聚簇索引一样的方式避免回表？

技术上能实现，但是缺点很明显，空间换时间的代价太大。
在非聚簇的B+tree上,树的叶子节点重复存储所在行的值，会造成大量的空间浪费。
其次是更新代价太大，可能更新一小块数据，就需要对这些索引上的数据做同步更新。
所以做逻辑关联更好。

为什么插入、更新、删除数据时，非聚簇索引比聚簇索引性能略高？

插入数据引起的索引分裂问题，非聚簇索引只需要调整当前索引的位置和主键就好，而非聚簇所以需要移动整行的数据。

什么是联合索引？

组合索引、复合索引、多列索引，联合索引一个意思，多个字段组合去创建索引。
索引排序规则：先按照左边的字段进行排序，如果左边字段相同，再根据右边的字段排序。

什么是前缀索引？

MySQL 前缀索引是一种，它只对列值的前部进行索引，而不是对整个列值进行索引。
做法：alter table 表名 add index (filed_name(长度));
优点：主要用于控制索引大小，由于底层在比较时字符串长度较短，所以比较起来也比单列索引块。
缺点：有误差，所以需要在空间和产品服务方面做取舍。
评估设置长度：需要根据数据情况测试，如果count(distince left('字段名', 10)) / count(*) 等于1左右，说明截取字段前10个字符去重后的数量，等于总数，说明依照前10个字符就能辨识度很高。如果结果略小于1，说明辨识度还不够，可以取12，如果远小于1，长度取20再试试，以此类推。
补充：《阿里巴巴Java开发手册》【强制】在varchar简历索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。

联合索引算不算聚簇索引？

相似但不算，联合索引关联的不是所在行的全部字段，而是部分字段。

为什么单表数据不能超过2000万条

这是个粗略的理论值，很多人说超过这个数，会把B+tree的层级转为4层，其实不准的。
这个还是要看叶子节点数据的大小，如果叶子结点很大，需要更多的页，则存不了太多，如果叶子节点数据很少，有人推算，存1个亿也没问题的。

先排除一些元数据的存储：数据存储在页上，每页大小16KB，每页需要开辟一些新的空间来存储元数据（例如指向上一页下一页的指针），页头存储文件头38字节，页面头56字节，最小记录和最大记录26个字节，为了保证不出错，出现了校验和的机制，这块功能的存储被放到了页尾，占8个字节。页里的数据呢，为了方便查找每行的数据，所以包含页目录（采用二分法，把查询复杂度从O(n)优化为O(log n)），这也占空间，这些可以粗略的估计为占用了1KB。

声明代数：假设非叶子节点指向叶子节点的指针数量为X，叶子节点能够容纳的行数为Y，B+tree层数为Z，那么能存储的总行数就是X^z-1 * Y。

计算X：主键假设用bigint，占8个字节，页号这个元数据占4个字节，非叶子节点一条数据占12个字节，15KB / 12B = 1280。
计算Y：假设一个行数据为1KB，也就是说可以放15条数据。

若Z为1：1280⁰ * 15 = 15行
若Z为2：1280¹ * 15 = 19200行
若Z为3：1280² * 15 = 24576000行
若Z为3：1280³ * 15 = 31457280000行

但是这是理想情况，很多主键id都用无符号int，能节省4个字节，行数大小也不确定，所以这是个理论值，究竟是多少，需要根据实际情况讨论。

什么是最左匹配原则？

生效的情况：
abc创建联合索引，where a = 'v1' and b = 'v2' and c= 'v3'，where顺序可以颠倒，但是必须都是and，左边的列不能包含区间查询。
失效的情况：
多字段创建联合索引，如果联合索引左边的字段的查询条件不存在，或者联合索引左边字段使用的区间查询，或者使用了or，都会导致索引失效。
注意，这里说的顺序，是联合索引的顺序，不是where条件的顺序。

底层什么原因导致最左匹配原则？

B+tree联合索引排序，是根据ASCII码的字典顺序进行从左到右依字符排序，然后依字段从左到右排序，没有其它方向的排序，这就不能兼容更多种的查询方式。

假如abc三个字段创建联合索引，where a = v1 and b = v2 and c = v3，此时字段索引的使用情况？

每个字段都能用上索引。

假如abc三个字段创建联合索引，where a = v1 or b = v2 or c = v3，此时字段索引的使用情况？

a能用上索引，b和c都无法使用索引，因为and是属于流水线式的筛选，而or是与前面的搜索条件不相关的个体，b和c都没有左边的字段配合成为联合索引。

假如abc三个字段创建联合索引，where a = v1 and b > v2 and c = v3，此时字段索引的使用情况？

a能用上索引，b能用上索引，c无法使用上索引，因为b是区间查询导致c无法按索引查询。

假如abc三个字段创建联合索引，where a = v1 and c = v3，此时字段索引的使用情况？

a可以用上索引，c用不上索引，因为缺少b。

假如abc三个字段创建联合索引，where b = v1 and c = v3，此时字段索引的使用情况？

b和c都用不上索引，因为缺少a。

假如abc三个字段创建联合索引，where a = v1 and b = v2 and c = v3 or c is null，此时字段索引的使用情况？

abc三个字段全部能用上索引。

假如abc三个字段创建联合索引，where a = v1 and b = v2 and c = v3 or d + 1 = 10，此时字段索引的使用情况？

用explain实测，type为all，索引用不上了，全表查，因为使用了表达式。

假如abc三个字段创建联合索引，where a = v1 and b = v2 and c = v3 and d + 1 = 10，此时字段索引的使用情况？

用explain实测，type为ref，并根据key_len字段评估，abc都能使用索引。

假如abc三个字段创建联合索引，where a = v1 and b = v2 and c = v3 and length(d) < 20，此时字段索引的使用情况？

用explain实测，type为ref，并根据key_len字段评估，abc都能使用索引。

假如abc三个字段创建联合索引，where a = v1 and b = v2 and c = v3 or length(d) < 20，此时字段索引的使用情况？

用explain实测，type为all，索引用不上了，全表查，因为使用了函数。

假如abc三个字段创建联合索引，where a = v1 and b = v2 and c = v3 or c = v4，此时字段索引的使用情况？

用explain实测，type为all，索引用不上了，全表查，因为c列没有建索引。

假如abc三个字段创建联合索引，where a = v1 and b = v2 and c = v3 and c = v4，此时字段索引的使用情况？

用explain实测，type为ref，并根据key_len字段评估，abc都能使用索引。

假如abc三个字段创建联合索引，where a > v1 and b = v2 and c = v3，索引失效，explain type为all，数据量大还经常查询，怎么办？

创建bca的联合索引即可，alter table 表名 add index(b,c,a)，每个字段，最好加上索引长度。
abc的联合索引，如果用不上，删掉。alter table 表名 drop index 索引名。

为什么不推荐mysql频繁使用null值？

null值是个特殊的存在，在sql查询上，即使是唯一索引列，也允许插入多个null值，这影响了了唯一索引的唯一性约束。
其次有些查询，用where field = ''，或者where filed = null，都是匹配补上的，只能用is null。
null值影响聚合函数的使用，导致count(字段)结果不符合真实情况。
否则就难以区分到底是没有关联记录还是其他情况。

什么是索引下推？

索引下推简称ICP，优化SQL执行的一种策略，将where条件下推至存储引擎执行，减少回表的数据量提高性能。
一般是针对二级索引说的，有多个where条件时，执行完第一个条件不着急回表，用剩余的数据再次执行第二个where条件，减少回表的数据量。
索引下推常见在联合索引中，只有使用了联合索引中的字段的时候，才可以。
举例：百万条数据，第一次where之后剩下1000条，执行完第二次where后身下5条，只需要回表5条数据即可，避免第一次where条件剩下的1000条数据回表，然后在执行第二个where，再回表。

为什么有覆盖索引时，不支持索引下推？

使用了覆盖索引，说明索引的数据满足了当前select查询，不需要回表，已经不需要下推了。
索引下推有个暗含的前提，是索引无法完全满足当前查询前提的优化策略，且where的字段又包含在联合索引中，索引下推的终极目的是减少回表数据数量，既然不需要回表，那就不需要下推。

为什么相关子查询，不支持索引下推？

相关子查询，指的是子sql与父sql的参数动态关联，这会导致子SQL语句的参数处于动态（不确定）状态，导致索引下推的目标都无法确定，所以不行。

什么是mysql filesort？

这是mysql explain中Extra中可能会展示的东西，当然也是一种机制，在order by的场景中去用。
这种方式指的是在内存中排序，效率略低，因为没有按照索引排序，尽量避免。
与之相对的，有个index排序，可以按照索引自然而然的排序，效率偏高。

filesort两种排序算法是什么？

双路排序：相对较慢。先找到orderby的列，然后排序，再根据排序的字段查找其它字段，类似回表，所以慢。其次相对于单路排序更可能发生随机io，order by排序后的数据，可能不在同一个页上，这个过程需要来回的读取页中的数据。
单路排序：相对较快，根据orderby的列，一次性取出来所有字段，然后再排序。

什么是覆盖索引？

要查询的字段上有索引，索引中的字段涵盖了select字段的结果，因为不需要回表操作去查询整行数据，避免回表的随机io（回表的数据可能不在同一个页上），这是一种性能优化的提现。
需要尽可能少select 字段的数量，避免使用select *。

什么情况下not in、is not null、<>、!=，左%能用上索引？

覆盖索引。
要查询的字段上有索引，不用回表，卸掉了一个重担，MySQL优化器认为这代价不大，所以选择用索引。
这种情况下，explain type为index。

什么是Hash索引？

基于哈希表实现的索引结构，用于快速定位数据的存储位置。在Hash索引中，索引键通过哈希算法计算得到一个哈希值，该哈希值指向存储数据的具体位置，从而实现快速的查找和定位。

MyISAM和InnoDB都支持哈希索引吗？

都不支持，只有Memory引擎支持。
并且不支持区间查找，所以索引主要依靠B+tree。

where条件的顺序会影响使用索引吗？

如果都是and，则不影响。
MySQL有个东西叫做优化器，它会根据查询的字段，筛选的字段，索引情况自动调整。
order by调整顺序会影响结果。

where条件左边的or遇见右边的and，谁会先执行？

or的优先级比and更小，会先执行右边的and，再执行左边的or，所以要控制好。避免索引失效。

那些查询适合创建索引？

• 需要唯一性约束兜底的字段。
• 经常被查询或者作为where条件的字段，=、>、<、<=、>=、in、between、like 右百分号
• 经常group by或者order by的字段。
• delete或update被作为where条件的字段。
• distinct的字段。
• join on的连接字段需要加索引，但是需要类型一致，因为MySQL内部有用函数做隐式转换，用了函数就不适用索引。
• 区分度（不重复度）高的字段。
• 把搜索最频繁的列，放在联合索引的左侧，（受联合索引的最左原则影响）。

那些查询不适合创建索引？

• 数据量小，一个表，例如配置表，总类别表，可能最多几十条记录，创建不创建区别不大。
• 写多读少，数据的写操作对索引字段的开销比没有索引要大，而且读操作还少。
• 区分度低的字段，例如性别状态等，这会导致线性查找，能提升搜索效率，但是不明显，可加可不加。
• sql语句包含<>、!=、not in、is not null，无法使用索引，所以专门用作排除性查找的，不建议创建索引。

哪些情况下索引会失效？

• 使用not in、is not null、<>、!=、这种排除法时会导致索引失效，覆盖索引除外。
• 最左匹配原则，左边的字段缺少时会出现，覆盖索引除外。
• 最左匹配原则，左边的字段有区间查询，导致右边的字段无法使用索引。
• like左边或两边加百分号。
• 类型的隐式转换，如varchar的字段，使用where varchar_field = 123，包括join表，用on连接的字段。
• where条件有函数，或表达式。
• where语句包含or，or中存在非索引列。
• 大数据量对二级索引字段排序，如果select * 或者其它字段，这个过程涉及回表，可能无法使用索引，因为数据量大，走索引的每条数据都需要回表，代价会很大。
• order by字段，如果排序与索引顺序不一致，则可能导致索引失效，如果order by的每个字段，都按照索引的顺序，或者反顺序，则仍旧会走索引。

如果一个表中的每个字段都加了单列索引，且每个字段都查询条件的操作，哪个索引会被优先使用?

这是个开放性的问题，不能一概而论，不过可根据SQL语句的执行顺序判断，先执行sql语句的某个部分，如果这个部分有索引，那就使用这个索引，其它索引在此查询过程中用不上。
可使用explain参考。

B+tree和Hash索引的适用场景？

B+tree：等值查找，区间查找，批量等值查找，order by。
hash：等值查找。

为什么大数据情况下，使用select *，并对某个或某些二级索引列order by，会导致索引失效？

因为数据量大又使用select * ，会导致二级索引的排序需要回表，这个太耗资源。所以MySQL优化器选择了全表查。
亲测加一个limit数据量不超过49000，就能解决这个问题，explain type由all变成了index。

为什么唯一索引比普通索引略快 ？

唯一索引找到数据就不往下找了。

一次查询，表中多个索引都可用，MySQL优化器只能选一个吗？

多数情况下是，但是还有index_merge（explain type的值）的情况。使用了多个单列索引来执行查询。当在查询条件中存在多个列，且每个列都有单独的索引时。

什么是自适应哈希？

InnoDB不支持hash索引，但是提供了一个自适应的哈希索引，属于MySQL内部的优化机制。
某些数据被经常访问，满足等值查询的时候，就会将这个数据页的地址存放到哈希表中，下次查询的时候直接用。
MySQL5.7和8默认都是开启的状态，可使用select @@innodb_adaptive_hash_index来查看。

为什么很多人都在讲索引就不得不提磁盘io?

因为最耗时的环节就在磁盘io上，索引就是为了减少磁盘io的次数。

字段被删除时，索引的情况？

单列索引，字段被删除时，同步删除索引。
联合索引，部分字段被删除时，索引删除，自动根据剩余字段重建索引。索引全部字段被删除时，索引删除。

什么是降序索引？

这是mysql8的新特性，创建索引时指定索引的排序方式为降序，CREATE INDEX idx_column_name ON table_name (column_name DESC);
对创建联合索引的列，进行不同的排序顺序时，使用降序排序，会提高性能。
前提是，需要保证创建索引的升降序与排序的升降序一致。

为什么子查询比join慢？

子查询的过程创建了临时表，临时表的创建和销毁会占用时间。
而join的过程，会产生一个结果集，这个结果集不是临时表。

count(*)、count(1)、count(字段)，count(id)怎么选，哪个性能高？

如果想统计全部数据，不推荐用count(字段)，因为遇到null值不会+1，如果统计某字段数量，用count(字段)。
性能方面，同一个引擎下差不多。
不同引擎下，MyISAM比InnoDB引擎性能高，MyISAM中，表的元数据就存了count值，通过表级锁自动维护一致性。时间复杂度为O(1)。InnoDB采用行级锁和MVCC机制（事务相关分为当前读和快照读之分，不同的事务隔离级别和读出的数据不一致，会造成不准确的情况），无法采用MyISAM的方案，所以要全表扫描，时间复杂度O(n)。

sql执行顺序where优先于limit，在不加索引的列使用where，为什么大数据情况下加limit 1能显著提升性能？

sql的执行顺序是：form->join on->where->group by->having->select->order by->limit，确实where在limit前面。
但是MySQL Server有个查询优化器的东西，大概是预加载了limit，在where环节找到数据后立马停止。
本问题没有找到官方说明，只是个人推断。
推断过程如下：
随便找了一个省市区县镇的四级联动的表，共46462条数据，name字段为中文，无索引，所以该字段where是全表扫描。
把全表数据复制了32遍，共1486784条数据，执行select * from address where name like '%北京%'，不加limit 1用时11.62秒，加上limit 1用时0.04秒，性能提升几百倍，如果limit在where全部取筛选数据后在截取，指望着限制条目，性能就提升几百倍，几乎不可能。因为最耗时过程是where环节的全表扫描，所以才猜测是预加载了limit，在where查询数据数量符合limit值时就直接中断。