浅谈mysql

 update T set c = c + 1 where ID = 2

update语句执行流程?

（1 执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果ID=2 这一行所在的数据页本来就 在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。

(2). 执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
(3). 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
(4). 执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
(5). 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

mysql主要功能模块：

1）连接器(连接/线程处理器)：主要负责管理客户端连接，权限验证。
2）查询缓存：负责缓存查询结果。
3）解析器：对sql语句进行词法分析，语法分析，校验sql语句。
4）优化器：对解析器解析完成的sql生成执行计划，选择索引。
5）存储引擎：存储数据，负责提供读写接口。

select语句执行流程?

1）建立连接

2）查询缓存

3)解析器会对sql语句进行分析

4)优优化器会在连表查询的时候确定怎样的查询顺序比较好，或是有多个索引的时候决定用哪一个索引，不用哪一个索引等等，它会根据执行的效率进行判断。当优化器决定了最终的执行方案后，就会交由执行器进行执行。

5)执行器会先判断当前的登陆用户是否有权限访问user这个表。根据优化器的分析如果是全表扫描的话就会调用InnoDB执行引擎调取第一行，记录之后调取“下一行”接口……直至找到；如果是通过索引查找，下文会详细介绍查找过程，最后执行器将所有结果的集合返回给用户。

 

问题二：mysql是怎么保证事务的acid?

A:原子性
原子性是指一个事务就是一个不可分割的工作单位，要么全部都执行成功，要么全部都执行失败，没有中间状态或是只执行一部分。

MySQL的InnoDB引擎是靠undo log（回滚日志）来实现的，undo log能够保证在事务回滚时，能够撤销所有已经执行成功的SQL。

例如在执行insert语句时会生成相关的delete语句的undo log。反之执行delete语句也会生成相关的insert语句的undo log。执行update语句时也是如此，不过update语句在执行undo log回滚时有可能会涉及到MVCC。主要是为了保证在执行undo log的时候的select能看到哪个版本的数据。
D:持久性

持久性是指事务一旦提交，对数据库的操作就是永久性的，接下来的其他操作和异常故障不应该对它有任何影响。

我们都知道MySQL的数据最终是存放在磁盘中的，所以才会有磁盘的容量大小决定数据容量的大小。但是如果对MySQL的操作都是通过读写磁盘来进行的话，那么光是磁盘的I/O就够把效率大大的拉低了。

所以InnoDB为MySQL提供了缓冲池（Buffer Pool）,Buffer Pool中包含了磁盘中部分数据页的映射。

当从数据库读取数据时，会先从Buffer Pool中读取数据，如果Buffer Pool中没有，则从磁盘读取后放入到Buffer Pool中。

当向数据库写入数据时，会先写入到Buffer Pool中，Buffer Pool中更新的数据会定期刷新到磁盘中（此过程称为刷脏）。

虽然Buffer Pool为MySQL的读写提高了效率，但是却也带来了新的问题，那就是如果数据刚更新到Buffer Pool中还没来得及刷新到磁盘中时，MySQL突然宕机了，这就会导致数据丢失，造成事务的持久性无法保证了。

为了解决这个缓存的一致性问题，redo log就出现了。在对Buffer Pool中的数据进行修改的时候通过redo log记录这次操作，当事务提交时会通过fsync接口对redo log进行刷盘。

redo log是记录在磁盘中的，所以当MySQL出现宕机时，可以从磁盘中读取redo log进行数据的恢复，从而保证了事务的持久性。

redo log 采用的预写的方式记录日志，即先记录日志，再更新Buffer Pool，这样就强行的保证了，数据只要保存在了redo log中就一定

I：隔离性

原子性和持久性都是基于单个事务内部的措施，而隔离性是只多个事务之间相互隔离，互不影响的特性。

我们都知道事务的隔离级别中最严谨的是串行化（Serializable）,但是隔离性越高，性能就越低，所以一般不使用串行化这个隔离级别。

对于隔离性的，我们要分两种情况进行讨论：

一个事务中的写操作对另一个事务中的写操作的影响；

一个事务中的写操作对另一个事务中的读操作的影响；
首先，事务间的写操作其实是靠MySQL的锁机制来实现隔离的，而事务间的写和读操作是靠MVCC机制来实现的

C：一致性

事务的最终目的，即需要数据库层面保证，又需要应用层面进行保证，

通过上面两个阶段提交保证了事务数据的一致性。
当事务提交redo log处于prepare状态的时候，发生mysql宕机或者奔溃，则会执行事务回滚。
当事务提交redo log 处于commit阶段时候，发生奔溃会执行事务恢复。本机事务会通过redo log恢复。而如果是主从数据库的话，在commit阶段，会根据binlog对数据库进行恢复。

 

 

MySQL中的锁主要有：

按照功能分：读锁和写锁；按照作用范围分：表级锁和行级锁；

还有意向锁，间隙锁等。

读锁：又称“共享锁”，是指多个事务可以共享一把锁，都只能访问数据，并不能修改。

写锁：又称“排他锁”，是不能和其他事务共享数据的，如果一个事务获取到了一个数据的排他锁，那么其他事务就不能再获取该行的其他锁，包括共享锁和排他锁。

表级锁：是指会将整个表进行锁定，性能较差，不同存储引擎支持的锁的粒度不同，MyISAM引擎支持表级锁，InnoDB引擎支持表级锁也支持行级锁。

行级锁：会将需要操作的相应行进行锁定，性能好。

意向锁：意向锁是表级锁，如果在一个事务已经对一个表中的某个数据加上了排他锁或共享锁，那么就可以加上意向锁，这样当下一个事务来进行锁表的时候发现已经存在意向锁了，就会先被阻塞，如果不加意向锁的话，第二个事务来锁表的时候需要一行一行的遍历查看是否有数据已经被锁住了。

间隙锁：间隙锁是为了防止产生幻读而加的锁，加在不存在的空闲空间，可以是两个索引记录之间，也可能是第一个索引记录之前或最后一个索引之后的空间（但是并不包含当前记录）。这样就保证了在间隙锁执行的时候，新增的数据会阻塞，保证了一个事务中的两次查询获得的记录数都是一致的。

Next-Key Lock：Next-Key Lock是行级锁和间隙锁的结合产生的锁，因为间隙锁是不会锁住当前记录的而Next-Key Lock是会将当前记录也锁住的。

mysql事务隔离特性：

*读未提交（read uncommitted）：**一个事务还没提交时，它做的变更就能被别的事务看到。
**读提交（read committed）（RC）：**一个事务提交之后，它做的变更才会被其他事务看到。
可重复读（repeatable read）（RR）：一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
**串行化（serializable ）：**顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

 

当数据库上有多个事务同时执行的时候，就可能出现如下问题：

• 脏读（dirty read）：读取了未提交的数据（涉及数据修改）
• 不可重复读（non-repeatable read）：同个事务中多次读取的同一条数据的值不同（涉及数据修改）
• 幻读（phantom read）：同个事务中相同条件下查出的数据条数不同（涉及数据增删）

可重复读怎么解决幻读？

可重复读本身还是存在幻读的，但是因为在可重复读上添加了NEXE-KEY锁(即行锁+gap锁)避免了幻读的产生。

 

mysql日志：redo log、binlog、undo log 区别与作用

redo log :

 重做日志

　　作用：确保事务的持久性。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性。

　　内容：物理格式的日志，记录的是物理数据页面的修改的信息，其redo log是顺序写入redo log file的物理文件中去的。

bin log

　归档日志（二进制日志）

　　作用：用于复制，在主从复制中，从库利用主库上的binlog进行重播，实现主从同步。
用于数据库的基于时间点的还原。

　　内容：逻辑格式的日志，可以简单认为就是执行过的事务中的sql语句。

undo log

回滚日志

　　作用：保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读

　　内容：逻辑格式的日志，在执行undo的时候，仅仅是将数据从逻辑上恢复至事务之前的状态，而不是从物理页面上操作实现的，这一点是不同于redo log的。