MySQL 索引底层数据结构

最新推荐文章于 2024-09-17 13:42:29 发布

xsd9527

最新推荐文章于 2024-09-17 13:42:29 发布

阅读量1.1k

点赞数 14

分类专栏： MySQL 文章标签：数据库 mysql mysql索引 b+树

本文链接：https://blog.csdn.net/qq_42909545/article/details/136982926

版权

MySQL 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

MySQL 索引概述

MySQL 索引是一种数据结构，用于加快数据库查询的速度和性能。MySQL 索引的建立对 MySQL 的高效运行是很重要的，如果表中的数据数据量很大，并且需要经常读取，那么就要花时间去建立优秀的索引，或者优化使用的查询语句。

索引的种类

一般从以下三个角度来划分索引：

逻辑功能	实现	作用字段个数
普通索引、唯一索引、主键索引、全文索引	聚簇索引、非聚簇索引	单列索引、联合索引

按逻辑功能划分

普通索引

普通索引是 MySQL 中最基本的索引类型之一，它的目的只是只是加快对表中数据的查询，提高查询效率。

下面用一个例子演示一下：
user 表的结构


CREATE TABLE users (
  id INT(11) NOT NULL AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL,
  password VARCHAR(255) NOT NULL,
  PRIMARY KEY (id)
);

通过以下的语句为 username 列创建普通索引：

ALTER TABLE users ADD INDEX idx_username (username);

这条语句为username列创建一个名为idx_username的索引。

唯一索引

在创建索引的时候添加关键字unique可以创建唯一索引，唯一索引的列要求列内的数据值在全表必须是唯一的，可以为空。

以上面users表为例子，创建唯一索引的语句如下：
ALTER TABLE users ADD UNIQUE INDEX idx_username (username);
这条语句会为username列创建一个名为idx_username的唯一索引，创建完唯一索引后，必须保证值的唯一性，否则会报错。

主键索引

主键索引用于标识每个表中唯一行的索引，主键索引要求主键列中的每个值都唯一且不为空。
在上面创建的users表中，主键列是 id，主键列在表中唯一，也就是说每个表只能有一个主键。

全文索引

全文索引是一种特殊索引类型，用于对文本字段进行全文搜索，全文索引可以帮助加快对文本数据的搜索速度，并支持全文搜索的高级功能，例如模糊搜索和关键词匹配

CREATE TABLE blogs(
  id INT(11) NOT NULL AUTO_INCREMENT,
  title VARCHAR(255) NOT NULL,
  content TEXT NOT NULL,
  PRIMARY KEY (id)
);

通过以下的语句为content列创建全文索引：

ALTER TABLE articles ADD FULLTEXT INDEX idx_content (content) WITH PARSER ngram;

从实现上划分

聚簇索引

聚簇索引是将表中的数据按索引的顺序存储在磁盘上，以提高数据访问的效率。聚簇索引的特点是索引和数据存储在一起，从而在数据查询时直接访问到数据无需再次去磁盘上查找数据。

非聚簇索引

与聚簇索引不同的是，非聚簇索引把索引和数据分开存储在磁盘上，因此查询时，先访问索引，再根据索引中的指针访问磁盘上的数据块，因此访问磁盘的次数比聚簇索引多。

聚簇索引与非聚簇索引的区别：

聚簇索引叶子节点存储的是行数据，而非聚簇索引叶子节点存储的聚簇索引的主键id。

聚簇索引查询效率更高，非聚簇索引需要进行回表查询，所以性能较差。

聚簇索引一般是主键索引，一张表只能有一个主键，所以一个表只能有一个聚簇索引，非聚簇索引可以有多个。

根据作用字段的个数划分

单列索引

单列索引就是指索引只包含一列的值。

组合索引

组合索引是包含多个列的值的索引，将多个列的值组合在一起作为索引键，以提高多列查询的效率。组合索引可以根据多个列的值来快速定位需要的数据，从而减少查询的开销。

覆盖索引是select查询的数据列只用从索引中就能够取得，不必读取数据行，也就是说查询列要被所建的索引覆盖，不需要回表操作就能得到所需的全部数据，相对需要回标查询的查询速度也就更快。

索引的数据结构

MySQL 索引的数据结构一般是 B+树，为什么不用哈希表、二叉搜索树或者B树呢？

先考虑哈希表，哈希表的优点是查询某一个元素的时间复杂度近似于O(1)，但如果对哈希表进行范围查找就只能遍历全表，这样对于存储数据量比较大而查询数据频繁的数据库肯定是不可取的。

二叉搜索树根节点的选取对查询的效率有很大的影响，即便选好了一个满二叉树，它的查询效率也跟树的深度有关，当数据量很大的时候，查询速度也会很慢。相比哈希表，二叉搜索树范围查询的表现也更好，但他需要从根节点进行多次遍历，查询效率也不高。

B 树是一种多叉平衡树，优点是每一层很多个分叉，每个节点都包含键和数据，按照键值大小进行排列。B树每个节点都存储数据，如果将B树存到内存会占用很大的内存空间，而且B树进行范围查找也不够快。

MySQL 之所以使用 B+树作为索引的数据结构，是因为B+树在B树的基础上进行了优化，只在叶子节点存储数据，非叶子节点存储键值，叶子节点采用链式存储结构，优化了范围查找，在进行范围查询的时候，极大的提高了查询速度，并且可以将只存储键值的B+树存储在内存中，数据存储在磁盘中，减少了内存空间的占用。

MySQL 交互

MySQL的基础存储单位是页，MySQL 在进行查询时会将一部分数据缓存到缓存池，这个动作叫预读，一次交互的加载的数据是 16KB，也就是一页的数据量是 16KB，可以根据索引存储的单个键值的大小来推算出一个节点能存放多少数据。

B树的行数据是存在每个节点上的，推算一页内能存多少个节点的数据就要看一行数据的大小，数据大，那一页上能存放的节点少，数据小了，节点就变多了，相应的树也变高了。

B+树相对B树，非叶子节点只存储键值，叶子节点存储数据，一个指针的大小是 6bit，id 如果是 integer 类型，大小是 8bit，一共是 14bit，一页是 16kb=16384bit，所以一层非叶子节点最多可以存储 16384/14=1170 个指针，假设每个叶子节点存储的一行数据是 1kb，那么一个节点就可以存储 16 行数据。所以三层的B+树可以存储 1170117016=21902400 行数据，三层的B+树能存储 2000 万的数据，仅需 3 次 IO 就能查询到，B树也是一样的，但B树的非叶子节点存储数据，所以一页的能存储的数据就更少。

MyISAM 和 InnoDB存储文件的区别

MyISAM 存储引擎的表有三个文件：

tableName.frm：表结构文件
tableName.MYD：数据文件（MyISAM Data）
table Name.MYI：索引文件（M有ISAM Index）

InnoDB 存储引擎的表有两个文件：

tableName.frm：表结构文件
tableName.ibd：索引和数据文件（InnoDB Data）

从物理文件的区别能看出聚簇索引和非聚簇索引的区别，MyISAM 没有主键索引，索引和数据物理文件是分开的，拿到地址进行回表操作去数据文件中获取行数据。

面试可能会问到的问题

为什么建议 InnoDB 表必须建主键，并且推荐使用整型的自增主键？

InnoDB 表采用 B+树作为存储结构，且索引和数据文件是存在一起的，聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个不包含有NULL值的唯一索引作为主键索引，但是如果没有这样的唯一索引，InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引(ROWID随着行记录的写入而主键递增)。

使用自增主键，每次插入新记录，记录会顺序添加到当前索引节点的后续位置，主键的顺序按照数据记录的插入顺序排列，自动有序。当一页写满，自动开辟新页。

如果使用非自增主键，由于每次插入的主键值接近随机，因此每次新纪录都要被插入到现有索引页中间的某一个位置，此时 MySQL 不得不移动数据的位置，以便于新记录的插入，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘中读取回来，增加了很多开销，影响性能。