索引详解-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_42748009/article/details/115288500

1.什么是索引？

在这里插入图片描述
1.索引是一种能提高数据库查询效率的数据结构。它可以比作一本字典的目录，可以帮你快速找到对应的记录。
2.索引一般存储在磁盘的文件中，它是占用物理空间的。
3.适当的索引能提高查询效率，过多的索引会影响数据库表的插入和更新功能。

2.索引类型

在这里插入图片描述

数据结构维度

B+树索引：所有数据存储在叶子节点，复杂度为O(logn)，适合范围查询。
哈希索引: 适合等值查询，检索效率高，一次到位。
全文索引：MyISAM和InnoDB中都支持使用全文索引，一般在文本类型char,text,varchar类型上创建。
R-Tree索引: 用来对GIS数据类型创建SPATIAL索引

物理存储维度

聚集索引：聚集索引就是以主键创建的索引，在叶子节点存储的是表中的数据。
非聚集索引：非聚集索引就是以非主键创建的索引，在叶子节点存储的是主键和索引列。

逻辑维度

主键索引：一种特殊的唯一索引，不允许有空值。
普通索引：MySQL中基本索引类型，允许空值和重复值。
联合索引：多个字段创建的索引，使用时遵循最左前缀原则。
唯一索引：索引列中的值必须是唯一的，但是允许为空值。
空间索引：MySQL5.7之后支持空间索引，在空间索引这方面遵循OpenGIS几何数据模型规则。

3.为什么选择B+树作为索引结构

问题：
查询是否够快
效率是否稳定
存储数据多少
以及查找磁盘次数等等。
为什么不是哈希结构？为什么不是二叉树，为什么不是平衡二叉树，为什么不是B树，而偏偏是B+树呢？

分析：
在写业务SQL查询时，大多数情况下，都是范围查询的，如下SQL：

select * from employee where age between 18 and 28;

为什么不使用哈希结构？

哈希结构，类似k-v结构，也就是，key和value是一对一关系。
它用于「等值查询」还可以，但是范围查询它是无能为力

为什么不是二叉树？

二叉树，特点如下：

每个结点最多两个子树，分别称为左子树和右子树。
左子节点的值小于当前节点的值，当前节点值小于右子节点值
顶端的节点称为根节点，没有子节点的节点值称为叶子节点。

二叉树有多种结构，如果二叉树特殊化为一个链表，相当于全表扫描。因此，一般二叉树不适合作为索引结构。

为什么不使用平衡二叉树呢？

平衡二叉树特点：它也是一颗二叉查找树，任何节点的两个子树高度最大差为1。所以就不会出现特殊化一个链表的情况啦

但是存在问题：

平衡二叉树插入或者更新时，需要左旋右旋维持平衡，维护代价大
如果数量多的话，树的高度会很高。因为数据是存在磁盘的，以它作为索引结构，每次从磁盘读取一个节点，操作IO的次数就多啦。

为什么不使用B树呢？

数据量大的话，平衡二叉树的高度会很高，会增加IO嘛。
那为什么不选择同样数据量，「高度更矮的B树」呢？

在这里插入图片描述
B树相对于平衡二叉树，就可以存储更多的数据，高度更低。
但是最后为甚选择B+树呢？因为B+树是B树的升级版

B+树
B+树非叶子节点上是不存储数据的，仅存储键值，而B树节点中不仅存储键值，也会存储数据。innodb中页的默认大小是16KB，如果不存储数据，那么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树就会更矮更胖，如此一来我们查找数据进行磁盘的IO次数有会再次减少，数据查询的效率也会更快。
B+树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的，链表连着的。那么B+树使得范围查找，排序查找，分组查找以及去重查找变得异常简单。

4.B+树索引搜索过程

# 建表和插入：
CREATE TABLE `student` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  `sex` int(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into student values(100,'小伦',43,'2021-01-20','0');
insert into student values(200,'俊杰',48,'2021-01-21','0');
insert into student values(300,'紫琪',36,'2020-01-21','1');
insert into student values(400,'立红',32,'2020-01-21','0');
insert into student values(500,'易迅',37,'2020-01-21','1');
insert into student values(600,'小军',49,'2021-01-21','0');
insert into student values(700,'小燕',28,'2021-01-21','1');

执行以下的查询SQL，需要执行几次的树搜索操作？可以画下对应的索引结构图

select * from student where age=32;

看下图：
在这里插入图片描述

这条 SQL 查询语句执行大概流程是：

搜索idx_age索引树，将磁盘块1加载到内存，由于32<37,搜索左路分支，到磁盘寻址磁盘块2。
将磁盘块2加载到内存中，在内存继续遍历，找到age=32的记录，取得id = 400.
拿到id=400后，回到id主键索引树（在idx_age二级索引树找到主键id后，回到id主键索引搜索的过程,就称为回表）。
搜索id主键索引树，将磁盘块1加载内存，在内存遍历，找到了400，但是B+树索引非叶子节点是不保存数据的。索引会继续搜索400的右分支，到磁盘寻址磁盘块3.
将磁盘块3加载内存，在内存遍历，找到id=400的记录，拿到R4这一行的数据。

5.覆盖索引

覆盖索引：在查询的数据列里面，不需要回表去查，直接从索引列就能取到想要的结果。换句话说，你SQL用到的索引列数据，覆盖了查询结果的列，就算上覆盖索引了。

如果不用select *, 而是使用select id,age，上面的题目执行了几次树搜索操作呢？
在这里插入图片描述
回到idx_age索引树，你可以发现查询选项id和age都在叶子节点上了。因此，可以直接提供查询结果啦，根本就不需要再回表了

6.索引失效

如果给name字段加上普通索引，然后用个like模糊搜索，那会执行多少次查询呢？
SQL如下：

select * from student where name like '%杰伦%';

like 模糊搜索，会导致不走索引的
因此，这条SQL最后就全表扫描

这几种操作都可能会导致索引失效，如下：

查询条件包含or，可能导致索引失效
如何字段类型是字符串，where时一定用引号括起来，否则索引失效
like通配符可能导致索引失效。
联合索引，查询时的条件列不是联合索引中的第一个列，索引失效。
在索引列上使用mysql的内置函数，索引失效。
对索引列运算（如，+、-、*、/），索引失效。
索引字段上使用（！= 或者 < >，not in）时，可能会导致索引失效。
索引字段上使用is null， is not null，可能导致索引失效。
左连接查询或者右连接查询查询关联的字段编码格式不一样，可能导致索引失效。
mysql估计使用全表扫描要比使用索引快,则不使用索引。

7.联合索引之最左前缀原则

如果给name,age字段加上联合索引索引，以下SQL执行多少次树搜索呢？

select * from student where name like '小%' order by age desc;

在这里插入图片描述
联合索引项是先按姓名name从小到大排序，如果名字name相同，则按年龄age从小到大排序。
SQL的like '小%'是可以用上idx_name_age联合索引的。

该查询会沿着idx_name_age索引树，找到第一个字是小的索引值，因此依次找到小军、小伦、小燕、，分别拿到Id=600、100、700，然后回三次表，去找对应的记录。这里面的最左前缀小，就是字符串索引的最左M个字符。实际上，这个最左前缀可以是联合索引的最左N个字段。比如组合索引（a,b,c）可以相当于建了（a），（a,b）,(a,b,c)三个索引，大大提高了索引复用能力。
最左前缀也可以是字符串索引的最左M个字符。

8.添加索引

如果一张表数据量级是千万级别以上的，那么给这张表添加索引，需要怎么做呢？

注意：需要知道一点，给表添加索引的时候，是会对表加锁的。如果不谨慎操作，有可能出现生产事故的。

可以参考以下方法：

先创建一张跟原表A数据结构相同的新表B。
在新表B添加需要加上的新索引。
把原表A数据导到新表B
rename新表B为原表的表名A，原表A换别的表名；

9.聚簇索引和非聚集索引

聚簇索引

定义：数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。

数据行的物理顺序与列值的顺序相同，如果我们查询id比较靠后的数据，那么这行数据的地址在磁盘中的物理地址也会比较靠后。而且由于物理排列方式与聚集索引的顺序相同，所以也就只能建立一个聚集索引了。

如果不创建索引，系统会自动创建一个隐含列作为表的聚集索引。
创建表的时候指定主键（注意：SQL Sever默认主键为聚集索引，也可以指定为非聚集索引，而MySQL里主键就是聚集索引）

非聚集索引

定义：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。

其实按照定义，除了聚集索引以外的索引都是非聚集索引，细分一下非聚集索引，分成普通索引，唯一索引，全文索引。

区别

聚集索引与非聚集索引的区别是：叶节点是否存放一整行记录

InnoDB 主键使用的是聚簇索引，MyISAM 不管是主键索引，还是二级索引使用的都是非聚簇索引。

在这里插入图片描述

对于聚簇索引表来说（左图），表数据是和主键一起存储的，主键索引的叶结点存储行数据(包含了主键值)，二级索引的叶结点存储行的主键值。使用的是B+树作为索引的存储结构，非叶子节点都是索引关键字，但非叶子节点中的关键字中不存储对应记录的具体内容或内容地址。叶子节点上的数据是主键与具体记录(数据内容)。

对于非聚簇索引表来说（右图），表数据和索引是分成两部分存储的，主键索引和二级索引存储上没有任何区别。使用的是B+树作为索引的存储结构，所有的节点都是索引，叶子节点存储的是索引+索引对应的记录的数据。

非聚集索引的二次查询问题：非聚集索引叶节点仍然是索引节点，只是有一个指针指向对应的数据块，此如果使用非聚集索引查询，而查询列中包含了其他该索引没有覆盖的列，那么他还要进行第二次的查询，查询节点上对应的数据行的数据。

聚簇索引的优点

需要取一定范围内的数据时，用聚簇索引也比用非聚簇索引好。
当通过聚簇索引查找目标数据时理论上比非聚簇索引要快，因为非聚簇索引定位到对应主键时还要多一次目标记录寻址,即多一次I/O。（索引的叶子节点就是对应的数据节点，可以直接获取到对应的全部列的数据，而非聚集索引在索引没有覆盖到对应的列的时候需要进行二次查询）
使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

聚簇索引的缺点

插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键。
更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。
二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。二级索引的叶节点存储的是主键值，而不是行指针（非聚簇索引存储的是指针或者说是地址），这是为了减少当出现行移动或数据页分裂时二级索引的维护工作，但会让二级索引占用更多的空间。
采用聚簇索引插入新值比采用非聚簇索引插入新值的速度要慢很多，因为插入要保证主键不能重复，判断主键不能重复，采用的方式在不同的索引下面会有很大的性能差距，聚簇索引遍历所有的叶子节点，非聚簇索引也判断所有的叶子节点，但是聚簇索引的叶子节点除了带有主键还有记录值，记录的大小往往比主键要大的多。这样就会导致聚簇索引在判定新记录携带的主键是否重复时进行昂贵的I/O代价。

10.索引的数据结构

目前大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为索引结构。

B-Tree

B-Tree：是一种多路搜索树。

B-tree中，每个结点包含：

本结点所含关键字的个数；
指向父结点的指针；
关键字；
指向子结点的指针；

B-树的特性：

关键字集合分布在整颗树中；
任何一个关键字出现且只出现在一个结点中；
搜索有可能在非叶子结点结束；
其搜索性能等价于在关键字全集内做一次二分查找；
自动层次控制；

B-树的搜索，从根结点开始，对结点内的关键字（有序）序列进行二分查找，如果命中则结束，否则进入查询关键字所属范围的儿子结点；重复，直到所对应的儿子指针为空，或已经是叶子结点；

B+Tree

B-Tree有许多变种，其中最常见的是B+Tree，例如MySQL就普遍使用B+Tree实现其索引结构。

与B-Tree相比，B+Tree有以下不同点：

非叶子结点的子树指针与关键字个数相同；
非叶子结点的子树指针P[i]，指向关键字值属于[K[i], K[i+1])的子树（B-树是开区间）；
为所有叶子结点增加一个链指针；
所有关键字都在叶子结点出现；
内节点不存储data，只存储key

B+的特性

所有关键字都出现在叶子结点的链表中（稠密索引），且链表中的关键字恰好是有序的；
不可能在非叶子结点命中；
非叶子结点相当于是叶子结点的索引（稀疏索引），叶子结点相当于是存储（关键字）数据的数据层；
更适合文件索引系统；

一般在数据库系统或者文件系统中，并不是直接使用B+Tree作为索引数据结构的，而是在B+Tree的基础上做了优化，增加了顺序访问指针。提升了区间查询的性能。

在这里插入图片描述

在B+Tree的每个叶子节点增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的B+Tree。例如要查询18到30之间的数据记录，只要先找到18，然后顺着顺序访问指针就可以访问到所有的数据节点。这样就提升了区间查询的性能。数据库的索引全扫描 index和索引范围扫描 range 就是基于此实现的。