MySQL架构与历史

作者

MySQL 最重要、最与众不同的特性是它的存储引擎架构，这种架构的设计将查询处理及其他系统任务，和数据的存储/提取相分离。

这种处理和存储分离的设计可以再使用时根据性能、特性，以及其他需求来选择数据存储的方式。

一、MySQL 逻辑架构

  • 第一层：连接/线程处理
  • 第二层：大多数 MySQL 的核心服务功能都在这一层，
    • 查询解析、分析、优化、缓存、内置函数的实现
    • 跨存储引擎的功能：存储过程、触发器、视图
  • 第三层：存储引擎。负责 MySQL 中数据的存储和提取。服务器通过 API 与存储引擎进行通信。

1、连接管理与安全性

  • 每个客户端连接都会在服务器进程中拥有一个线程，服务器会负责线程的缓存
  • 当客户端连接到 MySQL 服务器，服务器会对其进行验证

2、优化与执行

  • MySQL 会解析查询，并创建内部数据结构（解析树），然后对其进行各种优化：重写查询，决定表的读取顺序，选择合适的索引
  • 存储引擎对于优化查询是有影响的，某些存储引擎的某种索引，可能对特定的查询有优化
  • 对于 SELECT 语句会先查询缓存

二、并发控制

并发控制有两个层面：服务器层与存储引擎层。

1、读写锁

  • 读锁：共享锁
  • 写锁：排它锁

2、锁粒度

  • 在给定的资源上，锁定的数据量越少，则系统的并发程度越高
  • 加锁也需要消耗资源，所以锁策略就是在开销和数据的安全性之间寻求平衡
  • MySQL 提供了多种锁粒度和锁策略的选择

表锁

  • 开销最小的锁策略，锁定整张表
  • 写锁比读锁优先级高

行级锁

  • 可以最大限度的支持并发处理，也带来了最大的锁开销
  • 行级锁只在存储引擎层实现，MySQL 服务层没有实现

三、事务

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

  • Atomicity 原子性
  • Consistency 一致性
  • Isolation 隔离性
  • Durability 持久性

1、隔离级别

四种隔离级别，每一种都规定了一个事务中所作的修改。

  • 脏读、未提交读
  • 提交读
  • 可重复读
  • 可串行化

2、死锁：死锁指的是两个或多个事务在同一资源上相互占用并请求锁定对方占用的资源，从而导致恶性循环

  • 当多个事务试图以不同的顺序锁定资源时，就可能产生死锁
  • 多个表同时锁定一个资源时，也会产生死锁
  • 和存储引擎的实现方式有关

数据库实现了死锁检测和超时机制。

InnoDB 目前处理死锁，是将持有最少行级锁的事务进行回滚。

3、事务日志

  • 事务日志可以帮助提高事务的效率。

  • 先记录修改的行为，然后再慢慢刷数据到磁盘，所以需要写两次磁盘。

4、MySQL 中的事务

  • MySQL 中两种事务型的存储引擎：InnoDB 和 NDB Cluster。
  • 在一个事务中，使用多种存储引擎是不可靠的。因为事务是由下层的存储引擎实现的。
  • InnDB 既可以自动隐式加锁，也可以通过语句显式加锁。
  • LOCK TABLES 和 UNLOCK TABLES 是在服务器层实现的，和存储引擎无关，不能代替事务处理。

四、多版本并发控制 MVCC

MySQL 的大多数事务型存储引擎实现的都不是简单的行级锁，一般都同时实现了多版本并发控制。

  • MVCC可以认为是行级锁的一个变种。
  • 不同的存储引擎，MVCC的实现是不同的，主要有乐观和悲观并发控制。
  • InnoDB 的 MVCC，是通过在每行记录后面保存两个隐藏的列来实现的。一个保存了行的创建时版本号，一个保存行的过期时间版本号。
  • MVCC只在可重复读和提交读这两个隔离级别下工作。

五、MySQL 的存储引擎

  • 在文件系统中，MySQL 将每个数据库（schema） 保存为数据目录下的一个子目录。
  • 创建表时，MySQL 会在数据子目录下创建一个和表同名的 .frm文件，用来保存表的定义。
  • MySQL 用文件系统的目录和文件来保存数据库和表的定义，所以大小写敏感和具体的平台相关。

1、InnoDB

  • InnoDB 的数据存储在表空间（tableSpace）中，表空间是一个由 InnoDB 管理的黑盒子，由一系列的数据文件组成。
  • InnoDB采用 MVCC 来支持高并发，默认级别是可重复读，通过间隙锁（next-key locking)策略防止幻读的出现。
  • InnoDB 表是基于聚簇索引建立的，聚簇索引对主键查询有很高的性能。
  • InnoDB 内部做了很多优化，包括从磁盘读取数据时采用的可预测性预读，能够自动在内存中创建 hash 索引以加速读操作的自适应 hash 索引。
  • InnoDB 的行为是非常复杂的，不容易理解。
  • 作为事务型的存储引擎，InnoDB 支持热备份。

2、MyISAM 存储引擎

  • MySQL5.1之前的版本，MyISAM 是默认的存储引擎。
  • 不支持事务和行级锁
  • 崩溃后无法安全恢复
  • MyISAM会将表存储在两个文件中：数据文件 .MYD 和索引文件 .MYI

特性：

  • MyISAM 对整张表加锁
  • 可以手动或者自动执行检查和修复操作
  • 支持 BLOB和 TEXT 的索引，支持全表索引
  • 延迟更新索引键

压缩表：

  • 对不会变的表可以进行压缩，极大地减少空间占用，减少磁盘 IO，提高查询性能，也支持只读索引
  • 每条记录是独立压缩

MyISAM 性能：

3、其他内建索引

4、第三方存储引擎

  • OLTP 类引擎
  • 面向列的存储引擎
  • 社区存储引擎

5、转换表的引擎

1）ALTER TABLE

ALTER TABLE mytable ENGINE=InnoDB

  • 适合于任何存储引擎
  • 执行需要很长时间，按行将数据从原表复制到一张新的表中，原表会加读锁
  • 转换表的引擎将会失去原引擎相关的所有特性

2）导出与导入

用mysqldump工具将数据导出到文件，然后修改文件中 CREATE TABLE语句的存储引擎选项，注意同时修改表名。

mysqldump默认会自动在 CREATE TABLE 语句前面加上 DROP TABLE语句

3）创建与查询

综合了方法一的高效和方法二的安全

  • 不需要导出整个表的数据

  • 先创建一个新的存储引擎的表，然后利用 INSERT……SELECT语法来导数据

    create table innodb_table like myisam_table;
alter table innodb_table engine=InnoDB;
insert into innodb_table select * from myisam_table;

  • 数据量大的可以分批处理