java面试之Mysql基础二

java面试之Mysql基础二

1.架构

体系架构

MySQL Server 架构自顶向下大致可以分网络连接层、服务层、存储引擎层和系统文件层。

网络连接层

客户端连接器：提供与 MySQL 服务器建立支持。目前几乎支持所有主流的服务端编程技术，例如常见的 Java、C、Python、.NET 等，它们通过各自 API 技术与 MySQL 建立连接。

服务层

服务层是 MySQL Server 的核心，主要包含系统管理和控制工具、连接池、SQL 接口、解析器、查询优化器和缓存六个部分。

• 连接池（Connection Pool）：负责存储和管理客户端与数据库的连接，一个线程负责管理一个连接。

• 系统管理和控制工具（Management Services & Utilities）：例如备份恢复、安全管理、集群管理等。

• SQL 接口（SQL Interface）：用于接受客户端发送的各种SQL 命令，并且返回用户需要查询的结果。比如DML、DDL、存储过程、视图、触发器等。

• 解析器（Parser）：负责将请求的 SQL 解析生成一个"解析树"。然后根据一些MySQL规则进一步检查解析树是否合法。

• 查询优化器（Optimizer）：当“解析树”通过解析器语法检查后，将交由优化器将其转化成执行计划（产生多种执行计划，最终数据库会选择最优化的方案去执行），然后与存储引擎交互。

• 缓存（Cache&Buffer）： 缓存机制是由一系列小缓存组成。比如表缓存，记录缓存，权限缓存，引擎缓存等。如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。

存储引擎层

存储引擎负责 MySQL 中数据的存储与提取，与底层系统文件进行交互。MySQL 存储引擎是插件式的，服务器中的查询执行引擎通过接口与存储引擎进行通信，接口屏蔽了不同存储引擎之间的差异 。最常见的是MyISAM 和 InnoDB。

系统文件层

该层负责将数据库的数据和日志存储在文件系统之上，并完成与存储引擎的交互，是文件的物理存储层。主要包含日志文件、数据文件、配置文件、pid 文件、socket 文件等。

show variables like '%log_error%'; #查看错误日志
show variables like '%slow_query%'; #慢查询是否开启
show variables like '%long_query_time%';#慢查询时长，默认是 10 秒

运行机制

• 通过客户端/服务器通信协议与 MySQL 建立连接，MySQL 客户端与服务端的通信方式是 “ 半双工 ”。
• 客户端发送一条查询给服务器。
• 服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段。
• 服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划。
• MySQL根据优化器生成的执行计划，再调用存储引擎的API来执行查询。
• 将结果返回给客户端。

查询缓存

当判断缓存是否命中时，MySQL不会进行解析查询语句，而是直接使用SQL语句和客户端发送过来的其他原始信息。

如果查询语句中包含任何的不确定的函数，那么其查询结果不会被缓存。例如包含函数NOW()或者CURRENT_DATE()的查询不会缓存；包含任何用户自定义函数，存储函数，用户变量，临时表，mysql数据库中的系统表或者包含任何列级别权限的表，都不会被缓存。

在MySQL里查询缓存是由一个全局锁在控制，每次更新查询缓存的内存块都需要进行锁定。故query_cache_size的值设置在100MB以内即可。

对于一个更新频率非常低而只读查询频率非常高的场景下，打开查询缓存还是比较有优势的。

参数查看

# query_cache_type: 是否开启缓存功能，取值为ON, OFF(, DEMAND，默认值为ON。值为OFF或0时，查询缓存功能关闭；值为ON或1时，查询缓存功能打开，SELECT的结果符合缓存条件即会缓存，否则，不予缓存，显式指定SQL_NO_CACHE，不予缓存；值为DEMAND或2时，查询缓存功能按需进行，显式指定SQL_CACHE的SELECT语句才会缓存，其它均不予缓存。
show variables like '%query_cache_type%';
#最大能缓存的单条查询记录集大小，默认设置为1M
show variables like '%query_cache_limit%';
#缓存区总大小,允许设置query_cache_size的值最小为40K，默认1M，推荐设置为：64M
show variables like '%query_cache_size%';
#以上参数需要配置在my.ini中
#实时查看当前mysql中的数据情况
show status like '%qcache%';

存储引擎

MySQL5.5之前的默认存储引擎是MyISAM，5.5之后就改为了InnoDB。

show engines;#查看当前数据库支持的引擎信息
show variables like '%storage_engine%';#查看Mysql数据库默认的存储引擎 

特点	InnoDB	MyISAM	MEMORY	Archive	MERGE	NDB
存储限制	64TB	256TB	RAM	没有	没有	有
事务安全	是	否	否	否	否	是
锁机制	行锁(适合高并发)	表锁	表锁	行锁	表锁	行锁
B树索引	支持	支持	支持		支持	支持
哈希索引			支持
全文索引	支持(5.6版本之后)	支持
集群索引	支持
数据索引	支持		支持			支持
索引缓存	支持	支持	支持		支持	支持
数据可压缩		支持
空间使用	高	低	N/A		低	低
内存使用	高	低	中等		低	高
批量插入速度	低	高	高		高	高
支持外键	是	否
mvcc	是	否	否	否	否	否

InnoDB 存储结构

InnoDB的底层结构主要由2部分组成：内存结构和磁盘结构。

内存结构

内存结构主要包括 Buffer Pool、Change Buffer、Adaptive Hash Index 和 Log Buffer 四大组件。

Buffer Pool

缓冲池(buffer pool)是一种常见的降低磁盘访问的机制。常以页(page)为单位缓存数据，默认大小为 16K，采用链表结构管理页，算法是LRU。缓存最热的数据页(data page)与索引页(index page)。

Page 根据状态可以分为三种类型：

• free page ： 空闲 page，未被使用
• clean page：被使用 page，数据没有被修改过
• dirty page：脏页，被使用 page，数据被修改过，页中数据和磁盘的数据产生了不 一致

针对上述三种 page 类型，InnoDB 通过三种链表结构来维护和管理

• free list ：表示空闲缓冲区，管理 free page
• flush list：表示需要刷新到磁盘的缓冲区，管理 dirty page，内部 page 按修改时间排序。脏页即存在于 flush 链表，也在 LRU 链表中，但是两种互不影响，LRU 链表负 责管理 page 的可用性和释放，而 flush 链表负责管理脏页的刷盘操作。
• lru list：表示正在使用的缓冲区，管理 clean page 和 dirty page，缓冲区以 midpoint 为基点，前面链表称为 new 列表区，存放经常访问的数据，占 63%；后 面的链表称为 old 列表区，存放使用较少数据，占 37%。

改进型 LRU 算法

• 按照5:3的比例将Buffer pool空间划分成年轻代和老年代。年轻代的头部是经常被访问的数据，在老年代的尾部是很少被访问的数据。
• 从中间 midpoint 位置插入，如果数据很快被访问，那么 page 就会向 new 列表头部移动，如果数据没有被访问，会逐步向 old 尾部移动，等待淘汰。
• 每当有新的 page 数据读取到 buffer pool 时，InnoDb 引擎会判断是否有空闲页，如果有就将 free page 从 free list 列表删除，放入到 LRU 列表中。没有空闲页，就会根据 LRU 算法淘汰 LRU 链表默认的页，将内存空间释放分配给新的页。

show variables like '%innodb_buffer%'; #查看buffer pool参数
show variables like '%innodb_page_size%'; #查看page页大小
show variables like '%innodb_old%'; #查看lrulist中old列表参数

Change Buffer

在MySQL5.5之前，叫插入缓冲(insert buffer)，只针对insert做了优化；现在对delete和update也有效，叫做写缓冲(change buffer)。

Change Buffer是缓存那些不在buffer pool里的辅助索引的变化的特殊数据结构 。
在辅助索引发生改变时，如果辅助索引在buffer pool里面就会直接进行修改。如果发生变化的辅助索引页不在buffer pool里，则由Change Buffer先缓存这些辅助索引页的变更动作。等未来辅助索引页被读取时，再将数据再将数据合并(merge)恢复到缓冲池中的技术。写缓冲的目的是降低写操作的磁盘IO，提升数据库性能。

ChangeBuffer 占用 BufferPool 空间，默认占 25%，最大允许占 50%，可以根据读写业务量来进行调整。

show variables like '%innodb_change_buffer%';  #查看ChangeBuffer参数

写缓冲区，仅适用于非唯一普通索引页。如果在索引设置唯一性，在进行修改时，InnoDB 必须要做唯一性校验，因此必须查询磁盘， 做一次 IO 操作。会直接将记录查询到 BufferPool 中，然后在缓冲池修改，不会在 ChangeBuffer 操作。

自适应hash索引

InnoDB 存储引擎会自动根据访问的频率和模式来为某些页建立哈希索引，用于优化对 BP 数据的查询。

AHI是通过缓存池的B+树页构造而来，hash索引只能用来搜索等值的查询。

Log Buffer

日志缓冲区用来保存要写入磁盘上 log 文件（Redo/Undo）的数据，日志缓冲区的内容定期刷新到磁盘 log 文件中。

#日志缓冲区大小:当遇到 BLOB 或多行更新的大事务操作时，增加日志缓冲区可以节省磁盘 I/O，默认大小为16MB
show variables like '%innodb_log_buffer_size%'; 

磁盘结构

InnoDB 磁盘主要包含 Tablespaces，InnoDB Data Dictionary，Doublewrite Buffer、Redo Log 和 Undo Logs。

表空间

用于存储表结构和数据。表空间又分为系统表空间、独立表空间、 通用表空间、临时表空间、Undo 表空间等多种类型。

• 系统表空间包含 InnoDB 数据字典，Doublewrite Buffer，Change Buffer，Undo Logs 的存储区域。系统表空间是一个共享的表空间。该空间的数据文件通过参数 innodb_data_file_path 控制，默认值是 ibdata1:12M:autoextend（文件名为 ibdata1、12M、自动扩展）

• 独立表空间是一个单表表空间，默认开启。当 innodb_file_per_table 选项开启时，表将被创建于表空间中。否则， innodb 将被创建于系统表空间中。

• 通用表空间通过 create tablespace 语法创建的共享表空间。通用表空间可以创建于 mysql 数据目录外的其他表空间，其可以容纳多张表，且其支持所有的行格式。

  # 创建表空间ts1
  CREATE TABLESPACE ts1 ADD DATAFILE ts1.ibd Engine=InnoDB; 
  # 将表添加到ts1表空间
  CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1; 
  

• 撤销表空间由一个或多个包含 Undo 日志文件组成。InnoDB 使用的 undo 表空间由 innodb_undo_dataspaces 配置选项控制，默认为 0 。参数值为 0 表示使用系统表空间 ibdata1；大于 0 表示使用 undo 表空间 undo_001、undo_002 等。

• 临时表空间分为session temporary tablespaces 和global temporary tablespace两种。session temporary tablespaces 存储的是用户创建的临时表和磁盘内部的临时表。global temporary tablespace储存用户临时表的回滚段（rollback segments ）。MySQL 服务器正常关闭或异常终止时，临时表空间将被移除，每次启动时会被重新创建。

数据字典

InnoDB 数据字典由内部系统表组成，这些表用于查找表、索引和表字段等对象的元数据。元数据物理上位于 InnoDB 系统表空间中。

双写缓冲区

位于系统表空间，是一个存储区域。默认情况下启用双写缓冲区，在 BufferPool的 page 页刷新到磁盘真正的位置前，会先将数据存在 Doublewrite 缓冲区。如果在 page 页写入过程中出现操作系统、存储子系统或 mysqld 进程崩溃，InnoDB 可以在崩溃恢复期间从 Doublewrite 缓冲区中找到页面的一个备份。

要禁用 Doublewrite 缓冲区，可以将 innodb_doublewrite 设置为 0。使用 Doublewrite 缓冲区时建议将 innodb_flush_method 设 置为 O_DIRECT。

show variables like '%innodb_data_file_path%';#查看系统表空间
show variables like '%innodb_file_per_table %';#是否开启独立表空间
show variables like '%innodb_undo_dataspaces%';#是否开启撤销表空间
show variables like '%innodb_doublewrite%';#是否开启doublewrite 缓冲区
#设置O_DIRECT表示数据文件写入操作会通知操作系统不要缓存数据，也不要用预读，直接从Innodb Buffer写到磁盘文件；默认的 fdatasync意思是先写入操作系统缓存，然后再调用 fsync() 函数去异步刷数据文件与redo log的缓存信息；O_DSYNC表示innodb会使用O_SYNC方式打开和刷写redo log,使用fsync()刷写数据文件。
show variables like '%innodb_flush_method%';

Redo Log

一种基于磁盘的数据结构，用于在崩溃恢复期间更正不完整事务写入的数据。MySQL 以循环方式写入重做日志文件，记录 InnoDB 中所有对 Buffer Pool 修改的日志。当出现实例故障（像断电），导致数据未能更新到数据文件，则数据库重启时须 redo，重新把数据更新到数据文件。读写事务在执行的过程中，都会不断的产生 redo log。默认情况下，重 做日志在磁盘上由两个名为 ib_logfile0 和 ib_logfile1 的文件物理表示。

Undo Logs

撤消日志是在事务开始之前保存的被修改数据的备份，用于例外情况时回滚事务。撤消日志属于逻辑日志，根据每行记录进行记录。撤消日志存在于系统表空间、撤消表空间和临时表空间中。

mysql8.0 版本存储引擎

将 InnoDB 表的数据字典和 Undo 日志都从共享表空间 ibdata 中彻底分离出来了，以前需要 ibdata 中数据字典与独立表空间 ibd 文件中数据字典一致才行，8.0 版本就不需要了。

将 Doublewrite Buffer 从共享表空间 ibdata 中也分离出来了。

Buffer Pool 大小可以动态修改，无需重启数据库实例。

temporary 临时表空间也可以配置多个物理文件，而且均为 InnoDB 存储引擎并能创建索引，这样加快了处理的速度。

用户可以像 Oracle 数据库那样设置一些表空间，每个表空间对应多个物理文件，每个表空间可以给多个表使用，但一个表只能存储在一个表空间中。

InnoDB 数据文件

InnoDB所有数据都存放在一个表空间。表空间由段、区、页、行组成。

• 表空间：用于存储多个 ibd 数据文件，用于存储表的记录和索引。一个文件包含多个段。
• 段：用于管理多个 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。

如果要修改现有表的行模式，必须先将文件格式设置，再修改行模式。

set global innodb_file_format=Barracuda; #设置文件格式
ALTER TABLE tablename ROW_FORMAT=COMPRESSED #修改现有表的行模式

Undo Log

Undo Log：数据库事务开始之前，会将要修改的记录存放到 Undo 日志里，当事务回滚时或者数据库崩溃时，可以利用 Undo 日志，回滚数据，撤销未提交事务对数据库产生的影响。

Undo Log 产生和销毁：Undo Log 在事务开始前产生；事务在提交时，并不会立刻删除 undo log，innodb 会将该事务对应的 undo log 放入到删除列表中，后面会通过后台线程 purge thread 进行回收处理。

Undo Log 属于逻辑日志，记录一个变化过程。例如执行一个 delete，undolog 会记 录一个 insert；执行一个 update，undolog 会记录一个相反的 update。

Undo Log 存储：undo log 采用段的方式管理和记录。在 innodb 数据文件中包含一种 rollback segment 回滚段，内部包含 1024 个 undo log segment。可以通过下面一组参数来控制 Undo log 存储。

show variables like '%innodb_undo%';

作用

实现事务的原子性

Undo Log 是为了实现事务的原子性而出现的产物。事务处理过程中，如果出现了错误或者用户执 行了 ROLLBACK 语句，MySQL 可以利用 Undo Log 中的备份将数据恢复到事务开始之前的状态。

实现多版本并发控制（MVCC）

Undo Log 在 MySQL InnoDB 存储引擎中用来实现多版本并发控制。事务未提交之前，Undo Log 保存了未提交之前的版本数据，Undo Log 中的数据可作为数据旧版本快照供其他并发事务进行快照读。

事务 A 手动开启事务，执行更新操作，首先会把更新命中的数据备份到 Undo Buffer 中。

事务 B 手动开启事务，执行查询操作，会读取 Undo 日志数据返回，进行快照读。

Redo Log

Redo：顾名思义就是重做。以恢复操作为目的，在数据库发生意外时重现操作。

Redo Log：指事务中修改的任何数据，将最新的数据备份存储的位置（Redo Log），被称为重做日志。

Redo Log 的生成和释放：随着事务操作的执行，就会生成 Redo Log，在事务提交时会将产生 Redo Log 写入 Log Buffer，并不是随着事务的提交就立刻写入磁盘文件。等事务操作的脏页写入到磁盘之后，Redo Log 的使命也就完成了，Redo Log 占用的空间就可以重用（被覆盖写入）。Redo Log 文件内容是以顺序循环的方式写入文件，写满时则回溯到第一个文件，进行覆盖写。

工作原理

Redo Log 是为了实现事务的持久性而出现的产物。防止在发生故障的时间点，尚有脏页未写入表 的 IBD 文件中，在重启 MySQL 服务的时候，根据 Redo Log 进行重做，从而达到事务的未入磁盘数据进行持久化这一特性。

参数设置

每个 InnoDB 存储引擎至少有 1 个重做日志文件组（group），每个文件组至少有 2 个重做日志文 件，默认为 ib_logfile0 和 ib_logfile1。

show variables like '%innodb_log%';
#控制如何将日志缓冲区的内容写入并刷新到磁盘
show variables like '%innodb_flush_log_at_trx_commit%';
#控制日志刷新频率
show variables like '%innodb_flush_log_at_timeout%';
set global innodb_flush_log_at_trx_commit =2;

MySQL事务提交时刷redo log有三种策略：

（写日志文件： LogBuffer => OS cache），（刷盘：OS cache =>磁盘文件）

• 0 ： 每隔1秒写日志文件和刷盘操作，最多丢失1秒数据
• 1：事务提交，立即写日志文件和刷盘，数据不丢失，但是会频繁 IO 操作（默认）
• 2：事务提交，立即写日志文件，每隔 1 秒进行刷盘

Binlog

Redo Log 是属于 InnoDB 引擎所特有的日志，而 MySQL Server 也有自己的日志，即 Binary log（二进制日志），简称 Binlog。Binlog 是记录所有数据库表结构变更以及表数据修改的二进制 日志，不会记录 SELECT 和 SHOW 这类操作。Binlog 日志是以事件形式记录，还包含语句所执行的消耗时间。

文件结构

MySQL 的 binlog 文件中记录的是对数据库的各种修改操作，用来表示修改操作的数据结构是 Log event。不同的修改操作对应的不同的 log event。比较常用的 log event 有：Query event、Row event、Xid event 等。binlog 文件的内容是各种 Log event 的集合。 Binlog 文件中 Log event 结构如下图所示：

记录模式

文件记录模式有 STATEMENT、ROW 和 MIXED 三种：

• ROW（row-based replication, RBR）：日志中会记录每一行数据被修改的情况，然后在 slave 端对相同的数据进行修改。

  优点：能清楚记录每一个行数据的修改细节，能完全实现主从数据同步和数据的恢复。

  缺点：批量操作，会产生大量的日志，尤其是 alter table 会让日志暴涨。

• STATMENT（statement-based replication, SBR）：每一条被修改数据的 SQL 都会记录到 master 的 Binlog 中，slave 在复制的时候 SQL 进程会解析成和原来 master 端执行过的相同的 SQL 再次执行。

  优点：日志量小，减少磁盘 IO，提升存储和恢复速度

  缺点：在某些情况下会导致主从数据不一致，比如 last_insert_id()、now()等函数。

• MIXED（mixed-based replication, MBR）：以上两种模式的混合使用，一般会使用 STATEMENT 模式保存 binlog，对于 STATEMENT 模式无法复制的操作使用 ROW 模式保存 binlog，MySQL 会根据执行的 SQL 语句选择写入模式。

文件操作

根据记录模式和操作触发 event 事件生成 log event（事件触发执行机制）

将事务执行过程中产生 log event 写入缓冲区，每个事务线程都有一个缓冲区 Log Event 保存在一个 binlog_cache_mngr 数据结构中，在该结构中有两个缓冲区，一个是 stmt_cache，用于存放不支持事务的信息；另一个是 trx_cache，用于存放支持事务的信息。

事务在提交阶段会将产生的 log event 写入到外部 binlog 文件中。 不同事务以串行方式将 log event 写入 binlog 文件中，所以一个事务包含的 log event 信息在 binlog 文件中是连续的，中间不会插入其他事务的 log event。

show binary logs;# 查看所有binlog日志列表
show variables like '%log_bin%';#binlog参数查看
show master status; #查看master状态
flush logs;#重置(清空)所有binlog日志
purge binary logs to 'mysql-bin.000001'; #删除指定文件 
purge binary logs before '2021-07-08 00:00:00'; #删除指定时间之前的文件 
#使用 binlog 恢复数据
cd /usr/local/mysql/data;
mysqlbinlog --no-defaults --start-position=219 --stop-position=1496  mysql-bin.000001 | mysql -uroot -proot

场景

主从复制：在主库中开启 Binlog 功能，这样主库就可以把 Binlog 传递给从库，从库拿到 Binlog 后实现数据恢复达到主从数据一致性。

数据恢复：通过 mysqlbinlog 工具来恢复数据。

Redo Log 和 Binlog 区别

Redo Log 是属于 InnoDB 引擎功能，Binlog 是属于 MySQL Server 自带功能，并且是以二进制文件记录。

Redo Log 属于物理日志，记录该数据页更新状态内容，Binlog 是逻辑日志，记录更新过程。

Redo Log 日志是循环写，日志空间大小是固定，Binlog 是追加写入，写完一个写下一个，不 会覆盖使用。

Redo Log 作为服务器异常宕机后事务数据自动恢复使用，Binlog 可以作为主从复制和数据恢复使用。

Binlog 没有自动 crash-safe 能力。

2.事务

在关系型数据库管理系统中，一个逻辑工作单元要成为事务，必须满足这 4 个特性。即所谓的 ACID： 原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）。

ACID

原子性：一个事务（transaction）中的所有操作要么全部完成，要么全部不完成，不会结束在中间某个环节。即事务不可分割、不可约简。

一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。

隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。

持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

并发事务的问题

更新丢失：当两个或多个事务更新同一行记录，会产生更新丢失现象。

​ 回滚覆盖：一个事务回滚操作，把其他事务已提交的数据给覆盖了。

​ 提交覆盖：一个事务提交操作，把其他事务已提交的数据给覆盖了。

脏读 ：表示一个事务能够读取另一个事务中还未提交的数据。比如，某个事务尝试插入记录 A，此时该事务还未提交，然后另一个事务尝试读取到了记录 A。

不可重复读 ：是指在一个事务内，多次读同一数据，后面读取的跟前面读取的不一致。

幻读 ：指同一个事务内多次查询返回的结果集不一样。比如同一个事务 A 第一次查询时候有 n 条记录，但是第二次同等条件下查询却有 n+1 条记录，这就好像产生了幻觉。发生幻读的原因也是另外一个事务新增或者删除或者修改了第一个事务结果集里面的数据，同一个记录的数据内容被修改了，所有数据行的记录就变多或者变少了。

事务隔离级别

为了解决并发事务问题，MySQL 数据库是通过事务隔离级别来解决的。事务隔离性是采用锁来实现，对相应操作加不同的锁。MySQL 事务隔离是在 MySQL. ini 配置文件里添加的，在文件的最后添加：transaction-isolation=REPEATABLE-READ。

可用的配置值：READ-UNCOMMITTED、READ-COMMITTED、REPEATABLE-READ、SERIALIZABLE。

• READ-UNCOMMITTED：读未提交，最低隔离级别、事务未提交前，就可被其他事务读取（出现幻读、脏读、不可重复读）。
• READ-COMMITTED：读已提交，Oracle、SQLServer 默认级别，一个事务提交后才能被其他事务读取到（解决了脏读，会造成幻读、不可重复读）。
• REPEATABLE-READ：可重复读，默认级别，保证多次读取同一个数据时，其值都和事务开始时的内容是一致，禁止读取到别的事务未提交的数据（解决了脏读、不可重复读，会造成幻读）。
• SERIALIZABLE：串行化，通过强制事务排序，解决相互冲突。该隔离级别能防止脏读、不可重复读、幻读。导致大量超时现象和锁竞争，效率低下。

一般使用时，建议采用默认隔离级别，然后存在一些并发问题，可以通过悲观锁、乐观锁等实现处理。

#查看 MySQL 当前数据库的事务隔离级别
show variables like 'tx_isolation';
select @@tx_isolation;
#会话设置
set tx_isolation='READ-COMMITTED'; 
#全局设置加上global
set global tx_isolation='READ-UNCOMMITTED';

3.事务控制的演进

全局排队

完全顺序执行所有事务的数据库操作，不需要加锁。

数据库某个时刻只处理一个事务操作，特点是强一致性，处理性能低。

排他锁

引入锁之后可以支持并发处理事务，如果事务之间涉及到相同的数据项时，会使用排他锁（互斥锁）。先进入的事务独占数据项，其他事务被阻塞，等待前面事务释放锁。

读写锁

读写锁是进一步细化锁的颗粒度，区分读操作和写操作，让读和读之间不加锁，而读和写、写和读、写和写这几种之间还是要加排他锁。

MVCC

多版本控制 MVCC(Mutil-Version Concurrency Control)，也就是 Copy on Write 的思想。MVCC 除了支持读和读并行，还支持读和写、写和读的并行，但为了保证一致性，写和写是无法并行的。它是一种提高并发的技术，一般在数据库管理系统中，实现对数据库并发访问。

在Mysql的InnoDB引擎中是指在已提交读(READ COMMITTD)和可重复读(REPEATABLE READ)这两种隔离级别下的事务对于SELECT操作会访问版本链中的记录过程。这就使得别的事务可以修改这条记录，反正每次修改都会在版本链中记录。SELECT可以去版本链中拿记录，这就实现了读-写，写-读的并发执行，提升了系统的性能。

MVCC的实现，是通过保存数据在某个时间点的快照来实现的。

在 MVCC 并发控制中，读操作可以分为两类: 快照读（Snapshot Read）与当前读 （Current Read）。

快照读：读取的是记录的快照版本（有可能是历史版本），不用加锁。（select）

当前读：读取的是记录的最新版本，并且当前读返回的记录，都会加锁，保证其他事务不会再并发修改这条记录。（select… for update 或 lock in share mode）

实现原理

版本链

• trx_id：存储每次对某条聚簇索引记录进行修改时的事务id
• roll_pointer：每次对哪条聚簇索引记录修改时，会把老版本写入undo日志中。roll_pointer存了一个指针，指向这条聚簇索引记录的上一个版本位置，通过它来获得上一个版本的记录信息。(注意插入操作没有老版本)

ReadView

有个列表存储系统中当前活跃着的读写事务，即begin了还未提交的事务。通过此列表来判断记录的某个版本是否对当前事务可见。

假设当前列表里的事务id为[80,100]

• 如果要访问的记录版本事务id为50，比当前列表最小的id80小，说明这个事务在之前提交了，所以对当前活动事务来说是可访问的
• 如果要访问的记录版本事务id为90，发现此事务在列表id最大值和最小值之间，说明此事务还未提交，所以版本不能被访问
• 如果要访问的记录版本事务id为110，比事务列表最大id100大，说明这个版本是在ReadView生成之后才发生的，所以不能被访问

先找最近记录，如果最近这一条记录事务id不符合条件，再去找上一个版本再比较当前事务id和这个版本事务id看能不能访问，以此类推直到返回可见版本或者结束。

在已提交读隔离级别下：

有一个事务id为100的事务，修改了name,使得的name等于小明2，但是事务还没提交。则此时的版本链是

此时另一个事务发起了select 语句要查询id为1的记录，那此时生成的ReadView 列表只有[100]。那就去版本链找最近的一条，发现trx_id是100,也就是name为小明2的那条记录，发现在列表内，所以不能访问。通过指针继续找下一条，name为小明1的记录，发现trx_id是60，小于列表中的最小id,所以可以访问，直接访问结果为小明1。

此时把id为100的事务提交了，并且新建了一个事务id为110也修改id为1的记录，并且不提交事务。这时候版本链是

此时另一个事务发起了select语句又执行了一次查询id为1的记录，会重新一个ReadView，那活动事务列表中值变成了[110]。去版本链通过trx_id对比查找到合适的结果就是小明2。

在可重复读隔离级别下：

这时ReadView还是第一次select时生成的ReadView，也就是列表值还是[100]。所以select的结果是小明1，第二次select结果和第一次一样，所以叫可重复读！

MVCC对于已提交读隔离级别下的事务在每次查询的开始都会生成一个独立的ReadView，而可重复读隔离级别则在第一次读的时候生成一个ReadView。

高并发情况下，如何做到安全修改同一行数据？（写写冲突）

要安全修改同一行数据，就要保证一个线程在修改时其它线程无法更新这行记录。一般有悲观锁和乐观锁两种方案

使用悲观锁

悲观锁思想是当前线程要进来修改数据时，别的线程都得拒之门外。 比如可以使用select…for update

#这条sql会锁定User表中所有符合检索条件（name='jay'）的记录。本次事务提交之前，别的线程都无法修改这些记录。
select * from User where name='jay' for update

select for update 含义

select查询语句是不会加锁的，但是select for update除了有查询的作用外，还会加锁呢，而且它是悲观锁。至于加了是行锁还是表锁，这就要看是不是用了索引/主键。没用索引/主键的话是表锁，否则是行锁。

使用乐观锁

乐观锁思想是有线程来先放过去修改，如果看到别的线程没修改过就可以修改成功；如果别的线程修改过，就修改失败或者重试。

实现方式：乐观锁一般会使用版本号机制或CAS算法实现

4.MySql锁

分类

操作粒度

表级锁：每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在 MyISAM、InnoDB、BDB 等存储引擎中。

行级锁：每次操作锁住一行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在 InnoDB 存储引擎中。

页级锁：每次锁定相邻的一组记录，锁定粒度界于表锁和行锁之间，开销和加锁时间界于表 锁和行锁之间，并发度一般。应用在 BDB 存储引擎中。
MyISAM 只支持表锁，InnoDB 支持表锁和行锁，默认为行锁。

• 表级锁：开销小，加锁快，不会出现死锁。锁定粒度大，发生锁冲突的概率最高，并发量最低。
• 行级锁：开销大，加锁慢，会出现死锁。锁力度小，发生锁冲突的概率小，并发度最高。

操作类型

读锁（S 锁）：共享锁，针对同一份数据，多个读操作可以同时进行而不会互相影响。

写锁（X 锁）：排他锁，当前写操作没有完成前，它会阻断其他写锁和读锁。

IS 锁、IX 锁：意向读锁、意向写锁，属于表级锁，S 和 X 主要针对行级锁。

S 锁：事务 A 对记录添加了 S 锁，可以对记录进行读操作，不能做修改，其他事务可以对该记录追加 S 锁，但是不能追加 X 锁，需要追加 X 锁，需要等记录的 S 锁全部释放。

X 锁：事务 A 对记录添加了 X 锁，可以对记录进行读和修改操作，其他事务不能对记录做读和修改操 作。

操作性能

乐观锁：认为不会存在并发问题，一般的实现方式是对记录数据版本进行比对，在数据更新提交时才会进行冲突检测，如果发现冲突，则提示错误信息。

悲观锁：认为每次操作都会存在并发问题，在对一条数据修改时，为了避免同时被其他人修改，在修改数据前先锁定再修改的控制方式。共享锁和排他锁是悲观锁的不同实现。

行锁原理

在 InnoDB 引擎中，我们可以使用行锁和表锁，其中行锁又分为共享锁和排他锁。InnoDB 行锁是通过对索引数据页上的记录加锁实现的，主要实现算法有 3 种：Record Lock、Gap Lock 和 Next-key Lock。

RecordLock 锁：锁定单个行记录的锁。（记录锁，RC、RR 隔离级别都支持）

GapLock 锁：间隙锁，锁定索引记录间隙，确保索引记录的间隙不变。（范围锁，RR 隔离级别支持）

Next-key Lock 锁：记录锁和间隙锁组合，同时锁住数据，并且锁住数据前后范围。（记录锁 + 范围锁，RR 隔离级别支持）

在 RR 隔离级别，InnoDB 对于记录加锁行为都是先采用 Next-Key Lock，但是当 SQL 操作含有唯一索引 时，Innodb 会对 Next-Key Lock 进行优化，降级为 RecordLock，仅锁住索引本身而非范围。

主键锁

加锁行为：仅在 id=10 的主键索引记录上加 X 锁。

唯一索引锁

加锁行为：现在唯一索引 id 上加 X 锁，然后在 id=10 的主键索引记录上加 X 锁。

普通索引锁

加锁行为：对满足 id=10 条件的记录和主键分别加 X 锁，然后在(6,c)-(10,b)、(10,b)-(10,d)、(10,d)(11,f)范围分别加 Gap Lock。

无索引锁

加锁行为：表里所有行和间隙都会加 X 锁。（当没有索引时，会导致全表锁定，因为 InnoDB 引擎 锁机制是基于索引实现的记录锁定）。

悲观锁

悲观锁（Pessimistic Locking）是指在数据处理过程，将数据处于锁定状态，一般使用数据库的锁机 制实现。 前面提到的行锁、表锁、共享锁、排他锁等都属于悲观锁范畴。

表锁

表级锁每次操作都锁住整张表，并发度最低。表级读锁会阻塞写操作，但是不会阻塞读操作；而写锁则会把读和写操作都阻塞。

#手动增加表锁
lock table 表名称 read|write,表名称2 read|write;
#查看表上加过的锁
show open tables;
#删除表锁
unlock tables;

行级锁-读锁（共享锁）

共享锁是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。使用共享锁的方法是在 select … lock in share mode，只适用查询语句。

行级锁-写锁（排他锁）

排他锁是不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能对该行记录做其他操作，也不能获取该行的锁。

使用排他锁的方法是在 SQL 末尾加上 for update，innodb 引擎默认会在 update、delete 语句加上 for update。

如果查询没有使用到索引，将会锁住整个表记录。

乐观锁

乐观锁是相对于悲观锁而言的，它不是数据库提供的功能，需要开发者自己去实现。乐观锁实现的关键点：冲突检测。

使用版本字段

先给数据表增加一个版本(version) 字段，每操作一次将那条记录版本号加 1。version 是用来查看被读的记录有无变化，作用是防止记录在业务处理期间被其他事务修改。

使用时间戳

在数据表增加一个字段，字段类型使用 timestamp 时间戳。在更新提交时检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一致则提交更新，否则就是版本冲突取消操作。

许多数据库访问框架也封装了乐观锁的实现，比如 hibernate 框架、MyBatis 框架（使用 OptimisticLocker 插件）。

并发率要求高的选择乐观锁；对于并发率要求低的可以选择悲观锁。

死锁与解决方案

行、表锁死锁

1.原因：如果在事务中执行了一条没有索引条件的查询，引发全表扫描，把行级锁上升为全表记录锁定（等 价于表级锁），多个这样的事务执行后很容易产生死锁和阻塞，最终应用系统会越来越慢，发生阻塞或死锁。

解决方案：SQL 语句中不要使用太复杂的关联多表查询；使用 explain对 SQL 语句进行分析，对于有全表扫描和全表锁定的 SQL 语句，建立相应索引进行优化。

2.原因：两个事务分别想拿到对方持有的锁，互相等待，于是产生死锁。

解决方案：在同一个事务中，尽可能做到一次锁定所需要的所有资源，按照 id 对资源排序并按顺序进行处理。

共享锁转换为排他锁

原因：事务 A 查询一条纪录，然后更新该条纪录；此时事务 B 也更新该条纪录，这时事务 B 的排他锁由于 事务 A 有共享锁，必须等 A 释放共享锁后才可以获取，只能排队等待。事务 A 再执行更新操作时， 此处发生死锁。因为事务 A 需要排他锁来做更新操作。但是，无法授予该锁请求，因为事务 B 已经 有一个排他锁请求，并且正在等待事务 A 释放其共享锁。
解决方案：对于按钮等控件，点击立刻失效，不让用户重复点击，避免引发同时对同一条记录多次操作；使用乐观锁进行控制。乐观锁机制避免了长事务中数据库加锁开销，大大提升了大并发量下的系统性能。

死锁排查

#查看死锁日志
show engine innodb status \G;
#查看锁状态变量，分析系统中的行锁的争夺
show status like 'innodb_row_lock%';
#Innodb_row_lock_current_waits：当前正在等待锁的数量 
#Innodb_row_lock_time：从系统启动到现在锁定总时间长度 
#Innodb_row_lock_time_avg： 每次等待锁的平均时间 
#Innodb_row_lock_time_max：从系统启动到现在等待最长的一次锁的时间 
#Innodb_row_lock_waits：系统启动后到现在总共等待的次数

参考

MySQL高级笔记

innodb 数据页结构&行格式