MySQL梗概

MySQL梗概

MySQL逻辑架构

连接器

• 用户连接、用户验证

分析器

• 词法分析、语法分析

优化器

• 优化sql语句

  • 基于CBO成本/RBO规则优化

• 重写查询，优化表的执行顺序、选择最优索引

  • 重写查询：移除不必要的括号、移除没有用的条件、化简表达式的条件

• 解析查询之前，先查询缓存（MySQL8.0移除缓存）

• 注意：优化器只有在不改变结果的前提下才进行优化

执行器

• sql语句的执行组件。MyISAM和InnoDB

MySQL锁

读写锁

• 共享锁。多个事务对于同一数据可以共享一把锁。
  加锁期间其他事务可以加S锁，不能加X锁。

  • A:select * from student where id = 1 lock in share mode;
    B:select * from student where id = 1;-- (读取数据没问题)
    B:update student set name=‘hehe’ where id =1;
    – 注意：无法修改并会卡死，当事务A提交事务之后，会立刻修改成功

• 排他锁。一个事务获取了一个数据行的排他锁，其他事务
  就不能再获取该行的锁。排他锁不与其他锁共存。
  增删改都是排他锁。

  • A:select * from student where id = 1 for update;
    B:select * from student where id = 1 for update;
    B:select * from student where id = 1 lock in share mode;
    – 注意：两个事务B操作的时候会卡死，提交事务立马成功。

意向锁

• 一个事务在获得某个数据行的S锁之前必须先获得该行的IS锁。
• 一个事务在获取某个数据行的X锁之前必须先获得该行的IX锁。
• 意向锁是InnoDB数据操作之前自动加的，不需要用户干预。

自增锁

• 针对自增列自增长的一个特殊表级别锁

  • SHOW VARIABLES LIKE ‘innodb_autoinc_lock_mode’;
    – 默认值1 代表连续，事务未提交则id永久丢失

间隙锁

• 比如要查询某个范围内的数据，给范围内的数据加锁，防止幻读

Next-Key Locks

• 行锁 + 间隙锁。不仅锁定一个记录上的索引，也锁定索引之间的数据。
• MVCC+Next-Key Locks 可以解决幻读问题

锁粒度

• 行锁

  • InnoDB引擎支持。InnoDB存储引擎是给索引上的索引项加锁实现的。不通过索引查询数据时，InnoDB引擎会锁住整张表，而非锁行。

    • 1:set autocommit=0;
      2:set autocommit=0;
      1:select * from tab_no_index where id = 1 for update;
      2:select * from tab_no_index where id = 2 for update;
      – 1只给一行加了排他锁，但2在请求其他行的排他锁时，出现锁等待。
      – 原因在没有索引的情况下，InnoDB只能使用表锁。
    • 1:set autocommit=0;
      2:set autocommit=0;
      1:select * from tab_with_index where id = 1 for update;
      2:select * from tab_with_index where id = 2 for update;
      – 1只给一行加了排他锁，2在请求其他行的排他锁时，没有出现锁等待。
      – 原因在有索引的情况下，InnoDB使用的是行锁。

  • 无主键、无唯一键会生成一个6位的row_id来作为索引

• 表锁

  • InnoDB和MyISAM引擎都支持。给整张表加锁。

MySQL事务

事务：事务就是一组原子性的SQL查询，要么全部执行成功，要么全部执行失败。

事务的四大特性

• 原子性A

  • 一个事务中的操作，要么全部执行成功，要么全部失败回滚。

    • undo log：记录事务的修改操作，如果失败回滚，那么会执行undo log反向操作。undo log是逻辑日志。

• 一致性C

  • 数据库由一个一致性的状态变为另一个一致性的状态。比如：银行转账

• 隔离性 I

  • 一个事务在最终提交之前对其他的事务都是不可见的，并发执行的事务之间互不影响。

• 持久性D

  • 一个事务提交之后对数据库的操作是持久的。

    • redo log：当系统崩溃时，虽然数据没有持久化，但redo log已经持久化。redo log是物理日志。

并发一致性问题

• 丢失修改

  • T2修改数据覆盖了T1修改过的数据

• 脏读

  • T1读取到T2已修改后又撤销的脏数据

• 不可重复读

  • T1读取到一个数据，T2修改后，T1再次读取两次读取到的数据不一致

• 幻读

  • T1读取到某个范围的数据，T2在这个范围插入一条数据，当T1再次插入一条相同的数据，无法插入，这次会产生幻读

隔离级别

• 读未提交

  • 可能产生脏读

    • 1:select * from student;
      1:update student set name = ‘msb’;
      2:select * from student; //name=‘msb’ ‘msb’ ‘msb’
      1:rollback; //回滚，撤销update语句执行
      2:select * from student; //name=‘zhangsan’ ‘lisi’ ‘wangwu’
      – 2读取了1中未提交的实时数据

• 读已提交

  • 可能产生不可重复读。即多次读取到的数据不一致。不可重复读在于修改数据。

    • 1:select * from student;
      1:update student set name = ‘zhangsan’ where id = 1;
      2:select * from student;//name=‘msb’ ‘msb’ ‘msb’ 读undo log日志数据
      1:commit; //提交数据
      2:select * from student; //name=‘zhangsan’ ‘msb’ ‘msb’
      – 2读取1中两次的数据不一样，会产生不可重复读，但不会引发脏读

• 可重复读

  • MySQL的默认隔离级别。可能产生幻读。幻读问题在于新增或删除数据。

    • 1:select * from student;
      2:insert into student values(4,‘sisi’);
      2:commit;
      1:select * from student; //只显示3条记录
      1:insert into student values(4,‘sisi’); //插入失败
      – 1中明明没有显示第4条数据，但是却不允许插入id=4记录，好像产生幻觉一样
      1:rollback; //或commit;
      1:select * from student; //会显示四条记录

• 串行化

  • 最高的隔离级别。强制多个事务执行串行执行。

MVCC

多版本并发控制。MVCC可以看作是一个行锁的变种。在很多情况下避免了加锁，开销更低。

InnoDB采用MVCC来支持高并发。MVCC是通过保存在某个时间点的快照实现的。

前提：用于实现读已提交和可重复读这两种隔离级别。读未提交：总是读取最新的行，要求低，无需使用。串行化：对所有的数据行都要加锁，单纯使用MVCC无法实现。

原理：InnoDB的MVCC，是通过在每行记录后面保存两个隐藏的列实现的，一个是行的创建时间，一个是行的过期时间。当然存储的是系统版本号。每开始一个事务，系统版本号都会自动递增。

• 系统版本号 SYS_ID：是一个递增的数字，每开始一个新的事务，系统版本号就会自动递增。
• 事务版本号 TRX_ID ：事务开始时的系统版本号。

SELECT：数据创建时间在当前版本号之前，删除时间在当前版本号之后。INSERT和DELETE：为当前插入/删除的每一行数据保存当前系统版本号作为行版本号/删除标识。UPDATE：为更新的每一行数据保存当前系统版本号作为行版本号和行删除标识。

MVCC利用了多版本的思想，写操作更新最新的版本快照，读操作更新旧版本快照。MVCC规定只能读取已提交的快照，当然一个事务可以读取自身未提交的快照。

MySQL存储引擎

MyISAM

• 非聚簇索引

  • 数据和索引分开存放
  • .frm存放表结构
  • .MYI：存放索引文件
  • .MYD：存放数据文件

• 不支持事务

• 支持表锁

• 不支持行锁

• 不支持外键

• 适合大量SELECT

• 不支持MVCC

• 崩溃后无法安全恢复

• 支持压缩表和空间索引

  • 如果数据不修改，那么就是和压缩表

InnoDB

• 聚簇索引

  • 数据和索引一起存放
  • .frm存放表结构
  • .ibd：数据和索引文件一起存放

• 支持事务

• 支持表锁

• 支持行锁

• 支持外键

• 适合大量INSERT、UPDATE、DELETE

• 支持MVCC

• 崩溃后可以安全恢复

Memory

• 读取数据更快
• 适合需要快速访问的数据，并且这些数据也不会修改，重启后丢失也没关系。

XMind - Trial Version