mysql索引原理（二）

1.索引是什么

  1.1 定义

   在关系数据库中，索引是一种单独的、物理的数对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

    索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。数据库使用索引以找到特定值，然后顺指针找到包含该值的行。这样可以使对应于表的SQL语句执行得更快，可快速访问数据库表中的特定信息。

    当表中有大量记录时，若要对表进行查询，第一种搜索信息方式是全表搜索，是将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记录，这样做会消耗大量数据库系统时间，并造成大量磁盘I/O操作；第二种就是在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的ROWID（相当于页码）快速找到表中对应的记录。

   1.2 索引类型

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

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

2 索引的演变过程

    2.1 二分查找 

       二分查找又称折半查找，它是一种效率较高的查找方法。二分查找要求：线性表是有序表，即表中结点按关键字有序，并且要用向量作为表的存储结构。不妨设有序表是递增有序的。但是更新数据的时候会出现一个问题，可能要挪动大量的数据 ，所以只适合存储静态的数据。

      为了支持频繁的修改，比如插入数据，我们需要采用链表。链表的话，如果是单链表，它的查找效率还是不够高，所以二叉查找树诞生了。

    2.2 二叉查找树

        二叉查找树的特点是左子树所有的节点都小于父节点，右子树所有的节点都大于父节点。投影到平面以后，就是一个有序的线性表。

 

二叉查找树既能够实现快速查找，又能够实现快速插入。但是二叉查找树有一个问题：就是它的查找耗时是和这棵树的深度相关的，在最坏的情况下时间复杂度会退化成O(n)，如果我们插入的数据刚好是有序的，2、6、11、13、17、22。这个时候我们的二叉查找树变成链表（我们把这种树叫做“斜树”），这种情况下不能达到加快检索速度的目的，和顺序查找效率是没有区别的。https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

 

 

 

造成它倾斜的原因是因为左右子树深度差太大，这棵树的左子树根本没有节点。所以为了解决这个问题我们的平衡二叉树出现了。

 2.3平衡二叉树（AVL Tree）

  平衡二叉树的定义：左右子树深度差绝对值不能超过 1。正好完美解决了二叉查找树的缺点。

 

 

平衡二叉树模拟的网址：

https://www.cs.usfca.edu/~galles/visualization/AVLtree.html 在这个网页中操作当我们插入了 1、2 之后，如果按照二叉查找树的定义，3 肯定是要在2 的右边的，这个时候根节点 1 的右节点深度会变成 2，但是左节点的深度是 0，因为它没有子节点，所以就会违反平衡二叉树的定义。这个时候会发生左旋现象，把2提到上面来，将1旋转到2的左节点上。

同样的，如果我们插入 7、6、5，这个时候会变成左左型，就会发生右旋操作，把 6

提上去。

所以为了保持平衡，AVL 树在插入和更新数据的时候执行了一系列的计算和调整的操作。

      平衡问题解决了，那么接下来说下，AVL树作为索引在查询数据时是怎么查询的，在平衡二叉树中，一个节点，它的大小是一个固定的单位，它存储三块的内容：第一个是索引的键值。比如我们在 id 上面创建了一个索引，我在用 where id =1 的条件查询的时候就会找到索引里面的 id 的这个键值。第二个是数据的磁盘地址，因为索引的作用就是去查找数据的存放的地址。第三个，因为是二叉树，它必须还要有左子节点和右子节点的引用，这样我们才能找到下一个节点。比如大于 26 的时候，走右边，到下一个树的节点，继续判断

 

AVL树的存储索引数据说完了接下来说下，AVL树的缺点：相对于二叉查找树来说是解决了平衡的问题，但是平衡二叉树还不是很完美的树，因为随着数据量的增加，树的高度跟着增加，索引的数据，是放在硬盘上的。当我们用树的结构来存储索引的时候，访问一个节点就要跟磁盘之间发生一次 IO。这样交互次数越多，消耗的时间就越多。比如上面这张图，我们一张表里面有 6 条数据，当我们查询 id=37 的时候，要查询两个子节点，就需要跟磁盘交互 3 次，如果我们有几百万的数据呢？这个时间更加难以估计。所以我们第一个就是让每个节点存储更多的数据。第二个，节点上的关键字的数量越多，我们的指针数也越多，也就是意味着可以有更多的分叉（我们把它叫做“路数”）。因为分叉数越多，树的深度就会减少（根节点是 0）。这样，我们的树是不是从原来的高瘦高瘦的样子，变成了矮胖矮胖的样子。这个时候，我们的树就不再是二叉了，而是多叉，或者叫做多路。 由此，我们的多路平衡查找树（B Tree）出现了。在讲B Tree树之前我先说下InnoDB的逻辑存储结构，后面的讲解中要用到里面的页的概念https://dev.mysql.com/doc/refman/5.7/en/innodb-disk-management.html和https://dev.mysql.com/doc/refman/5.7/en/innodb-file-space.html；有兴趣的小朋友可以进去看下官网的详解，这里面只是作简单的说明

 

MySQL 的存储结构分为 5 级：表空间、段、簇、页、行。表空间在上一节中说明过这里面就不说了。

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

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

   页 ：为了高效管理物理空间，对簇进一步细分，就得到了页。簇是由连续的页组成的空间，一个簇中有 64 个连续的页。 （1MB／16KB=64）。这些页面在物理上和逻辑上都是连续的。跟大多数数据库一样，InnoDB 也有页的概念（也可以称为块），每个页默认 16KB。页是 InnoDB 存储引擎磁盘管理的最小单位，一个表空间最多拥有 2^32 个页，默认情况下一个页的大小为 16KB，也就是说一个表空间最多存储 64TB 的数据。假设一行数据大小是 1K，那么一个数据页可以放 16 行这样的数据。举例：一个页放 3 行数据往表中插入数据时，如果一个页面已经写完，产生一个新的叶页面。如果一个簇的所有的页面都被用完，会从当前页面所在段新分配一个簇。如果数据不是连续的，往已经写满的页中插入数据，会导致叶页面分裂

  行：InnoDB 存储引擎是面向行的，也就是说数据的存放按行进行存放

 

2.4多路平衡查找树（B Tree）

   多路平衡查找树它有一个特点就是分叉数（路数）永远比关键字数多 1。比如我们画的这棵树，每个节点存储两个关键字，那么就会有三个指针指向三个子节点。

 

 因为B树节点上存储的值的数目和分叉比平衡二叉树多，所以在访问数据时进行IO的次数明显要少，性能上比平衡二叉树更好

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

 其实在Mysql的InnoDB中使用的默认使用的并不是前面所介绍的树，而是我们现在要讲的B++树。

 

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

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

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

我们来计算下B+树在树深度为2时，大概能存储多少的数据：假设一条记录是 1K，一个叶子节点（一页）可以存储 16 条记录。索引字段是 bigint 类型，长度为 8 字节。指针大小在 InnoDB 源码中设置为6 字节，这样一共 14 字节。非叶子节点（一页）可以存储 16384/14=1170 个这样的单元（键值+指针），代表有 1170 个指针。树 深 度 为 2 的 时 候 ， 有 1170^2 个 叶 子 节 点 ， 可 以 存 储 的 数 据 为1170*1170*16=21902400。  通过这个例子我们可以看出树尝试3次IO就可以存储千万级的数据，在查找数据时我们访问磁盘的次数可以减的更小。

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

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

总结一下，InnoDB 中的 B+Tree 的特点：

1)它是 B Tree 的变种，B Tree 能解决的问题，它都能解决。B Tree 解决的两大问题（每个节点存储更多关键字；路数更多）

2)扫库、扫表能力更强（如果我们要对表进行全表扫描，只需要遍历叶子节点就可以了，不需要遍历整棵 B+Tree 拿到所有的数据）

3) B+Tree 的磁盘读写能力相对于 B Tree 来说更强（根节点和枝节点不保存数据区，所以一个节点可以保存更多的关键字，一次磁盘加载的关键字更多）

4)排序能力更强（因为叶子节点上有下一个数据区的指针，数据形成了链表）

5)效率更加稳定（B+Tree 永远是在叶子节点拿到数据，所以 IO 次数是稳定的）

 

3 B+Tree落地

   3.1. MySQL 数据存储文件

      首先，MySQL 的数据都是文件的形式存放在磁盘中的，我们可以找到这个数据目录的地址。在 MySQL 中有这么一个参数，运行下列sql：show VARIABLES LIKE 'datadir';

可以查询到文件位置 

 

 

在这里我们能看到，每张 InnoDB 的表有两个文件（.frm 和.ibd），MyISAM 的表有三个文件（.frm、.MYD、.MYI）。 有一个是相同的文件，.frm。 .frm 是 MySQL 里面表结构定义的文件，不管你建表的时候选用任何一个存储引擎都会生成。 

3.1.1 MyISAM 

   在 MyISAM 里面，另外有两个文件：一个是.MYD 文件，D 代表 Data，是 MyISAM 的数据文件，存放数据记录，比如我们的 user_myisam 表的所有的表数据。一个是.MYI 文件，I 代表 Index，是 MyISAM 的索引文件，存放索引，比如我们在id 字段上面创建了一个主键索引，那么主键索引就是在这个索引文件里面。也就是说，在 MyISAM 里面，索引和数据是两个独立的文件。MyISAM 的 B+Tree 里面，叶子节点存储的是数据文件对应的磁盘地址。所以从索引文件.MYI 中找到键值后，会到数据文件.MYD 中获取相应的数据记录。

 

 

 

 

 3.1.2  InnoDB

InnoDB 只有一个文件（.ibd 文件），在 InnoDB 里面，它是以主键为索引来组织数据的存储的，所以索引文件和数据文件是同一个文件，都在.ibd 文件里面。在 InnoDB 的主键索引的叶子节点上，它直接存储了我们的数据。

 

 

 

 聚集索引 

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

辅助索引

 在MyISAM中辅助索引跟主键索引存储和检索数据的方式是没有任何区别的，一样是在索引文件里面找到磁盘地址，然后到数据文件里面获取数据。

 

 

 

但是在InnoDB 中，主键索引和辅助索引是有一个主次之分的。辅助索引存储的是辅助索引和主键值。如果使用辅助索引查询，会根据主键值在主键索引中查询，最终取得数据。

比如我们用 name 索引查询 name= '张三'，它会在叶子节点找到主键值，也就是id=1，然后再到主键索引的叶子节点拿到数据。 

 

       至于InnoDB中辅助索引为什么和MyISAM中辅助索引情况不一样，那要从InnoDB的存储方式有关，前面说过在 InnoDB 里面，它是以主键为索引来组织数据的存储的，在主键索引的叶子节点上，它直接存储了我们的数据。如果辅助索引里面存储的是磁盘地址的话，如果发生页的分裂或者合并的话，数据的地址是会发生变化的，但是数据的主键值是不会改变的，这就是为什么在InnoDB中辅助索引为什么存储的是主键。

       在InnoDB中如果我们没有定义主键，InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引，如果没有这样条件的唯一索引，则 InnoDB 会选择内置 6 字节长的 ROWID 作为隐藏的聚集索引，它会随着行记录的写入而主键递增。

4 索引的使用原则  

    4.1. 列的离散度

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

 

 

 

 

了解了离散度的概念之后，我们再来思考一个问题，我们在 name 上面建立索引和在 gender 上面建立索引有什么区别。

 

 

 

 

 通过上面两组数据对比我们可以清楚的知道name字段的离散度高；如果在 B+Tree 里面的重复值太多，MySQL 的优化器发现走索引跟使用全表扫描差不了多少的时候，就算建了索引，也不一定会走索引。 这个给我们的启发是建立索引，要使用离散度（选择度）更高的字段。

 

4.2   联合索引最左匹配

    前面我们说的都是针对单列创建的索引，但有的时候我们的多条件查询的时候，也会建立联合索引。单列索引可以看成是特殊的联合索引。比如我们在 user 表上面，给 name 和 phone 建立了一个联合索引。

 

 

联合索引在 B+Tree 中是复合的数据结构，它是按照从左到右的顺序来建立搜索树的（name 在左边，phone 在右边）。在B+Tree树中name 是有序的，phone 是无序的。当 name 相等的时候，phone才是有序的。

这个时候我们使用联合索引去查询数据的时候，B+Tree 会优先比较 name 来确定下一步应该搜索的方向，往左还是往右。如果 name相同的时候再比较 phone。但是如果查询条件没有 name，就不知道第一步应该查哪个节点，因为建立搜索树的时候 name 是第一个比较因子，所以用不到索引。 

   联合索引在什么情况下起作用：

• 使用两个字段，可以用到联合索引

• 使用左边的 name 字段，可以用到联合索引
• 使用右边的 phone 字段，无法使用索引，全表扫描

4.3  覆盖索引 

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

       例如：

 

 

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

4.4. 索引条件下推

    “索引条件下推”，称为 Index Condition Pushdown (ICP)，这是MySQL提供的用某一个索引对一个特定的表从表中获取元组”，注意我们这里特意强调了“一个”，这是因为这样的索引优化不是用于多表连接而是用于单表扫描，确切地说，是单表利用索引进行扫描以获取数据的一种方式。 

 

 

 我们给last_name,first_name建一个联合索引，然后进行查询发现使用了索引下推

 

 

 

 

我们关闭ICP后再进行同样的查询，会发现索引下推失败，Using Where 代表从存储引擎取回的数据不全部满足条件，需要在 Server 层过滤。索引条件下推是5.6 以后完善的功能。只适用于二级索引。ICP 的目标是减少访问表的完整行的读数量从而减少 I/O 操作。

5. 索引的创建与使用

    5.1. 索引的创建

    

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

2、索引的个数不要过多。——浪费空间，更新变慢。

3、区分度低的字段，例如性别，不要建索引。——离散度太低，导致扫描行数过多。

4、频繁更新的值，不要作为主键或者索引。——页分裂

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

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

7、不建议用无序的值作为索引