序言:

文章内容输出来源：拉勾教育Java高薪训练营。
本篇文章是学习课程中的一部分课后笔记

一、 MySQL体系架构

体系架构.png

1、 网络连接层

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

2、服务层（MySQL Server）

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

• 连接池（Connection Pool）：负责存储和管理客户端与数据库的连接，一个线程负责管理一个连接。
  系统管理和控制工具（Management Services & Utilities）：例如备份恢复、安全管理、集群
• 管理等SQL接口（SQL Interface）：用于接受客户端发送的各种SQL命令，并且返回用户需要查询的结果。比如DML、DDL、存储过程、视图、触发器等。
• 解析器（Parser）：负责将请求的SQL解析生成一个"解析树"。然后根据一些MySQL规则进一步检查解析树是否合法。
• 查询优化器（Optimizer）：当“解析树”通过解析器语法检查后，将交由优化器将其转化成执行计划，然后与存储引擎交互。

 select uid,name from user where gender=1;
选取--》投影--》联接 策略
1）select先根据where语句进行选取，并不是查询出全部数据再过滤
2）select查询根据uid和name进行属性投影，并不是取出所有字段
3）将前面选取和投影联接起来最终生成查询结果

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

3、存储引擎层（Pluggable Storage Engines）

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

4、系统文件层（File System）

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

二、MySQL运行机制

运行机制.png

1. 建立连接（Connectors&Connection Pool），通过客户端/服务器通信协议与MySQL建立连接。MySQL 客户端与服务端的通信方式是 “ 半双工 ”。对于每一个 MySQL 的连接，时刻都有一个线程状态来标识这个连接正在做什么。

   • 通讯机制：
     全双工：能同时发送和接收数据，例如平时打电话。
     半双工：指的某一时刻，要么发送数据，要么接收数据，不能同时。例如早期对讲机
     单工：只能发送数据或只能接收数据。例如单行道

2. 查询缓存（Cache&Buffer），这是MySQL的一个可优化查询的地方，如果开启了查询缓存且在查询缓存过程中查询到完全相同的SQL语句，则将查询结果直接返回给客户端；如果没有开启查询缓存或者没有查询到完全相同的 SQL 语句则会由解析器进行语法语义解析，并生成“解析树”。

3. 解析器（Parser）将客户端发送的SQL进行语法解析，生成"解析树"。预处理器根据一些MySQL规则进一步检查“解析树”是否合法，例如这里将检查数据表和数据列是否存在，还会解析名字和别名，看看它们是否有歧义，最后生成新的“解析树”。

4. 查询优化器（Optimizer）根据“解析树”生成最优的执行计划。MySQL使用很多优化策略生成最优的执行计划，可以分为两类：静态优化（编译时优化）、动态优化（运行时优化）。

   • 等价变换策略
     • 5=5 and a>5 改成 a > 5
     • a < b and a=5 改成b>5 and a=5
     • 基于联合索引，调整条件位置等
   • 优化count、min、max等函数
     • InnoDB引擎min函数只需要找索引最左边
     • InnoDB引擎max函数只需要找索引最右边
     • MyISAM引擎count(*)，不需要计算，直接返回
   • 提前终止查询
     • 使用了limit查询，获取limit所需的数据，就不在继续遍历后面数据
   • in的优化
     • MySQL对in查询，会先进行排序，再采用二分法查找数据。比如where id in (2,1,3)，变
       成 in (1,2,3)

5. 查询执行引擎负责执行 SQL 语句，此时查询执行引擎会根据 SQL 语句中表的存储引擎类型，以及对应的API接口与底层存储引擎缓存或者物理文件的交互，得到查询结果并返回给客户端。若开启用查询缓存，这时会将SQL 语句和结果完整地保存到查询缓存（Cache&Buffer）中，以后若有相同的 SQL 语句执行则直接返回结果。

   • 如果开启了查询缓存，先将查询结果做缓存操作
   • 返回结果过多，采用增量模式返回

三、MySQL存储引擎

1、各引擎简介

在5.5版本之前默认采用MyISAM存储引擎，从5.5开始采用InnoDB存储引擎。

• InnoDB：支持事务，具有提交，回滚和崩溃恢复能力，事务安全；
• MyISAM：不支持事务和外键，访问速度快；
• Memory：利用内存创建表，访问速度非常快，因为数据在内存，而且默认使用Hash索引，但是一旦关闭，数据就会丢失；
• Archive：归档类型引擎，仅能支持insert和select语句；
• Csv：以CSV文件进行数据存储，由于文件限制，所有列必须强制指定not null，另外CSV引擎也不支持索引和分区，适合做数据交换的中间表；
• BlackHole: 黑洞，只进不出，进来消失，所有插入数据都不会保存；
• Federated：可以访问远端MySQL数据库中的表。一个本地表，不保存数据，访问远程表内容；
• MRG_MyISAM：一组MyISAM表的组合，这些MyISAM表必须结构相同，Merge表本身没有数据，对Merge操作可以对一组MyISAM表进行操作。

2、InnoDB和MyISAM对比

• 事务和外键
  InnoDB支持事务和外键，具有安全性和完整性，适合大量insert或update操作
  MyISAM不支持事务和外键，它提供高速存储和检索，适合大量的select查询操作
• 锁机制
  InnoDB支持行级锁，锁定指定记录；基于索引来加锁实现。
  MyISAM支持表级锁，锁定整张表。
• 索引结构
  InnoDB使用聚集索引（聚簇索引），索引和记录在一起存储，既缓存索引，也缓存记录。
  MyISAM使用非聚集索引（非聚簇索引），索引和记录分开。
• 并发处理能力
  InnoDB读写阻塞可以与隔离级别有关，可以采用多版本并发控制（MVCC）来支持高并发。
  MyISAM使用表锁，会导致写操作并发率低，读之间并不阻塞，读写阻塞。
• 存储文件
  InnoDB表对应两个文件，一个.frm表结构文件，一个.ibd数据文件。InnoDB表最大支持64TB；
  MyISAM表对应三个文件，一个.frm表结构文件，一个MYD表数据文件，一个.MYI索引文件。从MySQL5.0开始默认限制是256TB。

3、InnoDB存储结构

• 主要分为内存结构和磁盘结构两大部分：
  MySQL 5.7 版本：

  
  
  存储结构5.7.png

MySQL 8.0 版本：

存储结构8.0.png

• MySQL 5.7 版本
  • 将 Undo日志表空间从共享表空间 ibdata 文件中分离出来，可以在安装 MySQL 时由用
    户自行指定文件大小和数量。
  • 增加了 temporary 临时表空间，里面存储着临时表或临时查询结果集的数据。
    Buffer Pool 大小可以动态修改，无需重启数据库实例。
• MySQL 8.0 版本
  • 将InnoDB表的数据字典和Undo都从共享表空间ibdata中彻底分离出来了，以前需要
    ibdata中数据字典与独立表空间ibd文件中数据字典一致才行，8.0版本就不需要了。
  • temporary 临时表空间也可以配置多个物理文件，而且均为 InnoDB 存储引擎并能创建
    索引，这样加快了处理的速度。
  • 用户可以像 Oracle 数据库那样设置一些表空间，每个表空间对应多个物理文件，每个
    表空间可以给多个表使用，但一个表只能存储在一个表空间中。将Doublewrite Buffer从共享表空间ibdata中也分离出来了。

4、InnoDB数据文件

InnoDB数据文件存储结构：
分为一个ibd数据文件-->Segment（段）-->Extent（区）-->Page（页）-->Row（行）

• Tablesapce
  表空间，用于存储多个ibd数据文件，用于存储表的记录和索引。一个文件包含多个段。
• Segment
  段，用于管理多个Extent，分为数据段（Leaf node segment）、索引段（Non-leaf node
  segment）、回滚段（Rollback segment）。一个表至少会有两个segment，一个管理数
  据，一个管理索引。每多创建一个索引，会多两个segment。
• Extent
  区，一个区固定包含64个连续的页，大小为1M。当表空间不足，需要分配新的页资源，不会
  一页一页分，直接分配一个区。
• Page
  页，用于存储多个Row行记录，大小为16K。包含很多种页类型，比如数据页，undo页，系
  统页，事务数据页，大的BLOB对象页。
• Row
  行，包含了记录的字段值，事务ID（Trx id）、滚动指针（Roll pointer）、字段指针（Field
  pointers）等信息。

四、 Undo Log

1、 Undo Log 介绍

show variables like '%innodb_undo%';

• Undo：意为撤销或取消，以撤销操作为目的，返回指定某个状态的操作。
• Undo Log：数据库事务开始之前，会将要修改的记录存放到 Undo 日志里，当事务回滚时或者数据库崩溃时，可以利用 Undo 日志，撤销未提交事务对数据库产生的影响。
• Undo Log产生和销毁：Undo Log在事务开始前产生；事务在提交时，并不会立刻删除undo log，innodb会将该事务对应的undo log放入到删除列表中，后面会通过后台线程purge thread进行回收处理。Undo Log属于逻辑日志，记录一个变化过程。
  例如执行一个delete，undo log会记录一个insert；执行一个update，undo log会记录一个相反的update。
• Undo Log存储：undo log采用段的方式管理和记录。在innodb数据文件中包含一种rollback segment回滚段，内部包含1024个undo log segment。可以通过下面一组参数来控制Undo log存储。

2、 Undo Log 作用

• 实现事务的原子性
  Undo Log 是为了实现事务的原子性而出现的产物。事务处理过程中，如果出现了错误或者用户执行了 ROLLBACK 语句，MySQL 可以利用 Undo Log 中的备份将数据恢复到事务开始之前的状态。
• 实现多版本并发控制（MVCC）
  Undo Log 在 MySQL InnoDB 存储引擎中用来实现多版本并发控制。事务未提交之前，Undo Log保存了未提交之前的版本数据，Undo Log 中的数据可作为数据旧版本快照供其他并发事务进行快照读。

mvcc.png

• 事务A手动开启事务，执行更新操作，首先会把更新命中的数据备份到 Undo Buffer 中。
• 事务B手动开启事务，执行查询操作，会读取 Undo 日志数据返回，进行快照读。

五、 Redo Log和Binlog

1、 Redo Log日志

• Redo：顾名思义就是重做。以恢复操作为目的，在数据库发生意外时重现操作。
• Redo Log：指事务中修改的任何数据，将最新的数据备份存储的位置（Redo Log），被称为重做
  日志。
• Redo Log 的生成和释放：随着事务操作的执行，就会生成Redo Log，在事务提交时会将产生
• Redo Log写入Log Buffer，并不是随着事务的提交就立刻写入磁盘文件。等事务操作的脏页写入到磁盘之后，Redo Log 的使命也就完成了，Redo Log占用的空间就可以重用（被覆盖写入）。

原理.png

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

2、 Binlog日志

• Redo Log 是属于InnoDB引擎所特有的日志，而MySQL Server也有自己的日志，即 Binary log（二进制日志），简称Binlog。

• Binlog是记录所有数据库表结构变更以及表数据修改的二进制日志，不会记录SELECT和SHOW这类操作。Binlog日志是以事件形式记录，还包含语句所执行的消耗时间。开启Binlog日志有以下两个最重要的使用场景:

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

写入机制：

• 根据记录模式和操作触发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。

Binlog文件中Log event结构如下图所示 ：

even结构.png

3、 Redo Log和Binlog区别

• Redo Log是属于InnoDB引擎功能，Binlog是属于MySQL Server自带功能，并且是以二进制文件记录。
• Redo Log属于物理日志，记录该数据页更新状态内容，Binlog是逻辑日志，记录更新过程。
• Redo Log日志是循环写，日志空间大小是固定，Binlog是追加写入，写完一个写下一个，不会覆盖使用。
• Redo Log作为服务器异常宕机后事务数据自动恢复使用，Binlog可以作为主从复制和数据恢复使用。Binlog没有自动crash-safe能力。

六、 MySQL索引原理

1、索引类型

• 从索引存储结构划分：B Tree索引、Hash索引、FULLTEXT全文索引、R Tree索引
• 从应用层次划分：普通索引、唯一索引、主键索引、复合索引
• 从索引键值类型划分：主键索引、辅助索引（二级索引）
• 从数据存储和索引键值逻辑关系划分：聚集索引（聚簇索引）、非聚集索引（非聚簇索引）

2、 索引原理

• 是存储引擎用于快速查找记录的一种数据结构。需要额外开辟空间和数据维护工作。
  • 索引是物理数据页存储，在数据文件中（InnoDB，ibd文件），利用数据页(page)存储。
  • 索引可以加快检索速度，但是同时也会降低增删改操作速度，索引维护需要代价。

索引涉及的理论知识：

• 二分查找法 : 它的优点是等值查询、范围查询性能优秀，缺点是更新数据、新增数据、删除数据维护成本高。
• Hash : Hash底层实现是由Hash表来实现的，是根据键值 <key,value> 存储数据的结构。非常适合根据key查找value值，也就是单个key查询，或者说等值查询。
• B+Tree (MySQL数据库索引采用) :
  ① 非叶子节点不存储data数据，只存储索引值，这样便于存储更多的索引值
  ② 叶子节点包含了所有的索引值和data数据
  ③ 叶子节点用指针连接，提高区间的访问性能

B+树的搜索：
B+树进行范围查找时，只需要查找定位两个节点的索引值，然后利用叶子节点的指针进行遍历即可。

b+tree.png

• B-tree：
  ① 索引值和data数据分布在整棵树结构中
  ② 每个节点可以存放多个索引值及对应的data数据
  ③ 树节点中的多个索引值从左到右升序排列

B树的搜索：
从根节点开始，对节点内的索引值序列采用二分法查找，如果命中就结束查找。没有命中会进入子节点重复查找过程，直到所对应的的节点指针为空，或已经是叶子节点了才结束。

btree.png

3、 聚簇索引和辅助索引

• 聚簇索引和非聚簇索引：
  B+Tree的叶子节点存放主键索引值和行记录就属于聚簇索引；如果索引值和行记录分开存放就属于非聚簇索引。
• 主键索引和辅助索引：
  B+Tree的叶子节点存放的是主键字段值就属于主键索引；如果存放的是非主键值就属于辅助索引（二级索引）。

在InnoDB引擎中，主键索引采用的就是聚簇索引结构存储。

4、 回表查询

• 聚簇索引的叶子节点存储行记录，InnoDB必须要有，且只有一个。辅助索引的叶子节点存储的是主键值和索引字段值，通过辅助索引无法直接定位行记录，通常情况下，需要扫码两遍索引树。
  先通过辅助索引定位主键值，然后再通过聚簇索引定位行记录。

5、覆盖索引

• 只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快，这就叫做索引覆盖。
  实现索引覆盖最常见的方法就是：将被查询的字段，建立到组合索引。

6、最左前缀原则

• 复合索引使用时遵循最左前缀原则，即查询中使用到最左边的列，那么查询就会使用到索引，如果从索引的第二列开始查找，索引将失效。
  
  最左原则.png

七、 MySQL事务

1、事务

事务.png

特性.png

2、MVCC

• 多版本控制MVCC，也就是Copy on Write的思想。MVCC除了支持读和读并行，还支持读和写、写和读的并行，但为了保证一致性，写和写是无法并行的。

mvcc.png

在事务1开始写操作的时候会copy一个记录的副本，其他事务读操作会读取这个记录副本，因此不会影响其他事务对此记录的读取，实现写和读并行。

MVCC实现原理：

MVCC最大的好处是读不加锁，读写不冲突。在读多写少的系统应用中，读写不冲突是非常重要的，极大的提升系统的并发性能，这也是为什么现阶段几乎所有的关系型数据库都支持 MVCC 的原因，不过目前MVCC只在 Read Commited 和 Repeatable Read 两种隔离级别下工作。

两种读操作：

• 快照读：读取的是记录的快照版本（有可能是历史版本），不用加锁。（select）
• 当前读：读取的是记录的最新版本，并且当前读返回的记录，都会加锁，保证其他事务不会再并发修改这条记录。（select... for update 或lock in share mode，insert/delete/update）

八、mysql 锁

1、锁分类

1.1、 从操作的粒度可分为表级锁、行级锁和页级锁。

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

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

  
  
  支持类型.png

1.2、从操作的类型可分为读锁和写锁。

• 读锁（S锁）：共享锁，针对同一份数据，多个读操作可以同时进行而不会互相影响。
• 写锁（X锁）：排他锁，当前写操作没有完成前，它会阻断其他写锁和读锁。
• IS锁、IX锁：意向读锁、意向写锁，属于表级锁，S和X主要针对行级锁。在对表记录添加S或X锁之前，会先对表添加IS或IX锁。

1.3、 从操作的性能可分为乐观锁和悲观锁。

• 乐观锁：一般的实现方式是对记录数据版本进行比对，在数据更新提交的时候才会进行冲突检测，如果发现冲突了，则提示错误信息。
• 悲观锁：在对一条数据修改的时候，为了避免同时被其他人修改，在修改数据之前先锁定，再修改的控制方式。共享锁和排他锁是悲观锁的不同实现，但都属于悲观锁范畴。

2、行锁原理

• InnoDB行锁是通过对索引数据页上的记录加锁实现的。

2.1、实现算法

• RecordLock锁：锁定单个行记录的锁。（记录锁，RC、RR隔离级别都支持）
• GapLock锁：间隙锁，锁定索引记录间隙，确保索引记录的间隙不变。（范围锁，RR隔离级别支持）
• Next-key Lock 锁：记录锁和间隙锁组合，同时锁住数据，并且锁住数据前后范围。（记录锁+范围锁，RR隔离级别支持）

3、 死锁与解决方案

3.1、表锁死锁

原因：

• 用户A访问表A（锁住了表A），然后又访问表B；另一个用户B访问表B（锁住了表B），然后企图访问表A；这时用户A由于用户B已经锁住表B，它必须等待用户B释放表B才能继续，同样用户B要等用户A释放表A才能继续，这就死锁就产生了。
  用户A--》A表（表锁）--》B表（表锁）
  用户B--》B表（表锁）--》A表（表锁）

解决方案:

• 这种死锁比较常见，是由于程序的BUG产生的，除了调整的程序的逻辑没有其它的办法。
  仔细分析程序的逻辑，对于数据库的多表操作时，尽量按照相同的顺序进行处理，尽量避免同时锁定两个资源，如操作A和B两张表时，总是按先A后B的顺序处理， 必须同时锁定两个资源时，要保证在任
  何时刻都应该按照相同的顺序来锁定资源。

3.2、行级锁死锁

产生原因1：

• 如果在事务中执行了一条没有索引条件的查询，引发全表扫描，把行级锁上升为全表记录锁定（等价于表级锁），多个这样的事务执行后，就很容易产生死锁和阻塞，最终应用系统会越来越慢，发生阻塞或死锁。
  解决方案1：
• SQL语句中不要使用太复杂的关联多表的查询；使用explain“执行计划"对SQL语句进行分析，对于有全表扫描和全表锁定的SQL语句，建立相应的索引进行优化。

产生原因2：

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

解决方案2：

• 在同一个事务中，尽可能做到一次锁定所需要的所有资源
• 按照id对资源排序，然后按顺序进行处理

3.3、共享锁转换为排他锁

产生原因：

• 事务A 查询一条纪录，然后更新该条纪录；此时事务B 也更新该条纪录，这时事务B 的排他锁由于事务A 有共享锁，必须等A 释放共享锁后才可以获取，只能排队等待。事务A 再执行更新操作时，此处发生死锁，因为事务A 需要排他锁来做更新操作。但是，无法授予该锁请求，因为事务B 已经有一个排他锁请求，并且正在等待事务A 释放其共享锁。

事务A: select * from dept where deptno=1 lock in share mode; //共享锁,1
update dept set dname='java' where deptno=1;//排他锁,3

事务B: update dept set dname='Java' where deptno=1;//由于1有共享锁，没法获取排他锁，需等待，2

解决方案：

• 对于按钮等控件，点击立刻失效，不让用户重复点击，避免引发同时对同一条记录多次操作；使用乐观锁进行控制。
  乐观锁机制避免了长事务中的数据库加锁开销，大大提升了大并发量下的系统性能。需要注意的是，由于乐观锁机制是在我们的系统中实现，来自外部系统的用户更新操作不受我们系统的控制，因此可能会造成脏数据被更新到数据库中；

3.4、死锁排查

• 查看死锁日志
  通过show engine innodb status\G命令
  查看近期死锁日志信息。
  使用方法：
  1、查看近期死锁日志信息
  2、使用explain查看下SQL执行计划

• 查看锁状态变量
  通过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：系统启动后到现在总共等待的次数

如果等待次数高，而且每次等待时间长，需要分析系统中为什么会有如此多的等待，然后着
手定制优化。