MySQL学习笔记

MySQL学习笔记

MySQL的基础架构?

连查分优执插

• 连接器: 管理连接, 校验权限
• 查询缓存: 命中缓存, 直接返回
• 分析器: 语法检查
• 优化器: 选择索引
• 执行器: 调用存储引擎, 返回结果
• 插件式存储引擎: 存储数据, 提供读写接口

MyISAM和InnoDB的区别?

两者都是存储引擎, 存储引擎是基于表的, 可以为不同的表选择不同的存储引擎.

1. 是否支持行锁
2. 是否支持事务
3. 是否支持外键. 不过建议在业务层绑定外键,
4. 如果数据库崩溃, 重启后InnoDB能将数据库恢复到崩溃前的状态, 使用redo log.
5. 是否支持MVCC. MVCC是多版本并发控制机制, 通过在每行增加2个隐藏列, 1列存储创建版本, 1列存储删除版本, 减少加锁.
6. 索引实现机制 InnoDB的索引和数据不拆分, 数据存储在B+Tree的叶节点的data域.
7. 是否支持缓存, 在MySQL5.5.5版本后默认存储引擎是InnoDB, MySQL8.0版本以后去除了缓存机制.

什么是事务?

一组操作形成一个完整的业务逻辑, 可以将这组操作划分成一个事务.

比如我给你转账100, 大概分两步, 我的账户扣100, 你的账户加100, 两个操作组成一个事务.

事务的ACID特性?

• 原子性: 事务中的所有操作, 要么同时成功, 要么同时失败.

• 一致性: 事务提交以后, 结果符合预期.

• 隔离性: 并发事务之间相互隔离, 互不影响.

• 持久性: 事务提交以后, 结果写入文件.

  一致性是目的, 另外3个特性是方法.

事务并发可能造成的问题?

• 脏读: 一个事务正在修改某个值, 比如将1改为0, 还未提交前, 修改前的值1被另一个事务读走.
• 修改丢失: 两个事务都在修改某个值, 比如将1改为0, 第一个事务先读取到了1, 在修改为0之前, 被第二个事务读取到1, 也将1改为0, 最终1被改为0而不是预期的-1. 某个事务的修改操作丢失了.
• 不可重复读: 一个事务两次读取同一范围的数据, 两次读取之间, 另一个事务对这个范围内的数据做update/delete操作, 导致两次读取结果不一样.
• 幻读: 一个事务两次读取同一范围内的数据, 两次读取之间, 另一个事务对这个范围内的数据做insert操作, 导致两次读取结果不一样.

事务的隔离级别?

• 读未提交: 存在脏读, 修改丢失, 不可重复度的问题.
• 读已提交: 存在不可重复读的问题.
• 可重复读: MySQL的事务隔离级别, 如果是快照读, 使用MVCC机制解决幻读问题. 如果是当前读, 使用记录锁加间隙锁解决幻读问题.
• 串行化: 不允许并发, 满足acid原则, 但性能差.

MySQL锁

• 表级锁和行级锁
  • MyISAM默认表级锁, InnoDB默认行级锁.
  • 表级锁不锁索引, 行级锁锁索引.
• 共享锁(S锁)和排他锁(X锁)
  • 读共享, 写排他.
  • 表级和行级都有共享锁和排他锁.
  • 共享锁和共享锁兼容, 其他情况不兼容.
• 意向共享锁, 意向排他锁
  • 意向锁是表级锁
  • 意向锁之间互相兼容, 意向锁和行级共享锁排他锁相互兼容
  • 意向锁和表级共享锁兼容, 其他情况不兼容.
• 记录锁和间隙锁
  • 记录锁(record lock)和间隙锁(gap lock)组成临键锁(next-key lock)
  • 临键锁锁定某个查询范围, 不允许别的事务插入数据.

快照读和当前读

• 快照读

  MVCC是多版本并发控制机制, 通过在每行增加2个隐藏列, 1列存储创建版本, 1列存储删除版本. 事务序号随着新增递增.

  • 增: 将当前事务序号存储到"创建版本"字段

  • 删: 将当前事务序号存储到"删除版本"字段

  • 改: 复制一条记录, 将当前事务序号存储到, 新纪录的"创建版本"字段和旧记录的"删除版本"字段.

  • 查: 筛选"创建版本号"小于等于当前事务序号, 并且"删除版本号"为空或者大于当前事务序号的记录.

• 当前读

# 对读的记录加一个排他锁(X锁)
SELECT ... FOR UPDATE
# 对读的记录加一个共享锁(S锁)
SELECT ... LOCK IN SHARE MODE
# 对增删改的记录加一个排他锁(X锁), 增删改默认是当前读, 无需添加其他关键字.
INSERT ...
UPDATE ...
DELETE ...

# 查默认是快照读, 无需添加其他关键字.
SELECT ...