MySQL索引 B+树 覆盖索引 回表操作 联合索引与最左前缀匹配原则 索引下推技术 B+树的四大特点及索引回顾

让我们带着以下问题展开对索引的探索

1、为何索引叫key

2、索引是如何加速查询的，它的原理是啥？

索引模型/结构从二叉树-》平衡二叉树-》b树最后到b+树，每种树到底有什么问题最终演变成到了b+树

3、为何b+树不仅能够加速等值查询，还能加速范围查询

4、什么是聚集索引，什么是辅助索引

5、什么情况下叫覆盖了索引

6、什么情况下叫回表操作

7、什么是联合索引，最左前缀匹配原则

8、索引下推，查询优化

9、如何正确使用索引

详细笔记狂戳链接

一、 什么是索引

索引是mysql的一种数据结构，这种数据结构称之为key。大白话说索引就是一种数据的组织方式
表中的一行行数据按照索引规定的结构组织成了一种树型结构，该树叫B+树

二、 为何要用索引

数据库读写比例10:1        
读：selecrt  
写：insert update delete

优化查询速度

三、 如何正确地看待索引

错误的认知：
		1、软件上线之后，运行了一段时间，发现软件运行极卡，想到要加索引
		   火烧眉毛再想着加索引，光把问题定位到索引身上就需要耗费很长时间，排查成本很高	
  #正确做法：          
            最好是在软件开发之初配合开发人员，定位到常用的查询字段
            然后为该字段提前创建索引

        2、索引越多越好
            索引是用于加速查询的，降低写效率
            如果某一张表的ibd文件中创建了很多棵索引树，意味着很小一个update语句
            就会导致很多棵索引树都需要发生变化，从而把硬盘io打上去

四、理解索引的储备知识

​ 索引根本原理就是把硬盘io次数降下来
​ 为一张表中的一行行记录创建索引就好是为书的一页页内容创建目录
​ 有了目录结构之后，我们以后的查询都应该通过目录去查询

1.机械磁盘一次io的延迟时间=平均寻道时间（5ms）+平均延迟时间（转半圈的时间4ms）---》9ms

# 寻道时间 道时间指的是磁臂移动到指定磁道所需要的时间，主流磁盘一般在5ms以下 

# 旋转延迟 旋转延迟就是我们经常听说的磁盘转速，比如一个磁盘7200转，表示每分钟能转7200次，也就是说1 秒钟能转120次，旋转延迟就是1/120/2 = 4.17ms； 

# 硬盘通过本地往内存读，通过总线路板速度是非常快的，慢就慢在查询的速度上


这9ms对于人来说可能非常短，但对于计算机来可是非常长的一段时间，长到什么程度呢？ 一台500 -MIPS（Million Instructions Per Second）的机器每秒可以执行5亿条指令(cpu)，因为指令依靠的是电的性质，换句话说执行一次IO的时间可以执行约450万条指令，数据库动辄十万百万乃至千万级数据，每次9毫秒的时间，显然是个灾难。


2、磁盘预读
      当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次IO 读取的数据我们称之为一页(page)
	
	一页就是一个磁盘块
	innodb存储引擎一页16k，即一次io读16k到内存中

五、索引分类

#===========B+树索引（等值查询与范围查询都快） 
二叉树->平衡二叉树->B树->B+树 

#===========HASH索引(等值查询快，范围查询慢) 
将数据打散再去查询 


#===========FULLTEXT：全文索引 （只可以用在MyISAM引擎） 
通过关键字的匹配来进行查询，类似于like的模糊匹配 like + %在文本比较少时是合适的 但是对于大量的文本数据检索会非常的慢 全文索引在大量的数据面前能比like快得多，但是准确度很低 百度在搜索文章的时候使用的就是全文索引，但更有可能是ES

不同的存储引擎支持的索引类型也不一样

• InnoDB存储引擎

  支持事务，支持行级别锁定，支持 B-tree（默认）、Full-text 等索引，不支持 Hash 索

引；

• MyISAM存储引擎

不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；

• Memory存储引擎

不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；

因为mysql默认的存储引擎是innodb，而innodb存储引擎的索引模型/结构是B+树，所以我们着重介绍

B+树，那么大家最关注的问题来了：B+树索引到底是如何加速查询的呢

六、创建索引的两个步骤

1、提取索引字段的值当作key，value就是对应的本行记录
2、以key的为基础比较大小，生成树型结构

# 1、为user表的id字段创建索引，会以每条记录的id字段值为基础生成索引结构
create index 索引名 on user(id); 
使用索引 
select * from user where id = xxx; 
# 2、为user表的name字段创建索引，会以每条记录的name字段值为基础生成索引结构
create index 索引名 on user(id); 
使用索引 select * from user where name = xxx;	

七、B+树演变过程

innodb存储引擎默认的索引结构为B+树，而B+树是由二叉树、平衡二叉树、B树再到B+树一路演变过来

的

二叉树—》平衡二叉树—》B树—》B+树

二叉树

有user表，我们以id字段值为基础创建索引

# 构建树形结构后，查询次数跟树的高度有关系（树三层，查询三次）
1、提取每一条记录的id值作为key值，value为本行完整记录，即
key     value 
10      (10,zs) 
7       (7,ls) 
13      (13,ls) 
5       (5,ls) 


2、以key值的大小为基础构建二叉树，如上图所示
二叉查找树的特点就是任何节点的左子节点的键值都小于当前节点的键值，右子节点的键值都大于当前节点的键值。 顶端的节点我们称为根节点，没有子节点的节点我们称之为叶节点。

例如  select * from user where id=7;
# 加上条件where id=7 才会用上以id字段构建出来的索引树  这叫命中索引

平衡二叉树

只论二叉查找树，它的特点只是

​ 任何节点的左子节点的键值都小于当前节点的键值，右子节点的键值都大于当前节点的键值。

依据二叉查找树的特点，二叉树可以是这样构造的

这个时候可以看到我们的二叉查找树变成了一个链表。如果我们需要查找id=17的用户信息，我们需要查
找7次，也就相当于全表扫描了。 导致这个现象的原因其实是二叉查找树变得不平衡了，也就是高度太
高了，从而导致查找效率的不稳定。 为了解决这个问题，我们需要保证二叉查找树一直保持平衡，就需
要用到平衡二叉树了。

平衡二叉树

平衡二叉树保证了树的构造是平衡的，当我们插入或删除数据导致不满足平衡二叉树不平衡时，平衡二叉树会进行调整树上的节点来保持平衡,衡二叉树相比于二叉查找树来说，查找效率更稳定，总体的查找速度也更快

mysql中一次io只读一个节点或一页到内存（读一个磁盘块），一次io可以读16k,那如果一个节点只放一行记录，只读一行（几个字节）内容到内存那太浪费空间了，那如果是海量数据，平衡二叉树高度会很高，优化读的速度不是很明显。如果一个节点可以放多行记录，还能使用平衡二叉树，那高度下来了查询速度也快了io次数就降下来了，那就过渡到B树了

B树

1、图中的p节点为指向子节点的指针，二叉查找树和平衡二叉树其实也有，因为图的美观性，被

省略了。

2、图中的每个节点里面放入了多组键值对，一个节点也称为一页，一页即一个磁盘块，在

mysql中数据读取的基本单位都是页，即一次io读取一个页的数据，所以我们这里叫做页更符合

mysql中索引的底层数据结构

B树就是平衡二叉树的升级版

B树相对于平衡二叉树，每个节点存储了更多的键值(key)和数据(data)，并且每个节点
拥有更多的子节点，子节点的个数一般称为阶，上述图中的B树为3阶B树，高度也会很低。 基于这个特
性，B树查找数据读取磁盘的次数将会很少，数据的查找速度也会比平衡二叉树高很多。

假如我们要查找id=28的用户信息，那么我们在上图B树中查找的流程如下：（3次io查到）
1、先找到根节点也就是页1，判断28在键值17和35之间，我们那么我们根据页1中的指针p2找到页3。
2、将28和页3中的键值相比较，28在26和30之间，我们根据页3中的指针p2找到页8。
3、将28和页8中的键值相比较，发现有匹配的键值28，键值28对应的用户信息为(28,bv)。

#注：根节点常驻内存，只需要两次查找 B树的value还是每一行对应的完整记录，，B树只擅长做等值查询，而对于范围查询（范围查询的本质就是n次等值查询），没有排序操作

B+树

innodb存储引擎默认的数据组织结构

1、根节点常驻内存，叶子节点放的是key和完整的记录，非叶子节点放的只有key没有value，非叶子的磁盘块容纳的key值更多了。引发出来的指针更多了，树的高度进一步别压扁了，查找数据进行磁盘的IO次数有会再次减少，查询速度更快了


# 索引是浪费了空间，用空间换时间，换取了速度，B+树的高度是最低的，B+树的叶子节点默认排好序了

2、B+树中每个节点可以存储3个键，3层B+树存可以存储 3*3*3 =9个数据。所以如果我们的B+树一个节点可以存储1000个键值，那么3层B+树可以存储1000×1000×1000=10亿个数据。而一般根节点是常驻内存的，所以一般我们查找10亿数据，只需要2次磁盘IO


3、因为B+树索引的所有数据均存储在叶子节点leaf node，而且数据是按照顺序排列的。那么B+树使得范围查找，排序查找，分组查找以及去重查找变得异常简单。而B树因为数据分散在各个节点，要实现这一点是很不容易的。
而且B+树中各个页之间也是通过双向链表连接的，叶子节点中的数据是通过单向链表连接的，可以顺着叶子节点往下找

八、B+树索引两大类

一个ibd文件里面有好多个B+树

聚集索引

聚集索引、聚簇索引、主键索引：(有且只有一个，即使你不创建主键，系统也会帮你创建一个隐式的主键)
         以主键字段值为key构建的B+树，该B+树的叶子节点放的是主键值与本行完整的记录
							  即：表中的数据都聚集在叶子节点，所以称之为聚集索引				
 
 select * from user where id=2;
 以id字段创建的主键索引，只能加速查询以id字段的查询，但是查询以name的查询是不会加速的
 

非聚集索引

非聚集索引，非聚簇索引，辅助索引、二级索引:（一张表可以创建多个辅助索引）
        以非主键字段值为key构建的B+树，该B+树的叶子节点存放的是key与其对应的主键字段值
create index yyy on user(name);
select * from user where name="xxx";								                           
		数据即索引，索引即数据。						
# ps：一张innodb存储引擎表中必须要有且只能由一个聚集索引，但是可以多个辅助索引 

利用非聚集索引查找数据

九 覆盖了索引、回表操作

命中了辅助索引，然后要找的字段值不存在与辅助索引的叶子节点上，则需要根据拿到的主键值再去聚集索引中查询一遍，然后再聚集索引的叶子节点找到你想要的内容，这就叫回表操作

命中了某棵索引树，然后在其叶子节点就找到了你想要的值，即不需要回表操作，就是覆盖了索引

覆盖了索引:在命中索引的基础上，只在本索引树的叶子节点就找到了我们想要的数据
主键索引-》id字段
辅助索引-》name字段
	
	select id,name from user where name="yang";
	select * from user where id=3;
回表操作：在命中辅助索引的基础上，在辅助索引的叶子节点并没有找到想要的数据，需要拿着对应的主键字段值去聚集索引里再找一下
主键索引-》id字段
辅助索引-》name字段

	select name，age，gender from user where name="yang";
	
# 能用主键字段当索引就不要用其他字段当索引

十、索引管理

#1. 索引的功能就是加速查找
#2. mysql中的primary key，unique，联合唯一也都是索引，这些索引除了加速查找以外，还有约束的功能

常用的索引分类

#===========B+树索引（innodb存储引擎默认）

聚集索引：即主键索引，PRIMARY KEY
    用途：
        1、加速查找
        2、约束（不为空、不能重复）


唯一索引：UNIQUE
    用途：
        1、加速查找
        2、约束（不能重复） 

普通索引INDEX：
    用途：
        1、加速查找

联合索引： 
    PRIMARY KEY(id,name):联合主键索引 
    UNIQUE(id,name):联合唯一索引 
    INDEX(id,name):联合普通索引
    
    
    
    #案例
  #创建主键索引
  create table t1(
  id int,
  class_name varchar(10),
  name varchar(16),
  age int
  ); 

#创建主键索引
alter table t1 add primary key t1(id);

#删除主键索引
alter table t1 drop primary key;

#创建唯一索引
alter table t1 add unique key t1(class_name);

#删除唯一索引（删普通索引一样）
alter table t1 drop index t1;

#创建普通索引
create index yang on t1(name);

#删除普通索引
drop index yang on t1;

十一、测试索引

#1. 准备表
create table s1(
id int,
name varchar(20),
gender char(6),
email varchar(50)
);

#2. 创建存储过程，实现批量插入记录
delimiter $$ #声明存储过程的结束符号为$$
create procedure auto_insert1()
BEGIN
    declare i int default 1;
    while(i<3000000)do
        insert into s1 values(i,'egon','male',concat('egon',i,'@oldboy'));
        set i=i+1;
    end while;
END$$ #$$结束
delimiter ; #重新声明分号为结束符号

#3. 查看存储过程
show create procedure auto_insert1\G 

#4. 调用存储过程
call auto_insert1();
Query OK, 1 row affected (16 min 22.70 sec)  #  耗费时间16分钟
# 300w条记录-------ibd文件大概200M空间
-rw-rw---- 1 mysql mysql 8.5K Jul 13 19:12 s1.frm
-rw-rw---- 1 mysql mysql 196M Jul 13 19:29 s1.ibd

mysql> select * from s1 where id=22;
1 row in set (1.52 sec)  #查询速度明显变慢

#创建普通索引（为了实验）
mysql> create index yang on s1(id);
Query OK, 0 rows affected (9.01 sec) 

#注：
#会慢会占用ibd文件50M左右会出现效率问题  所以建索引要在创建表的时候建索引


#加入索引明显变快
mysql> select * from s1 where id=22;
1 row in set (0.00 sec)


#统计名字条数为啥不快（没有命中索引）
mysql> select count(id) from s1 where name="egon";
+-----------+
| count(id) |
+-----------+
|   2999999 |
+-----------+
1 row in set (1.53 sec)

#查看执行计划
mysql> explain select count(id) from s1 where id=23;


#删除索引
mysql> drop index yang on s1;
删除索引ibd文件的索引结构不会删除  mysql的机制有待优化

区分度很低的字段加了索引也没用比如性别


####################################################################################################

如何正确使用索引：
1、以什么字段的值为基础构建索引
最好是不为空、唯一、占用空间小的字段


2、针对全表查询语句如何优化？
应用场景：用户想要浏览所有的商品信息
select count(id) from s1;

如何优化：
开发层面分页查找，用户每次看现从数据中拿


3、针对等值查询如何优化？
以重复度低字段为基础创建索引加速效果明显
#查看执行计划
explain select count(id) from s1 where id = 33;
#创建索引
create index xxx on s1(id);
select count(id) from s1 where id = 33;


以重复度高字段为基础创建索引加速效果明显
create index yyy on s1(name);
select count(id) from s1 where name="egon"; -- 速度极慢
select count(id) from s1 where name!="egon";  -- 速度快（没有egon所以速度快）
select count(id) from s1 where name="mm";  -- 速度快（没有egon所以速度快）


以占用空间大字段为基础创建索引加速效果不明显

#  总结：给重复低、且占用空间小的字段值为基础构建索引


4、关于范围查询  
select count(id) from s1 where id=33;
select count(id) from s1 where id>33;
select count(id) from s1 where id>33 and id < 36;
select count(id) from s1 where id>33 and id < 1000000;
select count(id) from s1 where id between 33 and 50;  #大于等于33 小于等于50
# （like模糊查询也是范围查询的一种）
like匹配字符中有% 但是处于末尾，速度快
like匹配字符中有% 但是处于开头，速度慢
mysql> select count(id) from s1 where email like "egon3%";


# 总结：innodb存储能加速范围查询，但是查询范围不能特别大，大了查询速度慢

5、关于条件字段参与运算
select count(id) from s1 where id*12 = 10000;
select count(id) from s1 where id = 10000/12;
select count(id) from s1 where func(id) = 10000/12;

# 总结：不要让条件字段参与运算，或者说传递给函数

十二、联合索引与最左前缀匹配原则

我的查询条件当中涉及多个子段值，这多个字段值有一个共性，都会带着某一个字段，那我就可以建联合索引。可以包含多个字段值，比如有id name age 查询能够命中id 这就叫最左前缀匹配原则，需要注意的问题：重复度低且占用空间娇小的字段应该尽量往左放，让其成为最左前缀

#创建联合索引
create index zz on t1(id,name,age);

查询条件中出现
id name age
id name
id age
id
# 每次匹配从最左边开始匹配，比大小，缩小范围
#跟字符串比大小一样，从左往右比(最左前缀匹配原则)，第一个比出胜负，第二个就不用比了


#例：
#删除索引
mysql> drop index yang on s1;
#创建联合索引
mysql> create index ting on s1(email,name,gender);

#email最优字段放哪无所谓基于索引下推技术会找到email（最左前缀匹配原则)
select count(id) from  s1 where name="egon" and email="egon@oldbooy" and gender="male"; 

十三、索引下推技术

mysql架构：一条sql语句连接池进来以后先sql接口解析语法再交给优化器，优化器列出各种各样的优化计划，选取最优的方案，然后再到缓存，再到存储引擎层，再到文件系统层

索引下推技术就是mysql优化器一种机制(把可能的优化方案罗列出来，找到最优的方案)

#1、and与or的逻辑
    条件1 and 条件2:所有条件都成立才算成立，但凡要有一个条件不成立则最终结果不成立
    条件1 or 条件2:只要有一个条件成立则最终结果就成立



#创建索引
mysql> create index yang on s1(email);
mysql> desc s1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | YES  |     | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       | 
| gender | char(6)     | YES  |     | NULL    |       |
| email  | varchar(50) | YES  | MUL | NULL    |       |
+--------+-------------+------+-----+---------+-------+

mysql> select count(id) from  s1 where name="egon" and email="egon@oldbooy" and gender="male";   
#2、and的工作原理：对于连续多个and的条件，mysql的优化器会分析出多条执行方案，选取最优的一种，即先找到某一个条件把范围缩小,加速查询


#3、or的工作原理:对于连续多个or：mysql会按照条件的顺序，从左到右依次判断，即a->b->c->d
mysql> select count(id) from  s1 where name="egon" or email="egon@oldbooy" or gender="male";   

​

B+树的四大特点及索引回顾

1、非叶子节点只放key值，只有叶子节点才存放key以及对应的value----》非叶子节点能存放key的个数变多，衍生出的指针越多，树会变得更矮更胖，io效率进一步提升

2、叶子节点彼此之间有双向链表--->范围查询速度快
select * from user where id > 3;
先找到id=4所在的叶子节点，然后只需要根据
叶子节点的链表指向向右查找下一个叶子节点（id=5、id=6...）即可,不需要再回到根节点查找

3、叶子节点内的key值是单向链表，叶子节点与叶子节点之间是双向链表，即全都排好序了-------》排序也很快

4、一个页、一个磁盘块、一个节点固定大小16k，可以存放的数据量很多



聚集索引、聚簇索引、主键索引
1、一张表中必须有且只有一个
2、聚集索引叶子节点key所对应的value值是整行完整内容



辅助索引、二级索引、非聚簇索引
1、一张表中可以有多个
2、辅助索引叶子节点key所对应的value值是该所对应的主键字段值


覆盖索引
在命中了索引的前提下，在本索引树的叶子节点就找到了自己想要的全部数据


id----->主键索引
select * from user where id =3;
name--->辅助索引（需要回表）
select name,id from user where name="egon";


回表操作
在命中了辅助索引的前提下，在叶子节点没有找到想要的完整数据，需要拿着对应的主键字段值去聚集索引里再查询一次


联合索引
create index xxx on user(name,age,gender);

最左前缀匹配原则
name age gender
name gender
name age
name