MySQL架构

[TOC]

1. 逻辑架构

• 第一层为客户端的连接认证，C/S都有此架构
• 第二层为服务器层，包含MySQL的大多数核心服务功能
• 第三层包含了存储引擎，服务器通过API与其通信，API规避了不同存储引擎的差异，不同存储引擎也不会互相通信，另外存储引擎不会去解析SQL(InnoDB是例外，它会解析外键定义，因为服务器本身没有实现该功能)

1.1 连接管理及安全性

• 每个客户端在服务器进程中拥有一个线程

• 服务器会负责缓存线程，不需要为每一个新建的连接创建或销毁线程(5.5以后版本提供了线程池，可使用少量线程来服务大量连接)

• 服务器基于用户名、原始主机信息和密码对客户端进行认证，连接成功后会验证某个特定操作的权限。

  ​

1.2 优化和执行

• MySQL会解析查询，创建内部数据结构(解析树)，并对其进行各种优化(重写查询、决定表的读取顺序、选择适合的索引)
• 用户可以通过特殊的关键字提示(hint)优化器，影响MySQL的决策过程。也可以请求优化器解释(explain)优化过程的各个因素，便于用户重构查询和schema，修改相关配置
• 优化器不关心表使用的存储引擎，但是存储引擎对优化查询有影响。优化器会请求存储引擎提供容量或某个具体操作的开销信息，已经表数据的统计信息等。
• 对于SELECT语句，在解析查询前，服务器会先检查查询缓存(Query Cache)。

2. 并发控制

两个层面的并发控制: 服务器层和存储引擎层

2.1 读写锁

• 共享锁(shared lock), 读锁(read lock)：共享，相互不阻塞
• 排他锁(exclusive lock), 写锁(write lock)：排他(会阻塞其它的读写锁)

2.2 锁粒度

• 一种提供共享资源的并发性：让锁定对象更有选择性。尽量只锁定需要修改的部分数据\数据片，锁定的数据量越少，并发程度越高。
• 锁的各种操作(获得锁，检查锁是否解除，释放锁)需要消耗资源。
• 锁策略:
  • 表锁(table lock)：
    • 最基本、开销最小的策略。服务器层实现
    • 锁定整张表。写锁阻塞其它锁，读锁互不阻塞。
    • 特定场景下，表锁也可能有良好的性能。如READ LOCAL表锁支持某些类型的并发写操作。写锁比读锁有更高优先级，可能会被插入到读锁队列的前面。
    • 存储引擎可以管理自己的锁，服务器层还是会使用各种有效的表锁去实现不同的目的。如，服务器会为ALTER TABLE等语句使用表锁，而忽略存储引擎的锁机制。
  • 行级锁(row lock):
    • 最大程度地支持并发处理，也带来了最大的锁开销。
    • 只在存储引擎实现(InnnoDB和XtraDB等)

3. 事务

• 事务是一组原子性的SQL查询，或者说一个独立的工作单元。
• 事务的ACID:
  • 原子性(atomicty)：一个不可分割的最小工作单元。
  • 一致性(consistency)：数据库总是从一个一致性状态转换到另一个一致性的状态。
  • 隔离性(isolation)：一个事务所做的修改在最终提交以前，对其他事务是不可见的。
  • 持久性(durability)：事务提交后，其所做的修改会永久保存到数据库中。(没有100%的持久性持久性保证策略)

3.1 隔离线的隔离级别

SQL标准中的四种隔离级别:

• READ UNCOMMITED(未提交读)：
  • 事务中的修改，即使没有提交，对其它事务也是可见的。
  • 事务读取未提交的数据，称为脏读(Dirty READ)。
  • 性能并不比其它级别好太多，但确少了很多好处，不推荐。
• READ COMMITED(提交读)、不可重复读(nonrepeatable read)：
  • 大多数数据库的默认级别，但MySQL不是。
  • 一个事务只能读取已提交的事务所做的修改，换句话说，一个事务从开始直到提交之前，所做的任何修改对其它事务都是不可见的。
  • 可能会导致虚读，如事务A两次读取数据，事务B在这之间修改了数据，这两次读取会有不一样的结果，即可读取其它事务的增删改
• REPEATABLE READ(可重复读)：
  • MySQL默认级别。
  • 解决脏读问题，保证在同一个事务中多次读取同样记录的结果是一致的。
  • 可能会导致幻读(Phantom Read)，事务A读取并修改数据，事务B也在该范围修改了数据(插入或删除)，事务A再次读取该范围的数据，发现了幻行(Phantom Row)，即可读取其它事务的增删
• SERIALIZABLE(可串行化)：
  • 最高级别的隔离级别，强制事务串行执行。
  • 在读取的每一行数据上都加锁，导致大量的超时和锁争用问题。

3.2 死锁

• 死锁是指两个或多个事务在同一个资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。
• 产生原因：
  • 真正的数据冲突
  • 存储引擎的实现方式
• 解决方案(部分或完全回滚其中一个事务)：
  • 死锁检测。InnoDB处理死锁的方法：将持有最少行级的排它锁的事务进行回滚(比较简单的回滚算法)
  • 死锁超时机制，超时后放弃对锁的请求

3.3 事务日志(预写式日志，Write-Ahead Logging)

• 使用事务日志，存储引擎修改表数据，只需修改其内存拷贝，再将该行为记录到持久在硬盘的事务日志中。
• 大多数存储引擎的实现方案，修改数据需写两次磁盘(第一次为日志记录，第二次为数据)
• 优点：
  • 提高事务的效率
  • 速度快。采用追加方式，写日志的操作是磁盘一小块区域的顺序IO，而不是多区域的随机IO。

3.4 MySQL中的事务

事务型的存储引擎：MySQL的如InnoDB和NDB Cluster，第三方的如XtraDB和PBXT。

• 自动提交(AUTOCOMMIT):

  • MySQL的默认模式。如不显示地开始一个事务，每个查询都被当作一个事务执行提交操作。

  • 在当前连接可以通过设置AUTOCOMMIT变量来启用或禁用自动提交模式。当禁用时，所有的查询都是在一个事务中，直到显式地执行COMMIT或ROLLBACK，该事务结束后又开始另一个新的事务。

    mysql> SHOW VARIABLES LIKE 'AUTOCOMMIT';
    mysql> SET AUTOCOMMIT = 1;

  • 对非事务型地表，如MyISAM或者内存表，不会有任何影响。因为这类表没有COMMIT或ROLLBACK的概念，也就是说一直处于AUTOCOMMIT模式。

  • 在数据定义语言(DDL)中，如ALTER TABLE、LOCK TABLES等导致大量数据改变的操作，会在执行之前强制COMMIT提交当前事务。

    

  • MySQL可以在当前会话设置隔离级别，新的隔离级别会在下一个事务开始的事后生效。

    mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITED;

  • MySQL能够识别所有的4个ANSI隔离级别，InnoDB引擎支持所有的隔离级别。

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

  • MySQL服务器层不管理事务，事务是由下层的存储引擎实现的。所以在同一个事务，使用多种存储引擎不可靠。
  • 如在事务中混合使用存储引擎，如InnoDB和MyISAM，正常情况下不会有问题，但需要回滚时，非事务型的表上的变更无法撤销，导致数据库处于不一致的状态，而且MySQL对非事务型的表操作不会有提示。
  • 总结：最好不要在一个事务中混合使用存储引擎

• 隐式和显式锁定

  • 隐式锁定：

    • InnoDB采用两阶段锁定协议(two-phase locking protocol)。事务执行过程中，随时都可以执行锁定，锁只在COMMIT或ROLLBACK时被释放，并且所有的锁都在同一时间释放。InnoDB会根据隔离级别在需要的时候自动加锁。

  • 显式锁定：

    • MySQL可通过特定语句进行显式锁定，这些语句不属于SQL规范，尽量避免使用

      SELECT ... LOCK IN SHARE MODE
      SELECT ... FOR UPDATE

  • 其它锁定

    • MySQL支持LOCK TABLES和UNLOCK TABLES语句，这是在服务器层实现的，和存储引擎无关。并不能代替事务处理。在InnoDB使用，会严重影响性能，效果没有它的行级锁好
    • 建议：除了事务中禁用AUTOCOMMIT，可以使用LOCK TABLES之外，其余任何使用都不要显式地执行，不管使用地是什么存储引擎。

4. 多版本并发控制(MVVC)

MySQL的大多数存储引擎，Oracle以及PostgreSQL都实现MVVC，但实现的机制不尽相同，典型的有乐观并发控制和悲观并发控制。

• 目的：

  • 鄙弃简单地行级锁，可视为行级锁的变种，提升并发性能。
  • 在很多情况下避免了加锁操作，开销更低。
  • 实现了非阻塞的读操作，写操作也只锁定必要的行

• 实现原理：

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

• InnoDB简化版的MVVC工作原理:

  ​ 通过在每行记录后面保存两个隐藏的列，一列保存了行的创建时间，一个保存行的过期时间。存储的并不是实际的时间值，而是系统版本号。每一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。

  ​ 只在REPEATABLE READ和READ COMMITED两个隔离级别下工作。

  ​ 优点：大多数读操作不用加锁，读操作简单，性能很好，也保证只会读到符合标准的行。

  ​ 缺点：每行记录都需要额外的存储空间，需要更多的行检查及额外的维护工作。

  在REPETABLE READ隔离级别下，MVVC的工作：

  • SELECT：

    根据以下两个条件检查每行记录，符合的记录才能返回查询结果

    • 只查找版本号小于等于当前事务版本号的数据行。确保数据行是在事务开始前已经存在的，或者是事务本身插入或者修改过的。
    • 行的删除版本号要么未定义，要么大于当前事务版本号。确保数据行在事务读取前未被删除。

  • INSERT:

    为新插入的每一数据行保存当前系统版本号为行版本号

  • DELETE

    为删除的每一行保存当前系统版本号作为行删除标识。

  • UPDATE:

    插入一条新的记录，保存当前系统版本号为行版本号，保存当前系统版本号到原来的行作为行删除标识。

5. MySQL存储引擎

​ 在文件系统中，MySQL将每个数据库(schemaa)保存为数据目录下的一个子目录，将数据库的每个表在该子目录下创建一个和表同名的.frm文件保存表的定义。

-- 表路径：/MySQL_data/MyDB/Mytalbe.frm
mysql> SHOW TABLE STATUS LIKE 'MyTABLE'\G;

5.1 InnoDB存储引擎

MySQL默认事务型存储引擎，拥有良好的性能和自动崩溃恢复特性。

• 设计目的：处理大量的短期(short-lived)事务(短期事务大部分情况是正常提交的，很少被回滚)
• 概览：
  • 数据存储在表空间(tablespace)中，由InnoDB管理的黑盒子，有一系列的数据文件组成。
  • 采用MVVC支持高并发，实现四个标准的隔离级别，默认为REPEATABLE READ，并且通过间隙锁(next-key locking)策略使得InnoDB锁定查询涉及的行，还会对索引中的间隙进行锁定，防止幻读出现。
  • 基于聚簇索引建立，对主键查询有很高的性能。但是二级索引(secondary index，非主键索引)必须包含主键列，如主键索引过大，其它的所有索引都会很大。
  • 从磁盘读取数据采用可预测性预读、自动在内存中创建hash索引以加速读操作的自适应索引(adaptive hash index)、加速插入操作的插入缓冲区(insert buffer)
  • 通过一些机制和工具支持真正的热备份
  • 建议：有空阅读官方手册中的”InnoDB事务模型和锁”一节

5.2 MyISAM存储引擎

​ MySQL5.1及之前版本的默认存储引擎。支持全文索引、压缩、空间函数(GIS)，不支持事务和行级锁，并且崩溃后无法安全恢复。对于只读数据，或者表比较小，可以忍受修复(repair)操作，可以考虑MyISAM。

• 存储：表以.MYD和.MYI的数据文件和索引文件存储在文件系统。
• 特性：
  • 加锁与并发：对整张表而不是特定行加锁。读取时对读到的表加共享锁，写入时则加排它锁。支持并发插入(CONCURRENT INSERT)，在读取查询的同时，也可以插入新的数据。
  • 修复：与事务恢复以及崩溃恢复是不同的概念。速度慢，可能会导致数据丢失。通过CHECK TABLE mytable 检查表的错误，REPAIR TABLE mytable 进行修复。
  • 索引特性：支持全文索引，这是基于分词创建的索引。即使是BOLB和TEXT等长字段，也可以基于前500个字符创建索引。
  • 延迟更新索引键(Delayed Key Write)：如指定了DELAY_KEY_WRITE，每次修改执行完成时，不会将修改的索引数据写入磁盘而是写到内存中的键缓存区(in-memory key buffer)，只有在清理键缓存区或关闭表的时候才会写入磁盘。可极大提升写入性能，但可能在数据库或主机崩溃时造成索引损坏而执行修复操作。
• 压缩表：只进行读操作可采用压缩表，极大减少磁盘占用空间以及IO，从而提升查询性能。
• 性能：设计简单，数据以紧密格式存储，在某些场景下的性能很好。最典型的性能问题为表锁。

5.3 MySQL内建的其它存储引擎

• Archive引擎：非事务型对告诉插入和压缩做优化的引擎。支持INSERT、SELECT和索引，每次SELECT都需要全表扫描，并阻止其它SELECT执行，以实现一致性读；支持行级锁和专用缓冲区，实现高并发插入。适合日志和数据采集类应用。
• Blackhole引擎：没有实现任何的存储机制，因为它丢弃所有插入的数据，不做保存，但是服务器会记录Blackhole表的日志，所以可以用于复制数据到备库，或者简单地记录到日志。适合特殊的复制架构和日志审核，但并不推荐。
• CSV引擎：将CSV文件作为MySQL表来处理，但不支持索引。适合作为一种数据交换的机制。
• Federated引擎：访问其它MySQL服务器的代理，创建一个远程MySQL的客户端连接，将查询传到远程服务器执行，然后提取或发送需要的数据。
• Memory引擎：
  • 所有数据保存在内存中，不需要磁盘IO，比MyISAM快一个数量级。
  • 支持Hash索引，但是是表级锁，因此并发写入性能低
  • 不支持BOLB和TEXT的列，并且每行的长度是固定的。
  • 适合快速地访问数据，并且这些数据不会修改，重启以后丢失也没关系(数据会丢失，表结构仍保留)。
  • 应用场景：
    • 用于查找(lookup)或者映射(mapping)表
    • 用于缓存周期性聚合数据(periodically aggregated data)的结果
    • 用于保存数据分析中产生的中间数据
  • MySQL执行查询中需要使用临时表来保存中间结果，使用的就是Memory表，如果数据太大或者含有BLOB或TEXT字段，则使用MyISAM表。
  • Memory表并不是CREATE TEMPORARY mytable 创建的表，后者可以使用任何的存储引擎。
• Merge引擎：弃用
• NDB集群引擎：参加MySQL集群

5.4 选择合适的存储引擎

• 除非需要用到某些InnoDB不具备的特性，并且没有其它办法可以代替，否则都应该优先选择InnoDB引擎。
• 不要混合使用多种存储引擎。
• 考虑因素：
  • 事务
  • 备份
  • 崩溃恢复
  • 特有的特性
• 应用场景：
  • 日志型应用：
    • 使用MyISAM或Archive存储引擎
    • 采用主备架构，用于读写分离
    • 日志记录表的明显包含日期信息

5.5 转换表的引擎

转换引擎会失去与原引擎相关的特性。

• ALTER TABLE

  mysql> ALTER TABLE mytable ENGINE = InnoDB;

  需执行很长实际，数据库将数据从原表复制到一个新表，会消耗掉系统所有的IO能力。

• 导出和导入

  使用mysqldump，修改其中的CREATE TABLE语句。

• 创建和查询(CREATE和SELECT)

  mysql> CREATE TABLE innodb_table LIKE myisam_table;
  mysql> ALTER TABLE innodb_table ENGINE=InnoDB;
  mysql> INSERT INTO innodb_table SELECT * FROM myisam_table;
  -- 如数据量过大，可分批处理。
  mysql> START TRANSACTION;
  mysql> INSERT INTO innodb_table SELECT * FROM myisam_table where id BETWEEN x AND y;
  mysql> COMMIT;
  -- 如需要可对原表加锁，保证数据一致性。

  ​