当前位置: 澳门新濠3559 > 数据库 > 正文

在面试中也会经常会被问到的,搜索相关资料后

时间:2019-11-07 13:43来源:数据库
一次MySQL死锁问题解决 当 InnoDB 存储数据时,它可以使用不同的行格式进行存储;MySQL 5.7版本支持以下格式的行存储方式: 共享锁:允许一个事务去读一行,阻止其他事务获得相同数据

一次MySQL死锁问题解决

当 InnoDB 存储数据时,它可以使用不同的行格式进行存储;MySQL 5.7 版本支持以下格式的行存储方式:

  • 共享锁:允许一个事务去读一行,阻止其他事务获得相同数据集的排他锁。
    • 也叫做读锁:读锁是共享的,多个客户可以同时读取同一个资源,但不允许其他客户修改
  • 排他锁:允许获得排他锁的事务更新数据,阻止其他事务取得相同数据集的共享读锁和排他写锁。
    • 也叫做写锁:写锁是排他的,写锁会阻塞其他的写锁和读锁

四、分析

搜索相关资料后发现,原来MySQL InnoDB并不一定都是行级锁。

相关参考资料片段如下:

MySQL InnoDB锁机制之Gap Lock、Next-Key Lock、Record Lock解析
http://blog.sina.com.cn/s/blog_a1e9c7910102vnrj.html

4、锁选择
1)、如果更新条件没有走索引,例如执行”update from t1 set v2=0 where v2=5;” ,此时会进行全表扫描,扫表的时候,要阻止其他任何的更新操作,所以上升为表锁。
2)、如果更新条件为索引字段,但是并非唯一索引(包括主键索引),例如执行“update from t1 set v2=0 where v1=9;” 那么此时更新会使用Next-Key Lock。使用Next-Key Lock的原因:
a)、首先要保证在符合条件的记录上加上排他锁,会锁定当前非唯一索引和对应的主键索引的值;
b)、还要保证锁定的区间不能插入新的数据。
3)、如果更新条件为唯一索引,则使用Record Lock(记录锁)。

InnoDB根据唯一索引,找到相应记录,将主键索引值和唯一索引值加上记录锁。但不使用Gap Lock(间隙锁)。

MySQL InnoDB 锁表与锁行
http://blog.csdn.net/qq250782929/article/details/52063975

由于InnoDB预设是Row-Level Lock,所以只有「明确」的指定主键,MySQL才会执行Row lock (只锁住被选取的资料例) ,否则MySQL将会执行Table Lock (将整个资料表单给锁住)。

根据分析结论,猜测是在更新_task_tel表时Where条件中tel_id和task_id没有建立UNIQUE(唯一索引)原因;

Compact 和 Redundant 格式最大的不同就是记录格式的第一个部分;在 Compact 中,行记录的第一部分倒序存放了一行数据中列的长度(Length),而 Redundant 中存的是每一列的偏移量(Offset),从总体上上看,Compact 行记录格式相比 Redundant 格式能够减少 20% 的存储空间。

索引在数据库中是一个非常重要的知识点!上面谈的其实就是索引最基本的东西,要创建出好的索引要顾及到很多的方面:

五、解决

据此分析,尝试通过tel_id和task_id两个字段建立UNIQUE(唯一索引)来解决。 (也可以先查询出来,然后根据主键ID来更新,这样不会因表中数据量较大影响线上业务)。

用索引字段做为条件进行修改时, 是否表锁的取决于这个索引字段能否确定记录唯一,当索引值对应记录不唯一,会进行锁表,相反则行锁。

通过此种方式解决后,问题没有再重现。

如果你的问题和我遇到的类似,可以尝试据此解决。

锁的粒度

声明:如果没有说明具体的数据库和存储引擎,默认指的是MySQL中的InnoDB存储引擎

二、错误日志

2017-2-25 17:38:41 org.hibernate.util.JDBCExceptionReporter logExceptions
严重: Lock wait timeout exceeded; try restarting transaction
2017-2-25 17:39:05 org.hibernate.util.JDBCExceptionReporter logExceptions
警告: SQL Error: 1213, SQLState: 40001
2017-2-25 17:39:05 org.hibernate.util.JDBCExceptionReporter logExceptions
严重: Deadlock found when trying to get lock; try restarting transaction

索引的设计其实是一个非常重要的内容,同时也是一个非常复杂的内容;索引的设计与创建对于提升数据库的查询性能至关重要,不过这不是本文想要介绍的内容,有关索引的设计与优化可以阅读 数据库索引设计与优化 一书,书中提供了一种非常科学合理的方法能够帮助我们在数据库中建立最适合的索引,当然作者也可能会在之后的文章中对索引的设计进行简单的介绍和分析。

看起来哈希索引很牛逼啊,但其实哈希索引有好几个局限(根据他本质的原理可得):

三、排查

Check InnoDB status for locks
mysql> SHOW ENGINE InnoDB STATUS;

Check MySQL open tables
mysql> SHOW OPEN TABLES WHERE In_use > 0;

Check pending InnoDB transactions
mysql> SELECT * FROM `information_schema`.`innodb_trx` ORDER BY `trx_started`; 

Check lock dependency - what blocks what
mysql> SELECT * FROM `information_schema`.`innodb_locks`;

排查后发现都是执行类似这样的语句出现问题的:

update t_task_tel set state='iok', update_date='2017-02-27 11:03:02' where tel_id=66042 and task_id=350199;

数据库将所有的非聚集索引都划分为辅助索引,但是这个概念对我们理解辅助索引并没有什么帮助;辅助索引也是通过 B+ 树实现的,但是它的叶节点并不包含行记录的全部数据,仅包含索引中的所有键和一个用于查找对应行记录的『书签』,在 InnoDB 中这个书签就是当前记录的主键。

  • 本质上就是把键值换算成新的哈希值,根据这个哈希值来定位

六、参考资料

  • Mysql Innodb小结
  • MySQL InnoDB 锁表与锁行
  • MySQL InnoDB锁机制之Gap Lock、Next-Key Lock、Record Lock解析
  • mysql InnoDB锁等待的查看及分析
  • MySQL的表锁问题(二)——InnoDB表锁问题

  • SELECT … FOR UPDATE如何影响INNODB的锁级别 ---added by 20170305

共享锁(读锁):允许事务对一条行数据进行读取;

澳门新濠3559 1image

一、环境

  • CentOS, MySQL 5.6.21-70, JPA
  • 问题场景:系统有定时批量更新数据状态操作,每次更新上千条记录,表中总记录数约为500W左右。

记录锁是在存储引擎中最为常见的锁,除了记录锁之外,InnoDB 中还存在间隙锁(Gap Lock),间隙锁是对索引记录中的一段连续区域的锁;当使用类似 SELECT * FROM users WHERE id BETWEEN 10 AND 20 FOR UPDATE;的 SQL 语句时,就会阻止其他事务向表中插入 id = 15 的记录,因为整个范围都被间隙锁锁定了。

我们应该更加关注行锁的内容,因为InnoDB一大特性就是支持行锁!

聚集索引和辅助索引

区别:

不可重复读

脏读

只有光头才能变强

MySQL 从第一个版本发布到现在已经有了 20 多年的历史,在这么多年的发展和演变中,整个应用的体系结构变得越来越复杂:

首先Mysql的基本存储结构是

在页的头部和尾部之间就是用户记录和空闲空间了,每一个数据页中都包含 Infimum 和 Supremum 这两个虚拟的记录(可以理解为占位符),Infimum 记录是比该页中任何主键值都要小的值,Supremum 是该页中的最大值:

  • 我们前面知道了,如果不是聚集索引,叶子节点存储的是主键+列值
  • 最终还是要“回表”,也就是要通过主键查找一次。这样就会比较慢
  • 覆盖索引就是把要查询出的列和索引是对应的,不做回表操作!

事务与隔离级别

  • Mysql索引

当 InnoDB 使用 Compact 或者 Redundant 格式存储极长的 VARCHAR 或者 BLOB 这类大对象时,我们并不会直接将所有的内容都存放在数据页节点中,而是将行数据中的前 768 个字节存储在数据页中,后面会通过偏移量指向溢出页。

从上面也说了:我们是很少手动加表锁的。表锁对我们程序员来说几乎是透明的,即使InnoDB不走索引,加的表锁也是自动的!

重新开启了两个会话 SESSION 1 和 SESSION 2,在 SESSION 1 中我们查询全表的信息,没有得到任何记录;在 SESSION 2 中向表中插入一条数据并提交;由于 REPEATABLE READ 的原因,再次查询全表的数据时,我们获得到的仍然是空集,但是在向表中插入同样的数据却出现了错误。

如果一棵普通的树在极端的情况下,是能退化成链表的(树的优点就不复存在了)

同一个数据库实例的所有表空间都有相同的页大小;默认情况下,表空间中的页大小都为 16KB,当然也可以通过改变 innodb_page_size 选项对默认大小进行修改,需要注意的是不同的页大小最终也会导致区大小的不同:

澳门新濠3559 2image

MySQL 的架构

澳门新濠3559 3image

本文中对于数据库的介绍以及研究都是在 MySQL 上进行的,如果涉及到了其他数据库的内容或者实现会在文中单独指出。

澳门新濠3559 4image

如何存储记录

  • InnoDB行锁和表锁都支持
  • MyISAM只支持表锁

澳门新濠3559 5

参考资料:

在整个数据库体系结构中,我们可以使用不同的存储引擎来存储数据,而绝大多数存储引擎都以二进制的形式存储数据;这一节会介绍 InnoDB 中对数据是如何存储的。

索引和锁在数据库中可以说是非常重要的知识点了,在面试中也会经常会被问到的。

Next-Key 锁相比前两者就稍微有一些复杂,它是记录锁和记录前的间隙锁的结合,在 users 表中有以下记录:

澳门新濠3559 6image

当我们更新一条记录,比如 SELECT * FROM users WHERE age = 30 FOR UPDATE;,InnoDB 不仅会在范围 (21, 30] 上加 Next-Key 锁,还会在这条记录后面的范围 (30, 40] 加间隙锁,所以插入 (21, 40] 范围内的记录都会被锁定。

表锁下又分为两种模式

这种现象在数据库中就被称作幻读,虽然我们使用查询语句得到了一个空的集合,但是插入数据时却得到了错误,好像之前的查询是幻觉一样。

  • 聚集索引在叶子节点存储的是表中的数据
  • 非聚集索引在叶子节点存储的是主键和索引列
  • 使用非聚集索引查询出数据时,拿到叶子上的主键再去查到想要查找的数据。(拿到主键再查找这个过程叫做回表)

死锁的发生

2.2.1MVCC和事务的隔离级别

数据库事务有不同的隔离级别,不同的隔离级别对锁的使用是不同的,锁的应用最终导致不同事务的隔离级别

MVCC(Multi-Version Concurrency Control)多版本并发控制,可以简单地认为:MVCC就是行级锁的一个变种

  • 事务的隔离级别就是通过锁的机制来实现,只不过隐藏了加锁细节

表锁中我们读写是阻塞的,基于提升并发性能的考虑,MVCC一般读写是不阻塞的(所以说MVCC很多情况下避免了加锁的操作)

  • MVCC实现的读写不阻塞正如其名:多版本并发控制--->通过一定机制生成一个数据请求时间点的一致性数据快照,并用这个快照来提供一定级别(语句级或事务级)的一致性读取。从用户的角度来看,好像是数据库可以提供同一数据的多个版本

快照有两个级别

  • 语句级
    • 针对于Read committed隔离级别
  • 事务级别
    • 针对于Repeatable read隔离级别

我们在初学的时候已经知道,事务的隔离级别有4种

  • Read uncommitted
    • 会出现脏读,不可重复读,幻读
  • Read committed
    • 会出现不可重复读,幻读
  • Repeatable read
    • 会出现幻读(但在Mysql实现的Repeatable read配合gap锁不会出现幻读!)
  • Serializable
    • 串行,避免以上的情况!

Read uncommitted会出现的现象--->脏读:一个事务读取到另外一个事务未提交的数据

  • 例子:A向B转账,A执行了转账语句,但A还没有提交事务,B读取数据,发现自己账户钱变多了!B跟A说,我已经收到钱了。A回滚事务,等B再查看账户的钱时,发现钱并没有多。
  • 出现脏读的本质就是因为操作完该数据就立马释放掉锁,导致读的数据就变成了无用的或者是错误的数据

Read committed避免脏读的做法其实很简单:

  • 就是把释放锁的位置调整到事务提交之后,此时在事务提交前,其他进程是无法对该行数据进行读取的,包括任何操作

Read committed出现的现象--->不可重复读:一个事务读取到另外一个事务已经提交的数据,也就是说一个事务可以看到其他事务所做的修改

  • 注:A查询数据库得到数据,B去修改数据库的数据,导致A多次查询数据库的结果都不一样【危害:A每次查询的结果都是受B的影响的,那么A查询出来的信息就没有意思了】

上面也说了,Read committed语句级别的快照!每次读取的都是当前最新的版本

Repeatable read避免不可重复读是事务级别的快照!每次读取的都是当前事务的版本,即使被修改了,也只会读取当前事务版本的数据。

呃...如果还是不太清楚,我们来看看InnoDB的MVCC是怎么样的吧(摘抄《高性能MySQL》)

澳门新濠3559 7image澳门新濠3559 8image

至于虚读:是指在一个事务内读取到了别的事务插入的数据,导致前后读取不一致。

  • 注:和不可重复读类似,但虚读会读到其他事务的插入的数据,导致前后读取不一致
  • MySQL的Repeatable read隔离级别加上GAP间隙锁已经处理了幻读了

参考资料:

扩展阅读:

无论是Read committed还是Repeatable read隔离级别,都是为了解决读写冲突的问题。

单纯在Repeatable read隔离级别下我们来考虑一个问题:

澳门新濠3559 9image

此时,用户李四的操作就丢失掉了:

  • 丢失更新:一个事务的更新覆盖了其它事务的更新结果澳门新濠3559,。

(ps:暂时没有想到比较好的例子来说明更新丢失的问题,虽然上面的例子也是更新丢失,但一定程度上是可接受的..不知道有没有人能想到不可接受的更新丢失例子呢...)

解决的方法:

  • 使用Serializable隔离级别,事务是串行执行的!
  • 乐观锁
  • 悲观锁
  1. 乐观锁是一种思想,具体实现是,表中有一个版本字段,第一次读的时候,获取到这个字段。处理完业务逻辑开始更新的时候,需要再次查看该字段的值是否和第一次的一样。如果一样更新,反之拒绝。之所以叫乐观,因为这个模式没有从数据库加锁,等到更新的时候再判断是否可以更新。
  2. 悲观锁是数据库层面加锁,都会阻塞去等待锁。

并发控制机制

2.3.1悲观锁

所以,按照上面的例子。我们使用悲观锁的话其实很简单:

  • select * from xxxx for update

在select 语句后边加了 for update相当于加了排它锁,加了写锁以后,其他的事务就不能对它修改了!需要等待当前事务修改完之后才可以修改.

  • 也就是说,如果张三使用select ... for update,李四就无法对该条记录修改了~

如何存储表

  • B+树是一颗平衡树,如果我们对这颗树增删改的话,那肯定会破坏它的原有结构
  • 要维持平衡树,就必须做额外的工作。正因为这些额外的工作开销,导致索引会降低增删改的速度

当我们使用聚集索引对表中的数据进行检索时,可以直接获得聚集索引所对应的整条行记录数据所在的页,不需要进行第二次操作。

  • 如有索引(a, b, c, d),查询条件a = 1 and b = 2 and c > 3 and d = 4,则会在每个节点依次命中a、b、c,无法命中d。(很简单:索引命中只能是相等的情况,不能是范围匹配)

无论在 MySQL 中选择了哪个存储引擎,所有的 MySQL 表都会在硬盘上创建一个 .frm 文件用来描述表的格式或者说定义;.frm 文件的格式在不同的平台上都是相同的。

REPERATABLE READ 和 READ UNCOMMITED 其实是矛盾的,如果保证了前者就看不到已经提交的事务,如果保证了后者,就会导致两次查询的结果不同,MySQL 为我们提供了一种折中的方式,能够在 REPERATABLE READ 模式下加锁访问已经提交的数据,其本身并不能解决幻读的问题,而是通过文章前面提到的 Next-Key 锁来解决。

在创建多列索引中也涉及到了一种特殊的索引-->覆盖索引

行溢出数据

只会在某些特定的场景下才需要手动加锁,学习数据库锁知识就是为了:

既然叫 Next-Key 锁,锁定的应该是当前值和后面的范围,但是实际上却不是,Next-Key 锁锁定的是当前值和前面的范围。

  • 表锁
    • 开销小,加锁快;不会出现死锁;锁定力度大,发生锁冲突概率高,并发度最低
  • 行锁
    • 开销大,加锁慢;会出现死锁;锁定粒度小,发生锁冲突的概率低,并发度高

InnoDB 中用于存储数据的文件总共有两个部分,一是系统表空间文件,包括 ibdata1、ibdata2 等文件,其中存储了 InnoDB 系统信息和用户数据库表数据和索引,是所有表公用的。

澳门新濠3559 10image澳门新濠3559 11image

Record Lock

  • MyISAM可以支持查询和插入操作的并发进行。可以通过系统变量concurrent_insert来指定哪种模式,在MyISAM中它默认是:如果MyISAM表中没有空洞(即表的中间没有被删除的行),MyISAM允许在一个进程读表的同时,另一个进程从表尾插入记录。
  • 但是InnoDB存储引擎是不支持的

辅助索引

  • 对于UPDATE、DELETE、INSERT语句,InnoDB自动给涉及数据集加排他锁
  • MyISAM在执行查询语句SELECT前,会自动给涉及的所有表加读锁,在执行更新操作(UPDATE、DELETE、INSERT等)前,会自动给涉及的表加写锁,这个过程并不需要用户干预

REPEATABLE READ:多次读取同一范围的数据会返回第一次查询的快照,不会返回不同的数据行,但是可能发生幻读(Phantom Read);

非聚集索引也叫做二级索引,不用纠结那么多名词,将其等价就行了~

到目前为止已经对 InnoDB 中锁的粒度有一定的了解,也清楚了在对数据库进行读写时会获取不同的锁,在这一小节将介绍锁是如何添加到对应的数据行上的,我们会分别介绍三种锁的算法:Record Lock、Gap Lock 和 Next-Key Lock。

B+树删除和修改具体可参考:

意向共享锁:事务想要在获得表中某些记录的共享锁,需要在表上先加意向共享锁;

  • 此时就涉及到了哪个列会走索引,哪个列不走索引的问题了(最左匹配原则-->后面有说)
  • 创建多个单列索引的时候,会生成多个索引树(所以过多创建索引会占用磁盘空间)

如果我们使用 id 或者 last_name 作为 SQL 中 WHERE 语句的过滤条件,那么 InnoDB 就可以通过索引建立的 B+ 树找到行记录并添加索引,但是如果使用 first_name 作为过滤条件时,由于 InnoDB 不知道待修改的记录具体存放的位置,也无法对将要修改哪条记录提前做出判断就会锁定整个表。

参考资料:

数据库:物理操作文件系统或其他形式文件类型的集合;

不同的存储引擎支持的锁粒度是不一样的:

当打开 innodb_file_per_table 选项时,.ibd 文件就是每一个表独有的表空间,文件存储了当前表的数据和相关的索引数据。

哈希索引就是采用一定的哈希算法,把键值换算成新的哈希值,检索时不需要类似B+树那样从根节点到叶子节点逐级查找,只需一次哈希算法即可立刻定位到相应的位置,速度非常快

SERIALIZABLE:InnoDB 隐式地将全部的查询语句加上共享锁,解决了幻读的问题;

  • 定位到记录所在的页
    • 需要遍历双向链表,找到所在的页
  • 从所在的页内中查找相应的记录
    • 由于不是根据主键查询,只能遍历所在页的单链表了

但是事务还遵循包括原子性在内的 ACID 四大特性:原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)和持久性(Durability);文章不会对这四大特性全部展开进行介绍,相信你能够通过 Google 和数据库相关的书籍轻松获得有关它们的概念,本文最后要介绍的就是事务的四种隔离级别。

一般也就听过常说的乐观锁和悲观锁,了解过基本的含义之后就没了~~~

辅助索引的存在并不会影响聚集索引,因为聚集索引构成的 B+ 树是数据实际存储的形式,而辅助索引只用于加速数据的查找,所以一张表上往往有多个辅助索引以此来提升数据库的性能。

  • 意向共享锁:事务打算给数据行加行共享锁,事务在给一个数据行加共享锁前必须先取得该表的IS锁。
  • 意向排他锁:事务打算给数据行加行排他锁,事务在给一个数据行加排他锁前必须先取得该表的IX锁。
  • 意向锁也是数据库隐式帮我们做了,不需要程序员操心

一张表一定包含一个聚集索引构成的 B+ 树以及若干辅助索引的构成的 B+ 树。

想要了解更多与 InnoDB 存储引擎中记录的数据格式的相关信息,可以阅读 InnoDB Record Structure

  • 也就是说,InnoDB的行锁是基于索引的

既然 InnoDB 中实现的锁是悲观的,那么不同事务之间就可能会互相等待对方释放锁造成死锁,最终导致事务发生错误;想要在 MySQL 中制造死锁的问题其实非常容易:

  • 索引可以简单如一个列`,也可以复杂如多个列(a, b, c, d)`,即联合索引
  • 如果是联合索引,那么key也由多个列组成,同时,索引只能用于查找key是否存在,遇到范围查询(>、<、between、like左匹配)等就不能进一步匹配了,后续退化为线性查找。
  • 因此,列的排列顺序决定了可命中索引的列数

澳门新濠3559 12

2.3.2乐观锁

乐观锁不是数据库层面上的锁,是需要自己手动去加的锁。一般我们添加一个版本字段来实现:

具体过程是这样的:

张三select * from table --->会查询出记录出来,同时会有一个version字段

澳门新濠3559 13image

李四select * from table --->会查询出记录出来,同时会有一个version字段

澳门新濠3559 14image

李四对这条记录做修改:update A set Name=lisi,version=version+1 where ID=#{id} and version=#{version},判断之前查询到的version与现在的数据的version进行比较,同时会更新version字段

此时数据库记录如下:

澳门新濠3559 15image

张三也对这条记录修改:update A set Name=lisi,version=version+1 where ID=#{id} and version=#{version},但失败了!因为当前数据库中的版本跟查询出来的版本不一致

澳门新濠3559 16image

参考资料:

  • MVCC 的区别?

当我们用范围条件检索数据而不是相等条件检索数据,并请求共享或排他锁时,InnoDB会给符合范围条件的已有数据记录的索引项加锁;对于键值在条件范围内但并不存在的记录,叫做“间隙”。InnoDB也会对这个“间隙”加锁,这种锁机制就是所谓的间隙锁。

值得注意的是:间隙锁只会在Repeatable read隔离级别下使用~

例子:假如emp表中只有101条记录,其empid的值分别是1,2,...,100,101

Select * from emp where empid > 100 for update;

上面是一个范围查询,InnoDB不仅会对符合条件的empid值为101的记录加锁,也会对empid大于101的“间隙”加锁

InnoDB使用间隙锁的目的有两个:

  • 为了防止幻读(上面也说了,Repeatable read隔离级别下再通过GAP锁即可避免了幻读)
  • 满足恢复和复制的需要
    • MySQL的恢复机制要求:在一个事务未提交前,其他并发事务不能插入满足其锁定条件的任何记录,也就是不允许出现幻读

并发的问题就少不了死锁,在MySQL中同样会存在死锁的问题。

但一般来说MySQL通过回滚帮我们解决了不少死锁的问题了,但死锁是无法完全避免的,可以通过以下的经验参考,来尽可能少遇到死锁:

  • 1)以固定的顺序访问表和行。比如对两个job批量更新的情形,简单方法是对id列表先排序,后执行,这样就避免了交叉等待锁的情形;将两个事务的sql顺序调整为一致,也能避免死锁。
  • 2)大事务拆小。大事务更倾向于死锁,如果业务允许,将大事务拆小。
  • 3)在同一个事务中,尽可能做到一次锁定所需要的所有资源,减少死锁概率。
  • 4)降低隔离级别。如果业务允许,将隔离级别调低也是较好的选择,比如将隔离级别从RR调整为RC,可以避免掉很多因为gap锁造成的死锁。
  • 5)为表添加合理的索引。可以看到如果不走索引将会为表的每一行记录添加上锁,死锁的概率大大增大。

参考资料:

上面说了一大堆关于MySQL数据库锁的东西,现在来简单总结一下。

表锁其实我们程序员是很少关心它的:

  • 在MyISAM存储引擎中,当执行SQL语句的时候是自动加的。
  • 在InnoDB存储引擎中,如果没有使用索引,表锁也是自动加的。

现在我们大多数使用MySQL都是使用InnoDB,InnoDB支持行锁:

  • 共享锁--读锁--S锁
  • 排它锁--写锁--X锁

在默认的情况下,select是不加任何行锁的~事务可以通过以下语句显示给记录集加共享锁或排他锁。

  • 共享锁:SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
  • 排他锁:SELECT * FROM table_name WHERE ... FOR UPDATE

InnoDB基于行锁还实现了MVCC多版本并发控制,MVCC在隔离级别下的Read committedRepeatable read下工作。MVCC能够实现读写不阻塞

InnoDB实现的Repeatable read隔离级别配合GAP间隙锁已经避免了幻读!

  • 乐观锁其实是一种思想,正如其名:认为不会锁定的情况下去更新数据,如果发现不对劲,才不更新。在数据库中往往添加一个version字段来实现。
  • 悲观锁用的就是数据库的行锁,认为数据库会发生并发冲突,直接上来就把数据锁住,其他事务不能修改,直至提交了当前事务

参考资料:

  • )
  • InnoDB引擎锁的总结

本文主要介绍了数据库中的两个比较重要的知识点:索引和锁。他俩可以说息息相关的,锁会涉及到很多关于索引的知识~

我个人比较重视对整体知识点的把控,一些细节的地方可能就没有去编写了。在每一个知识点下都会有很多的内容,有兴趣的同学可以在我给出的链接中继续阅读学习。当然了,如果有比较好的文章和资料也不妨在评论区分享一下哈~

我只是在学习的过程中,把自己遇到的问题写出来,整理出来,希望可以对大家有帮助。如果文章有错的地方,希望大家可以在评论区指正,一起学习交流~

参考资料:

  • 《高性能MySQL 第三版》

如果文章有错的地方欢迎指正,大家互相交流。习惯在微信看技术文章,想要获取更多的Java资源的同学,可以关注微信公众号:Java3y。为了大家方便,刚新建了一下qq群:742919422,大家也可以去交流交流。谢谢支持了!希望能多介绍给其他有需要的朋友

文章的目录导航

最上层用于连接、线程处理的部分并不是 MySQL 『发明』的,很多服务都有类似的组成部分;第二层中包含了大多数 MySQL 的核心服务,包括了对 SQL 的解析、分析、优化和缓存等功能,存储过程、触发器和视图都是在这里实现的;而第三层就是 MySQL 中真正负责数据的存储和提取的存储引擎,例如:InnoDB、MyISAM等,文中对存储引擎的介绍都是对 InnoDB 实现的分析。

澳门新濠3559 17image

澳门新濠3559 18

  • 1,最左前缀匹配原则。这是非常重要、非常重要、非常重要的原则,MySQL会一直向右匹配直到遇到范围查询(>,<,BETWEEN,LIKE)就停止匹配。
  • 3,尽量选择区分度高的列作为索引,区分度的公式是 COUNT(DISTINCT col) / COUNT。表示字段不重复的比率,比率越大我们扫描的记录数就越少。
  • 4,索引列不能参与计算,尽量保持列“干净”。比如,FROM_UNIXTIME(create_time) = '2016-06-06' 就不能使用索引,原因很简单,B+树中存储的都是数据表中的字段值,但是进行检索时,需要把所有元素都应用函数才能比较,显然这样的代价太大。所以语句要写成 : create_time = UNIX_TIMESTAMP('2016-06-06')
  • 5,尽可能的扩展索引,不要新建立索引。比如表中已经有了a的索引,现在要加的索引,那么只需要修改原来的索引即可。
  • 6,单个多列组合索引和多个单列索引的检索查询效果不同,因为在执行SQL时,MySQL只能使用一个索引,会从多个单列索引中选择一个限制最为严格的索引。

澳门新濠3559 19

澳门新濠3559 20image

乐观锁不会存在死锁的问题,但是由于更新后验证,所以当冲突频率重试成本较高时更推荐使用悲观锁,而需要非常高的响应速度并且并发量非常大的时候使用乐观锁就能较好的解决问题,在这时使用悲观锁就可能出现严重的性能问题;在选择并发控制机制时,需要综合考虑上面的四个方面(冲突频率、重试成本、响应速度和并发量)进行选择。

  • 索引可以加快数据库的检索速度
  • 经常进行INSERT/UPDATE/DELETE操作就不要建立索引了,换言之:索引会降低插入、删除、修改等维护任务的速度。
  • 索引需要占物理和数据空间
  • 了解过索引的最左匹配原则
  • 知道索引的分类:聚集索引和非聚集索引
  • Mysql支持Hash索引和B+树索引两种

本文又是一篇转载,原文出处: 『浅入浅出』MySQL 和 InnoDB

参考资料:

澳门新濠3559 21

所以说,如果我们写select * from user where username = 'Java3y'这样没有进行任何优化的sql语句,默认会这样做:

乐观锁是一种思想,它其实并不是一种真正的『锁』,它会先尝试对资源进行修改,在写回时判断资源是否进行了改变,如果没有发生改变就会写回,否则就会进行重试,在整个的执行过程中其实都没有对数据库进行加锁

非聚集索引在建立的时候也未必是单列的,可以多个列来创建索引。

澳门新濠3559 22

  • 聚集索引就是以主键创建的索引
  • 非聚集索引就是以非主键创建的索引

在介绍了锁之后,我们再来谈谈数据库中一个非常重要的概念 —— 事务;相信只要是一个合格的软件工程师就对事务的特性有所了解,其中被人经常提起的就是事务的原子性,在数据提交工作时,要么保证所有的修改都能够提交,要么就所有的修改全部回滚。

澳门新濠3559 23

定心丸:即使我们不会这些锁知识,我们的程序在一般情况下还是可以跑得好好的。因为这些锁数据库隐式帮我们加了

如果在表 users 中存在一个辅助索引 (first_name, age),那么它构成的 B+ 树大致就是上图这样,按照 (first_name, age) 的字母顺序对表中的数据进行排序,当查找到主键时,再通过聚集索引获取到整条行记录。

简单概括:

虽然间隙锁中也分为共享锁和互斥锁,不过它们之间并不是互斥的,也就是不同的事务可以同时持有一段相同范围的共享锁和互斥锁,它唯一阻止的就是其他事务向这个范围中添加新的记录

澳门新濠3559 24image

我们都知道锁的种类一般分为乐观锁和悲观锁两种,InnoDB 存储引擎中使用的就是悲观锁,而按照锁的粒度划分,也可以分成行锁和表锁。

另外,为了允许行锁和表锁共存,实现多粒度锁机制,InnoDB还有两种内部使用的意向锁(Intention Locks),这两种意向锁都是表锁

几种隔离级别

  • 表读锁(Table Read Lock)
  • 表写锁(Table Write Lock)
  • 从下图可以清晰看到,在表读锁和表写锁的环境下:读读不阻塞,读写阻塞,写写阻塞
    • 读读不阻塞:当前用户在读数据,其他的用户也在读数据,不会加锁
    • 读写阻塞:当前用户在读数据,其他的用户不能修改当前用户读的数据,会加锁!
    • 写写阻塞:当前用户在修改数据,其他的用户不能修改当前用户正在修改的数据,会加锁!

澳门新濠3559 25

值得注意的是:

文章中的内容大都来自于 高性能 MySQL、MySQL 技术内幕:InnoDB 存储引擎、数据库索引设计与优化以及 MySQL 的 官方文档。

本文力求简单讲清每个知识点,希望大家看完能有所收获

澳门新濠3559 26

B+树是平衡树的一种。

澳门新濠3559 27

最左匹配原则

CREATETABLEusers(idINTNOTNULL,first_nameVARCHAR(20)NOTNULL,last_nameVARCHAR(20)NOTNULL,ageINTNOTNULL,PRIMARYKEY(id),KEY(last_name,first_name,age)KEY(first_name));

平衡树:它是一棵空树或它的左右两个子树的高度差的绝对值不超过1,并且左右两个子树都是一棵平衡二叉树。

RAED UNCOMMITED:使用查询语句不会加锁,可能会读到未提交的行(Dirty Read);

  • 哈希索引也没办法利用索引完成排序
  • 不支持最左匹配原则
  • 在有大量重复键值情况下,哈希索引的效率也是极低的---->哈希碰撞问题。
  • 不支持范围查询

在 Unix 上,启动一个 MySQL 实例往往会产生两个进程,mysqld 就是真正的数据库服务守护进程,而 mysqld_safe 是一个用于检查和设置 mysqld 启动的控制程序,它负责监控 MySQL 进程的执行,当 mysqld发生错误时,mysqld_safe 会对其状态进行检查并在合适的条件下重启。

InnoDB实现了以下两种类型的行锁。

澳门新濠3559 28

Intention locks do not block anything except full table requests (for example, LOCK TABLES ... WRITE). The main purpose of intention locks is to show that someone is locking a row, or going to lock a row in the table.

每一个页中包含了两对 header/trailer:内部的 Page Header/Page Directory 关心的是页的状态信息,而 Fil Header/Fil Trailer 关心的是记录页的头信息。

首先,从锁的粒度,我们可以分成两大类:

澳门新濠3559 29

  • 各个数据页可以组成一个双向链表
  • 每个数据页中的记录又可以组成一个单向链表
    • 每个数据页都会为存储在它里边儿的记录生成一个页目录,在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽,然后再遍历该槽对应分组中的记录即可快速找到指定的记录
    • 其他列作为搜索条件:只能从最小记录开始依次遍历单链表中的每条记录

Next-Key Lock

参考资料:

当然在实际存储中,可能会对不同长度的 TEXT 和 BLOB 列进行优化,不过这就不是本文关注的重点了。

The LOCAL modifier enables nonconflicting INSERT statements (concurrent inserts) by other sessions to execute while the lock is held. (See Section 8.11.3, “Concurrent Inserts”.) However, READ LOCAL cannot be used if you are going to manipulate the database using processes external to the server while you hold the lock. For InnoDB tables, READ LOCAL is the same as READ

对于数据库和实例的定义都来自于 MySQL 技术内幕:InnoDB 存储引擎 一书,想要了解 InnoDB 存储引擎的读者可以阅读这本书籍。

很明显的是:没有用索引我们是需要遍历双向链表来定位对应的页,现在通过“目录”就可以很快地定位到对应的页上了!

澳门新濠3559 30

参考资料:

MySQL 使用 InnoDB 存储表时,会将表的定义数据索引等信息分开存储,其中前者存储在 .frm 文件中,后者存储在 .ibd 文件中,这一节就会对这两种不同的文件分别进行介绍。

不少人在开发的时候,应该很少会注意到这些锁的问题,也很少会给程序加锁(除了库存这些对数量准确性要求极高的情况下)

接下来,我们将数据库中创建如下的表并通过个例子来展示在不同的事务隔离级别之下,会发生什么样的问题:

InnoDB只有通过索引条件检索数据才使用行级锁,否则,InnoDB将使用表锁

锁的算法

从上面已经看到了:读锁和写锁是互斥的,读写操作是串行

记录锁(Record Lock)是加到索引记录上的锁,假设我们存在下面的一张表 users:

  • 如果某个进程想要获取读锁,同时另外一个进程想要获取写锁。在mysql里边,写锁是优先于读锁的
  • 写锁和读锁优先级的问题是可以通过参数调节的:max_write_lock_countlow-priority-updates

在标准的事务隔离级别中,幻读是由更高的隔离级别 SERIALIZABLE 解决的,但是它也可以通过 MySQL 提供的 Next-Key 锁解决:

其实底层结构就是B+树,B+树作为树的一种实现,能够让我们很快地查找出对应的记录。

聚集索引与表的物理存储方式有着非常密切的关系,所有正常的表应该有且仅有一个聚集索引(绝大多数情况下都是主键),表中的所有行记录数据都是按照聚集索引的顺序存放的。

在mysql中的锁看起来是很复杂的,因为有一大堆的东西和名词:排它锁,共享锁,表锁,页锁,间隙锁,意向排它锁,意向共享锁,行锁,读锁,写锁,乐观锁,悲观锁,死锁。这些名词有的博客又直接写锁的英文的简写--->X锁,S锁,IS锁,IX锁,MMVC...

事务的隔离性是数据库处理数据的几大基础之一,而隔离级别其实就是提供给用户用于在性能和可靠性做出选择和权衡的配置项。

锁的相关知识又跟存储引擎,索引,事务的隔离级别都是关联的....

乐观锁和悲观锁其实都是并发控制的机制,同时它们在原理上就有着本质的差别;

B+树是平衡树的一种,是不会退化成链表的,树的高度都是相对比较低的(基本符合矮矮胖胖的结构)【这样一来我们检索的时间复杂度就是O!从上一节的图我们也可以看见,建立索引实际上就是建立一颗B+树。

数据库中的 B+ 树索引可以分为聚集索引(clustered index)和辅助索引(secondary index),它们之间的最大区别就是,聚集索引中存放着一条行记录的全部信息,而辅助索引中只包含索引列和一个用于查找对应行记录的『书签』。

  • )

但是当我们使用新的行记录格式 Compressed 或者 Dynamic 时都只会在行记录中保存 20 个字节的指针,实际的数据都会存放在溢出页面中。

上边简单讲解了表锁的相关知识,我们使用Mysql一般是使用InnoDB存储引擎的。InnoDB和MyISAM有两个本质的区别:

当事务的隔离级别为 READ UNCOMMITED 时,我们在 SESSION 2 中插入的未提交数据在 SESSION 1 中是可以访问的。

索引做了些什么可以让我们查询加快速度呢?

澳门新濠3559 31

除了B+树之外,还有一种常见的是哈希索引。

如果使用 Next-Key 锁,那么 Next-Key 锁就可以在需要的时候锁定以下的范围:

例子:

总结

不需要考虑=、in等的顺序,mysql会自动优化这些条件的顺序,以匹配尽可能多的索引列。

两种行记录格式 Compact 和 Redundant 在磁盘上按照以下方式存储:

这就给初学数据库锁的人带来不少的麻烦~~~于是我下面就简单整理一下数据库锁的知识点,希望大家看完会有所帮助。

上图展示了一个使用辅助索引查找一条表记录的过程:通过辅助索引查找到对应的主键,最后在聚集索引中使用主键获取对应的行记录,这也是通常情况下行记录的查找方式。

看完上面的有没有发现,在一开始所说的:X锁,S锁,读锁,写锁,共享锁,排它锁其实总共就两个锁,只不过它们有多个名字罢了~~~

CREATETABLEtest(idINTNOTNULL,UNIQUE(id));

  • 如有索引(a, b, c, d),查询条件c > 3 and b = 2 and a = 1 and d < 4a = 1 and c > 3 and b = 2 and d < 4等顺序都是可以的,MySQL会自动优化为a = 1 and b = 2 and c > 3 and d < 4,依次命中a、b、c。

Next-Key 锁的作用其实是为了解决幻读的问题,我们会在下一节谈事务的时候具体介绍。

要找到id为8的记录简要步骤:

Antelope 是 InnoDB 最开始支持的文件格式,它包含两种行格式 Compact 和 Redundant,它最开始并没有名字;Antelope 的名字是在新的文件格式 Barracuda 出现后才起的,Barracuda 的出现引入了两种新的行格式 Compressed 和 Dynamic;InnoDB 对于文件格式都会向前兼容,而官方文档中也对之后会出现的新文件格式预先定义好了名字:Cheetah、Dragon、Elk 等等。

  • 现在我创建了索引(username,age),在查询数据的时候:select username , age from user where username = 'Java3y' and age = 20
  • 很明显地知道,我们上边的查询是走索引的,并且,要查询出的列在叶子节点都存在!所以,就不用回表了~
  • 所以,能使用覆盖索引就尽量使用吧~

MySQL 官方文档中的 11.1 MySQL .frm File Format 一文对于 .frm 文件格式中的二进制的内容有着非常详细的表述,在这里就不展开介绍了。

很明显,在数据量很大的情况下这样查找会很慢

索引是数据库中非常非常重要的概念,它是存储引擎能够快速定位记录的秘密武器,对于提升数据库的性能、减轻数据库服务器的负担有着非常重要的作用;索引优化是对查询性能优化的最有效手段,它能够轻松地将查询的性能提高几个数量级。

在上一节中,我们谈了行记录的存储和页的存储,在这里我们就要从更高的层面看 InnoDB 中对于数据是如何存储的;InnoDB 存储引擎在绝大多数情况下使用 B+ 树建立索引,这是关系型数据库中查找最为常用和有效的索引,但是 B+ 树索引并不能找到一个给定键对应的具体值,它只能找到数据行对应的页,然后正如上一节所提到的,数据库把整个页读入到内存中,并在内存中查找具体的数据行。

  • 能让我们在特定的场景下派得上用场
  • 更好把控自己写的程序
  • 在跟别人聊数据库技术的时候可以搭上几句话
  • 构建自己的知识库体系!在面试的时候不虚

澳门新濠3559 32

参考资料:

索引的数据结构

例子:

澳门新濠3559 33

其实就是将无序的数据变成有序

对数据的操作其实只有两种,也就是读和写,而数据库在实现锁时,也会对这两种操作使用不同的锁;InnoDB 实现了标准的行级锁,也就是共享锁(Shared Lock)和互斥锁(Exclusive Lock);共享锁和互斥锁的作用其实非常好理解:

比如说:

InnoDB 存储引擎中的表都是使用索引组织的,也就是按照键的顺序存放;聚集索引就是按照表中主键的顺序构建一颗 B+ 树,并在叶节点中存放表中的行记录数据。

  • InnoDB支持行锁
  • InnoDB支持事务

幻读

看起来好像啥都知道,但面试让你说的时候可能就GG了:

有的人可能会对意向锁的目的并不是完全的理解,我们在这里可以举一个例子:如果没有意向锁,当已经有人使用行锁对表中的某一行进行修改时,如果另外一个请求要对全表进行修改,那么就需要对所有的行是否被锁定进行扫描,在这种情况下,效率是非常低的;不过,在引入意向锁之后,当有人使用行锁对表中的某一行进行修改之前,会先为表添加意向互斥锁(IX),再为行记录添加互斥锁(X),在这时如果有人尝试对全表进行修改就不需要判断表中的每一行数据是否被加锁了,只需要通过等待意向互斥锁被释放就可以了。

主流的还是使用B+树索引比较多,对于哈希索引,InnoDB是自适应哈希索引的(hash索引的创建由InnoDB存储引擎引擎自动优化创建,我们干预不了)!

当我们使用上面的代码创建表时,会在磁盘上的 datadir 文件夹中生成一个 test_frm.frm 的文件,这个文件中就包含了表结构相关的信息:

  • 使用索引为什么可以加快数据库的检索速度啊?
  • 为什么说索引会降低插入、删除、修改等维护任务的速度。
  • 索引的最左匹配原则指的是什么?
  • Hash索引和B+树索引有什么区别?主流的使用哪一个比较多?InnoDB存储都支持吗?
  • 聚集索引和非聚集索引有什么区别?
  • ........

当事务的隔离级别为 READ COMMITED 时,虽然解决了脏读的问题,但是如果在 SESSION 1 先查询了一个范围的数据,在这之后 SESSION 2 中插入一条数据并且提交了修改,在这时,如果 SESSION 1 中再次使用相同的查询语句,就会发现两次查询的结果不一样。

在之前,我对索引有以下的认知:

互斥锁(写锁):允许事务对一条行数据进行删除或更新;

意向锁其实不会阻塞全表扫描之外的任何请求,它们的主要目的是为了表示是否有人请求锁定表中的某一行数据

澳门新濠3559 34

+------|-------------|--------------|-------+|id|last_name|first_name|age||------|-------------|--------------|-------||4|stark|tony|21||1|tom|hiddleston|30||3|morgan|freeman|40||5|jeff|dean|50||2|donald|trump|80|+------|-------------|--------------|-------+

数据库的定义

CREATETABLEusers(idINTNOTNULLAUTO_INCREMENT,last_nameVARCHAR(255)NOTNULL,first_nameVARCHAR(255),ageINT,PRIMARYKEY(id),KEY(last_name),KEY(age));

图中对 B+ 树的描述与真实情况下 B+ 树中的数据结构有一些差别,不过这里想要表达的主要意思是:聚集索引叶节点中保存的是整条行记录,而不是其中的一部分。

无论是共享锁还是互斥锁其实都只是对某一个数据行进行加锁,InnoDB 支持多种粒度的锁,也就是行锁和表锁;为了支持多粒度锁定,InnoDB 存储引擎引入了意向锁(Intention Lock),意向锁就是一种表级锁。

不可重复读的原因就是,在 READ COMMITED 的隔离级别下,存储引擎不会在查询记录时添加间隙锁,锁定 id < 5 这个范围。

Gap Lock

页是 InnoDB 存储引擎管理数据的最小磁盘单位,而 B-Tree 节点就是实际存放表中数据的页面,我们在这里将要介绍页是如何组织和存储记录的;首先,一个 InnoDB 页有以下七个部分:

索引

随着意向锁的加入,锁类型之间的兼容矩阵也变得愈加复杂:

User Records 就是整个页面中真正用于存放行记录的部分,而 Free Space 就是空余空间了,它是一个链表的数据结构,为了保证插入和删除的效率,整个页面并不会按照主键顺序对所有记录进行排序,它会自动从左侧向右寻找空白节点进行插入,行记录在物理存储上并不是按照顺序的,它们之间的顺序是由 next_record 这一指针控制的。

READ COMMITED:只对记录加记录锁,而不会在记录之间加间隙锁,所以允许新的记录插入到被锁定记录的附近,所以再多次使用查询语句时,可能得到不同的结果(Non-Repeatable Read);

而它们的名字也暗示着各自的另外一个特性,共享锁之间是兼容的,而互斥锁与其他任意锁都不兼容:

与上一节中提到的两种锁的种类相似的是,意向锁也分为两种:

稍微对它们的使用进行思考就能想明白它们为什么要这么设计,因为共享锁代表了读操作、互斥锁代表了写操作,所以我们可以在数据库中并行读,但是只能串行写,只有这样才能保证不会发生线程竞争,实现线程安全。

作为一名开发人员,在日常的工作中会难以避免地接触到数据库,无论是基于文件的 sqlite 还是工程上使用非常广泛的 MySQL、PostgreSQL,但是一直以来也没有对数据库有一个非常清晰并且成体系的认知,所以最近两个月的时间看了几本数据库相关的书籍并且阅读了 MySQL 的官方文档,希望对各位了解数据库的、不了解数据库的有所帮助。

.ibd 文件

很多开发者在最开始时其实都对数据库有一个比较模糊的认识,觉得数据库就是一堆数据的集合,但是实际却比这复杂的多,数据库领域中有两个词非常容易混淆,也就是数据库实例

澳门新濠3559 35

虽然乐观锁和悲观锁在本质上并不是同一种东西,一个是一种思想,另一个是一种真正的锁,但是它们都是一种并发控制机制。

聚集索引

(-∞,21](21,30](30,40](40,50](50,80](80,∞)

索引的设计

.frm 文件

间隙锁是存储引擎对于性能和并发做出的权衡,并且只用于某些事务隔离级别。

澳门新濠3559 36

ISO 和 ANIS SQL 标准制定了四种事务隔离级别,而 InnoDB 遵循了 SQL:1992 标准中的四种隔离级别:READ UNCOMMITED、READ COMMITED、REPEATABLE READ 和 SERIALIZABLE;每个事务的隔离级别其实都比上一级多解决了一个问题:

InnoDB 存储引擎中对数据的存储是一个非常复杂的话题,这一节中也只是对表、行记录以及页面的存储进行一定的分析和介绍,虽然作者相信这部分知识对于大部分开发者已经足够了,但是想要真正消化这部分内容还需要很多的努力和实践。

数据的存储

MySQL 中默认的事务隔离级别就是 REPEATABLE READ,但是它通过 Next-Key 锁也能够在某种程度上解决幻读的问题。

悲观锁就是一种真正的锁了,它会在获取资源前对资源进行加锁,确保同一时刻只有有限的线程能够访问该资源,其他想要尝试获取资源的操作都会进入等待状态,直到该线程完成了对资源的操作并且释放了锁后,其他线程才能重新操作资源;

两个会话都持有一个锁,并且尝试获取对方的锁时就会发生死锁,不过 MySQL 也能在发生死锁时及时发现问题,并保证其中的一个事务能够正常工作,这对我们来说也是一个好消息。

意向互斥锁:事务想要在获得表中某些记录的互斥锁,需要在表上先加意向互斥锁;

在 MySQL 中,实例和数据库往往都是一一对应的,而我们也无法直接操作数据库,而是要通过数据库实例来操作数据库文件,可以理解为数据库实例是数据库为上层提供的一个专门用于操作的接口。

实例:MySQL 数据库由后台线程以及一个共享内存区组成;

如果使用上面的 SQL 在数据库中创建一张表,B+ 树就会使用 id 作为索引的键,并在叶子节点中存储一条记录中的所有信息。

与现有的大多数存储引擎一样,InnoDB 使用页作为磁盘管理的最小单位;数据在 InnoDB 存储引擎中都是按行存储的,每个 16KB 大小的页中可以存放 2-200 行的记录。

数据页结构

澳门新濠3559 37

B+ 树是平衡树,它查找任意节点所耗费的时间都是完全相同的,比较的次数就是 B+ 树的高度;在这里,我们并不会深入分析或者动手实现一个 B+ 树,只是对它的特性进行简单的介绍。

锁的种类

数据库和实例

在 InnoDB 存储引擎中,所有的数据都被逻辑地存放在表空间中,表空间(tablespace)是存储引擎中最高的存储逻辑单位,在表空间的下面又包括段(segment)、区(extent)、页(page):

B+ 树在查找对应的记录时,并不会直接从树中找出对应的行记录,它只能获取记录所在的页,将整个页加载到内存中,再通过 Page Directory 中存储的稀疏索引和 n_owned、next_record 属性取出对应的记录,不过因为这一操作是在内存中进行的,所以通常会忽略这部分查找的耗时。

澳门新濠3559 38

从图中可以看出,在 InnoDB 存储引擎中,一个区的大小最小为 1MB,页的数量最少为 64 个。

澳门新濠3559 39

CREATETABLEtest_frm(column1 CHAR(5),column2INTEGER);

编辑:数据库 本文来源:在面试中也会经常会被问到的,搜索相关资料后

关键词: