MySQL 索引整理（一）

最新推荐文章于 2024-11-24 15:45:17 发布

艾热在coding

最新推荐文章于 2024-11-24 15:45:17 发布

阅读量139

点赞数

分类专栏：数据库文章标签： mysql 数据库面试

本文链接：https://blog.csdn.net/weixin_51889618/article/details/128689156

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

索引是提升数据库查询效率的数据结构，包括聚簇索引（数据与索引存储在一起）和非聚簇索引（索引与数据分开存储）。聚簇索引适用于主键查找和范围查询，但插入和更新主键代价高；非聚簇索引查询效率相对较低，但更新较快。Hash索引适用于等值查询，不支持范围查询和排序。文章还讨论了不同索引的数据结构和代价，如空间和时间成本。

摘要由CSDN通过智能技术生成

索引基础

索引及其优缺点

概述

索引是帮助MySQL高效获取数据的数据结构，其本质是一种数据结构，满足特定的查找算法。引入索引的目的是为了减少查找数据过程中的磁盘IO次数

优点：

降低数据库IO成本
如果创建唯一索引，可以保证数据库表中每一行数据的唯一性
可以加速表与表之间的连接，提高查询速度
在使用分组和排序时，减少查询中分组和排序的时间，降低CPU消耗

缺点

创建和维护索引需要一定的成本
索引需要占用磁盘空间，如果有大量的索引就会占用大量的磁盘空间
虽然提高了查询速度，但是降低了更新表的速度，对表数据进行增删改的时候也要动态维护索引。

<aside> 💡 在突发频繁插入的情况下，可以先删除表中的索引，等插入完数据后，再创建索引

</aside>

常见的索引概念

按照物理实现方式，可以分为两种：聚簇索引和非聚簇索引（二级索引或辅助索引）

聚簇索引

所有的用户记录都存在了叶子节点，数据即索引，索引即数据

特点
1. 使用记录主键值的大小进行记录和页的排序
  1. 页内的记录是按照主键的大小形成一个单链表
  2. 每个存放用户记录的页之间按照页中的用户记录主键的大小形成一个**双向链表。**存放目录项记录的页分为不同层次，每个层次也是个双向链表
  3. B+树的叶子节点存储完整的用户记录
优点
1. 数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快
2. 聚簇索引对于主键的排序查找和范围查找速度非常快,按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以节省了大量的io操作
缺点
1. 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键
2. 更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新, 二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据

<aside> 💡 每个表只能创建一个聚簇索引，一般在该表的主键上创建。如果没有主键，则InnoDB会选择非空的唯一索引代替。MyISAM不支持聚簇索引

</aside>

非聚簇索引

回表：根据某一列大小排序的B+树只能确定要查找记录的主键值，如果需要找完整用户记录则需要在聚簇索引再次查询，这个叫回表。

特点
1. 叶子节点存储数据位置，不存储数据记录
2. 不影响数据的物理存储顺序
3. 增删改操作效率比聚簇索引高

<aside> 💡 一个表只能有一个聚簇索引，但可以有多个非聚簇索引

</aside>

联合索引

同时以多个列的大小作为排序规则，也就是同时为多个列建立索引。以c2和c3列的大小为排序规则建立的B+树称为 联合索引 ，本质上也是一个二级索引。

索引的代价

空间代价

每建立一个索引都要为它建立一棵B+树，每一棵B+树的每一个节点都是一个数据页，一个页默认会占用 16KB 的存储空间，一棵很大的B+树由许多数据页组成，那就是很大的一片存储空间

时间代价

每次对表中的数据进行增、删、改操作时，都需要去修改各个B+树索引。而且我们讲过，B+树每层节点都是按照索引列的值从小到大的顺序排序而组成了双向链表。不论是叶子节点中的记录，还是内节点中的记录（也就是不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序而形成了一个单向链表。而增、删、改操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行一些记录移位，页面分裂、页面回收等操作来维护好节点和记录的排序。如果我们建了许多索引，每个索引对应的B+树都要进行相关的维护操作，会给性能拖后腿

如何选择索引的数据结构

Hash结构

Hash本身是一个散列函数，Hash算法是通过一些确定性的算法来计算，相同的输入永远可以得到相同的输出

可以加快查找速度的数据结构常见有两种：

树：例如AVL，平均时间复杂度都是$O(log_2N)$
Hash: 例如HashMap，时间复杂度都是$O(1)$

Hash的查找速率是非常快的，从效率上来看Hash比B+树更快

如果Hash 函数将两个不同关键字映射到相同位置，就会出现“碰撞”，一般会采用“链接法”解决，即将发生碰撞的数据放在一个链表中。

为什么不用Hash索引？

Hash索引只能满足<>,=和IN 查询，对于范围的查询，Hash索引效率比树形索引低
Hash的数据存储是没有顺序的，在ORDER BY情况下，还需要重新排序
对于联合索引，Hash无法对单独的一个键或几个索引键进行查询
如果索引列的重复值比较多的情况下，效率会降低，因为在Hash冲突时，需要遍历桶中的指针进行比较，非常耗时间，索引一般重复较多的列不会使用Hash索引

Hash索引适用的引擎有

Memory, MyISAM和InnoDB都不适用

InnoDB本身不支持Hash索引，但是提供了自适应Hash索引。一般用于对比较频繁被访问的数据，将其数据页地址放到Hash表中，可以加快查询速度。

Hash索引的适用性

在键值对型的数据库中，例如Redis中，Redis存储的核心就是Hash表

二叉搜索树

什么是二叉搜索树：Binary Search Tree, BST 是一种二叉树，其中每个结点最多有两个子结点且具有二叉搜索树性质：左子树上所有结点的值均小于它的根结点的值以及右子树上所有结点的值均大于它的根结点的值.

左子节点<本节点≤ 右子节点

对于二叉搜索树结构的索引，它的查询效率与**树的高度有关。**在极端情况下，例如全部只有右节点时会退化成链表

AVL树

为了解决BST的不足，提出平衡二叉搜索树(Balanced Binary Tree),具有以下性质：

它是一颗空树或左右两个子树的高度差的绝对值不超过1，并且左右两个子树都是一个平衡二叉树

它的查询时间复杂度为$O(log_2N)$, 每次访问一次节点就需要进行一次磁盘IO操作，和树的高度相同，所以优化的思路就是尽量减少树的高度。

如何减少树的高度？

增加叉数，即二叉树→三叉树，n叉树，变成“矮胖型”

B树

Balanced-Tree，多路平衡查找树，B树的高度远小于平衡二叉树。它是多路平衡查找树，每个节点最多可以有m个子节点，被称为M阶。在MySQL索引中，每个磁盘块包含关键字和子节点的指针

特性

根节点的孩子数范围是[2, M]
每个中间节点包含k-1个关键字和k个孩子，孩子的数量=关键字数量+1，k的取值范围：[ceil(M/2),M]
叶子节点有k-1个关键字
假设中间节点的关键字为k1,k2,k3…k-1，且关键字按升序排序，此时相当于划分了k个范围，对应k个指针：p1,p2,p3,…pk,其中p1指向关键字小于k1的子树，其余同理
所有叶子节点位于同一层