数据库同步工具
sqlserver,Mysql数据同步软件

【MySQL】数据库两大神器【索引和锁】

购买事宜请联系QQ:1793040

目录

I.索引

1.1谈谈索引编制的基础

1.2索引提高了检索速度

1.3索引降低了添加,删除和修改的速度

1.4哈希索引

1.5InnoDB是否支持哈希索引?

1.6聚集索引和非聚集索引

1.7最左边的索引匹配原理

1.8 =,按自动优化顺序

1.9索引摘要

其次,锁定

2.1为什么需要学习数据库锁知识

2.2表锁简介

2.2行锁定详细信息

2.2.1 MVCC和事务隔离级别

2.3乐观锁和悲观锁

2.3.1悲观锁

2.3.2乐观锁

2.4间隙锁定GAP

2.5死锁

2.6锁定摘要

三,摘要


索引和锁定可以说是数据库中非常重要的知识点,并且经常在访谈中被问到。

本文力求简短地阐明每个知识点,希望每个人在阅读本书后都能有所收获

语句:如果未指定特定的数据库和存储引擎,则默认是指MySQL中的InnoDB存储引擎


在此之前,我对索引有以下了解:

  • 索引可以加快数据库的检索速度
  • 经常请勿创建操作索引。换句话说:索引将降低维护任务的速度,例如插入,删除和修改。
  • 索引需要占用物理和数据空间
  • 了解索引的最左侧匹配原则
  • 知道索引的分类:聚集索引和非聚集索引
  • Mysql同时支持哈希索引和B +树索引

似乎什么都知道,但是当面试让你说可能是GG:

  • 为什么使用索引可以加快数据库的检索速度?
  • 为什么索引会降低维护任务的速度,例如插入,删除和修改。
  • 索引最左边的匹配原理是什么?
  • 哈希索引与B +树索引之间有什么区别?哪个更常用?是否支持InnoDB存储?
  • 聚集索引和非聚集索引有什么区别?
  • ……..

首先,Mysql的基本存储结构是页面(记录存储在页面中):

  • 每个数据页可以形成双向链接列表
  • 每个数据页中的记录可以形成单向链接列表

    • 每个数据页面都会为其中存储的记录生成页面目录。通过主键搜索特定记录时,可以转到页面使用二分法在目录中快速找到到相应的插槽,然后遍历记录在相应的插槽组中快速找到指定的记录
    • 使用其他列(非主键)作为搜索条件:您只能从最小的记录开始依次遍历单链接列表中的每个记录

因此,如果我们编写没有任何优化的SQL语句,则默认情况下它将这样做:

  • 转到记录所在的页面

    • 您需要遍历双向链表才能找到它所在的页面
  • 从您所在的页面中找到相应的记录

    • 由于查询不是基于主键的,因此只能遍历查询所在页面的单链接列表

显然,当数据量很大时,此搜索将非常慢

索引如何使查询更快?

实际上,这是将无序数据转变为有序(相对):

查找ID为8的记录的简短步骤:

显然:没有索引我们需要遍历双向链接列表来找到相应的页面,现在它可以快速通过**”目录” **来对应页面!

实际上,底层结构是 B +树。作为树的一种实现,B +树允许我们快速找出相应的记录。

参考:

  • Mysql索引

B +树是平衡树的一种。

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

如果普通树处于极限以下,则可以退化为链接列表(不再存在树的优点)

B +树是一种平衡树,它不会退化为链表。树的高度相对较低(基本上符合矮胖(平衡)的结构)这样,我们检索的时间复杂度为O(登录)]!从上一节的图中我们还可以看到,创建索引实际上是在创建B +树。

  • B +树是平衡树。如果我们添加,删除或修改此树,则肯定会破坏其原始结构
  • 为了保持平衡的树,必须完成其他工作。由于这些附加任务开销,索引将降低添加,删除和修改的速度

B +树的删除和修改可以参考:

  • www.cnblogs.com/wade-luffy/…

除了B +树之外,还有一个公共哈希索引。

哈希索引是使用某种哈希算法将键值转换为新的哈希值,并且不需要像B +树那样从根节点搜索到叶节点搜索只需一种哈希算法即可非常快速地立即定位到相应位置

  • 本质上,它是根据要定位的哈希值将键值转换为新的哈希值

哈希索引似乎非常强大,但是实际上,哈希索引有几个限制(根据他的基本原理可以使用):

  • 无法使用哈希索引完成排序
  • 不支持最左边的匹配原则
  • 在大量重复的键值的情况下,哈希索引的效率也极低。 哈希冲突问题。
  • 不支持范围查询

参考资料:

  • www.cnblogs.com/zengkefu/p/…—哈希索引和b +树索引

主流仍然使用 B +树索引更多,对于哈希索引, InnoDB是自适应哈希索引(哈希索引由InnoDB存储引擎自动优化创建,我们无法干预)!

参考:

  • blog.csdn.net/doctor_who2…

简单总结:

  • 聚集索引是使用主键
  • 创建的索引

  • 非聚集索引是使用非主键
  • 创建的索引

区别:

  • 聚集索引将数据存储在叶节点的表中
  • 非聚集索引将主键和索引列存储在叶节点
  • 使用非聚集索引查询数据时,获取叶子上的主键并找到要查找的数据。 (获取主键,然后搜索此过程称为返回表

非聚集索引也称为二级索引,您不必为那么多名词而烦恼,只需使其等效即可

创建非聚集索引时,它们也不一定是单列,并且多列可用于创建索引。

  • 至此,它涉及到哪个列将被索引而哪个列将不被索引的问题(最左边的匹配原理->稍后再说)
  • 在创建多个单列(非聚集)索引时,将生成多个索引树(索引创建过多会占用磁盘空间)

在创建多列索引时还涉及特殊索引。 封面索引

  • 我们早先了解到,如果它不是聚簇索引,则叶节点将存储主键+列值
  • 最后,您仍然必须”返回表”,也就是说,您需要通过主键再次搜索 。这会比较慢
  • 覆盖索引意味着要查询的列与索引对应,而无需返回表!

例如:

  • 现在我在查询数据时创建了一个索引:
  • 显然,我们上面的查询已建立索引,并且要查询的列都存在于叶节点中!因此,无需返回表
  • 因此,如果可以使用覆盖率索引,请尝试尽可能多地使用它

最左边的匹配原理:

  • 索引可以简单到一列,也可以复杂到多列,即联合索引
  • 如果它是联合索引,则键也由多列组成。同时,索引只能用于查找键是否存在(等于)(如果遇到范围查询左匹配项)等。不再匹配,然后回归线性搜索。
  • 因此,列顺序确定可以命中索引的列数。

示例:

  • 如果存在索引和查询条件,它将依次击中每个节点中的a,b和c,而无法击中d。 (非常简单:索引命中只能是等于,而不是范围匹配)

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

示例:

  • 如果有索引,则查询条件和顺序都是可能的。 MySQL将自动优化以依次命中a,b和c。

索引是数据库中的非常重要知识点!我上面所说的实际上是基本索引。要创建一个良好的索引,需要考虑许多方面:

  • 1,最左边的前缀匹配原则。这是一个非常重要,非常重要,非常重要的原则(三件事要说三遍),MySQL始终会向右匹配,直到遇到范围查询时停止匹配为止。
  • 3.尝试选择高度区分的列作为索引。区分度的公式是。指示不重复字段的速率。速率越大,我们扫描的记录越少。
  • 4.索引列不能参与计算,请尝试使列保持”干净” 。例如不能使用索引,原因很简单, B +树将字段值存储在数据表中,但是在搜索时,需要将所有元素应用于该函数以比较一下,显然这个价格太高了。因此,该语句应写为:。
  • 5.尽可能扩展索引,不要创建新索引。例如,表中已经存在a的索引,现在需要添加(a,b)的索引,那么仅需要修改原始索引。
  • 6.单个多列复合索引和多个单列索引的搜索查询效果是不同的,因为执行SQL时, MySQL只能使用一个索引,它将从中选择一个多个单列索引限制性最强的索引。

参考资料:

  • zhuanlan.zhihu.com/p/23624390-简单理解索引
  • blog.csdn.net/mysteryhaoh…-MySQL学习索引(通用索引,唯一索引,全文索引,索引匹配原理,索引寿命)
  • monkeysayhi.github.io/2018/03/06/…-谈论MySQL的B树索引和索引优化

锁定mysql 外观很复杂,因为有许多事物和名词:排他锁,共享锁,表锁,页面锁,间隙锁,意图排他锁,意图共享锁,行锁,读取锁,写锁,乐观锁,悲观锁,死锁。这些名词中的某些博客直接写出英文缩写的锁—> X锁,S锁,IS锁,IX锁,MMVC…

有关锁的知识还与存储引擎,索引和事务隔离级别有关。…

这对于数据库锁的新手来说会造成很多麻烦~~~因此,我将在下面简要地整理一下数据库锁的知识点,希望阅读后对大家有所帮助。

开发时,许多人应该很少注意到这些锁定的问题,并且很少锁定程序(除了 inven(当精度极高时)

通常,我听说过常说的乐观锁和悲观锁。在理解了基本含义之后,它们就消失了~~~

定心丸:即使我们不知道这些锁,我们的程序在一般情况下仍然可以很好地运行。因为这些锁数据库隐式帮助我们添加了

  • 对于语句, InnoDB 将自动 向所涉及的数据集添加排他锁(X)
  • MyISAM 在执行查询语句之前,自动在执行更新操作(等)之前,向所有涉及的表添加读锁。 ,自动到相关表中添加写锁,此过程不需要用户干预

仅在某些特定情况下,需要手动锁定,学习数据库锁定知识的目的是:

  • 在某些情况下将为我们提供帮助
  • 更好的控制自己的程序
  • 与其他人讨论数据库技术时,您可能会说几句话
  • 构建自己的知识库系统!面试中不要错过

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

  • 表锁

    • 低开销,快速锁定;没有僵局;强锁定,高锁定冲突可能性和最低并发性
  • 线路锁定

    • 开销大且锁定缓慢;死锁将发生;锁粒度小,锁冲突可能性低和并发性高

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

  • 支持InnoDB行和表锁
  • MyISAM仅支持表锁

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

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

表锁分为两种模式:

  • 表读取锁
  • 表写锁
  • 从下图可以清楚地看到,在表读取锁定和表写入锁定的环境下:读取和读取未阻塞,读取和写入被阻塞,写入和写入被阻塞

    • 读取和读取不受阻碍:当前用户正在读取数据,其他用户也在读取数据,并且不会被锁定
    • 读写阻止:当前用户正在读取数据,其他用户无法修改当前用户读取的数据,它将被锁定!
    • 写阻止:当前用户正在修改数据,其他用户无法修改当前用户正在修改的数据,它将被锁定!

从上面可以看到:读锁和写锁是互斥的,读和写操作是串行的

  • 如果一个进程想要获取读锁定,同时另一个进程想要获取写锁定。在mysql中,写锁优先于读锁
  • 写入锁定和读取锁定的优先级可以通过以下参数进行调整:和

值得注意的是:

LOCAL修饰符使其他会话可以在保持锁的同时执行无冲突的INSERT语句(并发插入)。 (请参见第8.11.3节”并发插入”。)但是,如果您要在持有锁的同时使用服务器外部的进程来操作数据库,则无法使用READ LOCAL。 对于InnoDB表,READ LOCAL与READ

相同。

  • MyISAM可以支持并发查询和插入操作。您可以通过系统变量指定哪种模式。在 MyISAM 中,默认值为:如果MyISAM表中没有孔(也就是说,表中间没有删除的行),则MyISAM允许在进程中读取表。时间,另一个进程将从页脚插入记录。
  • 但是不支持InnoDB存储引擎

参考资料:

  • dev.mysql.com/doc/refman/…–官方手册
  • ourmysql.com/archives/56…—几个参数说明

上面简要解释了表锁的知识。当使用Mysql时,我们通常使用InnoDB存储引擎。 InnoDB和MyISAM之间有两个本质区别:

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

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

我们应该多注意行锁的内容,因为InnoDB的主要功能之一就是支持行锁!

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

  • 共享锁(S锁):允许一个事务读取一行,从而防止其他事务获得同一数据集的排他锁。

    • 也称为读锁:读锁是共享,多个客户可以同时读取同一资源,但不允许其他客户修改
  • 排他锁(X锁):允许获取排他锁的事务更新数据,并防止其他事务获取同一数据集的共享读取锁和排他写入锁。

    • 也称为写锁:写锁是排他的。 写锁将阻止其他写锁和读锁

阅读以上内容后,您是否一开始就发现:X锁,S锁,读取锁,写入锁,共享锁,排他锁?实际上,总共有/strong>个锁,但是它们有多个名称 ~~~

除了全表请求(例如LOCK TABLES…WRITE)之外,意图锁不会阻止任何其他内容。意向锁的主要目的是表明某人正在锁定一行,或者将要锁定表中的一行

另外,为了使行锁和表锁可以共存并实现多粒度锁机制,InnoDB还具有两个内部意图锁(Intent Locks),它们都表锁:

  • 意向共享锁(IS):事务打算将行共享锁添加到数据行。在将共享锁添加到数据行之前,事务必须首先获取表的IS锁。
  • 意向排他锁(IX):事务打算向数据行添加排他锁。事务必须先获取表的IX锁,然后才能向数据行添加排他锁。
  • 有意锁定也由数据库隐式完成,程序员不必担心

参考资料:

  • www.zhihu.com/question/51…
  • dev.mysql.com/doc/refman/…

数据库事务具有不同的隔离级别,并且不同的隔离级别使用锁的方式也不同。 锁的应用最终导致不同的事务隔离级别

MVCC(多版本并发控制)多版本并发控制,您可以简单地想到: MVCC是行级锁(升级版)的一种

  • 事务的隔离级别是通过锁定机制实现的,除了锁定的详细信息被隐藏

表锁中,我们读写被阻止,基于提高并发性能的考虑, MVCC通常不会被阻止(可以这么说(MVCC在许多情况下避免了锁定操作)

MVCC顾名思义,是

  • 读写不阻塞:多版本并发控制—>通过某种机制生成数据请求时间点一致性数据快照(Snapshot),并使用该快照提供特定级别(语句级别或事务级别)的一致性读取从用户的角度来看,数据库似乎可以提供同一数据的多个版本

快照具有两个级别:

  • 语句级别

    • 用于隔离级别
  • 交易级别

    • 用于隔离级别

当我们初次了解到有 4种类型的事务隔离级别时,我们已经知道:

  • 读未提交

    • 会发生脏读,不可重复读,幻像读
  • 读已提交

    • 不可重复阅读,将会出现幻像阅读
  • 可重复读取

    • 将会发生魔术读取(但是在Mysql中使用间隙锁定实现的可重复读取不会出现魔术读取!)
  • 可序列化

    • 串行,请避免上述情况!

将会发生的现象—\ gt;脏读:一笔交易读取另一笔交易的未提交数据

  • 示例:A将钱转移到B。 A执行转移语句,但是A尚未提交交易。 B读取数据,发现他的帐户有更多钱! B告诉A我已经收到了钱。 A回滚事务[rollback]。当B再次检查帐户中的钱时,他发现没有多少钱。
  • 脏读取的本质是因为数据的操作(修改)会立即释放锁,从而导致读取的数据变得无用或错误的数据强>。

避免脏读实际上非常简单:

  • 提交事务后,将调整释放锁的位置。此时,在提交事务之前,其他进程无法读取数据行,包括任何操作。 li>

但是,现象—>不可重复读取:一笔交易读取了另一笔交易提交的数据,这意味着一笔交易可以看到其他公司所做的修改

  • 注意: A查询数据库以获取数据,B修改数据库中的数据,导致A多次查询数据库,结果不同[危害:A的结果每个查询都受到B的影响,是的,那么A所查询的信息就没有意义了]

上面也说过,它是语句级别的快照! 每次阅读最新版本

避免不可重复的读取是事务级别快照!每次读取当前事务的版本时,即使已对其进行修改,也只会读取当前事务版本的数据。

呃……如果仍然不清楚,让我们看一下InnoDB的MVCC(摘录为”高性能MySQL”)

对于虚拟读取(幻像读取):是指读取另一个事务在一个事务中插入的数据,从而导致前后读取前后不一致。

  • 注意:它类似于不可重复读取,但是虚拟读取(幻像读取)将读取其他事务的插入数据,从而导致之前和之后的读取不一致。 li>

  • MySQL隔离级别加GAP间隙锁定魔术读取已处理

参考资料:

  • www.jianshu.com/p/cb97f76a9…
  • www.zhihu.com/question/26…

扩展阅读:

  • www.zhihu.com/question/67…

是否为隔离级别,都是为了解决读写冲突的问题。

让我们仅在隔离级别考虑问题:

这时,用户Li Si的操作丢失了:

  • 丢失更新:一个事务的更新覆盖其他事务的更新结果

(ps:我还没有想到一个更好的示例来说明丢失更新的问题。尽管以上示例也是丢失的更新,但在某种程度上可以接受。不知道是否有人能想到无法接受的缺少更新的示例…)

解决方案:

  • 在可序列化隔离级别下,事务是串行执行的!
  • 乐观锁
  • 悲观锁
  1. 乐观锁定是一个想法。具体实现是表中有一个version字段。首次读取时,将获得此字段。处理业务逻辑并开始更新后,您需要再次检查此字段的值是否与第一次相同。如果相同,它将被拒绝。之所以称为乐观,是因为该模式不会从数据库锁定,而是等待更新以确定是否可以更新。
  2. 悲观锁是一种数据库级锁,阻塞后会等待该锁。

因此,请按照上面的示例。如果我们使用悲观锁,那么它实际上非常简单(手动添加行锁):

在select语句之后添加排他锁(写锁)。添加写锁后,其他事务将无法对其进行修改!您需要等待当前事务被修改,然后才能对其进行修改。

  • 也就是说,如果张三使用它,那么李丝就无法修改记录

乐观锁不是数据库级别的锁。它们是需要手动添加的锁。通常,我们添加一个version字段来实现:

具体过程如下:

张三—>将查询记录,并且将有一个版本字段

Li Si—>将查询记录,并且将有一个版本字段

Li Si修改了该记录:为了判断先前查询的版本并将其与数据的当前版本进行比较,还将更新版本字段

数据库记录如下:

张三也修改了该记录:但是失败了!因为当前数据库中的版本与查询的版本不一致!

参考:

  • zhuanlan.zhihu.com/p/31537871-什么是悲观锁和乐观锁
  • www.zhihu.com/question/27…—乐观锁定和MVCC之间的区别?

当我们使用范围条件检索数据而不是相等条件来检索数据并请求共享或排他锁时,InnoDB将为满足范围条件的现有数据记录添加索引项;对于键值在条件范围内但不存在的记录,它称为”间隙”。 InnoDB也将锁定此”间隙”,这种锁定机制就是所谓的间隙锁定。

值得注意的是,间隙锁将仅在隔离级别使用

示例:如果emp表中只有101条记录,则empid的值为1,2,…,100,101

以上是范围查询。 InnoDB 不仅将锁定具有合格emid值101的记录,而且还将锁定” strong>大于101(这些记录不存在)的空值” 的记录。

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

  • 为了防止幻像读取(如上所述,可以使用GAP锁定来避免隔离级别的幻像读取)
  • 满足恢复和复制的需求

    • MySQL的恢复机制要求:在提交事务之前,其他并发事务不能插入任何满足其锁定条件的记录,即不允许进行魔术读取

并发问题对于死锁是必不可少的,在MySQL中也将存在死锁问题。

但是一般来说,MySQL通过回滚帮助我们解决了许多死锁问题,但是死锁是无法完全避免的。您可以使用以下经验参考来尝试避免尽可能少的死锁:

  • 1)以固定顺序访问表和行。例如,在批量更新两个作业的情况下,一种简单的方法是先对id列表进行排序,然后再执行它,从而避免了交叉等待锁的情况;将两个事务的SQL顺序调整为一致也可以避免死锁。
  • 2)大事小事。大型交易更容易陷入僵局。如果业务允许,请分解大笔交易。
  • 3)在同一事务中,尝试尽可能地执行一次锁定所需的所有资源,以减少死锁的可能性。
  • 4)降低隔离级别。如果服务允许,降低隔离级别也是一个更好的选择。例如,将隔离级别从RR调整为RC可以避免许多由间隙锁定引起的死锁。
  • 5)在表中添加合理的索引。可以看出,如果不采用索引,将为表的每一行添加一个锁,并且死锁的可能性大大增加。

参考资料:

  • hedengcheng.com/p=771#_Toc…
  • www.cnblogs.com/LBSer/p/518…

上面提到了许多有关MySQL数据库锁的内容,现在让我们简要地进行总结。

表锁,实际上,我们的程序员很少关心它:

  • 在MyISAM存储引擎中,当执行SQL语句时会自动添加它。
  • 在InnoDB存储引擎中,如果不使用索引,则会自动添加表锁。

现在我们大多数人都使用通过InnoDB使用的MySQL,InnoDB支持行锁:

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

默认情况下,不添加行锁可以通过以下语句将事务添加到记录集的共享锁或互斥锁。

  • 共享锁(S):。
  • 排他锁(X):。

InnoDB 基于行锁还实现了MVCC多版本并发控制,MVCC在隔离级别和隔离级别下工作。 MVCC可以实现读写而不会阻塞

InnoDB实现的隔离级别和GAP间隙锁定避免了魔术读取!

  • 乐观锁定实际上是一个主意,顾名思义:如果未锁定,则更新数据,如果不正确,则不更新(回滚)。通常将版本字段添加到数据库中以实现。
  • 悲观锁使用数据库的行锁。它认为数据库中将存在并发冲突。数据出现时直接被锁定,并且其他事务在提交当前事务之前无法修改

参考资料:

  • zhuanlan.zhihu.com/p/29150809–Mysql锁摘要
  • blog.csdn.net/mysteryhaoh…–MySQL学习锁(行锁,表锁,页面锁,乐观锁,悲观锁等)
  • segmentfault.com/a/119000001…–MySQL InnoDB引擎锁摘要

本文主要介绍数据库中的两个重要知识点:索引和锁。可以说它们是紧密相关的,锁将涉及很多有关索引的知识

我个人更注重对整体知识的控制,有些细节可能没有写。每个知识点下都会有很多内容。有兴趣的学生可以通过我给的链接继续阅读和学习。当然,如果您有更好的文章和信息,也可以在评论区域中共享它

我只是撰写并整理了学习过程中遇到的问题,希望对大家有帮助。如果文章有问题,希望您可以在评论区域中对我进行纠正,并学习一起交流

参考:

  • “高性能MySQL第三版”

作者:Java3y

链接:https://juejin.im/post/5b55b842f265da0f9e589e79

未经允许不得转载:数据库同步软件|Mysql数据同步软件|sqlserver数据库同步工具|异构同步 » 【MySQL】数据库两大神器【索引和锁】

分享到:更多 ()

syncnavigator 8.6.2 企业版

联系我们联系我们