索引数据结构B树B-树全称是 Balanced Tree是一种多路平衡查找树B树是一种自平衡的多路查找树B树和B树的最大区别在于非叶子节点是否存储数据B树叶子存数据且链表相连B树非叶子节点只是当索引使用同等空间下B树存储更多keyB树,非叶子节点和叶子节点都会存储数据找到对应节点就有对应的数据B树, 只有叶子节点才会存储数据,存储的数据都是在一行上找到非叶子节点的key还需要继续找到叶子节点才可以获取数据为什么选择b高效的范围查询B树是一种自平衡的树结构所有的叶子节点都在同一层因此支持高效的范围查询。因为在B树中叶子节点通过指针连接起来范围查询时只需要顺序扫描叶子节点避免了重新遍历树的过程。更高的磁盘I/O效率B树的节点存储了指向子节点的指针而且叶子节点通过链表连接这使得它非常适合磁盘存储。每个节点一般较大可以存储更多的元素从而减少了磁盘I/O的次数。B树的多级索引结构有助于减少需要访问的磁盘块数。支持顺序访问由于B树的叶子节点是按顺序排列的并且通过链表连接因此B树非常适合需要顺序遍历的场景比如ORDER BY查询或范围查询。优化查询性能B树的每个节点都存储了键值和指向下一层节点的指针。通过这种方式B树能在对大量数据进行查找时保持O(log N)的时间复杂度能快速定位到对应的数据。支持多种查询操作B树能够支持各种类型的查询操作包括等值查询、范围查询、前缀匹配等且在这些操作中都能够表现出较高的性能。易于维护B树自平衡的特点意味着它能够自动调整自身的结构避免了因数据变化导致的性能下降。对于频繁插入和删除操作的数据库B树能够保证始终保持较好的查询性能。聚簇索引和非聚簇索引数据和索引是否在一起innoDB的主键索引是聚簇索引其它是非聚簇索引myIsam引擎都是非聚簇索引,叶子节点存储的是数据的指针非聚簇索引需要回表回表和覆盖索引覆盖索引不需要回表索引中存在要查询的数据回表指的是先在非聚簇索引中查到主键ID再到聚簇索引中查询实际的数据索引优化索引类型主键索引唯一索引hash索引单值索引和复合索引什么场景会用不上索引字符串字段不加单双引号全表扫描的速度比走索引快。一般表的数据量很小最佳左前缀匹配原则如果索引了多例要遵循最左前缀原则查询从最左前列开始并且不跳过索引中的列不在索引列上做任何计算、函数操作会导致索引失效从而转向全表扫描存储引擎中不能使用索引中范围条件右边的列MySQL在使用不等于和is not null无法使用索引like以通配符开头会使索引失效导致全表扫描使用or连接索引失效索引优化explain 执行计划查看SQL是否走索引慢查询日志innoDB和myIsam引擎的区别InnoDB支持事务MyISAM不支持InnoDB支持外键而MyISAM不支持InnoDB是聚集索引使用BTree作为索引结构数据文件是和主键索引绑在一起的表数据文件本身就是按BTree组织的一个索引结构必须要有主键MyISAM是非聚集索引也是使用BTree作为索引结构索引和数据文件是分离的InnoDB表必须有唯一索引如主键用户没有指定的话会自己找/生产一个隐藏列Row_id来充当默认主键而Myisam可以没有InnoDB不保存表的具体行数执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数执行上述语句时只需要读出该变量即可速度很快InnoDB支持表、行(默认)级锁而MyISAM支持表级锁两者怎么选择如果应用程序需要支持事务处理例如银行交易或在线购物网站等则应该使用InnoDB存储引擎。InnoDB支持事务处理能够确保数据的一致性和完整性果应用程序需要支持外键约束例如一个订单必须关联一个客户等则应该使用InnoDB存储引擎。InnoDB支持外键约束可以在多个表之间建立关系从而实现数据的一致性和完整性。如果应用程序主要是进行大量的读取操作例如一个博客网站则可以使用MyISAM存储引擎。MyISAM对于读取操作的性能表现较好能够快速地检索和返回数据。如果应用程序需要进行大量的写入操作例如一个社交网站则应该使用InnoDB存储引擎。InnoDB对于写入操作的性能表现较好能够在并发写入操作的情况下保证数据的完整性。InnoDB为什么推荐使用自增ID作为主键自增ID可以保证每次插入时B索引是从右边扩展的可以避免B树和频繁合并和分裂对比使用UUID。如果使用字符串主键和随机主键会使得数据随机插入效率比较差InnoDB最多几层一般是3~4层极端五层计算公式页大小InnoDB 默认页大小为16KB每个索引节点页存储键值和指针。非叶子节点容量1200个假设主键为BIGINT8 字节指针为 6 字节每个键值对约14 字节。扣除页头等元数据后可用空间约15KB。每个非叶子节点可存储键值对数量15×102414≈11201415×1024≈1120 个。结论3 层 B树支持数据量 ≈ 1200×1200×16≈23001200×1200×16≈2300 万行。4 层 B树支持数据量 ≈ 1200×1200×1200×16≈271200×1200×1200×16≈27 亿行。5 层 B树支持数据量 ≈ 12004×16≈3.312004×16≈3.3 万亿行极端场景。事务四个特性: 原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Duration原子性通过undo_log实现持久性通过redo_log实现隔离性通过锁和多版本并发控制实现。一致性通过原子性隔离性持久性实现四个隔离级别以及会出现的问题 1读未提交脏读2读已提交RC不可重复读3可重复读RR,mysql默认幻读4串行化MVCC多版本并发工作通过undo_log实现当前读和快照读快照读所有不明显式加锁的都是快照读当前读所有写操作都是当前读update、insert、delete;读显式加锁 共享锁 select ... lock in share mod (排他锁 select ..... for update)常见的锁按粒度分表锁、行锁和页锁三种按类型分读锁和写锁两种。按性能分乐观锁、悲观锁。其中MyISAM和MEMORY存储引擎采用的是表级锁而InnoDB存储引擎支持行级锁和表级锁。锁模式间隙锁Gap-Lock间隙锁可以锁定一个范围内的所有记录包括不存在的记录从而防止其他事务在该范围内插入或修改数据。间隙锁只会在可重复读隔离级别REPEATABLE-READ下才会生效。临键锁Next-key Locks范围查询的时候会生效意向锁意向锁是表级锁分 IS意向共享、IX意向排他由 InnoDB 自动添加不能手动使用。事务加行 S 锁先加 IS加行 X 锁先加 IX。作用快速判断表锁与行锁是否冲突避免全表扫描提升加锁效率。意向锁之间兼容意向锁与表级排他锁互斥插入意向锁插入意向锁是 InnoDB 插入数据时自动添加在间隙上的锁用于提升并发插入与间隙锁兼容多个插入意向锁之间互不阻塞只有实际插入时才冲突配合间隙锁解决幻读。不是表级锁innoDB的可重复读已经很大限度的规避了幻读针对快照读普通 select 语句是通过 MVCC 方式解决了幻读因为可重复读隔离级别下事务执行过程中看到的数据一直跟这个事务启动时看到的数据是一致的即使中途有其他事务插入了一条数据是查询不出来这条数据的所以就很好了避免幻读问题。针对当前读select ... for update 等语句是通过 next-key lock记录锁间隙锁方式解决了幻读因为当执行 select ... for update 语句的时候会加上 next-key lock如果有其他事务在 next-key lock 锁范围内插入了一条记录那么这个插入语句就会被阻塞无法成功插入所以就很好了避免幻读问题。高可用主从复制的过程从库会生成两个线程,一个 I/O 线程,一个 SQL 线程;I/O 线程会去请求主库的 binlog并将得到的 binlog 写到本地的 relay-log (中继日志)文件中;主库会生成一个 dump 线程,用来给从库 I/O 线程传 binlog;SQL 线程,会读取 relay log 文件中的日志,并解析成 SQL 语句逐一执行;binlog 三种格式及优缺点StateMent: 记录每一条修改的SQL语句日志体积小但若SQL语句中有 now 等函数会有数据不一致问题Row: 默认方式记录数据的修改细节数据保持一致但日志体积大Mixed: 混合使用上面两种格式综合两者特点主从复制三种模式区别异步复制主库写完立即返回性能高存在丢数风险半同步复制主库等待至少一台从库确认接收日志后再返回兼顾安全性能MGR 组复制集群节点共识投票强数据一致性MYSQL主从复制延时问题的原因和解决方案(从IO线程和SQL线程维度出发)减少网络延迟例如同机房部署等提供从库性能分散从库读的压力提供硬件的磁盘和内存的性能提高主库性能优化SQL硬件升级等避免长事务长时间运行的事务会导致主从复制延迟并行复制多SQL线程复制从库不写binlog双主除外如何避免主从同步数据丢失开启半同步复制配置双 1 持久化参数使用 ROW 格式 binlog从库同步位点信息落盘存储。双 1 参数作用与含义sync_binlog1事务提交立刻刷 binlog 落盘innodb_flush_log_at_trx_commit1事务提交立刻刷 redo 日志落盘mysql的高可用框架主从架构 读写分离双主架构 其实原理就是两台服务器互为主从双向复制集群架构MySQL的增强半同步Enhanced Semi-Synchronous Replication是一种复制方式它可以提供更高的数据一致性和可靠性。当启用增强半同步时主数据库会等待至少一个备库确认已经接收到事务的复制数据后才认为事务提交成功。写入延迟增加主库性能下降可用性降低数据不一致两阶段提交第一阶段事务写入redo log但事务状态设为Prepare状态此状态代表事务还未提交成功第二阶段写入bin log然后再把redo log中的事务状态设置为Commit代表事务提交成功分库分表两种拆分方式垂直拆分按业务模块拆分库表水平拆分按数据行范围、哈希规则拆分数据分库分表带来的问题常见面试题mysql 一条SQL的执行流程MySQL数据库的连接池由一个线程来监听一个连接上请求以及读取请求数据解析出来一条我们发送过去的SQL语句SQL接口负责处理接收到的SQL语句查询解析器让MySQL能看懂SQL语句查询优化器选择最优的查询路径执行器根据执行计划调用存储引擎的接口存储引擎接口真正执行SQL语句MYSQL中的int(11)到底代表什么意思11代表的并不是长度而是字符的显示宽度在字段类型为int时无论你显示宽度设置为多少int类型能存储的最大值和最小值永远都是固定的当int字段类型设置为无符号且填充零UNSIGNED ZEROFILL时当数值位数未达到设置的显示宽度时会在数值前面补充零直到满足设定的显示宽度为什么会有无符号的限制呢是因为ZEROFILL属性会隐式地将数值转为无符号型因此不能存储负的数值。为什么MySQL中int默认11呢个人理解int占4个字节有符号整数取值范围-2147483648~2147483647无符号整数取值范围04294967295最大小值含负号共11位什么时候会加 间隙锁、临键锁和插入意向锁A. 临键锁Next-Key Lock何时触发触发条件只要是非唯一索引或普通索引的范围查询或者唯一索引的范围查询在扫描记录时为了覆盖范围都会产生 Next-Key Lock。形态(前一个记录值, 当前记录值]即左开右闭区间。唯一索引精准命中退化为行锁。B. 间隙锁Gap Lock何时触发唯一索引等值查询但记录不存在为了防止幻读会在记录缺失的位置加间隙锁锁定(左邻居, 右邻居)的开区间。非唯一索引等值查询不管记录是否存在为了防止幻读在该记录的左右两侧都会加上间隙锁。范围查询在命中范围边界时会向两侧延伸产生间隙锁。插入意向锁插入意向锁是 InnoDB 插入数据时自动添加在间隙上的锁用于提升并发插入与间隙锁兼容多个插入意向锁之间互不阻塞只有实际插入时才冲突配合间隙锁解决幻读。区别于意向锁是行级锁死锁出现的几种场景多个事务加锁顺序不一致当两个以上的事务同时操作同一组数据但是对数据的操作顺序不同就极有可能导致死锁。例如事务1在对A、B两个数据进行修改在修改之前先锁定A再锁定B而事务2在对A、B两个数据进行修改在修改之前先锁定B再锁定A。由此两个事务在相互等待之后就形成了死锁。间隙锁之间虽然不会互相阻塞但插入意向锁会和间隙锁阻塞 事务A和B先后再(20, 30)的区间上加了间隙锁此时间隙锁之间是没影响的因为间隙锁主要是为了防止幻读的发生也就是插入的发生。但是A此时有想插入数据了是需要在(20, 30)内生成插入意向锁的但这个区间在B的间隙锁范围内所以就会冲突场景 间隙锁 / 临键锁引发范围死锁数据id2,5事务范围查询加锁间隙区间交叉互堵表格事务 A事务 Bbegin;begin;select * from user where id5 for update;select * from user where id2 for update;insert into user (id) values (3);阻塞insert into user (id) values (4);阻塞间隙区间重叠互相占用锁区间形成死锁。场景 插入意向锁 间隙锁冲突死锁表格事务 A事务 Bbegin;begin;select * from user where id3 for update;无数据加间隙锁 (2,5)insert into user (id) values (3);申请插入意向锁阻塞insert into user (id) values (4);申请意向锁等待 B互相阻塞等待触发死锁。从死锁的定义来看MySQL 出现死锁的几个要素为a.两个或者两个以上事务b.每个事务都已经持有锁并且申请新的锁c.锁资源同时只能被同一个事务持有或者不兼容d.事务之间因为持有锁和申请锁导致彼此循环等待怎么解决死锁设置锁等待超时时间避免大事务一个事务中锁尽量少索引合理设置不要锁太多行少用大范围 for update 范围锁等值查询优先主键 / 唯一索引减少间隙锁选择合理的隔离级别
mysql面试题专辑
索引数据结构B树B-树全称是 Balanced Tree是一种多路平衡查找树B树是一种自平衡的多路查找树B树和B树的最大区别在于非叶子节点是否存储数据B树叶子存数据且链表相连B树非叶子节点只是当索引使用同等空间下B树存储更多keyB树,非叶子节点和叶子节点都会存储数据找到对应节点就有对应的数据B树, 只有叶子节点才会存储数据,存储的数据都是在一行上找到非叶子节点的key还需要继续找到叶子节点才可以获取数据为什么选择b高效的范围查询B树是一种自平衡的树结构所有的叶子节点都在同一层因此支持高效的范围查询。因为在B树中叶子节点通过指针连接起来范围查询时只需要顺序扫描叶子节点避免了重新遍历树的过程。更高的磁盘I/O效率B树的节点存储了指向子节点的指针而且叶子节点通过链表连接这使得它非常适合磁盘存储。每个节点一般较大可以存储更多的元素从而减少了磁盘I/O的次数。B树的多级索引结构有助于减少需要访问的磁盘块数。支持顺序访问由于B树的叶子节点是按顺序排列的并且通过链表连接因此B树非常适合需要顺序遍历的场景比如ORDER BY查询或范围查询。优化查询性能B树的每个节点都存储了键值和指向下一层节点的指针。通过这种方式B树能在对大量数据进行查找时保持O(log N)的时间复杂度能快速定位到对应的数据。支持多种查询操作B树能够支持各种类型的查询操作包括等值查询、范围查询、前缀匹配等且在这些操作中都能够表现出较高的性能。易于维护B树自平衡的特点意味着它能够自动调整自身的结构避免了因数据变化导致的性能下降。对于频繁插入和删除操作的数据库B树能够保证始终保持较好的查询性能。聚簇索引和非聚簇索引数据和索引是否在一起innoDB的主键索引是聚簇索引其它是非聚簇索引myIsam引擎都是非聚簇索引,叶子节点存储的是数据的指针非聚簇索引需要回表回表和覆盖索引覆盖索引不需要回表索引中存在要查询的数据回表指的是先在非聚簇索引中查到主键ID再到聚簇索引中查询实际的数据索引优化索引类型主键索引唯一索引hash索引单值索引和复合索引什么场景会用不上索引字符串字段不加单双引号全表扫描的速度比走索引快。一般表的数据量很小最佳左前缀匹配原则如果索引了多例要遵循最左前缀原则查询从最左前列开始并且不跳过索引中的列不在索引列上做任何计算、函数操作会导致索引失效从而转向全表扫描存储引擎中不能使用索引中范围条件右边的列MySQL在使用不等于和is not null无法使用索引like以通配符开头会使索引失效导致全表扫描使用or连接索引失效索引优化explain 执行计划查看SQL是否走索引慢查询日志innoDB和myIsam引擎的区别InnoDB支持事务MyISAM不支持InnoDB支持外键而MyISAM不支持InnoDB是聚集索引使用BTree作为索引结构数据文件是和主键索引绑在一起的表数据文件本身就是按BTree组织的一个索引结构必须要有主键MyISAM是非聚集索引也是使用BTree作为索引结构索引和数据文件是分离的InnoDB表必须有唯一索引如主键用户没有指定的话会自己找/生产一个隐藏列Row_id来充当默认主键而Myisam可以没有InnoDB不保存表的具体行数执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数执行上述语句时只需要读出该变量即可速度很快InnoDB支持表、行(默认)级锁而MyISAM支持表级锁两者怎么选择如果应用程序需要支持事务处理例如银行交易或在线购物网站等则应该使用InnoDB存储引擎。InnoDB支持事务处理能够确保数据的一致性和完整性果应用程序需要支持外键约束例如一个订单必须关联一个客户等则应该使用InnoDB存储引擎。InnoDB支持外键约束可以在多个表之间建立关系从而实现数据的一致性和完整性。如果应用程序主要是进行大量的读取操作例如一个博客网站则可以使用MyISAM存储引擎。MyISAM对于读取操作的性能表现较好能够快速地检索和返回数据。如果应用程序需要进行大量的写入操作例如一个社交网站则应该使用InnoDB存储引擎。InnoDB对于写入操作的性能表现较好能够在并发写入操作的情况下保证数据的完整性。InnoDB为什么推荐使用自增ID作为主键自增ID可以保证每次插入时B索引是从右边扩展的可以避免B树和频繁合并和分裂对比使用UUID。如果使用字符串主键和随机主键会使得数据随机插入效率比较差InnoDB最多几层一般是3~4层极端五层计算公式页大小InnoDB 默认页大小为16KB每个索引节点页存储键值和指针。非叶子节点容量1200个假设主键为BIGINT8 字节指针为 6 字节每个键值对约14 字节。扣除页头等元数据后可用空间约15KB。每个非叶子节点可存储键值对数量15×102414≈11201415×1024≈1120 个。结论3 层 B树支持数据量 ≈ 1200×1200×16≈23001200×1200×16≈2300 万行。4 层 B树支持数据量 ≈ 1200×1200×1200×16≈271200×1200×1200×16≈27 亿行。5 层 B树支持数据量 ≈ 12004×16≈3.312004×16≈3.3 万亿行极端场景。事务四个特性: 原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Duration原子性通过undo_log实现持久性通过redo_log实现隔离性通过锁和多版本并发控制实现。一致性通过原子性隔离性持久性实现四个隔离级别以及会出现的问题 1读未提交脏读2读已提交RC不可重复读3可重复读RR,mysql默认幻读4串行化MVCC多版本并发工作通过undo_log实现当前读和快照读快照读所有不明显式加锁的都是快照读当前读所有写操作都是当前读update、insert、delete;读显式加锁 共享锁 select ... lock in share mod (排他锁 select ..... for update)常见的锁按粒度分表锁、行锁和页锁三种按类型分读锁和写锁两种。按性能分乐观锁、悲观锁。其中MyISAM和MEMORY存储引擎采用的是表级锁而InnoDB存储引擎支持行级锁和表级锁。锁模式间隙锁Gap-Lock间隙锁可以锁定一个范围内的所有记录包括不存在的记录从而防止其他事务在该范围内插入或修改数据。间隙锁只会在可重复读隔离级别REPEATABLE-READ下才会生效。临键锁Next-key Locks范围查询的时候会生效意向锁意向锁是表级锁分 IS意向共享、IX意向排他由 InnoDB 自动添加不能手动使用。事务加行 S 锁先加 IS加行 X 锁先加 IX。作用快速判断表锁与行锁是否冲突避免全表扫描提升加锁效率。意向锁之间兼容意向锁与表级排他锁互斥插入意向锁插入意向锁是 InnoDB 插入数据时自动添加在间隙上的锁用于提升并发插入与间隙锁兼容多个插入意向锁之间互不阻塞只有实际插入时才冲突配合间隙锁解决幻读。不是表级锁innoDB的可重复读已经很大限度的规避了幻读针对快照读普通 select 语句是通过 MVCC 方式解决了幻读因为可重复读隔离级别下事务执行过程中看到的数据一直跟这个事务启动时看到的数据是一致的即使中途有其他事务插入了一条数据是查询不出来这条数据的所以就很好了避免幻读问题。针对当前读select ... for update 等语句是通过 next-key lock记录锁间隙锁方式解决了幻读因为当执行 select ... for update 语句的时候会加上 next-key lock如果有其他事务在 next-key lock 锁范围内插入了一条记录那么这个插入语句就会被阻塞无法成功插入所以就很好了避免幻读问题。高可用主从复制的过程从库会生成两个线程,一个 I/O 线程,一个 SQL 线程;I/O 线程会去请求主库的 binlog并将得到的 binlog 写到本地的 relay-log (中继日志)文件中;主库会生成一个 dump 线程,用来给从库 I/O 线程传 binlog;SQL 线程,会读取 relay log 文件中的日志,并解析成 SQL 语句逐一执行;binlog 三种格式及优缺点StateMent: 记录每一条修改的SQL语句日志体积小但若SQL语句中有 now 等函数会有数据不一致问题Row: 默认方式记录数据的修改细节数据保持一致但日志体积大Mixed: 混合使用上面两种格式综合两者特点主从复制三种模式区别异步复制主库写完立即返回性能高存在丢数风险半同步复制主库等待至少一台从库确认接收日志后再返回兼顾安全性能MGR 组复制集群节点共识投票强数据一致性MYSQL主从复制延时问题的原因和解决方案(从IO线程和SQL线程维度出发)减少网络延迟例如同机房部署等提供从库性能分散从库读的压力提供硬件的磁盘和内存的性能提高主库性能优化SQL硬件升级等避免长事务长时间运行的事务会导致主从复制延迟并行复制多SQL线程复制从库不写binlog双主除外如何避免主从同步数据丢失开启半同步复制配置双 1 持久化参数使用 ROW 格式 binlog从库同步位点信息落盘存储。双 1 参数作用与含义sync_binlog1事务提交立刻刷 binlog 落盘innodb_flush_log_at_trx_commit1事务提交立刻刷 redo 日志落盘mysql的高可用框架主从架构 读写分离双主架构 其实原理就是两台服务器互为主从双向复制集群架构MySQL的增强半同步Enhanced Semi-Synchronous Replication是一种复制方式它可以提供更高的数据一致性和可靠性。当启用增强半同步时主数据库会等待至少一个备库确认已经接收到事务的复制数据后才认为事务提交成功。写入延迟增加主库性能下降可用性降低数据不一致两阶段提交第一阶段事务写入redo log但事务状态设为Prepare状态此状态代表事务还未提交成功第二阶段写入bin log然后再把redo log中的事务状态设置为Commit代表事务提交成功分库分表两种拆分方式垂直拆分按业务模块拆分库表水平拆分按数据行范围、哈希规则拆分数据分库分表带来的问题常见面试题mysql 一条SQL的执行流程MySQL数据库的连接池由一个线程来监听一个连接上请求以及读取请求数据解析出来一条我们发送过去的SQL语句SQL接口负责处理接收到的SQL语句查询解析器让MySQL能看懂SQL语句查询优化器选择最优的查询路径执行器根据执行计划调用存储引擎的接口存储引擎接口真正执行SQL语句MYSQL中的int(11)到底代表什么意思11代表的并不是长度而是字符的显示宽度在字段类型为int时无论你显示宽度设置为多少int类型能存储的最大值和最小值永远都是固定的当int字段类型设置为无符号且填充零UNSIGNED ZEROFILL时当数值位数未达到设置的显示宽度时会在数值前面补充零直到满足设定的显示宽度为什么会有无符号的限制呢是因为ZEROFILL属性会隐式地将数值转为无符号型因此不能存储负的数值。为什么MySQL中int默认11呢个人理解int占4个字节有符号整数取值范围-2147483648~2147483647无符号整数取值范围04294967295最大小值含负号共11位什么时候会加 间隙锁、临键锁和插入意向锁A. 临键锁Next-Key Lock何时触发触发条件只要是非唯一索引或普通索引的范围查询或者唯一索引的范围查询在扫描记录时为了覆盖范围都会产生 Next-Key Lock。形态(前一个记录值, 当前记录值]即左开右闭区间。唯一索引精准命中退化为行锁。B. 间隙锁Gap Lock何时触发唯一索引等值查询但记录不存在为了防止幻读会在记录缺失的位置加间隙锁锁定(左邻居, 右邻居)的开区间。非唯一索引等值查询不管记录是否存在为了防止幻读在该记录的左右两侧都会加上间隙锁。范围查询在命中范围边界时会向两侧延伸产生间隙锁。插入意向锁插入意向锁是 InnoDB 插入数据时自动添加在间隙上的锁用于提升并发插入与间隙锁兼容多个插入意向锁之间互不阻塞只有实际插入时才冲突配合间隙锁解决幻读。区别于意向锁是行级锁死锁出现的几种场景多个事务加锁顺序不一致当两个以上的事务同时操作同一组数据但是对数据的操作顺序不同就极有可能导致死锁。例如事务1在对A、B两个数据进行修改在修改之前先锁定A再锁定B而事务2在对A、B两个数据进行修改在修改之前先锁定B再锁定A。由此两个事务在相互等待之后就形成了死锁。间隙锁之间虽然不会互相阻塞但插入意向锁会和间隙锁阻塞 事务A和B先后再(20, 30)的区间上加了间隙锁此时间隙锁之间是没影响的因为间隙锁主要是为了防止幻读的发生也就是插入的发生。但是A此时有想插入数据了是需要在(20, 30)内生成插入意向锁的但这个区间在B的间隙锁范围内所以就会冲突场景 间隙锁 / 临键锁引发范围死锁数据id2,5事务范围查询加锁间隙区间交叉互堵表格事务 A事务 Bbegin;begin;select * from user where id5 for update;select * from user where id2 for update;insert into user (id) values (3);阻塞insert into user (id) values (4);阻塞间隙区间重叠互相占用锁区间形成死锁。场景 插入意向锁 间隙锁冲突死锁表格事务 A事务 Bbegin;begin;select * from user where id3 for update;无数据加间隙锁 (2,5)insert into user (id) values (3);申请插入意向锁阻塞insert into user (id) values (4);申请意向锁等待 B互相阻塞等待触发死锁。从死锁的定义来看MySQL 出现死锁的几个要素为a.两个或者两个以上事务b.每个事务都已经持有锁并且申请新的锁c.锁资源同时只能被同一个事务持有或者不兼容d.事务之间因为持有锁和申请锁导致彼此循环等待怎么解决死锁设置锁等待超时时间避免大事务一个事务中锁尽量少索引合理设置不要锁太多行少用大范围 for update 范围锁等值查询优先主键 / 唯一索引减少间隙锁选择合理的隔离级别
