MySQL索引

索引

首先数据是以文件的形式存放在磁盘上面的，每一行数据都有它的磁盘地址。有了索引之后，只需要在索引里面去检索这条数据就行了，因为它是一种特殊 的专门用来快速检索的数据结构，我们找到数据存放的磁盘地址以后，就可以拿到数据了。

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

索引数据结构

推理数据结构

二叉查找树

既能够实现快速查找，又能够实现快速插入。但是二叉查找树有一个问题： 就是它的查找耗时是和这棵树的深度相关的，如果我们插入的数据刚好是有序的。在最坏的情况下时间复杂度会退化成 O(n)

平衡二叉树

左右子树深度差绝对值不能超过 1。它应该存储三块的内容：键值，数据的磁盘地址，左子节点和右子节点的引用

问题：如果我们一个节点只存一个键值+数据+引用，例如整形的字段，可能只用了十几个 或者几十个字节，它远远达不到 16K 的容量，所以访问一个树节点，进行一次 IO 的时候， 浪费了大量的空间。所以我们的解决方案是什么呢？ 第一个就是让每个节点存储更多的数据。 第二个，节点上的关键字的数量越多，我们的指针数也越多，也就是意味着可以有 更多的分叉（我们把它叫做“路数”）。 因为分叉数越多，树的深度就会减少（根节点是 0）。

多路平衡查找树（B Tree）（分裂、合并）

B 树在枝节点和叶子节点存储键值、数据地址、节点引用。它有一个特点：分叉数（路数）永远比关键字数多 1。比如我们画的这棵树，每个节 点存储两个关键字，那么就会有三个指针指向三个子节点

从这个里面我们也能看到，在更新索引对节点进行增删的时候会有大量的索引的结构的调整，所以 解释了为什么我们不要在频繁更新的列上建索引，或者为什么不要更新主键。 节点的分裂和合并来保持平衡，其实就是 InnoDB 页的分裂和合并

B+树（加强版多路平衡查找树）

InnoDB 里面的 B+树的存储结构

MySQL 中的 B+Tree 有几个特点：

1、它的关键字的数量是跟路数相等的；

2、B+Tree 的根节点和枝节点中都不会存储数据，只有叶子节点才存储数据。搜索 到关键字不会直接返回，会到最后一层的叶子节点。

         比如我们搜索 id=28，虽然在第一 层直接命中了，但是全部的数据在叶子节点上面，所以我还要继续往下搜索，一直到叶子节点

3、B+Tree 的每个叶子节点增加了一个指向相邻叶子节点的指针，它的最后一个数据会指向下一个叶子节点的第一个数据，形成了一个有序链表的结构。

        如果是范围查询只需要顺着节点和指针顺序遍历就可以一次性访问到所有的数据节点，这样就极大地提高了区间查询效率（不需要返回上层父节点重复遍历查找）。

4、它是根据左闭右开的区间 [ )来检索数据

所以在 InnoDB 中 B+ 树深度一般为 1-3 层，它就能满足千万级的数据存

InnoDB 逻辑存储结构

MySQL 的存储结构分为 5 级：表空间、段、簇、页、行。

表空间 Table Space

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

段 Segment

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

簇 Extent

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

页 Page

一个簇中有 64 个连续的页。（1MB／16KB=64）。这些页面在物理上和 逻辑上都是连续的。页是 InnoDB 存储引擎磁盘管理的最小单位，通过 innodb_page_size 设置。

操作系统和内存打交道，最小的单位是页 Page。文件系统的内存页通常是 4K。

行 Row

数据的存放按行进行存 放。 https://dev.mysql.com/doc/refman/5.7/en/innodb-row-format.html

文件格式	行格式	描述
Antelope （Innodb-base）	ROW_FORMAT=COMPACT ROW_FORMAT=REDUNDANT	Compact 和 redumdant 的区别在就是在于首部的存 存内容区别。 compact 的存储格式为首部为一个非 NULL 的变长字 段长度列表 redundant 的存储格式为首部是一个字段长度偏移 列表（每个字段占用的字节长度及其相应的位移）。 在 Antelope 中对于变长字段，低于 768 字节的，不 会进行 overflow page 存储，某些情况下会减少结果 集 IO
Barracuda (innodb-plugin)	ROW_FORMAT=DYNAMIC ROW_FORMAT=COMPRESSED	这两者主要是功能上的区别功能上的。 另外在行 里的变长字段和 Antelope 的区别是只存 20 个字节， 其它的 overflow page 存储。 另外这两都需要开启 innodb_file_per_table=1

 

B+Tree在存储引擎的实现

MyISAM

MyISAM 的 B+Tree 里面，叶子节点存储的是数据文件对应的磁盘地址。所以从索引文件.MYI 中找到键值后，会到数据文件.MYD 中获取相应的数据记录

InnoDB

在 InnoDB 里面，它是以主键为索引来组织数据的存储的，所以索引文件和数据文 件是同一个文件，都在.ibd 文件里面。

在 InnoDB 的主键索引的叶子节点上，它直接存储了我们的数据。

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

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

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

聚集索引（聚簇索引）

就是索引键值的逻辑顺序跟表数据行的物理存储顺序是一致的。（比如字典的目录 是按拼音排序的，内容也是按拼音排序的，按拼音排序的这种目录就叫聚集索引）。 在 InnoDB 里面，它组织数据的方式叫做叫做（聚集）索引组织表（clustered index organize table），所以主键索引是聚集索引，非主键都是非聚集索引

辅助索引存储的是辅助索引和主键值。如果使用辅助索引查询，会根据主键值在主键索引中查询，最终取得数据

问题为什么存储主键不存地址？

因为地址在经过分裂合并后会发生变化

索引使用原则

索引越多越好？

列的离散度 = count(distinct(column_name)) : count(*) 列的全部不同值和所有数据行的比例。 数据行数相同的情况下，分子越大，列的离散度就越高

如果列的重复值越多，离散度就越低。重复值太多，需要扫描的行数就更多。所以一般不使用性别字段创建索引

联合索引最左匹配

建立联合索引的时候，一定要把最常用的列放在最左边

覆盖索引

在辅助索引里面，不管是单列索引还是联合索引，如果 select 的数据列只用从索引 中就能够取得，不必从数据区中读取，这时候使用的索引就叫做覆盖索引，这样就避免了回表

回表： 非主键索引，我们先通过索引找到主键索引的键值，再通过主键值查出索引里面没 有的数据，它比基于主键索引的查询多扫描了一棵索引树，这个过程就叫回表

什么时候用不到索引？

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

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

3、like 条件中前面带%