1.索引概述

索引是帮助mysql高效获取数据的排好序的数据结构。

通俗点的说，数据库索引好比是一本书的目录，可以直接根据页码找到对应的内容。

没有建立索引

如果不走索引进行查找的话，默认是全表扫描。从表的第一行记录开始逐行对比，把每一行的col字段的值和88进行对比，也就是说有多少数据就进行多少次查询，然后找到相应的数据就把它们放到结果集中，直到全文扫描完毕。找到col=3 一共访问了7次磁盘。

建立索引

假设现在用一棵平衡二叉树数据结构存储我们的索引列。

该二叉树的存储结构（Key - Value）：Key 就是索引字段的数据，Value 就是索引所在行的磁盘文件地址。当最后找到3 的时候，就可以把它的 Value 对应的磁盘文件地址拿出来，然后就直接去磁盘上去找这一行的数据，这时候的速度就会比全表扫描要快很多。

结论：将索引存储在树结构中可以提高查询效率。

索引的优缺点

一般数据库的索引是存储在磁盘文件中的，查询数据时，需要先把磁盘中的数据加载到内存中。查找一次就会发生一次磁盘IO，然而磁盘IO操作非常耗时，想要提高查询效率就要尽量减少磁盘IO次数。

优点： 1.能够加快数据库检索效率，降低数据库的IO成本。 2.通过索引列对数据进行排序，降低数据的排序成本降低了CPU的消耗。

缺点：

索引会占据磁盘空间。索引一般是存储在磁盘上的文件中的，以空间换时间的设计。
索引虽然会提高查询效率，但是会降低更新表的效率。比如每次对表进行增删改查操作，MySQL不仅要保存数据，还有保存或者更新对应的索引文件。
维护索引需要消耗数据库资源。

2.索引常用的数据结构

Hash 表
二叉查找树
平衡二叉树
B-tree
B+tree

再讲b+之前，先了解一下常见的索引数据结构

实际上mysql默认存储引擎innodb B+tree（B+树）存储索引数据。介绍 B+ 树索引，就不得不提二叉查找树，平衡二叉树和 B 树这三种数据结构。B+ 树就是从他们演化来的。

2.1 Hash表

Hash表，也叫散列表，根据关键字而直接进行访问的数据结构。也就是说，散列表建立了关键字和存储地址之间的一种直接映射关系。

散列函数：一个把查找表中的关键字映射成该关键字对应的地址的函数，记为Hash (key)=Addr (这里的地址可以是数组下标、索引或内存地址等)。

构造散列函数常见的方法有：直接定址法，除留余数法，数字分析法，平方取中法。

处理冲突的方法:开放定址法,拉链法

例如：关键字序列为｛19, 14, 23,01,68, 20, 84, 27, 55, 11,10,79｝,散列函数H(key)=key%13,用拉链法处理冲突，建立的哈希表如下图：

可以看出Hash表在等值查询时效率很高，时间复杂度为O(1)；但是不支持范围快速查找，范围查找时还是只能通过扫描全表方式。

优点：一对一的查找效率很高。缺点：不支持范围查找。

参考链接：blog.csdn.net/qq_35190492…

2.2 二叉查找树

二叉查找树（BST，Binary Search Tree），也称二叉搜索树或二叉查找树。

一棵二叉树，可以为空；如果不为空，满足以下性质：

1.非空左子树的所有键值小于其根结点的键值。

2.非空右子树的所有键值大于其根结点的键值。

3.左、右子树都是二叉搜索树。

举例：

插入顺序：13，6，2，11，17，22

插入顺序：2，6，11，13，17，22

二叉查找树的时间复杂度是o(logn)，但是二叉查找树在经过多次插入与删除后，树没有保持平衡，有可能导致不同的结构。极端情况下会退化成线性结构，时间复杂度退化为O(N)，检索性能急剧下降。

特点

查找、删除的效率的时间复杂度均为O(logN)，快速查找，快速插入。
二叉查找树在经过多次插入与删除后，树没有保持平衡，有可能导致不同的结构。极端情况下会退化成线性结构，时间复杂度退化为O(N)，检索性能急剧下降。

优点：快速查找，快速插入。缺点：容易失去平衡，极端情况会全表扫描。

2.3 平衡二叉树

平衡二叉树解决了二叉查找树在特殊情况不平衡的问题，任意左右子树之间的高度差不大于1，平衡二叉树的查询时间复杂度是 O (log (N))。

对于一组数据，以插入顺序为2，6，11，13，17，22 为例

构建二叉查找树。

构建平衡二叉树，了解如何调整保持树的平衡。

1.插入结点2，平衡

2.插入结点6，平衡

3.插入结点11，发现结点2失去平衡，左子树高度为0，右子树高度为2，平衡因子-2。11的插入导致树不平衡了，11在2的右子树的右子树上，所以执行RR旋转。将6"左旋转"接替2的位置，6的左子树接2，右子树保持不变。

4.插入结点13，13>11,插入到11的右子树，平衡

5.插入结点17，17>13，插入到13的右子树，发现结点11失去平衡。17在11的右子树的右子树上，同理执行RR旋转。 13左旋转接替11的位置，13的左子树接11，右子树不变。

6.插入结点22，22>17, 插入到17的右子树，发现结点6失去平衡。22在6的右子树的右子树上，同理执行RR旋转。 13左旋转接替根节点6的位置，13的左子树接6，右子树不变，6的左子树不变，右子树接13的左子树。

所以同样一组数据，平衡二叉树通过左旋右旋方式，维持二叉树的平衡，能够避免出现线性结构。

优点：快速查找。

缺点：

时间复杂度和树高相关。当数据量很大时，树的层级太高，查询次数越多，磁盘IO次数增多，性能越差。
不支持快速范围查询，范围查询时，需要从根节点进行多次中序遍历。

参考链接：juejin.cn/spost/73120…

2.4 B树

与平衡二叉树不同，B树每个结点可以存放多个关键字，在相同数据时。可以有限降低树的高度。

B树是一种多路平衡查找树。

B树中所有结点的孩子个数最大值称为B树的阶。

一棵m阶B树是一棵平衡的m路查找树，特性如下：

1.树中每个结点至多有m棵子树。

2.除根结点外的所有非叶子结点，至少有M/2棵子树，至少含有M/2-1 个关键字。每个非叶子结点由n个key与n+1个指针组成，其中M/2-1 <= n <= m-1。

3.若根结点不是叶子结点，则至少有两棵子树。

4.所有的叶子结点都在同一层,所有索引元素不重复。

5.每个结点中的关键字都按照从小到大的顺序排列，每个关键字的左子树中的所有关键字都小于它，而右子树中的所有关键字都大于它。

以3阶BTree为例，key的数量：公式推导[ceil(m/2)-1]<=n<=m-1。所以1<=n<=2。当n>2时，中间节点分裂到父节点，两边节点分裂。

指针，存储子节点地址信息。

key是键值，索引值，关键字，代表当前记录的主键。

value是 data，代表当前记录中除主键之外的数据。

参考链接：juejin.cn/post/731078…

优点：相较于二叉树，层级较少，查找效率高

缺点：1.B树的非叶子节点也会保存数据，指针和数据共同保存在同一节点中，查找数据的效率不稳定。2.插入和删除数据需要频繁变更树的结构，结构不稳定。

2.5 B+树

B+树是一种B-树的变型树。：

数据均保存在叶子节点，每次查询次数都相同，保证了搜索的稳定性，每次查找都会从父节点到叶子节点结束。
插入和删除数据操作均放在叶子节点，维护了树结构的稳定性。
叶子节点同时还维护了一条双向链表，提高范围查询的效率，进行区间访问时，由于B+树叶子节点之间用指针相连，只需要遍历所有的叶子节点即可；而B-树则需要中序遍历那样遍历。

比如： m=3

前期准备

一棵B+树的阶数 M = 3 ，且 ⌈M/2⌉ = 2（取上限） 、⌊M/2⌋ = 1（取下限） ：

2.5.1 B+树插入

在B+树中插入关键字时，需要注意以下几点：

插入的操作全部都在叶子结点上进行，且不能破坏关键字自小而大的顺序；
由于 B+树中各结点中存储的关键字的个数有明确的范围，做插入操作可能会出现结点中关键字个数超过阶数的情况，此时需要将该结点进行 “分裂”；中间结点分裂到父结点，两边结点分裂。

B+树中做插入关键字的操作，有以下 4 种情况：

1、若被插入关键字所在的结点，其含有关键字数目小于阶数 M，则直接插入；

2、若被插入关键字所在的结点，其含有关键字数目等于阶数 M，则需要将该结点分裂为两个结点，一个结点包含 ⌊M/2⌋ ，另一个结点包含 ⌈M/2⌉ 。同时，将⌈M/2⌉的关键字上移至其双亲结点。假设其双亲结点中包含的关键字个数小于 M，则插入操作完成。

3、在第 2 情况中，如果上移操作导致其双亲结点中关键字个数大于 M，则应继续分裂其双亲结点。

4、若插入的关键字比当前结点中的最大值还大，破坏了B+树中从根结点到当前结点的所有索引值，此时需要及时修正后，再做其他操作。

举例：

1.比如插入关键字 12 ，插入关键字所在的结点的 [10，15] 包含两个关键字，小于 M ，则直接插入关键字 12 。

2.插入关键字 95 ，插入关键字所在结点 [85、91、97] 包含 3 个关键字，等于阶数 M ，则将 [85、91、97] 分裂为两个结点 [85、91] 和结点 [97] , 关键字 95 插入到结点 [95、97] 中，并将关键字 91 上移至其双亲结点中，发现其双亲结点 [72、97] 中包含的关键字的个数 2 小于阶数 M ，插入操作完成。

3.在第 2 情况中，如果上移操作导致其双亲结点中关键字个数大于 M，则应继续分裂其双亲结点。

插入关键字 40 ，按照第 2 种情况将结点分裂，并将关键字 37 上移到父结点，发现父结点 [15、37、44、59] 包含的关键字的个数大于 M ，所以将结点 [15、37、44、59] 分裂为两个结点 [15、37] 和结点 [44、59] ，并将关键字 37 上移到父结点中 [37、59、97] . 父结点包含关键字个数没有超过 M ，插入结束。

4.插入关键字 100，由于其值比最大值 97 还大，插入之后，从根结点到该结点经过的所有结点中的所有值都要由 97 改为 100。改完之后再做分裂操作。

2.5.2 B+树删除

在 B+树中删除关键字时，有以下几种情况：

1、找到存储有该关键字所在的结点时，由于该结点中关键字个数大于⌈M/2⌉，做删除操作不会破坏 B+树，则可以直接删除。

删除关键字 91，包含关键字 91 的结点 [85、91、97] 中关键字的个数 3 大于 ⌈M/2⌉ = 2 ，做删除操作不会破坏 B+树的特性，直接删除。

2、当删除某结点中最大或者最小的关键字，就会涉及到更改其双亲结点一直到根结点中所有索引值的更改。

以删除整颗 B+树中最大的关键字 97 为例，查找并删除关键字 97 ，然后向上回溯，将所有关键字 97 替换为次最大的关键字 91 :

3、当删除该关键字，导致当前结点中关键字个数小于 ⌈M/2⌉，若其兄弟结点中含有多余的关键字，可以从兄弟结点中借关键字完成删除操作。

当删除某个关键字之后，结点中关键字个数小于 ⌈M/2⌉ ，则不符合 B+树的特性，则需要按照 3 he 4 两种情况分别处理。以删除关键字 51 为例，由于其兄弟结点 [21、37、44] 中含有 3 个关键字，所以可以选择借一个关键字 44，同时将双亲结点中的索引值 44 修改 37 ，删除过程如下图所示：

4、第 3 种情况中，如果其兄弟结点没有多余的关键字，则需要同其兄弟结点进行合并。

为了说明这种情况，我们在第 3 种情况最终得到的 B+树之上进行删除操作。第 3 种情况删除关键字 51 之后得到如下所示 B+树：

我们以删除上面这个 B+树中的关键字 59 说明第 4 种情况，首先查找到关键 59 所在结点 [44、59] ，发现该结点的兄弟结点 [21、37] 包含的关键字的个数 2 等于 ⌈M/2⌉，所以删除关键字 59 ，并将结点 [21、37] 和 [44] 进行合并 [21、37、44] ，然后向上回溯，将所有关键字 59 替换为次最大的关键字 44 :

5、当进行合并时，可能会产生因合并使其双亲结点破坏 B+树的结构，需要依照以上规律处理其双亲结点。

删除关键字 63，当删除关键字后，该结点中只剩关键字 72，且其兄弟结点 [85、91] 中只有 2 个关键字，所以将 [72] 和 [85、91] 进行合并，向上回溯，删除结点 [72、91] 当中的关键字 72 ，此时结点中只有关键 91 ，不满足 B+树中结点关键字个数要求，但其兄弟结点 [15、44、59] 中包含的 3 个关键字，所以从其兄弟结点当中借一个关键字 59 , 再对其兄弟结点的父结点中的关键字进行调整，将关键字 59 替换为 44 .

原文链接：zhuanlan.zhihu.com/p/149287061

3.MySQL中的B+树

4.索引的分类

主键索引

一种特殊的唯一索引，不允许有空值。(主键约束 = 唯一索引 + 非空值)

唯一索引

索引列中的值必须是唯一的，但是允许为空值。

普通索引

MySQL 中的加索引类型，没啥限制。允许空值和重复值，纯粹为了提高查询效率而存在。

单列索引

一个索引只包含单个列，一个表可以有多个单值索引。

组合索引

一个索引包含多个列。注意，使用它的时候需要遵守最左匹配原则。多个列作为查询条件时，组合索引在工作中很常用。

全文索引

只能在文本内容，也就是 TEXT、CHAR、VARCHAR 数据类型的列上建全文索引。有人说创建单列索引不就完了吗？考虑一种情况：当这列的内容很长时，用 like 查询就会很慢，这是就适合建全文索引。仅在MyISam引擎中才会用到。

前缀索引

还是只能作用于文本内容，也就是 TEXT、CHAR、VARCHAR 数据类型的列上建前缀索引，它可以指定索引列的长度，它是这样写的：

// 在 x_test 的 x_name 列上创建一个长度为 4 的前缀索引
alter table x_test add index(x_name(4));

5.索引语法

创建两张表

CREATE TABLE `city` (
`city_id` INT(11) NOT NULL AUTO_INCREMENT,
`city_name` VARCHAR(50) NOT NULL,
`country_id` INT(11) NOT NULL,
PRIMARY KEY (`city_id`)
) ENGINE = INNODB DEFAULT CHARSET=UTF8;

INSERT INTO `city` (`city_id`, `city_name`,`country_id`) VALUES(1, '西安', 1);
INSERT INTO `city` (`city_id`, `city_name`,`country_id`) VALUES(2, '纽约', 2);
INSERT INTO `city` (`city_id`, `city_name`,`country_id`) VALUES(3, '北京', 1);
INSERT INTO `city` (`city_id`, `city_name`,`country_id`) VALUES(4, '上海', 1);

CREATE TABLE `country` (
`country_id` INT(11) NOT NULL AUTO_INCREMENT,
`country_name` VARCHAR(100) NOT NULL,
PRIMARY KEY (`country_id`)
) ENGINE = INNODB DEFAULT CHARSET=UTF8;

INSERT INTO `country` (`country_id`, `country_name`) VALUES(1, 'China');
INSERT INTO `country` (`country_id`, `country_name`) VALUES(2, 'America');
INSERT INTO `country` (`country_id`, `country_name`) VALUES(3, 'Japan');
INSERT INTO `country` (`country_id`, `country_name`) VALUES(4, 'UK');

5.1 创建索引

语法：

CREATE [UNIQUE] INDEX index_name
ON table_name (column_name);

示例：为city表中的city_name字段创建索引

create index idx_city_name on city(city_name);

5.2 查看索引

语法：

show index from table_name;

示例：查看city表中的索引信息

5.3 删除索引

语法：

drop index index_name on table_name;

示例删除city表上的索引idx_city_name,可以操作如下：

5.4 ALERT命令

修改表结构的方式创建索引

1. alter table tb_name add primary key(column_list);
该语句添加一个主键，这意味着索引值必须是唯一的，且不能为Null

2. alter table tb_name add unique index_name(column_list);
这条语句创建索引的值必须是唯一的(除了Null外，Null可能会出现多次)

3.alter table tb_name add fulltext index_name(column_list);
添加普通索引，索引值可以出现多次

4.alter table tb_name  add fulltext index_name(column_list);
该语句指定了索引为fulltext 用于全文索引。

5.组合索引
alter table t add index index_name(a,b,c);

5.聚簇索引和非聚簇索引

在MySQL中，B+树索引按照存储方式的不同分为聚簇索引和非聚簇索引。

1.聚簇索引

以innodb作为存储引擎的表，表中的数据都会有一个主键，即使不创建主键，系统也会帮你创建一个隐式的主键。因为innodb是把数据存放在B+树中，而B+树的键值就是主键，在B+树的叶子节点中存储了行数据，可以直接在聚集索引中查找到想要的数据。这种以主键作为B+树索引的键值而构建的B+树索引，称之为聚簇索引。

聚集索引的生成规则：

如果表定义了PK（Primary Key，主键），那么PK就是聚集索引。
如果表没有定义PK，则第一个NOT NULL UNIQUE的列就是聚集索引。
否则InnoDB会另外创建一个隐藏的ROWID作为聚集索引。

利用聚集索引查找数据 查找id>=18并且id<40的用户数据。对应的sql语句为select * from user where id>=18 and id <40，其中id为主键。具体的查找过程如下：

（1）根节点是常驻内存的，也就是说页1已经在内存中了，此时不需要到磁盘中读取数据，直接从内存中读取即可。从内存中读取到页1，要查找这个id>=18 and id <40或者范围值，我们首先需要找到id=18的键值。从页1中我们可以找到键值18，此时我们需要根据指针p2，定位到页3。

（2）要从页3中查找数据，我们就需要拿着p2指针去磁盘中进行读取页3。从磁盘中读取页3后将页3放入内存中，然后进行查找，我们可以找到键值18，然后再拿到页3中的指针p1，定位到页8。

（3）同样的页8页不在内存中，我们需要再去磁盘中将页8读取到内存中。将页8读取到内存中后。因为页中的数据是链表进行连接的，而且键值是按照顺序存放的，此时可以根据二分查找法定位到键值18。

（4）因为是范围查找，而且此时所有的数据又都存在叶子节点，并且是有序排列的，那么我们就可以对页8中的键值依次进行遍历查找并匹配满足条件的数据。我们可以一直找到键值为22的数据，然后页8中就没有数据了，此时我们需要拿着页8中的p指针去读取页9中的数据。因为页9不在内存中，就又会加载页9到内存中，并通过和页8中一样的方式进行数据的查找，直到将页12加载到内存中，发现41大于40，此时不满足条件。那么查找到此终止。

2.非聚簇索引

（1）以主键以外的列值作为键值构建的B+树索引，我们称之为非聚集索引。

（2）非聚集索引与聚集索引的区别在于非聚集索引的叶子节点不存储表中的数据，而是存储该列对应的主键，想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，我们称为回表。

（3）如果使用了覆盖索引，则不需要回表，直接通过辅助索引就可以查找到想要的数据。覆盖索引就是指select查询的数据只需要在索引中就能取得，而不必读取数据行，换句话说就是，查询列要被所建的索引覆盖。

利用非聚集索引查找数据

在叶子节点中，不在存储所有的数据了，存储的是键值和主键。

对于叶子节点中的x-y，比如1-1。左边的1表示的是索引的键值，右边的1表示的是主键值。假设这是以age字段建立的索引树，如果我们要找到age为33的用户信息，对应的sql语句为select * from user where age=33。

我们最终会找到主键值47，找到主键后我们需要再到聚集索引中查找具体对应的数据信息，此时又回到了聚集索引的查找流程。

下面看下具体的查找流程图：

根据在辅助索引树中获取的主键id，到主键索引树检索数据的过程称为回表查询。

理解的关系：Mysql索引实现—InnoDB引擎——两种索引存储方式——存储结构是B+树

参考链接：developer.aliyun.com/article/831… blog.csdn.net/Pireley/art… zhuanlan.zhihu.com/p/149287061

参考链接： blog.csdn.net/qq_35190492…

MySQL 学习笔记（一）索引概述及原理