mysql索引原理B+树 | 聚簇索引 | 覆盖索引(360面试题)

Mysql为什么要有自增主键？	(1)（主键递增,提高插入性能,避免page分裂,减少表碎片）主键递增，数据行写入可以提高插入性能，可以避免page分裂，减少表碎片提升空间和内存的使用。 (2)（减少空间,提高效率）主键要选择较短的数据类型，Innodb引擎普通索引都会保存主键的值，较短的数据类型可以有效的减少索引的磁盘空间，提高索引的缓存效率。 (3)（无主键的表删除会让备库卡住）无主键的表删除，在row模式的主从架构，会导致备库卡住。
数据库添加索引为什么能加快查询速度？	---定位特定值的行数，减少遍历匹配的行数 对某一字段增加索引，查询时就会先去索引表中一次定位到特定值的行数，大大减少遍历匹配的行数，所以能明显增加查询的速度。
聚簇索引&非聚簇索引的优缺点？	同：都是基于B+树 异：(1)聚集索引的顺序就是数据的物理存储顺序，而非聚集索引的顺序和数据物理排列无关。(最本质区别) 因为数据在物理存放时只能有一种排列方式，所以一个表只能有一个聚集索引。 (2)聚集索引的叶节点就是数据节点，而非聚集索引的叶节点仍然是索引节点，只不过其包含一个指向对应数据块的指针。 因为聚集索引会降低insert和update操作的性能，所以是否使用聚集索引要全面衡量。
覆盖索引	InnoDB支持的索引：主键索引(primary)，唯一索引(unique)，普通索引(index)，联合索引。 总体划分为两类，主键索引也被称为聚簇索引(clustered index)，其余都称呼为非主键索引也被称为二级索引(secondary index)， 两者区别在与叶子节点中，主键索引存储了整行数据，而非主键索引中存储的值为主键id 如表user(id,name,age),select age from user where name='张三'; a.在name的普通索引树上找到name='张三'的节点的id; b.再从id的主键索引树上找到id的节点对应的获取所有数据,从数据中获取字段命为age的值返回   主键索引(只看叶子节点) id:01 id:02 id:03 id:04 name: 张三 age:15 name:李四 age:17 name:王五 age:12  name:赵六 age:18   普通索引(只看叶子节点) name:张三 name:李四 name:王五 name:赵六 id:01 id:02 id:04 id:04   从普通索引B+树搜索回到主键索引B+树搜索的过程称为：回表。覆盖索引是省掉回表操作。 覆盖索引(covering index)即从非主键索引中就能查到的记录，而不需要查询主键索引中的记录，避免了回表的产生减少了树的搜索次数。 如：alter table user drop index idx_name;  alter table user add index idx_name_age(name, age); 在name,age联合索引树上找到名称为小李的节点，此时节点索引里包含信息age直接返回 检查：explain select age from user where name='张三'; 如果key为idx_name_age，Extra中有Using index， 说明成功使用了覆盖索引。 覆盖索引 {name:张三, age:15} {name:李四, age:17} {name:王五, age:12} {name:赵六, age:18} id:01 id:03 id:03 id:04

 

一、索引原理
索引的本质就是数据结构，采用B树或其变种B+树作为索引结构，以空间换时间。

索引的原理大致概括为以空间换时间，数据库在未添加索引的时候进行查询默认的是进行全量搜索，也就是进行全局扫描，有多少条数据就要进行多少次查询，然后找到相匹配的数据就把他放到结果集中，直到全表扫描完。而建立索引之后，会将建立索引的KEY值放在一个n叉树上（BTree）。因为B树的特点就是适合在磁盘等直接存储设备上组织动态查找表，每次以索引进行条件查询时，会去树上根据key值直接进行搜索，次数约为log总条数，底数为页面存储数，例如一个100万数据的表，页面存储数为100，那么有索引的查询次数为3次log1000000100，但是全量搜索为100万次搜索，这种方式类似于二分法，但是这个是n分法。

 

二、索引的优缺点和使用索引的原则
索引的优点：
①建立索引的列可以保证行的唯一性，生成唯一的rowId
②建立索引可以有效缩短数据的检索时间
③建立索引可以加快表与表之间的连接
④为用来排序或者是分组的字段添加索引可以加快分组和排序顺序

索引的缺点：
①创建索引和维护索引需要时间成本，这个成本随着数据量的增加而加大
②创建索引和维护索引需要空间成本，每一条索引都要占据数据库的物理存储空间，数据量越大，占用空间也越大（数据表占据的是数据库的数据空间）
③会降低表的增删改的效率，因为每次增删改索引需要进行动态维护，导致时间变长

使用索引的原则：
①总的来说就是数据量大的，经常进行查询操作的表要建立索
②表中字段建立索引应该遵循几个原则：
1)越小的数据类型通常更好：越小的数据类型通常在磁盘、内存中都需要更少的空间，处理起来更快。
2)简单的数据类型更好：整型数据比起字符，处理开销更小，因为字符串的比较更复杂，处理起来也更耗时。
3)尽量避免NULL：应该指定列为NOT NULL。含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。
4)唯一性高的字段加索引：对非唯一的字段，例如“性别”这种大量重复值的字段，增加索引也没有什么意义，所以索引的建立应当更多的选取唯一性更高的字段。
③join的字段加索引：表与表连接用于多表联合查询的约束条件的字段应当建立索引
④order和group的字段加索引：用于排序的字段可以添加索引，用于分组的字段应当视情况看是否需要添加索引。
⑤添加多列索引的时候，对应的多条件查询可以触发该索引的同时，索引最左侧的列的单条件查询也可以触发。
⑥如果有些表注定只会进行查询所有，也就没必要添加索引，因为查询全部只能进行全量搜索即扫描全表。

三、B树（或者B-树或者B_树）、B+树、B*树
（1）B-树(B树或者B-树或者B_树)：
平衡的多路搜索树，每个结点存储M/2到M个关键字，非叶子结点存储指向关键字范围的子结点；所有关键字在整颗树中出现，且只出现一次，非叶子结点可以命中；
===定义：
1.定义任意非叶子结点最多只有M个儿子；且M>2；
2.根结点的儿子数为[2, M]；
3.除根结点以外的非叶子结点的儿子数为[M/2, M]；
4.每个结点存放至少M/2-1（取上整）和至多M-1个关键字；（至少2个关键字）
5.非叶子结点的关键字个数=指向儿子的指针个数-1；
6.非叶子结点的关键字：K[1], K[2], …, K[M-1]；且K[i] < K[i+1]；
7.非叶子结点的指针：P[1], P[2], …, P[M]；其中P[1]指向关键字小于K[1]的
子树，P[M]指向关键字大于K[M-1]的子树，其它P[i]指向关键字属于(K[i-1], K[i])的子树；
8.所有叶子结点位于同一层；
===特色：
1.关键字集合分布在整颗树中；
2.任何一个关键字出现且只出现在一个结点中；
3.搜索有可能在非叶子结点结束；
4.其搜索性能等价于在关键字全集内做一次二分查找；
5.自动层次控制；
注意：B-树其实也就是B树，这个符号并不是加减中的减号，并不是所谓的"B减树"，只是一个连接符号而已。


（2）B+树：
在B-树基础上，为叶子结点增加链表指针，所有关键字都在叶子结点中出现，非叶子结点作为叶子结点的索引；B+树总是到叶子结点才命中；B+ 树的特点是能够保持数据稳定有序，其插入与修改拥有较稳定的对数时间复杂度。B+ 树元素自底向上插入。

1.有n棵子树的结点中含有n个关键字，每个关键字不保存数据，只用来索引，所有数据都保存在叶子节点。
2.所有的叶子结点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。
3.所有的非终端结点可以看成是索引部分，结点中仅含其子树（根结点）中的最大（或最小）关键字。
通常在B+树上有两个头指针，一个指向根结点，一个指向关键字最小的叶子结点。
4.非叶子结点的子树指针与关键字个数相同；
5.非叶子结点的子树指针P[i]，指向关键字值属于[K[i], K[i+1])的子树（B-树是开区间）；
6.为所有叶子结点增加一个链指针；
7.所有关键字都在叶子结点出现；

如图：B+树叶子结点存的是双向链表，非子结点不存数据只存储关键字跟指针。所有数据都在叶子节点,且叶子节点之间是排好序的双向链表,支持范围查询。B+树的每个叶子节点数范围[m/2,m]，一旦超过就需要分裂，一旦少于就需要合并。

PS碎碎念1：非叶子节点不存数据，那这个非叶子节点的关键字是什么？答：非叶子节点关键字存的只是划分的id(虽然这个划分id也是来自于非叶子节点的数据的id)，他左子树存的实际的id比这个非叶子id小,右子树存的实际的id比这个非叶子id大

PS碎碎念2：为什么B+树叶子节点间用双向链表？答：为了支持顺序和倒序查询，双向链表相对于单向链表虽然会浪费一倍的指针空间，但在硬盘中这点空间不算啥，用这点空间换时间是值得的。比如，PHP的数组实现也是使用的双向链表。

PS碎碎念3：索引为什么要保存在硬盘中？答：服务器存储一般分内存和硬盘，内存的大小相对于硬盘来说是很小的。内存的访问速度是纳秒级别的，非常快，而硬盘的访问速度相对内存来说就比较慢了。但不管是访问内存还是硬盘数据，操作系统都是按数据页来读取数据的，即每访问一次硬盘或内存，只读取一页大小的数据，一页的大小约等于4KB，向硬盘读取数据的操作叫做磁盘IO。因而问题得解：a.一方面是虽然内存访问速度快但容量一般都比较小，存不了多少数据; b.再一个mysql需要让数据持久化，如果服务器断电或异常重启会导致数据丢失。

PS碎碎念4：怎么让二叉搜索树支持区间查询？

答：如上图，把二叉树的叶子节点通过一个双向链表来连接，并且这个链表是有序的。因此只需要先找到区间的起始值在链表中的位置，然后再往后遍历，直到遍历到区间的终止值，即可完成区间查询。

PS碎碎念5：如何提升查询速度？

答：因为二叉搜索树保存在硬盘中，我们每访问一个节点，就对应着一次硬盘IO操作，树的高度就代表硬盘IO操作的次数。通过把树多分一些叉来想办法让树的高度变矮，来减少硬盘 IO。上图分别用二叉树和五叉树来存储16条数据，根节点一般存储在内存中，普通节点和叶子结点保存在硬盘中(硬盘只是磁盘的一种)。显然二叉树需要5次硬盘IO，而五叉树需要2次硬盘IO，后者更优。

PS碎碎念6：此时又引出第二个问题, 对于相同的数据量，是不是构建的多叉树的叉越多越好呢？答：不一定。操作系统是按数据页大小来访问硬盘的，每次IO只读取一个数据页4KB大小的数据，如果要读取的数据大于一个数据页，则会导致多次IO。因此我们要尽量让每个节点的数据大小刚好等于一个数据页4KB大小，即每访问一个节点只需一次IO

PS碎碎念7：插入和删除数据怎么办?

答：插入新的数据，即要往m叉树中插入新的节点，这可能就会导致某些节点的子节点个数大于m，也就会导致该节点大小大于一个数据页，访问该节点就需要多次IO。===>解决：分裂节点。m叉树会把该节点分裂成两个节点，然后改分裂操作又会导致其父节点的子节点数可能超过 m，我们再用同样的方法分裂节点，一直影响到根节点。                       删除节点，就会导致某些节点的子节点过少，就会浪费存储空间并降低查询效率。===>解决：合并节点。想办法让这些节点合并起来，合并的话就有可能会导致其子节点数超过m，超过的话就再用上面的分裂方法分裂子节点。

更多B+树的增删改查详见：B+树介绍

PS碎碎念8：mysql主键索引的数据都存在叶子结点，那text类型也存在里面吗？答：大文本类型(如下表, blob和text的4类)，是存在磁盘。出大文本类型的其他类型如：char，varchar,int..都是存在内存里的。

tinytext: 可变长度。	1B=8bit,最多255(2^8-1)个字符
text: 可变长度,一个blob或text列。	2B=16bit,最大长度为65535(2^16-1)个字符。
mediumtext: 可变长度,一个blob或text列。	3B=24bit,最大长度为16777215(2^24-1)个字符。
longtext:可变长度,一个blob或text列。	4B=32bit,最大长度为4294967295(2^32-1)个字符(4G)。

 

（3）B*树：
在B+树基础上，为非叶子结点也增加链表指针，将结点的最低利用率从1/2提高到2/3；
具体详见：B-树，B+树，B*树详解

InnoDB 为啥要选择B+树来存储数据 

索引的实现原理