MySQL体系结构、SQL执行流程、存储引擎、索引

系统（客户端）访问 MySQL 服务器前，做的第一件事就是建立 TCP 连接。经过三次握手建立连接成功后， MySQL 服务器对 TCP 传输过来的账号密码做身份认证、权限获取。

用户名或密码不对，会收到一个Access denied for user错误，客户端程序结束执行

用户名密码认证通过，会从权限表查出账号拥有的权限与连接关联，之后的权限判断逻辑，都将依赖于此时读到的权限

多个系统都可以和MySOL服务器建立连接，每个系统建立的连接肯定不止一个。所以，为了解决TCP无限创建与TCP频繁创建销毁带来的资源耗尽、性能下降问题。MySOL服务器里有专门的 TCP连接池限制连接数采用长连接模式复用TCP连接，来解决上述问题。

连接管理的职责是：负责认证、管理连接、获取权限信息

1.2第二层：服务层

SQL Interface: SQL 接口

接收用户的SQL命令，并且返回用户需要查询的结果。比如SELECT ... FROM就是调用SQL Interface
MySQL支持DML（数据操作语言）、DDL（数据定义语言）、存储过程、视图、触发器、自定义函数等多种SQL语言接口

Parser: 解析器

在解析器中对 SQL 语句进行语法分析、语义分析。将SQL语句分解成数据结构，并将这个结构传递到后续步骤，以后SQL语句的传递和处理就是基于这个结构的。如果在分解构成中遇到错误，那么就说明这个SQL语句是不合理的。
在SQL命令传递到解析器的时候会被解析器验证和解析，并为其创建 语法树 ，并根据数据字典查询语法树，会 验证该客户端是否具有执行该查询的权限 。创建好语法树后，MySQL还会对SQl查询进行语法上的优化，进行查询重写。

Optimizer: 查询优化器

SQL语句在语法解析之后、查询之前会使用查询优化器确定 SQL 语句的执行路径，生成一个 执行计划 。
这个执行计划表明应该 使用哪些索引 进行查询（全表检索还是使用索引检索），表之间的连接顺序如何，最后会按照执行计划中的步骤调用存储引擎提供的方法来真正的执行查询，并将查询结果返回给用户。
它使用“ 选取-投影-连接 ”策略进行查询。例如：

SELECT id,name FROM student WHERE gender = '女';

这个SELECT查询先根据WHERE语句进行选取，而不是将表全部查询出来以后再进行gender过滤。这个SELECT查询先根据id和name进行属性投影，而不是将属性全部取出以后再进行过滤，将这两个查询条件连接起来生成最终查询结果。

~~Caches & Buffers：查询缓存组件~~

MySQL内部维持着一些Cache和Buffer，比如Query Cache用来缓存一条SELECT语句的执行结果，如果能够在其中找到对应的查询结果，那么就不必再进行查询解析、优化和执行的整个过程了，直接将结果反馈给客户端。
这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等。
这个查询缓存可以在 不同客户端之间共享 。

从MySQL 5.7.20开始，不推荐使用查询缓存，并在 MySQL 8.0中删除 。原因如下：

查询缓存只能缓存精确相等的查询语句，而不能缓存包含变量或参数的查询语句。
查询缓存需要占用一定的内存空间，当缓存的查询语句过多时，会导致内存占用过高，从而影响数据库的性能。
查询缓存只能在单个服务器上使用，不能在分布式数据库中使用。

1.3第三层：引擎层

和其它数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用，主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。同时开源的 MySQL 还允许开发人员设置自己的存储引擎

这种高效的模块化架构为那些希望专门针对特定应用程序需求(例如数据仓库、事务处理或高可用性情况)的人提供了巨大的好处，同时享受使用一组独立于任何接口和服务的优势存储引擎

插件式存储引擎层( Storage Engines)，真正的负责了MySQL中数据的存储和提取，对物理服务器级别维护的底层数据执行操作，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。

1.4存储层

所有的数据、数据库、表的定义、表的每一行的内容、索引，都是存在文件系统上，以文件的方式存在的，并完成与存储引擎的交互。当然有些存储引擎比如InnoDB，也支持不使用文件系统直接管理裸设备，但现代文件系统的实现使得这样做没有必要了。在文件系统之下，可以使用本地磁盘，可以使用 DAS、NAS、SAN等各种存储系统。

三.SQL执行流程

MySQL的查询流程：

~~1. 查询缓存~~：Server 如果在查询缓存中发现了这条 SQL 语句，就会直接将结果返回给客户端；如果没有，就进入到解析器阶段。因为查询缓存往往效率不高，所以在 MySQL8.0 之后就抛弃了这个功能。

2. 解析器：在解析器中对 SQL 语句进行语法分析、语义分析

分析器先做“ 词法分析 ”。你输入的是由多个字符串和空格组成的一条 SQL 语句，MySQL 需要识别出里面的字符串分别是什么，代表什么。 MySQL 从你输入的"select"这个关键字识别出来，这是一个查询语句。它也要把字符串“T”识别成“表名 T”，把字符串“ID”识别成“列 ID”。
接着要做“ 语法分析 ”。根据词法分析的结果，语法分析器（比如：Bison）会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。例如下面的SQL词法正确，但是语法错误。

select user_id,name from users group by user_id;

如果SQL语句正确，则会生成一个这样的语法树：

3. 优化器：在优化器中会确定 SQL 语句的执行路径，比如是根据 全表检索 ，还是根据 索引检索等

在查询优化器中，可以分为 逻辑查询优化阶段 和 物理查询优化阶段

逻辑查询优化就是通过改变 SQL 语句的内容来使得 SQL 查询更高效，同时为物理查询优化提供更多的候选执行计划。通常采用的方式是对 SQL 语句进行 等价变换，对查询进行重写，而查询重写的数学基础就是关系代数。对条件表达式进行等价谓词重写、条件简化，对视图进行重写，对子查询进行优化，对连接语义进行了外连接消除、嵌套连接消除等。
物理查询优化是基于关系代数进行的查询重写，而关系代数的每一步都对应着物理计算，这些物理计算往往存在多种算法，因此需要计算各种物理路径的代价，从中选择代价最小的作为执行计划。在这个阶段里，对于单表和多表连接的操作，需要高效地使用索引，提升查询效率。

4. 执行器：截止到现在，还没有真正去读写真实的表，仅仅只是产出了一个执行计划。于是就进入了执行器阶段

在执行之前需要判断该用户是否 具备权限 。如果没有，就会返回权限错误。如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，调用存储引擎API对表进行读写。存储引擎 API 只是抽象接口，下面还有个存储引擎层，具体实现还是要看表选择的存储引擎。在 MySQL8.0 以下的版本，如果设置了查询缓存，会将查询结果进行缓存。

总结：SQL 语句在 MySQL 中的流程是:SQL语句→查询缓存(8.0之前)→解析器→优化器→执行器

四.存储引擎

1.介绍

存储引擎就是存储数据、建立索引、更新/查询数据 等技术的实现方式，他是mysql数据库的核心，负责接收上层传下来的指令，然后对表中的数据进行提取或写入操作。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。我们可以在创建表的时候，来指定选择的存储引擎，如果没有指定将自动选择默认的存储引擎。

2.InnoDB 引擎：具备外键支持功能的事务存储引擎(重点)

2.1介绍

InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在 MySQL 5.5及之后，InnoDB是默认的 MySQL 存储引擎。

2.2特点

InnoDB是MySQL的默认事务型引擎，它被设计用来处理大量的短期(short-lived)事务。可以确保事务的完整提交(Commit)和回滚(Rollback)
支持行级锁，提高并发访问性能；
支持外键FOREIGN KEY约束，保证数据的完整性和正确性；
InnoDB是为处理巨大数据量的最大性能设计
因为InnoDB支持事务及相关日志文件，所以崩溃后能安全恢复

除了增加和查询外，还需要更新、删除操作，那么，应优先选择InnoDB存储引擎。除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎

2.3表结构

为了保存表结构， InnoDB 在数据目录下对应的数据库子目录下创建了一个专门用于描述表结构的文件

表名.frm

比方说我在数据库qq下面创建一个名为test的表，数据库qq对应的子目录下就会创建一个名为 test.frm 的用于描述表结构的文件

注：MySQL8把frm文件合并到 ibd 文件中了。

2.4表中数据和索引

InnoDB其实是使用页为基本单位来管理存储空间的，默认的页大小为 16KB
对于InnoDB存储引擎来说，每个索引都对应着一棵B+树，该B+树的每个节点都是一个数据页，数据页之间不必要是物理连续的，因为数据页之间有 双向链表 来维护着这些页的顺序。
InnoDB的聚簇索引的叶子节点存储了完整的用户记录，也就是所谓的索引即数据，数据即索引

为了更好的管理这些页，lnnoDB提出了一个表空间 的概念，这个表空间是一个抽象的概念，它可以对应文件系统上一个或多个真实文件(不同表空间对应的文件数量可能不同)，负责存储表中的数据和索引。每一个表空间可以被划分为很多个页，我们的表数据就存放在某个表空间下的某些页里。这里表空间有几种不同的类型：

①系统表空间 (system tablespace)

默认情况下，InnoDB会在数据目录下创建一个名为 ibdata1 、大小为 12M 的文件，这个文件就是对应的系统表空间在文件系统上的表示。这个文件是自扩展文件，当不够用的时候它会自己增加文件大小。

在一个MySQL服务器中，系统表空间只有一份。从MySQL5.5.7到MySQL5.6.6之间的各个版本中，我们表中的数据都会被默认存储到这个系统表空间

②独立表空间(file-per-table tablespace)

在MySOL5.6.6以及之后的版本中，lnnoDB并不会默认的把各个表的数据存储到系统表空间中，而是 为每一个表建立一个独立表空间，也就是说我们创建了多少个表，就有多少个独立表空间。使用独立表空间来存储表数据的话，会在该表所属数据库对应的子目录下创建一个表示该独立表空间的文件，文件名和表名相同，只不过添加了一个ibd的扩展名而已:

表名.ibd

3.MyISAM 引擎：主要的非事务处理存储引擎

3.1介绍

MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但MyISAM 不支持事务、行级锁、外键，有一个毫无疑问的缺陷就是崩溃后无法安全恢复。是MySQL5.5之前默认的存储引擎

3.2特点

①不支持事务，不支持外键

②支持表锁，不支持行锁

③访问速度快，对事务完整性没有要求或者以SELECT、INSERT为主的应用

3.3表结构

MySQL8之前：

xxx.frm：存储表结构信息

MySQL8：

xxx.sdi：存储表结构信息

3.4表中数据和索引

在MyISAM中的索引全部都是二级索引，该存储引擎的数据和索引是分开存放的。所以在文件系统中也是使用不同的文件来存储数据文件和索引文件，同时表数据都存放在对应的数据库子目录下。

xxx.MYD: 存储数据
xxx.MYI: 存储索引

其中 .MYD 是表的数据文件，也就是我们插入的用户记录。采用独立表存储模式，每个表对应一个MYD文件; .MYI 是表的索引文件，我们为该表创建的索引都会放到这个文件中。

4.Memory

4.1介绍

Memory采用的逻辑介质是内存，响应速度很快，但是当mysqld守护进程崩溃的时候数据会丢失。另外，要求存储的数据是数据长度不变的格式，比如，Blob和Text类型的数据不可用(长度不固定的)。

4.2特点

Memory同时支持哈希（HASH）索引和 B+树索引。
Memory表至少比MyISAM表要快一个数量级。
MEMORY 表的大小是受到限制的。表的大小主要取决于两个参数，分别是 max_rows 和
max_heap_table_size 。其中，max_rows可以在创建表时指定；max_heap_table_size的大小默认为16MB，可以按需要进行扩大。
数据文件与索引文件分开存储。

缺点：其数据易丢失，生命周期短。基于这个缺陷，选择MEMORY存储引擎时需要特别小心。

5.存储引擎之间的区别

InnoDB、MyISAM、Memory

另外，InnoDB 本身不支持 Hash 索引，但是提供自适应 Hash 索引。如果某个数据经常被访问，当满足一定条件的时候，就会将这个数据页的地址存放到Hash 表中。这样下次查询的时候，就可以直接找到这个页面的所在位置，这样让 B+ 树也具备了 Hash 索引的优点。

InnoDB与MyISAM

6.存储引擎的选择

在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。

InnoDB: 是Mysql的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。

MyISAM ：如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。现在已经被mongodb替代

MEMORY：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。现在已经被redis替代

五.索引

1.介绍

官方介绍索引是帮助MySQL高效获取数据的数据结构，可以简单理解为"排好序的快速查找数据结构"。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。进行数据查找时，首先查看查询条件是否命中某条索引，符合则通过索引查找相关数据，如果不符合则需要全表扫描，即需要一条一条地查找记录，直到找到与条件符合的记录

索引是在存储引擎中实现的，因此每种存储引擎的索引不一定完全相同，并且每种存储引擎不一定支持所有索引类型。同时，存储引擎可以定义每个表的最大索引数和最大索引长度。所有存储引擎支持每个表至少16个索引，总索引长度至少为256字节。有些存储引擎支持更多的索引数和更大的索引长度。

如上图所示，数据库没有索引的情况下，数据分布在硬盘不同的位置上面，读取数据时，摆臂需要前后摆动查找数据，这样操作非常消耗时间。如果数据顺序摆放，那么也需要从1到6行按顺序读取，这样就相当于进行了6次I/O操作，依旧非常耗时。

如果给字段Col2添加了索引，就相当于在硬盘上为Col2维护了一个索引的数据结构，即这个二叉搜索树。二叉搜索树的每个结点存储的是(K，V)结构，key是Col2，value 是该 key 所在行的文件指针(地址)。比如：该二又搜索树的根节点就是：(34，0x07)。现在对 Col2添加了索引，这时再去查找 Col2= 89 这条记录的时候会先去查找该二又搜索树(二又树的遍历查找)。读 34到内存，89>34；继续右侧数据，读 89 到内存，89 = 89；找到数据返回。找到之后就根据当前结点的value快速定位到要查找的记录对应的地址。我们可以发现，只需要查找两次就可以定位到记录的地址，查询速度就提高了。
这就是我们为什么要建索引，目的就是为了 减少磁盘I/O的次数 ，加快查询速率。每个节点的读取可以视为一次I/O读取，因此树的高度与最多的I/O次数相关。

2.优势和劣势

优势

提高查询效率：索引可以快速定位到具有特定值的行，从而提高查询效率。
加速排序：索引可以按照特定的方式排序，从而加速排序操作。
降低系统的 I/O 开销：索引可以减少系统的 I/O 开销，从而提高系统的性能。

劣势

占用存储空间：索引需要占用额外的存储空间，如果表的数据量很大，索引会占用较大的磁盘空间。
增加数据插入、更新和删除的开销：索引需要在插入、更新和删除数据时进行维护，从而增加了数据操作的开销。
不适合于小表：对于小表，建立索引的意义不大，反而会增加系统的开销。
不支持一些复杂的查询：对于一些复杂的查询，如模糊查询等，索引的效果可能不如预期。

注：索引可以提高查询的速度，但是可能会影响插入、修改、删除记录的速度。因为需要维护索引。

3.索引建立过程

每一行存储记录的格式叫行格式，下面是简化后的行格式 Compact：

record_type：记录头信息的一项属性，表示记录的类型，0表示普通记录、1表示目录项记录、2表示最小记录、3表示最大记录。
next_record：记录头信息的一项属性，表示下一条地址相对于本条记录的地址偏移量，我们用箭头来表明下一条记录是谁。
各个列的值：这里只记录在index_demo表中的三个列，分别是c1、c2和c3。
其他信息：除了上述3种信息以外的所有信息，包括其他隐藏列的值以及记录的额外信息。

我们把一些记录放到页里，页内的每条记录都是逻辑连续的，但在物理存储上不是连续的，数据页之间也不是连续的，而是通过双向链表连接。

并且每个数据页还会维护一个页目录(广泛来讲也叫数据页)，里面的数据按照从小到大的顺序排序，这样可以通过二分法来快速定位页内的某些记录，比一条一条遍历的时间效率高很多

当我们往表里再插入一些数据后，很可能会出现下面这种情况

因为我们现在是按照主键的方式存储，我们需要保证记录按主键递增的方式存储(实际上这是建立主键索引的必要条件)，所以我们需要进行一些记录移动，整个过程，我们称之为页分裂

当数据页越来越多的时候，我们可以给每一个数据页做一个对应的目录项，用于快速定位某些记录的所在页，目录项之间通过双向链表连接。这些目录项其实就叫做索引

同样的，目录项也维护了一个自己的目录页（连续空间的数组结构）用于二分查找。

每个目录项包括下边两个部分：

页的用户记录中最小对主键值，我们用 key 来表示
页号，我们用 page_no 表示

当记录越来越多时，由于这些目录项是不连续的(通过链表连接)，我们需要能根据主键快速定位一个目录项。对此，我们可以为这些目录项再生成一个更高级的目录，如下图

我们可以用下边这个图来描述它：

这个数据结构，它的名称就是 B+树

4.B+树

不论是存放用户记录的数据页，还是存放目录项记录的数据页，我们都把它们存放到B+树这个数据结构中了，所以我们也称这些数据页为节点。从图中可以看出，我们的实际用户记录其实都存在了B+树的叶子节点上，其余用来存放目录项的节点称为非叶子节点或者内节点，其中B+树最上边的那个节点也称为根节点。

假设所有存放用户记录的叶子节点代表的数据页可以存放 100条用户记录，所有存放目录项记录的内节点代表的数据页可以存放 1000条目录项记录，那么：

如果B+树只有1层，也就是只有1个用于存放用户记录的节点，最多能存放100 条记录

如果B+树有2层，最多能存放 1000 x 100=10 0000 条记录

如果B+树有3层，最多能存放 1000 x 1000 x 100=1 0000 0000 条记录

如果B+树有4层，最多能存放 1000 x 1000 x 1000 x100=1 0000 0000 条记录。相当多的记录！

一般情况下，我们用到的B+树都不会超过 3 层，当然实际情况中每个节点可能不能填充满，因此在数据库中，B+ Tree的高度一般在2~4层，又因为InnoDB存储引擎设计时是将根节点常驻内存的，那我们通过主键值去查找某条记录最多只需要 1~3次 I/O 操作

又因为在每个页面内有所谓的 Page Directory (页目录)，所以在页面内也可以通过二分法实现快速定位记录。

5.分类

索引按照物理实现方式，索引可以分为2种：聚簇（聚集）和非聚簇（非聚集）索引。我们也把非聚集索引称为二级索引或者辅助索引。

聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式(所有的用户记录都存储在了叶子节点)，也就是所谓的索引即数据，数据即索引。

特点

1. 使用记录主键值的大小进行记录和页的排序，这包括三个方面的含义：

页内的记录是按照主键的大小顺序排成一个单向链表。
各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表。
存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表。

2. B+树的叶子节点存储的是完整的用户记录。所谓完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）。

优点

数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快
聚簇索引对于主键的排序查找和范围查找速度非常快
按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以节省了大量的I/O操作。

缺点

插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。
更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新
二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据，即回表查询。

二级索引(辅助索引、非聚簇索引)

聚簇索引只能在搜索条件是主键值时才能发挥作用，因为B+树中的数据都是按照主键进行排序的。如果我们想以别的列作为搜索条件，可以再建几棵B+树，不同的B+树中的数据采用不同的排序规则。比方说我们用c2 列的大小作为数据页、页中记录的排序规则，再建一棵B+树。

二级索引的内节点的目录项记录的内容实际上是由三个部分构成的：

索引列的值
主键值(保证唯一性)
页号

主键值的存在是为了保证新插入的记录能找到自己在哪个页里，比如我们想插入一行记录，其中c1、c2、c3的值分别：‘9’、‘1’、‘c’，页3中的两条目录项记录对应的c2列的值都是1，新插入的这条记录的c2列的值也是1，那就不知道该插入到哪个页中了。

但是当我们加上主键后，可以根据主键来确定该插入的页，‘9’>‘7’，应该放入页5内

回表

我们根据这个以c2列大小排序的B+树只能确定我们要查找记录的主键值，所以如果我们想根据c2列的值查找到完整的用户记录的话，仍然需要到聚簇索引中再查一遍，这个过程称为回表。也就是根据c2列的值查询一条完整的用户记录需要使用到 2 棵B+树！

非聚簇索引的存在不影响数据在聚簇索引中的组织，所以一张表可以有多个非聚簇索引。

联合索引

联合索引实际上也是一种二级索引(非聚簇索引)。我们可以同时以多个列的大小作为排序规则，也就是同时为多个列建立索引，比方说我们想让B+树按照 c2和c3列的大小进行排序，这个包含两层含义:

先把各个记录和页按照c2列进行排序。
在记录的c2列相同的情况下，采用c3列进行排序

为c2和c3列建立的索引的示意图如下:

6.InnoDB的B+树索引的注意事项

根页面位置不动

前边介绍B+树索引的时候，为了更容易理解，就先把存储用户记录的叶子节点都画出来，然后接着画存储目录项记录的内节点，自下而上。实际上B+树的形成过程是自上而下的：

每当为某个表创建一个B+树索引(聚族索引不是人为创建的，默认就有)的时候，都会为这个索引创建一个根节点页面。最开始表中没有数据的时候，每个B+树索引对应的根节点中既没有用户记录，也没有目录项记录。
随后向表中插入用户记录时，先把用户记录存储到这个根节点中
当根节点中的可用空间用完时继续插入记录，此时会将根节点中的所有记录复制到一个新分配的页，比如页a 中，然后对这个新页进行页分裂的操作，得到另一个新页，比如页b 。这时新插入的记录根据键值(也就是聚簇索引中的主键值，二级索引中对应的索引列的值)的大小就会被分配到页a 或者页b 中，而根节点便升级为存储目录项记录的页。

这个过程特别注意的是：一个B+树索引的根节点自诞生之日起，便不会再移动。这样只要我们对某个表建立一个索引，那么它的根节点的页号便会被记录到某个地方，然后凡是 InnoDB 存储引擎需要用到这个索引的时候，都会从那个固定的地方取出根节点的页号，从而来访问这个索引。

再强调一点：索引是在存储引擎中实现的，因此每种存储引擎的索引不一定完全相同，并且每种存储引擎不一定支持所有索引类型。

7.MyISAM中的索引方案

即使多个存储引擎支持同一种类型的索引，但是他们的实现原理也是不同的。Innodb 和 MylSAM默认的索引是B+ tree索引；而Memory默认的索引是Hash索引。

MyISAM引擎使用B+Tree 作为索引结构，叶子节点的data域存放的是数据记录的地址

7.1MyISAM索引的原理

我们知道 InnoDB中索引即数据，也就是聚簇索引的那棵B+树的叶子节点中包含了所有完整的用户记录，而 MyISAM 的索引方案虽然也使用树形结构，但是却将索引和数据分开存储，所以在MyISAM存储引擎中，并没有数据页的概念：

将表中的记录按照记录的插入顺序单独存储在一个文件中，称之为数据文件。这个文件并不划分为若干个数据页，有多少记录就往这个文件中塞多少记录就成了。由于在插入数据的时候并没有刻意按照主键大小排序，所以我们并不能在这些数据上使用二分法进行查找
使用 MyISAM 存储引擎的表会把索引信息另外存储到一个称为索引文件的另一个文件中。MyISAM会单独为表的主键创建一个索引，只不过在索引的叶子节点中存储的不是完整的用户记录，而是主键值 + 数据记录地址的组合。

这里设表一共有三列，假设我们以Col1为主键，上图是一个MyISAM表的主索引(Primary key)示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主键索引和二级索引(Secondary key) 在结构上没有任何区别，只是主键索引要求key是唯一的，而二级索引的key可以重复。如果我们在Col2上建立一个二级索引，如下图：

8.索引的代价

索引虽然能加速查询，但不能乱建，它在空间和时间上都会有消耗：

空间上的代价：每建立一个索引都要为它建立一棵B+树，每一棵B+树的每一个节点都是一个数据页，一个页默认会占用 16KB 的存储空间，一棵很大的B+树由许多数据页组成，那就是很大的一片存储空间。
时间上的代价：每次对表中的数据进行增、删、改操作时，都需要去修改各个B+树索引。而且我们讲过，B+树每层节点都是按照索引列的值从小到大的顺序排序，所以存储引擎需要额外的时间进行一些记录移位，页面分裂、页面回收等操作来维护好节点和记录的排序。如果我们建了许多索引，每个索引对应的B+树都要进行相关的维护操作，会给性能拖后腿

双非鼠不想输

关注

2
点赞
踩
6

收藏

觉得还不错? 一键收藏
0
评论
MySQL体系结构、SQL执行流程、存储引擎、索引

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式，他是mysql数据库的核心。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。我们可以在创建表的时候，来指定选择的存储引擎，如果没有指定将自动选择默认的存储引擎。下面主要提提三种存储引擎InnoDB、MyISAM、Memory的特点MyISAM是MySQL早期的默认存储引擎。
复制链接

扫一扫