《高性能MySQL》读书笔记

mysql架构

mysql被设计为三层架构。

第一层负责对外提供服务，具有连接处理、网络通信、授权认证、安全等功能。

第二层包括了MySQL的大部分核心功能，例如查询解析、分析、优化、缓存 和MySQL的所有内置函数的实现、还有所有跨存储引擎的功能，例如存储过程、触发器、视图。

第三层包括了存储引擎，mysql有多个不同的存储引擎，功能也不相同，可以根据业务的需要选择存储引擎。服务器通过api与存储引擎通信。

锁

读写锁

mysql 提供了两种锁，被称为读锁和写锁，也称为共享锁和排它锁。

读锁可以共享，对同一资源可以有多个线程持有读锁。其他资源不能加写锁。

写锁是排他的，同一资源只能有一个线程持有其写锁，同时其他线程也不能对此资源加读锁。

锁粒度

mysql锁具有不同的粒度，一般分为表级锁和行级锁，表级锁就是读整个表加锁，行级锁就是只对操作相关的行加锁。锁粒度越大对并发能力的负面影响越大，对系统的开销也越大。（myisam就是使用行级锁，因此修改效率差，而innodb使用行级锁具有更好的读写效率）

事务

事务包括了一组操作，这一组操作要么都执行，要么都不执行。

数据库事务

数据库事务是由多个sql语句组成的，以start transaction开始，当执行commit的时候生效。中途如果遇到rollback则之前执行的语句全部无效。

事务的ACID原则

A（Atomicity）原子性

原子性就是指事务要么执行其包括的所有操作，要么都不执行。中途如果有一个语句失败则之前执行的全部回滚。

C（Consistency）一致性

数据库的状态总是从一个一致性状态转移到另一个一致性状态。意思是说如果一个事务执行到一半失败，已经执行过的语句不会持久化，而会被回滚。

I（Isolation）隔离性

一个事务的修改在最终提交（commit）之前，对于其他事务是不可见的。

D（Durability）持久性

一旦事务被提交，发生的变化是持久的，即使数据库崩溃也不会丢失。

MYSQL的Innodb如何实现ACID

Atomicity 原子性

undo log（回滚日志）保证了mysql事务的原子性。

Isolation 隔离性

通过锁机制、MVCC等方式保证了MySQL事务的隔离性。

Durability 持久性

redo log（重做日志）保证了事务的持久性。即使mysql崩溃也可以从redo log中恢复尚未持久化的数据。

Consistency 一致性

当前三种特性得到保障后一致性也会被保障。

事务并发会出现的问题

和线程的并发相同，事务并发如果不加以控制或控制的幅度不够会发生以下几种异常现象

脏读

一个事务还么提交，另一个事务读取到已经被修改数据。即为脏读。

修改丢失

原本数据A = 20 ，一个事务对数据A进行+1，此时另一个事务也对数据A进行+1。两个事务先后提交。最后数据A = 21。说明有一个事务的修改没有生效，这种现象称为修改丢失。

不可重复读

数据A = 20，B事务在过程中先后两次读取数据A，C事务将数据A加上1，于是在B、C事务并发的过程中，B事务两次读取的A分别为20,21。两次读取统一数据结果不一致，这种现在称之为不可重复读。

幻读

原本数据表有5行数据，事务A先后两次读取行数，事务B给数据表添加一行数据。A、B并发执行中，A数据先后读取到的行数为5行、6行。就好像之前一次查询发生了幻觉一样，所以称之为幻读。

幻读和不可重复读的区别

总结一句话：幻读是表级别的，不可重读读是行级别的。

事务的隔离级别

为了避免发生前文所说的事务并发的几种异常，数据库需要对事务与事务进行控制，这种控制称为隔离，隔离分为几个级别。级别越高对系统的消耗越大，数据库的增删改查效率越收影响，但对事务的并发控制效果越显著。

SQL标准定义了4个隔离级别

READ-UNCOMMITTED 未提交读

第一级别其实可以视为没有对并发进行隔离，在这种级别下，一个事物即使还没有做提交。它所做的改变也会被其他事务所读取。因此上述的各个问题都会发生。

READ-COMMITTED 提交读

第二级别下，已提交的事务做的修改可以被其他事务读取。但是其他事务不会读到未提交的修改，因此不会发生脏读，但是其他三种异常情况依然会出现。（这个级别也是大部分数据库默认的隔离级别。但是mysql不是）

REPEATABLE-READ 可重复读

第三级别下，Innodb通过MVCC（多版本并发控制）技术实现了同一个事务多次读取同一条记录的结果必然是一样的。在这种级别下，不会发生不可重复读，但是依然会发生幻读的现象。（MYSQL默认的隔离级别就是REPEATABLE-READ）

SERIALIZABLE 串行化

最高级别串行化其实已经可以视为事务不是并发执行，因为这个级别强制事务必须一个接着一个的执行。每次只能执行一个事务，因此以上所述的每个异常现象都不会发生。（serializable极度影响数据库的读写效率，因此除非业务极度需要不会使用这个级别）

死锁

和并发进程相同，事务并发执行必然会引发死锁现象。当出现两个事务互相占用对方需要的资源，并且请求对方持有的资源时，死锁现象就会发生。

例如innodb默认的行级锁。当事务A修改了X行数据后请求Y行的锁，事务B修改了Y行后请求X行的锁，当A、B事务并发执行到这个时间点时死锁就发生了（因为只有当事务commit或rollback时锁才会被释放）。

因此mysql实现了一系列的死锁检测机制和死锁纠正机制

1. 死锁超时机制，当锁持有时间超过超时时间后释放锁。
2. 当死锁发生时，将持有最少行级排它锁的事务rollback（innodb的处理机制）

MYSQL的事务

innodb支持事务，而MYISAM不支持事务。但是mysql基于分层架构，服务层是否声明事务并不依赖于存储引擎。只是在使用不支持事务的存储引擎如myisam时，事务并不会生效。

mysql默认采用的是事务自动提交模式 AUTOCOMMIT，此模式下如果不是显示的声明一个事务，每一个语句都会被放进一个事务中执行，不需要显示的声明事务。

同时也可以开关自动提交模式（set autocommit = 1/0 , 1 = 开启， 0 = 关闭）。当关闭了自动提交模式后，每一个语句都是在同一个事务中，直到输入了commit才会生效，或者输入rollback回滚，事务才算结束。

部分命令会强制执行commit

如alter table 修改表结构或显示锁定 lock tables都会强制的执行commit

在事务中使用混合引擎

比如同一个事务涉及了一张innodb表和一张myisam表，当事务正常执行时不会有任何问题。但是当事务发生错误需要回滚的时候，innodb表上所作的修改可以被正常回滚而myisam表无法回滚。

显示加锁

mysql会根据指定的事务隔离级别隐式的给表或行加锁，但是同样也可以是使用select … lock in share mode 、 select … for update 、lock tables unlock tables 显示的加解锁。但是不要使用这样的方式，这些语句会和事务的自动加锁互相影响会严重影响性能。（实际上innodb的行级锁已经非常有效并且高效了）

多版本控制（MVCC）

实际情况下，mysql大部分的事务型存储引擎都不是用简单的行级锁来控制事务的并发（加锁对性能有一定损耗）。因此各个支持事务的存储引擎都以各自的方法实现了MVCC机制（read-uncommit和serializable级别下是不使用MVCC的）。这种设计方式可以在大部分的操作时都不需要加锁，使读写数据的性能更好，缺点是需要多余的存储空间，以及版本检查时和维护版本号时需要额外的时间开销。

INNODB的MVCC实现逻辑

innodb通过在每行的记录后保存两个隐藏的列，一个保存行的创造时间，一个报错行的过期时间（或删除时间）。（其实存储的不是具体时间而是系统版本号）

每开始一个事务，系统版本号都会自增1，事务开始的时候会以当前的系统版本号作为自己的事务版本号，用来和查询到的每行记录的版本号做比较。

REPEATABLE-READ级别下MVCC的具体工作

select时

innodb限定了查询的数据行具有以下两个特性

1. 版本小于等于当前事务版本号
2. 删除版本号大于当前版本号或不存在

insert时

为每行insert的数据设置版本号为当前系统版本号

delete时

为每行delete的数据设置过期版本号为当前系统版本号

update时

插入一行新数据保存修改后的数据，设置版本号为当前系统版本号。原来的行不变，将其过期版本号设为当前系统版本号。