【学习笔记】高性能Mysql学习笔记-第一章 Mysql 架构与历史

前言：本文章是学习过程中所写，只经过简单校准，作为学习笔记分享，如果发现错误欢迎指出。谢谢！！

概述

本章主要描述了

• Mysql的逻辑架构
• 并发控制
• 事务
• 多版本并发控制
• Mysql 的存储引擎
• Mysql 时间线
• Mysql 的开发模式

引用我本章最喜欢的一句话

Mysql并不完美，确足够灵活。

1.1 Mysql 逻辑架构

图片来源 https://blog.csdn.net/z_ryan/article/details/82260663 此博客中有更详细的Mysql 工作流

• 注意Mysql 8 中已经删除了查询缓存

经过上图我们可以看到，数据库简单分为三层

• 第一层 处理链接，授权认证和安全
• 第二次 为Mysql 的核心服务
• 第三层 负责Mysql 的存储和提取数据

从上面的图中我们不难发现，我们的执行语句的时候都是自上而下的执行的。
客户端连接层
一般的程序都会有这一层的功能，并非Mysql特殊的功能

1. 建立对客户端的链接。（通常为TCP协议）。
2. 认证用户
3. 验证该连接的权限

核心功能层
大多数Mysql的核心服务功能都在这一层，并且该层的功能是跨存储引擎的也就是说该层的功能是每个存储引擎通用的。

1. 通过分析器分析sql中的语义和语法
2. 检查当前语句是否能命中缓存，如果能命中就直接返回缓存中的数据，如果不能则执行下一步。
   注：Mysql8中删除了缓存,所以mysql8中没有该步骤
3. 对不能命中缓存的查询语句交给sql优化器，对语句进行优化，优化后交给第三层进行处理。如果是不是查询语句直接交给第三层进行处理

数据库引擎层
该层负责主要负责Mysql的存储和提取功能。
1.访问和更改数据
2.对数据进行加锁和释放锁

1.1.1 链接管理与安全性

链接管理

• 每个客户端链接都会在服务器进程中有一个线程，这个连接的查询只会在这个单独的线程中执行，该线程只能轮流在某个CPU或者某个CPU核心中运行。
• Mysql 自5.6以后默认使用线程池管理线程，线程池中分为若干个group

下面这篇文章详细解释了Mysql 的线程池。

https://www.sohu.com/a/226515477_411876

安全性

• mysql服务会验证客户端的用户名、原始主机和密码。
• 如果使用了安全套接字（SSL）的方式链接，还可以使用X.509证书认证。
• 检查客户端的权限

1.1.2 优化与执行

• mysql 在执行查询的时候会先对其进行优化（重写查询，表的读写顺序，合适的索引）。
• 用户可以通过关键字（hint）提示优化器执行sql。也可以使用优化器解析（explain) 查看优化器如何对语句进行优化。
• 优化器不关系存储引擎，但是存储引擎会影响优化器的效率。
• 对于select 语句，会先检查查询缓存器中是否有概查询，有则直接返回。（Mysql8 中已删除）

1.2 并发控制

无论何时，主要有多个查询，我们就需要考虑并发控制。

1.2.1 读写锁

共享锁（也叫读锁） - 做读操作的时候，其他读操作也可以执行。
排它锁（也叫写锁） - 做写操作的时候，读写操作都不可以执行。

1.2.2 锁力度

• 锁的力度越大锁定的内容越多，共享性越小，锁的开销越小。
• 锁粒度越小锁定的内容越精准，共享性越大，锁的开销越大。
• 大多数商业数据库都会使用行级锁。而Mysql提供了多种选择

表锁（table lock）

• 表锁是Mysql最基本的策略。
• 表锁会锁定整张表。
• ALTER TBALE 等语句会忽略储存引擎的锁机制直接使用表锁

行级锁

• 可以支持最大程度的并发。
• 只在存储引擎中实现。

1.3 事务

事务的四大特性，ACID

• 原子性（Atomicity）
  一个事务必须是一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功。要么全部失败回归。
• 一致性（Consistency）
  数据库总会从一个一致性的状态，转化为另一个一致性的状态。
• 隔离性（Isolation）
  通常来说，一个事务所做的修改在最后提交以前，对其他事务是不可以见的。
• 持久性(Durability)
  一旦我们数据提交，我们所做的修改就会永久的保存在数据库当中。

这四个特性保证里我们数据库可以保证我们的数据不会丢失（银行不会弄丢你的钱）
而使用了事务就必然跟锁一样会带来更多的资源消耗。而Mysql可以根据需要选择存储引擎，是否带有事务的特性。这样就跟文章一开都所说的Mysql更灵活。可以在你的业务不需要事务的时候选择合适的存储引擎。而且我们也可以通过LOCK TABLES 语句提供一定程度的保护。

扩展阅读

原子性和一致性的区别 https://www.cnblogs.com/stone94/p/10409669.html

我理解的原子性更多的是说的功能和执行上。
而一致性更多的是看执行的结果，是否符合我们程序员的预期。
例如：
事务1
①查询A 有350元。
①查询D 有2000元。
②将A 的改为250元.
③再给B 加100元
事务2
①查询C 有50元。
②查询A 有350元。
③将A 的改为250元.
④再给B 加100元

假设我们两个事务一起执行。按照上面说的原子性的特性我们是对的。
但是按照一致性的特性我们是错的。因为我们对A做了两次同样的修改。我们数据库从一个一致性的状态。变成了另一个不一致的状态。
当然这部分现在不理解也没关系。

1.3.1 隔离级别

聊到事务就一定绕不开的就是隔离级别，我们数据库中通常会有四种隔离级别。

• 未提交读
  事务可以读取到其他事务未提交的数据。
  会产生脏读问题
• 读已提交
  事务只能读取其他事务提交的数据。
  会产生不可重复读 问题
• 可重复读
  可以解决不可重复读的问题。该隔离级别解决了一个事务中重复读一条数据会得到不同结果的现象。
  会产生幻读的问题
• 可串行化
  强制事务串行执行，避免了前面说的幻读现象。简单来说可串行化会在读取的每一行数据都加锁。所以。就会产生大量的超时和争夺锁的现象。除非业务十分需要数据高度的统一，并且可以接受基本没有并发才会考虑这个隔离级别。

这里是引用原书中的表格

隔离级别	脏读可行性	不可重复读可行性	幻读可行性	加锁读可行性
未提交读	YES	YES	YES	NO
未读已提交读	NO	YES	YES	NO
可重复读	NO	NO	YES	NO
可串行化	NO	NO	NO	YES

如果这里大家还想深入了解的话，建议大家去这两篇文章看一下。
【原创】惊！史上最全的select加锁分析(Mysql) https://www.cnblogs.com/rjzheng/p/9950951.html
【原创】新说Mysql事务隔离级别 https://www.cnblogs.com/rjzheng/p/9950951.html

1.3.2 死锁

死锁是指两个以上的事务在同一个资源上互相占用,并锁定对方占用的资源。
例如：
事务1
更新用户A 金额100.
更新用户B 金额50
事务2
更新用户B 金额 100
更新用户A 金额 80

如果这两个事务同时执行了第一条语句，那么这两个事务就形成了互锁。
我记得当时一个经典的段子

出处未知，侵删
面试官：你回答一下什么时死锁。
我：你给我Offer 我就告你。

• 数据库实现了各种死锁检测和死锁超时机制。
• InnoDB 会将持有最少行级排他锁的事务进行回滚。
• 锁的行为和顺序时和存储引擎有关的。以同样的顺序执行语句，有的存储引擎可能会死锁，有的储存引擎则不会。
• 死锁发生后必须完全或部分回滚一个以上的事务，才能打破死锁。
• 大部分情况下我们只需要让因为死锁而回滚的事务重新提交就可以了。

1.3.3 事务的日志

• 事务日志可以帮助提高事务的效率。
• 存储引擎修改表的数据的时候只需要修改其内存拷贝，再把修改行为记录到磁盘的日志中，不是每次都把修改的数据本身持久化到磁盘中
• 事务采用的是追加的方式，因此写的操作是在磁盘很小的区域做顺序I/O操作。相比较于随机I/O 要快很多。
• 事务日志被持久化后，内存中被修改的数据再后台可以慢慢的刷会磁盘。
• 大部分的存储引擎都会使用日志现行的原则处理事务。
• 如果数据的修改记录持久化到了日志中，但还未完全持久化到硬盘中时发生了崩溃，我们可以根据事务日志回复这部分修改的数据。具体恢复方式视存储引擎而定。

如果想深入可以看一下下面两篇文章
顺序IO和随机IO https://www.jianshu.com/p/7f64caef3b7d
详细分析MySQL事务日志(redo log和undo log) https://www.cnblogs.com/f-ck-need-u/p/9010872.html

1.3.4 Mysql 中的事务

Mysql 提供两种事务型的存储引擎：InnoDB 和 NDB Cluster

自动提交（AUTOCOMMIT）

• Mysql 采用的是自动提交的模式，也就是说，如果不是显式开启事务。每一个查询（用sql语句）都会当做一个事务被提交，
• 可以通过设定AUTOCOMMIT变量来启用或禁止自动提交模式

SHOW VARIABLES LIKE 'AUTOCOMMIT';
SET AUTOCOMMIT = 1;

• 如果设定AUTOCOMMIT 为 0 所有的查询都是在一个事务中。直到显式的执行COMMIT 提交或者 ROLLBACK回滚，此时事务结束，并且重新开始一个新的事务。
• 数据库定义语句(DDL)中或者LOCK TABLES 等会造成大量数据改变的操作，会默认执行COMMIT（请查看对应的版本的 Mysql官网文档确认）
• Mysql通过一下语句调整数据库的隔离级别

#查询隔离级别mysql5.*
select @@tx_isolation;
#查询隔离级别mysql8.*
select @@transaction_isolation;
show variables like 'transaction_isolation';
#设置隔离级别
SET TRANSACTION LEVEL;
#只改变当前会话的隔离界别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

• MySQL 能识别ANSI 隔离界别，InnoDB 也支持四个隔离级别。

在事务中混合使用存储引擎

MySQL中尽量不要混合使用存储引擎，否则就会造成事务回滚的时候出现不一致的情况。
事务型存储引擎会回滚数据，非事务型存储引擎不会回滚事务。

隐式和显式锁定

隐式

• InnoDB 采用两阶段锁定协议（two-phase locking protocol）。
• 事务执行过程当中随时可以执行锁定，锁定只会在Commit 和Rollback 的时候才会释放。
• 所有的锁是同时释放的。
• InnoDB 会根据隔离级别在需要的时候自动加锁。
  显式
• InnoDB 也支持语句显式的加锁
• 通常情况下我们应该避免用显式的锁，因为这样可能会影响InnoDB 的性能，也可能造成未知的错误。

1.4 多版本并发控制

大多数的事务特性的存储引擎都不会直接使用行锁,而是通过实现多版本并发控制(MVCC)。
下面描述InnoDB 简化版的多版本控制。
首先我们会在每行记录保存两个隐藏的列。这两个列，一个保存了行的创建时间。一个保存了行的过期时间。这个时间并不是真正的时间，其实是系统的版本号（system version number）。没开始一个新的事务，版本号自增。
Select 情况下
①InnoDB会查询早于当前事务版本的数据行（也是就小于或等于当前版本）。这样我们可以保证只查询到早于当前事务创建的数据行，或者事务本城插入的数据行。
②InnoDB会查询没有过期版本或者过期版本大于当前事务版本的数据行。这样我们可以保证查询到的数据行或者是没有删除的或者是在当前事务创建之后删除的。

Insert 情况下
InnoDB 会在插入的每一行数据保存当前系统版本号作为行版本号。

Delete 情况下
InnoDB 会在删除的每一行数据行中保存当前的系统版本号作为删除标识。

Update 情况下
InnoDB 会在插入一条数据，并保存当前的创建版本，同时保存当前系统的版本号到原来的数据作为删除标识。

MVCC 只能Read Committed 和 Repeatable Read 的隔离级别下工作。其他两个隔离级别都和MVCC 不兼容。

1.5 Mysql 的存储引擎

• mysql 会将每个数据库保存为数据目录下的一个子目录。

• 创建表时，Mysql会在数据库子表中创建一个和表同名的frm 文件保存表的定义。

  Mysql8 删除了frm 文件

• 可以使用SHOW TABLE STATUS LIKE ‘table_name’ \G 查询表的相关信息

Name:
表名称
Engine:
表的存储引擎
Version:
版本
Row_format:
行格式。对于MyISAM引擎，这可能是Dynamic，Fixed或Compressed。动态行的行长度可变，例如Varchar或Blob类型字段。固定行是指行长度不变，例如Char和Integer类型字段
Rows:
表中的行数。对于MyISAM和其他存储引擎，这个值是精确的，对于innoDB存储引擎，这个值通常是估算的
Avg_row_length:
平均每行包括的字节数
Data_length:
整个表的数据量(以字节为单位)
Max_data_length:
表可以容纳的最大数据量，该值和存储引擎相关
Index_length:
索引占用磁盘的空间大小(以字节为单位)
Data_free:
对于MyISAM引擎，表示已经分配，但目前没有使用的空间。这部分空间包含之前被删除的行，以及后续可以被insert利用到的空间
Auto_increment:
下一个Auto_increment的值
Create_time:
表的创建时间
Update_time:
表的最近更新时间
Check_time:
使用 check table 或myisamchk工具最后一次检查表的时间
Collation:
表的默认字符集和字符排序规则
Checksum:
如果启用，保存的是整个表的实时校验和
Create_options:
创建表时指定的其他选项
Comment:
包含了其他额外信息，对于MyISAM引擎，保存的是表在创建时带的注释。如果表使用的是innodb引擎 ，保存的是InnoDB表空间的剩余空间。如果是一个视图，注释里面包含了VIEW字样。

1.5.1 InooDB 存储引擎

• 事务性存储引擎，是最重要的，也是使用最广泛的存储引擎。
• 用来处理大量的短期的事务，短期事务大部分都会提交，很少会回滚。
• InnoDB 的性能和自动崩溃回复的特性，使得它在非事务性存储引擎中也很流行。
• 值得花大量时间学习

InnoDB 的历史

• 2008 年 发布了InnoDB plugin 适用于mysql 5.1。
• Mysql 开始是默认使用InnoDB 而不是使用性能更好的InnoDB plugin
• Oracle 收购Sun 公司以后 默认使用InnoDB plugin

InnoDB 概览

• InnoDB 的数据存储在表空间中，表空间是由InnoDB 管理的一个黑盒子，由一系列的数据文件组成。
• InnoDB 采用了MVCC 来支持高并发，并实现四个常用的隔离级别。
• 默认隔离级别是repeatable read，并通过间隙锁（next-key locking）策略防止幻读的出现。
• InnoDB 表是基于聚簇索引建立的。
• InnoDB 内部做了很多优化
• InnoDB 支持热备份。

1.5.2 MyISAM 存储引擎

存储
MyISAM 的存储会存储在两个文件当中，分别是数据文件（.MYD）和索引文件（.MYI）为扩展命。

特性

1. 加锁与并发
   MyISAM 是对整个表加锁。读取是加共享锁，写入时加排它锁。但是MyISAM 在读取的时候依然可以插入。称为并发插入。
2. 修复
   MyISAM 可以通过 CHECK TABLE mytable 检查表的错误。如果有有错误可以用 REPAIR TABLE mytable 进行修复。如果Mysql服务已经关闭，也可以通过myisamchk 命令行工具进行检查和修复操作。
3. 索引特性
   MyISAM 即使时BLOB 和TEXT 等长字段，也可以基于其前500个字符创建索引。MyISAM 也支持全文索引。
4. 延迟更新索引键
   创建MyISAM 表的时候，如果指定了DELAY_KEY_WRITE选项，在每次修改执行完成时，不会立刻将修改的索引数据写入磁盘。而是会写到内存的缓冲区，只在清理键缓冲区或关闭表的时候才会将对应的索引块写入到磁盘.