深入学习理解MySQL高级特性系列（一） -- 掌握MySQL架构

MySQL最重要的特性：

• 存储引擎架构
• 架构的设计将查询处理(Query Processing)
• 其他系统任务(Server Task)
• 数据的存储/提取相分离

一、MySQL逻辑架构

• 逻辑架构：
  • 连接层（客户端）：负责连接处理、授权认证、安全等
  • 服务器层（Server）：查询解析、分析、优化、缓存、内置函数、跨存储引擎的功能（存储过程、触发器、视图）
  • 存储引擎层：负责数据的存储和提取，响应服务器层的请求
• 连接与安全：通过服务器进程中的线程，使用SSL（TCP三次握手）方式安全连接
• 优化与执行：解析查询，创建解析树，进行优化（重写查询、决定表的读取顺序、选择合适的索引）

二、并发控制

• 读写锁：
  • 共享读锁：同时读取同一个资源
  • 排他写锁：写入时阻塞其他写锁和读锁
• 锁粒度：
  • 表锁（服务器层）：开销小、锁定整张表，阻塞其他用户的所有读写操作（MyISAM）
  • 行级锁（存储引擎层）：开销大、支持并发处理（InnoDB）

三、事务

• 定义：一组原子性的SQL查询（独立的工作单元）
• 特性：ACID
  • 原子性（atomicity）：事务是不可分割的最小工作单元，事务中的操作要么全部提交成功，要么全部失败回滚（不能只执行其中一部分操作）
  • 一致性（consistency）：状态一致，使数据库从一个一致性状态转换到另一个一致性状态，没提交前，事务中所做的修改不会保存到数据库中（多个事务并行执行与按顺序串行执行的结果一致）
  • 隔离性（isolation）：事务的执行不受其他事务的干扰，事务在提交前的修改操作，对其他事务不可见（事务隔离级别）
  • 持久性（durability）：已提交的事务，所做的修改永久保存至数据库中，不被丢失，即使系统崩溃，数据库出现故障
• 隔离级别：规定了事务所做的修改，哪些是在事务内和事务间是可见的和不可见的
  • READ UNCOMMITTED（未提交读）：事务的修改，即使没有提交，对其他事务也是可见的
  • READ COMMITTED（提交读）/nonrepeatable（不可重复读）：事务从开始到提交之前，所做的修改对其他事务不可见（两次同样的查询，结果可能不同）
  • REPEATABLE READ（可重复读）：默认事务隔离级别，同一事务多次读取资源的结果一致
  • SERIALIZABLE（可串行化）：最高级别，强制事务串行执行，在读取的每一行数据上加锁，可能导致大量的超时和锁争用的问题
• 并发问题：
  • 脏读（Dirty Read）：一个事务可以读取其他事务未提交的数据
  • 不可重复读：两次同样的查询，可能得到不一样的结果
  • 幻读/虚读（Phantom Read）：并发事务增加了记录数（事务A新增了一条记录，事务B在事务A提交前后各执行了一次查询操作，发现后一次比前一次多了一条记录，产生幻行）
  • 不可重复读问题：解决
    • 给修改的记录加锁
    • MVCC，多版本并发控制，无锁（InnoDB）
  • 不可重复读和脏读的区别：
    • 脏读是某一事务读取了另一个事务未提交的脏数据
    • 不可重复读则是读取了前一事务提交的数据（导致数据不一样）
  • 幻读和不可重复读：
    • 相同：都是读取了另一条已经提交的事务（和脏读不同）
    • 不同：不可重复读查询的都是同一个数据项，幻读针对的是一批数据整体（数据的个数）
      
• 串行化（解决所有问题）、可重复读（解决脏读和不可重复读，不能解决幻读）、不可重复读（只能解决脏读）、未提交读（问题都不能解决）
• 死锁：两个或多个事务在同一资源相互占用，并请求锁定对方占用的资源
  • 多个事务以不同顺序锁定资源
  • 多个事务同时锁定同一个资源
  • 解决：死锁检测、死锁超时机制（InnoDB：将持有最少行级排他锁的事务进行回滚）
• 事务日志（预写式日志 Write-Ahead Logging）：存储引擎在修改表的数据时，只需要修改其内存的拷贝，再把修改操作记录到硬盘上的事务日志中（追加的方式，顺序IO），事务日志持久化后，内存中被修改的数据在后台慢慢地刷回到磁盘（若发生异常，就算数据没有持久化成功，只要日志持久化成功，数据就可以恢复）
• MySQl 中的事务：InnoDB支持
  • 自动提交（AUTOCOMMIT）：默认模式，每个查询都被当作一个事务执行提交操作
    • 参数：AUOTOCOMMIT
      • =0（OFF）：禁用（所有查询都在一个事务中，直到显示地提交或回滚，事务才结束）
      • =1（ON）：启用（默认，每次查询都提交，一个查询一个事务）
    • DDL（定义语言）比如ALTER TABLE或者 LOCK TABLES会强制提交当前事务
  • 事务由存储引擎实现，非事务型存储引擎执行提交和回滚，无效（不会有提示）
  • 两阶段锁定协议：事务执行过程中，随时都可以锁定
    • 隐式锁定：执行提交和回滚时才会释放锁，根据隔离级别自动加锁
    • 显式锁定：通过特定语句（LOCK IN SHARE MODE、FOR UPDATE）
  • LOCK/UNLOCK TABLES：服务器层实现，无法替代事务

四、多版本并发控制（MVCC）

• 定义：通过保存数据在某个时间点的快照实现，保证每个事务看到的数据一致
• 行级锁的变种，非阻塞的读，只锁定必要行的写
  • 乐观并发控制
  • 悲观并发控制
• MVCC只在读已提交（不可重复读）、可重复读两个隔离级别下工作
  未提交读：只会读取最新的数据行，不是当前事务版本号的行
  可序列化：对读取的行加锁
• InnoDB：每行记录后面保存两个隐藏的列
  • 两个列：一个保存行的创建时间、一个保存行的删除（过期）时间
    • 时间：系统版本号
    • 每开始一个新的事务，系统版本号自动递增
    • 用事务的版本号（事务开始的）和行记录的版本号比较
  • SELECT：符合两个条件，才返回查询结果
    • 查找行的版本号<=事务的版本号（确保事务读取的行，在事务开始前已经存在，或是事务自身插入或修改过的）
      • 事务自身插入的：行记录的当前版本号，所以等于事务的版本号
      • 事务修改过的：新行记录的是当前版本号，也等于事务的版本号
    • 行的删除版本未定义或大于当前事务版本号（确保事务读取的行，在事务开始前未被删除）
  • INSERT：新插入的行记录当前系统版本号作为行版本号
  • DELETE：删除的行记录当前系统版本号
  • UPDATE：
    • 插入的新行记录当前系统版本号
    • 原来的行记录当前系统版本号作为删除版本号
  • 优点：不加锁读，读操作简单，性能好且保证读取符合标准的行
  • 行记录需要额外存储空间，且需要行检查工作和维护工作

五、存储引擎

• 在frm文件保存表的定义（结构），使用show table status显示表的相关信息
• 参数：
  • Row_format（行的格式）：
    • Dynamic：行的长度可变
    • Fixed：固定长度的行
    • Compressed：压缩表中（MyISAM）
  • Rows：MyISAM（精确值）、InnoDB（估计值）
  • Avg_row_length：平均每行包含的字节数
  • Data_length：表数据的大小
  • Collation：表的默认字符集和字符列排序规则
  • Checksum：表的实时校验和
• InnoDB：默认引擎，处理大量的短期事务
  • 数据和索引一起存储在表空间（ibd文件）
  • 采用MVCC来支持高并发，并实现四个隔离级别（默认可重复读）
  • 通过间隙锁防止幻读（不仅锁定查询涉及的行，还锁定索引中的间隙）
  • 基于聚簇索引建立表，二级索引包含主键列
  • 支持自适应哈希索引：加速读操作（可预测性预读，自动在内存中创建hash索引）
  • 支持插入缓冲区：加速插入操作
• MyISAM：5.1之前默认引擎，全文索引、压缩、空间函数
  • 不足：表锁问题（查询可能一直阻塞状态），不支持事务和行级锁，崩溃后无法安全恢复
  • 数据（MYD）和索引（MYI）分别存储在不同文件中
  • 存储行记录数（便于count操作）
  • 对整张表加锁，可以并发插入（在读取时，插入新的行记录）
    • 读取：共享锁
    • 写入：排他锁
  • 修复：手动或自动执行检查和修复工作（非常慢，可能导致数据丢失）
  • 延迟更新索引键：修改完成时，不会立刻将修改的索引数据写入磁盘，而是写入内存中的键缓冲区，在清理键缓冲区或者关闭表的时候，才将对应的索引块写入磁盘）
  • 可以使用 myisampack 对表进行压缩（打包），压缩表不能进行修改（只能解压缩，修改数据，再压缩），减少空间占用，减少磁盘IO，提升查询性能，支持索引（只读）
  • 叶子节点存储数据的地址
  • 设计简单，数据紧密存储
• 其他存储引擎：
  • Archive：
    • 只支持INSERT和SELECT，缓存所有的写，对插入的行进行压缩，支持行级锁，可以实现高并发的插入
    • 适用：日志、数据采集
  • Blackhole：
    • 丢弃所有插入的数据，只记录表的日志
    • 适用：复制数据到备库、记录到日志
  • Memory：
    • 快速访问数据（Hash索引），数据不会被修改，数据保存在内存中，表结构在重启后会保留，数据会丢失
    • 不足：表级锁，并发写入的性能低，不支持BLOB/TEXT类型，行长度固定
    • 适用：查找或映射表、缓存周期性聚合数据的结果、保存数据分析中的中间数据
    • MySQL在执行查询过程中需要使用临时表（Memory表）来保存中间结果
    • Menory表不是临时表
• 选择合适的存储引擎：
  • 优先选择InnoDB（全文索引使用InnoDB + Sphinx）
  • 不要混合使用多种存储引擎
  • 日志型应用：MyISAM或Archive
  • 只读（读多写少）：MyISAM（不介意崩溃恢复问题）
• 转换表的引擎：失去原引擎的相关特性
  • ALTER TABLE：时间长（按行将数据复制到另一个表中）、原表加锁
  • 导出与导入（mysqldump）：导出文件，修改文件中creat table语句中的存储引擎
  • 创建与查询：先创建一个新的存储引擎表，通过insert……select将数据导入新表（可分批处理）