路由器 东莞网站建设,网站qq访客 原理,兰州新区规划建设局网站,做代金券的网站目录
MySQL基础部分
1. SELECT语句完整的执行顺序是什么#xff1f;
2. 说一说内连接和外连接。
3. 请说说数据库三大范式。
4. 请你说说视图的作用#xff0c;视图可以更改么#xff1f;
架构
5. 请你说一说MySQL架构。
6. 请你说说一条SQL语句的执行过程#xff…目录
MySQL基础部分
1. SELECT语句完整的执行顺序是什么
2. 说一说内连接和外连接。
3. 请说说数据库三大范式。
4. 请你说说视图的作用视图可以更改么
架构
5. 请你说一说MySQL架构。
6. 请你说说一条SQL语句的执行过程例如UPDATE user SET namexiaoming WHERE id1。
7. 如果是InnoDBUPDATE语句在执行器调用引擎时都发生了什么
索引
8. 请你说说索引的优缺点
9. 请你介绍MySQL数据库的索引原理它是如何提高查询性能的
10. 请你简单介绍你知道的数据库索引。
11. 请你说说Hash索引和B树索引说说他们的区别。
12. 介绍下主键索引主键索引和聚集索引有什么区别
13. 什么是聚簇索引和非聚簇索引
14. 什么是回表优化器如何决定回表还是全表查询
15. 如何防止回表
16. 什么是覆盖索引
17. 什么是索引条件下推
18. 请你介绍B树和B树的区别。
19. MySQL为什么选择B树而不是B树作为索引数据结构。
20. 如何合理设计索引如何选择合适的字段作索引
22. 索引什么时候失效
存储引擎
23. 有哪些常见的存储引擎
24. 说说你对InnoDB引擎的理解。
25. 说说你对MyISAM引擎的理解。
26. 如果表中绝大多数都只是读查询应该用哪个引擎
27. InnoDB、MyISAM和Memory的区别是什么
28. 什么是InnoDB的页、区、段
29. InnoDB为什么推荐使用自增主键
30. 在分布式架构中为什么又不推荐使用自增ID作为主键
31. UUID可以用来作为主键吗存在哪些问题
32. 雪花算法可以生成主键请请你描述雪花算法的原理能介绍一下它有哪些优势和不足该如何解决这些不足呢
事务
33. 什么是事务事事务的ACID特性是什么
34. 事务的数据并发问题有什么
35. 解释MySQL中的事务隔离级别以及它们如何影响并发。
事务日志
36. 请你说说Redo Log。
37. 请你说说Undo Log。
38. 请你说说Bin Log。
39. 为什么有了Bin Log还需要Redo Log
40. 请你说说Redo Log的三种刷盘策略。
41. 为何不直接将修改的数据写入磁盘而是要Write Ahead Log呢
42. 说说Redo Log的Checkpoint机制。
锁
43. 什么是锁MySQL中提供了几类锁
44. 什么是死锁产生死锁的原因是什么死锁产生的必要条件是什么
45. 如何查看死锁如何处理死锁
46. 查询结果长期不返回是什么原因
47. InnoDB默认是如何对待死锁的
48. 如何开启死锁检测
49. 什么是全局锁它的应用场景有哪些
50. 请你解释一下共享锁和排它锁。
51. 请你说说MySQL有什么表锁
52. 你了解自增锁吗
53. InnoDB有哪几类行锁
54. 请你解释一下悲观锁和乐观锁。
MVCC
55. ACID是什么保证的
56. 解释一下什么是快照读和当前读。
57. 谈谈你对MVCC的了解
58. 谈谈MVCC的隐藏字段
59. 谈谈MVCC的Undo Log版本链
60. 谈谈MVCC的Read ViewRead View生成的时机是什么时候
61. RR是如何解决幻读的
调优
62. 为什么避免使用SELECT *而选用SELECT 字段的语法
63. MySQL中1000万条数据如何快速分页查询
64. MySQL中的DISTINCT和GROUP BY哪个更高效
65. 如果发现SQL查询慢怎么排查和优化
66. MySQL中的慢查询日志是什么如何使用它来优化性能
67. 如何优化MySQL中的关联查询以提高查询效率
68. 为什么建议小表驱动大表 MySQL重点索引、事务、锁、MVCC
MySQL基础部分
1. SELECT语句完整的执行顺序是什么
FROM - WHERE - GROUP BY - HAVING - SELECT 的字段 - DISTINCT - ORDER BY - LIMIT
SELECT DISTINCT player_id, player_name, count(*) as num # 顺序 5
FROM player JOIN team ON player.team_id team.team_id # 顺序 1
WHERE height 1.80 # 顺序 2
GROUP BY player.team_id # 顺序 3
HAVING num 2 # 顺序 4
ORDER BY num DESC # 顺序 6
LIMIT 2 # 顺序 7
2. 说一说内连接和外连接。
内连接INNER JOIN仅返回两个表中匹配的行而外连接分为左外连接LEFT JOIN和右外连接RIGHT JOIN左外连接会返回左表的所有行以及右表中匹配的行右外连接则返回右表的所有行以及左表中匹配的行。在优化查询时内连接可以通过小表驱动大表来减少扫描行数而外连接则通常以左表为驱动表通过在右表的连接字段上建立索引来提高查询效率。
3. 请说说数据库三大范式。
第一范式1NF要求表中的所有字段都是原子性的即每个字段只包含不可分割的最小数据单位表中没有重复的行。
第二范式2NF在满足1NF的基础上要求表中的非主键字段必须完全依赖于主键不能仅依赖于主键的一部分即表中不存在部分依赖。
第三范式3NF在满足2NF的基础上要求表中的非主键字段必须直接依赖于主键不能存在传递依赖即非主键字段之间不能相互依赖。
4. 请你说说视图的作用视图可以更改么
1视图在MySQL中主要有以下作用
① 简化复杂的SQL操作
视图可以将复杂的查询语句封装起来用户可以通过简单的查询来获取所需的数据。
② 数据安全
视图可以限制用户访问数据表中的一部分数据只暴露必要的数据从而提供一定程度的数据安全性。
③ 数据独立性
视图可以屏蔽底层表结构的变化用户不必关心底层表的具体结构只需操作视图即可。
2是否可以更改
视图本身是一个虚拟表它不存储数据只保存逻辑而是基于底层表的结果集。关于视图是否可以更改这取决于视图的定义。
架构
5. 请你说一说MySQL架构。 MySQL的架构可以分为两层服务器层和存储引擎层。
1服务器层包括连接器、分析器、优化器、执行器。连接器负责处理客户端与MySQL服务器之间的连接包括管理连接、权限认证。分析器负责解析客户端发送的SQL语句包括词法分析、语法分析。优化器负责选择索引、优化SQL、为INNER JOIN选择驱动小表。执行器负责调用执行引擎。
2存储引擎层存储引擎用来具体执行SQL常见的存储引擎有InnoDB、MyISAM、Memory等。
6. 请你说说一条SQL语句的执行过程例如UPDATE user SET namexiaoming WHERE id1。
首先客户端与MySQL连接器进行连接然后分析器经过词法分析和语法分析然后到达优化器优化器生成执行计划选择索引这里选择主键索引id然后就到达了执行器执行器调用存储引擎存储引擎返回结果。
7. 如果是InnoDBUPDATE语句在执行器调用引擎时都发生了什么 执行器调用存储引擎找到id1的数据由于id是主键直接在主键索引树上查找。如果这行数据所在的数据页在内存中直接返回给执行器结果就可以了。如果不在内存中会从磁盘中读取数据到内存再返回给执行器结果。然后执行器拿到存储引擎返回的这行数据把name设置成xiaoming 得到新的数据再调用执行引擎的接口写入这行数据。接着执行引擎将这行新的数据更新到内存中同时将更新的记录写到Redo Log中此时Redo Log处于prepare的状态然后再告诉执行器随时可以提交事务执行器会根据操作生成Bin Log然后把Bin Log写入磁盘写入成功执行器会调用存储引擎提交事务的接口存储引擎把Redo Log的prepare状态改成commit状态到此整个更新就完成了。 索引
8. 请你说说索引的优缺点
1优点保证数据唯一性、检索速度快、加快表的连接、优化分组和排序
① 提高查询速度索引可以大幅提升数据检索的效率类似于图书馆的索引目录能够快速定位到所需的数据从而减少数据库的I/O操作提高查询性能。
② 保证数据唯一性通过创建唯一索引可以确保表中每一行数据的唯一性避免数据重复。
③ 加速表连接在执行涉及多个表的联合查询时索引可以加速表之间的连接操作特别是对于具有外键关系的表。
④ 优化分组和排序在进行分组GROUP BY和排序ORDER BY操作时索引可以显著减少查询时间降低CPU的使用率。
2缺点耗费时间和空间增删改的效率降低
① 时间和空间成本索引的创建和维护需要消耗时间尤其是在数据量大的情况下索引的构建和维护会更加耗时。同时索引本身需要占用额外的存储空间。
② 降低数据维护效率当对表中的数据进行插入、删除和更新操作时索引也需要相应地进行更新这可能会降低这些写操作的性能。
9. 请你介绍MySQL数据库的索引原理它是如何提高查询性能的
MySQL数据库的索引原理是基于数据结构中的B树实现的。索引能够提高查询效率其核心思想是通过快速定位来减少磁盘I/O次数。当执行查询操作时MySQL会使用索引来快速找到数据行而不是遍历整个数据表。B树结构使得索引在查找、插入和删除操作中都能保持较高的效率因为它的中间节点不存储数据只有叶子节点才存储数据并且叶子节点之间是通过指针连接的便于范围查询。
10. 请你简单介绍你知道的数据库索引。
1B树索引是一种自平衡的树结构它允许搜索、顺序访问和插入/删除操作但相比于B树它的磁盘I/O操作较多。
2B树索引是MySQL中最常用的索引类型它的所有数据都在叶子节点并且叶子节点之间有指针相连适合范围查询。
3哈希索引基于哈希表实现适用于等值查询但不支持排序和范围查询。
4全文索引用于全文搜索能够快速检索文本中的关键词。
5空间索引则是为空间数据类型设计的适用于地理空间数据的查询。每种索引都有其特定的应用场景和优缺点。
11. 请你说说Hash索引和B树索引说说他们的区别。
1Hash和B树索引的简要介绍
Hash索引底层就是Hash表进行查询时调用Hash函数获取到相应的键值对应地址然后回表查询获得实际数据。B树索引底层实现原理是多路平衡查找树对于每一次的查询都是从根节点出发查询到叶子节点方可以获得所查键值最后查询判断是否需要回表查询。
2Hash索引的特点
① Hash进行等值查询更快但无法进行范围查询。因为经过Hash函数建立索引之后索引的顺序与原顺序无法保持一致故不能支持范围查询。同理也不支持使用索引进行排序。
② Hash不支持模糊查询以及多列索引的最左前缀匹配因为Hash函数的值不可预测如AA和AB的算出的值没有相关性。
③ Hash任何时候都避免不了回表查询数据。
④ 虽然在等值上查询效率高但性能不稳定因为当某个键值存在大量重复时产生Hash碰撞此时查询效率反而可能降低。
附InnoDB不支持Hash索引Memory支持Hash索引
3B树索引的特点
① B树本质是一棵查找树自然支持范围查询和排序。
② 在符合某些条件聚簇索引、覆盖索引等时候可以只通过索引完成查询不需要回表。
③ 查询效率比较稳定因为每次查询都是从根节点到叶子节点且为树的高度。
12. 介绍下主键索引主键索引和聚集索引有什么区别
1主键索引
主键索引是一种特殊的唯一索引用于确保表中每一行数据的唯一性。在MySQL中当定义一个主键时系统会自动创建一个主键索引。主键索引可以加快查询速度因为它们提供了快速的数据访问路径。
2区别
主键索引和聚集索引的区别在于主键索引是一种约束用于保证数据的唯一性而聚集索引是一种索引存储方式它决定了表中数据的物理存储顺序。在InnoDB存储引擎中通常主键索引就是聚集索引因为InnoDB会按照主键的顺序来存储数据。如果一个表没有主键InnoDB会选择一个唯一的索引作为聚集索引或者隐式地创建一个。
13. 什么是聚簇索引和非聚簇索引
答案
1聚簇索引是指数据的物理存储顺序与索引顺序相同即索引项的顺序与表中记录的物理顺序一致。在聚簇索引中叶子节点直接存储了数据行。
2非聚簇索引的物理存储顺序与索引顺序不同叶子节点存储的是数据行的地址需要根据地址再次查询才能获取到数据行。
扩充请你进一步说说聚簇索引和非聚簇索引的数据结构和性能。
1数据结构
聚簇索引和非聚簇索引底层都是根据B树的数据结构实现的。
2性能
由于数据行和索引顺序一致对于范围查询和排序操作聚簇索引通常有更好的性能因为不需要额外的查找。对于非聚簇索引查询需要先在索引中找到对应的数据行指针然后根据指针去数据页中找到实际的数据可能会增加额外的IO开销。
3索引更新
由于数据的物理顺序依赖于聚簇索引因此当插入、删除或更新操作导致行移动时可能会引起大量的数据移动从而降低性能。非聚簇索引的更新不会引起数据行的物理移动因此通常对性能的影响较小。
4主键索引
在MySQL的InnoDB存储引擎中主键通常会自动创建为聚簇索引。
而在MyISAM存储引擎中即使主键也存在索引都是非聚簇的。
14. 什么是回表优化器如何决定回表还是全表查询
回表是指在非聚簇索引查询过程中当我们通过索引找到对应的数据行地址后需要回到数据表的聚簇索引中去查询完整的记录。
举一个例子例如SELECT * FROM table WHERE key1 AND key1000000; key是一个二级索引。
如果扫描的二级索引行数特别多不如全表扫描了那优化器就会选择全表扫描如果是有加上LIMIT 10这样的限制那么回表次数就少惹优化器可能就会选择回表。
15. 如何防止回表
如果一个索引包含了查询中需要的所有列那么这个索引就被称为“覆盖索引”。创建索引时可以包含查询中需要的所有列这样查询就可以直接通过索引返回结果而不需要回表。
如果查询条件中已经包含了主键那么直接使用主键进行查询不需要回表。
16. 什么是覆盖索引
覆盖索引是指一个索引包含了查询所需的所有字段的值。例如SELECT id, name FROM user WHERE nameJack为name建立了索引同时id也是主键。优点有避免InnoDB表的二次查询回表把随机I/O变成顺序I/O。
17. 什么是索引条件下推
索引条件下推ICP是一种数据库查询优化技术。它允许数据库在索引查找过程中将部分WHERE子句的条件提前应用到索引扫描阶段从而减少需要访问的索引项和回表的数据行数。这样做的目的是提高查询速度减少数据库系统的I/O开销进而提升整体查询性能。
18. 请你介绍B树和B树的区别。
B树和B树都是平衡多路查找树。
1节点结构
B树每个节点都包含键Key和数据Value。键用于排序而数据是与键关联的实际信息。
B树非叶子节点只包含键用来进行索引不包含数据。所有的数据都存储在叶子节点中并且叶子节点之间是通过指针相连的形成了一个有序链表。
2查询效率
B树由于每个节点都可能包含数据查询可能在非叶子节点结束这可能导致更多的磁盘I/O操作。
B树查询总是从根节点到叶子节点因为所有数据都在叶子节点上这样可以减少磁盘I/O操作提高查询效率。
19. MySQL为什么选择B树而不是B树作为索引数据结构。
1B树查询效率更稳定
由于数据只存在在叶子结点上所以查找效率固定为O(log n)所以B树的查询效率相比B树更加稳定。
2减少磁盘I/O
B树的非叶子节点不存储实际数据只存储索引值这使得每个节点可以存储更多的索引从而降低了树的高度减少了磁盘I/O操作的次数。
3适合范围查找
B树的叶子节点是有序的这使得范围查找变得非常高效而B树的范围查找可能需要通过中序遍历效率较低。数据库操作中范围查找非常常见因此B树在这方面更具优势。
20. 如何合理设计索引如何选择合适的字段作索引
1选择唯一性索引
唯一性索引的值是唯一的可以更快速的通过该索引来确定某条记录。
2为常作为查询条件的字段建立索引
如果某个字段经常用来做查询条件那么该字段的查询速度会影响整个表的查询速度。因此为这样的字段建立索引可以提高整个表的查询速度。
3为经常需要排序、分组和联合操作的字段建立索引
经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段排序操作会浪费很多时间。如果为其建立索引可以有效地避免排序操作。
4限制索引的数目
每个索引都需要占用磁盘空间索引越多需要的磁盘空间就越大修改表时对索引的重构和更新很麻烦。
5小表不建议索引如数量级在百万以内
由于数据较小查询花费的时间可能比遍历索引的时间还要短索引可能不会产生优化效果。
6尽量使用数据量少的索引
如果索引的值很长那么查询的速度会受到影响。此时尽量使用前缀索引。
7删除不再使用或者很少使用的索引。
21. 哪些情况不适合索引
1对于不在查询条件WHERE子句中使用的字段无需创建索引因为索引不会被查询优化器利用。
2对于数据量较小的表索引可能不会带来显著的性能提升反而可能因为索引的维护而降低写入效率。
3在那些具有高度重复值的列上建立索引通常是不划算的因为索引的筛选效果不佳。
4对于频繁进行写操作如插入、更新、删除的表应避免创建过多索引以免降低写操作的性能。
5使用无序的值作为索引可能会导致索引效率低下因为索引的目的是快速定位数据而无序的值无法有效支持这一目的。
22. 索引什么时候失效
1最左前缀原则在使用复合索引时查询未遵循最左前缀原则。
2函数和运算避免在查询条件中对索引字段使用函数或运算。
3类型转换确保查询条件中的数据类型与字段定义的类型匹配避免隐式类型转换。
4模糊查询避免使用前导通配符的模糊查询如LIKE %value。
5OR条件确保OR连接的所有条件都有索引否则整个查询可能不会利用索引。
6不等号查询使用!或可能会使索引失效应谨慎使用。
7IS NOT NULLIS NOT NULL条件通常不会利用索引IS NULL会利用索引。
8字符集不一致保持数据库字符集的一致性推荐使用utf8mb4。
存储引擎
23. 有哪些常见的存储引擎
1MyISAMMyISAM曾经是MySQL5.5版本及之前的默认存储引擎。MyISAM表不支持事务行级锁和外键约束的功能。
2InnoDBMySQL5.5版本开始InnoDB成为了默认的存储引擎它提供了事务、行级锁机制和外键约束的功能。
3Memory这种类型的数据表只存在于内存中。它使用Hash索引所以数据的存取速度非常快。因为是存在于内存中所以这种类型常应用于临时表中。
24. 说说你对InnoDB引擎的理解。
InnoDB是MySQL默认的存储引擎。
InnoDB支持事务最重要的特性MyISAM是不支持事务的来保证数据的ACID特性原子性、一致性、隔离性、持久性。
InnoDB支持行级锁MyISAM支持表级锁。行级锁对比表级锁粒度更细提高了系统的并发性能。
InnoDB实现了外键约束。这使得数据库设计者能够通过外键关系建立表与表之间的关联保证了数据的参照完整性。
InnoDB使用的是B树索引B树检索速度较快磁盘IO相对较少。
InnoDB的数据和索引等存储在.idb和.frm文件中.frm在MySQL8.0之后数据合并到.idb里面而MyISAM的表数据信息存放在.myd表数据索引存放在.myi。
25. 说说你对MyISAM引擎的理解。
MyISAM是基于B树实现的但是它B树索引的叶子节点存储的不是数据本身而是数据的指针或叫做数据的地址。所以索引和数据是分开存储的。因此相当于每次查询都要进行回表操作。MyISAM支持表锁和全文索引。但是不支持行锁和事务。由于锁的粒度是表级别的因此适合用于读多写少的场景例如日志分析等等。因此可以将读操作分配给MyISAM存储引擎写操作分配给其他存储引擎。
26. 如果表中绝大多数都只是读查询应该用哪个引擎
如果业务主要是读查询可以选择MyISAM引擎。MyISAM引擎在读取操作上进行了优化因为它支持表级锁定减少了锁定开销并且在查询时不会因为写锁定而阻塞读操作这使其在只读或读多写少的场景下性能表现优异。此外MyISAM的存储格式使得它占用的空间更小索引的大小也更紧凑这有助于提高I/O效率。然而需要注意的是MyISAM不支持事务和行级锁定如果业务未来可能需要事务支持或高并发写操作那么MyISAM可能不是最佳选择。
27. InnoDB、MyISAM和Memory的区别是什么
从事务支持外键支持和锁机制的角度回答。 28. 什么是InnoDB的页、区、段
在InnoDB存储引擎中页是磁盘管理的最小单位通常大小为16KB用于存储数据和索引区是由连续的页组成默认大小为1MB是空间分配的基本单位段则是由多个区组成用于管理表和索引的数据。 29. InnoDB为什么推荐使用自增主键
自增主键的顺序性保证了新记录总是被添加到索引的末尾从而避免了频繁的页分裂减小了插入数据操作的开销。如果插入到两个数据中间可能会进行频繁的数据移动。有助于减少数据碎片使得数据存储更加连续。对于范围查询自增主键能够更好地利用索引的有序性提高查询效率。
30. 在分布式架构中为什么又不推荐使用自增ID作为主键
1分区写入热点使用自增ID时所有的写入操作都会集中在最新的分区上这会导致写入热点问题。一旦当前分区写满写入操作才会移动到下一个分区这种“尾部热点”现象会降低系统的写入性能。
2负载均衡困难自增ID的连续性使得负载均衡变得困难。在分布式系统中理想的状况是将请求均匀地分配到不同的服务器或分区上而自增ID无法实现这一点。
31. UUID可以用来作为主键吗存在哪些问题
1优点UUID几乎可以保证在全球范围内是唯一的这对于分布式系统尤其重要。UUID不容易被猜解因此可以提供一定程度的隐私保护。
2缺点对于InnoDB这类使用B树索引的存储引擎无序的UUID会导致页分裂和碎片化降低查询效率。
32. 雪花算法可以生成主键请请你描述雪花算法的原理能介绍一下它有哪些优势和不足该如何解决这些不足呢
原理是基于时间戳和机器标识生成一个64位的唯一ID。 1优势
全局唯一性由于时间戳和机器标识的组合可以保证生成的ID在全局范围内是唯一的。
有序性生成的ID按照时间戳的顺序递增便于排序和范围查询。
2不足
时间依赖如果系统时钟发生回拨可能会导致ID重复
事务
33. 什么是事务事事务的ACID特性是什么
事务是指一系列操作这些操作要么全部执行要么全部不执行是一个不可分割的工作单位。
1原子性Atomicity事务中的所有操作要么全部成功要么全部失败回滚不会处于中间状态。
2一致性Consistency事务必须使数据库从一个一致性状态合法的状态转移到另一个一致性状态合法的状态。
破坏一致性的例子
举例1A账户有200元转账300元出去此时A账户余额为-100元不合法。你自然就发现此时数据是不一致的为什么呢因为你定义了一个状态余额这列必须0。
举例2A账户有200元转账50元给B账户A账户的钱扣了但是B账户因为各种意外余额并没有增加不合法。你也知道此时的数据是不一致的为什么呢因为你定义了一个状态要求AB的总余额必须不变。
举例3在数据表中我们将姓名字段设置为唯一性约束这时当事务进行提交或者事务发生回滚的时候如果数据表的姓名不唯一就破坏了事务的一致性要求。
3隔离性Isolation一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的。
4持久性Durability一个事务一旦提交它对数据库中数据的改变就是永久性的。
34. 事务的数据并发问题有什么
1脏写 一个事务修改了另一个未提交事务的数据。例如如果事务 A修改了另一个未提交事务B修改过的数据然后B回滚操作那就意味着发生了脏写没有提交的数据就是脏数据。 此时A和B事务结束后查询既不是张三又不是李四。
2脏读
一个事务读取了另一个未提交事务的数据。例如事务A读取了事务B更新的数据然后B回滚操作那么A读取到的数据是脏数据没有提交的数据就是脏数据。 如果解决了“脏读”那么事务A读取的是之前的数据。
3不可重复读
一个事务在一段时间内两次读取的数据不一致。例如事务A多次读取同一数据事务B在事务A多次读取的过程中对数据作了更新并提交导致事务A多次读取同一数据时结果不一致。 如果解决了“不可重复读”那么事务A读取的student信息仍然保持不变。 4幻读
一个事务按照相同的查询条件重新读取以前检索过的数据却发现其他事务插入了满足条件的新数据。例如事务A从一个表中读取了一个字段然后事务B在该表中插入了一些新的行。之后, 如果事务A再次读取同一个表就会多出几行。那就意味着发生了幻读。 MySQL默认是解决了可重复读如果事务B插入了“赵六”的信息事实上事务A仍然查询不到“赵六”的信息了这是MySQL的特性但是无法插入“赵六”数据仍然有“幻读”。
35. 解释MySQL中的事务隔离级别以及它们如何影响并发。
按照严重性来排一下序不论是哪种隔离级别都不允许脏写的情况发生。
脏写 脏读 不可重复读 幻读
READ UNCOMMITTED读未提交
允许读取尚未提交的数据变更可能会导致脏读、不可重复读和幻读。
READ COMMITTEDRC读已提交
只允许读取已经提交的数据变更可以避免脏读但不可重复读和幻读仍可能发生。
REPEATABLE READRR可重复读
确保在事务内可以多次读取同样的数据结果避免脏读和不可重复读但幻读仍可能发生。这是MySQL默认的隔离级别。
SERIALIZABLE可串行化
完全隔离确保事务可以从数据库中检索到的数据是一致的如同事务是顺序执行的一样避免了脏读、不可重复读和幻读但会大大降低系统的并发能力。 MySQL支持以上四种隔离级别默认为REPEATABLE READ而 Oracle 只支持SERIALIZABLE级别和READ COMMITTED两种其中默认为读已提交。
事务日志 36. 请你说说Redo Log。
Redo Log保证的是事务的持久性用于记录事务对数据库所做的修改操作的日志用于在系统发生故障时重新执行这些操作。。
Redo Log由Redo Log Buffer和Redo Log File组成。
Redo Log采取了WAL机制也就是先写入Redo Log File然后定时刷新到磁盘的数据库内这样可以减少磁盘的随机写减少了写操作的延迟。Redo Log在内存中是一个环形队列写入的顺序是顺序写的由Write Pos和CheckPoint维护由于环形队列的空间是有限的当环形队列快要满的时候就会阻塞将Redo Log File的数据刷新到磁盘的数据库中。
37. 请你说说Undo Log。
Undo Log保证的是事务的原子性和一致性。
Undo Log是用于记录事务发生之前的数据状态的日志用于在事务执行失败或系统发生故障时回滚事务对数据库所做的修改确保事务的原子性。当对一条记录改动的时候如插入了一条数据Undo Log就要根据主键删除一条数据。Undo Log的作用是回滚数据其次MVCC也是由Undo Log保证的。
38. 请你说说Bin Log。
Bin log主要存储SQL语句主要用于主从复制例如读写分离和主从备份。
Bin Log有三种不同的日志格式
STATEMENT格式记录的是SQL语句本身。优点是日志文件较小便于传输和存储缺点是可能导致主从数据不一致。
ROW格式记录的是数据行的实际变化即每行数据的变化前后的完整记录。优点是可以确保主从数据的一致性缺点是日志文件可能会非常大。
MIXED格式结合了STATEMENT和ROW格式的优点。对于大多数操作它使用STATEMENT格式记录但在必要时如非确定性操作会自动切换到ROW格式。这种格式旨在提供一种平衡既减少了日志文件的大小又保持了数据一致性。
39. 为什么有了Bin Log还需要Redo Log
有了Bin Log还需要Redo Log因为Redo Log确保了事务的持久性即使在数据库崩溃时也能恢复未写入磁盘的数据更改而Bin Log主要用于数据复制和恢复两者的写入时机和作用不同共同保障了数据库的完整性和高可用性。
Bin Log是在事务提交后才记录的如果数据库在事务执行过程中崩溃那么尚未提交到Bin Log的事务更改将会丢失因为没有Redo Log来保证这些更改在崩溃后能够被重新应用。
40. 请你说说Redo Log的三种刷盘策略。
通过设置innodb_flush_log_at_trx_commit参数可以控制Redo Log的显式刷盘行为。
1当设置为1时默认值每个事务提交时InnoDB都会执行一个显式的刷盘操作确保Redo Log记录被立即写入磁盘。这是最安全的设置可以保证事务的ACID属性。
2当设置为0时InnoDB不会在每次事务提交时进行刷盘操作而是依赖于操作系统的调度。这可能会在系统崩溃时丢失最后一个事务。
3当设置为2时InnoDB会在事务提交时将Redo Log写入文件系统缓存但不会立即执行fsync()操作而是在每秒执行一次刷盘操作。
41. 为何不直接将修改的数据写入磁盘而是要Write Ahead Log呢
WAL是一种日志记录机制它要求所有的数据修改操作如插入、更新、删除都必须先写入日志文件然后才应用到数据库的实际数据页上。
WAL保证了数据恢复确保了所有的修改操作都有记录在系统崩溃或断电的情况下未写入磁盘的数据可能会丢失而可以根据Redo Log File日志来重放这些操作确保数据的完整性。
42. 说说Redo Log的Checkpoint机制。
在整个日志文件组中还有两个重要的属性分别是Write Pos和Checkpoint。Write Pos是当前记录的位置记录了Redo Log Buffer到Redo Log File的刷盘写到了哪里一边写一边后移Checkpoint是当前要擦除的位置记录了内存到磁盘的数据页写到哪了也是往后推移。随着新日志的写入和应用Write Pos和Checkpoint都会不断向后移动。在系统崩溃时数据库会从Checkpoint开始重放至Write Pos之间的日志记录以确保所有数据变更得以持久化。
锁
43. 什么是锁MySQL中提供了几类锁
锁是实现数据库并发控制的重要手段可以保证数据库在多人同时操作时能够正常运行。MySQL提供了全局锁、行级锁、表级锁。其中InnoDB支持表级锁和行级锁MyISAM只支持表级锁。
44. 什么是死锁产生死锁的原因是什么死锁产生的必要条件是什么 1死锁是指多个进程因为竞争资源而造成相互等待的情况若无外力作用这些进程都无法向前推进。
2产生死锁的原因①资源竞争多个事务同时访问同一资源并且请求的资源总量超过了系统可用资源量。②资源持有等待事务持有部分资源等待获取其他事务持有的资源而其他事务也在等待该事务释放其持有的资源。③事务不按顺序访问资源多个事务访问同一批资源但访问顺序不一致可能导致循环等待。④锁定资源不释放事务在等待其他资源时没有释放自己已经持有的资源。
3死锁产生的必要条件①互斥条件进程对所获得的资源必须互斥访问即同一时刻只能有一个进程访问②请求和保持进程在至少获得一个资源的情况下又提出新的资源需求③不可抢占进程所获得的资源在其完成之前不可被其他进程抢占④循环等待链当发生死锁时必然存在一个进程——资源循环等待链。
45. 如何查看死锁如何处理死锁
1使用命令 show engine innodb status 查看最近的一次死锁。InnoDB Lock Monitor打开锁监控每15s输出一次日志。使用完毕后建议关闭否则会影响数据库性能。
2对待死锁常见的两种策略通过innodb_lock_wait_timeout来设置超时时间一直等待直到超时使用死锁检测处理死锁程序通过wait-for graph算法来主动进行死锁检测发现死锁之后主动回滚死锁中的某一个事务让其它事务继续执行。
46. 查询结果长期不返回是什么原因
执行结果长期不返回可能是发生了死锁可以通过执行show processlist命令来查看当前正在运行的所有数据库连接和线程的状态从而发现是否有线程处于“Waiting for a lock”或“Locked”状态。
47. InnoDB默认是如何对待死锁的
InnoDB 默认是使用设置死锁时间来让死锁超时的策略默认innodb_lock_wait_timeout设置的时长是 50s。
48. 如何开启死锁检测
设置innodb_deadlock_detect on可以主动检测死锁在 Innodb 中这个值默认就是 on 开启的状态。
49. 什么是全局锁它的应用场景有哪些
全局锁就是对整个数据库实例加锁它的典型使用场景就是做全库逻辑备份和数据迁移。
50. 请你解释一下共享锁和排它锁。
共享锁允许多个事务同时读取同一数据项但不允许进行写操作。
排它锁用于独占访问数据项即加锁的数据项不能被其他事务读取或修改。
对于InnoDB引擎来说共享锁和排它锁可以加在表上也可以加在行上。 51. 请你说说MySQL有什么表锁 1表级的共享锁和排它锁。当一个事务对表中的数据执行读取操作时会施加共享锁其他事务也可以施加共享锁但无法进行更新操作直到所有共享锁被释放。当一个事务需要对表中的数据进行写操作时会施加排他锁这个锁会阻塞其他事务对这个表进行读或写操作直到排他锁被释放。
2意向锁。当事务准备在表中的某些行上施加共享锁时它首先会在表级别施加一个意向共享锁。当事务准备在表中的某些行上施加排他锁时它会在表级别施加一个意向排他锁。意向锁的主要目的是为了在一个事务中表明对表中的行记录进行加锁的意图这可以使得其他事务更高效地判断是否可以安全地获取表中的行锁。例如对整个表添加表锁时先检查是否有意向锁无须检查每个行是否有行锁。
3元数据锁。元数据锁确保了在事务执行期间表的结构不会发生改变从而保证数据的一致性。
4自增锁。自增锁的作用是在执行插入操作时确保为AUTO_INCREMENT列分配的唯一序列号不会因为并发插入操作而产生冲突。
52. 你了解自增锁吗
自增锁AUTO-INC锁是MySQL中InnoDB存储引擎为了维护带有AUTO_INCREMENT属性列的一致性和顺序性而采用的一种表级锁。当多个事务同时插入数据时自增锁能够保证每个事务获取到的自增值是连续且不重复的从而确保了数据的一致性。
自增锁有三种模式传统锁定模式会为所有插入操作加AUTO-INC锁连续锁定模式只为批量插入加AUTO-INC锁交错锁定模式是MySQL8.0之后的默认模式它不使用AUTO-INC锁允许多个语句交错生成自增值可能会导致自增值不连续。
53. InnoDB有哪几类行锁
1记录锁Record Lock也称为行锁它锁定的是索引记录。如果表没有设置索引那么InnoDB会使用隐式的主键索引来锁定记录。记录锁只会锁定涉及的特定行不会影响到其他行。
2间隙锁Gap Lock当使用范围条件而不是相等条件检索数据时并请求共享或排他锁时InnoDB会使用间隙锁。间隙锁锁定的是索引记录之间的间隙目的是防止其他事务在这个间隙内插入新的记录从而避免幻读问题。
3临键锁Next-Key Lock临键锁是记录锁和间隙锁的组合它不仅锁定一个记录还锁定这个记录前面的间隙。在InnoDB的可重复读隔离级别下默认使用临键锁来锁定记录这样可以有效防止幻读现象。
54. 请你解释一下悲观锁和乐观锁。
1悲观锁
① 基本概念
悲观锁假设在数据被访问时极有可能发生冲突因此在数据处理之前就加锁直到事务完成后再释放锁。在写多读少的情况下悲观锁可能是更合适的选择。
② 实现方式
通常通过数据库提供的锁定机制实现如行锁、表锁、读写锁等。
③ 特点
阻塞如果一个事务持有了某数据的锁其他尝试访问该数据的事务将会被阻塞直到锁被释放。
锁竞争可能导致较高的锁竞争和较少的并发。
安全性相对更安全因为锁机制保证了数据在事务中的隔离性。
2乐观锁
① 基本概念
乐观锁假设在数据被访问时发生冲突的可能性较低因此不会在操作开始时就加锁而是在数据提交时检查是否有冲突。在读多写少的情况下乐观锁通常是更好的选择。
② 实现方式
通常通过版本号或时间戳机制实现。每次读取数据时会记录数据的版本号或时间戳更新数据时检查版本号或时间戳是否发生变化。如果变化了表示数据在读取后被其他事务修改更新操作将失败。
③ 特点
非阻塞不会阻塞其他事务的执行只有在提交时才会检查冲突。
锁竞争减少了锁竞争提高了并发性。
安全性相对于悲观锁来说乐观锁在高度并发环境下可能会遇到更多的一致性问题。
MVCC
55. ACID是什么保证的
1原子性
Undo Log如果事务中的某条SQL语句执行失败那么MySQL会使用undo日志来撤销该事务已经执行的所有操作确保数据库状态回到事务开始之前。
2一致性
一致性是事务的最终目标它确保事务执行的结果是数据库状态的合法状态。MySQL通过原子性、隔离性和持久性来间接保证一致性。
3隔离性
① MVCCMVCC允许数据在多个事务中可见而不会互相干扰这样不同的读写操作可以并发执行而不会互相阻塞。
② 隔离级别MySQL通过设置不同的隔离级别来控制事务之间的可见性和交互从而提供不同程度的隔离性。
4持久性
Redo Log当事务提交时InnoDB存储引擎会将事务对数据页的所有修改记录到Redo Log中。如果系统发生故障MySQL可以使用Redo Log来恢复数据确保已经提交的事务所做的更改不会丢失。
56. 解释一下什么是快照读和当前读。
1快照读就是读取的是快照数据。快照读通常用于SELECT语句它不会对读取的数据加锁从而避免了脏读、不可重复读和幻读的问题。
2当前读就是读的是最新数据而不是历史的数据。在执行当前读时InnoDB存储引擎会对读取的数据加锁确保在事务提交前其他事务不能对这些数据进行修改。适用于UPDATE、DELETE和SELECT FOR UPDATE语句。
57. 谈谈你对MVCC的了解
MVCC多版本并发控制允许多个事务同时对同一数据进行读取和修改而不会相互干扰。MySQL默认的隔离级别是REPEATABLE READ可以通过MVCC解决脏读、不可重复读和幻读问题。的MVCC的实现原理是依靠隐含字段、Undo Log版本链和Read View实现的。MVCC适用于读已提交和可重复读的隔离级别。
58. 谈谈MVCC的隐藏字段
1事务IDTransaction ID指的是修改该行记录的事务ID。每次一个事务修改一行数据时系统会将这个事务的ID写入该字段。
2回滚指针Rollback Pointer这个字段指向该行数据的前一个版本。当数据被修改时并不是直接覆盖原有数据而是插入一条新记录并通过回滚指针链接到旧记录。这样即使有新的事务在操作数据旧的事务依然可以通过回滚指针访问到旧版本的数据。
59. 谈谈MVCC的Undo Log版本链
版本链是指在MVCC中每个数据行可能存在多个版本这些版本按照时间顺序连成一条链。每个版本代表了一个事务对数据行的修改结果。当事务读取数据时会根据事务的隔离级别和事务开始的时间点来决定可以读取哪个版本的数据。
60. 谈谈MVCC的Read ViewRead View生成的时机是什么时候
它是事务在某一时刻的“一致性读”视图。简而言之Read View是事务执行SQL语句时用来确定哪些版本的数据是可见的快照。
对于RC隔离级别每当执行一个普通的SELECT操作时InnoDB会为这个SELECT语句创建一个Read View用于确定语句可以看到哪些数据版本。
对于RR隔离级别Read View是在事务的第一个一致性读SELECT操作时创建的并且在事务的整个过程中都使用这个Read View这就是可重复读的隔离级别能够确保事务中的一致性读的原因。
61. RR是如何解决幻读的
在Repeatable ReadRR隔离级别下解决幻读问题主要依赖于以下三个机制快照读、行锁和间隙锁。
1快照读。大多数实现RR隔离级别的数据库系统使用MVCC机制的快照读。它通过保存数据行的多个版本来支持并发读取使得每个事务看到的是数据库的一个快照从而避免了读取到其他事务插入或删除的行。
2行锁。当事务更新某行数据时数据库会锁定这一行直到事务完成。这样其他事务就不能修改这行数据从而保证了事务读取的一致性。
3间隙锁。在RR隔离级别下除了对索引记录加锁外还会对索引记录之间的间隙加锁。如果一个事务在某个范围内读取数据其他事务不能在这个范围内插入新的记录从而避免了幻读。
调优
【MySQL调优】如何进行MySQL调优一篇文章就够了-CSDN博客
62. 为什么避免使用SELECT *而选用SELECT 字段的语法
1通过EXPLAIN查看执行计划
SELECT *的访问方法typeALL级别
SELECT 字段1, 字段2的访问方法typerange级别
2执行索引
如果查询中只涉及某些字段而这些字段恰好建立了索引那么SELECT 字段1, 字段2的访问方法查询将能够更有效地利用索引。如果使用SELECT *可能会涉及到不在索引中的字段导致索引的覆盖扫描无法使用从而转变为更耗费资源的全表扫描。
腾讯二面你对禁止使用 SELECT *有什么看法我被恶心到了。。_哔哩哔哩_bilibili
63. MySQL中1000万条数据如何快速分页查询
1原子性
Undo Log如果事务中的某条SQL语句执行失败那么MySQL会使用undo日志来撤销该事务已经执行的所有操作确保数据库状态回到事务开始之前。
2一致性
一致性是事务的最终目标它确保事务执行的结果是数据库状态的合法状态。MySQL通过原子性、隔离性和持久性来间接保证一致性。
3隔离性
① MVCCMVCC允许数据在多个事务中可见而不会互相干扰这样不同的读写操作可以并发执行而不会互相阻塞。
② 隔离级别MySQL通过设置不同的隔离级别来控制事务之间的可见性和交互从而提供不同程度的隔离性。
4持久性
Redo Log当事务提交时InnoDB存储引擎会将事务对数据页的所有修改记录到Redo Log中。如果系统发生故障MySQL可以使用Redo Log来恢复数据确保已经提交的事务所做的更改不会丢失。
64. MySQL中的DISTINCT和GROUP BY哪个更高效
在语义相同且有索引的情况下DISTINCT和GROUP BY确实都可能使用索引来提高查询效率如果查询优化的结果是相同的那么它们的效率可能会相近。
但是在没有索引的情况下DISTINCT通常会比GROUP BY更高效因为DISTINCT只需要找出不同的值而不需要进行排序或其他额外的分组操作。相比之下GROUP BY可能会在内存中进行排序这会增加额外的计算开销。因此在没有索引支持时选择DISTINCT通常可以避免不必要的排序操作提高查询性能。
65. 如果发现SQL查询慢怎么排查和优化
1使用EXPLAIN分析查询计划通过执行EXPLAIN命令可以查看MySQL是如何执行SQL查询的包括是否使用了索引、扫描的行数、连接类型等这有助于发现查询性能瓶颈。
2分析慢查询日志开启MySQL的慢查询日志功能可以记录执行时间超过设定阈值的查询语句通过分析这些日志可以找到慢查询的具体原因。
3利用开源工具如Arthas、Prometheus、Skywalking跟踪哪条SQL查询慢。
4检查索引使用情况确认查询中涉及的字段是否都有适当的索引。如果没有索引或者索引使用不当可能会导致全表扫描从而降低查询速度。
5优化SQL语句检查SQL语句是否可以优化比如减少子查询、避免使用SELECT *、优化JOIN操作、使用LIMIT限制返回结果等。
6检查数据库表结构确保表结构设计合理比如避免过多的冗余数据、合理使用数据类型、适当的分区等。
7服务器性能监控监控服务器的CPU、内存、磁盘I/O等性能指标确定是否是服务器资源不足导致查询变慢。
8查询缓存检查MySQL的查询缓存是否开启以及查询是否能够被缓存命中。
9数据库配置优化检查MySQL的配置文件如my.cnf或my.ini确保配置参数如innodb_buffer_pool_size、query_cache_size等适合当前的工作负载。
66. MySQL中的慢查询日志是什么如何使用它来优化性能
慢查询日志是MySQL数据库中记录执行时间超过指定阈值的查询语句的日志。通过分析慢查询日志可以发现数据库性能瓶颈从而优化SQL语句或数据库结构。要使用慢查询日志首先需要开启慢查询日志功能并设置合适的阈值例如将long_query_time参数设置为大于0的值。然后通过查看慢查询日志文件分析日志中的慢SQL语句对其进行优化比如增加索引、优化查询条件等。
67. 如何优化MySQL中的关联查询以提高查询效率
1对于外连接将小表作为驱动表大表作为被驱动表以减少外层循环的次数。
2对于内连接优化器会自动选择小结果集的表作为驱动表。
3确保被驱动表的连接字段上有索引以减少被驱动表的搜索次数。
4保持JOIN字段的数据类型一致避免索引失效。
5避免使用子查询尽量使用JOIN代替减少查询的复杂度和计算开销。
6添加冗余字段直接避免关联查询。
68. 为什么建议小表驱动大表
数据库在执行JOIN操作时驱动表可能会放入JOIN BUFFER来存储然后再对被驱动表进行全表扫描匹配。如果小表被用作驱动表那么整个小表可以一次性装入JOIN BUFFER中避免了对大表进行多次分批处理的需要。
