1. 架构基础
MySQL是C/S 架构的 。
从MySQL的架构图 , 我们可以看出MySQL的架构自顶向下大致可以分为网络连接层、数据库服务层、存储引擎层和系统文件层四大部分。
MySQL在整体架构上分为Server层和存储引擎层。其中Server层 , 包括连接器、查询缓存、分析器、优化器、执行器等 , 存储过程、触发器、视图和内置函数都在这层实现。数据引擎层负责数据的存储和提取 , 如InnoDB、MyISAM、Memory等引擎。在客户端连接到Server 层后 , Server会调用数据引擎提供的接口 , 进行数据的变更。
1.1 网络连接层
网络连接层位于整个MySQL体系架构的最上层 , 主要担任客户端连接器的角色。提供与MySQL服务器建立连接的能力 , 几乎支持所有主流的服务端语言 , 例如: Java、C、C++、Python等 , 各语言都是通过各自的API接口与MySQL建立连接。
连接器(connectors)可供Native C API、JDBC、ODBC、NET、PHP、Perl、 Python、Ruby、Cobol等连接mysql ; ODBC(open database connection)叫开放数据库(系统)互联 ;JDBC(Java DataBase Connection)是主要用于java语言利用较为底层的驱动连接数据库 ; 以上这些 , 站在编程角度可以理解为连入数 据库管理系统的驱动 , 站在mysql角度称作专用语言对应的连接器。
1.2 数据服务层
数据库服务层是整个数据库服务器的核心 , 主要包括了系统管理和控制工具、连接池、SQL接口、解析器、查询优化器和缓存等部分。
1.2.1 连接池
连接池: 负责和客户端建立连接，获取用户权限以及维持和管理连接。通过 show processlist; 来查询连接的状态。在用户建立连接后，即使管理员改变连接用户的权限，也不会影响到已连接的用户。默认连接时长为 8 小时，超过时间后将会被断开。
mysql是单进程多线程模型的 , 任何连接器连入mysql以后 , 每个用户连接, 都会创建一个单独的连接线程 。其实mysql连接也有长短连接两种方式 , 使用mysql客户端连入数据库后 , 直到使用quit命令才退出 , 可认为是长连接 ; 使用mysql中的-e选项 , 在mysql客户端向服务器端申请运行一个命令后则立即退出 , 也就意味着连接会立即断开 , 理解为短连接 。所以 , 用户连入mysql后 , mysql会创建一个连接线程。 线程创建完成之后 , 能够通过这个连接线程完成接收客户端发来的请求 , 为客户端处理该请求 ,随后构建响应报文并发给客户端 ;
简单说下长连接:
优势 : 在连接时间内 , 客户端一直使用同一连接，避免多次连接的资源消耗。
劣势 : 在MySQL执行时 , 使用的内存被连接对象管理，由于长时间没有被释放，会导致系统内存溢出，被系统kill. 所以需要定期断开长连接，或执行大查询后 , 断开连接。MySQL 5.7后 , 可以通过 mysql_rest_connection 初始化连接资源，不需要重连或者做权限验证。
由于是单进程模型 , 就意味着必须要维持一个线程池 , 跟之前介绍过的varnish很接近 , 需要一个线程池来管理众多线程 , 以保证线程池中的众多线程有序接收众多客户端的并发请求 , 完成请求并发出响应。 组件连接池 ( connection pool)就是实现这样功能 ;连接池 ( connection pool)对于mysql而言 , 它所实现的功能 , 包括authentication认证 , 用户发来的账号密码是否正确要完成认证功能 ; thread reuse , 线程重用功能 , 一般当一个用户连接进来以后要用一个线程来响应它 , 而后当用户退出这个线程有可能并非被销毁 , 而是把它清理完以后 ,重新收归到线程池当中的空闲线程中去 , 以完成所谓的线程重用 ; connection limit 线程池的大小决定了连接并发数量的上限 , 例如 , 最多容纳100线程 , 一旦到达此上限后续到达的连接请求则只能排队或拒绝连接 ; check memory用来检测内存 ;caches实现线程缓存。 整个都属于线程池的功能。
1.2.2 SQL接口
SQL接口主要负责接收客户端发送过来的各种SQL命令 , 并将SQL命令发送到其他部分 , 并接收其他部分返回的结果数据，将结果数据返回给客户端。
当用户请求之后 , 通过线程池建立一个用户连接 , 这个线程一直存在 , 然后用户就通过这个会话 , 发送对应的SQL语句到服务器端。服务器收到SQL语句后, 如果要执行对应的SQL语句 , 首先要能理解sql语句需要有sql解释器或叫sql接口 ( sql interface) 。 SQL接口可理解为是整个mysql的外壳 , 就像shell是linux操作系统的外壳一样道理 。
用户无论通过哪种连接器发来的基本的SQL请求对SQL而言分为DDL和DML两种类型 , 但是无论哪种类型 , 提交以后必须交给内核 , 让内核来运行(当然 , 事实上通过native C API也有发过来的不是SQL请求 , 而仅仅是对API中的传递参数后的调用 , 不是SQL语句不过都统统理解为sql语句罢了)。在这之前必须要告诉内核哪个是命令 , 哪个是选项 , 哪些是参数 , 是否存在语法错误等等 ; 因此 , 这个整个SQL接口就是一个完完整整的sql命令的解释器 , 并且这个sql接口还应该具备完整的sql接口功能 , 比如支持所谓过程式编程 、支持代码块的实现像存储过程、存储函数、触发器 , 必要时还要实现部署一个关系型数据库应该具备的基本组件例如视图等等。
1.2.3 解析器
SQL接口做完词法分析、句法分析后 , 要分析语句如何执行才可以让parser解析器或分析器实现 , parser是专门的分析器 , 这个分析器并不是分析语法问题的 , 语法问题在sql接口时就能发现是否有错误了。一个语句没有问题, 就要做执行分析 , 即所谓叫查询翻译 , 把一个查询语句给它转换成对应的能够在本地执行的特定操作 ; 比如说看上去是语句而背后可能是执行的一段二进制指令 , 这个时候就完成对应的指令。还要根据用户请求的对象 , 比如某一字段查询内容是否有对应数据的访问权限 , 或叫对象访问权限 ; 在数据库中库、表、字段、字段中的数据有时都称为object , 叫一个数据库的对象 。用户认证的通过 , 并不意味着就一定能访问数据库上的所有数据, 所以说 , mysql 的认证大概分为两过程都要完成 , 第一是连入时需要认证账号密码是否正确 , 这是authentication , 然后 验证成功后用户发来sql语句 , 还要验证用户是否有权限获取它期望请求获取的数据 ;这个称为object privilege , 这一切都是由parser分析器进行的。
1.2.4 查询优化器
分析器分析完成之后 , 可能会生成多个执行树，这意味着为了能够达到访问期望访问到的目的 , 可能有多条路径都可实现 , 就像文件系统一样可以使用相对路径也可使用绝对路径 ; 它有多种方式 , 在多种路径当中一定有一个是最优的 , 类似路由选择。因此 ， 优化器就要去衡量多个访问路径中哪一个代价或开销是最小的 ， 这个开销的计算要依赖于索引等各种内部组件来进行评估; 而且这个评估的只是近似值 , 同时还要考虑到当前mysql内部在实现资源访问时统计数据 , 比如 , 根据判断认为是1号路径的开销最小的 , 但是众多统计数据表明发往1号路径的访问的资源开销并不小 , 并且比3号路径大的多 , 因此 , 可能会依据3号路径访问 ; 这就是所谓的优化器 , 它负责检查多条路径 , 每条路径的开销 , 然后评估开销 , 这个评估根据内部的静态数据、索引 , 根域根据动态生成的统计数据来判定每条路径的开销大小 , 因此这里还有statics ;一旦优化完成之后 , 还要生成统计数据 , 这就是优化器的作用 ; 如果没有优化器mysql执行语句是最慢的 。其实优化还包括一种功能 , 一旦选择完一条路径后 , 例如用户给的这个命令执行起来 , 大概需要100个开销 , 如果通过改写语句能够达到同样目的可能只需要30个开销 , 于是 , 优化器还要试图改写sql语句 。 所以优化本身还包括查询语句的改写 , 一旦优化完成 , 接下来就交给存储引擎完成。
1.2.5 缓存
当接受到查询请求时 , 会现在查询缓存中查询(key/value保存) , 是否执行过。没有的话，再走正常的执行流程。
但在实际情况下，查询缓存一般没有必要设置。因为在查询涉及到的表被更新时 , 缓存就会被清空。所以适用于静态表。在MySQL8.0后，查询缓存被废除。
事实上 , 整个存取过程 , 尤其是访问比较热点的数据 , 也不可能每一次当用户访问时或当某SQL语句用到时再临时从磁盘加载到内存中。因此 , 为了能够加上整个性能 , mysql的有些存储引擎可以实现把频繁访问到的热点数据统统装入内存。用户访问、修改热点数据时直接在内存中操作 , 只不过周期性的写入磁盘上而已 , 比如像InnoDB。所以caches和buffers组件就是实现此功能的。MySQL为了执行加速 , 因为mysql会不断访问数据 , 而随计算机来说io是最慢的一环 , 尤其是磁盘io , 所以mysql为了执行加速都载入内存中管理。这就需要MySQL维护cache和buffer缓存或缓冲，有很多存储引擎自己也有cache和buffer。
1.3 存储引擎层
MySQL中的存储引擎层主要负责数据的写入和读取 , 与底层的文件进行交互。
mysql是插件式存储引擎 , 因此可以选择多种不同的引擎 ,服务器中的查询执行引擎通过相关的接口与存储引擎进行通信 , 同时接口屏蔽了不同存储引擎之间的差异。MySQL中 , 最常用的存储引擎就是InnoDB和MyISAM。 MyISAM是MySQL经典的存储引擎之一 。InnoDB是由Innobase Oy公司所开发 , 2006年五月由甲骨文公司并购提供给MySQL的 , NDB主要用于MySQL Cluster分布式集群环境 , archive做归档的等等 。还有许多第三方开发的存储引擎。存储引擎负责把具体分析的结果完成对磁盘上文件路径访问的转换 , 数据库中的行数据都是存储在磁盘块上的。因此存储引擎要把数据库数据映射为磁盘块, 并把磁盘块加载至内存中。 进程实现数据处理时 , 是不可能直接访问磁盘上的数据的 , 因为它没有权限。只有让内核把数据库所访问的数据加载至内存中以后 , 进程在内存中完成数据修改 , 由内核再负责把数据存回磁盘。
对于文件系统而言 , 数据的存储都是以磁盘块方式存储的。但是mysql在实现数据组织时不完全依赖于磁盘 , 而是把磁盘块再次组织成更大一级的逻辑单位 , 类似于lvm中的PE或LE的形式。其实 , MySQL的存储引擎在实现数据管理时 , 也是在文件系统之上布设文件格式。对于文件而言在逻辑层上还会再次组织成一个逻辑单位 , 这个逻辑单位称为mysql的数据块(datablock), 数据块一般为16k。对于关系型数据库 , 数据是按行存储的 , 一般一行数据都是存储在一起的。因此 , MySQL在内部有一个数据块datablock , 在datablock就存储一行数据 , 一个数据块里可能存放了n行数据。将来在查询加载一行数据时 , 内核会把一整个数据块加载至内存中。而mysql存储引擎 , 就从中挑出来某一行返回给查询者，是这样实现查询的。所以整个存储是以datablock在底层为其最终级别的。
1.4 系统文件层
一个数据库提供了3种视图 , 物理视图就是看到的对应的文件系统存储为一个个的文件 , MySQL的数据文件类型常见的有redo log重做日志 、 undo log撤销日志 、 data是真正的数据文件、 index是索引文件 , binary log是二进制日志文件、 error log错误日志、 query log查询日志、 slow query log慢查询日志 、在复制架构中还存在中继日志文件，跟二进制属于同种格式。逻辑视图这是在mysql接口上通过存储引擎把mysql文件尤其是data文件 , 给它映射为一个个关系型数据库应该具备组成部分。比如表 , 一张表在底层是一个数据文件而已 , 里面组织的就是datablock , 然后映射为磁盘上文件系统的block , 最终再次映射为本地扇区的存储。但是整个mysql需要把他们映射成一个二维关系表的形式 , 需要依赖sql接口以及存储引擎共同实现。所以 , 把底层数据文件映射成关系型数据库的组件就是逻辑视图。DBA就是关注内部组件是如何运作的 , 并且定义、配置其运作模式 ,而连接器都是终端用户通过连接器的模式进入数据库来访问数据。数据集可能非常大 , 每一类用户可能只有一部分数据的访问权限。这个时候最终的终端用户所能访问到的数据集合称作用户视图 。为了保证MySQL运作 , 提供了管理和服务工具 , 例如:备份恢复工 具，安全工具，复制工具，集群服务，管理、配置、迁移、元数据等工具
系统文件层主要包括MySQL中存储数据的底层文件 , 与上层的存储引擎进行交互 , 是文件的物理存储层。其存储的文件主要有: 日志文件、数据文件、配置文件、MySQL的进行pid文件和socket文件等。
1.4.1 日志文件
MySQL中的日志主要包括 : 错误日志、通用查询日志、二进制日志、慢查询日志等。
错误日志
主要存储的是
MySQL运行过程中产生的错误信息。可以使用下面的SQL语句来查看MySQL中的错误日志。show variables like '%log_error%';通用查询日志 主要记录
MySQL运行过程中的一般查询信息 , 可以使用下面的SQL语句来查看MySQL中的通用查询日志文件。show variables like '%general%';二进制日志
主要记录对
MySQL数据库执行的插入、修改和删除操作 , 并且也会记录SQL语句执行的时间、执行的时长 ,但是二进制日志不记录select、show等不修改数据库的SQL。主要用于恢复数据库的数据和实现MySQL主从复制。查看二进制日志是否开启。
show variables like '%log_bin%';查看二进制日志的参数
show variables like '%binlog%'查看日志文件
show binary logs;慢查询日志
慢查询主要记录的是执行时间超过指定时间的
SQL语句，这个时间默认是10秒。查看是否开启慢查询日志
show variables like '%slow_query%';查看慢查询设置的时长
show variables like '%long_query_time%'
1.4.1.1 日志模块
如前面所说 , MySQL整体分为Server层和数据引擎层, 而每层也对应了自己的日志文件。如果选用的是InnoDB 引擎，对应的是redo log文件。Server层则对应了binlog文件。
1.4.1.1.1 redo log
redo log是InnoDB特有日志 , 为什么要引入redo log呢 , 想象这样一个场景 , MySQL为了保证持久性是需要把数据写入磁盘文件的。我们知道 , 在写入磁盘时 , 会进行文件的IO , 查找操作 , 如果每次更新操作都这样的话 , 整体的效率就会特别低 , 根本没法使用。
既然直接写入磁盘不行 , 解决方法就是先写进内存 , 在系统空闲时再更新到磁盘就可以了。但光更新内存不行 ,假如系统出现异常宕机和重启 , 内存中没有被写入磁盘的数据就会被丢掉 ,数据的一致性就出现问题了。这时redo log就发挥了作用 , 在更新操作发生时 , InnoDb会先写入redo log日志( 记录了数据发生了怎么样的改变) ,同时更新内存 , 最后在适当的时间再写入磁盘 , 一般是找系统空闲的时间做。先写日志 , 在写磁盘的操作 ,就是常说到的WAL(Write-Ahead-Logging)技术。
redo log 的出现 , 除了在效率上有了很大的改善 , 还保证了 MySQL 具有了 crash-safe 的能力 , 在发生异常情况下 , 不会丢失数据。
在具体实现上redo log的大小是固定的 , 可配置一组为 4 个文件 , 每个文件 1GB , 更新时对四个文件进行循环写入。
write pos记录当前写入的位置 , 写完就后移 , 当第写入第 4 个文件的末尾时 , 从第 0 号位置重新写入。
check point表示当前可以擦除的位置 , 当数据更新到磁盘时 , check point 就向后移动。
write pos和check point之间的位置 , 就是可以记录更新操作的空间。当write pos追上check point ,不在能执行新的操作 , 先让check point去写入一些数据。
可以将 innodb_flush_log_at_trx_commit 设置成 1 , 开启redo log持久化的能力。
1.4.1.1.2 binlog
binlog则是Server层的日志 , 主要用于归档 , 在备份、主备同步、恢复数据时发挥作用 , 常见的日志格式有 row, mixed, statement 三种。
可以通过 sync_binlog=1 开启binlog写入磁盘。
这里对binlog和redo进行下区分:
所有者不同 ,
binlog是Server层 , 所有引擎都可使用。redo log是InnoDB特有的。类型不同 ,
binlog是逻辑日志 , 记录的是语句的原始逻辑(比statement)。redo log是物理日志 , 记录某个数据页被做了怎样的修改。数据写入的方式不同 ,
binog日志会一直追加 , 而redo log是循环写入。功能不同 ,
binlog用于归档 , 而redo log用于保证crash-safe。
1.4.1.1.3 两阶段提交
下面执行器和InnoDB执行Update时内部流程:
以更新 update T set c=c+1 where ID=2; 语句为例:
执行器通过
InooDB引擎去ID所在行 ,ID为主键。引擎通过树搜索找到该行 , 如果该行所在数据页在内存中，返回给执行器。否则先从磁盘读入内存 , 然后再返回。执行器拿到引擎给的数据 , 将
C值加1, 等到新的一行 , 然后通过引擎接口重新写入新数据。引擎将该行更新到内存中 , 同时将该更新操作记录到
redo log中 , 并更改redo log的状态为prepare状态。然后告知执行器 , 在合适的时间提交事务。执行器生成这个操作的
binlog, 并将binlog写入磁盘。执行器调用引擎到的提交事务接口 , 将刚刚写入的
redo log改成commit状态 ,更新完成。
浅色为执行器执行 , 深色为引擎执行。
在更新内存后 , 将写入redo log拆分了成两个步骤: prepare和commit , 就是常说的两阶段提交。用于保证当有意外情况发生时 , 数据的一致性。
这里假设下，如果不采用两阶段提交会发生什么？
先写
redo log后写binlog。 假设在写入redo log后 ,MySQL发生异常重启 , 此时binlog没有写入。在重启后 , 由于redolog已经写入 , 此时数据库的内容是没有问题的。但此时 , 如果想要拿binlog进行备份或恢复 , 发现会少了最后一条的更新逻辑 , 导致数据不一致。先写
binlog后写redo log。binlog写入后 ,MySQL异常重启 ,redo log没有写入。此时重启后 , 发现redo log没有成功写入 , 认为这个事务无效 , 而此时binlog却多了一条更新语句 , 拿去恢复后自然数据也是不一致的。
再分析下两阶段提交的过程:
在写
redo log prepare阶段奔溃 , 时刻A的位置。重启后 , 发现redo log没写入 , 回滚此次事务。如果在写
binlog时奔溃 , 重启后 , 发现binlog未被写入 , 回滚操作。binlog写完 , 但在提交redo log的commit状态时发生crash。如果
redo log中事务完整 , 有了commit标识 , 直接提交。如果
redo log中只有完整的prepare, 判断对应binlog是否完整。完整 , 提交事务
不完整 , 回滚事务。
如何判断binlog是否完整？
statement格式binlog, 会有COMMIT; 标识row格式的binlog, 会有XID event. 标识在
5.6后 , 还有binlog-checksum参数 , 验证binlog正确性。
如何将redo log和 binlog 关联表示同一个操作？ 结构中有一个共同的数据字段，XID。 在崩溃恢复时 , 会按顺序扫描 redo log:
如果有
prepare, 又有commit的redo log, 直接提交。如果只有
prepare, 没有commit的redo log, 拿XID去binlog找对应的事务做判断。
数据写入后 , 最终落盘和redo log有无关系？
对于正常运行的
instance来说 , 内存中页被修改后 , 和磁盘的数据页不一致 , 称为脏页。而落盘的过程 , 是把内存中的数据页写入磁盘。对于
crash场景 ,InnoDB判断一个数据页是否丢失了更新 , 会将其读到内存 , 然后让redo log更新内存内容。更新完成后，内存页就变成脏页，然后回到第一种情况的状态。
redo log buffer和redo log的关系？
在一个事务的更新过程中 , 存在多个SQL语句 , 所以是要写多次日志的。 但在写的过程中 , 生产的日志要先保存起来 , 但在commit前 , 不能直接写到redo log中。 所以通过内存中redo log buffer先存redo log的日志。在commit时 , 将buffer中的内容写入redo log。
1.4.2 数据文件
数据文件中主要包括了: db.opt文件、frm文件、MYD文件、MYI文件、ibd文件、ibdata文件、ibdata1文件、ib_logfile0和ib_logfile1文件等。
db.opt文件 :主要记录当前数据库使用的字符集和检验规则等信息。frm文件 : 存储数据表的结构信息 , 主要是数据表相关的元数据信息 , 包括数据表的表结构定义信息 ,每张表都会有一个frm文件。值得注意的是:
MySQL8版本中的innodb存储引擎的表没有frm文件。 ( 后面专门写一些MySQL8新特性的文章 , 从使用到底层原理与MySQL5到底有何不同)。MYD文件 :MyISAM存储引擎专用的文件格式 , 主要存放MyISAM存储引擎数据表中的数据 , 每张MyISAM存储引擎表对应一个.MYD文件。MYI文件 :MyISAM存储引擎专用的文件格式 , 主要存放与MyISAM存储引擎数据表相关的索引信息 , 每张MyISAM存储引擎表对应一个.MYI文件。ibd文件 : 存放Innodb存储引擎的数据文件和索引文件 , 主要存放的是独享表空间的数据和索引 , 每张表对应一个.ibd文件。ibdata文件 : 存放Innodb存储引擎的数据文件和索引文件 , 主要存放的是共享表空间的数据和索引 , 所有表共用一个(或者多个).ibdata文件 , 可以根据配置来指定共用的.ibdata文件个数。ibdata1文件 :MySQL的系统表空间数据文件 , 主要存储MySQL的数据表元数据、Undo日志等信息。ib_logfile0和ib_logfile1文件 :MySQL数据库中的Redo log文件 , 主要用于MySQL实现事务的持久性。如果在某个时间点MySQL发生了故障 , 此时如果有脏页没有写入到数据库的ibd文件中 , 在重启MySQL的时候,MySQL会根据Redo Log信息进行重做 , 将写入Redo Log并且尚未写入数据表的数据进行持久化操作。
1.4.3 配置文件
用于存在MySQL所有的配置信息 , 在Unix/Linux环境中是my.cnf文件 , 在Windows环境中是my.ini文件。
1.4.4 pid文件
pid文件是存放MySQL进程运行时的进程号的文件 , 主要存在于Unix/Linux环境中 , 具体的存储目录可以在my.cnf或者my.ini文件中进行配置。
1.4.5 socket文件
socket文件和pid文件一样 , 都是MySQL在Unix/Linux环境中运行才会有的文件。在Unix/Linux环境中 ,客户端可以直接通过socket来连接MySQL。
2. 存储引擎
存储引擎(storage engine)是MySQL的专用称呼 , 数据库行业老大哥Oracle、SQL Server以及PostgreSQL等都没有存储引擎的说法。MySQL中的数据用各种不同的技术存储在文件(或者内存)中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力,此种技术称为存储引擎 。 MySQL支持多种存储引擎其中目前应用最广泛的是InnoDB和MyISAM两种。
2.1 MyISAM存储引擎
MyISAM是MySQL的默认数据库引擎(5.5版之前) , 由早期的ISAM(Indexed Sequential Access Method : 有索引的顺序访问方法)所改良。MyISAM拥有较高的插入、查询速度 , 但却有一个缺点 : 不支持事务处理(transaction) 。不过, 在这几年的发展下 , MySQL也导入了InnoDB(另一种数据库引擎) , 以强化参考完整性与并发违规处理机制 , 后来就逐渐取代MyISAM 。
MyISAM存储引擎特点
不支持事务
表级锁定, 不支持行锁。读取时对需要读到的所有表加锁,写入时则对表加排他锁
读写相互阻塞,写入不能读，读时不能写
只缓存索引
不支持外键约束
不支持聚簇索引
读取数据较快, 占用资源较少
不支持
MVCC(多版本并发控制机制)高并发崩溃恢复性较差
MySQL5.5.5前默认的数据库引擎
MyISAM存储引擎适用场景
只读(或者写较少)
表较小(可以接受长时间进行修复操作)
MyISAM引擎文件
tbl_name.frm表格式定义tbl_name.MYD数据文件tbl_name.MYI索引文件
2.2 InnoDB存储引擎
InnoDB是MySQL的数据库引擎之一 , 为MySQL AB发布binary的标准之一。InnoDB由Innobase Oy公司所开发 , 2006年五月时由甲骨文公司并购。与传统的ISAM与MyISAM相比 , InnoDB的最大特色就是支持了ACID兼容的事务(Transaction)功能 , 类似于PostgreSQL。目前InnoDB采用双轨制授权 , 一是GPL授权 , 另一是专有软件授权。
InnoDB引擎特点
行级锁
支持事务，适合处理大量短期事务
读写阻塞与事务隔离级别相关
可缓存数据和索引 。
InnoDB提供了专门的缓冲池 , 实现了缓冲管理 , 不仅能缓冲索引也能缓冲数据 , 常用的数据可以直接从内存中处理 , 比从磁盘获取数据处理速度要快。在专用数据库服务器上 , 通常会将最多80％的物理memory分配给缓冲池。支持聚簇索引
崩溃恢复性更好
支持
MVCC高并发从
MySQL5.5后支持全文索引
InnoDB数据库文件
表空间 : 用于存储多个
ibd数据文件 , 用于存储表的记录和索引。一个文件包含多个段。所有
InnoDB表的数据和索引放置于同一个表空间中数据文件: ibdata1, ibdata2。存放在datadir定义的目录下, 默认为/var/lib/mysql 表格式定义: tb_name.frm,存放在datadir定义的每个数据库对应的目录下, 默认为/var/lib/mysql/database_name/tb_name.frm可以使用参数
innodb_file_per_table=ON(MariaDB 5.5以后版是默认值) ，开启每个表单独使用一个表空间存储表的数据和索引。两类文件放在对应每个数据库独立目录中数据文件(存储数据和索引): tb_name.ibd 表格式定义: tb_name.frm 文件默认所在目录为: /var/lib/mysql/database_name
段: 用于管理多个
Extent, 分为数据段(Leaf node segment) 、索引段(Non-leaf node segment)、回滚段(Rollback segment) 。一个表至少会有两个segment: 一个管理数据 , 一个管理索引。区 : 由连续的页组成的空间 , 每个区大小都为1MB。为了保证区中页的连续性 ,
InnoDB存储引擎一次从磁盘中申请4~5个区。在默认情况下 ,InnoDB存储引擎页大小为16KB, 即一个区中一共有64个连续的页。页 :
InnoDB磁盘管理的最小单位 , 默认每个页大小为16KB。常见的页类型有 : 数据页、undo页、系统页、事务数据页、BLOB 对象页。行 : 包含了记录的字段值 , 事务ID(
Trx id)、滚动指针(Roll pointer)、字段指针(Field pointers)等信息。Antelope是原始的InnoDB文件格式 , 支持两种行格式:COMPACT和REDUNDANT;Barracuda是新的文件格式 , 支持InnoDB的所有行格式 , 包括新的行格式 :COMPRESSED和DYNAMIC。
2.3 其他存储引擎
Performance_Schema:MySQL数据库系统专用引擎 ,Performance_Schema数据库使用, 用户不能创建这种存储引擎的表。 数据库performance_Schema用于监控MySQL在一个较低级别的运行过程中的资源消耗、资源等待等情况。Memory: 将所有数据存储在RAM中 , 以便在需要快速查找参考和其他类似数据的环境中进行快速访问, 但使用时要考虑内存消耗。如果数据库重启或者发生崩溃, 表中的数据都将消失。适用存放临时数据。引擎以前被称为HEAP引擎。MRG_MyISAM: 使MySQL DBA或开发人员能够对一系列相同的MyISAM表进行逻辑分组 , 并将它们作为一个对象引用 ,但是他的内部没有数据, 真正的数据依然是MyISAM引擎的表中 , 但是可以直接进行查询、删除更新等操作。适用于VLDB(Very Large Data Base)环境 , 如数据仓库Archive: 为存储和检索大量很少参考的存档或安全审核信息 , 只支持SELECT和INSERT操作 ; 支持行级锁和专用缓存区。Archive拥有很好的压缩机制 , 比MyISAM、InnoDB存储引擎更加节约存储空间。可以用于: 日志记录，打卡记录，天气信息记录等不需要数据更新的场景。Federated联合 : 用于访问其它远程MySQL服务器一个代理 , 它通过创建一个到远程MySQL服务器的客户端连接 , 并将查询传输到远程服务器执行 ,而后完成数据存取, 提供链接单独MySQL服务器的能力 , 以便从多个物理服务器创建一个逻辑数据库。非常适合分布式或数据集市环境BDB: 可替代InnoDB的事务引擎 , 支持COMMIT、ROLLBACK和其他事务特性Cluster/NDB:MySQL的簇式数据库引擎 , 尤其适合于具有高性能查找要求的应用程序 , 这类查找需求还要求具有最高的正常工作时间和可用性CSV:CSV存储引擎 , 使用逗号分隔值格式将数据存储在文本文件中。可以使用CSV引擎以CSV格式导入和导出其他软件和应用程序之间的数据交换。会在MySQL安装目录data文件夹中 , 和该表所在数据库名相同的目录生成一个.CSV文件 , 它可以将CSV类型的文件当做表进行处理 , 相比其他存储引擎的文件内容 , 可以直接查看和编辑。BLACKHOLE: 黑洞存储引擎 , 接受数据但不存储数据 , 检索总是返回一个空集。该功能可用于分布式数据库设计, 数据自动复制 , 但不是本地存储。example: "stub"引擎 , 它什么都不做。可以使用此引擎创建表 , 但不能将数据存储在其中或从中检索。目的是作为例子来说明如何开始编写新的存储引擎。
2.4 管理存储引擎
查看mysql支持的存储引擎
show engines;查看当前默认的存储引擎
show variables like '%storage_engine%'; 设置默认的存储引擎
vim /etc/my.cnf
[mysqld]
default_storage_engine= InnoDB查看库中所有表使用的存储引擎
show table status from db_name;查看库中指定表的存储引擎
show table status like 'tb_name';
show create table tb_name;设置表的存储引擎:
CREATE TABLE tb_name(... ) ENGINE=InnoDB;
ALTER TABLE tb_name ENGINE=InnoDB;2.5 多版本并发控制MVCC
为了保证事务启动到结束整个生命周期看到的数据是一致的, 一般有两种方案:
MySQL对数据“读-写”的时候加锁 , 事务写这条数据时加上锁 , 其他事务读取的时候阻塞。MySQL可以对事务启动的时候 , 对数据库拍个"快照" , 那么事务运行过程中读取都从这个快照读取 , 不也是保证数据一致么。
第一种方案存在明显的问题, 加锁会引发阻塞, 从而降低数据库性能。而MySQL设计者们采用第二种, 也就是大名鼎鼎的MVCC, 它不仅能够解决不可重复读 , 还一定程度解决幻读的问题, 因为你整个数据库快照都有了 ,你就知道那个时刻的数据了。
MVCC , 即Multi-Version Concurrency Control(多版本并发控制)。它是一种并发控制的方法 , 一般在数据库管理系统中 , 实现对数据库的并发访问 , 在编程语言中实现事务内存。
通俗的讲 , 数据库中同时存在多个版本的数据 ,并不是整个数据库的多个版本，而是某一条记录的多个版本同时存在 ,在某个事务对其进行操作的时候，需要查看这一条记录的隐藏列事务版本id ,比对事务id并根据事物隔离级别去判断读取哪个版本的数据。简单理解 , 就是相当于给我们的MySQL数据库拍个“快照” , 定格某个时刻数据库的状态。
数据库隔离级别读已提交、可重复读 都是基于MVCC实现的 , 相对于加锁简单粗暴的方式, 它用更好的方式去处理读写冲突 , 能有效提高数据库并发性能。
MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能, 用更好的方式去处理读-写冲突 , 做到即使有读写冲突时 , 也能做到不加锁 , 非阻塞并发读 , 而这个读指的就是快照读 , 而非当前读。
快照读 : 快照读又叫普通读 , 也就是利用
MVCC机制读取快照中的数据。不加锁的简单的SELECT都属于快照读。既然是多版本 , 那么快照读读到的数据不一定是当前最新的数据 , 有可能是之前历史版本的数据。快照读是基于
MVCC实现的 , 提高了并发的性能，降低开销大部分业务代码中的读取都属于快照读
当前读 : 读取的是记录的最新版本 , 读取时会对读取的记录进行加锁 , 其他事务就有可能阻塞。加锁的
SELECT, 或者对数据进行增删改都会进行当前读。比如:SELECT * FROM user LOCK IN SHARE MODE; # 共享锁 SELECT * FROM user FOR UPDATE; # 排他锁 INSERT INTO user values ... # 排他锁 DELETE FROM user WHERE ... # 排他锁 UPDATE user SET ... # 排他锁update、delete、insert语句虽然没有select, 但是它们也会先进行读取 , 而且只能读取最新版本。
2.5.1 基础概念介绍
悲观锁 : 悲观并发控制(又名"悲观锁" ,
PessimisticConcurrency Control,缩写"PCC")是一种并发控制的方法。它可以阻止一个事务以影响其他用户的方式来修改数据。如果一个事务执行的操作读某行数据应用了锁 , 那只有当这个事务把锁释放，其他事务才能够执行与该锁冲突的操作。事实上我们常说的悲观锁并不是一种实际的锁 , 而是一种并发控制的思想 , 悲观并发控制对于数据被修改持悲观的态度 , 认为数据被外界访问时 , 必然会产生冲突 , 所以在数据处理的过程中都采用加锁的方式来保证对资源的独占。数据库悲观锁的加锁流程大致如下:
开始事务后 , 按照操作类型给需要加锁的数据申请加某一类锁: 例如共享行锁等
加锁成功则继续后面的操作 , 如果数据已经被加了其他的锁 , 而且和现在要加的锁冲突，则会加锁失败(例如已经加了排他锁) , 此时需等待其他的锁释放(可能出现死锁)
完成事务后释放所加的锁
优点:
悲观并发控制采取的是保守策略 : " 先取锁 , 成功了才访问数据" , 这保证了数据获取和修改都是有序进行的 , 因此适合在写多读少的环境中使用。当然使用悲观锁无法维持非常高的性能 ,但是在乐观锁也无法提供更好的性能前提下 , 悲观锁却可以做到保证数据的安全性。
缺点:
由于需要加锁 , 而且可能面临锁冲突甚至死锁的问题 , 悲观并发控制增加了系统的额外开销 , 降低了系统的效率 , 同时也会降低了系统的并行性。
乐观锁 : 乐观并发控制(又名"乐观锁" ,
OptimisticConcurrency Control, 缩写"OCC")是一种并发控制的方法。它假设多用户并发的事务在处理时不会彼此互相影响 , 各事务能够在不产生锁的情况下处理各自影响的那部分数据。乐观并发控制对数据修改持乐观态度 , 认为即使在并发环境中 , 外界对数据的操作一般是不会造成冲突 , 所以并不会去加锁 , 而是在提交数据更新之前 , 每个事务会先检查在该事务读取数据后 , 有没有其他事务又修改了该数据。如果其他事务有更新的话 , 则让返回冲突信息 , 让用户决定如何去做下一步 , 比如说重试或者回滚。
可以看出 , 乐观锁其实也不是实际的锁，甚至没有用到锁来实现并发控制 , 而是采取其他方式来判断能否修改数据。乐观锁一般是用户自己实现的一种锁机制，虽然没有用到实际的锁 , 但是能产生加锁的效果。
实现方式
CAS(比较与交换 ,Compare and swap)是一种有名的无锁算法。无锁编程 , 即不使用锁的情况下实现多线程之间的变量同步 , 也就是在没有线程被阻塞的情况下实现变量的同步 , 所以也叫非阻塞同步(Non-blocking Synchronization)。实现非阻塞同步的方案称为“无锁编程算法”(Non-blocking algorithm)。乐观锁基本都是基于
CAS(Compare and swap)算法来实现的。我们先来看下CAS过程 , 一个CAS操作的过程可以用以下c代码表示:intcas(long*addr,longold,longnew) { /* Executes atomically. */ if(*addr!= old) return0; *addr= new; return1; }CAS有3个操作数 , 内存值V, 旧的预期值A, 要修改的新值B。当且仅当预期值A和内存值V相同时 , 将内存值V修改为B, 否则什么都不做。整个CAS操作是一个原子操作 , 是不可分割的。乐观锁的实现就类似于上面的过程 , 主要有以下几种方式 :
版本号标记: 在表中新增一个字段(
version) , 用于保存版本号。获取数据的时候同时获取版本号 , 然后更新数据的时候用以下命令:updatexxx set version=version+1,… where … version="old version" and ....。这时候通过判断返回结果的影响行数是否为0来判断是否更新成功 , 更新失败则说明有其他请求已经更新了数据了。时间戳标记 : 和版本号一样 , 只是通过时间戳来判断。一般来说很多数据表都会有更新时间这一个字段 , 通过这个字段来判断就不用再新增一个字段了。
待更新字段 : 如果没有时间戳字段 , 而且不想新增字段 , 那可以考虑用待更新字段来判断 , 因为更新数据一般都会发生变化 , 那更新前可以拿要更新的字段的旧值和数据库的现值进行比对 , 没有变化则更新。
所有字段标记 : 数据表所有字段都用来判断。这种相当于就、不仅仅对某几个字段做加锁了 , 而是对整个数据行加锁 , 只要本行数据发生变化，就不进行更新。
优点 : 乐观并发控制没有实际加锁 , 所以没有额外开销 , 也不错出现死锁问题 , 适用于读多写少的并发场景 , 因为没有额外开销, 所以能极大提高数据库的性能。
缺点 :乐观并发控制不适合于写多读少的并发场景下 , 因为会出现很多的写冲突 , 导致数据写入要多次等待重试 , 在这种情况下 , 其开销实际上是比悲观锁更高的。而且乐观锁的业务逻辑比悲观锁要更为复杂 , 业务逻辑上要考虑到失败 , 等待重试的情况 , 而且也无法避免其他第三方系统对数据库的直接修改的情况。
事务版本号(
trx_id)事务每次开启前 , 都会从数据库获得一个自增长的事务
ID,可以从事务ID判断事务的执行先后顺序。这就是事务版本号。InnoDB里面每个事务有一个唯一的事务ID, 叫作transaction id。它是在事务开始的时候向InnoDB的事务系统申请的，是按申请顺序严格递增的。隐式字段
对于
InnoDB存储引擎 , 每一行记录都有两个隐藏列trx_id、roll_pointer, 如果表中没有主键和非NULL唯一键时 , 则还会有第三个隐藏的主键列row_id。undo log回滚日志(
undo log) 用于记录数据被修改前的信息。在表记录修改之前 , 会先把数据拷贝到undo log里 , 如果事务回滚 , 即可以通过undo log来还原数据。可以这样认为 , 当
delete一条记录时undo log中会记录一条对应的insert记录 , 当update一条记录时,它记录一条对应相反的update记录。undo log主要用途 : 1. 事务回滚时，保证原子性和一致性 ; 2. 用于MVCC快照读。版本链
多个事务并行操作某一行数据时 , 不同事务对该行数据的修改会产生多个版本, 然后通过回滚指针(
roll_pointer) , 连成一个链表 , 这个链表就称为版本链。
通过版本链我们就可以看出事务版本号、表格隐藏的列和
undo log它们之间的关系。我们再来小分析一下。假设现在有一张
core_user表 , 表里面有一条数据 ,id为1, 名字为孙权:
现在开启一个事务
A, 对core_user表执行update core_user set name ="曹操" where id=1,会进行如下流程操作:首先获得一个事务
ID=100把
core_user表修改前的数据,拷贝到undo log修改
core_user表中 ,id=1的数据 , 名字改为曹操把修改后的数据事务
Id=101改成当前事务版本号 , 并把roll_pointer指向undo log数据地址。
一致性视图(
ReadView) :ReadView就是事务在使用MVCC机制进行快照读操作时产生的一致性视图。实际上在innodb中 , 每个SQL语句执行前都会得到一个Read View。 它主要是用来做可见性判断的 , 即判断当前事务可见哪个版本的数据。重要属性
m_ids/trx_ids: 指的是在创建ReadView时 , 当前数据库中活跃事务的事务id列表 , 注意是一个列表 , "活跃事务"指的就是 , 启动了但还没提交的事务。数据结构为一个List。min_trx_id: 指的是在创建ReadView时 , 当前数据库中「活跃事务」中事务 id 最小的事务 , 也就是m_ids的最小值。max_trx_id: 这个并不是m_ids的最大值 , 而是创建ReadView时当前数据库中应该给下一个事务的 id 值，也就是全局事务中最大的事务 id 值 + 1；creator_trx_id: 指的是创建该ReadView的事务的事务id, 只有在对表中的记录做改动时(执行INSERT、DELETE、UPDATE这些语句时)才会为事务分配事务id,否则在一个只读事务中的事务id值都默认为0。
匹配规则
如果在绿色部分(
trx_id < min_limit_id)，表明生成该版本的事务在生成Read View前 , 已经提交(因为事务ID是递增的) , 所以该版本可以被当前事务访问。如果在红色部分(
trx_id>= max_limit_id) , 表明生成该版本的事务在生成ReadView后才生成 , 所以该版本不可以被当前事务访问。如果
min_limit_id =<trx_id< max_limit_id,需腰分3种情况讨论如果
m_ids包含trx_id,则代表Read View生成时刻 ,这个事务还未提交,但是如果数据的trx_id等于creator_trx_id的话,表明数据是自己生成的,因此是可见的。如果
m_ids包含trx_id, 并且trx_id不等于creator_trx_id,则Read View生成时, 事务未提交 ,并且不是自己生产的 ,所以当前事务也是看不见的；如果
m_ids不包含trx_id, 则说明你这个事务在Read View生成之前就已经提交了,修改的结果 ,当前事务是能看见的。
2.5.2 MVCC原理分析
MVCC= 隐藏字段 ( rowId+trxId)+ undo log + ReadView
MVCC具体流程如下 :
获取事务自己的版本号，即事务ID
获取
Read View查询得到的数据，然后
Read View中的事务版本号进行比较。如果不符合
Read View的可见性规则 , 即就需要Undo log中历史快照;最后返回符合规则的数据
InnoDB实现MVCC , 是通过Read View+ Undo Log 实现的 , Undo Log保存了历史快照 ,Read View可见性规则帮助判断当前版本的数据是否可见。
2.5.2.1 读已提交 ( RC）隔离级别，存在不可重复读问题的分析历程
创建
core_user表，插入一条初始化数据,如下:
隔离级别设置为读已提交(
RC) ,事务A和事务B同时对core_user表进行查询和修改操作。事务A: select * fom core_user where id=1 事务B: update core_user set name =”曹操”执行流程如下:
最后事务
A查询到的结果是 ,name=曹操的记录 , 我们基于MVCC, 来分析一下执行流程:A开启事务 , 首先得到一个事务ID为100B开启事务 , 得到事务ID为101事务
A生成一个Read View,read view对应的值如下然后回到版本链 : 开始从版本链中挑选可见的记录:
由图可以看出 , 最新版本的列
name的内容是孙权, 该版本的trx_id值为100。开始执行read view可见性规则校验:min_limit_id(100)=<trx_id ( 100）<102; creator_trx_id = trx_id =100;由此可得 ,
trx_id=100的这个记录 , 当前事务是可见的。所以查到是name为孙权的记录。事务
B进行修改操作 , 把名字改为曹操。把原数据拷贝到undo log,然后对数据进行修改 , 标记事务ID和上一个数据版本在undo log的地址。
提交事务
事务
A再次执行查询操作 , 新生成一个Read View ,Read View对应的值如下然后再次回到版本链, 从版本链中挑选可见的记录。
从图可得 , 最新版本的列
name的内容是曹操, 该版本的trx_id值为101。开始执行Read View可见性规则校验:min_limit_id(100)=<trx_id ( 101）<max_limit_id ( 102); 但是,trx_id=101，不属于m_ids集合因此 ,
trx_id=101这个记录 , 对于当前事务是可见的。所以SQL查询到的是name为曹操的记录。综上所述 , 在读已提交 ( RC）隔离级别下 , 同一个事务里 , 两个相同的查询 , 读取同一条记录(
id=1) ,却返回了不同的数据(第一次查出来是孙权，第二次查出来是曹操那条记录) , 因此RC隔离级别 , 存在不可重复读并发问题。
2.5.2.2 可重复读 ( RR）隔离级别 , 解决不可重复读问题的分析
在RR隔离级别下 , 是如何解决不可重复读问题的呢？我们一起再来看下，
还是2.5.2.2小节那个流程，还是这个事务A和事务B，如下:
2.5.2.2.1 不同隔离级别下，Read view的工作方式不同
实际上 , 各种事务隔离级别下的Read view工作方式 , 是不一样的 ,RR可以解决不可重复读问题 , 就是跟Read view工作方式有关。
在读已提交(
RC)隔离级别下，同一个事务里面，每一次查询都会产生一个新的Read View副本 , 这样就可能造成同一个事务里前后读取数据可能不一致的问题(不可重复读并发问题)。在可重复读(
RR)隔离级别下 , 一个事务里只会获取一次read view, 都是副本共用的 , 从而保证每次查询的数据都是一样的。
2.5.2.2.2 实例分析
我们穿越下 , 回到刚2.5.2.1的例子 ,然后执行第2个查询的时候:
事务A再次执行查询操作 , 复用老的Read View副本 ,Read View对应的值如下
然后再次回到版本链 , 从版本链中挑选可见的记录:
从图可得 , 最新版本的列name的内容是曹操 , 该版本的trx_id值为101。开始执行read view可见性规则校验 :
min_limit_id(100)=<trx_id ( 101）<max_limit_id ( 102);
因为m_ids{100,101}包含trx_id ( 101），
并且creator_trx_id (100) 不等于trx_id ( 101）所以, trx_id=101这个记录 , 对于当前事务是不可见的。这时候呢 , 版本链roll_pointer跳到下一个版本, trx_id=100这个记录 , 再次校验是否可见
min_limit_id(100)=<trx_id ( 100）< max_limit_id ( 102);
因为m_ids{100,101}包含trx_id ( 100），
并且creator_trx_id (100) 等于trx_id ( 100）所以 , trx_id=100这个记录 , 对于当前事务是可见的。即在可重复读(RR)隔离级别下 , 复用老的Read View副本 , 解决了不可重复读的问题。
2.5.3 MVCC是否解决幻读问题
2.5.3.1 RR级别下，一个快照读的例子，不存在幻读问题
由图可得，步骤2和步骤6查询结果集没有变化，看起来RR级别是已经解决幻读问题啦~
2.5.3.2 RR级别下，一个当前读的例子
假设现在有个account表，表中有4条数据 , RR级别。
开启事务A , 执行当前读 , 查询
id>2的所有记录。再开启事务
B, 插入id=5的一条数据。
流程如下:
显然 , 事务B执行插入操作时 , 阻塞了~ 因为事务A在执行select ... lock in share mode(当前读)的时候 , 不仅在id = 3,4 这2条记录上加了锁 , 而且在id > 2 这个范围上也加了间隙锁。
因此 , 我们可以发现 , RR隔离级别下 , 加锁的select , update , delete等语句 , 会使用间隙锁+ 临键锁 , 锁住索引记录之间的范围 , 避免范围间插入记录 , 以避免产生幻影行记录 , 那就是说RR隔离级别解决了幻读问题？？？
2.5.3.3 这种特殊场景，似乎有幻读问题
其实 , 上图事务A中 , 多加了update account set balance=200 where id=5;这步操作 , 同一个事务 , 相同的sql , 查出的结果集不同了 , 这个结果 , 就符合了幻读的定义~
这个问题，亲爱的朋友，你觉得它算幻读问题吗？因此，本质上来讲，那RR隔离级别还是可能存在幻读问题。