五. MySQL 索引

一. 基础解释

1. 什么是索引:索引(Index)是帮助mysql提高查询数据效率的一种数据结构,可以简单理解为"对数据根据某种规则进行排序,根据这个有序的规则能够更快的查找数据"(数据之外,数据库系统维护着满足特定查找算法的数据结构,这种数据结构一引用的方式执行数据例如B+树,类比字典)
2. 索引对SQL执行的影响: 在执行一条sql时,索引会影响到"where"条件约束,是否用到索引,还会影响到"order by"排序两大功能(总结,索引拥有排序查找两大功能,一个解决Where后的过滤条件,提高查询效率,一个解决OrderBy时如何查找的快)
3. 索引往往以文件的形式存储在磁盘中:

1. MyISAM存储引擎时索引与实际数据分开存储,"表名.MYD"文件存储了实际数据,"表名.MYI"文件存储了索引相关信息
2. InnoDB存储引擎时索引与实际数据同时存储在".ibd"文件中

4. 我们常说的索引如果没有特别指明,默认是InnoDB,采用B+数(多路搜索数,并不一定是二叉的)结构组织的索引
5. 除了B+树索引外其他还有例如: hash哈希索引,二叉树, b树等等

二. 使用索引带来的好处与坏处

1. 优势:

创建索引,提高数据检索效率,降低数据库IO,提高数据查询效率
通过索引对数据进行排序,降低数据排序成本,减低CPU消耗,降低IO

2. 劣势:

索引可以提高查询效率,但是会影响更新速度,例如insert update delete,因为更新表时,mysql不紧要保存数据,还需要保存索引文件,保存每次更新对键值变化后的索引信息
在执行sql时底层会先通过执行优化器对sql进行优化,如果索引过多,会增加优化器的优化时间

3. mysql执行过程(由于mysql会自己对sql进行预处理,优化,如果索引过多,会增加预处理优化的实际)

1. 客户端发sql请求到达服务器
2. 服务器检查是否可以通过缓存命中
3. 服务器对sql进行解析,预处理, 执行优化器进行优化,生成执行计划
4. 根据执行计划,调用存储引擎API查询数据
5. 返回结果

三. 索引分类与创建语法

1. 索引分为: (推荐一张表创建索引不要超过五个)

单值索引: 既一个索引只包含一个列,一个表中可以建多个单值索引
唯一索引: 索引列的值必须唯一,但是允许空值(主键索引不能为null)
复合索引: 既一个索引可以包含多列

2. 基本语法

创建：ALTER mytable ADD [UNIQUE ] INDEX [indexName] ON (columnname(length))
删除：DROP INDEX [indexName] ON mytable;
查看：SHOW INDEX FROM table_name\G

3. 四种添加索引语句解释

##该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list)

##这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list)

##添加普通索引，索引值可出现多次
ALTER TABLE tbl_name ADD INDEX index_name (column_list)

##该语句指定了索引为 FULLTEXT ，用于全文索引
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)

主键索引语法示例

#随表一起建索引：
CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
  PRIMARY KEY(id) 
);
#使用AUTO_INCREMENT关键字的列必须有索引(只要有索引就行)。
CREATE TABLE customer2 (id INT(10) UNSIGNED,customer_no VARCHAR(200),customer_name VARCHAR(200),
  PRIMARY KEY(id) 
);
#单独建主键索引：
ALTER TABLE customer add PRIMARY KEY customer(customer_no);  
#删除建主键索引：
ALTER TABLE customer drop PRIMARY KEY ;  
#修改建主键索引：
#必须先删除掉(drop)原索引，再新建(add)索引

单值索引语法示例

#随表一起建索引：
CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
  PRIMARY KEY(id),
  KEY (customer_name)  
);
#随表一起建立的索引 索引名同 列名(customer_name)
#单独建单值索引：
CREATE INDEX idx_customer_name ON customer(customer_name); 
#删除索引：
DROP INDEX idx_customer_name ;

唯一索引语法示例

#随表一起建索引：
CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
  PRIMARY KEY(id),
  KEY (customer_name),
  UNIQUE (customer_no)
);
#建立 唯一索引时必须保证所有的值是唯一的（除了null），若有重复数据，会报错。   
#单独建唯一索引：
CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no); 
#删除索引：
DROP INDEX idx_customer_no on customer ;

复合索引语法示例

#随表一起建索引：
CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
  PRIMARY KEY(id),
  KEY (customer_name),
  UNIQUE (customer_name),
  KEY (customer_no,customer_name)
);
#单独建索引：
CREATE INDEX idx_no_name ON customer(customer_no,customer_name); 
#删除索引：
DROP INDEX idx_no_name  on customer ;

四. 索引的问题点一

哪些情况下需要创建索引

1. 主键自动建立唯一索引
2. 频繁作为查询条件的字段应该创建索引(where 后面的语句)
3. 查询中与其它表关联的字段，外键关系建立索引
4. 单键/组合索引的选择问题，who？(在高并发下倾向创建组合索引)
5. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
6. 查询中统计或者分组字段

哪些情况不需要创建索引

1. 表记录太少
2. 经常增删改的表：提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE时,除了操作实际数据外还需要维护索引结构
3. Where条件里用不到的字段不创建索引
4. 数据重复且分布平均的表字段，某个数据列重复的内容过多时，建立索引没有太大实际效果。

聚簇索引(主索引)

一张表只能有一个聚簇索引，通常是主键列，在innodb存储引擎中由聚簇索引构成的b+tree其的节点中key就是主键id，该主键索引的叶子节点上存储的就是一行记录的所有字段。

非聚簇索引(辅助索引)

一个表中可以有多个，叶子节点存放的不是一整行数据，而是主键，所以无法命中覆盖索引时会需要通过叶子结点中的主键去聚簇索引的b+tree中再次寻找，也就是常说的非聚簇索引无法命中覆盖索引时会造成两次b+tree的搜索

最左前缀匹配原则

1. 假设联合索引由列(a,b,c)组成,满足最左前缀规则：a、ab、abc；selece、where、order by 、group by都可以匹配最左前缀。其它情况都不满足最左前缀规则就不会用到联合索引(执行的sql中索引顺序无所谓,但是要存在a,覆盖索引时select出的字段要与索引的顺序对应)
2. **(也可以看为是最左前缀匹配原则原理)**最左前缀匹配原则和联合索引的索引存储结构和检索方式是有关系的, 在组合索引树中，最底层的叶子节点按照第一列a列从左到右递增排列，但是b列和c列是无序的，b列只有在a列值相等的情况下小范围内递增有序，而c列只能在a，b两列相等的情况下小范围内递增有序, 就像上面的查询，B+树会先比较a列来确定下一步应该搜索的方向，往左还是往右。如果a列相同再比较b列。但是如果查询条件没有a列，B+树就不知道第一步应该从哪个节点查起
3. 可以说创建的idx_abc(a,b,c)索引，相当于创建了(a)、(a,b)(a,b,c)三个索引。
4. 组合索引的最左前缀匹配原则：使用组合索引查询时，mysql会一直向右匹配直至遇到范围查询(>

覆盖索引

1. 正常情况下通过非聚簇索引(辅助索引)查询数据时,非聚簇索引的叶子节点中存放的是该数据的主键,然后通过主键找到聚簇索引,再使用聚簇索引查询到数据
2. 如果select的列个数,刚好满足某个索引(查询的字段个数可以小于索引中的字段个数,但是不能大于),或符合索引,就叫做覆盖索引,覆盖索引则是通过非聚簇索引(辅助索引)不需要再查询聚簇索引,直接就可以找到记录(where的条件存不存在索引列无所谓),简单解释

## id为主键,name添加了索引,在执行该sql时,select查询的包含索引列,此时就称为覆盖索引,无需回表
select id,name from table where name=""

## select时多加了一个age字段,如果age没有添加索引,不满足索引覆盖,则需要通过id值使用聚集索引再次查询age字段,多一次回表
select id, name, age from table where name=""

## 如果上面在添加索引时,添加的是"name"+"age"的联合索引,再执行上方sql时,则会满足覆盖索引,无需回表

3. 使用覆盖索引的一个好处是因为辅助索引不包括一条记录的整行信息，所以数据量较聚集索引要少，可以减少大量io操作

索引下推

1. 解释: 索引下推是MySql在5.6版本推出的针对联合索引的功能,查询条件命中了联合索引的第一个字段,后续条件可以直接在联合索引树中匹配过滤, 根据下方sql举例说明索引下推的好处

## user表中添加了两个索引,分别是name, age
SELECT * from user where  name like '陈%' and age=20

2. 在5.6以前没有索引下推功能时,sql执行,拿到第一个name索引字段后,就不会继续向下寻找后会的索引,又由于是模糊查询,需要多次回表通过id查询出数据,然后进行过滤
3. 在5.6推出索引下推后,在索引内部通过联合索引树对多个索引值进行匹配，对于不等于20的记录直接跳过，匹配成功后再通过id去主键索引树中回表查询全部数据，这个过程只需要回表一次
4. 注意点: 实际当执行的sql中存在多个二级索引时,执行时只会选择其中一个使用,其它的二级索引是作为过滤条件来使用的,特殊场景下会触发索引合并

索引合并

1. 上面说了索引下推好像是针对联合索引的,那么如果命中两个单值索引时是如何优化的, mysql 提出了索引合并, 可以简单理解为:执行查询时用到了多个索引树, 对多个索引进行分别扫描，然后根据结果进行集合操作(如取交集和并集)
2. 在索引合并时有三种算法: Intersection交集合并, Union并集合并, Sort-Union合并
3. Intersection交集合并的几种情况:

1. and条件连接多索引等值查询 例如 where a=" " and b=" " ,此时会分别在a和b两个索引树上获取到等值匹配的数据(也就是id),取交集,然后通过交集拿到的id再去主索引树获取数据,
2. 多索引范围查询: 注意只有主键索引范围查询与其它单值索引配合才会触发交集合并,例如where id<100 and a=“”, 因为主键索引是有序的

4. Union并集合并:**OR条件连接多索引等值查询 ** 例如 where a=“” or b=“” 实际底层会改写为 select * from table a=“” union select * from table b=“”
5. Sort-Union合并: 多索引范围查询, 例如: where a < " " OR b >" " 先通过a索引树匹配获取数据,由于无序是无序的需要先进行排序, 再查询b的索引说,然后排序,再用union合并索引,获取到并集id,通过拿到的id再去主索引树查询数据，这种方法就是sort-union
6. 注意点: 联合索引不可以代替intersection合并 如果a与b两个索引字段,其中任何一个是个联合索引的一部分,即使是联合索引的头结点,也不会触发交集合并, 并且如果ab是一个完整的联合索引时,比索引合并更优

五. 索引数据结构

1. 索引包含: BTree索引, Hash索引, full-text全文索引, R-Tree索引

1. hash

1. 以Map集合hash为例来说明,调用hashCode()方法获取到一个唯一的hash值,根据该hash值确定当前数据在集合中的存放位置,数据库中当查询数据时直接通过hash值去获取,提高查询效率
2. 问题: 在存储数据时发生hash冲突,需要全表hash,重新计算每个数据的hash值,重新排列,hash只满足"=","IN”和“<=>”查询,不能范围查询,因为经过hash算法处理后的hash值的大小关系，并不能保证与处理前的hash大小关系对应,由于这个原因,也无法通过hash对数据进行排序,并且无法进行组合索引

2. 平衡二叉树

1. 节点最多有两个子节点的树结构,“左子树”和“右子树”,获取一个中间值为跟节点,后续按照数据的大小与中间值进行比对将数据放入二叉树的指定节点上,在查询时有点像二分查找,折半查找提高查询效率,所谓平衡说的是这棵树的各个分支的高度是均匀的，它的左子树和右子树的高度之差绝对值小于1,这种树的优点是不会出现一条支路特别长的情况,通过二叉树查找数据,每次查找的次数也就确保不会超过数的高度,从而确保查询效率
2. 通过下图解释查询过程,假设查询10,会执行四次查询,有四次IO操作(io操作读取硬盘数据到内存中的动作,第一次读取硬盘数据到内存中4判断大于4,第二次读取到8判断大于8----->最终读取到10)
3. 二叉树平衡二叉树的缺点:

1. 二叉树,在构建树时,可能会出现其中一个分支特别长的情况,例如添加的数据都比父节点大或者小,那么后续的数据都会存在右树或者左树上,出现树退化
2. 平衡二叉树问题1: 在范围查询时定位到上限或者下限后需要回旋判断,如下图: 假设查询大于5的数据,首先会查询到5,然后一个一个的回旋判断6,7,8----一直到获取到所有的大于5的数据,效率比较低,
3. 平衡而查询问题2: 重点,插入删除数据时为了保证树的平衡(左子树和右子树的高度差小于1)需要维护树的结构
   

3. b树

1. 在平衡二叉树基础上进行了改进,在平衡二叉树中,树的高度决定了查询时的io次数,b树的一个节点上可以存放多个数据,减少数据高度,减少io操作(下图中查询10只需要2次io操作),范围查询时与平衡二叉树相同需要回旋,效率较低
   
2. B树的节点中分为三个部分,分别保存了"键值",“指向下一个节点的索引”,与data数据值
3. B树的优点

每个节点至多可以拥有m(m>=2)棵树
根节点至少有两个节点(意思是根节点至少存在两个数据?要么极端情况,根节点也就是叶节点的情况)
非根非叶的节点至少有的Ceil(m/2)个子树(Ceil表示向上取整，图中5阶B树，每个节点至少有3个子树，也就是至少有3个叉)
非叶节点中的信息包括[n,A0,K1,A1,K2,A2,…,Kn,An]，，其中n表示该节点中保存的关键字个数，K为关键字且Ki<Ki+1，A为指向子树根节点的指针。
从根到叶子的每一条路径都有相同的长度，也就是说，叶子节在相同的层，并且这些节点不带信息，实际上这些节点就表示找不到指定的值，也就是指向这些节点的指针为空
B树在插入删除新的数据记录会破坏B-Tree的性质，因为在插入删除时，需要对树进行一个分裂、合并、转移等操作以保持B-Tree性质

4. b+树

1. 在b树的基础上区分叶子节点与非叶子节点,将数据分为key—value的形式,所有数据保存在叶子节点上,并且叶子节点中使用链表对所有数据进行了排序,在生成树时,非叶子节点上只存储了该数据该数据的key,通过该key可以直接在叶子节点上找到该数据,由于使用链表进行了排序在范围查询时不需要回旋,该范围以前的就是大于,以后的就是小于(再次解释上面说的key-value)

非叶子节点中只存放了key,也可以看为指向下一个节点的地址值, value是是叶子节点中保存的数据
叶子节点中的value不一定是保存的数据,要根据当前索引使用的是InnoDB,还是MyISAM,并且要区分聚簇索引还是非聚簇索引

2. b+树优点

有n棵子树的节点含有n个关键字（也有认为是n-1个关键字）
所有关键字都存在叶子节点中,且叶子节点是经过排序的
非叶子节点可以看为是索引部分,节点中包含了子节点的最大值关键字(key),与最小值关键字(key)
查询时非叶子节点不会命中返回。非叶子节点相当于叶子节点的索引，叶子节点相当于是存储（关键字）数据的数据层

带有顺序访问指针的B+Tree

解释:说白了就是在叶子节点部分加上顺序指针，在B+Tree的每个叶子节点增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的B+Tree。做这个优化的目的是为了提高区间访问的性能, 在数据库系统或文件系统中使用的B+Tree结构都在经典B+Tree的基础上进行了优化，增加了顺序访问指针

MySQL为什么使用B树（B+树）作为索引

1. hash只满足"=","IN”和“<=>”查询,不能范围查询,因为经过hash算法处理后的hash值的大小关系，并不能保证与处理前的hash大小关系对应
2. 平衡二叉树,一个树高问题一个节点最多只有两个子节点,一个是在范围查询时回旋次数问题,重点是插入与删除数据时为了保证树的平衡需要调整树的结构
3. 索引本身就很大,存储在磁盘上,如果通过索引查找数据,也要先进行磁盘io,所以评价一个索引的好坏要看磁盘io的次数,复杂度,使用索引要尽量减少磁盘io,B+树,每个节点中可以存储多个数据,减少了树的高度,减少了io次数,回表次数
4. b树节点的结构是键值,指向下一个节点的指针,data数据,三部分,b+树内非叶子节点去掉了data域，因此可以拥有更大的出度(出度也就是树中各个节点的度的最大值)，拥有更好的性能
5. b+树,叶节点之间通过双向链表链接,在B树中进行范围查询时，首先找到要查找的下限，然后对B树进行中序遍历，直到找到查找的上限；而B+树的范围查询，只需要对链表进行遍历即可
6. B树的查询时间复杂度在1到树高之间(分别对应记录在根节点和叶节点)，而B+树的查询复杂度则稳定为树高，因为所有数据都在叶节点
7. b+树在新建节点时会申请一页的空间,保证一个节点物理上也存储在一个页里,进而保证了每读取一个节点只需要一次磁盘io
8. b+树,根节点是储存在内存中的,这样一次检索只需树的高度-1次io操作,渐进复杂度为O(h)=O(logdN)。一般实际应用中，树中各个节点的度的最大值是非常大的数字，通常超过100，因此树的高度非常小

B+树也存在劣势

B树中一条记录只会出现一次，不会重复出现，而B+树的键则可能重复重现——一定会在叶节点出现，也可能在非叶节点重复出现,由于键会重复出现，因此会占用更多的空间。但是与带来的性能优势相比，空间劣势往往可以接受，因此B+树的在数据库中的使用比B树更加广泛

总结

1. 二叉查找树(BST)：解决了排序的基本问题，但是由于无法保证平衡，可能退化为链表；
2. 平衡二叉树(AVL)：通过旋转解决了平衡的问题，但是旋转操作效率太低；
3. 红黑树：通过舍弃严格的平衡和引入红黑节点，解决了AVL旋转效率过低的问题，但是在磁盘等场景下，树仍然太高，IO次数太多整理了一份328页MySQLPDF文档；
4. B树：通过将二叉树改为多路平衡查找树，解决了树过高的问题；
5. B+树：在B树的基础上，将非叶节点改造为不存储数据的纯索引节点，进一步降低了树的高度；此外将叶节点使用指针连接成链表，范围查询更加高效

5. B+树层高与存储数据个数计算

1. B树/B+树与红黑树二叉树等相比,最大的优势在于树高更小,Innodb的B+索引树的高度一般在2-4层
2. 树的高度取决于每个节点可以存储多少条记录,Innodb中每个节点使用一个page页,页的大小为16KB,其中元数据占大约128字节左右(包括文件管理头信息、页面头信息等等),大多数空间都用来存储数据
3. 对于非叶节点,内部存储了索引的键和指向下一层节点的指针,假设每个非叶节点页面存储1000条记录,则每条记录大约占用16字节,这也是索引列长度不应过大的原因,如果索引列过长每个节点包含的记录数就会减少,会导致树高增加,总结:

1. InnoDB时,以页为单位存储数据,一个页为16384个字节,也就是16k,一般情况下一棵B+树有三层, 以三层B+树为例计算
2. 首先B+树中分为叶子节点与非叶子节点, 叶子节点中存放了一行完整的数据,非叶子节点中存放了当前索引的值与多个指向下一个节点的指针
3. InnoDB时 所有数据以主键索引构建B+树存放数据,假设使用Bigint作为主键类型,那么主键占8个字节,默认指向下一个节点的指针占6个字节,也就是说非叶子节点中,也就是说根节点由16384/(8+6)=1170个子节点,如果B+树为3层,第二层1170个子节点下有1170*1170=1368900叶子节点
4. 假设一行数据为1k, 每个叶子节点可以存放16条数据(以页为单位一页16k),一共可以存储1368900*16=21902400条记录

七. IO 次数

MyISAM

以下分析仅供参考，MyISAM在查询时，会将索引节点缓存在MySQL缓存中，而数据缓存依赖于操作系统自身的缓存，所以并不是每次都是走的磁盘，这里只是为了分析索引的使用过程

根据主键等值查询

1. 先在主键树中从根节点开始检索，将根节点加载到内存，比较28<75，走左路。(1次磁盘IO)
2. 将左子树节点加载到内存中，比较16<28<47，向下检索。(1次磁盘IO)
3. 检索到叶节点，将节点加载到内存中遍历比较16<28，18<28，28=28<47。查找到值等于28的索引项。
4. 根据磁盘地址从数据文件中获取行记录缓存到结果集中。(1次磁盘IO)
5. 我们的查询语句时范围查找，需要向后遍历底层叶子链表，直至到达最后一个不满足筛选条件。
6. 向后遍历底层叶子链表，将下一个节点加载到内存中，遍历比较，28<47=47，根据磁盘地址从数据文件中获取行记录缓存到结果集中。(1次磁盘IO)
7. 最后得到两条符合筛选条件，将查询结果集返给客户端

根据主键范围查询

1. 先在主键树中从根节点开始检索，将根节点加载到内存，比较28<75，走左路。(1次磁盘IO)
2. 将左子树节点加载到内存中，比较16<28<47，向下检索。(1次磁盘IO)
3. 检索到叶节点，将节点加载到内存中遍历比较16<28，18<28，28=28<47。查找到值等于28的索引项。
4. 根据磁盘地址从数据文件中获取行记录缓存到结果集中。(1次磁盘IO)
5. 我们的查询语句时范围查找，需要向后遍历底层叶子链表，直至到达最后一个不满足筛选条件。
6. 向后遍历底层叶子链表，将下一个节点加载到内存中，遍历比较，28<47=47，根据磁盘地址从数据文件中获取行记录缓存到结果集中。(1次磁盘IO)
7. 最后得到两条符合筛选条件，将查询结果集返给客户端。

InnoDB

InnoDB区分聚集索引(主索引),非聚集索引(普通索引)

根据主键等值查询

1. 先在主键树中从根节点开始检索，将根节点加载到内存，比较28<75，走左路。(1次磁盘IO)
2. 将左子树节点加载到内存中，比较16<28<47，向下检索。(1次磁盘IO)
3. 检索到叶节点，将节点加载到内存中遍历，比较16<28，18<28，28=28。查找到值等于28的索引项，直接可以获取整行数据。将改记录返回给客户端。(1次磁盘IO)

总结: 在使用主键进行等值查询时,根节点通常是存在内存中的,如果读取跟节点不算,则io次数是主索引树树高-1,如果读取根节点算磁盘IO,则IO次数=主索引树树高,注意InnoDB存储引擎时,主键索引的B+树,叶子节点中存储了完整的数据行,直接返回即可

辅助索引等值查询

1. 假设下图表中age字段添加了索引,除聚簇索引之外的所有索引都称为辅助索引，InnoDB的辅助索引只会存储主键值而非磁盘地址,底层叶子节点的按照(age，id)的顺序排序，先按照age列从小到大排序，age列相同时按照id列从小到大排序
   
2. 使用辅助索引需要检索两遍索引：首先检索辅助索引获得主键，然后使用主键到主索引中检索获得记录,根据在辅助索引树中获取的主键id，到主键索引树检索数据的过程称为回表查询,下图中磁盘IO数：辅助索引3次+获取记录回表3次
   
3. 总结: (MySql使用InnoDB存储引擎,B+树,树高3层)

1. 第一次磁盘访问：(由于会缓存根节点,根节点IO不算,如果算第一次应该是加载根节点,定位到节点所在范围,通过这个范围向下IO查询也就是后面说的: ),在普通索引的根节点中,通过二分查找等算法,定位到指定节点数据页号
2. 第二次磁盘访问：根据数据页号,从数据页读取目标行数据,普通索引B+树的叶子节点中存储的是索引列的值和一个指向目标行所在页的指针,也可看为是主键索引,如果此时查询的数据只是普通索引列,此时直接返回即可,如果需要其他数据,继续io
3. 第三次磁盘访问(可选):如果查询的结果需要使用到其他索引列或需要取出记录中的其他列数据,那么就需要通过主键索引再次查询

4. 未命中索引时的IO次数(MySql使用InnoDB存储引擎,B+树,树高3层)

1. 第一次磁盘访问：从根节点开始,通过比较查询条件和键值大小,定位到包含目标行数据的叶子节点
2. 第二次磁盘访问：从每个非叶子节点开始，读取下一个子节点的页号，直到定位到目标行数据的叶子节点,未命中索引时，需要遍历整个索引树。因此，需要从每个非叶子节点读取它所对应子节点的页号，并且把这些页号存储在内存中，以便后续访问子节点
3. 第三次磁盘访问：从目标叶子节点开始，扫描该节点上的所有数据行，找到值为 v 的行
4. 第四次磁盘访问：如果查询的结果需要使用到其他索引列，或者需要取出记录中的其他列数据，那么就需要通过主键索引再次查询，以获取进一步的数据。这一步磁盘访问的次数可能会因具体查询条件而有所不同

组合索引(跟为什么最左前缀匹配原则)

1. 组合索引数据结构,假设对表中a,b,c三个字段添加了组合索引
   
2. 组合索引查询过程
   
3. (也可以看为是最左前缀匹配原则原理)最左前缀匹配原则和联合索引的索引存储结构和检索方式是有关系的, 在组合索引树中，最底层的叶子节点按照第一列a列从左到右递增排列，但是b列和c列是无序的，b列只有在a列值相等的情况下小范围内递增有序，而c列只能在a，b两列相等的情况下小范围内递增有序, 就像上面的查询，B+树会先比较a列来确定下一步应该搜索的方向，往左还是往右。如果a列相同再比较b列。但是如果查询条件没有a列，B+树就不知道第一步应该从哪个节点查起
4. 可以说创建的idx_abc(a,b,c)索引，相当于创建了(a)、(a,b)(a,b,c)三个索引。
5. 组合索引的最左前缀匹配原则：使用组合索引查询时，mysql会一直向右匹配直至遇到范围查询(>

回表与MRR多范围查找技术

1. 当一条查询语句命中二级索引时,会先通过二级索引查找到满足过滤条件的一批数据,然后获取到这些数据的主键,在不考虑索引下推,覆盖索引等优化情况下会使用拿着主键id一个一个的通过主索引多次回表查询,二级索引是按照索引值进行排序的,所以拿到的这批数据的主键是无序的,在通过这批主键进行回表时,执行的是随机读
2. 为了提高回表性能,MySQL提出了MRR(Disk-Sweep Multi-Range Read)多范围查找技术,通过二级索引查找到满足条件的一批数据,也就是这批数据的主键id后,会先对这批数据的主键id进行排序,在回表时可以认为是MySQL将随机读优化成了顺序读,也可以认为MySQL预读一定范围的数据到缓存中

单表数据推荐

1. 通过上面我们了解到MySql使用InnoDB存储引擎时,以B+树作为数据结构,推荐树高不要超过3层,也计算到了3层时大概可以存储2000万左右条数据,
2. 如果考虑扇出减半问题,推荐表数据不要超过500万-1000万之间
3. 什么是扇出减半: 是指在索引列值相同的情况下，B+ 树 索引的查询效率会随着 B+ 树 层数的增加而降低

举个例子: 有一个包含 1 百万行数据的表,表上有一个 name 列作为索引列。在通过 name 列的值进行查询时,该列值存在重复的情况,此时就需要继续IO比如说通过其它索引列比对,或者通过主键索引拿到叶子节点也就是完整数据行进行比对, 随着 B+ 树层数的增加,需要遍历的叶子节点数量也会呈指数级别增长,导致查询效率急剧下降