0%

Mysql数据库考点复习

整体面貌

数据库的存储引擎,有如下两种,目前主要使用的是InnoDB引擎,之后的内容默认都将是InnoDB数据库的知识

InnoDB MyISAM
底层实现 B+树
锁粒度 表锁、行锁 表锁
事务 支持事务的四种原则,四种隔离级别,默认RR 不支持

Mysql数据库的主要组成部分如下图:

整体架构如下

整体缓存的设计和操作系统的缓存设计十分相似,缓存的内容有如下几部分。感觉Mysql数据库的缓存设计方案和操作系统的缓存设计比较相似,这里就不深入探索了。

文件

日志文件

  • 错误日志

    记录MySQL运行错误、警告信息,可以通过这个来优化数据库参数配置。

  • 慢查询日志

    记录运行时间超过某个阈值的所有SQL语句,通过这个来做SQL层面的优化。

  • 二进制日志

    记录对MySQL数据库执行更改的所有操作,但是不包括查询语句。用于数据库的恢复、复制和审计(判断是否有SQL注入攻击)。

InnoDB文件

  • 表空间文件

    管理InnoDB存储引擎的存储,包括索引页和数据页等。

  • 重做日志文件

    当实例或介质失败时,如数据库由于所在主机掉电导致实例失败,InnoDB会使用重做日志(redo log)恢复到掉电前的时刻,以此来保证数据完整性。

    与二进制日志的区别

    二进制日志(binlog) 重做日志文件(redo log file)
    实现方式 MySQL数据库的Server层实现,记录所有与数据库有关的各种引擎的日志记录 InnoDB存储引擎层实现,仅记录InnoDB有关的事务日志
    内容形式 逻辑日志,循环写入,关于事务的具体操作,是一条条SQL语句,非幂等 物理格式日志,追加写入,记录每个页的更改的物理情况,是幂等的
    写入时间 仅在事务提交前进行提交,只写盘一次,不论这时该事务有多大 事务进行的过程中,不断有重做日志条目写入
    适用场景 主从复制和数据恢复 崩溃恢复

    幂等的意思是\(f(f(x)) = f(x)\),即一个操作一次执行与多次执行的效果一致。

索引

索引是存储引擎用于快速查找记录的一种数据结构,要额外占用内存,在加快检索数据的同时,插入、删除、修改数据都需要DBMS动态更新索引结构,占用一定的系统开销。

在InnoDB中的表是索引组织表,即每张表都有主键,都是根据主键顺序组织存放的。主键的生成规则如下:

  1. 首先判断表中是否有非空的唯一索引(Unique NOT NULL),如果有多个,将选择建表时第一个定义的非空唯一索引作为主键;
  2. 否则InnoDB自动创建一个6字节大小的指针作为主键。

B+树索引

B+树数据结构

非叶子节点存放的是索引数据,叶子节点存放的是行数据,并且是顺序存放的(叶节点间是双向链表结构)。

  • 插入节点

    • 叶子节点未满,直属索引节点未满。直接插入叶子
    • 叶子节点满,直属索引节点未满。插入叶子,更新直属索引节点
    • 叶子节点满,直属索引节点满。先动叶子,更新直属索引节点,再更新上一层的索引节点
  • 删除节点

    定义填充因子,0.5是最小值,下面的满指的是到达填充因子。

    • 叶子节点未满,直属索引节点未满。直接删除叶子中的值
    • 叶子节点满,直属索引节点未满。删除叶子中的值,与其兄弟节点合并,更新直属索引节点
    • 叶子节点满,直属索引节点满。删除叶子中的值,与其兄弟节点合并,更新直属索引节点,再更新上一层的索引节点和他的兄弟节点

数据库中的索引一般是在磁盘上,每访问一个节点,就对应着一次硬盘 IO 操作,数据量的大的情况下,索引无法一次装入内存,B+树的设计可以允许数据分批加载,同时树的高度较低,提高查找效率。

B+树索引

根据叶子节点是否存放整行的数据分为:

  • 聚集索引

    按照每张表的主键构造一颗B+树,数据页(叶子节点)上存放的是的完整的行记录,而在索引页(非叶子节点)中存放的是键值及指向数据页的偏移量。因为页是通过双向链表链接,按照主键顺序排序,所以聚集索引的存储是逻辑上连续而非物理上连续。

  • 辅助索引

    叶子节点存放的是键值和相应行数据的聚集索引键。当通过辅助索引来寻找数据时,InnoDB 存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键,然后再通过主键索引来找到一个完整的行记录。 如果在一个高度为 3 的辅助索引树中查找数据,那需要对这棵辅助索引树遍历 3 次找到指定主键,如果聚集索引树的高度同样为 3,那么还需要对聚集索引树进行 3 次查找,最终找到一个完整的行数据所在的页,因此一共需要 6 次逻辑 IO 访问最终的一个数据页。

什么时候添加B+树索引?

在高选择率字段上使用B+树才有意义,对于性别、地区、类型字段,他们可取值的范围很小,是低选择性字段,添加索引意义不大。

B+树索引使用

  • 联合索引

    对表上的多个列进行索引。从本质上说,联合索引也是一颗B+树,不同的是联合索引的键值数量大于等于2。如果一张表上有联合索引(a, b, c),那么找(a), (a, b), (a, b, c)都会走联合索引,但是找(b), (c), (b,c), (a,c)无法避免额外排序。

  • 覆盖索引

    从辅助索引中就能得到查询结果,而不需要查询聚集索引中的记录,从而减少大量的IO操作。常用于某些统计问题。如果table上有主键索引也有辅助索引,那么select count(*) from table优先走辅助索引。通过explain查看执行计划中的Extra字段,如果有Using index代表使用了覆盖索引。

优化器选择不使用辅助索引的情况

select * from table where id > 100 and id < 10000,这种需要行记录全部字段数据的情况,尽管走辅助索引数据是有序存放的,但是根据其叶指针查找的行记录则是无序的,因此变成了磁盘上的离散读操作,所以优化器会选择直接走主键索引。但如果访问的数据量很小的话,优化器会走辅助索引。

面试实战问题

为什么是B+树,而不是其他的数据结构?

  • 每个节点的索引数据量的大小的角度,
  • 树高是否一致,查询写入是否稳定,
  • 顺序逆序访问,叶子结点双向链表

锁机制用于管理对共享资源的并发访问,提供数据的完整性和一致性,是数据库系统区别于文件系统的一个关键特性。InnoDB根据每个事务访问的每个页对锁进行管理,采用的是位图的方式,也就是说,不管一个事务锁住页中一个记录还是多个记录,其开销通常是一致的。

锁类型

  • 行级锁

    • 共享锁(S Lock),允许事务读一行数据。
    • 排他锁(X Lock),允许事务删除或更新一行数据。

    InnoDB所有的行锁算法都是基于索引实现的,锁定的也都是索引或索引区间。在默认的RR级别,InnoDB会自动为增删改操作的行加X锁,读不加锁(只有在Serializable级别会为读加S锁),并在事务提交或回滚后自动释放锁。

  • 表级锁

    • 表级共享锁(S Lock),用读锁锁表,会阻塞其他事务修改该表数据。
    • 表级排他锁(X Lock),用写锁锁表,会阻塞其他事务对该表的读和写。
    • 意向共享锁(IS Lock),事务想要获得一张表中某几行的行共享锁。
    • 意向排他锁(IX Lock),事务想要获得一张表中某几行的排他锁。
    • 自增锁(AOTU-INC Lock),一种特殊表锁,用于保证自增长计数器的正常赋值。

在为行数据加共享 / 排他锁之前,InooDB 会先获取该数据行所在数据表的对应意向锁,用户无法手动操作意向锁。

锁的兼容性 表级排他锁(X) 意向排他锁(IX) 表级共享锁(S) 意向共享锁(IS)
表级排他锁(X) N N N N
意向排他锁(IX) N Y N Y
表级共享锁(S) N N Y Y
意向共享锁(IS) N Y Y Y

按照上面的兼容性,如果不同事务之间的锁兼容,则当前加锁事务可以持有锁,如果有冲突则会等待其他事务的锁释放。由于InnoDB支持的是行级别的锁,因此意向锁不会阻止除了全表锁定请求之外的任何锁请求,其主要目的是显示事务正在锁定某行或者正意图锁定某行。

通过在INFORMATION_SHECMA架构下的表INNODB_TRX,INNODB_LOCKS,INNODB_LOCK_WAITS,用户可以简单的监控当前事务并分析可能存在的锁问题。对应的SQL语句为 select * from information_shecma.INNODB_TRX;

锁实现

行锁

InnoDB中的行锁是通过对索引数据上的记录加锁实现的。有3种行锁算法,分别是:

  • Record Lock(行锁):单个行记录的锁,锁数据,不锁Gap。
  • Gap Lock(间隙锁):锁定一个范围,不锁数据,仅锁数据前面的Gap。
  • Next-key Lock:行锁与间隙锁的结合,锁数据,锁Gap。

默认情况(隔离级别为RR),InnoDB对于行的查询都是采用Next-key Lock算法,例如一个索引有10,11,13,20这四个值,那么该索引可能被Next-Key Lock的区间为:(-无穷大,10](10, 11](11, 13](13,20](20,+无穷大],还有一种Previous-key Locking的技术,其区间为左闭右开。

  • 当查询的索引是辅助索引或是多列唯一索引中的某一列,用Next-key Lock进行锁定。

    举个例子,一张表z中,列a是主键,列b是一般键,即辅助索引,已有行数据(1, 1),(3, 1),(5, 3),(7, 6),(10, 8)

    时间 会话A 会话B 会话C
    1 begin;
    2 select * from t where b = 3 for update; begin; begin;
    3 select * from z where a = 5 lock in share mode; insert into z select 8, 6;
    4 insert into z select 4, 2; insert into z select 2, 0;
    5 insert into z select 6, 5; insert into z select 6, 7;

    会话A中通过索引列b进行查询,需要对两个索引分别锁定。

    • 对于聚集索引,其仅对列a等于5的索引加上Record Lock,即范围为a∈[5];

    • 对于辅助索引,对键值3加上Next-Key Lock,作用范围(1, 3],且对下一个键值6,加上Gap Lock,作用范围(3, 6),总范围为b∈(1, 6);

    因此会话B中SQL语句全部阻塞,会话C中全部可以立即执行。

  • 当查询的索引是唯一索引时,InnoDB会将Next-key Lock降级为Record Lock。

    举个例子,在一张表t中,列a是主键,已有行数据1,2,5,执行如下操作:

    时间 会话A 会话B
    1 begin;
    2 select * from t where a = 5 for update;
    3 begin;
    4 insert into t select 4;
    5 commit; #成功,不需要等待
    6 commit

    在会话A中首先对a = 5进行X锁定,而由于a是主键且唯一,因此锁定的仅是5这个值,而不是(2, 5)这个范围,这样在会话B中插入值4不会阻塞,可以立即插入并返回。

  • 当查询不使用索引时,对主键索引全表加上Next-key Lock,相当于锁表了。

外键锁

对于一个外键列,如果没有显示地对这个列加索引,InnoDB会自动对其加一个索引,这样可以避免表锁。

对于外键值的插入或更新,首先需要select 父表 lock in share mode,即主动对父表加一个表级S锁,以此来保证与父表数据的一致性。

加锁读方式

  • 一致性非锁定读,对应乐观锁、快照读

    直接读取一个快照数据,而不需要等待访问的行上X锁的释放,在事务隔离级别RC和RR下,InnoDB使用非锁定的一致性读。举个例子,在一张表中,现在只有一条id = 1的行数据,执行如下操作:

    时间 会话A 会话B
    1 begin;
    2 select * from parent where id = 1;
    3 begin;
    4 update parent set id = 3 where id = 1;
    5 select * from parent where id = 1;
    6 commit;
    7 select * from parent where id = 1;
    8 commit;
    • 在RC中读取被锁定行的最新一份快照数据。对于上述例子,步骤5,查询结果非空;步骤7,查询结果为空
    • 在RR中读取事务开始时的行数据版本。对于上述例子,步骤5,查询结果非空;步骤7,查询结果非空。
  • 一致性锁定读,对应悲观锁、当前读

    在默认配置下,事务的隔离级别为RR,InnoDB的select操作使用一致性非锁定读,但在某些情况下(转账交易等),用户需要显式地对数据库读取操作加锁以保证数据逻辑的一致性。这要求数据库支持加锁语句,即使是对select的只读操作。InnoDB对于select支持两种一致性等待锁定读:

    • select ... for update,对读取的行记录加一个X锁(事务要获取某些行的 X 锁,必须先获得表的 IX 锁)
    • select ... lock in share mode,对读取的行记录加一个S锁(事务要获取某些行的 S 锁,必须先获得表的 IS 锁)

锁问题

脏读

先区分脏页与脏数据:

  • 脏页:在缓冲池中已经被修改的页,但是还没有刷新到磁盘中
  • 脏数据:事务对缓冲池中行记录的修改,还没有被提交。

脏读指的就是在不同的事务下,当前事务可以读到另外事务未提交的数据。举个例子,在一张表中只有1个行数据,值为a = 1:

时间 会话A 会话B
1 set @@tx_isolation = 'read-uncommitted'
2 set @@tx_isolation = 'read-uncommitted'
3 begin;
4 select * from t ; # a = 1;
5 insert into t select 2;
6 select * from t # a = 1, 2;

在会话A中,事务并没有提交,但是在会话B中的两次select操作取得不同的结果,即产生了脏读,违反了事务的隔离性。

脏读看似毫无用处,但在特殊情况也可以使用,如replication环境中的slave节点,并且在该slave上查询并不需要特别精确的返回值。

不可重复读

不可重复读指的是在一个事务内多次读到的数据是不一样的情况。

区分不可重复读与脏读

  • 脏读是在当前事务内读取到未提交的数据
  • 不可重复读是在当前事务内读取到其他事务已提交的数据,但前后不一致。

举个例子,在一张表中只有1个行数据,值为a = 1:

时间 会话A 会话B
1 set @@tx_isolation = 'read-committed'
2 set @@tx_isolation = 'read-committed'
3 begin; begin;
4 select * from t ; # a = 1;
5 insert into t select 2;
6 commit;
7 select * from t # a = 1, 2;

在事务隔离级别RC下,InnoDB仅采用Record Lock,也就是在第一次查询时仅锁住了范围[1],允许行数据2的插入,于是在会话A中一个事务的两次读取结果因为另一个事务的修改而不一致,即产生了不可重复读,违反了事务的一致性。

一般来说,不可重复读也是可以接受的,Oracle,MS SQL Server的隔离级别是RC,即允许不可重复读的现象。

幻读

在MySQL官方文档中将不可重复读定义为幻读,也就是说他们本质是一类问题,即当前事务还未结束前读取到了其他事务提交之后的结果。

非要强行说区别的话(如果面试官的知识体系跟我们不一样的话),那么:

  • 不可重复读强调 update, delete
  • 幻读强调 insert

InnoDB采用Next-Key Lock算法避免出现幻读,对于上个例子,select * from t,其在范围(-无穷大,+无穷大)上加了X锁,因此其他事务执行的插入操作会被阻塞。

丢失更新

丢失更新指的是一个事务的更新操作会被另一个事务的更新操作所覆盖,从而导致数据不一致。

在当前数据库的任何隔离级别下,即使是RU,对于行的DML操作,需要对行或其他粒度级别的对象加锁,因此理论上不会出现丢失更新的情况,但考虑这种情况,针对多用户计算机系统环境,出现以下情况,就会发生丢失更新:

时间 会话A 会话B
1 begin;
2 查询余额a = 1000,放入本地内存,显示给终端用户A begin;
3 update a = 100; 查询同一行数据a = 1000,显示给一个终端用户B
4 commit; update a = 500;
5 commit;

最终的结果是两个事务都提交成功,但是最终的余额却不是1000 - 100 - 500 = 400。

要避免丢失更新发生,需要让事务在这种情况下的操作变成串行化,即在查询时,对用户读取的记录加上一个排他锁,会话B必须等待会话A完成之后方可执行

死锁

死锁产生的四个必要条件:

  • 互斥:一个资源每次只能被一个进程使用。
  • 请求与保持:一个进程因请求资源而阻塞时,对已获得的资源保持不放。
  • 不抢占:进程已获得的资源,在没事用完之前,不强行剥夺。
  • 循环等待:多个进程之间形成一种互相循环等待资源的关系。

以上四个条件,任何一个被破坏,就有可能产生死锁。

MyISAM中仅有表锁的概念,所以不会发生死锁问题,而InnoDB是逐行加锁的,容易产生死锁,在发生死锁时,InnoDB会自动检测并回滚持有最少行锁的事务来解决死锁问题。但很多时候需要人工解决,最好的方式还是从根源上避免死锁的产生,对此有如下几种做法:

  • 加锁顺序一致,尽量一次性锁定所有所需的数据行。
  • 控制事务大小,尽量减少锁定数据量和锁定时间长度。
  • 减少范围更新,尤其非主键/非唯一键上的范围更新。(即对辅助索引的更新是Next-Key Lock级别,锁强度最大)
  • 减少锁定资源,SQL语句的where过滤尽量走索引。(避免直接读缓冲池中的数据页导致的低效)

三级封锁协议

  • 一级封锁协议:事务 T 要修改数据 A 时必须加 X 锁,直到 T 结束才释放锁,可以解决丢失更新问题。
  • 二级封锁协议:在一级的基础上,要求读取数据 A 时必须加 S 锁,读取完马上释放 S 锁,可以解决读脏数据问题。
  • 三级封锁协议:在二级的基础上,要求读取数据 A 时必须加 S 锁,直到事务结束才能释放 S 锁,可以解决不可重复读的问题。

事务

事务的特性

事务遵循ACID四大原则:原子性、事务性、持久性、一致性

  • 原子性(atomicity):事务的所有操作,要么全部完成,要么全部不完成。通过redo log完成。
  • 一致性(consistency):由约束一致性(创建表时所指定的外键、唯一索引等)和数据一致性(由其他三个特性共同保证的结果)保证。通过undo log实现。
  • 隔离性(isolation):一个事务提交前的任何操作,其他事务都无法看见。通过锁实现。
  • 持久性(durability):事务一旦提交,其结果就是永久性的。通过redo log实现。

事务实现

InnoDB是事务的储存引擎,使用日志优先技术(Write-Ahead-Logging,WAL)实现事务的持久化。也就是说当一个事务提交时,必须先将该事务的所有日志写入重做日志文件中进行持久化,待事务的提交操作完成才算完成,若过程中事务更新失败,则通过重做日志文件回滚。

其中,该事务的所有日志包括redo log 跟 undo log,它们的区别是:

  • redo log:物理日志,顺序写,记录的是页的物理修改操作,恢复提交事务修改的页操作,用于保证事务的持久性。
  • undo log:逻辑日志,随机写,根据每行数据进行记录, 回滚行数据到某个特定版本,用于帮助事务回滚及MVCC功能。

事务隔离级别

以下四种隔离级别的具体表现仅针对InnoDB,隔离级别越低,事务请求的锁越少或保持锁的时间越短。用set @@tx_isolation = 'READ-CCOMMITTED'来设置:

  • 读未提交(RU, Read Uncommitted)

    没有应用锁机制。

    一个事务可以读到另一个事务未提交的结果,存在脏读和不可重复读问题。

  • 读提交(RC, Read Committed)(如何实现的)

    除了唯一性的约束检查和外键约束检查需要Gap Lock,其他全是用Record Lock的算法。

    只有在事务提交后,其更新结果才会被其他事务看见。可以解决脏读问题,存在不可重复读问题。

  • 可重复读(RR, Repeatable Read)

    基本都是用Next-Key Lock算法,除了当查询的索引是唯一索引时退化成Record Lock,支持的是一致性的非锁定读。

    在一个事务中,对于同一份数据的读取结果总是相同的,无论是否有其他事务对这份数据进行操作,以及这个事务是否提交。可以解决脏读、不可重复读(幻读)问题。这是InnoDB默认级别。

  • 可串行化(Serialiable)

    对每个select语句后自动加上lock in share mode,即为每个读取操作加一个共享锁,支持的是一致性锁定读。

    事务串行化执行,隔离级别最高,单机不再具有并发性,常用于分布式事务。

MVCC

多版本并发控制(Multi-Version Concurrency Control, MVCC)是 InnoDB 实现隔离级别的一种具体方式,用于实现RC和RR这两种隔离级别。RU总是读取最新的数据行,无需使用 MVCC,而Serialiable需要对所有读取的行都加锁,单纯使用 MVCC 无法实现。

先了解版本号的概念:

  • 系统版本号:是一个递增的数字,每开始一个新的事务,系统版本号就会自动递增。
  • 事务版本号:事务开始时的系统版本号。当开始新一个事务时,该事务的版本号肯定会大于当前所有数据行快照的创建版本号

MVCC 在每行记录后面都保存着两个隐藏的列,用来存储两个版本号:

  • 创建版本号:创建一个数据行的快照时的系统版本号。如果该快照的创建版本号大于当前事务版本,表示该快照是其他事务的最新修改结果,不该读取。
  • 删除版本号:删除一个数据行的快照时的系统版本号。如果该快照的删除版本号大于当前事务版本号,表示该快照有效, 否则表示该快照已经被删除了。

MVCC 使用到的快照存储在 undo log中,该日志通过回滚指针把一个行数据的所有快照连接起来。

RR级别的具体实现如下:

  • select

    仅读事务T 所要读取的数据行快照的创建版本号必须小于 T 的版本号,因为如果大于或者等于 T 的版本号,那么表示该数据行快照是其它事务的最新修改,因此不能去读取它。除此之外,T 所要读取的数据行快照的删除版本号必须大于 T 的版本号,因为如果小于等于 T 的版本号,那么表示该数据行快照是已经被删除的,不应该去读取它。

  • insert

    将当前系统版本号作为数据行快照的创建版本号。

  • delete

    将当前系统版本号作为数据行快照的删除版本号。

  • update

    将当前系统版本号作为更新前的数据行快照的删除版本号,并将当前系统版本号作为更新后的数据行快照的创建版本号。可以理解为先执行 delete 后执行 insert

分布式事务

分布式事务

分布式系统存在3个重要指标:

  • Consistency:一致性,写操作之后的读操作,必须返回该值(问题的关键在于可能在服务器A上执行了写入操作,但是读是分配到了服务器B上,由于A、B不一致导致的数据不一致性)
  • Availability:可用性,任何时候服务都是可用的。只要收到用户的请求,服务器就必须给出回应。(为了实现一致性,服务器们就需要在写入操作发生时执行同步操作,以实现一致性)
  • Partition tolerance:分区容错性,指多个设备或单元之间的通讯不一定成功

上述三个指标也被称为CAP问题,但任何系统只能满足其中的两点。由于P是必须要考虑到的问题,所以现实中,CA不得兼容

InnoDB通过 XA 事务来支持分布式事务,此时的事务隔离级别必须设置为Serializable。XA 事务由一个或多个资源管理器、一个事务管理器以及一个应用程序组成:

  • 资源管理器(Resource Managers):提供访问事务资源的方法,通常一个MySQL数据库就是一个资源管理器。
  • 事务管理器(Transaction Manager):协调参与全局事务中的各个事务,需要和参与全局事务的所有资源管理器进行通信,通常为连接MySQL服务器的客户端。
  • 应用程序(Application Program):定义事务的边界,指定全局事务中的操作。

分布式事务使用两阶段提交(two-phase commit),与本地事务不同的是,分布式事务需要多一次的PREPARE操作,待收到所有节点的同意信息后,再进行commit 或是 rollback 操作。

  • 在第一阶段,所有参与全局事务的节点都开始准备(prepare),告诉事务管理器它们准备好提交了。
  • 在第二阶段,事务管理器告诉资源管理器执行 rollback 还是 commit。如果任何一个节点显示不能提交,则所有的节点都被告知需要回滚。

实际操作中,基本都是通过编程语言来完成分布式事务的操作。

主从复制

通过二进制日志文件在主从服务器之间传递备份

读写分离

一致性哈希