1.为什么使用索引

顺序查询和数据使用二叉树结构再进行查询,如图：

2. 索引及其优缺点

2.1 索引概述
MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。

索引的本质：索引是数据结构。你可以简单理解为“排好序的快速查找数据结构”，满足特定查找算法。这些数据结构以某种方式指向数据， 这样就可以在这些数据结构的基础上实现 高级查找算法 。

2.2 优点
（1）类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本 ，这也是创建索引最主要的原因。（2）通过创建唯一索引，可以保证数据库表中每一行数据的唯一性 。（3）在实现数据的参考完整性方面，可以加速表和表之间的连接 。换句话说，对于有依赖关系的子表和父表联合查询时，可以提高查询速度。（4）在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间 ，降低了CPU的消耗。

2.3 缺点
增加索引也有许多不利的方面，主要表现在如下几个方面： （1）创建索引和维护索引要耗费时间 ，并且随着数据量的增加，所耗费的时间也会增加。 （2）索引需要占磁盘空间 ，除了数据表占数据空间之外，每一个索引还要占一定的物理空间， 存储在磁盘上 ，如果有大量的索引，索引文件就可能比数据文件更快达到最大文件尺寸。 （3）虽然索引大大提高了查询速度，同时却会降低更新表的速度 。当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。

因此，选择使用索引时，需要综合考虑索引的优点和缺点。

3. InnoDB中索引

1. 在一个页中的查找 或者在很多页中查找
在没有索引的情况下，不论是根据主键列或者其他列的值进行查找，由于我们并不能快速的定位到记录所在的页，所以只能从第一个页 沿着双向链表 一直往下找，在每一个页中根据我们上面的查找方式去查找指定的记录。因为要遍历所有的数据页，所以这种方式显然是超级耗时 的。如果一个表有一亿条记录呢？此时索引应运而生。

2. 设计索引

mysql> CREATE TABLE index_demo(
-> c1 INT,
-> c2 INT,
-> c3 CHAR(1),
-> PRIMARY KEY(c1)
-> ) ROW_FORMAT = Compact;

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

• record_type ： 记录头信息的一项属性，表示记录的类型， 0 表示普通记录、1表示目录项记录、 2 表示最小记录、 3 表示最大记录。

• next_record ： 记录头信息的一项属性，表示下一条地址相对于本条记录的地址偏移量，我们用箭头来表明下一条记录是谁。

• 各个列的值 ：这里只记录在 index_demo 表中的三个列，分别是 c1 、 c2 和 c3 。

• 其他信息 ：除了上述3种信息以外的所有信息，包括其他隐藏列的值以及记录的额外信息。

这里再次强调目录项记录和普通的用户记录的不同点：

• 目录项记录的record_type值是1，而普通用户记录的record_type 值是0。
• 目录项记录只有主键值和页的编号两个列，而普通的用户记录的列是用户自己定义的，可能包含很多列 ，另外还有InnoDB自己添加的隐藏列。
• 了解：记录头信息里还有一个叫 min_rec_mask 的属性，只有在存储目录项记录的页中的主键值最小的目录项记录的min_rec_mask 值为 1 ，其他别的记录的 min_rec_mask 值都是 0 。
  相同点：
• 两者用的是一样的数据页，都会为主键值生成 Page Directory （页目录），从而在按照主键值进行查找时可以使用 二分法来加快查询速度。

3.索引设计方案
我们在根据某个搜索条件查找一些记录时为什么要遍历所有的数据页呢？因为各个页中的记录并没有规律，我们并不知道我们的搜索条件匹配哪些页中的记录，所以不得不依次遍历所有的数据页。所以如果我们想快速的定位到需要查找的记录在哪些数据页中该咋办？我们可以为快速定位记录所在的数据页而 建立一个目录 ，建这个目录必须完成下边这些事：

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

一个B+树的节点其实可以分成好多层，规定最下边的那层，也就是存放我们用户记录的那层为第 0 层，之后依次往上加。之前我们做了一个非常极端的假设：存放用户记录的页最多存放3条记录，存放目录项记录的页最多存放4条记录 。其实真实环境中一个页存放的记录数量是非常大的，假设所有存放用户记录的叶子节点代表的数据页可以存放100条用户记录 ，所有存放目录项记录的内节点代表的数据页可以存
放1000条目录项记录，那么：

• 如果B+树只有1层，也就是只有1个用于存放用户记录的节点，最多能存放 100 条记录。
• 如果B+树有2层，最多能存放 1000×100=10,0000 条记录。
• 如果B+树有3层，最多能存放 1000×1000×100=1,0000,0000 条记录。
• 如果B+树有4层，最多能存放 1000×1000×1000×100=1000,0000,0000 条记录。相当多的记录！！！

4.常见索引概念
索引按照物理实现方式，索引可以分为 2 种：聚簇（聚集）和非聚簇（非聚集）索引。我们也把非聚集索引称为二级索引或者辅助索引。

4.1. 聚簇索引
特点：

1 .使用记录主键值的大小进行记录和页的排序，这包括三个方面的含义：

• 页内的记录是按照主键的大小顺序排成一个单向链表 。
• 各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个 双向链表 。
• 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表。

2 .B+树的叶子节点存储的是完整的用户记录。
所谓完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）。

优点：

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

缺点：

• 插入速度严重依赖于插入顺序 ，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键
• 更新主键的代价很高 ，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新
• 二级索引访问需要两次索引查找 ，第一次找到主键值，第二次根据主键值找到行数据

4.2 二级索引（辅助索引、非聚簇索引）

回表 我们根据这个以c2列大小排序的B+树只能确定我们要查找记录的主键值，所以如果我们想根据c2列的值查找到完整的用户记录的话，仍然需要到 聚簇索引 中再查一遍，这个过程称为 回表 。也就是根据c2列的值查询一条完整的用户记录需要使用到 2 棵B+树！

问题：为什么我们还需要一次 回表 操作呢？直接把完整的用户记录放到叶子节点不OK吗？

4.3. 联合索引
我们也可以同时以多个列的大小作为排序规则，也就是同时为多个列建立索引，比方说我们想让B+树按照 c2和c3列 的大小进行排序，这个包含两层含义：

• 先把各个记录和页按照c2列进行排序。
• 在记录的c2列相同的情况下，采用c3列进行排序

注意一点，以c2和c3列的大小为排序规则建立的B+树称为 联合索引 ，本质上也是一个二级索引。它的意思与分别为c2和c3列分别建立索引的表述是不同的，不同点如下：

• 建立 联合索引 只会建立如上图一样的1棵B+树。
• 为c2和c3列分别建立索引会分别以c2和c3列的大小为排序规则建立2棵B+树。

4.4 InnoDB的B+树索引的注意事项

1. 根页面位置万年不动
2. 内节点中目录项记录的唯一性
3. 一个页面最少存储2条记录

4.MyISAM中的索引方案

Innodb和MyISAM默认的索引是Btree索引；而Memory默认的索引是Hash索引。MyISAM引擎使用B+Tree作为索引结构，叶子节点的data域存放的是数据记录的地址 。

如果我们在Col2上建立一个二级索引，则此索引的结构如下图所示：

2.MyISAM 与 InnoDB对比
MyISAM的索引方式都是“非聚簇”的，与InnoDB包含1个聚簇索引是不同的。小结两种引擎中索引的区别：

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

5.MySQL数据结构选择的合理性

5.1全表遍历

全部加载，进行遍历，略

5.2Hash结构

Hash索引适用存储引擎如表所示：

Hash结构效率高，那为什么索引结构要设计成树型呢？

原因1: Hash索引仅能满足(=)(<>）和IN查询。如果进行范围查询，哈希型的索引，时间复杂度会退化为O(n);而树型的“有序"特性，依然能够保持o(log2N)的高效率。
原因2: Hash索引还有一个缺陷，数据的存储是没有顺序的，在ORDER BY的情况下，使用 Hash索引还需要对数据重新排序。
原因3∶对于联含索引的情况，Hash值是将联合索引键合并后一起来计算的，无法对单独的一个键或者几个索引键进行查询。
原因4:∶对于等值查询来说，通常Hash索引的效率更高，不过也存在一种情况，就是索引列的重复值如果很多，效率就会降低。这是因为遇到Hash冲突时，需要遍历桶中的行指针来进行比较，找到查询的关键字，非常耗时。所以，Hash索引通常不会用到重复值多的列上，比如列为性别、年龄的情况等。

5.3 二叉搜索树

如果我们利用二叉树作为索引结构，那么磁盘的IO次数和索引树的高度是相关的
二叉搜索树的特点

• 一个节点只能有两个子节点，也就是一个节点度不能超过2
• 左子节点<本节点;右子节点>=本节点，比我大的向右，比我小的向左

理想情况：

非理想情况：

5.4 AVL树

为了解决上面二叉查找树退化成链表的问题，人们提出了平衡二叉搜索树(Balanced Binary Tree)，又称为AVL树(有别于AVL算法)，它在二叉搜索树的基础上增加了约束，具有以下性质:
它是一棵空树或它的左右两个子树的高度差的绝对值不超过1，并且左右两个子树都是一棵平衡二叉树。
这里说一下，常见的平衡二叉树有很多种，包括了平衡二叉搜索树、红黑树、数堆、伸展树。平衡二叉搜索树是最早提出来的自平衡二叉搜索树，当我们提到平衡二叉树时一般指的就是平衡二叉搜索树。事实上，第一棵树就属于平衡二叉搜索树，搜索时间复杂度就是0( log2n)。
数据查询的时间主要依赖于磁盘V/o的次数，如果我们采用二叉树的形式，即使通过平衡二叉搜索树进行了改进，树的深度也是O(log2n)，当n比较大时，深度也是比较高的，比如下图的情况:

5.5 B Tree

B 树的结构如下图所示：

一个 M 阶的 B 树（M>2）有以下的特性：

1. 根节点的儿子数的范围是 [2,M]。
2. 每个中间节点包含 k-1 个关键字和 k 个孩子，孩子的数量 = 关键字的数量 +1，k 的取值范围为[ceil(M/2), M]。
3. 叶子节点包括 k-1 个关键字（叶子节点没有孩子），k 的取值范围为 [ceil(M/2), M]。
4. 假设中间节点节点的关键字为：Key[1], Key[2], …, Key[k-1]，且关键字按照升序排序，即 Key[i] <Key[i+1]。此时 k-1 个关键字相当于划分了 k 个范围，也就是对应着 k 个指针，即为：P[1], P[2], …,P[k]，其中 P[1] 指向关键字小于 Key[1] 的子树，P[i] 指向关键字属于 (Key[i-1], Key[i]) 的子树，P[k]指向关键字大于 Key[k-1] 的子树。
5. 所有叶子节点位于同一层。

上面那张图所表示的 B 树就是一棵 3 阶的 B 树。我们可以看下磁盘块 2，里面的关键字为（8，12），它有 3 个孩子 (3，5)，(9，10) 和 (13，15)，你能看到 (3，5) 小于 8，(9，10) 在 8 和 12 之间，而 (13，15)大于 12，刚好符合刚才我们给出的特征。

然后我们来看下如何用 B 树进行查找。假设我们想要查找的关键字是 9 ，那么步骤可以分为以下几步：

1. 我们与根节点的关键字 (17，35）进行比较，9 小于 17 那么得到指针 P1；
2. 按照指针 P1 找到磁盘块 2，关键字为（8，12），因为 9 在 8 和 12 之间，所以我们得到指针 P2；
3. 按照指针 P2 找到磁盘块 6，关键字为（9，10），然后我们找到了关键字 9。

你能看出来在 B 树的搜索过程中，我们比较的次数并不少，但如果把数据读取出来然后在内存中进行比较，这个时间就是可以忽略不计的。而读取磁盘块本身需要进行 I/O 操作，消耗的时间比在内存中进行比较所需要的时间要多，是数据查找用时的重要因素。 B 树相比于平衡二叉树来说磁盘 I/O 操作要少 ，在数据查询中比平衡二叉树效率要高。所以只要树的高度足够低，IO次数足够少，就可以提高查询性能 。

5.6 B+Tree

B+ 树和 B 树的差异：

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

思考题：为了减少IO，索引树会一次性加载吗？

1、数据库索引是存储在磁盘上的，如果数据量很大，必然导致索引的大小也会很大，超过几个G.
2、当我们利用索引查询时候，是不可能将全部几个G的索引都加载进内存的，我们能做的只能是:逐一加载每一个磁盘页，因为磁盘页对应着索引树的节点。

思考题：B+树的存储能力如何？为何说一般查找行记录，最多只需1~3次磁盘IO

InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT(占用4个字节)或BIGINT(占用8个字节)，指针类型也一般为4或8个字节，
也就是说一个页（B+Tree中的一个节点）中大概存储16KB/(8B+8B)=1K个键值（因为是估值，为方便计算，这里的K取值为10^3。
也就是说一个深度为3的B+Tree索引可以维护10^3*10^3 *10^3= 10亿条记录。
(这里假定一个数据页也存储10^3条行记录数据了)实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree 的高度一般都在2~4层。
MysQL的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘1/o操作。

思考题：为什么说B+树比B树更适合实际应用中操作系统的文件索引和数据库索引？

1、B+树的磁盘读写代价更低
B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B树更小。如果把所有同一内部结点的关键字存放在同一盘块中，
那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说Io读写次数也就降低了。
2、B+树的查询效率更加稳定
由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。
所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

思考题：Hash 索引与 B+ 树索引的区别

1、Hash索引【不能进行范围查询】，而B+树可以。这是因为Hash索引指向的数据是无序的，而B+树的叶子节点是个有序的链表。
2、Hash索引【不支持联合索引的最左侧原则】(即联合索引的部分索引无法使用)，而B+树可以。对于联合索引来说，
Hash索引在计算Hash值的时候是将索引键合并后再一起计算Hash值，所以不会针对每个索引单独计算Hash值。
因此如果用到联合索引的一个或者几个索引时，联合索引无法被利用。
3、Hash索引【不支持ORDER BY排序】，因为Hash索引指向的数据是无序的，因此无法起到排序优化的作用，
而B+树索引数据是有序的，可以起到对该字段ORDER BY排序优化的作用。
同理，我们也无法用Hash索引进行【模糊查询】，而B+树使用LIKE进行模糊查询的时候，LIKE后面后模糊查询（比如%结尾）的话就可以起到优化作用。
4、【InnoDB 不支持哈希索引】

思考题：Hash 索引与 B+ 树索引是在建索引的时候手动指定的吗？

你能看到，针对 InnoDB和MylSAM存储引擎，都会默认采用B+树索引，无法使用Hash索引。InnoDB提供的自适应Hash是不需要手动指定的。如果是Memory/Heap和NDB存储引擎，是可以进行选择Hash索引的。