innodb 通过多版本并发控制(MVCC)来获得高并发性,并且实现了SQL标准的4种隔离级别,默认为REPEATABLE级别。同时,使用一种被称为next-key locking的策略来避免幻读(phantom)现象的产生。除此之外,InnoDB存储引擎还提供了插入缓冲(insert buffer)、二次读写(double write)、自适应哈希索引(adaptive hash index)、预读(read ahead)等高性能和高可用的功能。
一、MVCC(Multi-Version Concurrency Control)多版本并发控制。
特点,读不加锁,读写不冲突。
1. mvcc读操作
- 快照读 (snapshot read):
读取的是记录的可见版本 (有可能是历史版本),不用加锁 - 当前读 (current read):读取的是记录的最新版本,并且,当前读返回的记录,都会加上锁,保证其他事务不会再并发修改这条记录。
- 快照读使用
# 简单select操作,属于快照读(不加锁)
select * from table where ? ;
- 当前读使用
#特殊的读操作,插入/更新/删除操作,属于当前读
#以下,除了第一句,加的是共享锁(S锁),其他加的都是排他锁(X锁)
select * from table where ? lock in share mode;
select * from table where ? for update;
insert into table values (...);
update table set ? where ?;
delete from table where ?;
- 解释:为什么将 插入/更新/删除 操作,都归为当前读
update操作流程
1、mysql 收到update sql语句
2、mysql server根据where条件,读取第一条满足条件的记录
3、InnoDB引擎将第一条记录返回,并加锁(current read)
4、mysql server收到这条加锁的记录之后,会再发起一个update请求,更新这条记录。
5、重复第二条,再次读取一条满足条件的数据
2.mvcc实现
innodb的mvcc是通过在每行记录后面保存两个隐藏的列来实现的。一个列报错了行的创建时间,一个保存行的过期时间(或删除时间)(这边说的时间不是具体的时间值,而是系统版本号,每开启一个新事务,版本号递增)。
select 只有满足以下两个条件,才能作为结果返回
a. innodb只查找版本号早于当前事务版本号的数据行,这样就能确保事务读取的行,要么是事务开始前已经存在,要么是事务自身插入或者修改过。
b. 行的删除版本号要么未定义,要么大于当前事务版本号。这样能保证事务读到的行,在事务开始前未被删除
mvcc只在repeatable read 和 read uncommited 隔离级别下工作
二、InnoDB锁类型。
InnoDB存储引擎实现如下两种标准的行级锁
- 共享锁(S Lock), 允许事务读一行数据
- 排他锁(X Lock),允许事务删除或更新一行数据
共享锁和排他锁的兼容
type | X | S |
---|---|---|
X | 不兼容 | 不兼容 |
S | 不兼容 | 兼容 |
注意:
(1)S锁和X锁都是行锁,兼容是指对同一记录(row)锁的兼容性.
(2)事务T1已经获得行R的共享锁,另一个事务T2可以立即获得行R的共享锁,这种情况称为锁兼容。事务T3想获得行R的排他锁,则必须等待事务T1、T2释放行R上的共享锁,这种情况成为锁的不兼容.
意向锁
作用:检测表锁和行锁的冲突。
说明:对细粒度的对象上锁,需要先对粗粒度的对象上锁。
例1:对行R上X锁,需要先对数据库D、表A、页P(innodb存储的最小物理单位)上IX锁。若任何一部分导致等待,该操作需要等粗粒度的锁完成;
例2:在行R上X锁之前,已经有事务对表A加了S锁,之后事务需要对表A加IX锁,由于不兼容,所以,该事务需要等待。
- 共享意向锁(IS LOCK)
- 排他意向锁(IX LOCK)
锁兼容:
type | IS | IX | S | X |
---|---|---|---|---|
IS | 兼容 | 兼容 | 兼容 | 不兼容 |
IX | 兼容 | 兼容 | 不兼容 | 不兼容 |
S | 兼容 | 不兼容 | 兼容 | 不兼容 |
X | 不兼容 | 不兼容 | 不兼容 | 不兼容 |
.
三、事务隔离级别
在讲事务隔离级别之前,我们先讲讲数据库的并发操作,可能带来的问题:
- 更新丢失 : 事务1的更新覆盖了事务2的更新操作。
- 读脏数据 :事务1读到了事务2未提交的数据,事务2若回滚,事务1读到的就是错误的数据。
- 不可重复读 : 事务1两次读取的数据不一致(原因,事务1两次读取的间隙,事务2修改/删除了数据)。
- 幻读 : 事务1两次读取数据不一致(原因,事务1两次读取间隙,事务2新增了数据)。
如果不太明白,请继续往下看。
SQL标准的四种隔离级别(Innodb全部支持)
- READ UNCOMMITTED
事务中的修改,即使没有提交,对其他事务也是可见的。事务可以读取未提交的数据,导致脏读。 - READ COMMITTED
事务不能读取未提交的数据,避免脏读,但容易导致不可重复读(两次相同的查询操作,可能读到不同的数据)。 - REPEATABLE READ
该隔离级别下,保证同一事务中,两次相同的查询操作结果是一致的,避免不可重复读的发生,但不能防止幻读的发生 (innodb解决了该隔离级别下幻读的发生,下面会介绍。) - SERIALIZABLE
事务串行执行,避免幻读。它会在读的每一行数据都加锁。
InnoDB存储引擎默认支持的隔离级别是REPEATABLE READ,但是与标准的SQL不同的是,InnoDB存储引擎在REPEATABLE READ事务隔离级别下使用Next-Key Lock算法,避免了幻读的产生。达到了SQL标准的隔离级别SERIALIZABLE
理解Next-Key Lock
next-key lock是一个区间锁,它锁定的是一个范围
下面通过实例来说明:
mysql> create table test2(id int)engine = innodb;
Query OK, 0 rows affected
mysql> insert into test2 value(1),(5),(9),(12),(18);
Query OK, 5 rows affected
Records: 5 Duplicates: 0 Warnings: 0
# session 1
mysql> start transaction;
Query OK, 0 rows affected
mysql> select * from test2 where id = 9 for update;
+----+
| id |
+----+
| 9 |
+----+
1 row in set
#session 2
mysql> insert into test2 select 6;
#session 2 这个操作会被一直阻塞,直到session1的事务提交
通过上面例子可以知道,test2表有1,5,9,12,18 五条数据,当我们为9那行加排他锁的时候,innodb会锁住两个区间5-9,9-12,包括5,不包括12。(可以自己试验下).
修改数据库事务隔离级别
set [session|global]
transaction isolation level
[READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE]
#例如
#设置当前会话的事务隔离级别为READ UNCOMMITTED
set session transaction isolation level read uncommitted;
#设置全局的事务隔离级别为REPEATABLE READ
set global transaction isolation level repeatable read;
四、Innodb后台线程
InnoDB存储引擎是多线程的模型,因此后台有多个不同的后台线程,负责处理不同的任务
- Master Thread
核心线程,负责将缓冲池中的数据异步刷新到磁盘,保证数据的一致性,包括脏页的刷新、合并插入缓冲、UNDO页的回收等。 - IO Thread
mysql存储引擎中,大量使用AIO来处理写IO请求,IO Thread用于处理这些IO请求的回调 - Purge Thread
事务提交后,其所使用的undolog可能不再需要,因此需要Purge Thread来回收已经使用并分配的undolog页(1.1以前,purge操作在master thread中进行,1.1以后,独立在purge thread中) - Page Cleaner Thread
执行脏页刷新操作(1.2以后引入的,之前在master thread中操作)
五、日志
- 错误日志
# 查看错误日志路径
show variables LIKE 'log_error';
查询结果:
Variable_name | Value |
---|---|
log_error | /var/log/mysqld.log |
.
- 慢查询日志
慢查询日志可以帮助我们定位可能存在问题的sql语句,从而进行sql语句层面的优化。
MySQL默认没有开启慢查询日志,需要手动开启
# 查看 慢查询日志是否开启
show variables LIKE 'slow_query_log';
查询结果:
Variable_name | Value |
---|---|
slow_query_log | OFF |
设置slow_query_log 的值为 ON
# 开启慢查询日志
set global slow_query_log=ON;
其他慢查询日志参数配置 (使用同样的方式,查看或修改参数值)
long_query_time : 设定慢查询的阀值,超出次设定值的SQL即被记录到慢查询日志,缺省值为10s
slow_query_log : 指定是否开启慢查询日志
log_slow_queries : 指定是否开启慢查询日志(该参数要被slow_query_log取代,做兼容性保留)
slow_query_log_file : 指定慢日志文件存放位置,可以为空,系统会给一个缺省的文件host_name-slow.log
min_examined_row_limit:查询检查返回少于该参数指定行的SQL不被记录到慢查询日志
log_queries_not_using_indexes: 不使用索引的慢查询日志是否记录到索引
[1]姜承尧 .MySQL技术内幕:InnoDB存储引擎 机械工业出版社
[2][http://hedengcheng.com/?p=771](http://hedengcheng.com/?p=771)