【总结】MySQL索引

一、MySQL索引(InnoDB)

01 /索引概述

1.1索引的分类

1.B+树索引:

B+树是为磁盘或其他直接存取辅助设备设计的一种平衡查找树。在B+树中，所有记录节点都是按键值的大小顺序存放在同一层的叶子节点上，由各叶子节点指针进行连接。
在数据库中，B+树的高度一般都在2~4层，这也就是说查找某一键值 的行记录时最多只需要2到4次10。这很不错，因为当前一般的机械磁盘每秒至少可以做100次I0，2 ~ 4次的I0意味着查询时间只需0.02~ 0.04秒。
在数据库中，B+树索引还可以分为聚集索引和辅助索引，但不管是聚集索引还是辅助索引，其内部都是B+树的，即高度平衡的，叶子节点存放着所有的数据。聚集索引与辅助索引不同的是,叶子节点存放的是否是一整行的信息。

减少和磁盘的交互次数。聚集索引的叶子节点存的是完整数据，而辅助索引存放的不是完整数据

2.哈希索引:

InnoDB存储引擎支持的哈希索引是自适应的，InnoDB存储引擎会根据表的使用情况自动为表生成哈希索引，不能人为干预是否在–张表中生成哈希索弘。

3.全文索引:

全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。全文
搜索和其他几类索引的匹配方式完全不一样。它有许多需要注意的细节，如停用词、词干和复数、
布尔搜索等。全文索引更类似于搜索引擎做的事情，而不是简单的WHERE条件匹配。

1.2索引组织表

==在InnoDB存储引擎中，表都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表。==在InnoDB存储引擎表中，每张表都有个主键，如果在创建表时没有显式地定义主键，则InnoDB存 储引擎会按如下方式选择或创建主键:

1、首先判断表中是否有非空的唯一索引(Unique NOT NULL)， 如果有，则该列即为主键。
2、如果不符合上述条件， InnoDB存 储引擎自动创建一个6字节大小的指针。

另外，当表中有多个非空唯一索引时， InnoDB存储引擎将选择建表时第一个定义的非空唯一索引为主键。这里需要非常注意的是，主键的选择根据的是定义索引的顺序，而不是建表时列的顺序。

1.3索引与约束的区别

对于InnoDB存储引擎本身而言，提供了以下五种约束: Primary Key、Unique Key、Foreign Key、Default、NOT NULL。对于Primary Key、Unique Key约束，有人不禁会问:这不就是通常创建索引的方法吗?那约束和索引有什么区别呢?
的确，当用户创建了一个唯-索引就创建了-个唯-的约束。但是约束和索引的概念还是有所不同的，约束更是一个逻辑的概念，用来保证数据的完整性，而索引是一个数据结构，既有逻辑上的概念,在数据库中还代表着物理存储的方式。

约束是我想实现唯一的需求，而索引是实现的方式

2.1存储结构

从InnoDB存储引擎的逻辑存储结构看，所有数据都被逻辑地存放在一个空间中，称之为表空间(Tablespace)。表空间又由段(Segment)、 区 (Extent) 、页(Page，最小管理单位) 组成。

例如我要查找某一行，将该行所在的页load下来，即也是最小的管理单位

1.表空间:
表空间可以看做是InnoDB存储引擎逻辑结构的最高层，所有的数据都存放在表空间中。在默认情况下 InnoDB存储引擎有一个共享表空间ibdata1（默认只有一个表空间）,即所有数据都存放在这个表空间内。若启用了参数innodb_ file_ per_table, 则每张表内的数据可以单独放到一个表空间内（修改为每一张表一个表空间）。

2.段:
表空间是由各个段组成的，常见的段有数据段、索引段、回滚段（存日志）等。因为InnoDB存 储引擎表是索引组织的，因此数据即索引，索引即数据。那么数据段存放的是B+树的叶子节点(Leaf node segment),索引段存放的是B+树的非叶子节点(Non-leaf node segment)。

3.区:
区是由连续页组成的空间，在任何情况下每个区的大小都为1MB。为了保证区中页的连续性，InnoDB存储引擎一次从磁盘申请4 ~ 5个区。在默认情况下，InnoDB存储引擎页的大小为16KB,即一个区中有64个连续的页

4.页:

页是InnoDB磁盘管理的最小单位，在InnoDB存 储引擎中，默认每个页的大小为16KB。从InnoDB1.2.x版本开始，可以通过参数innodb_page_size将页的大小设置为4K、8K、16K。 若设置完成，则所有表中页的大小都为innodb_page_size, 不可以对其再次进行修改。

需要注意的是，B+树索引只能找到记录所在的页,但是并不能定位到记录在页中的具体位置，这需要通过page directory的二分查找得到具体的记录(即先找到页，load下来再在此页中查找数据)。然而，由于通过B+树索引得知记录所在的页后，InnoDB存储引擎会将页加载到缓冲池中，二分查找在内存中完成，速度很快，因此-般会忽略这个查询的开销。

在InnoDB存储引擎中，常见的页类型有:数据页、undo页、系统页、事务数据页、插入缓冲位图页、插入缓冲空闲列表页、未压缩的二进制大对象页、压缩的二进制大对象页。

2.2聚集索引

==聚集索引就是按照每张表的主键构造一棵B+树， 同时叶子节点中存放的即为整张表的行记录数据（每个叶子节点存的是完整的一行数据，将所有叶子节点连在一起就是整张表的数据）,也将聚集索引的叶子节点称为数据页。==聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。
同B+树数据结构一样，每个数据页都通过一个双向链表来进行链接。

由于实际的数据页只能按照一棵B+ 树进行排序，因此每张表只能拥有一个聚集索引（只有一个主键）。在多数情况下，查询优化器倾向于采用聚集索引，因为聚集索引能够在B+树索引的叶子节点上直接找到数据。此外,由于定义了数据的逻辑顺序，聚集索引能够特别快地访问针对范围值的查询。

repete函数：7千个a

3是索引页，存索引。4，5，6是数据页，存数据

1：存主键大于等于1小于2的数据

2：存主键大于等于2小于4的数据

3；存主键大于等于4的数据

划分为不同的区间，存的是某一范围的数据。索引页存的是索引和（指向下一个的）指针，数据页（叶子节点）存的是完整的数据行，数据依附索引存的

聚集索引的叶子节点连起来就是整张表的数据，叶子节点通过双向指针连接

2.3辅助索引

对于辅助索引，叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外,每个叶子节点中的索引行中还包含了一个书签。该书签用来告诉InnoDB存储引擎哪里可以找到与索引相对应的行数据。（辅助索引存了一个key，用来指向聚集索引）

由于InnoDB存储引擎表是索引组织表，因此InnoDB存储引擎的辅助索引的书签就是相应行数据的聚集索引键。
辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引。当通过辅助索引来寻找数据时，InnoDB存 储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键,然后再通过主键索引来找到一个完整的行记录。

举例来说，如果在-棵高度为3的辅助索引树中查找数据，那需要对这棵辅助索引树遍历3次找到指定主键，如果聚集索引树的高度同样为3，那么还需要对聚集索引树进行3次查找，最终找到一个完整的行数据所在的页，因此一共需要6次逻辑IO访问以得到最终的一个数据页。

辅助索引存放key和指向聚集索引的指针。

简要说明：

辅助索引是建立在c列上的，聚集索引建立在a上，如果你查找的时候不用a，用到了c，如果没有辅助索引的话，就需要从叶子节点一个一个遍历。但是如果有辅助索引的话，可以从辅助索引中找到聚集索引的位置，再根据聚集索引找到对应的页。

2.4索引选择

一张表上可以建立多个索引，在执行查询时具体选择哪一个索引， 这是由SQL优化器来决定的。
我们可以通过分析执行计划，来查看SQL优化器选择了哪-个索引。

通常，SQL优化器选择的索引都是高效的。但如果你确定要显示指定个索引来完成查询，则可以使用索引提示功能来实现这样的需求。

2.5覆盖索引

InnoDB存储引擎支持覆盖索引(covering index,或称索引覆盖)， 即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作。
当发起一个被索引覆盖的查询时，在EXPL AIN的Extra列可以看到"Using index"的信息。

2.6匹配规则

B+树索引是基于B+树构建出来的有序结构，只有利用上它的有序性才能提高查询的效率。若不满足有序性这个前提，则在这个索引中的查询是离散的，其效率反而更低。

假设有如下一张表:

abc做联合索引

假设idx_ union的叶子节点数据如下:

主键索引是潜在的，我们上面创建的那个联合索引是辅助索引。

首先保证a的顺序，再保证b的顺序（在a一样的情况下），最后再保证c的顺序（局部有序）

利用好索引，利用好它的顺序规则

与该索引匹配的SQL:

从左往右看，还要是精确匹配，如果a是一个范围，去找b，b是无序的，索引就不好使了。

03 /哈希索引

3.1 InnoDB中的哈希算法

InnoDB存储引擎使用哈希算法来对字典进行查找，其冲突机制采用链表方式，哈希函数采用除法散列方式。对于缓冲池页的哈希表来说，在缓冲池中的Page页都有-个chain指针，它指向相同哈希函数值的页。
对于除法散列，槽的取值为略大于2倍的缓冲池页数量的质数。例如:当前参数
innodb_ buffer_ pool size的大小为10M，则共有640个16KB的页。对于缓冲池页内存的哈希表来说，需要分配640x2=1280个槽，但是由于1280不是质数，需要取比1280略大的一-个质数，应该是1399, 所以在启动时会分配1399个槽的哈希表，用来哈希查询所在缓冲池中的页。

3.2自适应哈希索引

InnoDB存储引擎会监控对表上各索引页的查询。如果观察到建立哈希索引可以带来速度提升，则建立哈希索引，称之为自适应哈希索引(Adaptive Hash Index, AHI) 。AHI是通过缓冲池的B+树页构造而来，因此建立的速度很快，而且不需要对整张表构建哈希索引。InnoDB存储引擎会自动根据访问的频率
和模式来自动地为某些热点页建立哈希索引。
InnoDB存储引擎 支持的哈希索引是自适应的，自适应哈希索引采用哈希表的方式实现。不同的是,这仅是数据库自身创建并使用的，DBA本身并不能对其进行干预。自适应哈希索引经哈希函数映射到一个哈希表中，因此对于字典类型的查找非常快速，但是对于范围查找就无能为力了。

04 /全文索引

4.1倒排索引

全文检索通常使用倒排索引(inverted index)来实现。倒排索引同B+树索引-样，也是一种索引结构。它在辅助表(auxiliarytable) 中存储了单词与单词自身在一个或多个文档中所在位置之间的映射。这通常利用关联数组实现，其拥有两种表现形式。

• inverted file index, 其表现形式为{单词，单词所在文档的ID};
• full inverted index,其表现形式为{单词，(单词所在文档的ID，在具体文档中的位置)};

例如，表T存储的内容如下所示:

4.2全文检索

InnoDB存储引擎从1.2.x版本开始支持全文检索的技术，其采用full inverted index的方式。在InnoDB存储引擎中，将(Documentld, Position)视为一 -个ilist"。 因此在全文检索的表中，有两个列，一个是word字段，另一个是ilist字段,并且在word字段上有设有索引。

正如之前所说的那样，倒排索引需要将word存放到一张表中，这个表称为Auxiliary Table (辅助表)。在InnoDB存储引擎中，为了提高全文检索的并行性能，共有6张Aiuxiliary Table，目前每张表根据word的L atin编码进行分区。

Auxiliary Table是持久的表，存放于磁盘上。然而在InnoDB存储引擎的全文索引中，还有另外一个重要的概念FTS Index Cache (全文检索索引缓存)， 其用来提高全文检索的性能。FTS Index Cache是一个红黑树结构，其根据(word, ilist) 进行排序。

助表)。在InnoDB存储引擎中，为了提高全文检索的并行性能，共有6张Aiuxiliary Table，目前每张表根据word的L atin编码进行分区。

Auxiliary Table是持久的表，存放于磁盘上。然而在InnoDB存储引擎的全文索引中，还有另外一个重要的概念FTS Index Cache (全文检索索引缓存)， 其用来提高全文检索的性能。FTS Index Cache是一个红黑树结构，其根据(word, ilist) 进行排序。