【高性能MySQL笔记】 ---- MySQL架构

文章目录

1.1 MySQL 逻辑架构
1.2 并发控制
1.3 事务
1.4 多版本并发控制
1.5 MySQL的存储引擎

1.1 MySQL 逻辑架构

MySQL 使用典型的客户端/服务器（Client/Server）结构，逻辑结构图如下所示：

MySQL 体系结构大体可以分为三层：客户端、服务器层以及存储引擎层。其中，服务器层又包括了连接管理、查询缓存 、SQL 接口、解析器、优化器、缓冲与缓存以及各种管理工具与服务等。

1.1.1 连接管理与安全性

• 每个客户端都会在服务器进程中拥有一个线程,这个连接的查询只会在这个单独的线程中执行。
  服务器会缓存线程,不需要为每一个新建的连接创建或者销毁线程。
• 当客户端连接到MySQL服务器时,服务器需要对其进行认证。认证基于用户名、原始主机信息和密码。
  如果使用了安全套接字(SSL)方式连接,还可以使用X.509证书认证。客户端连接成功后,服务器会继续验证该客户端是否具有执行某个特定查询的权限。

1.2.1 优化与执行
MySQL会解析查询,并创建内部数据结构(解析树),然后对其各种优化,包括重写查询、决定表的读取顺序,以及选择合适的索引等。

对于SELECT语句,在解析查询之前,服务器会先检查查询缓存(Query Cache),如果能够在其中找到对应的查询,服务器就不必再执行查询解析、优化和执行的整个过程,而是直接返回查询缓存中的结果集。

1.2 并发控制

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

1.2.1 读写锁
多个用户并发读取数据时候,此时某个客户正在读取数据,另外一个用户试图修改数据;
此时读的客户可能会报错退出,也可能读取到不一致的数据
解决这类问题方法就是:并发控制

在处理并发读或写时,由两种类型的锁组成的锁系统来解决问题,分别是共享锁(shared lock)和排他锁(exclusive lock), 也叫读锁(read lock)和写锁(write lock)

• 读锁: 共享,相互不阻塞。多个客户在同一时刻可以同时读取同一个资源
• 写锁: 排他,会阻塞其他的写锁和读锁, 确保在给定时间内, 只有一个用户能执行写入, 防止其他用户读取正在写入的同一资源

1.2.2 锁粒度
一种提高共享资源并发性的方式是让锁定对象更有选择性。尽量只锁定需要修改的部分数据,而不是所有的资源。
在给定的资源上,锁定的数据量越少,则系统的并发程度越高,只要相互之间不发生冲突即可。

• 锁策略: 在锁的开销和数据的安全性之间寻求平衡,这种平衡也会影响到性能。

  大多数商业数据库系统一般都是在表头上施加行级锁(row lock),并以各种复杂的方式来实现,以便在锁比较多的情况下尽可能地提供更好的性能。

• 表锁(table lock): 是MySQL中最基本的锁策略,并且是开销最小的策略。锁定整张表,对表进行写操作(插入、删除、更新等)前,需要先获取写锁,阻塞其他用户对该表的所有读写操作。没有写锁时,其他读取的用户才能获取读锁,读锁之间不相互阻塞。

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

• 行级锁(row lock): 最大程度支持并发处理(同时带来了最大的锁开销)。在InnoDB和XtraDB以及其他一些存储引擎中实现了行级锁。行级锁只在存储引擎层实现,而MySQL服务器层没有实现

1.3 事务

事务:一组原子性的SQL查询,或者说一个独立的工作单元。事务内的语句,要么全部执行成功,要么全部执行失败

事务的特性: ACID

• 原子性(atomicity): 事务中的所有操作要么全部提交成功,要么全部失败回滚
• 一致性(consistency): 数据库总是从一个一致性的状态转换到另外一个一致性的状态
• 隔离性(isolation): 通常来说,一个事务所做的修改在最终提交以前,对其他事务是不可见的
• 持久性(durability): 一旦事务提交,其所做的修改会永久保存到数据库中

1.3.1 隔离级别

SQL标准中定义了四种隔离级别,较低级别的隔离通常可以执行更高的并发,系统的开销也更低。
四种隔离级别:

• READ UNCOMMITTED(未提交读)
  事务中的修改,即使未提交,对其他事务也是可见的。事务可以读取未提交的数据,也被称为脏读。
  在实际应用中一般很少使用
• READ COMMITED(提交读)
  一个事务从开始直到提交之前,所做的任何修改对其他事务都是不可见的。
  这个级别也叫做不可重复读(nonrepeatable read),因为两次执行相同的查询,可能会得到不一样的结果
• REPEATABLE READ(可重复读)
  MySQL默认事务隔离级别
  该级别保证了同一个事务中多次读取同样记录的结果是一致的。但是无法解决另一个幻读问题。
  幻读: 当某个事务在读取某个范围内的记录时,另外一个事务又在该范围内插入了新的记录,当之前的事务再次读取该范围的记录时,会产生幻行。
  InnoDB和XtraDB存储引擎通过多版本并发控制(MVCC, Multiversion Concurrency Control)解决了幻读问题
• SERIALIZABLE(可串行化)
  最高隔离级别。强制事务串行执行,避免了幻读问题。
  SERIALIZABLE会在读取每一行数据上都加锁,可能导致大量的超时和锁争用问题。(实际上很少用到)

ANSI SQL隔离级别

隔离级别	脏读	不可重复读	幻读	加锁读
READ UNCOMMITTED	√	√	√	×
READ COMMITTED	×	√	√	×
REPEATABLE READ	×	×	√	×
SERIALIZABLE	×	×	×	√

1.3.2 死锁

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

• InnoDB目前处理死锁的方法: 将持有最少行级排他锁的事务进行回滚

• 死锁产生的双重原因: 有些是因为真正的数据冲突; 有些是由于存储引擎的实现方式导致的

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

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

事务日志持久化之后,内存中被修改的数据在后台可以慢慢地刷回到磁盘。

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

自动提交(AUTOCOMMIT)
MySQL默认采用自动提交模式

ON或者1表示启动

在事务中混合使用存储引擎
MySQL服务器层不管理事务,事务是由下层的存储引擎实现的。

在事务中混合使用了事务型和非事务型的表(如InnoDB和MyISAM表),正常提交的情况下不会有什么问题。
但如果事务需要回滚,非事务型的表上的变更无法撤销。

隐式和显式锁定
InnoDB采用的是两阶段锁定协议(two-phase locking protocol)
隐式锁定: 在事务执行过程中,随时都可以执行锁定,锁只有在执行COMMIT或者ROLLBACK的时候才会释放,并且所有的锁是在同一时刻释放
显式锁定: 通过特定的语句进行显示锁定, SELECT … LOCK IN SHARE MODE

1.4 多版本并发控制

MySQL大多数事务型存储引擎实现的都不是简单的行级锁。它们一般都同时实现了多版本并发控制(MVCC),不同的存储引擎的MVCC实现是不同的

MVCC实现: 通过保存数据在某个时间点的快照来实现的

MVCC只在REPEATABLE READ 和 READ COMMITTED两个隔离级别下工作。其它两个隔离级别不兼容

1.5 MySQL的存储引擎

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

InnoDB概览
InnoDB数据存储在表空间(tablespace)中,表空间是由InnoDB管理的一个黑盒子,由一系列的数据文件组成。

InnoDB采用MVCC来支持高并发,并且实现了四个标准的隔离级别。默认级别是REPEATABLE READ(可重复读),并且通过间隙锁(next-key locking)策略防止幻读。
间隙锁使InnoDB不仅锁定查询所涉及的行,还会对索引中的间隙进行锁定,以防止幻读的插入。

InnoDB表基于聚簇索引建立

InnoDB内部优化了:从磁盘读取数据时采用的可预测性预读,能够在自动在内存中创建hash索引以加速读操作的自适应哈希索引(adaptive hash index),以及能够加速插入操作的插入缓冲区(insert buffer)等。

MyISAM存储引擎
特性: 全文索引、压缩、空间函数(GIS)等,不支持事务和行级锁,崩溃后无法安全恢复。