MySQL基础部分

如果是InnoDBUPDATE语句在执行器调用引擎时都发生了什么

10.

雪花算法可以生成主键请请你描述雪花算法的原理能介绍一下它有哪些优势和不足该如何解决这些不足呢

33.

JOIN左外连接会返回左表的所有行以及右表中匹配的行右外连接则返回右表的所有行以及左表中匹配的行。

在优化查询时内连接可以通过小表驱动大表来减少扫描行数而外连接则通常以左表为驱动表通过在右表的连接字段上建立索引来提高查询效率。

第一范式1NF要求表中的所有字段都是原子性的即每个字段只包含不可分割的最小数据单位表中没有重复的行。

第二范式2NF在满足1NF的基础上要求表中的非主键字段必须完全依赖于主键不能仅依赖于主键的一部分即表中不存在部分依赖。

第三范式3NF在满足2NF的基础上要求表中的非主键字段必须直接依赖于主键不能存在传递依赖即非主键字段之间不能相互依赖。

视图可以将复杂的查询语句封装起来用户可以通过简单的查询来获取所需的数据。

数据安全

视图可以限制用户访问数据表中的一部分数据只暴露必要的数据从而提供一定程度的数据安全性。

数据独立性

视图可以屏蔽底层表结构的变化用户不必关心底层表的具体结构只需操作视图即可。

视图本身是一个虚拟表它不存储数据只保存逻辑而是基于底层表的结果集。

关于视图是否可以更改这取决于视图的定义。

1服务器层包括连接器、分析器、优化器、执行器。

连接器负责处理客户端与MySQL服务器之间的连接包括管理连接、权限认证。

分析器负责解析客户端发送的SQL语句包括词法分析、语法分析。

优化器负责选择索引、优化SQL、为INNER

2存储引擎层存储引擎用来具体执行SQL常见的存储引擎有InnoDB、MyISAM、Memory等。

user

首先客户端与MySQL连接器进行连接然后分析器经过词法分析和语法分析然后到达优化器优化器生成执行计划选择索引这里选择主键索引id然后就到达了执行器执行器调用存储引擎存储引擎返回结果。

如果是InnoDBUPDATE语句在执行器调用引擎时都发生了什么

执行器调用存储引擎找到id1的数据由于id是主键直接在主键索引树上查找。

如果这行数据所在的数据页在内存中直接返回给执行器结果就可以了。

如果不在内存中会从磁盘中读取数据到内存再返回给执行器结果。

然后执行器拿到存储引擎返回的这行数据把name设置成xiaoming

得到新的数据再调用执行引擎的接口写入这行数据。

接着执行引擎将这行新的数据更新到内存中同时将更新的记录写到Redo

Log中此时Redo

Log处于prepare的状态然后再告诉执行器随时可以提交事务执行器会根据操作生成Bin

Log然后把Bin

Log写入磁盘写入成功执行器会调用存储引擎提交事务的接口存储引擎把Redo

Log的prepare状态改成commit状态到此整个更新就完成了。

1优点保证数据唯一性、检索速度快、加快表的连接、优化分组和排序

提高查询速度索引可以大幅提升数据检索的效率类似于图书馆的索引目录能够快速定位到所需的数据从而减少数据库的I/O操作提高查询性能。

保证数据唯一性通过创建唯一索引可以确保表中每一行数据的唯一性避免数据重复。

加速表连接在执行涉及多个表的联合查询时索引可以加速表之间的连接操作特别是对于具有外键关系的表。

优化分组和排序在进行分组GROUP

时间和空间成本索引的创建和维护需要消耗时间尤其是在数据量大的情况下索引的构建和维护会更加耗时。

同时索引本身需要占用额外的存储空间。

降低数据维护效率当对表中的数据进行插入、删除和更新操作时索引也需要相应地进行更新这可能会降低这些写操作的性能。

MySQL数据库的索引原理是基于数据结构中的B树实现的。

索引能够提高查询效率其核心思想是通过快速定位来减少磁盘I/O次数。

当执行查询操作时MySQL会使用索引来快速找到数据行而不是遍历整个数据表。

B树结构使得索引在查找、插入和删除操作中都能保持较高的效率因为它的中间节点不存储数据只有叶子节点才存储数据并且叶子节点之间是通过指针连接的便于范围查询。

10.

1B树索引是一种自平衡的树结构它允许搜索、顺序访问和插入/删除操作但相比于B树它的磁盘I/O操作较多。

2B树索引是MySQL中最常用的索引类型它的所有数据都在叶子节点并且叶子节点之间有指针相连适合范围查询。

3哈希索引基于哈希表实现适用于等值查询但不支持排序和范围查询。

5空间索引则是为空间数据类型设计的适用于地理空间数据的查询。

每种索引都有其特定的应用场景和优缺点。

11.

Hash索引底层就是Hash表进行查询时调用Hash函数获取到相应的键值对应地址然后回表查询获得实际数据。

B树索引底层实现原理是多路平衡查找树对于每一次的查询都是从根节点出发查询到叶子节点方可以获得所查键值最后查询判断是否需要回表查询。

Hash进行等值查询更快但无法进行范围查询。

因为经过Hash函数建立索引之后索引的顺序与原顺序无法保持一致故不能支持范围查询。

同理也不支持使用索引进行排序。

Hash不支持模糊查询以及多列索引的最左前缀匹配因为Hash函数的值不可预测如AA和AB的算出的值没有相关性。

虽然在等值上查询效率高但性能不稳定因为当某个键值存在大量重复时产生Hash碰撞此时查询效率反而可能降低。

在符合某些条件聚簇索引、覆盖索引等时候可以只通过索引完成查询不需要回表。

查询效率比较稳定因为每次查询都是从根节点到叶子节点且为树的高度。

12.

主键索引是一种特殊的唯一索引用于确保表中每一行数据的唯一性。

在MySQL中当定义一个主键时系统会自动创建一个主键索引。

主键索引可以加快查询速度因为它们提供了快速的数据访问路径。

2区别

主键索引和聚集索引的区别在于主键索引是一种约束用于保证数据的唯一性而聚集索引是一种索引存储方式它决定了表中数据的物理存储顺序。

在InnoDB存储引擎中通常主键索引就是聚集索引因为InnoDB会按照主键的顺序来存储数据。

如果一个表没有主键InnoDB会选择一个唯一的索引作为聚集索引或者隐式地创建一个。

13.

1聚簇索引是指数据的物理存储顺序与索引顺序相同即索引项的顺序与表中记录的物理顺序一致。

在聚簇索引中叶子节点直接存储了数据行。

2非聚簇索引的物理存储顺序与索引顺序不同叶子节点存储的是数据行的地址需要根据地址再次查询才能获取到数据行。

1数据结构

由于数据行和索引顺序一致对于范围查询和排序操作聚簇索引通常有更好的性能因为不需要额外的查找。

对于非聚簇索引查询需要先在索引中找到对应的数据行指针然后根据指针去数据页中找到实际的数据可能会增加额外的IO开销。

3索引更新

由于数据的物理顺序依赖于聚簇索引因此当插入、删除或更新操作导致行移动时可能会引起大量的数据移动从而降低性能。

非聚簇索引的更新不会引起数据行的物理移动因此通常对性能的影响较小。

4主键索引

在MySQL的InnoDB存储引擎中主键通常会自动创建为聚簇索引。

14.

回表是指在非聚簇索引查询过程中当我们通过索引找到对应的数据行地址后需要回到数据表的聚簇索引中去查询完整的记录。

举一个例子例如SELECT

如果扫描的二级索引行数特别多不如全表扫描了那优化器就会选择全表扫描如果是有加上LIMIT

15.

如果一个索引包含了查询中需要的所有列那么这个索引就被称为“覆盖索引”。

创建索引时可以包含查询中需要的所有列这样查询就可以直接通过索引返回结果而不需要回表。

如果查询条件中已经包含了主键那么直接使用主键进行查询不需要回表。

16.

覆盖索引是指一个索引包含了查询所需的所有字段的值。

例如SELECT

id,

nameJack为name建立了索引同时id也是主键。

优点有避免InnoDB表的二次查询回表把随机I/O变成顺序I/O。

17.

索引条件下推ICP是一种数据库查询优化技术。

它允许数据库在索引查找过程中将部分WHERE子句的条件提前应用到索引扫描阶段从而减少需要访问的索引项和回表的数据行数。

这样做的目的是提高查询速度减少数据库系统的I/O开销进而提升整体查询性能。

18.

B树每个节点都包含键Key和数据Value。

键用于排序而数据是与键关联的实际信息。

B树非叶子节点只包含键用来进行索引不包含数据。

所有的数据都存储在叶子节点中并且叶子节点之间是通过指针相连的形成了一个有序链表。

2查询效率

B树由于每个节点都可能包含数据查询可能在非叶子节点结束这可能导致更多的磁盘I/O操作。

B树查询总是从根节点到叶子节点因为所有数据都在叶子节点上这样可以减少磁盘I/O操作提高查询效率。

19.

B树的非叶子节点不存储实际数据只存储索引值这使得每个节点可以存储更多的索引从而降低了树的高度减少了磁盘I/O操作的次数。

3适合范围查找

B树的叶子节点是有序的这使得范围查找变得非常高效而B树的范围查找可能需要通过中序遍历效率较低。

数据库操作中范围查找非常常见因此B树在这方面更具优势。

20.

如果某个字段经常用来做查询条件那么该字段的查询速度会影响整个表的查询速度。

因此为这样的字段建立索引可以提高整个表的查询速度。

经常需要ORDER

BY、DISTINCT和UNION等操作的字段排序操作会浪费很多时间。

如果为其建立索引可以有效地避免排序操作。

每个索引都需要占用磁盘空间索引越多需要的磁盘空间就越大修改表时对索引的重构和更新很麻烦。

由于数据较小查询花费的时间可能比遍历索引的时间还要短索引可能不会产生优化效果。

如果索引的值很长那么查询的速度会受到影响。

此时尽量使用前缀索引。

21.

1对于不在查询条件WHERE子句中使用的字段无需创建索引因为索引不会被查询优化器利用。

2对于数据量较小的表索引可能不会带来显著的性能提升反而可能因为索引的维护而降低写入效率。

3在那些具有高度重复值的列上建立索引通常是不划算的因为索引的筛选效果不佳。

4对于频繁进行写操作如插入、更新、删除的表应避免创建过多索引以免降低写操作的性能。

5使用无序的值作为索引可能会导致索引效率低下因为索引的目的是快速定位数据而无序的值无法有效支持这一目的。

22.

3类型转换确保查询条件中的数据类型与字段定义的类型匹配避免隐式类型转换。

5OR条件确保OR连接的所有条件都有索引否则整个查询可能不会利用索引。

7IS

8字符集不一致保持数据库字符集的一致性推荐使用utf8mb4。

存储引擎

1MyISAMMyISAM曾经是MySQL5.5版本及之前的默认存储引擎。

MyISAM表不支持事务行级锁和外键约束的功能。

2InnoDBMySQL5.5版本开始InnoDB成为了默认的存储引擎它提供了事务、行级锁机制和外键约束的功能。

3Memory这种类型的数据表只存在于内存中。

它使用Hash索引所以数据的存取速度非常快。

因为是存在于内存中所以这种类型常应用于临时表中。

24.

InnoDB支持事务最重要的特性MyISAM是不支持事务的来保证数据的ACID特性原子性、一致性、隔离性、持久性。

InnoDB支持行级锁MyISAM支持表级锁。

行级锁对比表级锁粒度更细提高了系统的并发性能。

InnoDB实现了外键约束。

这使得数据库设计者能够通过外键关系建立表与表之间的关联保证了数据的参照完整性。

InnoDB使用的是B树索引B树检索速度较快磁盘IO相对较少。

InnoDB的数据和索引等存储在.idb和.frm文件中.frm在MySQL8.0之后数据合并到.idb里面而MyISAM的表数据信息存放在.myd表数据索引存放在.myi。

25.

MyISAM是基于B树实现的但是它B树索引的叶子节点存储的不是数据本身而是数据的指针或叫做数据的地址。

所以索引和数据是分开存储的。

因此相当于每次查询都要进行回表操作。

MyISAM支持表锁和全文索引。

但是不支持行锁和事务。

由于锁的粒度是表级别的因此适合用于读多写少的场景例如日志分析等等。

因此可以将读操作分配给MyISAM存储引擎写操作分配给其他存储引擎。

26.

如果业务主要是读查询可以选择MyISAM引擎。

MyISAM引擎在读取操作上进行了优化因为它支持表级锁定减少了锁定开销并且在查询时不会因为写锁定而阻塞读操作这使其在只读或读多写少的场景下性能表现优异。

此外MyISAM的存储格式使得它占用的空间更小索引的大小也更紧凑这有助于提高I/O效率。

然而需要注意的是MyISAM不支持事务和行级锁定如果业务未来可能需要事务支持或高并发写操作那么MyISAM可能不是最佳选择。

27.

在InnoDB存储引擎中页是磁盘管理的最小单位通常大小为16KB用于存储数据和索引区是由连续的页组成默认大小为1MB是空间分配的基本单位段则是由多个区组成用于管理表和索引的数据。

29.

自增主键的顺序性保证了新记录总是被添加到索引的末尾从而避免了频繁的页分裂减小了插入数据操作的开销。

如果插入到两个数据中间可能会进行频繁的数据移动。

有助于减少数据碎片使得数据存储更加连续。

对于范围查询自增主键能够更好地利用索引的有序性提高查询效率。

30.

1分区写入热点使用自增ID时所有的写入操作都会集中在最新的分区上这会导致写入热点问题。

一旦当前分区写满写入操作才会移动到下一个分区这种“尾部热点”现象会降低系统的写入性能。

2负载均衡困难自增ID的连续性使得负载均衡变得困难。

在分布式系统中理想的状况是将请求均匀地分配到不同的服务器或分区上而自增ID无法实现这一点。

31.

1优点UUID几乎可以保证在全球范围内是唯一的这对于分布式系统尤其重要。

UUID不容易被猜解因此可以提供一定程度的隐私保护。

2缺点对于InnoDB这类使用B树索引的存储引擎无序的UUID会导致页分裂和碎片化降低查询效率。

32.

雪花算法可以生成主键请请你描述雪花算法的原理能介绍一下它有哪些优势和不足该如何解决这些不足呢

1优势

全局唯一性由于时间戳和机器标识的组合可以保证生成的ID在全局范围内是唯一的。

2不足

事务是指一系列操作这些操作要么全部执行要么全部不执行是一个不可分割的工作单位。

1原子性Atomicity事务中的所有操作要么全部成功要么全部失败回滚不会处于中间状态。

2一致性Consistency事务必须使数据库从一个一致性状态合法的状态转移到另一个一致性状态合法的状态。

举例1A账户有200元转账300元出去此时A账户余额为-100元不合法。

你自然就发现此时数据是不一致的为什么呢因为你定义了一个状态余额这列必须0。

举例2A账户有200元转账50元给B账户A账户的钱扣了但是B账户因为各种意外余额并没有增加不合法。

你也知道此时的数据是不一致的为什么呢因为你定义了一个状态要求AB的总余额必须不变。

举例3在数据表中我们将姓名字段设置为唯一性约束这时当事务进行提交或者事务发生回滚的时候如果数据表的姓名不唯一就破坏了事务的一致性要求。

3隔离性Isolation一个事务的执行不能被其他事务干扰。

即一个事务内部的操作及使用的数据对并发的其他事务是隔离的。

4持久性Durability一个事务一旦提交它对数据库中数据的改变就是永久性的。

34.

A修改了另一个未提交事务B修改过的数据然后B回滚操作那就意味着发生了脏写没有提交的数据就是脏数据。

2脏读

一个事务读取了另一个未提交事务的数据。

例如事务A读取了事务B更新的数据然后B回滚操作那么A读取到的数据是脏数据没有提交的数据就是脏数据。

一个事务在一段时间内两次读取的数据不一致。

例如事务A多次读取同一数据事务B在事务A多次读取的过程中对数据作了更新并提交导致事务A多次读取同一数据时结果不一致。

如果解决了“不可重复读”那么事务A读取的student信息仍然保持不变。

4幻读

一个事务按照相同的查询条件重新读取以前检索过的数据却发现其他事务插入了满足条件的新数据。

例如事务A从一个表中读取了一个字段然后事务B在该表中插入了一些新的行。

之后,

如果事务A再次读取同一个表就会多出几行。

那就意味着发生了幻读。

MySQL默认是解决了可重复读如果事务B插入了“赵六”的信息事实上事务A仍然查询不到“赵六”的信息了这是MySQL的特性但是无法插入“赵六”数据仍然有“幻读”。

35.

按照严重性来排一下序不论是哪种隔离级别都不允许脏写的情况发生。

READ

只允许读取已经提交的数据变更可以避免脏读但不可重复读和幻读仍可能发生。

REPEATABLE

确保在事务内可以多次读取同样的数据结果避免脏读和不可重复读但幻读仍可能发生。

这是MySQL默认的隔离级别。

完全隔离确保事务可以从数据库中检索到的数据是一致的如同事务是顺序执行的一样避免了脏读、不可重复读和幻读但会大大降低系统的并发能力。

READ而

Log保证的是事务的持久性用于记录事务对数据库所做的修改操作的日志用于在系统发生故障时重新执行这些操作。

。

Redo

File然后定时刷新到磁盘的数据库内这样可以减少磁盘的随机写减少了写操作的延迟。

Redo

Pos和CheckPoint维护由于环形队列的空间是有限的当环形队列快要满的时候就会阻塞将Redo

Log

Log是用于记录事务发生之前的数据状态的日志用于在事务执行失败或系统发生故障时回滚事务对数据库所做的修改确保事务的原子性。

当对一条记录改动的时候如插入了一条数据Undo

38.

log主要存储SQL语句主要用于主从复制例如读写分离和主从备份。

Bin

STATEMENT格式记录的是SQL语句本身。

优点是日志文件较小便于传输和存储缺点是可能导致主从数据不一致。

ROW格式记录的是数据行的实际变化即每行数据的变化前后的完整记录。

优点是可以确保主从数据的一致性缺点是日志文件可能会非常大。

MIXED格式结合了STATEMENT和ROW格式的优点。

对于大多数操作它使用STATEMENT格式记录但在必要时如非确定性操作会自动切换到ROW格式。

这种格式旨在提供一种平衡既减少了日志文件的大小又保持了数据一致性。

39.

Log确保了事务的持久性即使在数据库崩溃时也能恢复未写入磁盘的数据更改而Bin

Log主要用于数据复制和恢复两者的写入时机和作用不同共同保障了数据库的完整性和高可用性。

Bin

Log是在事务提交后才记录的如果数据库在事务执行过程中崩溃那么尚未提交到Bin

40.

通过设置innodb_flush_log_at_trx_commit参数可以控制Redo

1当设置为1时默认值每个事务提交时InnoDB都会执行一个显式的刷盘操作确保Redo

Log记录被立即写入磁盘。

这是最安全的设置可以保证事务的ACID属性。

2当设置为0时InnoDB不会在每次事务提交时进行刷盘操作而是依赖于操作系统的调度。

这可能会在系统崩溃时丢失最后一个事务。

Log写入文件系统缓存但不会立即执行fsync()操作而是在每秒执行一次刷盘操作。

41.

WAL是一种日志记录机制它要求所有的数据修改操作如插入、更新、删除都必须先写入日志文件然后才应用到数据库的实际数据页上。

WAL保证了数据恢复确保了所有的修改操作都有记录在系统崩溃或断电的情况下未写入磁盘的数据可能会丢失而可以根据Redo

Log

File的刷盘写到了哪里一边写一边后移Checkpoint是当前要擦除的位置记录了内存到磁盘的数据页写到哪了也是往后推移。

随着新日志的写入和应用Write

Pos和Checkpoint都会不断向后移动。

在系统崩溃时数据库会从Checkpoint开始重放至Write

43.

锁是实现数据库并发控制的重要手段可以保证数据库在多人同时操作时能够正常运行。

MySQL提供了全局锁、行级锁、表级锁。

其中InnoDB支持表级锁和行级锁MyISAM只支持表级锁。

44.

1死锁是指多个进程因为竞争资源而造成相互等待的情况若无外力作用这些进程都无法向前推进。

2产生死锁的原因①资源竞争多个事务同时访问同一资源并且请求的资源总量超过了系统可用资源量。

②资源持有等待事务持有部分资源等待获取其他事务持有的资源而其他事务也在等待该事务释放其持有的资源。

③事务不按顺序访问资源多个事务访问同一批资源但访问顺序不一致可能导致循环等待。

④锁定资源不释放事务在等待其他资源时没有释放自己已经持有的资源。

3死锁产生的必要条件①互斥条件进程对所获得的资源必须互斥访问即同一时刻只能有一个进程访问②请求和保持进程在至少获得一个资源的情况下又提出新的资源需求③不可抢占进程所获得的资源在其完成之前不可被其他进程抢占④循环等待链当发生死锁时必然存在一个进程——资源循环等待链。

45.

Monitor打开锁监控每15s输出一次日志。

使用完毕后建议关闭否则会影响数据库性能。

2对待死锁常见的两种策略通过innodb_lock_wait_timeout来设置超时时间一直等待直到超时使用死锁检测处理死锁程序通过wait-for

graph算法来主动进行死锁检测发现死锁之后主动回滚死锁中的某一个事务让其它事务继续执行。

46.

processlist命令来查看当前正在运行的所有数据库连接和线程的状态从而发现是否有线程处于“Waiting

for

默认是使用设置死锁时间来让死锁超时的策略默认innodb_lock_wait_timeout设置的时长是

48.

全局锁就是对整个数据库实例加锁它的典型使用场景就是做全库逻辑备份和数据迁移。

50.

排它锁用于独占访问数据项即加锁的数据项不能被其他事务读取或修改。

对于InnoDB引擎来说共享锁和排它锁可以加在表上也可以加在行上。

51.

1表级的共享锁和排它锁。

当一个事务对表中的数据执行读取操作时会施加共享锁其他事务也可以施加共享锁但无法进行更新操作直到所有共享锁被释放。

当一个事务需要对表中的数据进行写操作时会施加排他锁这个锁会阻塞其他事务对这个表进行读或写操作直到排他锁被释放。

2意向锁。

当事务准备在表中的某些行上施加共享锁时它首先会在表级别施加一个意向共享锁。

当事务准备在表中的某些行上施加排他锁时它会在表级别施加一个意向排他锁。

意向锁的主要目的是为了在一个事务中表明对表中的行记录进行加锁的意图这可以使得其他事务更高效地判断是否可以安全地获取表中的行锁。

例如对整个表添加表锁时先检查是否有意向锁无须检查每个行是否有行锁。

3元数据锁。

元数据锁确保了在事务执行期间表的结构不会发生改变从而保证数据的一致性。

4自增锁。

自增锁的作用是在执行插入操作时确保为AUTO_INCREMENT列分配的唯一序列号不会因为并发插入操作而产生冲突。

52.

自增锁AUTO-INC锁是MySQL中InnoDB存储引擎为了维护带有AUTO_INCREMENT属性列的一致性和顺序性而采用的一种表级锁。

当多个事务同时插入数据时自增锁能够保证每个事务获取到的自增值是连续且不重复的从而确保了数据的一致性。

自增锁有三种模式传统锁定模式会为所有插入操作加AUTO-INC锁连续锁定模式只为批量插入加AUTO-INC锁交错锁定模式是MySQL8.0之后的默认模式它不使用AUTO-INC锁允许多个语句交错生成自增值可能会导致自增值不连续。

53.

Lock也称为行锁它锁定的是索引记录。

如果表没有设置索引那么InnoDB会使用隐式的主键索引来锁定记录。

记录锁只会锁定涉及的特定行不会影响到其他行。

2间隙锁Gap

Lock当使用范围条件而不是相等条件检索数据时并请求共享或排他锁时InnoDB会使用间隙锁。

间隙锁锁定的是索引记录之间的间隙目的是防止其他事务在这个间隙内插入新的记录从而避免幻读问题。

3临键锁Next-Key

Lock临键锁是记录锁和间隙锁的组合它不仅锁定一个记录还锁定这个记录前面的间隙。

在InnoDB的可重复读隔离级别下默认使用临键锁来锁定记录这样可以有效防止幻读现象。

54.

悲观锁假设在数据被访问时极有可能发生冲突因此在数据处理之前就加锁直到事务完成后再释放锁。

在写多读少的情况下悲观锁可能是更合适的选择。

实现方式

阻塞如果一个事务持有了某数据的锁其他尝试访问该数据的事务将会被阻塞直到锁被释放。

2乐观锁

乐观锁假设在数据被访问时发生冲突的可能性较低因此不会在操作开始时就加锁而是在数据提交时检查是否有冲突。

在读多写少的情况下乐观锁通常是更好的选择。

实现方式

通常通过版本号或时间戳机制实现。

每次读取数据时会记录数据的版本号或时间戳更新数据时检查版本号或时间戳是否发生变化。

如果变化了表示数据在读取后被其他事务修改更新操作将失败。

安全性相对于悲观锁来说乐观锁在高度并发环境下可能会遇到更多的一致性问题。

MVCC

Log如果事务中的某条SQL语句执行失败那么MySQL会使用undo日志来撤销该事务已经执行的所有操作确保数据库状态回到事务开始之前。

2一致性

一致性是事务的最终目标它确保事务执行的结果是数据库状态的合法状态。

MySQL通过原子性、隔离性和持久性来间接保证一致性。

3隔离性

MVCCMVCC允许数据在多个事务中可见而不会互相干扰这样不同的读写操作可以并发执行而不会互相阻塞。

隔离级别MySQL通过设置不同的隔离级别来控制事务之间的可见性和交互从而提供不同程度的隔离性。

4持久性

Log当事务提交时InnoDB存储引擎会将事务对数据页的所有修改记录到Redo

56.

1快照读就是读取的是快照数据。

快照读通常用于SELECT语句它不会对读取的数据加锁从而避免了脏读、不可重复读和幻读的问题。

2当前读就是读的是最新数据而不是历史的数据。

在执行当前读时InnoDB存储引擎会对读取的数据加锁确保在事务提交前其他事务不能对这些数据进行修改。

适用于UPDATE、DELETE和SELECT

FOR

MVCC多版本并发控制允许多个事务同时对同一数据进行读取和修改而不会相互干扰。

MySQL默认的隔离级别是REPEATABLE

READ可以通过MVCC解决脏读、不可重复读和幻读问题。

的MVCC的实现原理是依靠隐含字段、Undo

Log版本链和Read

ID指的是修改该行记录的事务ID。

每次一个事务修改一行数据时系统会将这个事务的ID写入该字段。

2回滚指针Rollback

Pointer这个字段指向该行数据的前一个版本。

当数据被修改时并不是直接覆盖原有数据而是插入一条新记录并通过回滚指针链接到旧记录。

这样即使有新的事务在操作数据旧的事务依然可以通过回滚指针访问到旧版本的数据。

59.

版本链是指在MVCC中每个数据行可能存在多个版本这些版本按照时间顺序连成一条链。

每个版本代表了一个事务对数据行的修改结果。

当事务读取数据时会根据事务的隔离级别和事务开始的时间点来决定可以读取哪个版本的数据。

60.

View是事务执行SQL语句时用来确定哪些版本的数据是可见的快照。

对于RC隔离级别每当执行一个普通的SELECT操作时InnoDB会为这个SELECT语句创建一个Read

对于RR隔离级别Read

View是在事务的第一个一致性读SELECT操作时创建的并且在事务的整个过程中都使用这个Read

View这就是可重复读的隔离级别能够确保事务中的一致性读的原因。

61.

ReadRR隔离级别下解决幻读问题主要依赖于以下三个机制快照读、行锁和间隙锁。

1快照读。

大多数实现RR隔离级别的数据库系统使用MVCC机制的快照读。

它通过保存数据行的多个版本来支持并发读取使得每个事务看到的是数据库的一个快照从而避免了读取到其他事务插入或删除的行。

2行锁。

当事务更新某行数据时数据库会锁定这一行直到事务完成。

这样其他事务就不能修改这行数据从而保证了事务读取的一致性。

3间隙锁。

在RR隔离级别下除了对索引记录加锁外还会对索引记录之间的间隙加锁。

如果一个事务在某个范围内读取数据其他事务不能在这个范围内插入新的记录从而避免了幻读。

【MySQL调优】如何进行MySQL调优一篇文章就够了-CSDN博客

62.

如果查询中只涉及某些字段而这些字段恰好建立了索引那么SELECT

字段1,

字段2的访问方法查询将能够更有效地利用索引。

如果使用SELECT

*可能会涉及到不在索引中的字段导致索引的覆盖扫描无法使用从而转变为更耗费资源的全表扫描。

腾讯二面你对禁止使用

Log如果事务中的某条SQL语句执行失败那么MySQL会使用undo日志来撤销该事务已经执行的所有操作确保数据库状态回到事务开始之前。

2一致性

一致性是事务的最终目标它确保事务执行的结果是数据库状态的合法状态。

MySQL通过原子性、隔离性和持久性来间接保证一致性。

3隔离性

MVCCMVCC允许数据在多个事务中可见而不会互相干扰这样不同的读写操作可以并发执行而不会互相阻塞。

隔离级别MySQL通过设置不同的隔离级别来控制事务之间的可见性和交互从而提供不同程度的隔离性。

4持久性

Log当事务提交时InnoDB存储引擎会将事务对数据页的所有修改记录到Redo

64.

BY确实都可能使用索引来提高查询效率如果查询优化的结果是相同的那么它们的效率可能会相近。

BY更高效因为DISTINCT只需要找出不同的值而不需要进行排序或其他额外的分组操作。

相比之下GROUP

BY可能会在内存中进行排序这会增加额外的计算开销。

因此在没有索引支持时选择DISTINCT通常可以避免不必要的排序操作提高查询性能。

65.

1使用EXPLAIN分析查询计划通过执行EXPLAIN命令可以查看MySQL是如何执行SQL查询的包括是否使用了索引、扫描的行数、连接类型等这有助于发现查询性能瓶颈。

2分析慢查询日志开启MySQL的慢查询日志功能可以记录执行时间超过设定阈值的查询语句通过分析这些日志可以找到慢查询的具体原因。

3利用开源工具如Arthas、Prome***us、Skywalking跟踪哪条SQL查询慢。

4检查索引使用情况确认查询中涉及的字段是否都有适当的索引。

如果没有索引或者索引使用不当可能会导致全表扫描从而降低查询速度。

5优化SQL语句检查SQL语句是否可以优化比如减少子查询、避免使用SELECT

6检查数据库表结构确保表结构设计合理比如避免过多的冗余数据、合理使用数据类型、适当的分区等。

7服务器性能监控监控服务器的CPU、内存、磁盘I/O等性能指标确定是否是服务器资源不足导致查询变慢。

8查询缓存检查MySQL的查询缓存是否开启以及查询是否能够被缓存命中。

9数据库配置优化检查MySQL的配置文件如my.cnf或my.ini确保配置参数如innodb_buffer_pool_size、query_cache_size等适合当前的工作负载。

66.

慢查询日志是MySQL数据库中记录执行时间超过指定阈值的查询语句的日志。

通过分析慢查询日志可以发现数据库性能瓶颈从而优化SQL语句或数据库结构。

要使用慢查询日志首先需要开启慢查询日志功能并设置合适的阈值例如将long_query_time参数设置为大于0的值。

然后通过查看慢查询日志文件分析日志中的慢SQL语句对其进行优化比如增加索引、优化查询条件等。

67.

1对于外连接将小表作为驱动表大表作为被驱动表以减少外层循环的次数。

5避免使用子查询尽量使用JOIN代替减少查询的复杂度和计算开销。

68.

BUFFER来存储然后再对被驱动表进行全表扫描匹配。

如果小表被用作驱动表那么整个小表可以一次性装入JOIN