索引

数据库索引，是数据库管理系统（DBMS）中一个排序的数据结构，以协助快速查询、更新数据库表中数据。首先数据是以文件的形式存放在磁盘上面的，每一行数据都有它的磁盘地址。如果没有索引的话，要从500万行数据里面检索一条数据，只能依次遍历这张表的全部数据，直到找到这条数据。
但是有了索引之后，只需要在索引里面去检索这条数据就行了，因为它是一种特殊的专门用来快速检索的数据结构，我们找到数据存放的磁盘地址以后，就可以拿到数据了。
就像我们从一本500页的书里面去找特定的一小节的内容，肯定不可能从第一页开始翻。那么这本书有专门的目录，它可能只有几页的内容，它是按页码来组织的，可以根据拼音或者偏旁部首来查找，只要确定内容对应的页码，就能很快地找到我们想要的内容。

1.索引的类型

在InnoDB 里面，索引类型有三种，普通索引、唯一索引（主键索引是特殊的唯一索引）、全文索引。

普通（Normal）：也叫非唯一索引，是最普通的索引，没有任何的限制。
唯一（Unique）：唯一索引要求键值不能重复。另外需要注意的是，主键索引是一种特殊的唯一索引，它还多了一个限制条件，要求键值不能为空。主键索引用primaykey创建。
全文（Fulltext）：针对比较大的数据，比如我们存放的是消息内容，有几KB的数据的这种情况，如果要解决like查询效率低的问题，可以创建全文索引。只有文本类型的字段才可以创建全文索引，比如char、varchar、text。第一个是索引的名称，第二个是索引的列，比如我们是要对id创建索引还是对name 创建索引，最后两个是索引类型和索引方法

2.InnoDB 逻辑存储结构

表空间 TableSpace

表空间可以看做是InnoDB 存储引擎逻辑结构的最高层，所有的数据都存放在表空间中。分为：系统表空间、独占表空间、通用表空间、临时表空间、Undo表空间。

段 Segment

表空间是由各个段组成的，常见的段有数据段、索引段、回滚段等，段是一个逻辑的概念。一个ibd文件（独立表空间文件）里面会由很多个段组成。创建一个索引会创建两个段，一个是索引段：leaf node segment，一个是数据段： non-leafnodesegment。索引段管理非叶子节点的数据。数据段管理叶子节点的数据。也就是说，一个表的段数，就是索引的个数乘以2。

簇 Extent

一个段（Segment）又由很多的簇（也可以叫区）组成，每个区的大小是1MB（64 个连续的页）。每一个段至少会有一个簇，一个段所管理的空间大小是无限的，可以一直扩展下去，但是扩展的最小单位就是簇。

页 Page

InnoDB页的概念（也可以称为块），每个页默认16KB。页是InnoDB存储引擎磁盘管理的最小单位，通过innodb_page_size设置。一个表空间最多拥有2^32个页，默认情况下一个页的大小为16KB，也就是说一个表空间最多存储64TB的数据。往表中插入数据时，如果一个页面已经写完，产生一个新的叶页面。如果一个簇的所有的页面都被用完，会从当前页面所在段新分配一个簇。如果数据不是连续的，往已经写满的页中插入数据，会导致叶页面分裂：

行 Row

InnoDB 存储引擎是面向行的（row-oriented），也就是说数据的存放按行进行存放。

3.索引存储模型推演

为什么MySQL的innodb存储引擎最终选择了B+树的数据结构，他的有点在哪？

1.数组

我们可以考虑用有序数组作为索引的数据结构。
有序数组的等值查询和比较查询效率非常高，但是更新数据的时候会出现一个问题，可能要挪动大量的数据（改变index），所以只适合存储静态的数据。
为了支持频繁的修改，比如插入数据，我们需要采用链表。链表的话，如果是单链表，它的查找效率还是不够高

2.二叉查找树

二叉查找树的特点左子树所有的节点都小于父节点，右子树所有的节点都大于父节点。投影到平面以后，就是一个有序的线性表。
但是她有一个问题，他的树结构是不稳定的，在最坏的情况下他会变成一张链表，我们称这种树为“斜树"，这种情况下不能达到加快检索速度目的，和顺序查找效率是没有区别的。

3.AVL树

平衡二叉树的定义：左右子树深度差绝对值不能超过1。是什么意思呢？比如左子树的深度是2，右子树的深度只能是1或者3。这个时候我们再按顺序插入1、2、3、4、5、6，一定是这样，不会变成一棵"斜树"
所以为了保持平衡，AVL树在插入和更新数据的时候执行了一系列的计算和调整的操作。也就是左旋、右旋

它应该存储三块的内容：

是索引的键值。比如我们在id上面创建了一个索引，我在用whereid=1的条件查询的时候就会找到索引里面的id的这个键值。
是数据的磁盘地址，因为索引的作用就是去查找数据的存放的地址。
因为是二叉树，它必须还要有左子节点和右子节点的引用，这样我们才能找到下一个节点。比如大于26的时候，走右边，到下一个树的节点，继续判断。
当我们用树的结构来存储索引的时候，访问一个节点就要跟磁盘之间发生一次IO。 InnoDB操作磁盘的最小的单位是一页（或者叫一个磁盘块），大小是16K(16384字节)。
那么，一个树的节点就是16K的大小。
如果我们一个节点只存一个键值+数据+引用，例如整形的字段，可能只用了十几个或者几十个字节，它远远达不到16K的容量，所以访问一个树节点，进行一次IO的时候，浪费了大量的空间。
所以如果每个节点存储的数据太少，从索引中找到我们需要的数据，就要访问更多的节点，意味着跟磁盘交互次数就会过多。
如果是机械硬盘时代，每次从磁盘读取数据需要10ms左右的寻址时间，交互次数越多，消耗的时间就越多。

4.B 树

分叉数（路数）永远比关键字数多1。比如我们画的这棵树，每个节点存储两个关键字，那么就会有三个指针指向三个子节点。
B 树为了保持平衡用了一种分裂的技术比如MaxDegree（路数）是3的时候，我们插入数据1、2、3，在插入3的时候，本来应该在第一个磁盘块，但是如果一个节点有三个关键字的时候，意味着有4个指针，子节点会变成4 路，所以这个时候必须进行分裂。把中间的数据2提上去，把1和3变成2的子节点。
从这个里面我们也能看到，在更新索引的时候会有大量的索引的结构的调整，所以解释了为什么我们不要在频繁更新的列上建索引，或者为什么不要更新主键。

5 B+树

B+树的特点

它的关键字的数量是跟路数相等的；
B+Tree的根节点和枝节点中都不会存储数据，只有叶子节点才存储数据。搜索到关键字不会直接返回，会到最后一层的叶子节点。比如我们搜索 id=28，虽然在第一层直接命中了，但是全部的数据在叶子节点上面，所以我还要继续往下搜索，一直到叶子节点。

假设索引字段是bigint 类型，长度为 8 字节。指针大小在 InnoDB 源码中设置为 6 字节，这样一共 14 字节。非叶子节点（一页）可以存储16384/14=1170个这样的单元（键值+指针），代表有1170个指针。
树深度为 2 的时候，有 1170^2 个叶子节点，可以存储的数据为 1170117016=21902400。
B+Tree的每个叶子节点增加了一个指向相邻叶子节点的指针，它的最后一个数据会指向下一个叶子节点的第一个数据，形成了一个有序链表的结构。
它是根据左闭右开的区间 [ ) 来检索数据。

B+树的搜索过程

比如我们要查找 28，在根节点就找到了键值，但是因为它不是页子节点，所以会继续往下搜寻，28是[28,66)的左闭右开的区间的临界值，所以会走中间的子节点，然后继续搜索，它又是[28,34)的左闭右开的区间的临界值，所以会走左边的子节点，最后在叶子节点上找到了需要的数据。
如果是范围查询，比如要查询从22到60的数据，当找到22之后，只需要顺着节点和指针顺序遍历就可以一次性访问到所有的数据节点，这样就极大地提高了区间查询效率（不需要返回上层父节点重复遍历查找）。

InnoDB中的B+Tree的特点：

它是BTree的变种，BTree能解决的问题，它都能解决。BTree解决的两大问题是什么？（每个节点存储更多关键字；路数更多）
扫库、扫表能力更强（如果我们要对表进行全表扫描，只需要遍历叶子节点就可以了，不需要遍历整棵B+Tree拿到所有的数据）
B+Tree的磁盘读写能力相对于BTree来说更强（根节点和枝节点不保存数据区，所以一个节点可以保存更多的关键字，一次磁盘加载的关键字更多）
排序能力更强（因为叶子节点上有下一个数据区的指针，数据形成了链表）
效率更加稳定（B+Tree永远是在叶子节点拿到数据，所以IO次数是稳定的）

6 红黑树

为什么不用红黑树？1、只有两路；2、不够平衡。红黑树一般只放在内存里面用。例如Java的TreeMap。

4.B+Tree 落地形式

首先，MySQL的数据都是文件的形式存放在磁盘中的，我们可以找到这个数据目录的地址，在这里我们能看到，每张InnoDB 的表有两个文件（.frm和.ibd）
xxx.frm是MySQL里面表结构定义的文件，不管你建表的时候选用任何一个存储引擎都会生成
InnoDB只有一个文件（.ibd文件），那索引放在哪里呢？在InnoDB 里面，它是以主键为索引来组织数据的存储的，所以索引文件和数据文件是同一个文件，都在.ibd文件里面。在InnoDB的主键索引的叶子节点上，它直接存储了我们的数据。

聚集索引

就是索引键值的逻辑顺序跟表数据行的物理存储顺序是一致的。
（比如字典的目录是按拼音排序的，内容也是按拼音排序的，按拼音排序的这种目录就叫聚集索引）。
在InnoDB里面，它组织数据的方式叫做叫做（聚集）索引组织（clusteredindexorganize table），所以主键索引是聚集索引，非主键都是非聚集引。
如果InnoDB里面主键是这样存储的，那主键之外的索引，比如我们在name字段上面建的普通索引，又是怎么存储和检索数据的呢？ InnoDB中，主键索引和辅助索引是有一个主次之分的。
辅助索引存储的是辅助索引和主键值。如果使用辅助索引查询，会根据主键值在主键索引中查询，最终取得数据。
比如我们用 name 索引查询 name='青山'，它会在叶子节点找到主键值，也就是 id=1，然后再到主键索引的叶子节点拿到数据。
为什么辅助索引上不储存磁盘上的真实地址，因为前面说过页的地址会因为主键进行分裂和修改，所以如果存储地址，每当页发生改变所有的辅助索引都需要改变，这非常消耗性能

如果没有主键怎么处理

1、如果我们定义了主键(PRIMARY KEY)，那么InnoDB会选择主键作为聚集索引。

2、如果没有显式定义主键，则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引。

3、如果也没有这样的唯一索引，则InnoDB会选择内置6字节长的ROWID作为隐藏的聚集索引，它会随着行记录的写入而主键递增。

联合索引

有一个最左匹配原则，即扫描name可以使用到索引，扫描phone就不会使用到索引

覆盖索引

回表：非主键索引，我们先通过索引找到主键索引的键值，再通过主键值查出索引里面没有的数据，它比基于主键索引的查询多扫描了一棵索引树，这个过程就叫回表。在辅助索引里面，不管是单列索引还是联合索引，如果select的数据列只用从索引中就能够取得，不必从数据区中读取，这时候使用的索引就叫做覆盖索引，这样就避免了回表。

索引条件下推

索引条件下推（IndexConditionPushdown），5.6以后完善的功能。只适用于二级索引。ICP 的目标是减少访问表的完整行的读数量从而减少 I/O 操作。

举个例子

根据联合索引查出所有姓wang的二级索引数据，然后回表，到主键索引上查询全部符合条件的数据（3 条数据）。然后返回给 Server 层，在Server 层过滤出名字以 zi结尾的员工。
根据联合索引查出所有姓wang的二级索引数据（3个索引），然后从二级索引中筛选出first_name以zi结尾的索引（1个索引），然后再回表，到主键索引上查询全部符合条件的数据（1条数据），返回给Server 层。

5.索引使用原则

列的离散（sàn）度第一个叫做列的离散度，我们先来看一下列的离散度的公式：
count(distinct(column_name)) : count(*)，列的全部不同值和所有数据行的比例。数据行数相同的情况下，分子越大，列的离散度就越高。

简单来说，如果列的重复值越多，离散度就越低，重复值越少，离散度就越高
了解了离散度的概念之后，我们再来思考一个问题，我们在name上面建立索引和在gender上面建立索引有什么区别。
当我们用在gender上建立的索引去检索数据的时候，由于重复值太多，需要扫描的行数就更多。例如，我们现在在gender列上面创建一个索引，然后看一下执行计划。如果在B+Tree里面的重复值太多，MySQL的优化器发现走索引跟使用全表扫描差不了多少的时候，就算建了索引，也不一定会走索引。所以我们建立索引要使用离散度（选择度）更高的字段。

6.索引的创建与使用

1、在用于where判断order排序和join的（on）字段上创建索引

2、索引的个数不要过多。

——浪费空间，更新变慢。 3、区分度低的字段，例如性别，不要建索引。

——离散度太低，导致扫描行数过多。 4、频繁更新的值，不要作为主键或者索引。

——页分裂 5、组合索引把散列性高（区分度高）的值放在前面。

6、创建复合索引，而不是修改单列索引。

7、过长的字段，怎么建立索引 1. 使用文本索引 2. 使用前缀索引（截取字段的前几位作为索引）

8、为什么不建议用无序的值（例如身份证、UUID ）作为索引 1. 避免频繁的进行页的分裂、旋转和开辟新页

什么时候用不到索引？

1、索引列上使用函数（replace\SUBSTR\CONCAT\sum count avg）、表达式、计算（+ - * /）：

2、字符串不加引号，出现隐式转换

3、like条件中前面带%

4、部分负向查询

一个SQL语句是否使用索引，跟数据库版本、数据量、数据选择度都有关系。

其实，用不用索引，最终都是优化器说了算。优化器是基于什么的优化器？基于cost开销（CostBaseOptimizer），它不是基于规则（Rule-BasedOptimizer），也不是基于语义。怎么样开销小就怎么来。

MySQL架构（二）-索引的结构与使用

索引