廊坊商昊网站建设/外贸全网营销推广

一 存储引擎体系

1.1 MySQL体系架构

上图描述Connection Pool : 连接池组件

Management Services & Utilities : 管理服务和工具组件

SQL Interface : SQL接口组件

Parser : 查询分析器组件

Optimizer : 优化器组件

Caches & Buffers : 缓冲池组件

Pluggable Storage Engines : 存储引擎

File System : 文件系统

1. 连接层最上层是一些客户端和链接服务，包含本地sock 通信和大多数基于客户端/服务端工具实现的类似于TCP/IP的通信。主要完成一些类似于连接处理、授权认证、  及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接  入的每个客户端验证它所具有的操作权限。

2. 服务层第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实  现，如 过程、函数等。在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定表的查询的顺序，是否利用索引等，最后生成相应  的执行操作。如果是select语句，服务器还会查询内部的缓存，如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的

性能。

3. 引擎层存储引擎层， 存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能，这样我们可以根据自  己的需要，来选取合适的存储引擎。

4. 存储层数据存储层， 主要是将数据存储在文件系统之上，并完成与存储引擎的交互。

和其他数据库相比，MySQL存储引擎是插件式的存储引擎架构。将 查询处理和其他的系统任务以及数据的存储提取分离。这种架构可 以根据业务的需求和实际需要选择合适的存储引擎。

1.2 存储引擎介绍

1. 概述

针对不同的存储需求可以选择最优的存储引擎。存储引擎就是存 储数据，建立索引，更新查询数据等等技术的实现方式 。存储引 擎是基于表的，而不是基于库的。所以存储引擎也可被称为表类 型。

2. 查看MySQL存储引擎

3. MySQL常见两种引擎

MySQL默认支持InnoDB

二 InnoDB深入刨析

2.1 InnoDB体系结构

1. 缓冲池

介绍

InnoDB存储引擎基于磁盘文件存储，访问物理硬盘和在内存中进行访问，速度相差很大，为了尽可能弥补这两者之间的I/O效率的差值，就需要把经常使用的数 据加载到缓冲池中，避免每次访问都进行磁盘I/O。

在InnoDB的缓冲池中不仅缓存了索引页和数据页，还包含了undo页、插入缓存、自适应哈希索引以及InnoDB的锁信息等等。

读取

在数据库中进行读取页的操作时， 首先将磁盘中读取到的页数据存放在缓冲池中， 下一次再读相同的页时， 首先判断缓冲池中是否存在，如果缓冲池被命中， 则直接读取数据， 如果没有，则读取磁盘中的页数据。

更新

而对于数据库中页的修改操作，则首先修改在缓冲池中的页，然后再以一定的频率刷新到磁盘上，从而保证缓冲池中的数据与磁盘中的数据一致。页从缓冲池刷 新回磁盘的操作并不是在每次页发生更新时，都需要触发，出于整体的性能考虑，而是通过checkpoint机制刷新回磁盘。

参数配置

在专用服务器上，通常将多达80％的物理内存分配给缓冲池。参数设置：show variables like 'innodb_buffer_pool_size';

在InnoDB引擎中，允许有多个缓冲池实例，根据页的哈希值分配到不同的缓冲池实例中，从而减少数据库内部的资源竞争， 提升并发处理能力。 参数配置：

vi /etc/my.conf

innodb_buffer_pool_size=268435456

2. 后台线程

Master Thread

主要负责将缓冲池中的数据异步刷新到磁盘中, 保持数据的一致性， 还包括脏页的刷新、合并插入缓存、undo页的回收 。

IO Thread

在InnoDB存储引擎中大量使用了AIO来处理IO请求, 这样可以极大地提高数据库的性能，而IOThread主要负责这些IO请求的回调。

Purge Thread

主要用于回收事务已经提交了的undo log，在事务提交之后，undo log可能不用了，就用它来回收。

Pager Cleaner Thread

新引入的一个用于协助 Master Thread 刷新脏页到磁盘的线程，它可以减轻 Master Thread 的工作压力，减少阻塞。

3. 文件

frm文件

该文件是用来保存每个表的元数据信息的， 主要包含表结构定义 。

系统表空间

系统表空间是InnoDB数据字典，二次写缓冲区，更改缓冲区和撤消日志的存储区 。系统表空间可以具有一个或多个数据文件， 默认情况下会在数据存放目录中 创建一个名为 ibdata1 表空间数据文件。该文件名称可以通过参数 innodb_data_file_path 指定。

file_name:file_size[:autoextend[:max:max_file_size]]

独占表空间

innodb中设置了参数 innodb_file_per_table 为 1/ON，则会将存储的数据、索引等信息单独存储在一个独占表空间，因此也会产生一个独占表空间文件(ibd)

redo log

重做日志， 用于恢复提交事务修改的页操作 ， 用来保证事务的原子性和持久性。主要是解决 提交的事务没有执行完成但是数据库崩溃了，当数据库恢复之后， 可以完整的恢复数据。在执行操作时，InnoDB存储引擎会首先将重做日志信息放到这个缓冲区 redo log buffer,然后按照不同的策略和频率将buffer中的数据刷新 到重做日志中。redo log在磁盘中保存的名称为 ib_logfile0，ib_logfile1。

bin log

二进制日志，其中记录表结构中的数据变更，包含DDL与DML。

其他

错误日志、查询日志、慢查询日志等。

2.2 InnoDB逻辑存储结构

1. 表空间

表空间是InnoDB存储引擎逻辑结构的最高层， 大部分数据都存在于共享表空间ibdata1中。如果用户启用了参数 innodb_file_per_table ，则每张表都会有一个表 空间(xxx.ibd),里面存放表中的数据、索引和插入缓存Bitmap页。其他的数据如undo log、插入缓存索引页、系统事务信息、二次写缓存都是在共享表空间 中。

2. 段

表空间是由各个段组成的，常见的段有数据段、索引段、回滚段等。InnoDB存储引擎是基于索引组织的，因此数据即是索引，索引即数据。数据段就是B+树的 叶子节点， 索引段即为B+树的非叶子节点。InnoDB中对于段的管理，都是引擎自身完成，不需要人为对其控制。

3. 区

区是表空间的单元结构，每个区的大小为1M。 默认情况下， InnoDB存储引擎页大小为16K， 即一个区中一共有64个连续的页。

4. 页

页是组成区的最小单元，页也是InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为 16KB。为了保证页的连续性，InnoDB 存储引擎每次从磁盘申请 4- 5 个区。

5. 行

InnoDB 存储引擎是面向行的(row-oriented)，也就是说数据是按行进行存放的，每个页存放的行记录也是有硬性定义的，最多允许存放 16KB/2-200 行，即 7992 行记录。trx_id：每次对某条聚簇索引记录进行改动时，都会把对应的事务id赋值给trx_id隐藏列。

roll_pointer：每次对某条聚簇索引记录进行改动时，都会把旧的版本写入到undo日志中，然后这个隐藏列

就相当于一个指针，可以通过它来找到该记录修改前的信息。

2.3 checkpoint

1. 介绍

由于日常的DML语句操作时，首先操作的是缓冲池，并没有直接写入到磁盘，这有可能会导致内存中的 数据与磁盘中的数据产生不一致的情况，而与磁盘中数据不一致的页我们成为"脏页"。 而 checkpoint的工作，就是将内存中的脏页，在一定条件下刷新到磁盘。

如果在从缓冲池将页数据刷新到磁盘的过程中发生宕机，那么数据就无法恢复了；为了避免这种情况的 发生，采用了Write Ahead Log(WAL)策略，即当事务提交时，先写重做日志(redo log)，再修改 缓冲池数据页，最后通过Checkpoint刷新到磁盘(事务提交会触发checkpoint)。这样正在执行的 事务，因为存在日志都可以被恢复，没有日志的事务还没有执行也不会丢失数据。

2. 作用

A. 缩短数据恢复时间

当数据库发生宕机时，数据库不用重做所有的日志，因为Checkpoint之前的页都已经刷新会磁盘了， 故数据库只需要重做Checkpoint之后的日志就好，这样就大大缩短了恢复时间。

B. 缓冲池不够用时，需要先将脏页数据刷新到磁盘中；

当缓冲池不够用时, 根据LRU算法溢出最近最少使用的页, 如果此页是脏页,则强制执行 Checkpoint, 刷新脏页到磁盘。

C. 重做日志不可用时，刷新脏页到磁盘；

redo log大小是固定的, 当前的InnoDB引擎中， 重做日志的设计都是循环使用的，并不是无限增 大的。重做日志可以被重用的部分是已经不再需要的， 数据库发生宕机也不需要这部分的重做日志， 因此可以被覆盖使用， 如果此时重做日志还需要使用，那么必须强制执行Checkpoint，将缓冲池中 的页至少刷新磁盘, checkpoint移动到当前重做日志的位置。

write pos表示日志当前记录的位置，当ib_logfile_1写满后，会从ib_logfile_0从头开始记 录；check point表示将日志记录的修改写进磁盘，完成数据落盘，数据落盘后checkpoint会将日 志上的相关记录擦除掉，即write position ->checkpoint 之间的部分是redo log空着的部 分，用于记录新的记录，checkpoint->write position 之间是redo log待落盘的数据修改记 录。当write postion追上checkpoint时，得先停下记录，先推动checkpoint向前移动，空出位 置记录新的日志。

3. 分类

A. Sharp Checkpoint

Sharp Checkpoint 发生在数据库关闭时，将所有的脏页都刷新回磁盘，这是默认的工作方式，参 数：innodb_fast_shutdown=1。

B. Fuzzy Checkpoint

在InnoDB存储引擎运行时，使用Fuzzy Checkpoint进行页刷新，只刷新一部分脏页。

2.4 InnoDB关键特性

1. 插入缓存

主键是行唯一的标识符，在应用程序中行记录的插入顺序一般是按照主键递增的顺序进行插入的。因 此，插入聚集索引一般是顺序的，不需要磁盘的随机读取。因此，在这样的情况下，插入操作一般很快 就能完成。

但是，不可能每张表上只有一个聚集索引，在更多的情况下，一张表上有多个非聚集的辅助索引 (secondary index)。比如，我们还需要按照name这个字段进行查找，并且name这个字段不是唯 一的, 这样的情况下产生了一个非聚集的并且不是唯一的索引。在进行插入操作时，数据页的存放还是 按主键id的执行顺序存放，但是对于非聚集索引，叶子节点的插入不再是顺序的了。这时就需要离散地 访问非聚集索引页，插入性能在这里变低了。然而这并不是这个name字段上索引的错误，因为B+树的 特性决定了非聚集索引插入的离散性。

InnoDB存储引擎开创性地设计了插入缓冲，对于非聚集索引的插入或更新操作，不是每一次直接插入 索引页中，而是先判断插入的非聚集索引页是否在缓冲池中。如果在，则直接插入；如果不在，则先放 入一个插入缓冲区中，好似欺骗数据库这个非聚集的索引已经插到叶子节点了，然后再以一定的频率执 行插入缓冲和非聚集索引叶子节点的合并操作，这时通常能将多个插入合并到一个操作中(因为在一个 索引页中)，这就大大提高了对非聚集索引执行插入和修改操作的性能。

2. 两次写

当数据库写物理页时，如果宕机了，那么可能会导致物理页的一致性被破坏。

可能有人会说，重做日志不是可以恢复物理页吗？实际上是的，但是要求是在物理页一致的情况下。 也就是说，如果物理页完全是未写之前的状态，则可以用重做日志恢复。如果物理页已经完全写完了， 那么也可以用重做日志恢复。但是如果物理页前面2K写了新的数据，但是后面2K还是旧的数据，则种 情况下就无法使用重做日志恢复了。

这里的两次写就是保证了物理页的一致性，使得即使宕机，也可以用重做日志恢复。 在写物理页时，并不是直接写到真正的物理页上去，而是先写到一个临时页上去，临时页写完后，再写 物理页。这样一来：A. 如果写临时页时宕机了，物理页还是完全未写之前的状态，可以用重做日志恢复

B. 如果写物理页时宕机了，则可以使用临时页来恢复物理页

每次写物理页时，先写到double write buffer中，然后从double write buffer写到double write上去。最后再从double write buffer写到物理页上去。

3. 自适应hash索引

在InnoDB中默认支持的索引结构为 B+ 树，B+ 树索引可以使用到范围查找，同时是按照顺序的方式 对数据进行存储，因此很容易对数据进行排序操作，在联合索引中也可以利用部分索引键进行查询 。 而对于Hash索引则只能满足 =，<>,in查询，不能使用范围查询， 而且数据的存储是没有顺序的。

MySQL 默认使用 B+ 树作为索引，因为 B+ 树有着 Hash 索引没有的优点，那么为什么还需要自 适应 Hash 索引呢？

这是因为B+树的查找次数，取决于B+树的高度，在生产环境中，B+树的高度一般为3-4层，故需要3-4 次查询。而 Hash 索引在进行数据检索的时候效率非常高，通常只需要 O(1) 的复杂度，也就是一 次就可以完成数据的检索。虽然 Hash 索引的使用场景有很多限制，但是优点也很明显。InnoDB存储 引擎会监控对表上各索引页的查询，如果观察到hash索引可以提升速度，则建立hash索引，称之为自 适应hash索引(Adaptive Hash Index，AHI)。

注意，这里的自适应指的是不需要人工来指定，系统会根据情况自动完成。

什么情况下才会使用自适应 Hash 索引呢？如果某个数据经常被访问，当满足一定条件的时候，就会 将这个数据页的地址存放到 Hash 表中。这样下次查询的时候，就可以直接找到这个页面的所在位 置。值得注意的是，hash索引只能用于= ，in的查询，对于其他的查询类型，如范围匹配等是不能使 用hash索引的。而且自适应 Hash 索引只保存热数据(经常被使用到的数据)，并非全表数据。因此 数据量并不会很大，因此自适应 Hash 也是存放到缓冲池中，这样也进一步提升了查找效率。

4. 异步IO

为了提高磁盘的操作性能，在InnoDB存储引擎中使用异步非阻塞AIO的方式来操作磁盘。

与AIO对应的是Sync IO，如果是同步IO操作，则每进行一次IO操作，需要等待此次操作结束后才可 以进行接下来的操作。但是如果用户发出的是一条索引扫描的查询，那么这条SQL查询语句可能需要扫 描多个索引页，也就是需要进行多次的IO操作。每扫描一个页并等待其完成之后，再进行下一次扫描， 这是没有必要的。

用户可以在发出一个IO请求后立即再发出另一个IO请求，当全部的IO请求发送完毕后，等待所有的IO 操作完成，这就是AIO。

5. 刷新临接页

InnoDB提供刷新临近页功能：当刷新一脏页时，同时检测所在区(extent)的所有页，如果有脏页则 一并刷新，好处则是通过AIO特性合并写IO请求，缺点则是有些页不怎么脏也好被刷新，而且频繁的更 改那些不怎么脏的页又很快变成脏页，造成频繁刷新。对于固态磁盘则考虑关闭此功能(将 innodb_flush_neighbors设置为0)。

2.5 InnoDB事务

事务可由一条简单的SQL语句组成，也可以由一组复杂的SQL语句组成。事务是访问并更新数据库中各 个数据项的一个程序执行单元。在事务操作时，这组执行单元中的SQL，要么全部成功， 要么全部失 败。

1. 事务具有以下四个特性(ACID)

2. 隔离级别

并发事务带来的问题：

为了解决上述提到的事务并发问题，数据库提供一定的事务隔离机制来解决这个问题。数据库的事务隔 离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使用事务在一定程度上 “串行化” 进行，这显然与“并发” 是矛盾的。

数据库的隔离级别有4个，由低到高依次为Read uncommitted、Read committed、Repeatable read、Serializable，这四个级别可以逐个解决脏写、脏读、不可重复读、幻读这几类问题。

3. 实现

1). redo log

redo log叫做重做日志，是用来实现事务的持久性。该日志文件由两部分组成：重做日志缓冲(redo log buffer)以及重做日志文件(redo log),前者是在内存中，后者在磁盘中。当事务提交之后 会把所有修改信息都会存到该日志中, 用于在刷新脏页到磁盘时,发生错误时, 进行数据恢复使用。 例：

执行事务操作start transaction;

select balance from bank where name="Tom";

-- 生成 重做日志 balance=8000

update bank set balance = balance - 2000;

-- 生成 重做日志 account=2000

update finance set account = account + 2000;

commit;

流程

mysql 为了提升性能不会把每次的修改都实时同步到磁盘，而是会先存到Buffer Pool(缓冲池)里 头，把这个当作缓存来用。然后使用后台线程将缓存池刷新到磁盘。

当在执行刷新时，宕机或者断电，可能会丢失部分数据。所以引入了redo log来记录已成功提交事务 的修改信息，并且在事务提交时会把redo log持久化到磁盘，系统重启之后在读取redo log恢复最 新数据。

简单来说 ， redo log是用来恢复数据的 用于保障，已提交事务的持久化特性 ;

2). undo log

undo log 叫做回滚日志，用于记录数据被修改前的信息。他正好跟前面所说的重做日志所记录的相 反，重做日志记录数据被修改后的信息。undo log主要记录的是数据的逻辑变化，为了在发生错误时 回滚之前的操作，需要将之前的操作都记录下来，然后在发生错误时才可以回滚。

undo log 记录事务修改之前版本的数据信息，因此假如由于系统错误或者rollback操作而回滚的话 可以根据undo log的信息来进行回滚到没被修改前的状态。

三 存储引擎应用场景

在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据 实际情况选择多种存储引擎进行组合。以下是几种常用的存储引擎的使用环境 。InnoDB : 是Mysql的默认存储引擎，用于事务处理应用程序，支持外键, 行锁。如果应用对事

务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询以外，还

包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。InnoDB存储引擎除了有

效的降低由于删除和更新导致的锁定， 还可以确保事务的完整提交和回滚，对于电商系统中的商

品(SPU、SKU、分类、品牌)、订单、用户等信息的存储，InnoDB是最合适的选择。

MyISAM ： 如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完

整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。对于电商系统中，系统的操作

日志、用户评价、足迹等信息的存储，MyISAM是合适的选择。