MySQL-MySQL逻辑架构

文章目录

目录

文章目录

前言

一、MySQL逻辑架构

二、并发控制

三、事务

四、多版本并发控制MVCC

五、MySQL的存储引擎

总结

---

前言

主要介绍了MySQL逻辑架构。

---

一、MySQL逻辑架构

• 

• 连接管理与安全
  • 连接管理
    客户端拥有服务器进程中的一个线程，在这个线程中执行查询（服务器缓存线程无需为每个连接建立新线程）
  • 安全
    连接时服务器会对客户端（应用）认证，连接成功服务器继续验证查询权限
• 优化与执行
  • MySQL解析查询构建解析树，对其进行优化，用户可以通过关键字hint影响优化，也可以请求服务器explain优化细节
  • 存储引擎会影响优化效果，优化器请求存储引擎信息作为优化方式选择的条件，如一些索引和scheme的优化与存储引擎相关
  • 查询缓存中找到查询后无需解析优化，可以直接返回缓存中的结果

二、并发控制

• 读写锁
  共享锁（读锁）、排他锁（写锁）
• 锁粒度
  表锁（服务器和存储引擎层都可实现）、行级锁（只在部分存储引擎层实现）

三、事务

• ACID原子/一致/隔离/持久性，必须全部一起完成的一组动作
• 隔离级别
  • 未提交读（脏读、不可重复读、幻读）
    每次都读最新数据行，不管是否提交，不符合当前事务的版本，与MVCC不兼容
  • 提交读（不可重复读、幻读）
    提交读每次都读最新提交后的版本
    ​可重复读可通过事务版本号确定哪些版本的行可以读，最新版本可能不能读
  • 可重复读（幻读）
    幻读：用户a读一段空间内数据，同时另一个用户b在这段空间写入一行，用户a读到用户b写入的幻行，MVCC通过间隙锁(在索引的间隙中加锁)解决
  • 可串行化（加锁读，强制事务串行化）
    所有读取的行都加锁，与MVCC不兼容
• 死锁
  锁粒度小导致一次任务需要多把锁，然后出现多个线程请求保持情况，通过回滚一个事务释放一些锁可以解决
• 事务日志
  预写式日志，修改内容时，先将修改行为记录到硬盘上的可以快速顺序I/O的事务日志中，然后再慢慢随机I/O修改磁盘上的数据本身，修改一次访问磁盘两次，但是第一次很快，可以提高效率
• MySQL的事务
  • 自动提交
    在没有显示的创建事务时，每一个查询都自动被提交为一个单独的事务
  • 事务中混合使用存储引擎
    事务在存储引擎层实现，服务器层对于事务型存储引擎和非事务性存储引擎的混合一般不会报错，但是在回滚时，非事务型存储引擎中的操作会回滚失败导致数据库不一致问题
  • 显示/隐式锁定
    InnoDB事务型存储引擎隐式加行锁，用户可在MyISAM存储引擎的服务器层显示加表锁=

四、多版本并发控制MVCC

• 行级锁的变种(尽量避免加锁，降低开销)，通过保存数据在某个时间点的快照实现，每个事务依据其执行开始的时间看到表在某一时刻的快照
• 只与 [提交读] 和 [可重复读] 两个隔离级别兼容
• InnoDB的MVCC
  在每行记录后添加隐藏的两个列，一个记录行创建时系统版本号，一个记录行过期时系统版本号
  每次执行一个事务，系统版本号会作为该事务的版本号然后自动递增，将查询到的行数据版本号与事务版本号比较
  • SELECT
    数据行创建版本号<=事务版本号(事务开始前/事务本身添加的数据行)
    ​数据行过期版本号>事务版本号或不存在(事务开始前该行未失效)
  • INSERT / DELETE
    保存当前系统版本号作为行 创建 / 失效 版本号
  • UPDATE
    INSERT一条新数据、DELETE旧数据，当前版本号分别作为这两行数据的 创建 / 失效 版本号

五、MySQL的存储引擎

• 数据目录下的一个子目录存数据库(shcema)，数据库目录下存一个与表同名的 table.frm 文件保存表的定义
• InnoDB存储引擎
  默认使用这个，对大数据读写都合适，可以应用于订单处理(必须支持事务)
  • 支持行级锁(在存储引擎层隐式上锁)，支持事务，B+树建立聚簇和二级索引、自适应hash索引、支持在线热备份、数据存在磁盘
  • 数据和索引一起存在 table.idb 文件
  • 聚簇索引：对于主键建立索引，其叶子结点直接存数据
  • 二级索引：非主键索引的叶子结点存主键，没有索引覆盖时需要回表，主键要尽可能小
• MyISAM存储引擎
  对日志型应用、只读的表、不建议崩溃恢复的表、无需事务的操作、小数据合适
  • 支持表锁(读共享写排他)、支持全文索引(关键词)、B+树建立非聚簇索引(叶子节点存指针，数据不存在索引中)、支持压缩表(只读表压缩后可大大减少磁盘I/O次数)、支持空间函数(地理空间的搜索)、数据存在磁盘
  • 不支持事务、不支持行级锁、不支持崩溃后的安全恢复
  • 数据存在 table.MYD文件 索引存在 table.MYI文件
• Memory存储引擎
  快速访问不会改变的、掉电可丢失的数据时用
  • 支持表级锁、支持Hash索引
  • 数据都存在内存，无需磁盘I/O，速度很快；重启后结构保存数据丢失
  • 在MySQL中查询时需要利用临时表存储中间数据时，首先考虑Memory表，如果结果太多存不下则转换为MyISAM表
• 转换表的存储引擎
  • 修改表(高效)
    ALTER TABLE xxx ENGINE=XXX
  • 导出与导入(安全)
    导出数据后，在CREAT TABLE语句中修改ENGINE后，再重新导入
  • 创建与查询(高效又安全)
    创建新表CREAT TABLE xxx，修改旧表ALTER TABLE xxx ENGINE=XXX后，改后的旧表数据插入新表INSERT INTO xxx SELECT * FROM xxx

---

总结