• MySQL redo日志恢复

    背景

    前面主要介绍了日志的基础概念,但这些都是为了有朝一日我们的数据库挂了而做准备的,那么数据库如果挂了以后,我们的redo 又是如何发挥作用的呢 ?

    这就要从InnoDB的开机启动说起了。InnoDB启动之前,肯定是处于shutdown状态的,而导致shutdown状态的要么是正常关闭,要么是意外Crash。数据恢复主要是针对异常Crash的。

    在开始准备做数据库恢复时,首先要做的就是从日志文件中找到最新的检查点信息。因为只有知道最新的检查点了,我们才知道从哪里开始恢复。我们已经知道,在日志文件最开始的4个页面中,存储的就是用来管理日志文件及日志写入情况的信息,具体格式如下:

    %e5%b1%8f%e5%b9%95%e5%bf%ab%e7%85%a7-2017-07-13-%e4%b8%8a%e5%8d%8811-59-46

    这里关注的信息就是 LOG_CHECKPOINT_LSN , 找到这个值以后也就意味着这个点之前的所有日志都是失效的。而这个点之后的页面,有可能是完整的,也有可能是需要做REDO的。所以需要扫一遍,然后将那些大于此点的页做REDO。 这个就是大概的一个思路,具体的我们来看看代码:

    上面的代码就是 recv_recovery_from_checkpoint_start_func 函数的执行过程,主要包括两部分:

    • 从日志文件的固定位置找到最新的检查点(最近一次checkpoint)相关的信息。
    • 从最新的检查点位置开始扫描日志文件,做数据库的恢复。

    注: 在调用完函数 recv_recovery_from_checkpoint_start 之后,又调用了 recv_recovery_checkpoint_finish。 这个函数主要是用来做数据库回滚的地方,也可以看出来,InnoDB的REDO是在UNDO之前做的,是等到物理的数据库操作都完成后,才能在物理数据一致的基础上去做一些逻辑操作,比如UNDO回滚操作。

     

  • MySQL数据库锁(三)

    InnoDB锁阻塞

    因为不同锁之间的兼容性关系,在有些时候一个事务中的锁要等待另一个事务中的锁释放它所占用的资源,这就是阻塞。这里需要明确的是,阻塞不是死锁,并不一定是坏事,它的存在意义是为了确保事务可以并发且正常的运行。

    在InnoDB存储引擎中,参数Innodb_lock_wait_timeout用来控制锁等待的时间。

    %e5%b1%8f%e5%b9%95%e5%bf%ab%e7%85%a7-2017-04-09-%e4%b8%8b%e5%8d%889-36-32

    当一个事务锁等待超过这个时间以后,会产生如下错误:

    当这个错误报出后,当前的这个SQL语句将被回滚(是SQL语句,不是事务),如果想在这种时候让事务也回滚的话可以设置下面这个参数。

    Innodb_rollback_on_timeout用来设定是否在等待超时时对事物进行回滚操作(默认是OFF,不回滚)。

    %e5%b1%8f%e5%b9%95%e5%bf%ab%e7%85%a7-2017-04-09-%e4%b8%8b%e5%8d%889-49-10

    我们来实践一下:

    %e5%b1%8f%e5%b9%95%e5%bf%ab%e7%85%a7-2017-04-09-%e4%b8%8b%e5%8d%8810-11-13

    注:我这张图片做的真的好丑啊!!!!

    如上,即使我在插入199时锁等待超时了,但是整个事务并没有回滚,在事务2提交之后,我依旧可以从事务1中读到id为2980的值。也就是说,session在锁等待超时后虽然抛出了异常,但是既没有主动commit 也没有主动rollback。 而这是十分危险的状态,因此用户必须判断是否需要commit或者rollback,之后再做下一步的操作。

    这里可能会有疑惑,MVCC的作用不就是读写不冲突吗 ? 为什么我在两个事务中的读写还互相阻塞呢 ?

    MVCC 虽然可以做到读写不冲突,但是MVCC中所谓的“读写”中的“读”指的是 快照读 ,“写”指的是当前读。 上面的例子中是当前读和当前读,相当于两个写操作,当然还是冲突的。


    InnoDB 死锁

    死锁是指两个或两个以上的事务在执行过程中,因争夺资源而造成的一种互相等待的现象。 若无外力作用,则事务将无法推进下去。我们随便举两个常见的例子:

    1. 这种情况很好理解,事务A和事务B操作两张表,但出现循环等待锁情况

    session1 session2
    begin begin
    # 这里两边事务都插入数据是为了方便观察一会有死锁后任何一个事务回滚后是否会将这些insert一起回滚,还是只回滚当前SQL 。

    mysql> insert into t5 (name) values  (“11111”),(“22222”),(“33333”) ;
    Query OK, 3 rows affected (0.00 sec)
    Records: 3  Duplicates: 0  Warnings: 0

    mysql> insert into t3 (name) values (“puppet”)  ;
    Query OK, 1 row affected (0.00 sec)
    mysql> update t5 set name = “d1” where id = 1 ;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    mysql> update t3 set name = “ansible” where id = 1;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    # 更新时阻塞住了

    mysql> update t3 set name = “salt” where id = 1;

    # 同时这边的阻塞也通了

    Query OK, 1 row affected (9.41 sec)
    Rows matched: 1  Changed: 1  Warnings: 0

    # 这边刚执行这条SQL,就直接报死锁发生了,本事务回滚。

    mysql>  update t5 set name = “d2” where id = 1 ;
    ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
    mysql>

    commit commit

    从上面的测试中我们得到如下结果:

    • 死锁发生时,立刻就被MySQL发现并选择其中一个事务回滚了,并没有感觉阻塞的过程。
    • 和阻塞的回滚不一样的是,从t3表中并没有查到事务2的插入,所以是将事务2整个事务回滚掉了。

    2. 相同表记录的行锁冲突,两个job在执行数据批量更新时,session 1 处理的的id先后顺序与 session 2处理的id先后顺序顺序相反相交,就可能造成死锁。

    session1 session2
    begin begin
    mysql> update t3 set name = “000000” where id = 1;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    mysql> update t3 set name = “99999” where id = 2;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    # 阻塞了

    mysql> update t3 set name = “888888” where id = 2;

    # 这边立马就通过了
    Query OK, 1 row affected (9.37 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    # 执行后立马报死锁,失败回滚

    mysql> update t3 set name = “77777” where id = 1;
    ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
    mysql>

    mysql> commit ;
    Query OK, 0 rows affected (0.01 sec)

    那看上面这个情况,感觉死锁的场景还是很有可能的啊,那么怎么去避免死锁呢 ?

    可能这时候你会想到,如果只要事务等待了就把它回滚,并且让事务重新开始不就完了吗 ? 这或许是一种解决办法,但是风险很大,因为在线上环境中,这可能导致并发性能的下降,甚至到某些情况下任何一个事务都不能进行,这样的话造成的后果比死锁还严重,得不偿失了。

    解决死锁问题最简单的一种办法是超时,即当两个事务互相等待时,当一个等待时间超过设置的某一阈值时,其中一个事务进行回滚,另一个等待的事务就可以继续进行了。 在InnoDB存储引擎中,参数innodb_lock_wait_timeout用来设置超时的时间。

    超时机制虽然简单,但是它遵循的FIFO(先入先出)的回滚原则,这时候就可能会有问题,因为倘若先超时的那个事务所占权重比较大,回滚代价也比较高,那么其实如果回滚其他的可能更划算些。

    这点MySQL也想到了,因此,除了超时机制,当前数据库都还普遍采用 wait-for graph(等待图)的方式来进行死锁检测。 较之超时的解决方案,这是一种更为主动的死锁检测方式。InnoDB存储引擎也采用这种方式。这也是我们上面的死锁为什么刚发生就立刻回滚的原因。

    wait-for graph 要求数据库保存以下两种信息:

    • 锁的信息链表
    • 事务等待链表

    它根据这两个列表主动的去发现是否存在回环来确定死锁的存在。在每个事务请求锁并发生等待时都会判断是否存在回路,若存在则有死锁,通常来说InnoDB会选择回滚undo量最小的事务


    那么到这里,大家可能会有疑问,死锁这么可怕,它发生的概率高吗 ? 都和哪些因素有关 ?

    在这里我们举个例子:假设当前数据库中共有100(n)个并发线程在执行,而且每个线程都是在执行同样的一个事务,这个事务由10 (r)个操作组成,每个操作从10000 (R )行数据里面随机操作一行数据,并占用该行的锁。每个事务在执行完最后一个操作后释放锁占用的所有锁资源。

    在上述模型下:

    • 事务发生锁等待的概率为: 1 / 2  (二分之一)
    • 一个事务发生死锁的概率为:  1 / 400 (四百分之一)
    • 这个系统中有可能发生死锁的概率为: 1 / 4  (四分之一)

    可能觉得有点难以接受?  我这个系统中竟然有四分之一的概率发生死锁。大兄弟,我们这个案例可是开了一百个并发执行同样的事务哦。

    死锁发生的概率主要与以下几点因素有关:

    • 系统中同一时间运行的事务的数量,数量越多发生死锁的概率越大(废话)
    • 每个事务包含的操作越多,发生死锁的概率就越大(废话)
    • 操作的数据集合越小,发生的死锁概率就越大

    注:当一个事务发生死锁后它会回滚,这和我们之前说的事务中锁等待的情况不一样。总结起来说:如果一个事务中的某个操作锁等待超时了,那么这个事务不会回滚,而如果一个事务死锁了,那么InnoDB存储引擎会回滚整个事务哦。


    InnoDB锁信息查看

    其实,对于我们做数据库运维来说,查看锁信息并依次来定位问题至关重要。 或许我们对锁信息的查看还仅仅局限于通过 show processlist 或者 show engine innodb status 命令。但其实在5.6及以上的版本中我们不必再那样去看了,因为那样看很不直观,而且在高并发的情况下执行结果瞬间挤满屏幕,很不利于我们分析查看。

    information_schema 库底下有三张表分别为 innodb_trx 、innodb_locks、innodb_lock_waits 。通过这三张表,我们可以更简单的监控当前事务并分析可能存在的锁问题。

    innodb_trx

    这张表描述了当前数据库系统下所有正在进行的事务的状态

    名称 属性
    TRX_ID 唯一的事务ID号
    TRX_WEIGHT 一个事务的权重,主要反映改变的记录数和被事务锁定的记录数。 在处理死锁时, InnoDB 会选择一个具有小权重的事务作为"受害者”回滚。
    TRX_STATE 事务执行的状态, 允许的值为 RUNNING, LOCK WAIT, ROLLING BACK, and COMMITTING.
    TRX_STARTED 事务开始时间
    TRX_REQUESTED_LOCK_ID 如果TRX_STATE值为LOCK WAIT,则此值表示的是事务等待的锁ID,否则此值为null,可以使用LOCK_ID和INNODB_LOCKS表关联
    TRX_WAIT_STARTED 如果TRX_STATE值为LOCK WAIT,则此值表示事务从什么时候开始等待的
    TRX_MYSQL_THREAD_ID MySQL thread ID,得到细节关于thread, 使用这个列和NFORMATION_SCHEMA PROCESSLIST table的ID进行关联
    TRX_QUERY 事务正在执行的语句
    TRX_OPERATION_STATE 事务的当前操作(如果有的话)否则为NULL
    TRX_TABLES_IN_USE 此事务涉及的表的数量
    TRX_TABLES_LOCKED 事务加锁的表的数量
    TRX_ISOLATION_LEVEL 事务的隔离级别
    TRX_LOCK_STRUCTS 事务占据的锁的数量
    TRX_LOCK_MEMORY_BYTES 事务的锁消耗的内存
    TRX_ROWS_LOCKED 事务锁定的行数(大约值)

    innodb_locks

    顾名思义,这张表描述的是innodb存储引擎的锁信息:

    名称 属性
    LOCK_ID 全局的锁ID
    LOCK_TRX_ID 锁对应的事务ID,可以与innodb_trx做链接
    LOCK_MODE 锁模式,主要包括: S[,GAP], X[,GAP], IS[,GAP], IX[,GAP], AUTO_INC, and UNKNOWN. Lock
    LOCK_TYPE 锁类型,主要包括: RECORD for a row-level lock, TABLE for a table-level lock.
    LOCK_TABLE 锁对应的表
    LOCK_INDEX 锁住的索引
    LOCK_SPACE 所对象的space id
    LOCK_PAGE 事务锁住的页的数量,若是表锁,则该值为null
    LOCK_REC 事务锁定行的数量,若是表锁,则该值为null
    LOCK_DATA 事务锁定记录的主键值,若是表锁,则该值为null

    其实,当我们在某些场景下想查看当前数据库系统的锁情况时,可以直接查询这张表即可。下面为一个示例:

    innodb_lock_waits

    顾名思义,这张表描述了锁等待的信息:

    名称 属性
    REQUESTING_TRX_ID 被阻塞的事务ID
    REQUESTED_LOCK_ID 被阻塞的锁ID
    BLOCKING_TRX_ID 阻塞了别人的事务ID
    BLOCKING_LOCK_ID 阻塞了别人的锁ID

    这张表需要和前两张表一起联合查询能起到查看当前事务和锁的状态。

    操作实例

    查看当前数据库系统有哪些事务在等待锁 ? 处于等待状态的相关SQL是什么?在等待哪些事务完成 ?拥有当前锁的SQL是什么?比如下面这个例子:

    %e5%b1%8f%e5%b9%95%e5%bf%ab%e7%85%a7-2017-04-13-%e4%b8%8b%e5%8d%886-05-37

    我们可以这样的方式查询得出想要的结果:

    我们举个实际的例子:

    session1 session2
    begin begin
    mysql> select * from t3 for update ;
    +—-+———–+
    | id | name      |
    +—-+———–+
    |  1 | 000000    |
    |  2 | 888888    |
    |  3 | gggggg    |
    |  4 | 444444    |
    |  5 | ccccccc   |
    |  6 | 666666    |
    |  7 | hehehehe  |
    |  8 | sssssss   |
    |  9 | bbbbbbbbb |
    | 11 | nnnnnn    |
    | 12 | lkjhgfd   |
    | 13 | hhhh      |
    | 15 | pppppp    |
    +—-+———–+
    13 rows in set (0.00 sec)
    mysql> update t3 set name = “cvbnm” where id = 6;

    如上,当我们在事务2中执行update就阻塞了,然后我们看看:

    如上,Id 为 31 是我的第三个session用来查看的 ,Id 为 32 是session 1的连接,Id 为 33 是 session 2 的连接,我们用上面的SQL试一下:

    是不是一目了然呢 ?

    对于死锁来说,在MySQL中可以把死锁信息打印到错误日志里,开启如下变量即可。

    然后死锁发生时错误日志中会出现如下信息,以及对应的SQL语句和死锁信息和事务信息。:

    另外,我们也可以根据全局的status变量来监控和衡量锁的相关情况:

    • Innodb_row_lock_current_waits:当前锁等待的数量
    • Innodb_row_lock_time:自系统启动到现在,锁定的总时间,单位:毫秒 ms
    • Innodb_row_lock_time_avg:平均锁定的时间,单位:毫秒 ms
    • Innodb_row_lock_time_max:最大锁定时间,单位:毫秒 ms
    • Innodb_row_lock_waits:自系统启动到现在,锁等待次数,即锁定的总次数。

     

     

  • MySQL数据库锁(二)

    背景

    在上文中,我们讲述了MyISAM存储引擎的表级别锁。主要是一些基本概念和简单应用,可能比较浅显,甚至有些错漏,后续的实践中再持续改进吧。 今天主要是研究一下InnoDB存储引擎,这个是我们日常运维中涉及和使用最多的,也是最重要的。 InnoDB引擎的锁机制概念非常庞杂,牵扯到很多的知识点,想要彻底搞明白绝非易事。当然业内不乏一些对MySQL源码深入了解的大牛深入剖析,但本人目前明显还不具备这个实力,此博客本着研究学习的态度,去总结研究InnoDB锁相关知识。

    内容

    当我们打开MySQL的官方文档,搜索lock时,会看到MySQL有如下几种锁:

    %e5%b1%8f%e5%b9%95%e5%bf%ab%e7%85%a7-2017-04-07-%e4%b8%8b%e5%8d%882-28-04

    额,感觉作为一个存储引擎好累啊,每天要处理和管理那么多锁。 呵呵,幸好他不是人,但由此也可以想到编写数据库的程序猿们还是很厉害的。那么这么多锁,我们该要如何下手呢 ? 先从最熟悉的,也就是我们经常念叨在嘴边的InnoDB的行锁下手:

    共享锁与排它锁(Shared and Exclusive Locks)

    InnoDB实现了两种标准的行级锁

    共享锁(S Lock),允许事务读一行数据。                     用法:  SELECT … LOCK IN SHARE MODE ;

    排他锁(X Lock),允许事务删除或更新一条数据。       用法:  SELECT … FOR UPDATE ;

    注:持锁的粒度是事务,即当一个事务A获得某行的共享锁后,另一个事务B也可以获取此行的共享锁。

    InnoDB行级锁遵循普通的共享、排他规则。 举个最简单的例子:

    yy

    意向锁(Intention Locks)

    InnoDB存储引擎支持多粒度的锁定,意向锁是InnoDB引擎支持的表级锁。

    意向共享锁(IS Lock): 事务想要获得一张表中某几行的共享锁 , 顾名思义, 事务有意向去给一张表中的几行加S锁。当事务想要获得一张表的某几行记录的S锁时,必须先要获得此表的IS锁

    意向排他锁(IX Lock): 事务想要获得一张表中某几行的排他锁, 顾名思义, 事务有意向去给一张表中的几行加X锁。当事务想要获得一张表的某几行记录的X锁时,必须先要获得此表的IX锁

    他们的兼容性如下:

    %e5%b1%8f%e5%b9%95%e5%bf%ab%e7%85%a7-2017-04-07-%e4%b8%8b%e5%8d%883-05-26

    到这里其实我心里有了疑惑:

    1. 意向锁的存在意义是什么 ? 如果说加意向锁的目的是为了表明某个事务正在锁定一行或者将要锁定一行。那么,意向锁的作用就是“表明”加锁的意图,可是为什么要表明这个 意图呢?如果仅仅锁定一行仅仅需要加一个锁,那么就直接加锁就好了呗。
    2. 从上面的兼容性矩阵我们发现IX 与 X锁不兼容,那这岂不是反而降低了InnoDB事务的并发性? 比如:
    • 事务A获取test表的IX表级锁成功
    • 事务B也获取test表的IX表级锁成功(因为IX与IX兼容)
    • 事务A想要给test某行加X锁时,阻塞了 ? (因为X锁与IX锁不兼容)

    InnoDB肯定不会这么挫吧? 肯定是我的想法与实际有偏差,经查阅资料后原来是我自己的理解有误。

    我们先来说说意向锁的存在意义,假设有如下场景:

    —— 事务A锁住了表中的一行,让这一行只能读,不能写。

    —— 之后,事务B申请整个表的写锁。

    如果事务B申请成功,那么理论上它就能修改表中的任意一行,这与A持有的行锁是冲突的。数据库需要避免这种冲突,就是说要让B的申请被阻塞,直到A释放了行锁。

    那么数据库要怎么判断才能让B事务避免这个冲突呢 ?

    step1:判断表是否已被其他事务用表锁锁表,如果是则调到step2,否则直接加锁成功。
    step2:判断表中的每一行是否已被行锁锁住。

    注意step2,这样的判断方法效率实在不高,因为需要遍历整个表。于是就有了意向锁。在意向锁存在的情况下,事务A必须先申请表的意向共享锁,成功后再申请一行的行锁。

    那么在意向锁存在的情况下,上面的判断就变成了:

    step1:不变
    step2:发现表上有其他事务的意向共享锁,事务B申请表的写锁直接被阻塞。不必再判断每一行了

    注:以上解释来自知乎问答(https://www.zhihu.com/question/51513268)

    也就是说,意向锁的存在意义是避免事务需要获取表级别的锁时,总是遍历表的所有行以判断是否被加了行锁。举个实际的例子,比如当我执行(ALTER TABLE, DROP TABLE, LOCK TABLES)等操作时,如果没有意向锁,我需要遍历表的所有行才能知道是否被加了行锁,如果被加了,那么这个操作肯定要先被阻塞。 但如果我有意向锁,我只需要查看这个表是否有意向锁即可,不必再遍历表了。

    其实到这里,我们的第二个问题的答案也呼之欲出了,上面的兼容矩阵里面描述的与IX意向锁不兼容的X锁指的是由类似(ALTER TABLE, DROP TABLE, LOCK TABLES)操作触发的表级X锁,而不是我之前理解的行级X锁。 也就是说:

    IX,IS是表级锁,不会和行级的X,S锁发生冲突。只会和表级的X,S发生冲突。

    InnoDB锁的算法

    恩?  怎么跳到锁的算法了? 讲完InnoDB的行锁和意向锁后不是应该挨着顺序讲那个什么Record Locks 和 Gaps Locks等等吗? 其实,这些本身就是行锁的一种算法,不是一种独立的锁。

    InnoDB存储引擎有3种行锁的算法:

    Record Locks: 单个索引行记录上的锁

    Gap Locks:间隙锁,锁定一个范围,但不包含记录本身

    Next-Key Lock: Record Locks + Gap Locks ,以上两种锁的结合,锁定一个范围,并且锁定记录本身。

    那么问题来了,为什么要有三种行锁的算法呢? 他们各有什么特性,孰优孰劣 ?

    其实,他们各有千秋,适用于不同的场景,我们从最基础的Record Lock说起,他是最基本的行锁,必须建立在索引行之上,我们都可以理解,无非就是用于给表中的某一索引行记录加锁。那么有它就够了啊,要间隙锁干毛啊 ? 还是有用的,我们来看如下这个场景:

    %e5%b1%8f%e5%b9%95%e5%bf%ab%e7%85%a7-2017-04-07-%e4%b8%8b%e5%8d%886-13-09

    注:之所以将session1的隔离级别改成RC,是因为RC隔离级别默认没有Gap Lock。 

    如上,session1在一个事务内连续的两次同样的读,得到的结果竟不一样,这不就是传说中的幻读吗 ? 这不就是传说的RC隔离级别无法避免幻读吗 ?   同样的,我改成RR隔离级别试了一下发现它不存在这个现象了,而RR隔离级别之所以这么屌就是因为它采用了Nect-Key Lock(兼容了Gap Lock的Record Lock)。

    那么间隙锁长啥样呢? X锁又长啥样呢? 什么情况下会用到什么样的锁呢 ?我们以实际的例子来看一下,如果我有这样一张表:

    那么当我给一张表执行如下操作的时候,他都会加什么锁呢?

    SQL1: select * from hello where id = 23;                                        # 主键索引

    SQL2:    select * from hello where grade = 555;                               # 唯一索引

    SQL3:    update hello set name = “james” where age  = 98;            # 普通索引

    SQL4:    delete from hello where extr = “for”;                                   # 无索引

    注:MySQL的默认隔离界级别是RR隔离级别的。  

     

     

    SQL1主键索引为检索条件时,对应的加锁如下:

    %e5%b1%8f%e5%b9%95%e5%bf%ab%e7%85%a7-2017-04-08-%e4%b8%8b%e5%8d%884-07-59

    注:当我们的索引条件对应的值是主键时,只会在对应的那一行上加上R读锁或者X写锁。 我这里暂时没有考虑意向锁。

    SQL2唯一索引为检索条件时,对应的加锁应该如下:

    %e5%b1%8f%e5%b9%95%e5%bf%ab%e7%85%a7-2017-04-08-%e4%b8%8b%e5%8d%884-21-50

    注:InnoDB是基于主键的聚簇索引,当我们以唯一索引为主键时,他会先通过唯一索引定位到主键索引并加锁。

    SQL3普通索引为检索条件时,对应的加锁应该如下:

    %e5%b1%8f%e5%b9%95%e5%bf%ab%e7%85%a7-2017-04-08-%e4%b8%8b%e5%8d%884-56-20

    注:终于见到传说中的间隙锁啦,哈哈,这下很容易想明白间隙锁的作用啦,假设如果没有最左上角那个间隙锁,那么另外一个session想要执行如下SQL时,就可以直接执行成功了:

    insert into hello (id, age , name) values (10, 98, “kvbnxs”);  

    如果我本session在insert之前执行where age = 98 的查询和insert之后执行where age = 98 的查询得到的结果可能就不一样了!就产生了我们说的幻读。 

    SQL4检索条件为无索引时,加锁条件应该如下:

    %e5%b1%8f%e5%b9%95%e5%bf%ab%e7%85%a7-2017-04-08-%e4%b8%8b%e5%8d%884-53-29

    注:当检索列无索引时,MySQL无可避免的要进行全表扫描,因为它并不知道你的目的行在那里,所以他只能先将所有行都锁住,同时他为了避免幻读,会加上间隙锁。如果我们将隔离级别换做RC或者设置参数

    innodb_locks_unsafe_for_binlog = 1

    那么就没有间隙锁了。

    Next-Key Locks 说白了就是两种锁的结合,RR 隔离级别采用这种加锁方式避免了幻读,这个就不再这里做详细介绍了。

    Insert Intention Locks

    插入意向锁是间隙所的一种,顾名思义,就是在插入数据之前获取一个有插入意向的间隙锁,它的存在意义其实我也没太搞明白,觉得Next-Key Locks就可以搞定了,后续有机会深入再叙述吧,现在没有头绪,没法写这个。

    AUTO-INC Locks

    自增锁是一种表锁,应用于锁上定义了 auto_increment 时,并发插入操作能够以竞态方式保证插入的行是按定义自增的。可以由 innodb_autoinc_lock_mode  参数控制。

     

    今天先讲到这里,关于InnoDB的锁还有很多内容,比如:锁的阻塞、死锁、锁的查看与分析等,后续章节我们再继续。

     

    参考:

    MySQL 加锁处理分析

     

  • InnoDB 二次写 与 自适应哈希索引

    背景

    之前提到MyISAM引擎与InnoDB的区别中,有说起插入缓存、二次写和自适应哈希索引三大特性是InnoDB特有的,也是强于MyISAM的三大靓点。所以既插入缓存后,也简单的阐述一下二次写和自适应哈希索引。本章节的阐述框架还是主要来源于——《InnoDB存储引擎》一书。

    二次写

    如果说Insert Buffer带给InnoDB存储引擎的是性能上的提升,那么二次写带给InnoDB存储引擎的是数据页的可靠性。

    当数据库正在从内存向磁盘写一个数据页时,数据库宕机,从而导致这个页只写了部分数据,这就是部分写失效,它会导致数据丢失。这时是无法通过重做日志恢复的,因为重做日志记录的是对某个完整页的物理修改,如果这个页本身已经损坏,重做日志也无能为力。也就是说,这个页如果发生部分写失效的情况,那么除非先把这个页修好,然后再用重做日志才行。所以,二次写就应用而生了。

    顾名思义,二次写,就是将即将要刷新到磁盘的脏页,分两次。分别写入到共享表空间(ibdata)和实际的存储引擎物理文件(.idb)中,它的思想最终是一种备份思想,也就是一种镜像。如下图(摘自《InnoDB存储引擎》)

    yy

    两次写需要额外添加两个部分:
    • 内存中的两次写缓冲(doublewrite buffer),大小为2MB
    • 磁盘上共享表空间中连续的128页,大小也为2MB
    其原理是这样的:
    • 当刷新缓冲池脏页时,并不直接写到数据文件中,而是先拷贝至内存中的两次写缓冲区。
    • 接着从两次写缓冲区分两次写入磁盘共享表空间中,每次写入1MB。
    • 待第2步完成后,再将两次写缓冲区写入数据文件。
    这样就可以解决上文提到的部分写失效的问题,因为在磁盘共享表空间中已有数据页副本拷贝,如果数据库在页写入数据文件的过程中宕机,在实例恢复时,可以从共享表空间中找到该页副本,将其拷贝覆盖原有的数据页,再应用重做日志即可。
    但是这里可能存在疑问:
    • 1. 如果是在上述第二步就发生宕机导致部分写失效怎么办?
    其实对于这个问题,其实是不用担心的,因为如果两次写有问题,则本身数据页面就没有做写操作,此时系统挂了,发生错误的是两次写页面,而数据页面在挂之前都是在buffer里面,文件中还是当前事务操作前的值,它自己没有变,还是一致状态,所以两次写页面压根就不会被使用到。
    • 2. 两次写相当于中间多写了一遍,感觉对性能的影响应该还是挺大的,这个该怎么权衡呢?

    表面看上去,它是每一个页面都写了2遍,则会非常影响性能,但实际上,由于将所写的页面都先缓存到内存中,到达128个页即1M之后才真正写入,那么对于磁盘而言,连续写与分散写(每个页面自己写)的性能相差很大的,而两次写正是将一个区数量的页面组合起来形成2个连续的空间写入到两次写空间中,有效的利用这了这特点,所以性能是不会相差1倍的。实际上经过测试,可能两次写使得性能降低了10%。(本人未亲自测试,此观点摘自网上

    可以通过参数skip_innodb_doublewrite禁用两次写功能,默认是开启的,建议开启该功能。

    自适应哈希索引

    我们知道,在一般情况下InnoDB的数据都是走的B+树索引方式。一般情况下B+树查找次数取决于B+树的高度,在生产环境中,B+树的高度一般为3-4层,故需要3-4次查询。相对来说,哈希的效率就会高很多,它是可以直接命中的,时间复杂度仅为O(1)。

    InnoDB存储引擎会监控对表上各索引页的查询。如果观察到建立哈希索引可以带来速度提升,则建立自适应哈希索引(AHI)。AHI是通过缓冲池的B+树页构造而来,因此建立的速度很快,而且不需要对整张表构建哈希索引。 AHI有一个要求,即对这个页的连续访问模式必须是一样的。例如,对于(a,b)这对联合索引,可以有如下访问模式:

    • where a = ???
    • where a = ??? and b = ???

    访问模式一样指的是查询的条件一样,若交替进行上述两种查询,则InnoDB不会建立AHI。此外建立AHI还有如下要求:

    • 以该模式访问了100次
    • 页通过该模式访问了N次,其中N = 页中记录数的十六分之一

    根据InnoDB存储引擎官方文档的显示,启用AHI后,读取和写入速度可以提高2倍,辅助索引的链接操作性能可以提升5倍。毫无疑问,AHI是很好的优化模式,而且是数据库自己优化的,并不需要DBA人为干涉。

    我们可以通过 SHOW ENGINE INNODB STATUS 查看当前AHI的情况。

    哈希索引也是有限制的:比如它只能用来查找等值的查找情况,而不能用来直接进行范围查找。

    参考:
    http://www.cnblogs.com/bamboos/p/3553703.html
    《MySQL技术内幕——InnoDB存储引擎》
  • InnoDB —— 插入缓存

    背景

    1. 什么是插入缓存,原理是什么?
    2.  它存在的意义是什么? 有什么好处?
    3. 怎样利用与查看它?

    内容

    插入缓存的原理和意义

    插入缓存是InnoDB独有的优化模式。 我们都知道,InnoDB是基于主键的聚簇索引。通常应用程序中行纪录的插入顺序是按照主键递增的顺序进行插入的。因此,插入聚集索引一般是顺序的,不需要磁盘的随机读写。如下,假如我有这样一个表:

    那么当我不断插入时,若对id列插入null值,则由于其具有auto_increment的属性,其值会自动增长。同时页中的行记录按照id的值进行存放。在一般情况下,不需要随机读取另一个页中的记录。因此,对于这类情况下的插入操作,速度是非常快的。(uuid除外)

    yy

    当我insert  into  test (count) values (12),(23),(56),(89) …… ,左边的那页会随着主键顺序递增,它不会去考虑其他的页。 假如当我要插入100的时候,刚好左边那一页满了。那么很自然的就插入到下一页了,还是不需要去查询我应该插入在哪里

    我靠,那岂不是不需要插入缓存了,我这样多高效! ! !

    但通常情况下,我们的表结构还存在其他的非聚集的辅助索引的啊。那么假如我们的表结构是这样的呢? 我给count也加了一个索引:

    alter  table test  add  index  count (count);     这时候我们的表结构就变成了这样:

    在这样的情况下产生了一个非聚簇索引且不是唯一索引,在插入的时候,数据页的存放还是按照主键id进行顺序存放的。 但是!!  对于非聚簇索引的叶子节点的插入不再是顺序的了,这是就需要离散的访问非聚簇索引页,由于随机读取的存在而导致了插入操作的性能下降。   不理解?   看图(图片摘自高性能MySQL):

    yy

     

    上图中左侧为InnoDB的主键索引,以及二级索引的结构。右边是MyISAM的索引结构。我们把注意力放在左下角,你会发现。 InnoDB非聚簇索引的叶子节点的值不再是行,而是指向了主键索引

    当我再次重新向test插入插入时:   insert   into  test  (count) values (55) ;

    这时,数据页的存放还是按照主键id进行顺序存放的,但是count的页节点就不是了。 count为55的值应该放在B+树的哪里,它得有一个判断的过程。 它不像主键id一样,可以直接放在末尾而不用寻找。   那么问题来了,假如每插入一次就得进行一次判断与结构的调整,那么成本是很高的。

    所以, 插入缓存就诞生了,这就是它的存在意义:

    对于非聚簇索引的插入或更新操作,不是每一次直接插入到索引页中,而是先判断插入的非聚簇索引页是否在缓冲池中,若在,则直接插入; 若不在,则先放入到一个Insert  Buffer对象中,好似欺骗。数据库这个非聚簇索引的已经插入到叶子节点中,而实际没有,只是存放在了另一个位置。然后再以一定的频率和情况进行insert Buffer和辅助索引叶子节点的merge操作,这样就可以把多个插入操作合并到一个操作中,大大提高对非聚簇索引的插入性能。

    然而Insert Buffer的使用需要同时满足以下两个条件:

    • 索引是辅助索引 (非主键索引)
    • 索引不是唯一索引

    为什么必须不能是唯一索引呢?  因为插入的时候,数据库并不去查找索引页来判断插入的记录的唯一性。 如果去查找肯定又会有离散的读取的情况发生,岂不是得不偿失,那Insert Buffer也失去了意义。

    InnoDB 插入缓存的查看与调整

    我们已经粗略知道了 Insert  Buffer的基本概念,那么该如何去查看它呢?

    yy

    如上图,有一部分标题为 插入缓存与自适应哈希索引的:

    size 代表已经合并记录页的数量;

    free list len代表了用于change buffer的空闲队列的长度;

    seg size代表了当前change Buffer的大小为 4745 * 16K = 74MB 。

    merges : change buffer 一共合并了多少次 。

    merged operations – insert :  插入记录被merge的次数

    merged operations  – delete mark : 删除操作被merge的次数

    merged options -delete  : 更新操作被merge了多少次

    上面的这些信息我们都可以在infoemation_schema. innodb_metrics 里面查到:

    yy

    我们也可以从information_schema.innodb_buffer_page里面得到各种buffer page的信息,当然也包括change buffer.  例如,我们想得到change  buffer page占所有buffer page的百分比:

    yy

    细心的reader会发现,我们上面不再是insert buffer,而是change  buffer了,这是因为InnoDB后来觉得这种优化方式不错,所以给所有的DML操作(INSERT , DELETE, UPDATE) 都做了缓冲,他们分别是 Insert Buffer ,  Delete Buffer , Purge Buffer .

    目前,change buffer存在一个问题,在写密集的情况下,插入缓存会占用过多的缓冲池内存(innodb_buffer_pool), 默认是可以占到 1/2 的缓冲池大小。 我们可以通过下面这个参数来控制change buffer最大使用的内存占比:

    yy

    默认值是25,代表最多使用25%的innodb_buffer_pool。

    到了这里,又产生了几个疑问:

    1. Insert  Buffer在内存中它是怎么存在的,存在在哪里呢?

    2. 它在什么时候会merge进入物理内存呢?

    其实上述的问题涉及到InnoDB的insert buffer的实现原理。最好可以从数据库源码入手,就可以透彻的进行了解了。我也试着去入手这一部分的源码,但发现无从下手。代码能力还是有待提高,所以暂且搁置,以后继续补充。  这里我翻阅了一些书籍得到了一些解百纳概念:

    Insert Buffer的数据结构式一颗B+树。 这颗B+树负责对所有的辅助索引进行插入缓存。 而这颗B+树存放在共享表空间中,也就是我们经常看到的ibdata1.

    而Merge  Insert Buffer主要由于下面几种情况:

    •  辅助索引对应的物理页要被读取到缓冲池时:  假如我要查询该页的内容了,而你还没有实际插入物理也那肯定不行啊。
    •  Insert Buffer Bitmap 追踪到辅助索引页的可用空间不大时。
    •  Master Thread 它每隔10秒会进行一次Merge Insert Buffer,不同之处在于每次Merge操作的页的数量不同。

    本文的阐述框架来自于 ——    《MySQL技术内幕—— Innodb存储引擎》

    上面只是简单综述了 Insert Buffer的基本概念,至于它是怎么实现的。 还有待进一步深究, 未完待续 。。。。。。

  • InnoDB存储引擎文件

    InnoDB表空间文件

    那个名为ibdata1的文件就是啦,我们可以通过innodb_data_file_path对其进行设置。

    yy

    设置此参数后,所有基于InnoDB的表的数据等都会放在该共享表空间中。 若设置了innodb_file_per_table, 则启用独立表空间。 独立表空间的命名为 .ibd 。 需要注意的是,这些单独的表文件内只是存放了表数据、索引、以及插入缓存BITMAP等信息,其余的还是存放在默认的ibdata1中。

    InnoDB 重做日志文件:

    在默认情况下,InnoDB存储引擎的数据目录下会有两个文件名为 ib_logfile0和ib_logfile1的文件。

    每个InnoDB存储引擎至少有1个重做日志文件组,每个组至少有两个重做日志文件,如默认的ib_datafile0, ib_datafile1。 为了得到更高的可用性,用户可以通过innodb_mirrored_log_groups参数设置多个组。

    参数innodb_log_file_size指定了每个重做日志文件的大小。

    yy

    重做日志文件的设置要合理,若设置的过大,可能会导致恢复时花费很长的时间; 设置的过小,又可能导致一个事务的日志需要多次切换重做日志文件

    那么我们已经有了binlog日志文件了,为什么还需要它呢?他俩的区别是什么呢?

    1. 记录对象不同: binlog记录的是所有和MySQL相关的事务日志,包括各种引擎。而重做日志文件则只记录InnoDB的事务日志。

    2. 记录内容不同: binlog记录的是一次事务的具体操作内容,即该事务的逻辑日志。而重做日志文件记录的是关于每个页的更新的物理情况。

    3. 写入时间不同: binlog仅在事务提交前进行提交,即只写磁盘一次,不论这个事务多大。 而在事务进行的过程中,却不断有重做日志被记录。

    对于binlog来说,所有未提交的二进制日志会被记录到一个缓存中去,直到事务提交才会刷新到二进制事务文件中,而该缓冲的大小为binlog_cache_size决定。此参数是基于会话级别的, 因此不能设置的过大。 但是如果当一个事务的记录大于设定的binlog_cache的时候,MySQL会把缓存中的日志写入一个临时的磁盘文件中。binlog_cache_disk_use表示从临时文件写入二进制日志的次数。

    其实,在默认情况下,binlog并不是每次写的时候都会同步刷新到磁盘。由参数sync_binlog控制:

    yy

    sync_binlog 的默认值是0,像操作系统刷其他文件的机制一样,MySQL不会同步到磁盘中去而是依赖操作系统来刷新binary log。

    当sync_binlog =N (N>0) ,MySQL 在每写 N次 二进制日志binary log时,会使用fdatasync()函数将它的写二进制日志binary log同步到磁盘中去。

    在这里大家可能会被缓存和内存和磁盘的刷新搞昏头,简单解释一下:首先,我们抛开mysql不说, linux本身对于各式文件的写入和更新都是有一定的缓存的。 这样可以减少和磁盘的交互从而提高整个系统的效率。 举个最简单的例子, 当我们执行free -m命令的时候就可以看到cache ,buffer 两个参数其实就是标识linux系统本身的缓存大小的:

    yy

    如上图, 对于mysql来说, 所有未提交的事务日志首先写入binlog_cache,而这个binlog_cache是在内存中的。  然后,当事务提交时,mysql将binlog_cache的内容刷新到我们经常说的binlog文件中。 这个binlog文件按理来说应该在磁盘上,但是linux系统本身做了缓存。 他是将binlog_cache的更改先放在内核的缓冲区中,然后再根据一定的规则刷新到磁盘。 而sync_binlog控制的就是那个fsync的频率。

    而对于重做日志来说:写入重做日志文件的操作也不是直接写,而是先写到redo log buffer当中,然后按照一定条件顺序写入日志文件。innodb_flush_log_at_trx_commit控制,表示在commit时,处理重做日志的方式:

    yy

    0 表示当事务提交时,log buffer 会 每秒写入到日志文件并刷写(flush)到磁盘。但每次事务提交不会有任何影响,也就是 log buffer 的刷写操作和事务提交操作没有关系。在这种情况下,MySQL性能最好,但如果 mysqld 进程崩溃,通常会导致最后 1s 的日志丢失。

    1 表示每次事务提交时,log buffer 会被写入到日志文件并刷写到磁盘。这也是默认值。这是最安全的配置,但由于每次事务都需要进行磁盘I/O,所以也最慢。

    2 表示每次事务提交会写入日志文件,但并不会立即刷写到磁盘,日志文件会每秒刷写一次到磁盘。这时如果 mysqld 进程崩溃,由于日志已经写入到系统缓存,所以并不会丢失数据;在操作系统崩溃的情况下,通常会导致最后 1s 的日志丢失。

     

    其实这两个参数也就是我们通常在MySQL优化中说起的双1设置。