MySql学习之索引知识点总结

最新推荐文章于 2024-11-15 16:27:37 发布

南波兔不写巴哥

最新推荐文章于 2024-11-15 16:27:37 发布

阅读量230

点赞数 1

分类专栏：数据库学习文章标签： mysql 索引数据库 B+树

本文链接：https://blog.csdn.net/qq_34687559/article/details/114638472

版权

数据库学习专栏收录该内容

6 篇文章 0 订阅

订阅专栏

何谓索引

如果将数据库比作一本书，数据是书中的知识，索引就是这本书的目录，通过索引可以快速定位到数据

它是帮助mysql高效获取数据的数据结构

在没有索引时，我们如果要查询一个数据，则需要顺序查找
而拥有了索引之后，我们先查找索引，之后便能快速的定位到目标数据的位置，然后去拿，效率高了很多
例如下图
在这里插入图片描述
在没有建立索引前，我们要查询一个col2==91的行数据，需要O(N)的去遍历
而建立索引之后，我们只需要在BST中进行查找，两步就可以获得行数的指针，然后获得数据。这里的索引位BST，将时间复杂度降到了O(logN)

索引的优缺点：

实现数据的快速查询
索引本身也是数据，需要空间进行存储
在进行update，insert，delete时，需要对索引进行维护，是一笔开销

索引的类型

常见的索引类型有两种

哈希索引
B+树索引

哈希索引：对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择BTree索引。

B+树索引：这里我们稍微展开讲讲
B+树索引的底层是B+树（多路平衡搜索树），它是B树的升级。
下面先来说说B树

BST作为搜索树，一次只能有两个分叉，而B树可以有M个分叉。则此时，树的高度就降低了很多，查找的效率也就变成了O(logmN)，提高了很多。
关于B+树，这里简单的提一提：

树中的每个节点最多包含m个孩子
除根节点外，每个节点至少哟cell(m/2)个孩子
若根节点不是叶子节点，则至少有两个孩子
所有的叶子节点都在同一层
每个非叶子节点由n个key与n+1个指针组成（很好理解，因为每个key都有一个指向大于等于的指针，和小于的指针），其中cell(m/2 -1)<= n <= m-1

在这里插入图片描述

以上是B树的插入操作，
当一个节点的key值超过了m-1，则会分裂，中间值去到上一层，而左右两边分为两个节点。此时如果上面也超了，也会分裂。
总体插入分裂的逻辑是向上的。
（不是一定超过m-1就会分裂的，可能会发生旋转：左右叶子节点如果还有空，会移动一下当前节点的key，从而完成插入操作，同时避免分裂，不过上层的key需要更新替换）
对于其删除，其实也是差不多的，这里涉及到了合并操作。
会有一个填充因子。一般最小为50%，当一个节点的key值低于填充因子，就会合并。

B+树：
B+树是BTree索引中的底层结构，它对B树进行了优化。
它们最大的区别在于：
B+树的叶子节点之间是通过双向的指针相连。于是便可以通过B+树来实现范围访问。
在这里插入图片描述
B+树与B树的区别：
B+树可以理解为：B树+索引顺序访问方法

B 树的所有节点既存放键(key) 也存放数据(data);而 B+树只有叶子节点存放 key 和 data，其他内节点只存放
key。
B 树的叶子节点都是独立的;B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。

哈希索引与BTree索引：

单条查询时，哈希肯定是最快的，毋庸置疑，O(1)优于O(logmN)
顺序查询和范围查询时，BTree快很多，例如SELECT * FROM tb1 WHERE id < 500;，哈希索引只能1-499来分别算一下，而BTree则可以通过页节点的指针实现顺序查找，在此种环境下特别的快

（也有B树索引）

B+树索引分类

B+树索引可以分为聚集索引和辅助索引（非聚集索引）
二者的主要区别在于叶子节点存放的是否是行数据
聚集索引存放的是数据
而辅助索引的叶子节点存放的是主键

聚集索引：
也称聚簇索引
聚集索引即索引结构和数据一起存放的索引。主键索引属于聚集索引。
对于 InnoDB 引擎表来说，该表的索引(B+树)的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据。
（在Mysql中，非叶子节点是IndexPage，叶子节点是LeafPage）
（主键索引：数据表的主键列使用的就是主键索引。在 mysql 的 InnoDB 的表中，当没有显示的指定表的主键时，InnoDB 会自动先检查表中是否有唯一索引的字段，如果有，则选择该字段为默认的主键，否则 InnoDB 将会自动创建一个 6Byte 的自增主键。即，一张表一定会有且仅有一个主键索引）

在聚集索引中，定位叶子节点是十分快的，同时，定位到叶子节点，便是定位到了数据

辅助索引：
非聚集索引即索引结构和数据分开存放的索引。（叶子节点中不存储索引对应的数据，一般存储主键）
二级索引属于非聚集索引。
（二级索引：二级索引又称为辅助索引，是因为二级索引的叶子节点存储的数据是主键。也就是说，通过二级索引，可以定位主键的位置。唯一索引、普通索引等都是二级索引）
（二级索引和主键索引都可以看做是索引的类型之一）

所以，实际在我们的Mysql中查询时，如果查询条件是非主键的字段（列），那么查询的逻辑是，先看有没有对应的辅助索引，如果有查询，找到符合条件的主键，再通过聚集索引来查询主键对应的数据（该过程叫回表）
如果没有对应的辅助索引，则全文遍历查询。

索引的创建

1.添加PRIMARY KEY（主键索引）

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

2.添加UNIQUE(唯一索引)

ALTER TABLE `table_name` ADD UNIQUE ( `column` )

3.添加INDEX(普通索引)

ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

4.添加FULLTEXT(全文索引)

ALTER TABLE `table_name` ADD FULLTEXT ( `column`)

5.添加多列索引

ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

覆盖索引

就是索引本身就已经包含了所要查询的列。
则此时就不用进行回表，直接返回就好
哈希索引、空间索引等索引都不存储索引列的值，所以mysql只能使用B-TREE来做覆盖索引
这里Extra，如果using idnex，就是覆盖索引

如果是Using idnex condition，就是二级检索

如果type是ALL，则是全表查询
主键是不需要加入聚集索引的，因为它本身就在索引的data中
这也引出一个问题，非聚集索引不一定回表（如果是覆盖索引，或者是主键）

索引分类

单值索引：（单列索引）按照单列，一个表可以有多个
唯一索引：索引值必须唯一，允许有空值
复合（联合）索引：多个列一起构成索引

最左前缀原则

对于联合索引，如果查询的时候查询条件精确匹配索引的左边连续一列或几列，则此列就可以被用到。
这里需要注意的是，查询的时候如果两个条件都用上了，但是顺序不同，那么现在的查询引擎会自动优化为匹配联合索引的顺序，这样是能够命中索引的。
举个例子：
如User表的name和city加联合索引就是(name,city)

	select * from user where name=xx and city=xx ; ／／可以命中索引
	select * from user where name=xx ; // 可以命中索引
	select * from user where city=xx ; // 无法命中索引

索引下推

如果不符合最左前缀原则呢，怎么办?如where name like ‘张%’ and age=10 and ismale= 1.对于(name,age)这个索引，只能用“张"找到第一个满足条件的记录，然后一个一个回表查询判断其他条件
MySQL5.6之后引入索引下推优化，可以在索引|遍历过程中，对索引|中包
含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。比如
直接在索引(name,age) 的时候就判断age是不是等于10,不等于就直
接跳过,减少回表次数。