说明:内容均摘自《高性能MySQL》,边读边记。
目录
一、MySQL服务器逻辑架构
MySQL 服务器逻辑架构主要包含三层:
- 第一层是基于网络的客户端/服务器,实现的连接处理、授权认证、安全等。
- 第二层是 MySQL 核心服务功能,包括查询解析、分析、优化、缓存以及所有的内置函数(例如,日期、时间、数学和加密函数),所有跨存储引擎的功能实现:存储过程、触发器、视图等。
- 第三层是存储引擎。存储引擎负责 MySQL 中数据的存储和提取。服务器通过 API 与存储引擎进行通信,这些接口屏蔽了不同存储引擎之间的差异,使得这些差异对上层的查询过程透明。存储引擎 API 包含几十个底层函数,用于执行例如 “开启一个事务” 或者 “根据主键提取一行记录”等操作。但是存储引擎不会解析 SQL(但是 innoDB 是一个例外,它会去解析外键定义),不同存储引擎之间也不会相互通信,而是简单地响应上层服务器的请求。
MySQL 如何保证连接的安全性?
客户端连接 MySQL 服务器,服务器需要对其进行认证,认证基于用户名、原始主机信息和密码,如果使用了安全套接字(SSL),还可以使用 X.509 证书认证,连接成功以后,服务器会继续验证该客户端是否具有某个特定的查询权限。
MySQL 优化和执行是如何进行的?
MySQL 会解析查询,创建内部数据结构(解析树),然后对其优化,优化内容包括:重写查询、决定表的读取顺序,以及选择合适的索引等。
用户可以通过特殊关键字提示(hint)优化器,影响它的决策过程。
也可以请求优化器解释(explain)优化过程的各个因素,使用户可以知道服务器是如何进行优化决策的,并提供一个参考标准,便于用户重构查询和 schema、修改相关配置,使应用尽可能高效运行。
优化器不关心表使用的是什么存储引擎,但是存储引擎对于优化查询是有影响的。优化器会请求存储引擎提供容量或某个具体操作的开销信息,以及表数据的统计信息等。
对于 SELECT 语句,在解析查询之前,服务器会先检查查询缓存(Query Cache),如果能够在其中找到对应的查询,服务器就不必再执行查询解析、优化和执行的整个过程,而是直接返回查询缓存中的结果集。
MySQL 是如何控制并发的?
MySQL 处理并发读或者写的时候,通过实现由两种类型的锁组成的锁系统来解决问题,这两种类型的锁通常被称为共享锁(shared lock)和排他锁(exclusive lock),也叫读锁(read lock)和写锁(write lock)。
读锁:是共享的,相互不阻塞,多个客户在同一时刻可以同时读取同一个资源,而互不干扰。
写锁:是排他的,也就是说一个写锁会阻塞其他的写锁和读锁,只有这样才能确保在给定的时间里,只有一个用户能执行写入,并防止其他用户读取正在写入的同一资源。
MySQL 还会通过控制锁的粒度来提高共享资源并发性,让锁定对象更有选择性。尽量只锁定需要修改的部分数据,而不是所有的资源。问题是加锁也需要消耗资源,锁的各种操作,包括获得锁、检查锁是否已经解除、释放锁,都会增加系统的开销,如果系统花费大量的时间来管理锁,而不是存取数据,那么系统的性能可能会因此受影响。
锁策略,就是在锁的开销和数据安全之间寻求平衡,这种平衡也会影响到性能。大多数商业数据库系统没有提供更多的选择,一般都是在表上施加行级锁(row-level-lock),以便在锁比较多的情况下尽可能提供更好的性能。
每种 MySQL 存储引擎都可以实现自己的锁策略和锁粒度,在存储引擎的设计中,锁管理是个非常重要的决定,将锁粒度固定在某个级别,可以为特定的应用场景提供更好的性能。
MySQL 的表锁
表锁是 MySQL 中最基本的锁策略,也是开销最小的策略,锁定整张表,一个用户在对表进行写操作(插入、删除、更新等)前,需要先获取写锁,同时会阻塞其他用户对该表的所有读写操作,只有没有写锁的时候,其他读取的用户才能获得读锁,读锁之间不相互阻塞。
在特定的场景中,表锁也可能有良好的性能,例如 READ LOCAL 表锁支持某些类型的并发写操作,另外,写锁也比读锁有更高的优先级,因此一个写锁请求可能会被插入到读锁队列的前面(写锁可以插入到锁队列中读锁的前面,反之读锁则不能插入到写锁的前面)。
尽管存储引擎可以管理自己的锁,MySQL 本身还是会使用各种有效的表锁来实现不同的目的。例如,服务器会为诸如 ALTER TABLE 之类的语句使用表锁,而忽略存储引擎的锁机制。
MySQL 的行级锁(row lock)
行级锁可以最大程度地支持并发处理(同时也带来了最大的锁开销)。InnoDB 和 XtraDB,以及其他一些存储引擎中实现了行级锁。行级锁只在存储引擎层实现,而 MySQL 服务器层没有实现。服务器层完全不了解存储引擎中的锁实现。所有的存储引擎都以自己的方式实现了锁机制。
MySQL 的事务
事务就是一组原子性的 SQL 查询,或者说一个独立的工作单元,具备 ACID 特性,A 表示原子性 (atomicity)、C 表示一致性 (consistency)、I 表示隔离性(isolation)还有 D 代表持久性(durablilty)。一个运行良好的事务处理系统必须通过严格的 ACID 测试。
MySQL 提供了两种事务型的存储引擎:InnoDB 和 NDB Cluster。
MySQL 默认采用自动提交(AUTOCOMMIT)模式,也就是说,如果不是显式地开启一个事务,则每个查询都被当作一个事务执行提交操作。在当前连接中可以通过设置 AUTOCOMMIT 变量来启用或者禁用自动提交模式。
四种隔离级别
一、READ UNCOMMITED (读未提交)
会产生脏读,读取到其他事务中没有提交的数据,Dirty Read 会导致很多问题,从性能上来说,READ UNCOMMITED 不会比其他的级别好太多,但却缺乏其他级别的很多好处,除非真的有非常必要的理由,在实际应用中一般很少使用。
二、READ COMMITED(读已提交)
大多数数据库系统的默认隔离级别都是 READ COMMITTED ( MySQL 不是)。READ COMMITTED 满足前面提到的隔离性的简单定义:一个事务开始时,只能“看见”已经提交的事务所作的修改。这种级别也叫不可重复度(nonerepeatable read),因为两次执行同样的查询,可能会得到不一样的结果。
三、REPEATABLE READ(可重复读)
REPEATABLE READ 解决了脏读的问题,该级别保证了在同一个事务中多次读取同样记录的结果是一致的。理论上,可重复读还是无法解决另外一个幻读(Phantom Read)的问题。
什么是幻读?
幻读指的是当某个事务在读取某个范围内的记录时,另外一个事务又在该范围内插入了新的记录,当之前的事务再次读取该范围的记录时,会产生幻行(Phantom Row)。InnoDB 和 Xtra-DB 存储引擎通过多版本并发控制(MVCC,Multiversion Concurrency Control)解决了幻读的问题。
SERIALIZABLE (可串行化)
SERIALIZABLE 是最高的隔离级别。它通过强制事务串行执行,避免了前面说的幻读的问题。简单地来说,SERIALIZABLE 会在读取的每一行数据上都加锁,所以可能导致大量的超时和锁争用的问题。实际应用中很少用到这个隔离级别,只有在非常需要确保数据的一致性而且可以接受没有并发的情况下,才考虑采用该级别。
隔离级别 | 脏读可能性 | 不可重复读可能性 | 幻读可能性 | 加锁读 |
---|---|---|---|---|
READ UN-COMMITTED | Yes | Yes | Yes | No |
READ COMMITTED | No | Yes | Yes | No |
REPEATABLE READ | No | No | Yes | No |
SERIALIZABLE | No | No | No | Yes |
死锁
死锁是指两个或者多个事务在同一资源上相互占用,并请求锁定对方占用的资源,从而导致恶性循环的现象。
当多个事务试图以不同的顺序锁定资源时,就可能会产生死锁。
多个事务同时锁定同一个资源时,也会产生死锁。
InnoDB 目前处理死锁的方法是,将持有最少行级排他锁的事务进行回滚。
事务日志
事务日志可以帮助提高事务的效率,存储引擎在修改表的数据时只需要修改其内存拷贝,再把该修改行为记录持久化到硬盘上的事务日志中,而不用每次都将修改的数据持久到磁盘。事务日志采用的是追加的方式,因此写日志的操作是磁盘上一小块区域内的顺序 I/O ,而不像随机 I/O 需要在磁盘的多个地方移动磁头,所以采用事务日志的方式相对来说要快得多。我们通常称之为预写式日志(Write-Ahead Logging),修改数据需要写两次磁盘,如果数据的修改已经记录到事务日志中并持久化,但数据本身还没有写回磁盘,此时系统崩溃,存储引擎在重启时能够自动恢复这部分修改的数据。