知识点

MySQL进阶知识点

MySQL进阶知识点

存储引擎

MyISAM

不支持事务、行级锁和表锁，在INSERT和UPDATE时会锁定整个表。

MyISAM的特点是执行读取速度快，且占用的内存和存储资源较少。

InnoDB

InnoDB的底层存储是B+树，B+树的每个节点都对应InnoDB的一个Page，Page的大小固定，一般设为16KB。非叶子节点只有键值，叶子节点包含完整的数据。

img.png

主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。

非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。

基于非主键索引的查询需要多扫描一棵主键索引树，这个过程称为回表。

减少回表的方式：索引覆盖。

隔离级别

MySQL 可重复读

InnoDB 的行数据有多个版本，每个数据版本有自己的row trx_id，每个事务或者语句有自己的一致性视图。普通查询语句是一致性读，一致性读会根据 row trx_id和一致性视图确定数据版本的可见性。

  • 可重复读，查询只承认在事务启动前就已经提交完成的数据
  • 读提交，查询只承认在查询语句启动前就已经提交完成的数据
  • 当前读，总是读取已经提交完成的最新版本

MVCC

在可重复读隔离级别下，事务在启动的时候就拍了快照。注意：这个快照是基于整库的。

InnoDB 里面每个事务有一个唯一的事务 ID，叫作 transaction id。它是在事务开始的时候向InnoDB 的事务系统申请的，是按申请顺序严格递增的。

而每行数据也都是有多个版本的。每次事务更新数据的时候，都会生成一个新的数据版本，并且把 transaction id 赋值给这个数据版本的事务 ID，记为 row trx_id。同时，旧的数据版本要保留，并且在新的数据版本中，能够有信息可以直接拿到它。

也就是说，数据表中的一行记录，其实可能有多个版本 (row)，每个版本有自己的 row trx_id。

数据库锁

行级锁

行级锁指对某行数据加锁，是一种排它锁，防止其他事务修改此行。

  • SELECT … FOR UPDATE 语句允许用户一次针对多条记录执行更新。
  • COMMIT或者ROLLBACK释放锁

表级锁

表级锁是对当前操作的整张表加锁，表级锁分为表共享读锁与表独占写锁。
使用InnoDB引擎，如果筛选条件里面没有索引字段，就使用表锁，反之，则使用行锁。

页级锁

页级锁的锁定粒度介于行级锁和表级锁。表级锁的加锁速度快，但冲突多；行级锁冲突少，但加锁慢；页级锁在二者之间做了平衡，一次锁定相邻的一组记录。

间隙琐

在Innodb可重复读情况下，解决幻读引入的锁机制，前开后闭原则。

可重复读隔离级别下，数据库是通过行锁和间隙锁共同组成的。