高性能MySQL——第一章 MySQL 架构与历史

本章主要了解：

• MySQL的服务器架构
• 各种存储引擎之间的主要区别
• 这些区别的重要性

MySQL 逻辑架构

一共三层服务：

• 最上层并不是MySQL独有，大多基于网络的客户端/服务器的工具或服务都有类似架构。
• 第二层包含了大多数MySQL的核心服务功能。包括查询解析、分析、优化、缓存以及所有的内置函数（例如，日期、时间、数学和加密函数），所有跨存储引擎的功能都在这一层实现：存储过程、触发器、视图。
• 第三层包含了存储引擎。存储引擎负责MySQL中数据的存储和提取。服务器通过API与存储引擎进行通信，存储引擎并不会解析SQL，就是简单响应上层服务器的请求。

1 连接管理与安全性

• 连接：
  每个客户端连接都会在服务器进程中拥有一个线程，查询只在这个线程中执行。

• 认证：
  若客户端要连接到MySQL服务器时，服务器需对其进行认证（基于用户名、原始主机信息和密码）。
  即使连接成功，还会继续验证该客户端是否具有执行某个特定查询的权限。

2 优化与执行

• 解析
  当一个查询到来时，MySQL会解析此查询，创建内部解析树。

• 优化
  接着对其优化，如重写查询、决定表的读取顺序，以及选择合适的索引等。
  用户可以 提示 优化器影响优化过程，也可请求优化器 解释 优化过程。

• 查询缓存
  对于SELECT语句，服务器在解析查询之前先检查查询缓存，如果能找到就直接返回结果集。

并发控制

当存在多个查询，就会产生并发控制。

MySQL在两个层面上有并发控制：

• 服务器层

• 存储引擎层

如果两个进程在同一时刻对同一个邮箱投递邮件，如果什么也不做，邮箱里的数据会被破坏。所以一般会设计通过 锁 来防止数据损坏。一旦客户试图投递邮件，而邮箱已经被其他客户锁住，那就必须等待，直到锁释放才能进行投递。

因为在任意一个时刻，只有一个进程可以修改邮箱的数据，所以这种锁机制不支持并发处理。

1 读写锁

读数据时不会出错。
但是如果某个客户正在读取邮箱，同时另外一个用户试图删除编号为25的邮件，读的客户可能会报错退出，也可能读取到不一致的邮箱数据。因此读取邮箱也需要注意。

可以使用 并发控制 解决这类问题。实现一个由两种类型的锁：共享锁（读锁） 和 排它锁（写锁）。

• 读锁
  读锁是共享的，互不阻塞。多个客户在同一时刻可以同时读取同一个资源，而互不干扰。
• 写锁
  写锁是排他的，一个写锁会阻塞其他的写锁和读锁（注意两种锁都被阻塞）。

2 锁粒度

提高共享资源并发性方法之一：尽量只锁定需要修改的部分数据。

锁策略：
在锁的开销和数据的安全性之间寻求平衡。
MySQL提供了多种选择。

• 表锁 （table lock)
  会锁定整张表。一个用户在对表进行写操作（插入、删除、更新等）前，需要先获得写锁，这会阻塞其他用户对该表的所有读写操作。只有没有写锁时，其他读取的用户才能获得读锁，读锁之间是不相互阻塞的。

• 行级锁 （row lock)
  每次锁定的是一行数据的锁机制就是行级别锁定。可最大程度支持并发处理，行级锁只在存储引擎层实现，但是实现复杂，开销大。

事务

事务就是一组原子性的SQL查询。满足ACID

• Atomicity 原子性
  整个事务中所有操作要么全部提交成功，要么全部失败回滚。不可能只执行其中的一部分操作。

• Consistentcy 一致性
  数据库总是从一个一致性的状态转换到另外一个一致性的状态。

• Isolation 隔离性
  通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。

• Durability 持久性
  一旦事务提交，则其所做的修改就会永久保存到数据库中。

1 隔离级别

SQL定义了四种隔离级别：

• READ UNCOMMITTED（未提交读）
  事务中的修改，即使没有提交，对其他事务也都是可见的。所以会出现脏读。
• READ COMMITTED（提交读）
  一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。也叫不可重复读。
• REPEATABLE READ （可重复读）
  保证了在同一个事务中多次读取同样记录的结果是一致的。会产生幻读。
• SERIALIZABLE（可串行化）
  最高的隔离级别，强制事务串行执行。会出现超时和锁争用问题。很少用。

2 死锁

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

• 当多个事务试图以不同的顺序锁定资源时，就可能会产生死锁。
• 多个事务同时锁定同一个资源时，也会产生死锁。

InnoDB处理死锁的方法：将持有最少行级排他锁的事务进行回滚。只有部分或完全回滚其中一个事务，才能打破死锁。

3 事务日志

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

使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把该修改行为 记录到 持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。

事务日志采用 追加 的方式，因此写日志的操作是磁盘上一小块区域内的顺序I/O，而不像随机I/O在磁盘多处移动磁头，所以快得多！

预写式日志：事务日志持久以后，内存中被修改的数据在后台可以慢慢地刷回到磁盘。

4 MySQL 中的事务

MySQL提供两种事务型存储引擎：

• InnoDB
• NDB Cluster

另外还有一些第三方存储引擎，如 XtraDB 和 PBXT。

自动提交

若没有显式开始一个事务，则每个查询都会被当作一个事务执行提交操作。MySQL默认采用此模式。
可通过设置 AUTOCOMMIT 启动或禁用自动提交模式。AUTOCOMMIT = 1表示启用，AUTOCOMMIT=0表示禁用。如果禁用，必须显式执行 COMMIT 提交 或 ROLLBACK 回滚。

在事务中混合使用存储引擎

MySQL服务器层不管理事务，事务是由下层的存储引擎实现的。

如果在事务中混合使用了事务型和非事务型的表（例如InnoDB和MyISAM表），正常提交不会有什么问题。但如果该事务要回滚，非事务型的表上的变更就无法撤销，这会导致数据库处于不一致的状态。

隐式和显式锁定

InnoDB采用的是两阶段锁定协议。

• 隐式锁定
  在事务执行过程中，随时都可以执行锁定，锁只有在执行 COMMIT 或者 ROLLBACK 的时候才会释放，并且所有的锁是在同一时刻被释放。

• 显式锁定
  
  MySQL也支持LOCK TABLES 和UNLOCK TABLES 语句，这是在服务器
  层实现的，和存储引擎无关。

多版本并发控制 MVCC

基于提高并发性能的考虑，MySQL实现了MVCC。

MVCC可以认为是行级锁的一个变种。在很多情况避免加锁操作，开销低，但大都实现非阻塞的读操作，写操作只锁定必要的行。

MVCC通过保存数据在某个时间点的快照实现的。不管需要执行多长时间，每个事务看到的数据都是一致的。根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。

不同存储引擎的MVCC实现是不同的，典型的有：

• 乐观并发控制

• 悲观并发控制

MVCC 只在 REPEATABLE READ 可重复读 和 READ COMMITTED 提交读 两个隔离级别下工作。

MySQL的存储引擎

InnoDB 存储引擎

InnoDB 是 MySQL 默认事务型引擎，最重要最广泛。用来处理大量短期事务（大部分情况是正常提交的，很少会被回滚）。

数据存储在 表空间 中，它是由一系列数据文件组成。InnoDB可以将每个表的数据和索引存放在单独的文件中。

InnoDB采用MVCC支持高并发，并实现四个标准的隔离级别，默认可重复读，通过间隙锁防止幻读（使得InnoDB不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定，以防止幻影行的插入）。

InnoDB基于聚簇索引建立的，对主键查询有很高的性能。

InnoDB内部做了很多优化，包括从磁盘读取数据时采用的可预测性预读，能够自动在内存中创建hash索引以加速读操作的自适应哈希索引（adaptive hash index），以及能够加速插入操作的插入缓冲区（insertbuffer）等。

InnoDB支持热备份（是系统处于正常运转状态下的备份）。

MyISAM 存储引擎

MyISAM不支持事务和行级锁，且崩溃后无法安全恢复。

存储：MyISAM会将表存储在两个文件中：数据文件和索引文件。

特性

• 加锁与并发
  MyISAM 对整张表加锁，而不是针对行。
• 修复
  MySQL可以手工或者自动执行检查和修复操作。
• 索引特性
  可以基于其前500个字符创建索引。也支持全文索引，这是一种基于分词创建的索引。
• 延迟更新索引键
  在每次修改执行完成时，不会立刻将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区，可以极大地提升写入性能，但可能在数据库或主机崩溃时造成索引损坏。

如果对表不进行修改操作，则可以采用MyISAM压缩表。通过myisampack对MyISAM表压缩，这样能极大减少磁盘空间占用和磁盘I/O。