Fork me on GitHub
王翔的技术博客

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

MySQL的特性是它的存储引擎架构

这种设计将查询处理(Query Processing)及其他系统任务(Server Task) 和数据的存储/提取相分离。

在使用时根据具体情况选择存储引擎。

MySQL逻辑架构

MySQL服务器逻辑架构图

由上图可以主要分为三层:

第一层架构 主要功能:连接处理,授权认证,安全等。

第二层架构 大多数MySQL的核心服务都在这一层:查询解析、分析、优化、缓存以及所有的内置函数(eg: 日期、时间、数学、加密等函数),所有跨存储引擎的功能都在这一层实现:存储过程、触发器、视图等。

第三层架构 包含了存储引擎 负责MySQL中数据的存储和提取。 服务器通过API与存储引擎进行通信,API屏蔽了不同引擎之间的差异。

连接管理与安全性

每个客户端连接都会在服务器进程中拥有一个线程,这个连接的查询只会在这个单独的线程中执行。

服务器会缓存线程或者使用线程池技术,不需要为每个连接创建一个线程,因而使用较少的线程来服务大量的连接。

当客户连接服务器的时候,服务器会对其进行认证,基于用户名、密码、主机信息。

客户连接上服务器,执行某个操作时,服务器会对其进行权限判断,判断该用户是否有权限进行该项操作。

优化与执行

MySQL会解析查询,并创建解析树,然后对其进行各种优化,包括重写查询、决定表的读取顺序,以及选取合适的索引等。

用户可以使用特殊的关键字提示(hint)优化器,影响其决策过程。

也可以请求优化器解释(explain)优化过程,让客户知道服务器如何进行优化。

以便用户修改查询和schema、修改相关配置,使之高效运行。

优化器并不关心表使用的是什么存储引擎,但存储引擎对于优化查询是有影响的。

对于SELECT语句,在解析查询之前,服务器会先检查查询缓存(Query Cache),如果能找到对应的查询,服务器就会直接返回查询缓存中的结果集。


并发控制

无论何时,只要有多个查询需要在同一时刻修改数据,就会产生并发控制问题。

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

MySQL的并发控制主要靠机制解决。

读写锁

读锁(read lock) 也叫 共享锁(shared lock)

写锁(write lock) 也叫 排他锁(exclusive lock)

读锁是共享的,是相互不阻塞的。多个客户可以同时读取同一个资源而不受干扰。

写锁是排他的,一个写锁会阻塞其他的读锁和写锁。

在实际的数据库系统中,每时每刻都会发生锁定。大多数时候,MySQL锁的内部管理都是透明的。

锁粒度

加锁会消耗资源。锁的各种操作,包括获得锁,检查所是否解除,释放锁,都会增加系统开销。

锁的粒度越小,就能对修改的数据片进行更精确的锁定,系统的并发程度就越高,但是系统的开销就越大。

表锁(table lock)

表锁是MySQL中最基本的一种锁策略,并且是开销最小的策略,它会锁定整张表。

当一个用户在对表进行写操作(插入、删除、更新等)前,需要先获取写锁,这会阻塞其他用户对该表的所有读写操作。

只有在没有写锁时,其他用户才能获得读锁。

尽管存储引擎可以管理自己的锁,MySQL本身还是会使用各种有效的表锁来实现不同的目的。

比如,服务器会为诸如ALTER TABLE之类的语句使用表锁,而忽略存储引擎的锁机制。

行级锁(row lock)

行级锁可以最大程度的支持并发操作(同时也带来了最大的锁开销)

行级锁只在存储引擎层次实现,而在MySQL服务器层没有实现,服务器层完全不了解存储引擎中的锁实现。

所有存储引擎都以自己的方式实现了锁机制。


事务

事务就是一组原子性的SQL查询,或者说一个独立的工作单元。

事务内的语句,要么全部成功,要么全部失败。

事务的ACID特性,表示原子性(atomicity)、一致性(consistency)、隔离性(isolation)、永久性(durability)

用户可以根据业务是否需要事务处理,来选择合适的存储引擎。

对于不需要事务的查询类应用,可以选择一个非事务型的存储引擎。

隔离级别

在SQL标准中定义了4种隔离级别,每一种级别都规定了一个事务中所做的修改,哪些在事务内和事务间是可见的,哪些是不可见的。

较低级别的隔离通常可以执行更高的并发,系统的开销也更低。

READ UNCOMMITTED (未提交读)

在这个级别,事务中的修改,即便没有提交,对其他事务也是可见的。事务可以读取未提交的数据,这也被成为脏读(Dirty Read)

这个级别会导致很多问题,性能也不会比别的级别好很多,也没有别的级别的优点,实际应用中很少使用。

READ COMMITTED (提交读)

大多数数据库系统的默认隔离级别都是这个(mysql不是)。

在这个级别,一个事务开始时,只能‘看到’已经提交的事务所作的修改。即:一个事务从开始知道提交之前,所作的修改对其他事务都是不可见的。

场景:

在一个事务内,多次读同一个数据。在这个事务还没有结束时,另一个事务也访问该同一数据。
那么,在第一个事务的两次读数据之间。
由于第二个事务的修改,那么第一个事务读到的数据可能不一样。
这样就发生了在一个事务内两次读到的数据是不一样的。

因而这个级别有时候也叫做不可重复读

REPEATABLE READ (可重复读)

这个级别是MySQL默认的事务隔离级别

这个级别解决了脏读的问题,保证在同一个事务中的多次读取同样记录的结果是一致的,但是该级别无法解决幻读(Phantom Read)的问题

所谓幻读:就是当一个事务在读取某个范围的记录时,另一个事务又在该范围插入了新的记录,之前的事务再次读取该范围的数据时,会产生幻行(Phantom Row)

SERIALIZABLE (可串行化)

这个级别是最高的隔离级别,它通过强制事务串行执行,避免了前面的幻读的的问题。

其他的隔离级别中,各个事务还是有一定程度的并发执行。

可串行化会在读取的每一行数据上都加锁,可能会导致大量的超时和锁争用的问题,实际中很少使用。

隔离级别 脏读可能性 不可重复读可能性 幻读可能性 加锁读
READ UNCOMMITTED YES YES YES NO
READ COMMITTED NO YES YES NO
REPEATABLE READ NO NO YES NO
SERIALIZABLE NO NO NO YES

死锁

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

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

多个事务同时锁定同一个资源时,也会产生死锁。

例如下面两个事务同时处理 StockPrice表:

事务1

1
2
3
4
START TRANSACTION;
UPDATE StockPrice SET close = 45.50 WHERE stock_id = 4;
UPDATE StockPrice SET close = 19.80 WHERE stock_id = 5;
COMMIT;

事务2

1
2
3
4
START TRANSACTION;
UPDATE StockPrice SET close = 19.80 WHERE stock_id = 5;
UPDATE StockPrice SET close = 45.50 WHERE stock_id = 4;
COMMIT;

如果刚好两个事务都执行了第一条UPDATE语句,更新了一条数据,同时也锁定了该行数据。

然后每个事务都尝试去执行第二条UPDATE语句,发现已经被对方锁定,然后都等待对方释放锁,又都同时持有对方需要的锁,则陷入死循环。除非有外部接入才可能解除死锁。

为了解决这个问题,数据库系统实现了各种死锁检测和死锁超时机制。

死锁发生后,只有部分或者完全回滚其中一个事务,才能打破死锁。

InnoDB处理死锁的办法是:将持有最少行级排他锁的事务进行回滚。

事务日志

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

事务采用追加的方式,因此写日志是对磁盘上一小块位置的顺序IO,速度较快。

事务日志持久化之后,内存中被修改的数据在后台可以慢慢的刷回到磁盘。如果系统崩溃,存储引擎在重启时可以根据事务日志恢复数据。

这种称之为预写式日志(Write-Ahead Logging),修改数据需要写两次磁盘。

MySQL中的事务

MySQL提供了两种事务型的存储引擎:InnoDB 和 NDB Cluster。

自动提交 (AUTOCOMMIT)

MySQL默认采用自动提交模式。也就是说,如果不是显示的开始一个事务,则每个查询都被当做一个事务执行操作。

在当前连接中,可以通过设置 AUTOCOMMIT 变量来启用或者禁用自动提交模式。

1
SHOW VARIABLES LIKE 'AUTOCOMMIT';
Variable_name Value
autocommit ON

对于非事务型的表,相当于一直处于AUTOCOMMIT的状态。

MySQL可以通过执行 SET TRANSACTION ISOLATION LEVEL XXX 命令来设置隔离级别,新的隔离级别会在下一个事务开始时生效。

可以在配置文件中设置整个数据库的隔离级别,也可以只改变当前会话的隔离级别:

1
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMIT

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

MySQL服务器层不管理事务,事务是由下层的存储引擎实现的。所以在同一个事务中使用多种存储引擎是不可靠的。

如果在事务中混合使用了事务型和非事务型存储引擎,在正常提交的情况下不会有什么问题。

但是如果该事务需要回滚,非事务型的表上的变更无法撤销。

在非事务型的表上执行事务相关操作的时候,MySQL通常不会发出提醒,也不会报错,只有在回滚的时候才会发一个警告:‘某些非事务型的表上的变更无法被回滚’。

隐式和显示锁定

InnoDB会根据隔离级别在需要的时候自动加锁,这种锁定叫做隐式锁定

另外,InnoDB也支持通过特定的语句进行显示锁定,这些语句不属于SQL规范。

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

MySQL也支持 LOCK TABLESUNLOCK TABLES 语句,这是在服务器层实现的,与存储引擎无关,不能替代事务处理。

但是建议任何时候都不要显示的执行 LOCK TABLES。


多版本并发控制

MySQL的大多数事务型存储引擎实现的都不是简单的行级锁。基于提升并发性能的考虑,一般都实现了 多版本并发控制(MVCC)

不仅是MySQL,包括Oracle、PostgreSQL等其他数据库系统都实现了MVCC,但实现的机制不尽相同,因为MVCC没有统一的实现标准。

MVCC可以认为是行级锁的一个变种,在很多情况下避免了加锁操作,因此开销更低。

虽然实现机制不同,但大都实现了非阻塞的读操作,写操作也只锁定必要的行。


Mysql的存储引擎

InnoDB 存储引擎

InnoDB存储引擎是MySQL自5.5版本以来的默认事务型引擎,也是最重要、使用最广泛的存储引擎。

它被设计用来处理大量的短期事务,在非事务行的存储的需求中也很流行。

除非有非常特别的原因需要使用其他的存储引擎,否则应该优先考虑使用InnoDB引擎。

MyISAM 存储引擎

在MySQL5.1及之前的版本,MyISAM是默认的存储引擎,MyISAM不支持事务和行级锁。

其他存储引擎

Archive引擎

Archive存储引擎只支持INSERT和SELECT操作

Backhole引擎

Backhole没有实现任何的存储机制,他会丢弃所有插入的数据,不做保存,服务器会记录Backhole表的日志。

CSV引擎

CSV引擎可以将普通的csv文件作为MySQL的表来处理,但是这种引表不支持索引。

Memory引擎

Memory引擎数据保存在内存中,查询速度快,但是重启之后数据丢失,表结构还会保留。

NDB集群引擎

MySQL服务器,NDB集群存储引擎,以及分布式的、share-nothing的、容灾的、高可用的NDB数据库的组合,被称为MySQL集群(MySQL Cluster)。

第三方存储引擎

MySQL从2007年开始提供插件式的存储引擎API,从此产生了许多为不同目的而设计的存储引擎

主要有: OLTP类引擎面向列的存储引擎社区存储引擎 等。

选择合适的存储引擎

大多数情况下,InnoDB都是正确的选择。

只有遇到一些比较特殊的需求时,才会考虑用其他引擎。


Mysql时间线

版本 时间 说明
版本3.23 2001 这个版本的发布被认为是Mysql真正诞生的时刻,以MyISAM引擎代替之前的ISAM引擎,引入全文索引和复制
版本4.0 2003 支持UNION和多表DELETE语法,重写了复制,InnoDB成为标配
版本4.1 2005 支持子查询和INSERT ON DUPLICATE KEY UPDATE,支持UTF-8字符集
版本5.0 2006 支持视图、触发器、存储过程和存储函数,ISAM代码被彻底移除
版本5.1 2008 支持分区、基于行的复制以及Plugin API
版本5.5 2010 InnoDB成为默认的存储引擎

1995 MySQL AB公司创建。

2008年 MySQL AB公司被SUN收购

2010年 SUN公司被Oracle收购

随着MySQL被Oracle收购,许多公司开始寻找替代品。

倒向MariaDB: 谷歌(2013年9月) RedHat(2013年6月) 维基百科(2013年4月)

倒向PostreSQL: 苹果(2011年)


坚持原创技术分享,您的支持将鼓励我继续创作!