您的位置: 首页  >  文章  >  MySQL高级性能优化知识，这些面试常问的东西你都知道吗？

MySQL高级性能优化知识，这些面试常问的东西你都知道吗？

分类: 文章 • 2024-01-14 09:38:58

文章目录

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。

推荐阅读

范式

⽬前关系数据库有六种范式：第⼀范式（1NF）、第⼆范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，⼜称完美范式）。满⾜最低要求的范式是第⼀范式（1NF）。在第⼀范式的基础上进⼀步满⾜更多规范要求的称为第⼆范式（2NF），其余范式以次类推。⼀般说来，数据库只需满⾜第三范式(3NF）就⾏了。

第⼀范式（1NF）

  • 即表的列的具有原⼦性,不可再分解，即列的信息，不能分解, 只要数据库是关系型数据库，就⾃动的满⾜1NF。数据库表的每⼀列都是不可分割的原⼦数据项，⽽不能是集合，数组，记录等⾮原⼦数据项。如果实体中的某个属性有多个值时，必须拆分为不同的属性 。通俗理解即⼀个字段只存储⼀项信息。

第⼆范式（2NF）

  • 第⼆范式（2NF）是在第⼀范式（1NF）的基础上建⽴起来的，即满⾜第⼆范式（2NF）必须先满⾜第⼀范式（1NF）。第⼆范式（2NF）要求数据库表中的每个实例或⾏必须可以被唯⼀地区分。为实现区分通常需要我们设计⼀个主键来实现(这⾥的主键不包含业务逻辑)。即满⾜第⼀范式前提，当存在多个主键的时候，才会发⽣不符合第⼆范式的情况。⽐如有两个主键，不能存在这样的属性，它只依赖于其中⼀个主键，这就是不符合第⼆范式。通俗理解是任意⼀个字段都只依赖表中的同⼀个字段。（涉及到表的拆分）

第三范式（3NF）

  • 满⾜第三范式（3NF）必须先满⾜第⼆范式（2NF）。简⽽⾔之，第三范式（3NF）要求⼀个数据库表中不包含已在其它表中已包含的⾮主键字段。就是说，表的信息，如果能够被推导出来，就不应该单独的设计⼀个字段来存放(能尽量外键join就⽤外键join)。很多时候，我们为了满⾜第三范式往往会把⼀张表分成多张表。即满⾜第⼆范式前提，如果某⼀属性依赖于其他⾮主键属性，⽽其他⾮主键属性⼜依赖于主键，那么这个属性就是间接依赖于主键，这被称作传递依赖于主属性。 通俗解释就是⼀张表最多只存两层同类型信息。

巴斯-科德范式（BCNF）

  • Boyce-Codd Normal Form（巴斯-科德范式）在3NF基础上，任何⾮主属性不能对主键⼦集依赖（在3NF基础上消除对主码⼦集的依赖）BCNF是第三范式（3NF）的⼀个⼦集，即满⾜BCNF必须满⾜第三范式（3NF）。通常情况下，巴斯-科德范式被认为没有新的设计规范加⼊，只是对第⼆范式与第三范式中设计规范要求更强，因⽽被认为是修正第三范式，也就是说，它事实上是对第三范式的修正，使数据库冗余度更⼩。这也是BCNF不被称为第四范式的原因。某些书上，根据范式要求的递增性将其称之为第四范式是不规范，也是更让⼈不容易理解的地⽅。⽽真正的第四范式，则是在设计规范中添加了对多值及依赖的要求

第四范式（4NF）

  • 对于第四范式，从理论层⾯来讲是，关系模式R∈1NF,如果对于R对于R的每个⾮平凡多值依赖X→Y(Y不属于X),X都含有候选码，则R∈4NF。4NF就是限制关系模式的属性之间不允许有⾮平凡且⾮函数依赖的多值依赖。显然⼀个关系模式是4NF，则必为BCNF。也就是说，当⼀个表中的⾮主属性互相独⽴时（3NF），这些⾮主属性不应该有多值。若有多值就违反了第四范式。

Mysql逻辑架构

总体分层

MySQL高级性能优化知识，这些面试常问的东西你都知道吗？

  • 简化之后的架构图
    MySQL高级性能优化知识，这些面试常问的东西你都知道吗？

连接层

  • 主要负责连接管理、授权认证、安全等等。每个客户端连接都对应着服务器上的⼀个线程。服务器上维护了⼀个线程池，避免为每个连接都创建销毁⼀个线程。当客户端连接到MySQL服务器时，服务器对其进⾏认证。可以通过⽤户名与密码认证，也可以通过SSL证书进⾏认证。登录认证后，服务器还会验证客户端是否有执⾏某个查询的操作权限。这⼀层并不是MySQL所特有的技术。

为什么要设计成线程池？

  • 在服务器内部，每个client都要有⾃⼰的线程。这个连接的查询都在⼀个单独的线程中执⾏。想象现实场景中数据库访问连接实在是太多了，如果每次连接都要创建⼀个线程，同时还要负责该线程的销毁。对于系统来说是多么⼤的消耗。由于线程是操作系统宝贵的资源。这时候线程池的出现就显得⾃然了，服务器缓存了线程，因此不需要为每个Client连接创建和销毁线程。

服务层

  • 服务层是MySQL的核⼼，MySQL的核⼼服务层都在这⼀层，查询解析，SQL执⾏计划分析，SQL执⾏计划优化，查询缓存。以及跨存储引擎的功能都在这⼀层实现：存储过程，触发器，视图等。服务器会解析查询并创建相应的内部解析权,并对其完成相应的优化,生成相应的执行操作。服务器还会查询内部的缓存,如果缓存空间足够大,这样可以解决大量读操作的环境中,能够很好的提升系统性能。

  • 服务层的内部结构：
    MySQL高级性能优化知识，这些面试常问的东西你都知道吗？

SQL语句在服务层中具体的流程

  • 查询缓存：在解析查询之前，服务器会检查查询缓存，如果能找到对应的查询，服务器不必进⾏查询解析、优化和执⾏的过程，直接返回缓存中的结果集。
  • 解析器与预处理器： MySQL会解析查询，并创建了⼀个内部数据结构（解析树）。这个过程解析器主要通过语法规则来验证和解析。⽐如SQL中是否使⽤了错误的关键字或者关键字的顺序是否正确等等。预处理会根据MySQL的规则进⼀步检查解析树是否合法。⽐如要查询的数据表和数据列是否存在等。
  • 查询优化器 ：优化器将其转化成查询计划。多数情况下，⼀条查询可以有很多种执⾏⽅式，最后都返回相应的结果。优化器的作⽤就是找到这其中最好的执⾏计划。优化器并不关⼼使⽤的什么存储引擎，但是存储引擎对优化查询是有影响的。优化器要求存储引擎提供容量或某个具体操作的开销信息来评估执⾏时间。
  • 查询引擎 ：在完成解析和优化阶段以后，MySQL会⽣成对应的执⾏计划，查询执⾏引擎根据执⾏计划给出的指令调⽤存储引擎的接⼝得出结果。

引擎层

  • 负责MySQL中数据的存储与提取。 服务器中的查询执⾏引擎通过API与存储引擎进⾏通信，通过接⼝屏蔽了不同存储引擎之间的差异。MySQL采⽤插件式的存储引擎。MySQL为我们提供了许多存储引擎，每种存储引擎有不同的特点。我们可以根据不同的业务特点，选择最适合的存储引擎。如果对于存储引擎的性能不满意，可以通过修改源码来得到⾃⼰想要达到的性能。例如阿⾥巴巴的X-Engine，为了满⾜企业的需求facebook与google都对InnoDB存储引擎进⾏了扩充。
  • 特点： 存储引擎是针对于表的⽽不是针对库的（⼀个库中不同表可以使⽤不同的存储引擎），服务器通过API与存储引擎进⾏通信，⽤来屏蔽不同存储引擎之间的差异。

存储引擎

  • 在数据库中查看你的数据库存储引擎：SHOW ENGINES;
    MySQL高级性能优化知识，这些面试常问的东西你都知道吗？
    InnoDB
  • 特点 ：⽀持事务，适合OLTP（联机事务处理）应⽤，假设没有什么特殊的需求，⼀般都采⽤InnoDB作为存储引擎。⽀持⾏级锁，从MySQL 5.5.8开始,InnoDB存储引擎是默认的存储引擎。 InnoDB采用MVCC来支持事务一致性和并发，并且实现了4个基本的隔离级别，默认级别是PREATABLE READ(可重复读)，并且通过间隙锁(next-key loking)策略来防止欢读的出现。
    MyISAM
  • 特点 ：不⽀持事务，表锁设计，⽀持全⽂索引，主要应⽤于OLAP（联机分析处理）应⽤。MyISAM提供了全文索引、压缩、空间函数等，但是MyISAM是不支持事务和行级锁的，MyISAM会将表存储在两个文件中，数据文件和索引文件，拓展名分别是.MYD和.MYI，MyISAM虽然没有行级锁，不过是可以支持表锁的，所以在一些业务系统还是可以做到并发控制的。
  • 场景 ：在排序、分组等操作中，当数量超过⼀定⼤⼩之后，由查询优化器建⽴的临时表就是MyISAM类型 报表，数据仓库
  • 知识拓展：

数据处理大致可以分成两大类：联机事务处理OLTP（on-line transaction processing）、联机分析处理OLAP（On-Line Analytical Processing）。
OLTP是传统的关系型数据库的主要应用，主要是基本的、日常的事务处理，例如银行交易。
OLAP是数据仓库系统的主要应用，支持复杂的分析操作，侧重决策支持，并且提供直观易懂的查询结果。
OLTP 系统强调数据库内存效率，强调内存各种指标的命令率，强调绑定变量，强调并发操作；
OLAP 系统则强调数据分析，强调SQL执行市场，强调磁盘I/O，强调分区等。

InnoDB与MyISAM 对比

MySQL高级性能优化知识，这些面试常问的东西你都知道吗？

  • Memory
  • 特点：数据都存放在内存中，数据库重启或崩溃，表中的数据都将消失，但是标的结构还是会保存下来。默认使⽤Hash索引。
  • 场景 ：适合存储OLTP应⽤的临时数据或中间表。 ⽤于查找或是映射表，例如邮编和地区的对应表。
    Archive
  • Archive存储引擎只支持INSERT和SELECT操作，在Mysql5.1版本之前不支持索引的，Archive引擎会缓存所有的写数据，利用zlib对写入的数据进行压缩，所以需要的磁盘/IO比MyISAM更少的，但是SELECT是全表扫描的，因此Archive表适合用于日志和数据采集类的应用，Archive引擎是支持行级锁的
    Blackhole
  • Blackhole引擎没有存储机制，不做保存处理，但是服务器还是会记录Blackhole表的日志，所以可以用于复制数据到备库，或者用于记录日志
    CSV
  • CSV引擎可以将普通的CSV文件作为MySql的表处理，可以在数据库运行时拷贝文件，将Excel表另存为CSV文件，然后就可以直接复制到数据库，不过这种表是不支持索引的
    Federated
  • Federated引擎是访问其它服务器的代理，会 创 建 一 个 到 远 程 MySQL 服 务 器 的 客 户 端 连 接， 并 将 查 询 传 输 到 远 程 服 务 器 执 行， 然 后 提 取 或 者 发 送 需 要 的 数 据。
    Merge
  • Merge引擎是由多个MyISAM表合并而来的表，可以用于存储日志或者用于当数据存库
    自定义存储引擎
  • Mysql的存储引擎是支持可以自己开发和修改的，比如阿里的mysql就不是用默认InnoDB

存储层

  • 该层主要是将数据库的数据存储在⽂件系统之上，并完成与存储引擎的交互。

SELECT语句执⾏顺序

代码编写顺序

  1. select distinct 查询字段
  2. from 表名
  3. JOIN 表名
  4. ON 连接条件
  5. where 查询条件
  6. group by 分组字段
  7. having 分组后条件
  8. order by 排序条件
  9. limit 查询起始位置, 查询条数

Mysql读取顺序

  1. from 表名
  2. ON 连接条件
  3. JOIN 表名
  4. where 查询条件
  5. group by 分组字段
  6. having 分组后条件
  7. select distinct 查询字段
  8. order by 排序条件
  9. limit 查询起始位置, 查询条数
  • 图示
    MySQL高级性能优化知识，这些面试常问的东西你都知道吗？
  • 这些步骤执⾏时，每个步骤都会产⽣⼀个虚拟表，该虚拟表被⽤作下⼀个步骤的输⼊。这些虚拟表对调⽤者（客户端应⽤程序或者外部查询）不可⽤。只是最后⼀步⽣成的表才会返回给调⽤者。如果没有在查询中指定某⼀⼦句，将跳过相应的步骤。

JOIN连接

由于之前我已经写过一篇JOIN连接的文章，这里不再过多阐述。

优化分析

性能下降的原因

  • 查询语句写的不好:各种连接,各种子查询导致用不上索引或者没有建立索引
  • 建立的索引失效:建立了索引,在真正执行时,没有用上建立的索引
  • 关联查询太多join
  • 服务器调优及和个配置参数导致:如果设置的不合理,比例不恰当,也会导致性能下降,sql变慢

DQL的执行过程

SQL的执行过程
  • SQL的执行过程；
    1. 客户端发送一条查询给服务器；
    2. 服务器通过权限检查之后,先会检查查询缓存，如果命中了缓存，则立即返回存储在缓存中的结果。否则进入下一阶段；
    3. 服务器端进行SQL解析、预处理，再由优化器根据该SQL所涉及到的数据表的统计信息进行计算，生成对应的执行计划；
    4. MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询；
    5. 将结果返回给客户端。
查询优化器
  • 写的任何sql,到底是怎么样真正执行的,按照什么条件查询,最后执行的顺序,可能都会有多个执行方案
  • 查询优化器根基对数据表的统计信息(比如索引,有多少条数据),在真正执行一条sql之前,会根据自己内部的数据,进行综合的查询,
  • 根据mysql自身的统计信息, 从多种执行方案当中, 选择一个它认为是最优的执行方案,来去执行
做优化,做什么
  • 做优化, 就是想让查询优化器按照我们的想法,帮我们选择最优的执行方案,
  • 让优化器选择符合程序员计划的执行语句,来减少查询过程中产生的IO

MySQL 优化

  • 表关联查询时务必遵循 小表驱动大表 原则；
  • 使用查询语句 where 条件时，不允许出现 函数，否则索引会失效；
  • 使用单表查询时，相同字段尽量不要用 OR，因为可能导致索引失效，比如：SELECT * FROM table WHERE name = '手机' OR name = '电脑'，可以使用 UNION 替代；
  • LIKE 语句不允许使用 % 开头，否则索引会失效；
  • 组合索引一定要遵循 从左到右 原则，否则索引会失效；比如：SELECT * FROM table WHERE name = '张三' AND age = 18，那么该组合索引必须是 name,age 形式；
  • 索引不宜过多，根据实际情况决定，尽量不要超过 10 个；
  • 每张表都必须有 主键，达到加快查询效率的目的；
  • 分表，可根据业务字段尾数中的个位或十位或百位（以此类推）做表名达到分表的目的；
  • 分库，可根据业务字段尾数中的个位或十位或百位（以此类推）做库名达到分库的目的；
  • 表分区，类似于硬盘分区，可以将某个时间段的数据放在分区里，加快查询速度，可以配合 分表 + 表分区 结合使用；

存储过程

存储过程（Stored Procedure）是⼀种在数据库中存储复杂程序，以便外部程序调⽤的⼀种 数据库对象。
存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，⽤户可通过指定存储过程的名字并给定参数(需要时)来调⽤执⾏。
存储过程思想上很简单，就是数据库 SQL 语⾔层⾯的代码封装与重⽤。

优点

  • 存储过程可封装，并隐藏复杂的商业逻辑。
  • 存储过程可以回传值，并可以接受参数。
  • 存储过程⽆法使⽤ SELECT 指令来运⾏，因为它是⼦程序，与查看表，数据表或⽤户定义函数不同。
  • 存储过程可以⽤在数据检验，强制实⾏商业逻辑等。

缺点

  • 存储过程，往往定制化于特定的数据库上，因为⽀持的编程语⾔不同。当切换到其他⼚商的数据库系统时，需要重写原有的存储过程。
  • 存储过程的性能调校与撰写，受限于各种数据库系统。

MySQL锁机制

锁是计算机协调多个进程或线程并发访问某一资源的机制。锁保证数据并发访问的一致性、有效性；锁冲突也是影响数据库并发访问性能的一个重要因素。锁是Mysql在服务器层和存储引擎层的的并发控制。
加锁是消耗资源的，锁的各种操作，包括获得锁、检测锁是否是否已解除、释放锁等。

粒度锁

  • MySQL的锁机制比较简单，其最 显著的特点是不同的存储引擎支持不同的锁机制。比如，MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；BDB存储引擎采用的是页面锁（page-level locking），但也支持表级锁；InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。
不同粒度锁的比较
  • 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
  • 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
  • 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般
  • 从锁的角度 来说：表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有 并发查询的应用，如一些在线事务处理（OLTP）系统。

共享锁与排他锁

  • 共享锁（读锁）：其他事务可以读，但不能写。
  • 排他锁（写锁） ：其他事务不能读取，也不能写。

MyISAM表锁

  • MySQL的表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。
  • 对MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；对 MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作；MyISAM表的读操作与写操作之间，以及写操作之间是串行的
如何加表锁
  • MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作 （UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。

InnoDB行级锁

  • InnoDB与MyISAM的最大不同有两点：
    • 一是支持事务（TRANSACTION）；
    • 二是采用了行级锁。
  • 行级锁与表级锁本来就有许多不同之处，另外，事务的引入也带来了一些新问题。
InnoDB锁模式
  • InnoDB 实现了以下两种类型的行锁：
    • 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
    • 排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。
  • 为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB 还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁：
    • 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。
    • 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的 IX 锁。

事务

事务（Transaction）及其ACID属性
  • 事务是由一组SQL语句组成的逻辑处理单元，事务具有4属性，通常称为事务的ACID属性。
    • 原子性（Actomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。
    • 一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以 操持完整性；事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的。
    • 隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。
    • 持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。
并发事务带来的问题

  • 相对于串行处理来说，并发事务处理能大大增加数据库资源的利用率，提高数据库系统的事务吞吐量，从而可以支持可以支持更多的用户。但并发事务处理也会带来一些问题，主要包括以下几种情况。

    • 更新丢失（Lost Update）：当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题——最后的更新覆盖了其他事务所做的更新。例如，两个编辑人员制作了同一文档的电子副本。每个编辑人员独立地更改其副本，然后保存更改后的副本，这样就覆盖了原始文档。最后保存其更改保存其更改副本的编辑人员覆盖另一个编辑人员所做的修改。如果在一个编辑人员完成并提交事务之前，另一个编辑人员不能访问同一文件，则可避免此问题。
    • 脏读（Dirty Reads）：一个事务正在对一条记录做修改，在这个事务并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”的数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做“脏读”。
    • 不可重复读（Non-Repeatable Reads）：一个事务在读取某些数据已经发生了改变、或某些记录已经被删除了！这种现象叫做“不可重复读”。
    • 幻读（Phantom Reads）：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。
事务隔离级别

  • 在并发事务处理带来的问题中，“更新丢失”通常应该是完全避免的。但防止更新丢失，并不能单靠数据库事务控制器来解决，需要应用程序对要更新的数据加必要的锁来解决，因此，防止更新丢失应该是应用的责任。

  • “脏读”、“不可重复读”和“幻读”，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。数据库实现事务隔离的方式，基本可以分为以下两种。

    • 一种是在读取数据前，对其加锁，阻止其他事务对数据进行修改。
    • 另一种是不用加任何锁，通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot），并用这个快照来提供一定级别（语句级或事务级）的一致性读取。从用户的角度，好像是数据库可以提供同一数据的多个版本，因此，这种技术叫做数据多版本并发控制（ＭultiVersion Concurrency Control，简称MVCC或MCC），也经常称为多版本数据库。

  • 在MVCC并发控制中，读操作可以分成两类：快照读 (snapshot read)与当前读 (current read)。快照读，读取的是记录的可见版本 (有可能是历史版本)，不用加锁。当前读，读取的是记录的最新版本，并且，当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。

死锁（Deadlock Free）

死锁产生
  • 死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环。
  • 当事务试图以不同的顺序锁定资源时，就可能产生死锁。多个事务同时锁定同一个资源时也可能会产生死锁。
  • 锁的行为和顺序和存储引擎相关。以同样的顺序执行语句，有些存储引擎会产生死锁有些不会——死锁有双重原因：真正的数据冲突；存储引擎的实现方式。
检测死锁
  • 数据库系统实现了各种死锁检测和死锁超时的机制。InnoDB存储引擎能检测到死锁的循环依赖并立即返回一个错误。
死锁恢复
  • 死锁发生以后，只有部分或完全回滚其中一个事务，才能打破死锁，InnoDB目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚。所以事务型应用程序在设计时必须考虑如何处理死锁，多数情况下只需要重新执行因死锁回滚的事务即可。
外部锁的死锁检测
  • 发生死锁后，InnoDB 一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁，或涉及表锁的情况下，InnoDB 并不能完全自动检测到死锁， 这需要通过设置锁等待超时参数 innodb_lock_wait_timeout 来解决
死锁影响性能
  • 死锁会影响性能而不是会产生严重错误，因为InnoDB会自动检测死锁状况并回滚其中一个受影响的事务。在高并发系统上，当许多线程等待同一个锁时，死锁检测可能导致速度变慢。 有时当发生死锁时，禁用死锁检测（使用innodb_deadlock_detect配置选项）可能会更有效，这时可以依赖innodb_lock_wait_timeout设置进行事务回滚。
MyISAM避免死锁
  • 在自动加锁的情况下，MyISAM 总是一次获得 SQL 语句所需要的全部锁，所以 MyISAM 表不会出现死锁。
InnoDB避免死锁
  • 为了在单个InnoDB表上执行多个并发写入操作时避免死锁，可以在事务开始时通过为预期要修改的每个元祖（行）使用SELECT … FOR UPDATE语句来获取必要的锁，即使这些行的更改语句是在之后才执行的。
  • 在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁、更新时再申请排他锁，因为这时候当用户再申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁
  • 如果事务需要修改或锁定多个表，则应在每个事务中以相同的顺序使用加锁语句。 在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会
  • 通过SELECT … LOCK IN SHARE MODE获取行的读锁后，如果当前事务再需要对该记录进行更新操作，则很有可能造成死锁。
    改变事务隔离级别
  • 如果出现死锁，可以用 SHOW INNODB STATUS 命令来确定最后一个死锁产生的原因。返回结果中包括死锁相关事务的详细信息，如引发死锁的 SQL 语句，事务已经获得的锁，正在等待什么锁，以及被回滚的事务等。据此可以分析死锁产生的原因和改进措施。
一些优化锁性能的建议
  • 尽量使用较低的隔离级别；
  • 精心设计索引， 并尽量使用索引访问数据， 使加锁更精确， 从而减少锁冲突的机会
  • 选择合理的事务大小，小事务发生锁冲突的几率也更小
  • 给记录集显示加锁时，最好一次性请求足够级别的锁。比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁
  • 不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会
  • 尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响
  • 不要申请超过实际需要的锁级别
  • 除非必须，查询时不要显示加锁。 MySQL的MVCC可以实现事务中的查询不用加锁，优化事务性能；MVCC只在COMMITTED READ（读提交）和REPEATABLE READ（可重复读）两种隔离级别下工作
  • 对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能

乐观锁、悲观锁

  • 乐观锁(Optimistic Lock)：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。 乐观锁不能解决脏读的问题。
    • 乐观锁, 顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库如果提供类似于write_condition机制的其实都是提供的乐观锁。
  • 悲观锁(Pessimistic Lock)：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。
    • 悲观锁，顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。

你知道的越多，你不知道的越多。
有道无术，术尚可求，有术无道，止于术。
如有其它问题，欢迎大家留言，我们一起讨论，一起学习，一起进步

相关推荐