MySQL系列-- 1.MySQL架构

9,405 阅读16分钟

MySQL架构

[TOC]

1. 逻辑架构

MySQL逻辑架构图
MySQL逻辑架构图

  • 第一层为客户端的连接认证,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(可串行化):
    • 最高级别的隔离级别,强制事务串行执行。
    • 在读取的每一行数据上都加锁,导致大量的超时和锁争用问题。
隔离级别 脏读可能性 不可重复读可能性 幻读可能性 加锁读
未提交读 Yes Yes Yes No
提交读 No Yes Yes No
可重复读 No No Yes No
可串行化 No No No Yes

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提交当前事务。

      SQL类型
      SQL类型

    • 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;
    -- 如需要可对原表加锁,保证数据一致性。