一次查询过程

使用索引查询时，如果需要读取一个页，并不会直接读取，而是先读取这个页到缓冲池，再从缓冲池进行index key等等的查找！！！不使用缓冲池的化就是直接读取查找索引叶子数据页了，但是有了缓冲池能够使得速度更快，所以都是现罢要查询的数据页读取到缓冲池中，再进行搜索。

最左匹配原则

最左匹配原则

1：概述

InnoDB支持一下几种常见的索引：

• B+索引（B代表平衡）

B+数索引并不能找到一个给定键值的具体行，只能查找到数据行所在的页。把页读入内存（即缓冲池），再在内存缓冲池中进行查找。

• 全文索引
• 哈希索引（自适应的）

2:索引

2.1：B+树简介

B+数索引的每个节点都是一个页！中间节点是索引页，叶子节点是数据页

下面的B+树扇出为5.

注意每个大方框（页）才是一个节点，而不是一个记录就是一个节点。

B+树的插入（非B+索引）

要插入数据的数据页（叶子节点）	上层索引页（中间页）	操作
没满	/	直接插入到数据页即可
满了	没满	拆分叶子节点（一半一半再插入数据）
满了	满了	两次拆分，先才分中间页，变成第二种情况，再才分数据页

其实才分很耗性能贺时间，所以B+树也有类似于旋转的功能，比如上表情况二，如果相邻的叶子节点还有空余，就可以进行旋转（移动），一半先看左边，再看右边有没有空余，从而减少了一次拆分。

B+树的删除（非B+索引）

删除操作与填充因子学习相关，50%是填充因子的最小值。

叶子节点小于填充因子	中间节点小于填充因子	操作
否	/	直接在页（叶子节点）上删除即可，如果该节点是页最小元素，还要更新上面的中间节点
是	否	合并叶子节点贺它的兄弟节点，并且更新中间节点
是	是	先合并叶子节点贺中间节点，如何更新中间节点，再合并中间节点，两次合并

2.2：B+树索引

B+树索引的本质就是B+树再数据库中的实现。但是B+树索引再数据库中的一个特点是高扇出性，所以B+索引的层级少，一般为 2-4层。

聚集索引

聚集索引就是按照每张表的主键来构造一颗B+树。同时叶子节点存放的就是整张表的行记录数据，聚集索引的叶子节点也称为数据页。这个特性决定了索引组织表中数据也是索引的一部分。一张表只会有一个聚集索引，按照主键生成。

特点：聚集索引的叶子节点（数据页）上存放的是完整的每行的记录，而在非数据页（中间节点）存放的只是键值（聚集索引这里是主键）贺指向数据页的偏移量（指针）。

非聚集索引

辅助索引： 一张表可以有多个， 但不是越多越好。叶子节点并不是包含行记录的全部数据，而是除了包含键值（索引列）外，包含一个对应行数据的书签，这个书签就是对应的主键。

非聚集索引的存在不影响数据再聚集索引中的组织，所以非聚集索引可以有多个。

再使用辅助索引查找数据时，先通过辅助索引找到书签后，再通过书签（主键）在聚集索引中去查找一个完整的行记录。

2.3:在InnoDB中B树的分裂

在innoDB中不能总是在页的中间节点进行分裂，因为插入的数据时按照主键顺序自增的插入的。很容易造成页空间的浪费。

比如： 第一页：123 第二页：45678. 由于插入是顺序的，那么第一页的剩余位置一般是不会再被插入数据的，就造成了空间的浪费。

在InnoDB中一般是向左或者向右分裂：

中间插入： 当前定位到的记录是待插入数据的前一条记录。如果在当前页中当前定位记录的右边还有n条数据记录，不管是否当前页占满，那么分裂点都是当前页占满后的下一记录。

最右插入：如果当前记录是当前页的最后一条记录，那么再插入记录的第一个记录就是分裂点，新成页，是自增主键中最常见的分裂。

2.4:B+树索引的管理

索引的创建和删除

创建与删除语句

Fast index creation-快速创建索引

之前创建或者删除索引，要先创建一张新的表，如何复制数据，删除原表。对于一个数据量大的表来说，可知非常消耗时间。更关键的是如果有大量事务访问该表，会出现数据库服务不可用，对于高并发很不友好。

从innodb 1.0.x版本开始支持fast index creation即快速索引创建。简称FIC.

创建辅助索引时，只需要对表加上S锁，不需要重建表，所以速度提高了很多。删除富足索引只需更新内部视图，并将辅助索引的空间标记为可用，同时删除MYSQL数据库内部视图上的索引定义即可。

注意：由于创建辅助索引时要对表加s锁，所以只支持读的事务，对于大量的写事务还是会出现服务不可用的情况。此外，FIC只针对辅助索引，聚集索引的创建贺删除还是要重建表。

online DDL在线数据定义

比起FIC，通过online DDL允许在创建辅助索引的同时，进行对表的修改（insert,update,delete）操作。 不只是创建贺删除辅助索引可以使用online DDl,以下都可以使用：

• 辅助索引的创建贺删除
• 改变自增长值
• 添加或者删除外键约束
• 列的重命名

使用online DDL有新的alter table语法：

alter table [tbl_name]
|ADD {index|key} [index_name](index_col.....)
algorithm [=] {default|inplace|copy}
lock [=] {defalult|none|shared|exclusive}

ALGORITHM指定了索引的创建算法

• COPY：创建索引是复制一个临时表。（最老的方式）
• INPLACE：创建或者删除索引不需要创建临时表
• DEFAULT：默认是INPLACE

LOCK为索引创建或者删除时对表添加锁的情况：

• NONE：不加锁，可以进行读写操作，获得最大的并发度
• SHARE：加一个S锁，并发的读事务，不能写，贺fic类似
• EXCLUSIVE：加一个X锁。读写事务都不能进行，与copy状态类似，但是不用创建表
• DEFAULT：NONE->SHARE->EXCLUSIVE

online DDL是在执行创建或者删除索引时，将DML 操作日志写入到一个缓存中。待完成索引创建后再将重做应用到表上。

2.5：Cardinality值

下接

该值表示索引中唯一数目的估计值。不是比值，比如一个有100行记录的表，主键索引的Cardinality一定为100。

改值非常关键，优化器会根据改值来判断要不要使用该索引。

Cardinality/表记录总数越接近一表示该索引越好。越接近1，选择性越高，索引的效果越好。

但是这个值只是一个大概的值，不是实时更新的，可以使用analyze table [表名]手动更新。

---

Cardinality的自动更新策略：因为更新Cardinality的消耗特别高，所以满足以下条件存储引擎才会更新：

• 表中1/16的数据发生变化
• 表更新次数>2 000 000 000(因为可能每次更新的都是那一行记录，就永远不能触发第一个条件)

Cardinality的自动更新算法：取样，所以Cardinality只是一个大概得到值，而不是精确值。

• 随机取八个数据页对该索引列不同值进行统计。
• 然后除以8，得到Cardinality

这也导致了虽然就算表没有任何更新，但是两次计算Cardinality的值会不同。

3：B+树索引的使用

3.1:联合索引

参考文章 联合索引时对表上的多列进行索引

与单列索引的创建方法一样，只是有多个索引列

alter table table_name add key idx_a_b (a,b)

索引中每列都进行了排序。很多时候可以减少一次排序操作。 例如查询某一班级中前三名的人员数据 而没有使用class索引，class索引是列class的索引。

3.2：索引覆盖

参考文章

只使用辅助索引就可以查询道结果，而不需要使用聚集索引。只要查询中使用到的列被这个索引中使用的列覆盖就行。

辅助索引的大小远小于聚集索引，因此可以减少大量的IO操作。

覆盖索引对于计数很有帮助，可以大大减少IO操作

适合统计问题

比如一个学生表，对于班级class建立了索引，执行下面的统计查询只使用辅助索引就可以完成。

select count(*) from student where class=1;

这样不仅可以不回表，而且极大的减少了io操作。（因为要把某些数据页读到内存进行统计）

• class为单列索引：

using index表示使用了索引覆盖

• 使用联合索引，class在第二个。

按照最左匹配原则，该索引应该不能使用，但是在统计操作，又能进行索引覆盖的时候却能使用

不是统计，也没有索引覆盖就不能使用，如下：

3.3：优化器选择不使用索引的情况

通过辅助索引查找，虽然索引列时有序的，但是查找出来的主键时无序的，即是离散的，再通过聚集索引进行查找便变成了磁盘上的离散读(因为对于磁盘操作，数据量多时，顺序读快于离散读。直接进行一次全表扫描肯定远远快于离散读)。

如果在辅助索引中查的主键比较多，那么再使用聚集索引进行查找开销太大，速度慢，这时候优化器不会使用辅助索引。

因此对于不能使用索引覆盖的情况，只有通过辅助索引查找的数据时少量的才会使用辅助索引。

即：

从辅助索引中查找到的离散主键比较少，优化器还是会选择使用辅助索引。 离散主键比较多了（占全表20%），优化器就不会使用辅助索引，而是直接使用聚集索引进行查询。

多发生于范围查找，辅助索引中查找出来的主键是离散的，如果比较多的话，就直接使用聚集索引进行查找了。而不会选择辅助索引。

如果使用固态硬盘，随机读很快，有足够的自信，可以使用force index来强制使用某个索引

3.4：索引提示

显示的告诉优化器使用哪个索引。

两种情况可以使用到：

• 优化器错误的选择了某个索引，导致运行慢（但是基本上不会出现）

• 某个查询语句可以使用的索引特别多，这时候优化器选择执行计划时间（选择使用说明索引）的开销可能大于语句本身。

• use index 只是建议，优化器不一定采纳。

• force index 可以强制使用这个索引。（确定要使用哪个索引就用这个）

3.5：MRR优化

MRR优化的目的是为了减少磁盘的随机访问，并且将随机访问转换为较为顺序的数据访问。

• 在查询辅助索引时，首先根据得到的查询结果，按照主键进行排序，并按照主键排序的顺序进行书签查找。
• 减少缓冲池中页背替换的次数
• 查询条件的拆分，防止查询到过多无用的数据（两个and连接的查询条件，没有mrr就是先查询一个，再使用第二个条件进行过滤，有mrr的话，就拆分为键值对（k1,l1）、(k2,l2)....进行查询）

工作方式如下：

• 将查询得到的辅助索引键值存放于一个缓存(键值缓冲区)中，这时缓存中的数据时根据辅助索引键值排序的。
• 将缓存中的键值按照主键进行排序
• 根据主键的排序顺序来访问实际的数据文件

概括就是辅助索引查询到的主键进行排序，再到主键索引中进行查询。

此外innod存储引擎的缓冲池不是足够大，如果是离散读，会导致缓存的页被新的页替换出缓冲池（innodb的缓冲池），但是如果按照主键顺序访问，此重复行为可以降为最低

使用索引覆盖会显示using MRR.

参考文章

3.6：ICP优化

与MRR一样，是mysql5.6开始支持的。

当进行索引查询时，首先根据索引来查找记录，然后再根据where条件来过滤记录。在支持index condition pushdown后，mysql数据库会在取出索引的同时，判断是否可以进行where条件的过滤，也就是将where的部分过滤操作放在存储引擎层

有一个前提条件是，where可以过滤的条件是要该索引可以覆盖到的范围。

使用ICP会有using index condition提示

where条件的提取

ICP优化文章

4:全文索引

4.1:概述

B+索引树无法很好的完成全文索引的任务。

例如like '%aaa%'

全文索引是将存储于数据库中的整本书或则整篇文章中的任意内容信息查找出来的技术。

4.2:倒排索引

倒排索引在辅助表中存储了单词和文档之间的映射关系。

通常使用关系数组来实现辅助表：其有两种类型：

• inverted file index:其表现形式为：{单词，单词所在文档的Id}
• full inverted index:其表现形式为：{单词，（单词所在的文档Id，在文档中的具体位置第几个）}

辅助表是持久的表，存放在磁盘中。

4.3:InnoDB全文检索

innodb1.2.x版本后支持全文检索

p233

采用full inverted index的关系数组来实现辅助表 。

innodb存储引擎中有六张辅助表，提高了全文检索的并行能力。

FTX index cache 全文检索索引缓存

FTX index cache是一个红黑树结构，根据（word，I list）进行排序。这就意味着插入的数据已经更新到表中来，但是辅助表还没有更新，这时分词操作的结果还缓存在FTX index cache中。

innodb存储引擎会批量对辅助表进行跟新，而不是插入一个就去更新辅助表，而已现将插入信息缓存在FTX index cache中。

当对全文检索进行查询时，辅助表要先将FTX index cache中的内容更新到辅助表中，同步数据。

在数据库关闭时，FTX index cache中的数据也会同步到辅助表中。

数据库如果宕机了，那么FTX index cache中的数据很可能没有同步到辅助表中，那么在数据库重新启动后，当用户进行全文检索（插入或者查询）时，innodb存储引擎会自动读取未完成的文档，然后进行分词操作，最终将分词的结果放入到FTX index cache中。

FTX index cache可以设置大小，默认时32M。当该缓存满时，会将分词信息同步到辅助表中。增大FTX index cache可以增强全文检索的能力，但是越大，宕机时恢复就要花费更多的时间。

技术内幕Ｐ235后面的内容。

4.4:全文检索

mysql支持全文检索

参考文章

三种查询模式。

5：自适应hash索引

innodb中hash索引的创建不能认为干预

自适应性hash索引经hash函数映射到一个hash表中，一次对于字典类型的查找非常快速，比如where name="aaa"