# 1.MySQL的架构体系

![image](https://developer.qcloudimg.com/http-save/yehe-6193905/ef718a3611b3d167473ce7ee6f90072b.png)
 
# 2.MySQL的执行引擎

![image](https://developer.qcloudimg.com/http-save/yehe-6193905/dd6b6fea2dee11931a405a574169ade7.png)
 
#### 2.1.3.1.MySQL官方引擎概要

##### InnoDB存储引擎

InnoDB是MySQL的默认事务型引擎，也是最重要、使用最广泛的存储引擎。它被设计用来处理大量的短期(short-lived)事务，短期事务大部分情况是正常提交的，很少会被回滚。InnoDB的性能和自动崩溃恢复特性，使得它在非事务型存储的需求中也很流行。除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。如果要学习存储引擎，InnoDB也是一个非常好的值得花最多的时间去深入学习的对象，收益肯定比将时间平均花在每个存储引擎的学习上要高得多。所以InnoDB引擎也将是我们学习的重点。
##### MylSAM存储引擎

在MySQL 5.1及之前的版本，MyISAM是默认的存储引擎。MyISAM提供了大量的特性，包括全文索引、压缩、空间函数（GIS）等，但MyISAM不支持事务和行级锁，而且有一个毫无疑问的缺陷就是崩溃后无法安全恢复。尽管MyISAM引擎不支持事务、不支持崩溃后的安全恢复，但它绝不是一无是处的。对于只读的数据，或者表比较小、可以忍受修复（repair）操作，则依然可以继续使用MyISAM（但请不要默认使用MyISAM，而是应当默认使用InnoDB)。但是MyISAM对整张表加锁，而不是针对行。读取时会对需要读到的所有表加共享锁,写入时则对表加排他锁。MyISAM很容易因为表锁的问题导致典型的的性能问题。
##### Mrg_MylSAM

Merge存储引擎，是一组MyIsam的组合，也就是说，他将MyIsam引擎的多个表聚合起来，但是他的内部没有数据，真正的数据依然是MyIsam引擎的表中，但是可以直接进行查询、删除更新等操作。
##### Archive引擎

Archive存储引擎只支持INSERT和SELECT操作，在MySQL 5.1之前也不支持索引。Archive引擎会缓存所有的写并利用zlib对插入的行进行压缩，所以比MyISAM表的磁盘I/O更少。但是每次SELECT查询都需要执行全表扫描。所以Archive表适合日志和数据采集类应用，这类应用做数据分析时往往需要全表扫描。或者在一些需要更快速的INSERT操作的场合下也可以使用。Archive引擎不是一个事务型的引擎，而是一个针对高速插入和压缩做了优化的简单引擎。
##### Blackhole引擎

Blackhole引擎没有实现任何的存储机制，它会丢弃所有插入的数据，不做任何保存。但是服务器会记录Blackhole表的日志，所以可以用于复制数据到备库，或者只是简单地记录到日志。这种特殊的存储引擎可以在一些特殊的复制架构和日志审核时发挥作用。但这种引擎在应用方式上有很多问题，因此并不推荐。
##### CSV引擎

CSV引擎可以将普通的CSV文件(逗号分割值的文件）作为MySQL的表来处理，但这种表不支持索引。CSV引擎可以在数据库运行时拷入或者拷出文件。可以将Excel等的数据存储为CSV文件，然后复制到MySQL数据目录下，就能在MySQL 中打开使用。同样，如果将数据写入到一个CSV引擎表，其他的外部程序也能立即从表的数据文件中读取CSV格式的数据。因此CSV引擎可以作为一种数据交换的机制，非常有用。
##### Federated引擎

Federated引擎是访问其他MySQL服务器的一个代理，它会创建一个到远程MySQL服务器的客户端连接，并将查询传输到远程服务器执行，然后提取或者发送需要的数据。最初设计该存储引擎是为了和企业级数据库如Microsoft SQL Server和 Oracle的类似特性竞争的，可以说更多的是一种市场行为。尽管该引擎看起来提供了一种很好的跨服务器的灵活性，但也经常带来问题，因此默认是禁用的。
##### Memory 引擎

如果需要快速地访问数据，并且这些数据不会被修改，重启以后丢失也没有关系，那么使用Memory表(以前也叫做HEAP表）是非常有用的。Memory表至少比MyISAM 表要快一个数量级，因为每个基于MEMORY存储引擎的表实际对应一个磁盘文件。该文件的文件名与表名相同，类型为frm类型。该文件中只存储表的结构。而其数据文件，都是存储在内存中，这样有利于数据的快速处理，提高整个表的效率，不需要进行磁盘I/O。所以Memory表的结构在重启以后还会保留，但数据会丢失。
Memory表支持 Hash索引，因此查找操作非常快。虽然Memory表的速度非常快，但还是无法取代传统的基于磁盘的表。Memroy表是表级锁，因此并发写入的性能较低。它不支持BLOB或TEXT类型的列，并且每行的长度是固定的，所以即使指定了VARCHAR 列，实际存储时也会转换成CHAR，这可能导致部分内存的浪费。
##### NDB集群引擎

> 使用MySQL服务器、NDB集群存储引擎，以及分布式的、share-nothing 的、容灾的、高可用的NDB数据库的组合，被称为MySQL集群（(MySQL Cluster)。
#### 2.1.3.2.值得了解的第三方引擎

**Percona的 XtraDB存储引擎**
基于InnoDB引擎的一个改进版本，已经包含在Percona Server和 MariaDB中，它的改进点主要集中在性能、可测量性和操作灵活性方面。XtraDB可以作为InnoDB的一个完全的替代产品，甚至可以兼容地读写InnoDB的数据文件，并支持InnoDB的所有查询。
**TokuDB引擎**
使用了一种新的叫做分形树(Fractal Trees)的索引数据结构。该结构是缓存无关的，因此即使其大小超过内存性能也不会下降，也就没有内存生命周期和碎片的问题。TokuDB是一种大数据（Big Data)存储引擎，因为其拥有很高的压缩比，可以在很大的数据量上创建大量索引。现在该引擎也被Percona公司收购。
**Tips ： 分形树，是一种写优化的磁盘索引数据结构。 分形树的写操作（Insert/Update/Delete）性能比较好，同时它还能保证读操作近似于B+树的读性能。据测试结果显示， TokuDB分形树的写性能优于InnoDB的B+树，读性能略低于B+树。分形树核心思想是利用节点的MessageBuffer缓存更新操作，充分利用数据局部性原理，将随机写转换为顺序写，这样极大的提高了随机写的效率。**
**Infobright**
MySQL默认是面向行的，每一行的数据是一起存储的，服务器的查询也是以行为单位处理的。而在大数据量处理时，面向列的方式可能效率更高，比如HBASE就是面向列存储的。
Infobright是最有名的面向列的存储引擎。在非常大的数据量（数十TB)时，该引擎工作良好。Infobright是为数据分析和数据仓库应用设计的。数据高度压缩，按照块进行排序，每个块都对应有一组元数据。在处理查询时，访问元数据可决定跳过该块，甚至可能只需要元数据即可满足查询的需求。但该引擎不支持索引，不过在这么大的数据量级，即使有索引也很难发挥作用，而且块结构也是一种准索引 (quasi-index)。Infobright需要对MySQL服务器做定制，因为一些地方需要修改以适应面向列存储的需要。如果查询无法在存储层使用面向列的模式执行，则需要在服务器层转换成按行处理，这个过程会很慢。Infobright有社区版和商业版两个版本。
# 3.InnoDB存储引擎为什么选择B+Tree作为数据存储结构

## 3.1MySQL官方对索引的定义为：

索引（Index）是帮助MySQL高效获取数据的数据结构。可以得到索引的本质： **索引是数据结构** 。
InnoDB存储引擎支持以下几种常见的索引：**B+树索引、全文索引、哈希索引**，其中比较关键的是B+树索引。
![image](https://developer.qcloudimg.com/http-save/yehe-6193905/18ed0ab0409f30dcd66b0318312d1070.png)
 
### 数据结构地址：

**https://www.cs.usfca.edu/~galles/visualization/Algorithms.html **

## 3.2 mysql 为啥不选用 哈希表，二叉树，BST，AVL，红黑树数据结构，而是选用 B+tree

支持K-V格式的数据结构有很多，比如哈希表，二叉树，BST，AVL，红黑树，但是MYSQL最终选择了B+树，下面我们要对比下各个数据结构之间的区别：
 （1）使用哈希表可以进行数据存储，但是哈希表本质是无序散列表，因此在进行范围查询的时候就必须要挨个进行数据的对比，此时的效率是比较低的，此外，哈希表会存在哈希碰撞或者哈希冲突的问题，需要设计性能优良的哈希算法，因此哈希表并不适用，但是在MYSQL中，MEMORY存储引擎支持哈希索引，innodb存储引擎支持自适应哈希，
 （2）二叉树、BST、AVL、红黑树这几种树也可以支持K-V格式的数据存储，但是它们有一个共同的特点就是至多只有两个分支，那么在进行数据存储的时候，一个三层的树至多可以存储7个数据结果值，这个数据太少了，如果想要存储更多的数据，只能把树的高度变高，而树的高度变高之后又会导致IO的次数变多，影响查询效率，那么我们就要思考如何在保证树的高度不变的情况下存储更多的数据，上述的这些树存储数据少的原因在于分支至多只有两个，那么我们就要思考改变分支的结构了，因此有了B-树。


### **3.2.1 哈希表**

![image](https://developer.qcloudimg.com/http-save/yehe-6193905/d63f940d39b55fd8ee57f4264c50b442.png)
 
### 3.2.2 二叉树-二叉树 不平衡，数据可以均在右叶子节点

![image](https://developer.qcloudimg.com/http-save/yehe-6193905/fe0f4d8515c12fe4eda1f2fd603f4a71.png)
 
### 3.2.3 二叉查找树（BST）

![image](https://developer.qcloudimg.com/http-save/yehe-6193905/8c9d5ef6c6dc737b40ceb91382397948.png)
 
### 3.2.4 平衡二叉树（AVL）

它是一棵二叉排序树，它的左右两个子树的高度差（平衡因子）的绝对值不超过1，并且左右两个子树都是一棵平衡二叉树。
目的：使得树的高度最低，因为树查找的效率决定于树的高度
![image](https://developer.qcloudimg.com/http-save/yehe-6193905/56ae5b302891afef7d99e81dca95efb3.png)
 
缺点：平衡二叉树的查找性能是比较高的，但是维护一棵平衡二叉树的代价是非常大的。通常来说，需要1次或多次左旋和右旋来得到插入、更新和删除后树的平衡性。
 
## 3.3先说说B+树和B树的区别？

 
```
B+树节点关键字采用的是闭合区间，B树是开区间。
B+树非叶子节点不存储数据相关信息，只保存关键字（主键）和叶子节点的引用。相对于B树节省了节点的空间开销，所以能存储的关键字变多了。
B+树的叶子节点保存了数据部分
B+树叶子节点是顺序排列的，并且相邻节点具有顺序引用的关系。
```## 3.4 总结

索引 B+TREE  bigint索引（索引8b，指针 6b，14b，一次页加载到内存16kb，大 1000多行，第二次 1000多行 inodb数据都在叶子节点16kb10001000，可以查询索引的数据） 
   为什么是B+tree       
二叉树 不平衡，数据可以均在右叶子节点       
平衡二叉树  深度太深 io次数可能会很多       
hash 适合精确查询，不适合范围查询       
btree 有序，根节点 索引和数据 在一起，每页16kb，一次加载的数据会有限，在就是 有时候会io一次、两次、三次不稳定 

# 3.5MySql中的索引

## 3.5.1 B+Tree索引

假设我们有一张表`t_user`，包含以下列：
```

CREATE TABLE `t_user` (
 &nbsp;`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
 &nbsp;`name` varchar(20) NOT NULL DEFAULT '' COMMENT '名称',
 &nbsp;`age` int(10) NOT NULL DEFAULT 0 COMMENT '年龄',
 &nbsp;`city` varchar(20) DEFAULT '' COMMENT '城市',
 &nbsp;`create_user` varchar(20) DEFAULT '' COMMENT '创建人',
 &nbsp;`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
 &nbsp;`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
 &nbsp;PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB COMMENT='用户表';
```
### 3.5.1.1 聚集索引

​正如我们上面介绍 InnoDB 存储结构，索引与数据是共同存储的，不管是主键索引还是辅助索引，在查找时都是通过先查找到索引节点才能拿到相对应的数据。
​**如果我们没有定义主键呢？MySQL会使用唯一性索引，没有唯一性索引，MySQL也会创建一个隐含列RowID来做主键，然后用这个主键来建立聚集索引。**
聚集索引（主键索引）: 以`id`为索引键。 
### 3.5.1.2 辅助索引（二级索引）

对于辅助索引(Secondary Index，也称二级索引、非聚集索引)，叶子节点并不包含行记录的全部数据。**叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了一个书签( bookmark)**。该书签用来告诉InnoDB存储引擎哪里可以找到与索引相对应的行数据。因此InnoDB存储引擎的辅助索引的书签就是相应行数据的聚集索引键。
示例： 以name建立二级索引，索引叶子节点包含索引name字段和主键id
```

ALTER TABLE `t_user` ADD INDEX `idx_name`(`name`);
```### 3.5.1.3 回表

InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引（聚集索引）来找到一个完整的行记录。这个过程也被称为 **回表**
```

select name from t_user; &nbsp; &nbsp;  //不会回表，走二级索引
select name,city from t_user; //先走二级索引拿到主键，在通过主键id走聚簇索引查询得到city
```
### 3.5.1.4 联合索引

建立联合索引，索引键先按name排序，再按create-time排序。 
```

ALTER TABLE `t_user` ADD INDEX `uni_name_create_time`(`name`,`create_time`);
```
### 3.5.1.5 覆盖索引

InnoDB存储引擎支持覆盖索引(covering index，或称索引覆盖)，即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录(回表)。使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作。所以记住，覆盖索引并不是索引类型的一种。
```

select name,create_time from t_user; //走覆盖索引
```### 3.5.1.6 索引下推-ICP

`t_user`表中有以下字段：id，name，age 其中id是主键，(name,age)是组合索引
​select * from `t_user`where name = 'zhangsan' and age = 10;
​没有索引下推：mysql执行这条SQL语句的时候，会首先根据name的值去存储引擎中拉取数据，然后将数据返回到mysql server，然后在server层对age进行条件过滤，把符合条件的结果返回给客户端​有索引下推：mysql执行这条SQL语句的时候，会直接根据name和age的值去存储引擎中拉取数据，而无需在server层对数据进行条件过滤
​所谓的下推指的是将条件的筛选从server层下推到存储引擎层
​可以通过optizizer_switch中的index_condition_pushdown条件来是否开启，默认是开启的
```

SET optimizer_switch = 'index_condition_pushdown=off';
SET optimizer_switch = 'index_condition_pushdown=on';
```
## 3.5.2 Hash索引（自适应哈希索引）

InnoDB存储引擎内部自己去监控索引表，如果监控到某个索引经常用，那么就认为是热数据，然后内部自己创建一个hash索引，称之为自适应哈希索引( Adaptive Hash Index,AHI)，创建以后，如果下次又查询到这个索引，那么直接通过hash算法推导出记录的地址，直接一次就能查到数据，比重复去B+tree索引中查询三四次节点的效率高了不少。
InnoDB存储引擎使用的哈希函数采用除法散列方式，其冲突机制采用链表方式。注意，对于自适应哈希索引仅是数据库自身创建并使用的，我们并不能对其进行干预。
哈希索引只能用来搜索等值的查询,如 SELECT* FROM t_user WHERE name=xxx。**而对于其他查找类型,如范围查找,是不能使用哈希索引的,**
在Adaptive Hash Index，Key就是经常访问到的索引键值，Value就是该索引键值匹配的完整记录所在页面（Page）的位置
**Adaptive Hash Index只能用于等值比较，例如=、<=>、IN、AND等，无法用于排序**
Adaptive Hash Index是InnoDB自己维护创建的，人为无法干预。初始化为innodb_buffer_pool_size的1/64，会随着InnoDB Buffer Pool动态调整


## 3.5.3 全文索引

 不过MySQL从设计之初就是关系型数据库，存储引擎虽然支持全文检索，整体架构上对全文检索支持并不好而且限制很多，比如每张表只能有一个全文检索的索引，不支持没有单词界定符( delimiter）的语言，如中文、日语、韩语等。
所以MySQL中的全文索引功能比较弱鸡，了解即可。


## 3.6 Mysql为啥不用跳表，而选择B+Tree？（后面会详细篇章）

 
参考资料： 
https://dev.mysql.com/doc/refman/5.7/en/storage-engines.html  
《mysql是怎么运行的：从根上理解mysql》

InnoDB是MySQL的默认事务型引擎，也是最重要、使用最广泛的存储引擎。它被设计用来处理大量的短期(short-lived)事务，短期事务大部分情况是正常提交的，很少会被回滚。InnoDB的性能和自动崩溃恢复特性，使得它在非事务型存储的需求中也很流行。除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。如果要学习存储引擎，InnoDB也是一个非常好的值得花最多的时间去深入学习的对象，收益肯定比将时间平均花在每个存储引擎的学习上要高得多。所以InnoDB引擎也将是我们学习的重点。

1、mysql索引

InnoDB是MySQL的默认事务型引擎，也是最重要、使用最广泛的存储引擎。它被设计用来处理大量的短期(short-lived)事务，短期事务大部分情况是正常提交的，很少会被回滚。InnoDB的性能和自动崩溃恢复特性，使得它在非事务型存储的需求中也很流行。除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。如果要学习存储引擎，InnoDB也是一个非常好的值得花最多的时间去

硬件

MySQL存储引擎详解：InnoDB作为默认事务型引擎支持行锁和崩溃恢复，MyISAM适合只读场景但存在表锁缺陷。对比B+Tree与哈希、二叉树等结构，解析为何MySQL选择B+Tree实现高效索引。涵盖二级索引、联合索引、覆盖索引等核心机制，深入剖析InnoDB自适应哈希和索引下推优化原理。

关系型数据库

数据仓库

数据存储

哈希函数

数据结构

数据库

大数据

服务器

MariaDB

容灾

MySQL

腾讯云数据库特惠，新用户首单0.5折起


4核4G3M云服务器 新用户低至38元/年！

2026新春采购季

tencentdb-catalog

对象存储COS新用户低至1元！


bigdata-class

文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云AI代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

功能1上新10个字符

功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符。

功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符。

功能5描述100个字符功能5描述100个字符功能5描述100个字符功能5描述100个字符功能5描述100个字符功能5描述100个字符

功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符

功能4上新

文章&问答评论现已支持表情

全新交互，全新视觉，新增快捷键、悬浮工具栏、高亮块等功能并同时优化现有功能，全面提升创作效率和体验

社区富文本编辑器全新改版！诚邀体验～ 

精选全网热门MCP server，让你的AI更好用 🚀

💥开发者 MCP广场重磅上线！

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

1、mysql索引-腾讯云开发者社区-腾讯云

1、mysql索引

1、mysql索引

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐