Mysql——索引

大西洲_

已于 2024-08-11 15:34:27 修改

阅读量584

点赞数 14

分类专栏： MySQL 文章标签： mysql 数据库 java sql

于 2024-08-11 15:28:38 首次发布

本文链接：https://blog.csdn.net/weixin_43891057/article/details/141065898

版权

MySQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

一、什么是索引

索引是一种数据结构，可以帮助mysql高效获取数据，如同字典目录帮我们快速定位数据。

优点：

提高了数据检索的效率
降低了磁盘IO的成本
索引会对数据进行排序降低了排序成本

缺点：

索引会占用磁盘空间
索引提高了查询速度，但是增删改操作同时还需要更新索引文件速度降低。

二、InnoDB引擎的逻辑结构和物理结构

在介绍索引的分类及结构之前我们先了解一下Mysql默认存储引擎的逻辑结构和物理结构

1. 存储结构

由大到小分别是：表空间>段>区>页>行

表空间：所有的数据都存储在表空间中。
- 共享表空间：InnoDB引擎有一个默认的共享表空间用来存储undolog信息、事务信息等。
- 独立表空间：每个表对应一个独立的表空间，用来存储数据、索引等信息。
段：多个段组成了表空间，而一个段又管理着多个区。段有三种：
- 数据段：存放B+树的叶子结点
- 索引段：存放B+树的非叶子结点
- 回滚段：存放撤销信息
区：由多个连续页组成，一个区的大小固定为1MB,一个区中可以存储64个页。
页：InnoDb磁盘管理的最小单元，每个页大小默认16K,为了保证页的连续性，每次申请4-5个区。
行：InnoDB存储引擎是按行存储。每个页最多可存储16KB/2-200行（16*1024/2-200行，即7992行记录）。

2. 物理结构

InnoDB引擎按照物理结构划分可以分为：内存结构、磁盘结构
在这里插入图片描述

内存结构：包括Buffer Pool、Change Buffer、自适应hash索引（Adaptive Hash Index）、Log Buffer
- Buffer Pool缓冲池：缓存表中数据、索引，降低磁盘IO次数。
  以页为单位存储数据，常见的管理算法是LRU算法，其中InnoDB就是采用的LRU算法并对其进行了优化。
  LRU算法又称为最近最远算法，按照5:3的比例将缓冲池分为年轻代（头部存放经常访问的数据）、老年代（尾部存放很少被使用的数据）。
- Change Buffer写缓冲
  在辅助索引发生改变时，如果辅助索引在buffer pool里面就会直接进行修改。如果辅助索引页不在buffer pool里，则由Change Buffer先缓存这些辅助索引页的变更动作。
  change buffer既包含内存结构，也包含磁盘结构。在内存中，Change Buffer是缓冲池的一个组成部分。在磁盘上，Change Buffer是system tablespace（系统表空间）的一部分。
- 自适应哈希索引
  InnoDB存储引擎会监控对表上各索引页的查询，如果观察到建立hash索引可以提高查询速度，则自动建立hash索引
- Log Buffer
  在内存中，保存日志文件数据。日志缓冲区的内容定期刷新到磁盘。事务提交后，必须将事务对数据页的修改刷(fsync)到磁盘上，才能保证事务的ACID特性。较大的日志缓冲区使大型事务可以运行，而无需在事务提交之前将redo日志数据写入磁盘。
磁盘结构：表、索引、表空间、双写缓冲区Doublewrite Buffer、重做日志 Redo Log、回滚日志 Undo Log

二、索引的分类及结构

不同的存储引擎索引的结构也不相同，以下是几种常见的存储引擎：

1. 存储引擎

在mysql服务中负责存储数据、建立索引、操作数据。

InnoDB引擎： MySQL 5.5及之后，InnoDB设定为了 MySQL默认存储引擎。
- 支持事务：事务的完整提交和回滚
- 支持外键：保证数据的完整性
- 支持行级锁：提高了并发访问性能
MyISAM存储引擎：
- 不支持事务、外键、行锁
- 支持表锁，更新操作会锁住整张表，读和写不能同时进行
- 适用于单方面读或者写的业务，分开读写操作速度快，同时并发量不高的业务场景
MEMORY存储引擎：
- 数据存储在内存中，所以处理速度非常快，但是服务器关闭表数据就会被清空。
- 支持哈希索引

2. 索引的分类

按照数据结构划分：B+树索引、Hash索引
数据结构有很多种包括二叉树、红黑树、B树、B+树，为什么我们要选择B+树作为InnoDB引擎的索引，下面分别介绍下几种树
- 二叉查找树：二叉树的特点是每个结点有两个分支，左小右大。但是如果根结点值小很容易形成单链表，这样的索引依旧是全表扫描效率低，所以我们不考虑将二叉树作为索引结构。
- 红黑树：红黑树又叫做二叉平衡树，解决了二叉查找树一边倒的问题。但是如果插入大量数据会造成红黑树的高度过高。
  - 根结点和叶子结点都是黑色的
  - 每个结点要么是黑色要么是红色
  - 所有路径上的黑色结点数相同
- 哈希索引：采用数组+链表的方式来存储，用hash函数计算出键值，如果出现冲突在数组冲突位置上拉出一个链表，因为hash索引是根据键值来匹配的，范围查询并不支持。
- B树：多路自平衡搜索树，允许每个节点拥有多个子结点。每个结点包含：键值key（表中记录的主键）、数据、指针。InnoDB引擎是以页为磁盘管理的最小单位，页的大小为16KB，因为一个磁盘快的空间没有那么大，所以每次都会申请若干个连续的地址空间。
- B+树：数据库中最常见最频繁使用的一种索引，B+树是对B树的一种优化，因为页的空间大小是固定的，B树中每个节点不仅包含键值key，还包含数据，如果数据很大占用空间大，键值数量就会很少，那么树的层数就会增多，查询时就会增加磁盘IO影响效率。B+树优化了这一缺点，只用叶子结点来存取数据，非叶子结点只存储键值key并按照键值大小排序，增加了每个结点存储键值的数量，减少了树的层数。
按照物理存储划分：聚集索引、非聚集索引
- 聚集索引：
  - 在每个表中都必须有且只有一个聚集索引
  - 默认情况下使用主键作为聚集索引，没有主键使用唯一索引作为聚集索引，如果都没有mysql会默认生成一个隐藏的row_id作为聚簇索引
  - 聚集索引的叶子结点保存了一行的数据
- 非聚集索引（二级索引）：
  - 一个表中可以有多个二级索引，一般对非主键列建立的索引就是二级索引
  - 非聚集索引叶子结点保存的是主键
  - 二级索引在查找时会涉及到回表查询
按照字段特性分类：主键、唯一、普通、前缀
- 主键索引：创建主键时系统自动创建的索引。
- 唯一索引：索引列中的值必须是唯一的，如果是组合索引，组合列的值必须是唯一的。
- 普通索引：无约束
- 前缀索引：当字段类型为字符串时，有时候需要索引很长的字符串，会让索引变得很大，查询时浪费大量的磁盘io，影响查询效率。此时可以取索引前缀，截取部分字符串作为索引。
按照字段数量分类：单列索引、联合索引
- 单列索引：建立在单个列上的索引为单列索引，单列索引有：普通索引，唯一索引，主键索引
- 联合索引：一个索引包含多个列（联合索引的使用要遵循最左前缀法则）