萧十一郎
所有文章 标签 分类 系列 小册 KeepLearning LeetCode 友链 关于
萧十一郎

MySQL 常见问题（三）

 收录于  类别 mysql
     约 4063 字   预计阅读 9 分钟 
  • 磁盘文件不同：

MyISAM 引擎的表会生成三个磁盘文件：

table_name.frm 该文件中存储表的结构信息。table_name.MYD 该文件中存储表的行数据。table_name.MYI 该文件中存储表的索引数据。

而 InnoDB 引擎的表只会生成两个磁盘文件：

table_name.frm 该文件中存储表的结构信息。table_name.ibd 该文件中存储表的行数据和索引数据。

  • InnoDB 支持聚簇索引，而 MyISAM 只支持非聚簇索引，因为 MyISAM 索引数据和表数据是分开存储的。
  • InnoDB 基于 Undo-log 日志实现了事务机制，但 MyISAM 没有，所以 MyISAM 不支持事务。
  • InnoDB 基于 Redo-log 日志实现了故障恢复机制，但 MyISAM 则只能依靠 Bin-log，因此会有丢失数据的风险。
  • InnoDB 可以基于聚簇索引实现行锁，同时还兼容表锁，但 MyISAM 仅支持表锁。
  • InnoDB 因为支持行锁以及 MVCC 机制，所以并发场景下的性能会远超 MyISAM 引擎。
  • InnoDB 由于设计了 BufferPool 缓冲池，所以内存利用度会远超 MyISAM 引擎。

主要是粒度不同，表锁是指对一整张表加锁，当加锁后，其他来访问该表的事务都会被阻塞，而行锁的粒度则小很多，是指针对于一条/多条数据加锁，并不会阻塞操作同一表的事务，而仅仅只会阻塞操作相同行数据的事务。

共享锁允许多个事务一起持有，而排他锁在同一时间内只能允许一个事务持有，也就是但凡出现排他锁的场景，其他事务都需要阻塞等待。

表锁有元数据锁、意向锁、自增锁、全局锁这四种，行锁有记录锁、间隙锁、临键锁、插入意向锁这四类，行锁在 MySQL 中是 InnoDB 引擎独有的，并且 InnoDB 的行锁和表锁之间，是相互兼容的。

记录锁是指对一条数据上锁、当我们针对主键或者唯一索引加锁的时候， MySQL 默认会对查询的这一行数据加行锁，避免其他事务对这一行数据进行修改。

间隙锁，就是锁定一个索引区间。在普通索引或者唯一索引列上，由于索引是基于 B+ 树的结构存储，所以默认会存在一个索引区间。而间隙锁，就是某个事务对索引列加锁的时候，默认锁定对应索引的左右开区间范围。在基于索引列的范围查询，无论是否是唯一索引，都会自动触发间隙锁。比如基于between的范围查询，就会产生一个左右开区间的间隙锁。

临键锁，它相当于「行锁+间隙锁」的组合，也就是它的锁定范围既包含了索引记录，也包含了索引区间，它会锁定一个左开右闭区间的数据范围。比如我们使用非唯一索引列进行查询的时候，默认会加一个临键锁，锁定一个左开右闭区间的范围。

总的来说，记录锁、临键锁、间隙锁只是表示锁定数据的范围，最终目的是为了解决幻读的问题。而临键锁相当于「行锁+间隙锁」，因此当我们使用非唯一索引进行精准匹配的时候，会默认加临键锁，因为它需要锁定匹配的这一行数据，还需要锁定这一行数据对应的左开右闭区间。因此在实际应用中，尽可能使用唯一索引或者主键索引进行查询，避免大面积的锁定造成性能影响。

几乎所有释放锁的工作都是 MySQL 自动完成的，但不同事务隔离级别中，释放锁的时机也不同，如果目前是读未提交级别，MySQL 执行完一条语句后就会立马释放锁。如果是其他级别中，基本上都需要等待持有锁的事务结束（commit/rollback）后才会释放。

因为索引树的结构会发生变更，比如一个无序数据插入时，就会导致树节点的分裂，这时需要挪动树中的一些节点位置，为了防止其他事务再次破坏树结构，或从索引树中读到不对的数据，所以会对整棵树上锁，这个问题被称为SMO问题，共享排他锁主要就是用来解决SMO问题。

死锁，简单来说就是两个或者两个以上的线程在执行的过程中，争夺同一个共享资源造成的相互等待的现象。

如果没有外部干预，线程会一直阻塞无法往下执行，这些一直处于相互等待资源的线程就称为死锁线程。

导致死锁的条件有四个，也就是这四个条件同时满足就会产生死锁👇

  • 互斥条件，共享资源 X 和 Y 只能被一个线程占用
  • 请求和保持条件，线程 T1 已经取得共享资源 X，在等待共享资源 Y 的时候，不释放共享资源 X
  • 不可抢占条件，其他线程不能强行抢占线程 T1 占有的资源
  • 循环等待条件，线程 T1 等待线程 T2 占有的资源，线程 T2 等待线程 T1 占有的资源，就是循环等待

导致死锁之后，只能通过人工干预来解决，比如重启服务，或者杀掉某个线程。所以，只能在写代码的时候，去规避可能出现的死锁问题。

按照死锁发生的四个条件，只需要破坏其中的任何一个，就可以解决，但是，互斥条件是没办法破坏的，因为这是互斥锁的基本约束，其他三方条件都有办法来破坏：

  • 对于“请求和保持”这个条件，可以一次性申请所有的资源，这样就不存在等待了。
  • 对于“不可抢占”这个条件，占用部分资源的线程进一步申请其他资源时，如果申请不到，可以主动释放它占有的资源，这样不可抢占这个条件就破坏掉了。
  • 对于“循环等待”这个条件，可以靠按序申请资源来预防。所谓按序申请，是指资源是有线性顺序的，申请的时候可以先申请资源序号小的，再申请资源序号大的，这样线性化后自然就不存在循环了。

聚簇索引，在聚簇索引中，索引数据和表数据在磁盘中的位置是一起的。聚簇索引存的是主键和当前行的数据，一张表中只能存在一个聚簇索引，一般都会选用主键作为聚簇索引。一般聚簇索引要求索引必须是非空唯一索引才行。

唯一索引，索引中的索引节点值不允许重复，一般配合唯一约束使用。

主键索引，是一种特殊的唯一索引，和普通唯一索引的区别在于不允许有空值。

普通索引，通过KEY、INDEX关键字创建的索引就是这个类型，没什么限制，就是单纯的可以让查询快一点。

全文索引在 5.7 版本之前，只有 MyISAM 引擎支持。全文索引只能创建在CHAR、VARCHAR、TEXT等这些文本类型字段上，而且使用全文索引查询时，条件字符数量必须大于 3 才生效。如果想要创建出的全文索引支持中文，需要在最后指定解析器with parser ngram。

覆盖索引

比如有个联合索引为user_name、user_sex、password，那么 SQL:

1
2
3
4
5
6

EXPLAIN SELECT
    `user_name`,`user_sex`
FROM
    `zz_users`
WHERE
    `password` = "1234" AND `user_sex` = "男";

虽然不符合联合索引的最左前缀匹配原则的，但是也可以用到索引，这就是索引覆盖。也就是：查询的列，在使用的索引中已经包含，被所使用的索引覆盖，这种情况称之为索引覆盖。

索引下推

1
2
3
4
5
6
7

{
    ["熊猫","女","6666"] : 1,
    ["竹子","男","1234"] : 2,
    ["子竹","男","4321"] : 3,
    ["1111","男","4321"] : 4,
    ["竹竹","女","8888"] : 5
}

比如 zz_users 表中有数据☝️，索引user_name、user_sex、password，查询 SQL：

1

SELECT * FROM `zz_users` WHERE `user_name` LIKE "竹%" AND `user_sex`="男";

由于使用了模糊查询，但%在结尾，因此可以使用竹这个字作为条件在联合索引中查询，整个查询过程如下：

  • ① 利用联合索引中的user_name字段找出「竹子、竹竹」两个索引节点。
  • ② 返回索引节点存储的值「2、5」给Server层，然后去逐一做回表扫描。
  • ③ 在Server层中根据user_sex="男"这个条件逐条判断，最终筛选到「竹子」这条数据。

那么为什么user_sex="男"这个条件不在联合索引中处理呢？因为前面是模糊查询，所以拼接起来是这样的竹X男，由于这个X是未知的，因此无法根据最左前缀原则去匹配数据，最终这里只能使用联合索引中user_name字段的一部分，后续的user_sex="男"还需要回到Server层处理。

所谓的索引下推，就是将Server层筛选数据的工作，下推到引擎层处理。

MySQL5.6 后加入索引下推机制后，其执行过程就是下面的顺序：

  • ① 利用联合索引中的user_name字段找出「竹子、竹竹」两个索引节点。
  • ② 根据user_sex="男"这个条件在索引节点中逐个判断，从而得到「竹子」这个节点。
  • ③ 最终将「竹子」这个节点对应的「2」返回给Server层，然后聚簇索引中回表拿数据。

相较于没有索引下推之前，原本需要做「2、5」两次回表查询，但在拥有索引下推之后，仅需做「2」一次回表查询。

脏读：指一个事务读到了其他事务还未提交的数据，也就是当前事务读到的数据，由于还未提交，因此有可能会回滚。

幻读：另外一个事务在第一个事务要处理的目标数据范围之内新增了数据，然后先于第一个事务提交造成的问题。幻读仅专指 “新插入的行”。

幻读是对自己来说的，比如，事务 A 在对表中多行数据进行修改，将性别「男、女」改为「0、1」，此时事务 B 又插入了一条性别为男的数据，当事务 A 提交后，再次查询表时，会发现表中依旧存在一条性别为男的数据。

不可重复读：指在一个事务中，多次读取同一数据，先后读取到的数据不一致。

事务 A 执行下单业务时，因为添加物流信息的时候出错了，导致整个事务回滚，事务回滚完成后，事务 A 就结束了。但事务 B 却并未结束，在事务 B 中，在事务 A 执行时读取了一次剩余库存，然后在事务 A 回滚后又读取了一次剩余库存，仔细想想：B 事务第一次读到的剩余库存是扣减之后的，第二次读到的剩余库存则是扣减之前的（因为 A 事务回滚又加回去了），导致两次读取的数据不一致，这就是不可重复读。

多个事务一起操作同一条数据，例如两个事务同时向表中添加一条ID=88的数据，此时就会造成数据覆盖，或者主键冲突的问题，这个问题也被称之为更新丢失问题。

更新于 2023-09-12  64cc149f
阅读原始文档
 mysql
返回 | 主页