Mysql 读书笔记 - 《高性能MySQL》

mysql 存储引擎结构： 将查询处理，及其他系统任务和数据的存储/提取相分离。

mysql逻辑架构

mysql服务逻辑架构图

连接/线程处理

• 每个客户端都会在服务器进程中拥有一个线程，该连接的查询只会在这个单独的线程中执行。
• 客户端连接时需要对其进行认证
• 连接成功后会继续验证客户端是否有执行特定查询的权限

优化与执行

• mysql会解析查询，创建内部数据结构--解析树

并发控制

mysql 控制并发的两个层面：服务器层与存储引擎层

读写锁

实现一个由两种类型的锁组成的锁系统来解决问题
这两种类型的锁被称为共享锁和排他锁,也叫读锁和写锁

读锁是共享的，相互不干扰的。
写锁是排他的，即写锁会阻塞其他的写锁和读锁

读写锁确保在给定时间内，只有一个用户能执行写入，并且防止其他用户读取正在写入的同一资源

锁粒度

• 任何情况下，在给定的资源上，锁定的数据越少，系统的并发程度越高(前提是相互之间不发生冲突)
  
• 加锁也需要消耗资源

综合上述两点，引出了锁策略

锁策略，即在锁的开销和数据的安全性之间寻求平衡

两种重要的锁策略：

• 表锁

  表锁是mysql最基本的锁策略，也是开销最小的策略，他会锁定整张表

• 行级锁

  行级锁可以最大程度的支持并发处理，也带来了最大的锁开销。行级锁只在存储引擎层实现，在服务器层没有实现

事物

什么是事物，事物是一组原子性的SQL查询，或者说一个独立的工作单元。事物内的语句，要么全部执行成功，要么全部执行失败

事物的ACID
事物的ACID表示原子性、一致性、隔离性、持久性

一个良好的事物处理系统，必须具备这些标准的特征

• 原子性：一个事物必须被视为一个不可分割的最小工作单元，整个事物中的所有操作要么全部执行成功，要么全部执行失败
• 一致性：数据库总是从一个一致性状态转换到另外一个一致性的状态
• 隔离性：一个事物所做的修改在最终提交之前，其他事物是不能够看到的。
• 持久性：事物一旦提交，所做的修改就会保存到数据库中

事物同锁粒度一样，同样会增加系统的开销。

隔离级别

在SQL标准中定义了四种隔离级别，每一种级别都规定了一个事物中所做的修改，哪些在事物内和事物间是可见的，哪些是不可见的
四种隔离级别：

• READ UNCOMMITTED (未提交读)
  事物中的修改，即使没有提交，对其他事物也是可见的。因此会产生脏读,即事物读取未提交的数据。这个级别会产生很多问题，但在性能上却不比其他级别好太多。
• READ COMMITTED (提交读)
  此级别为大多数数据库系统的默认隔离级别（MYSQL不是）。此级别下，一个事物从开始直到提交之前，所做的任何修改对其他事物都是不可见的。此级别下，执行同样的查询，可能会得到不一样的结果。因此此级别有时也叫不可重复读
• REPEATABLE READ (可重复读)
  此级别是MYSQL默认的事物隔离级别。
  此级别保证在同一个事物中多次读取同样的记录的结果是一致的。但是，此级别还有另外一个无法解决的问题，幻读。幻读，指的是当某个事物在读取某个范围内的记录时，其他的事物在该范围内插入了新的记录，当事物在此读取该范围的记录时，会产生幻行.InnoDB 和 XtraDB存储引擎通过多版本并发控制解决了幻读的问题。
• SERIALZABLE (可串行化)
  此级别时事物的最高级别。它通过强制事物串行执行，避免幻读问题，但是会导致大量的超时和锁争用的问题。他的原理是在读取的每一行数据上都加锁。

死锁

死锁是指两个或者多个事物在同一资源上相互占用，并请求锁定对方占用的资源，从而导致的恶性循环的现象。当多个事物以不同的顺序锁定资源时，就可能会产生死锁。
数据库系统实现各种死锁检测和死锁超时机制
InnoDB 处理死锁的方法：将持有最少行级排他锁的事物进行回滚。

事物日志

目前大多数存储引擎都采用预写式日志。

预写式日志：存储引擎在修改表的时候只需要修改其内存拷贝，再吧该修改行为记录到持久在硬盘上的事物日志中，而不用每次都将修改的数据本身持久到磁盘。事物日志持久后，内存中被修改的数据在后台可以慢慢地刷回到磁盘。

MySQL中的事物

MySQL提供的两种事务型存储引擎：InnoDB，NDB Cluster。

MySQL的自动提交机制

• MySQL中默认采用自动提交模式，如果不是显示地开始一个事物，每个查询都会被当作一个事物执行提交操作。
  可以通过设置AUTOCOMMIT变量来改变自动提交模式

• 当AUTOCOMMIT=0 时，所有的查询都在一个事物中，直到显示执行COMMIT或ROLLBACK时，事物结束，同事开启新的事物。

  还有些命令会强制执行COMMIT提交当前活动的事物 ，例如DDL语句

• 可以通过执行SET TRANSACTION ISOLATION LEVEL XXX来设置隔离级别，新的级别将会在下个事物开始生效。
  

• 设置当前回话的隔离级别SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED

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

MySQL中服务层不管理事务，事务都是由下层存储引擎实现的。

如果在事物中混合使用了事务型和非事务型表，在正常提交情况下没有任何问题，但是一旦事物需要回滚时，非事务型表上的操作就无法撤销，这样就会造成数据库不一致的问题

显示和隐式锁定

• 隐式锁定
  InnoDB 会根据隔离级别在需要的时候自动加锁，在事物的执行过程中，随时都可以执行锁定，但是只有在执行COMMIT或ROLLBACK时候进行锁释放。
• 显示锁定
  SELECT ... LOCAK IN SHARE MODE
SELECT ... FOR UPDATE

多版本并发控制

多版本并发控制(MVCC)的实现，是通过保存数据在某个时间点的快照来实现的。根据事物的开始时间不同，每个事物对同一张表，同一个时刻看到的数据可能不一样，同一个事物不管需要执行多长时间，看到的数据都是一致的。

InnoDB 的 MVCC

通过在每行记录后面保存两个隐藏的列来实现。一个保存的是行的创建时间，一个保存的是行的过期时间。其中存储的是系统版本号，每开始一个新的事物，系统版本号都会自动递增，事物开始时的系统版本号作为事物的版本号。

InnoDB MVCC具体操作：

• SELECT 时，查询复合以下条件的数据：
  • 创建时间版本早于或等于当前事物版本。 -- 这样可以确保查询的数据在事物开始前就已经存在，或者由事物本身插入
  • 删除时间未定义或大于当前事物版本。 -- 这样可以确保查询到的数据在事物开始之前未被删除
• INSERT 时，
  为新插入的每一行保存当前事物版本号作为创建时间
• DELETE 时，
  为删除的每一行保存当前事物版本号作为删除时间
• UPDATE 时，
  新插入一行记录，保存当前事物版本号作为创建时间，同事保存事物版本号作为原来行的删除时间

MVCC 只在 REPEATABLE READ 和 READ COMMITTED两个隔离级别下工作

原文：https://www.cnblogs.com/herberts/p/11185068.html