type
status
date
slug
summary
tags
category
icon
password

Exist和In

ㅤ
EXISTS
IN
作用
检查子查询是否有数据返回
检查某值是否在子查询结果集中
子查询结果处理
只要子查询返回第一条记录,立即返回TRUE
取其中的每一个值比较
NULL 的影响
对 NULL 不敏感，子查询的字段即使包含 NULL，不会影响结果。
如果子查询返回结果中有 NULL，会出现异常数据
适用场景
子查询和主查询基于不同的表，或者只需要检查存在性时更高效。
子查询返回的结果集较小，且需要与主表字段逐一匹配时更合适。
性能
大数据量场景下，EXISTS 通常比 IN 更高效，尤其是子查询的结果集非常大时。
当子查询的结果集很小或主查询字段与子查询直接相关时，IN 的性能可能更优。

SQL查询执行顺序

notion image

MySQL中数据文件形式

db.opt：用来存储当前数据库的默认字符集和字符校验规则。 t_order.frm：t_order 的表结构会保存在这个文件。在 MySQL 中建立一张表都会生成一个.frm 文件，该文件是用来保存每个表的元数据信息的，主要包含表结构定义。 t_order.ibd：t_order 的表数据会保存在这个文件。表数据既可以存在共享表空间文件（文件名：ibdata1）里，也可以存放在独占表空间文件（文件名：表名字.ibd）。这个行为是由参数 innodb_file_per_table 控制的，若设置了参数 innodb_file_per_table 为 1，则会将存储的数据、索引等信息单独存储在一个独占表空间，从 MySQL 5.6.6 版本开始，它的默认值就是 1 了，因此从这个版本之后， MySQL 中每一张表的数据都存放在一个独立的 .ibd 文件。

InnoDB和MyISAM索引区别

notion image

B+树和跳表比较

什么是前缀索引

指定一个字符串类型前几个字符建立索引
优点：降低索引存储空间，提高较长字符串字段的索引性能（树结构更小，查询更快）
缺点：
  • 前缀索引不能用于完整匹配
  • 不包含字段的完整内容，无法支持覆盖索引
  • 前缀长度的选择需要权衡，太短导致大量重复的索引值；太长占用较多的存储空间
索引长度：区分度=唯一前缀值的数量/总记录数>90% 接近→1

锁

加锁的是主键索引和使用的索引
间隙锁与间隙锁允许同时存在
记录锁有X和S类型，会互斥
插入意向锁与间隙锁互斥
间隙锁不会阻塞同一事务内的操作

死锁

死锁的四个必要条件：互斥、占有且等待、不可强占用、循环等待
 
解决方法
开始死锁检测 innodb_deadlock_detect
识别死锁 SHOW ENGINE INNODB STATUS
设置事务等待锁超时时间 innodb_lock_wait_timeout
notion image
事务A select…for update 在 >1006加上间隙锁
事务A insert被事务B的锁阻塞
 
事务B select…for update 在 >1006加上间隙锁
事务B insert被事务A的锁阻塞
 
在插入数据时，如果记录之间有间隙锁，会生成插入意向锁（锁是等待状态）

Insert是怎么加行级锁的

当事务需要加锁的时，如果这个锁不可能发生冲突，InnoDB会跳过加锁环节，这种机制称为隐式锁
加锁的情况
  • 如果记录之间加有间隙锁，为了避免幻读，此时是不能插入记录的；
  • 如果 Insert 的记录和已有记录存在唯一键冲突，此时也不能插入记录