【MYSQL索引详解】

码农最酷

已于 2022-11-08 10:33:09 修改

阅读量223

点赞数 1

文章标签： mysql 数据库

于 2022-11-08 10:28:37 首次发布

本文链接：https://blog.csdn.net/fangchen1007/article/details/127742069

版权

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

文章目录

前言
一、MySQL架构介绍
- - 1、MySQL体系介绍
  - 2、SQL运行过程
二、MySQL索引
三、其他注意事项

前言

本章内容主要从mysql架构设计介绍，讲到mysql索引的结构设计，主要分析mysql索引在数据结构方面选择，以及原理分析。

一、MySQL架构介绍

1、MySQL体系介绍

网络连接层：提供与Mysql服务器建立连接的支持
核心服务层：主要包含系统管理和控制工具、连接池、SQL接口、解析器、查询优化器、执行期和缓存等几个部分
存储引擎：负责Mysql中数据的存储与提取，与底层系统文件进行交互
文件系统：负责将数据库的数据和日志存储在文件系统之上，并完成与存储引擎的交互，是文件的物理存储层

2、SQL运行过程

建立连接->从缓存中获取->解析器对SQL进行解析->优化器调优->执行器->存储引擎获取数据

二、MySQL索引

1、MySQL 索引是什么

索引是一种特殊的数据库结构，由数据表中的一列或多列组合而成，可以用来快速查询数据表中有某一特定值的记录。通过索引，查询数据时不用读完记录的所有信息，而只是查询索引列即可

2、MySQL 索引的存储类型有哪些

MySQL中索引的存储类型有两种，即 BTree 和 Hash。BTree又可以分为BTree和B+Tree，在innodb和myisam引擎中使用的是B+Tree，而mermory引擎使用的hash

3、MySQL 索引在哪里实现的

索引是在存储引擎中实现的

4、索引的优缺点有哪些

优点：

提高数据的查询的效率（类似于书的目录）
可以保证数据库表中每一行数据的唯一性（唯一索引）
减少分组和排序的时间（使用分组和排序子句进行数据查询）,被索引的列会自动进行分组和排序

缺点：

占用磁盘空间
降低更新/插入插入表的效率（不仅要更新表中的数据，还要更新/插入相对应的索引文件）

5、MySQL索引的数据结构

MySQL 索引的数据结构可以分为 BTree 和 Hash 两种，BTree 又可分为 BTree 和 B+Tree。BTree 是一棵多叉查找树，之所以选择这种数据结构，主要是基于查询效率和大数据存储的考量。如果想要深入了解其中的原因，可以去了解下树的概念，然后去对比二叉树，平衡二叉树，红黑树各自的优劣点，就能完全理解mysql索引最终选择B+Tree的原因。
不同的数据引擎对索引的实现存在着差异，比如innodb、MyISAM的索引结构是B+tree，而Memory的索引结构是Hash
下面来具体分析BTree和B+Tree这两者的差异：
1、查询过程对比：
都是从根节点开始查找，然后树的查找特点逐层遍历。但是主要的区别是，Btree的非叶子点是可以存储的数据的，而B+tree只有在叶子节点存储数据，非叶子节点只存储指向下一个节点的地址，这在很大程度上保证了每个节点的在同样磁盘空间下，B+tree可以存储更多数据。如果选择BTree，势必会导致存储相同的数据，树的深度会更高，从而导致查询数据的适合导致磁盘的I/O次数更多
在这里插入图片描述 2、查询性能的比较：
通过单元素查询和范围查询来比较分析。
2.1单元素查询：
按照上图比如查询数字6，不管B还是B+都会经历3次磁盘IO,看起来似乎都是一样的，但是B+树的磁盘页存放的节点比B树多，一旦数据量大了之后会明显降低磁盘IO。
B+树比B树查询更稳定：B树要查询到匹配元素（最好情况是根节点，最坏情况是叶子节点），B+树无论匹配元素在哪都必须查询到叶子节点（数据存放在叶子节点）。
2.2范围查询
在实际应用场景中，一般查询的都是多条数据，有可能会需要跨节点遍历查询
B树的范围查找需要不断的中序遍历。首先二分查找到范围下限，在不断的通过中序遍历，直到查询到范围上限，比较耗时繁琐。
B+树相比较于B树就简单许多，首先通过二分查找，找到范围下限，然后通过叶子结点的链表顺序遍历，直至范围上限，效率很高。

6、MySQL索引的实现

6.1 MyISAM 索引

特点：

MyISAM 的数据文件（.myd）和索引文件（.myi）是分开存储的
MyISAM（B+Tree）叶子节点中存储的键值为索引列的值，数据为索引所在行的磁盘地址
MyISAM 的主键索引（Primary key）和辅助索引（Secondary key）在结构上没有任何区别，只是主键索引要求键值唯一，而辅助索引键值可以重复

6.2 InnoDB 索引

数据和索引都存储在一个文件中（.ibd）
一般情况下，聚簇索引等同于主键索引；除聚簇索引外的所有索引均称为辅助索引,也成为非聚簇索引
InnoDB（B+Tree）叶子节点中存储的键值为索引列的值
- 如果是聚簇索引，数据为整行记录（除了主键值）
- 如果是辅助索引，数据为该行的主键值
每一张表都有一个聚簇索引
- 如果表中有定义主键，主键索引用作聚簇索引
- 如果表中没有定义主键，选择第一个不为 NULL 的唯一索引列用作聚簇索引
- 如果以上都没有，使用一个 6 字节长整形的隐式字段 ROWID （自增）用作聚簇索引
根据在辅助索引树中获取到的主键id，再到主键索引树查询数据的过程称为回表查询
6.3 索引详细结构
组合索引：
- 创建组合索引 – 对于大量数据的表，使用联合索引会大大的减少开销！
- 为 table 中的 name、age、gender字段创建组合索引 idx_name_age_gender：
  ALTER TABLE table_name ADD INDEX (name,age,gender);
  索引 idx_name_age_gender 相当于创建了三个索引，分别为 idx_name(单列索引)、idx_name_age(组合索引)、idx_name_age_gender(组合索引)
- 使用规则（最左匹配规则）
覆盖索引：
覆盖索引不是一种索引结构，而是一种优化手段
我们只需要查询组合索引中的字段，而不需要表中的其它字段，在这过程中不会产生回表现象，这种情况称为覆盖索引