数据库基础知识（3）

当前读与快照读

  1. 当前读：即加锁读，读取记录的最新版本，会加锁保证其他并发事务不能修改当前记录，直至获取了锁的事务释放锁；

    使用当前读的操作主要包括：显式加锁的读操作与插入/更新/删除等写操作，如下所示：

    1
    2
    3
    4
    5
    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 SQL被发给MySQL后，MySQL Server会根据where条件，读取第一条满足条件的记录，然后InnoDB引擎会将第一条记录返回，并加锁，待MySQL Server收到这条加锁的记录之后，会再发起一个Update请求，更新这条记录。一条记录操作完成，再读取下一条记录，直至没有满足条件的记录为止。因此，Update操作内部，就包含了当前读。同理，Delete操作也一样。Insert操作会稍微有些不同，简单来说，就是Insert操作可能会触发Unique Key的冲突检查，也会进行一个当前读。

  2. 快照读：即不加锁读，读取记录的快照版本而非最新版本，通过MVCC实现；

    InnoDB默认的RR事务隔离级别下，不显式加『lock in share mode』与『for update』的『select』操作都属于快照读，保证事务执行过程中只有第一次读之前提交的修改和自己的修改可见，其他的均不可见；

MVCC 原理

MVCC（多版本并发控制）的最大好处：读不加任何锁，读写不冲突，对于读操作多于写操作的应用，极大的增加了系统的并发性能；

MVCC是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存行的过期时间（或删除时间）。当然存储的并不是实际的时间值，而是系统版本号（system version number)。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。

在REPEATABLE READ隔离级别下，MVCC的操作方式如下：

  • SELECT
    InnoDB会根据以下两个条件检查每行记录：
    1. InnoDB只查找版本早于当前事务版本的数据行（也就是，行的系统版本号小于或等于事务的系统版本号），这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的。
    2. 行的删除版本要么未定义，要么大于当前事务版本号。这可以确保事务读取到的行，在事务开始之前未被删除。
      只有符合上述两个条件的记录，才能返回作为查询结果
  • INSERT
    InnoDB为新插入的每一行保存当前系统版本号作为行版本号。
  • DELETE
    InnoDB为删除的每一行保存当前系统版本号作为行删除标识。
  • UPDATE
    InnoDB插入一行新记录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识。即UPDATE = INSERT新行 + DELETE旧行。

保存这两个额外系统版本号，使大多数读操作都可以不用加锁。这样设计使得读数据操作很简单，性能很好，并且也能保证只会读取到符合标准的行，不足之处是每行记录都需要额外的存储空间，需要做更多的行检查工作，以及一些额外的维护工作。

MySQL存储引擎

1
2
3
4
5
6
show engines;                                            -- 查看存储引擎
show variables like 'storage_engine';                    -- 查看数据库默认使用的引擎
create table table_name()engine=engine_name;             -- 建表时设置引擎

show status like '%table_lock%'                          -- 查看表级锁争用情况
show status like 'innodb_row_lock'                       -- 查看InnoDB行级锁争用情况

Mysql有很多种引擎，这里主要记录一下 InnoDB 和 MyISAM 两种。

InnoDB存储引擎

InnoDB是事务型数据库的首选引擎，也是默认的MySQL引擎，支持事务安全表（ACID），支持外键，支持表锁和行锁，不过行锁是在命中索引的情况下才会起作用

InnoDB主要特性

  • 为MySQL提供了具有提交、回滚和崩溃恢复能力的事物安全（ACID兼容）存储引擎。InnoDB锁定在行级并且也在SELECT语句中提供一个类似Oracle的非锁定读。这些功能增加了多用户部署和性能。在SQL查询中，可以自由地将InnoDB类型的表和其他MySQL的表类型混合起来，甚至在同一个查询中也可以混合
  • 为在主内存中缓存数据和索引，InnoDB存储引擎有维持它自己的缓冲池。InnoDB将它的表和索引在一个逻辑表空间中，表空间可以包含数个文件（或原始磁盘文件）。这与MyISAM表不同，比如在MyISAM表中每个表被存放在分离的文件中。InnoDB表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上
  • InnoDB支持外键完整性约束，存储表中的数据时，每张表的存储都按主键顺序存放，如果没有显示在表定义时指定主键，InnoDB会为每一行生成一个6字节的ROWID，并以此作为主键
  • InnoDB支持多粒度锁，默认使用行锁，锁粒度最小，锁冲突发生的概率也最低，支持的并发度也最高，但系统消耗成本也相对最高
  • 共享锁和排他锁是InnoDB实现的两种标准的行锁
  • InnoDB有三种锁算法——记录锁(Record Lock)、间隙锁(Gap Lock)、next-key锁(Next-key Lock)
InnoDB 锁算法

InnoDB 的三种锁算法：

行锁算法 锁定内容
Record Lock 记录锁，锁定一个行记录
Gap Lock 间隙锁，锁定一个区间
Next-key Lock 记录锁+间隙锁，锁定行记录+区间

InnoDB所有的行锁算法都是基于索引实现的，锁定的也都是索引或索引区间；

不同的事务隔离级别、不同的索引类型、是否为等值查询，使用的行锁算法也会有所不同；下面仅以InnoDB默认的RR隔离级别、等值查询为例，介绍几种行锁算法：

等值查询使用的索引类型 锁定内容
主键(聚簇索引) 对聚簇索引记录 + 记录锁
唯一索引 对辅助索引记录 + 记录锁；对聚簇索引记录 + 记录锁
普通索引 对相关辅助索引 + next-key锁；对聚簇索引记录 + 记录锁
不使用索引 对聚簇索引全表 + next-key锁

行级锁也分为共享锁和排他锁，具体的兼容情况详见参考链接描述。

MyISAM存储引擎

MyISAM 基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM 拥有较高的插入、查询速度，但不支持事务。

MyISAM主要特性：

  • 被大文件系统和操作系统支持
  • 当把删除和更新及插入操作混合使用的时候，动态尺寸的行产生更少碎片。这要通过合并相邻被删除的块，若下一个块被删除，就扩展到下一块自动完成
  • 每个MyISAM表最大索引数是64，这可以通过重新编译来改变。每个索引最大的列数是16
  • 最大的键长度是1000字节，这也可以通过编译来改变，对于键长度超过250字节的情况，一个超过1024字节的键将被用上
  • BLOB和TEXT列可以被索引
  • NULL被允许在索引的列中，这个值占每个键的0~1个字节
  • 所有数字键值以高字节优先被存储以允许一个更高的索引压缩
  • 每个MyISAM类型的表都有一个AUTO_INCREMENT的内部列，当INSERT和UPDATE操作的时候该列被更新，同时AUTO_INCREMENT列将被刷新。所以说，MyISAM类型表的AUTO_INCREMENT列更新比InnoDB类型的AUTO_INCREMENT更快
  • 可以把数据文件和索引文件放在不同目录
  • 每个字符列可以有不同的字符集
  • 有VARCHAR的表可以固定或动态记录长度
  • VARCHAR和CHAR列可以多达64KB

与默认支持行级锁的InnoDB不同，MyISAM使用表级锁，表级锁也可细分为共享锁和排他锁。

MyISAM的读操作是串行的，读操作和写操作是互斥的，并且MyISAM中写操作的优先级更高。例如，一个进程请求某个MyISAM表的读锁，同时另一个进程也请求同一表的写锁，MySQL如何处理呢？答案是写进程先获得锁。不仅如此，即使读进程先请求先到锁等待队列，写请求后到，写锁也会插到读请求之前！这是因为MySQL认为写请求一般比读请求重要。这也正是MyISAM表不太适合有大量更新操作和查询操作应用的原因，因为大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。

  • 调节MyISAM调度方式的一些方法
    • 通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。
    • 通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低。
    • 通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。

MySQL也提供了一种折中的办法来调节读写冲突，即给系统参数max_write_lock_count设置一个合适的值，当一个表的读锁达到这个值后，MySQL变暂时将写请求的优先级降低，给读进程一定获得锁的机会。

存储引擎的比较与选择

在实际工作中，选择一个合适的存储引擎是一个比较复杂的问题。每种存储引擎都有自己的优缺点，不能笼统地说谁比谁好。但建议选择使用InnoDB。

特性 InnoDB MyISAM
事务安全 支持 无
存储限制 64TB 有
空间使用 高 低
内存使用 高 低
插入数据的速度 低 高
对外键的支持 支持 无

InnoDB： 支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高（比如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势。如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交（commit）和回滚（rollback）。

MyISAM： 插入数据快，空间和内存使用比较低。如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率。如果应用的完整性、并发性要求比 较低，也可以使用。

参考链接

MVCC实现原理
MyISAM表锁
InnoDB行锁