高性能Mysql笔记-1架构

MySQL架构与历史

MySQL架构图

并发控制

读写锁

解决这类读写冲突经典问题的方法就是并发控制，其实非常简单。在处理并发读或者写时，可以通过实现一个由两种类型的锁组成的锁系统来解决问题。这两种类型的锁通常被称为共享锁 (shared lock) 和排他锁 (exclusive lock)，也叫读锁 (read lock) 和写锁 (writelock)。
这里先不讨论锁的具体实现，描述一下锁的概念如下:读锁是共享的，或者说是相互不阻塞的。多个客户在同一时刻可以同时读取同一个资源，而互不干扰。写锁则是排他的，也就是说一个写锁会阻寒其他的写锁和读锁，这是出于安全策略的考虑，只有这样，才能确保在给定的时间里，只有一个用户能执行写人，并防止其他用户读取正在写人的后资源。

锁粒度

MySOL则提供了多种选择。每种MySOL存储引擎都可以实现自己的锁策略和锁粒度在存储引擎的设计中，锁管理是个非常重要的决定。将锁粒度固定在某个级别，可以为某些特定的应用场景提供更好的性能，但同时却会失去对另外一些应用场景的良好支持好在MySQL 支持多个存储引擎的架构，所以不需要单一的通用解决方案。下面将介绍两种最重要的锁策略；

• 表锁（table lock）

表锁是最基本的锁策略，开销最小，锁定一整张表。
尽管存储引擎可以管理自己的锁，MySQL本身还是会使用各种有效的表锁来实现不同的目的。例如，服务器会为诸如 ALTER TABLE之类的语句使用表锁，而忽略存储引擎的锁机制。

• 行级锁（row lock）

行级锁可以最大程度地支持并发处理 (同时也带来了最大的锁开销)。众所周知，在InnoDB 和XtraDB，以及其他一些存储引擎中实现了行级锁。行级锁只在存储引擎层实现，而MySQL服务器层(如有必要，请回顾前文的逻辑架构图) 没有实现。服务器层完全不了解存储引擎中的锁实现。在本章的后续内容以及全书中，所有的在储引警都以自己的方式显现了锁机制

事务

事务就是一组原子性的 SOL 查询，或者说一个独立的工作单元。如果数据库引擎能够成功地对数据库应用该组查询的全部语句，那么就执行该组查询。如果其中有任何一条语句因为崩溃或其他原因无法执行，那么所有的语句都不会执行。也就是说，事务内的语句，要么全部执行成功，要么全部执行失败。
除非系统通过严格的 ACID 测试，否则空谈事务的概念是不够的。ACID 表示原子性(atomicity)、一致性 (consistency)、隔离性 (isolation) 和持久性 (durability)。一个运行良好的事务处理系统，必须具备这些标准特征。

• 原子性(atomicity)

一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性。

• 一致性 (consistency)

数据库总是从一个一致性的状态转换到另外一个一致性的状态。只要事务最终没有提交,所以事务中所做的修改也不会保存到数据库中。

• 隔离性 (isolation)

通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。后面我们讨论隔离级别(Isolation level)的时候,会发现为什么我们要说“通常来说”是不可见的

• 持久性 (durability)

一旦事务提交，则其所做的修改就会永久保存到数据库中。此时即使系统崩溃，修改的数据也不会丢失。持人性是个有点模糊的概念，因为实际上持久性也分很多不同的级别。有些持久性策略能够提供非常强的安全保障，而有些则未必。不可能会有100%的持久性策略。
对应的，实现acid的数据库系统，需要更多的cpu、内存和磁盘资源来，mysql的优势在于用户可以根据自己的需求，来选择不同的存储引擎来选择是否需要事务

隔离级别

在 SQL 标准中定义了四种隔离级别，每一种级别都规定了一个事务中所做的修改，哪些在事务内和事务间是可见的，哪些是不可见的。较低级别的隔离通常可以执行更高的并发，系统的开销也更低

• READ UNCOMMITTED (读未提交)

在读未提交级别，事务中的修改，即使没有提交，对其他事务也都是可见的。事务可以读取未提交的数据，这也被称为脏读 (Dirty Read)。这个级别会导致很多问题，从性能上来说，READ UNCOMMITTED 不会比其他的级别好太多，但却缺乏其他级别的很多好处，除非真的有非常必要的理由，在实际应用中一般很少使用。

• READ COMMITTED(读提交)

大多数数据库系统的默认隔离级别都是 READ COMMITTED ( MySQL不是)。READ COMMITTED 满足前面提到的隔离性的简单定义:一个事务开始时，只能“看见”已经提交的事务所做的修改。换句话说，一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。这个级别有时候也叫做不可重复读 (nonrepeatableread)，因为两次执行同样的查询，可能会得到不一样的结果。

• REPEATABLE READ (可重复读)

REPEATABLE READ 解决了脏读的问题。该级别保证了在同一个事务中多次读取同样记录的结果是一致的。但是理论上，可重复读隔离级别还是无法解决另外一个**幻读(Phantom Read)**的问题。所谓幻读，指的是当某个事务在读取某个范围内的记录时另外一个事务又在该范围内插人了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行(Phantom Row)。InnoDB和XtraDB 存储引擎通过多版本并发控制(MVCC，Multiversion Concurrency Control) 解决了幻读的问题。可重复读是 MySQL的默认事务隔离级别。

幻读，并不是说两次读取获取的结果集不同，幻读侧重的方面是某一次的 select 操作得到的结果所表征的数据状态无法支撑后续的业务操作。更为具体一些：select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入，此时就发生了幻读。

• SERIALIZABLE(可串行化)

SERIALIZABLE是最高的隔离级别。它通过强制事务串行执行，避免了前面说的幻读的问题。简单来说，SERIALIZABLE会在读取的每一行数据上都加锁，所以可能导致大量的超时和锁争用的问题。实际应用中也很少用到这个隔离级别，只有在非常需要确保数据的一致性而且可以接受没有并发的情况下，才考虑采用该级别。

事务日志

事务日志可以帮助提高事务的效率。使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把该修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。事务日志采用的是追加的方式，因此写日志的操作是磁盘上一小块区域内的顺序I/O，而不像随机 I/O 需要在磁盘的多个地方移动磁头，所以采用事务日志的方式相对来说要快得多。事务日志持久以后，内存中被修改的数据在后台可以慢慢地刷回到磁盘。目前大多数存储引擎都是这样实现的，我们通常称之为预写式日志(Write-Ahead Logging)，修改数据需要写两次磁盘。

MySQL中的事务

MySOL提供了两种事务型的存储引擎:InnoDB和NDB Cluster。另外还有一些第三方存储引擎也支持事务，比较知名的包括 XtraDB和PBXT。

自动提交 (AUTOCOMMIT)
MySQL 默认采用自动提交 (AUTOCOMMIT) 模式。也就是说，如果不是显式地开始一个事务，则每个查询都被当作一个事务执行提交操作。在当前连接中，可以通过设置AUTOCOMMIT变量来启用或者禁用自动提交模式 。
在非事务的存储引擎中，不存在提交和事务一说，也可以说一直在自动提交 (AUTOCOMMIT) 模式。

隐式和显式锁定
InnoDB 采用的是两阶段锁定协议 (two-phase locking protocol)。在事务执行过程中，随时都可以执行锁定，锁只有在执行 COMMIT或者 ROLLBACK的时候才会释放，并且所有的锁是在同一时刻被释放。前面描述的锁定都是隐式锁定，InnoDB 会根据隔离级别在需要的时候自动加锁。
可以通过特定的语句进行显示锁定，这些不属于SQL规范
select ··· lock in share mode
select ··· for update
mysql也支持显式锁定表锁lock tables 和unlock tables全表，不过非常消耗性能，不如使用事务数据库的行级锁。

多版本并发控制MVCC

MVCC 的实现，是通过保存数据在某个时间点的快照来实现的。也就是说，不管需要执行多长时间，每个事务看到的数据都是一致的。根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。如果之前没有这方面的概念，这句话听起来就有点迷惑。熟悉了以后会发现，这句话其实还是很容易理解的
前面说到不同存储引擎的 MVCC 实现是不同的，典型的有乐观 (optimistic)并发控制和悲观 (pessimistic)并发控制。下面我们通过InnoDB 的简化版行为来说明MVCC是如何工作的。
InnoDB的MVCC，是通过在每行记录的末端保存两个隐藏标志列实现的，一个保存创建时间的版本号，另一个保存删除时间的版本号。每当一行开启新的事务，版本号都会递增。事务开始时的版本号会作为事务的版本号，用来和查询到每行记录的版本号进行比较。
MVCC具体简化版操作流程：

• SELECT

InnoDB 会根据以下两个条件检查每行记录:
a.InnoDB 只查找版本早于当前事务版本的数据行 (也就是，行的系统版本号小于或等于事务的系统版本号)，这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的
b.行的删除版本要么未定义，要么大于当前事务版本号。这可以确保事务读取到的行，在事务开始之前未被删除。
即**create_version <= transation_version && (delete_version == null || delete_version > transation_version)**，只有符合上述两个条件的记录，才能返回作为查询结果

• INSERT

InnoDB 为新插人的每一行保存当前系统版本号作为行版本号

• DELETE

InnoDB 为删除的每一行保存当前系统版本号作为行删除标识

• UPDATE

InnoDB 为插人一行新记录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识。
保存这两个额外系统版本号，使大多数读操作都可以不用加锁。这样设计使得读数据操作很简单，性能很好，并且也能保证只会读取到符合标准的行。不足之处是每行记录都需要额外的存储空间，需要做更多的行检查工作，以及一些额外的维护工作。
MVCC 只在 REPEATABLE READ 和 READ COMMITTED 两隔离级别下工作。其他两个隔离级别都和MVCC不兼容，因为 READ UNCOMMITTED 总是读取最新的数据行，而不是符合当前事务版本的数据行。而 SERIALIZABLE则会对所有读取的行都加锁。

MySQL的存储引擎

InnoDB存储引擎

ImmoDB是MySOL的默认事务型引擎，也是最重要、使用最广泛的存储引擎。它被设计用来处理大量的短期 (short-lived) 事务，短期事务大部分情况是正常提交的，很少会被回滚。InnoDB 的性能和自动崩溃恢复特性，使得它在非事务型存储的需求中也很济行。除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB 引擎如果要学习存储引擎，InnoDB 也是一个非常好的值得花最多的时间去深入学习的对象收益肯定比将时间平均花在每个存储引擎的学习上要高得多。

InnoDB 采用MVCC 来支持高并发，并且实现了四个标准的隔离级别。其默认级别是REPEATABLE READ (可重复读),并且通过间隙锁(next-key locking)策略防止幻读的出现。间隙锁使得InnoDB 不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定，以防止幻影行的插入。

转换表的引擎

**alter table **

上述语法可以适用任何存储引擎。但有一个问题:需要执行很长时间。MySQL 会按行将数据从原表复制到一张新的表中，在复制期间可能会消耗系统所有的I/O 能力，同时原表上会加上读锁。所以，在繁忙的表上执行此操作要特别小心。一个替代方案是采用接下来将讨论的导出与导人的方法，手工进行表的复制。
手动导入导出
使用工具手动导出数据和导入数据，创建新表。
创建和查询

数据量不大的话，这样做工作得很好。如果数据量很大，则可以考虑做分批处理，针对每一段数据执行事务提交操作，以避免大事务产生过多的 undo。假设有主键字段 id.重复运行以下语句(最小值 x 和最大值 y进行相应的换将数据导人到新表 :

这样是一个全量复制，如果需要保持数据一致，则可以对原表加锁，以保证数据一致。

如果这篇【文章】有帮助到你，希望可以给笔者点个赞👍，创作不易，感兴趣的也可以关注一下笔者，后续也会更新更多的内容，都会在后续慢慢更新。