1.高性能MySQL --- MySQL 架构_mysql性能分析框架-CSDN博客

1.MySQL 的架构与历史
	1.MySQL 的逻辑架构
		最上层：不是 MySQL 独有，大多数基于网络的客户端/服务器工具或者服务都具有类似的架构。比如连接处理，授权认证，安全等
		第二层：大多数 MySQL 的核心功能都在这一层，包括查询解析，分析，优化，缓存以及所有的内置函数(如日期，时间，数学和加密
		       函数)，所有跨存储引擎的功能都在这一层实现：存储过程，触发器，视图等。
		第三层：包含了存储引擎。存储引擎负责 MySQL 中数据的存储和提取。和 GNU/Linux 下的各种文件系统一样，每个存储引擎都有它
		 	   的优势和劣势。服务器通过 API 与存储引擎进行通信。这些接口屏蔽了不同存储引擎之间的差异，使得这些差异对上层的查询
		 	   过程透明。存储引起API包含几十个底层函数，用于执行诸如 "开始一个事务"或者 "根据主键提取一行记录"等操作。但存储引擎
		 	   不会去解析SQL，不同存储引擎之间也不会互相通信，而只是简单的响应上层服务的请求。

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

	1.1.2 优化与执行
		MySQL 会解析查询，并创建内部数据结构(解析树)，然后对其进行各种优化，包括重写查询，决定表的读取顺序，以及选择合适的索引等。用户可以通过
	  关键字提示(hint)优化器，影响它的决策过程。也可以请求优化器解释(explain)优化过程的各个因素，使用户可以知道服务器是如何进行优化决策的，并
	  提供一个参考基准，便于用户重构查询和scheme，修改相关配置，使应用尽可能高效运行。
	    优化器并不关心表使用的是什么存储引擎，但存储引擎对于优化查询是有影响的。优化器会请求存储引擎提供容量或某个具体操作的开销信息，以及表数据
	  的统计信息等。例如，某些存储引擎的某种索引，可能对一些特定的查询有优化。
	    对于 select 查询，在解析查询之前，服务器会先检查查询缓存(query cache)，如果能够在其中找到对应的查询，服务器就不必再执行查询解析，优化，
	  和执行整个过程，而是直接返回查询缓存中的结果集。

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

	1.2.1 读写锁
		共享锁(读锁) : 互不阻塞的, 多个客户在同一时刻可以同时读取同一资源
		排它锁(写锁) : 排他的，也就是说一个写锁会阻塞其他的写锁和读锁

	1.2.2 锁粒度
		一种提高共享资源并发的方式是让锁定对象更具有选择性。尽量只锁定需要修改的部分数据，而不是所有的资源。更理性的方式是，只对会修改的数据片进行精确的
	  锁定。任何时候，在给定的资源上，锁定的数量越少，则系统的并发程序越高。
	    加锁也是需要耗费资源的。锁的各种操作，包括获得锁，检查锁是否已经解除，释放锁等，都会增加系统的开销。
	    所谓的锁策略，就是在锁的开销和数据的安全性之间寻求平衡。

	    表锁： 
	    	表锁是 MySQL 中最基本的锁策略，并且是开销最小的策略。它会锁定整个表。一个用户对表进行写操作(插入，删除，更新等)前，需要先获得写锁，这会阻塞
	    其他用户对该表的所有读写操作。只有没有写锁时，其他读取的用户才能获得读锁，读锁之间是不互相阻塞的。
	    	在特定的场景中，表锁也可能有良好的性能。例如，READ LOCAL 表锁支持某些类型的并发写操作。另外，写锁也比读锁具有更高的优先级，因此一个写锁请求
	    可能会被插入读锁队列的前面。
	    	尽管存储引擎可以管理自己的锁，MySQL 本身还是会使用各种有效的表锁来实现不同的目的。例如，服务器会为诸如 ALTER TABLE 之类的语句使用表锁，而忽略
	    存储引擎的锁机制。

	    行级锁：
	    	行级锁可以最大程度的支持并发处理(同时也带来最大的锁开销)。众所周知，在InnoDB 和 XtraDB，以及其他一些存储引擎中实现了行级锁。行级锁只在存储引擎
	    层实现，而 MySQL 服务器层没有实现。服务器层完全不了解存储引擎中锁的实现。

	1.3 事务
		事务就是一组原子性的 SQL 查询，或者说一个独立的工作单元。
		事务的 ACID :
			原子性(atomicity)：一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚。

			一致性(consistency)：数据库总是从一个一致性的状态转到另外一个一致性的状态。

			隔离性(isolation)：通常来说，一个事务所做的修改在最终提交之前，对其他事务是不可见的。

			持久性(durability)：一旦事务提交，则其所做的修改就会永久保存到数据库中。

	1.3.1 隔离级别
		在 SQL 标准中定义了4种隔离级别，每一种级别都规定了一个事务中所做的修改，哪些在事务内和事务间是可见的，哪些是不可见的。较低级别
	  的隔离通常可以执行更高的并发，系统的开销也更低。

	  READ UNCOMMITTED(未提交读)：
	  		事务当中的修改，即使没有提交，对其他事务也都是可见的。事务可以读取未提交的数据，这也被称为脏读(dirty read)。这个级别会导致很多问题，从性能上来说，
	  	  read uncommited 不会比其他级别好太多，但却缺乏其他级别的很多好处，实际很少用。

	  READ COMMITTED(提交读):
	  		大多数数据库系统默认的级别都是 read commited(但MySQL 不是)。read commited 满足前面提到的隔离性的简单要求：一个事务开始时，只能看见已经提交的事务
	  	  所做的修改。换句话说，一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。这个级别有时候也叫做不可重复读。因为2次执行同样的查询，可能会得到
	  	  不一样的结果。

	  REPEATABLE READ(可重复读):
	  		repeatable read 解决了脏读问题。该级别保证了在同一个事务中多次读取同样记录的结果是一致的。但是理论上，可重复读级别还是无法解决另外一个幻读的问题。所谓
	  	  幻读，指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行。InnoDB 和 XtraDB 存储
	  	  引擎通过多版本并发控制(MVCC)解决了幻读的问题。
	  	    可重复读是 MySQL 的默认事务级别。

	  SERIALIZABLE(可串行化):
	  		serealizble 是最高的隔离级别。它通过强制事务串行执行，避免了前面说的幻读问题。简单的说，serializable 会在读取的每一行数据上都加锁，所以可能导致大量的超时
	  	  和锁争用问题。实际也很少用到这种级别。

	1.3.2 死锁
		死锁是指2个或多个事务在同一资源上互相占用，并请求锁定对方占用的资源，而导致恶性循环现象。当多个事务视图以不同的顺序锁定资源时，就可能会产生死锁。多个事务同时锁定同一个
	  资源时，也会产生死锁。
	    死锁检测和死锁超时机制。InnoDB 目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚。
	    锁的行为和顺序是和存储引擎相关的。以同样的顺序执行，有些存储引擎会产生死锁，有些则不会。

	1.3.3 事务日志
		事务日志可以帮助提高事务的效率。使用事务日志，存储引擎在修改表的数据时，只需要修改其内存拷贝，再把该修改行为记录到持久的硬盘上的事务日志中，而不用每次都将修改的数据本身
	  持久化到磁盘上。事务日志采用追加的方式，因此写日志的操作是磁盘上一小块区域内的顺序IO，而不像随机IO需要在磁盘的多个地方移动磁头，所以采用事务日志的方式相对来说要快很多。事务
	  日志持久后，内存中被修改的数据可在后台慢慢刷回磁盘。目前大多数存储引擎都是这样实现的，我们称为预写式日志，修改数据需要写2次磁盘。

	1.3.4 MySQL 中的事务
		MySQL 中提供了2种事务型的存储引擎： InnoDB 和 NDB Cluster。
		MySQL 默认采用自动提交(autocommit)的模式。也就是说，如果不是现实的开始一个事务，则每个查询都被当作一个事务执行提交的。
		show variables like 'autocommit';
		当 autocommit = 0时，所有的查询都是在一个事务中，直到显示的执行 commit 或者 rollback 回滚，该事务结束。修改 autocommit 对于非事务型的表，比如 MyISAM 或者内存表，
	  不会有任何影响。对于这类表，没有 commit 或者 rollback 的概念，也可以说相当于一直处于 autocommit 启用的模式。
	    另外还有些命令，会在执行之前强制执行 commit 提交当前的活动事务。典型的例子，在数据定义语言(DDL)中，如果会导致大量数据修改的操作，比如 alter table 就是如此。另外还有
	  lock tables 等其他语句也会导致同样的结果。
	    set session transaction isolation level read committed; // 设置事务级别

	    在事务中混合使用存储引擎：
	    	MySQL 服务器层不管理事务，事务是由下层存储引擎层实现的。所以在同一个事务中，使用多种存储引擎是不可靠的。
	    	如果在事务中混合使用了事务型和非事务型的表，正常提交是不会有什么问题的。但是如果该事务回滚，非事务型的表上的变更是无法撤销的。

	    隐式锁定和显式锁定：
	    	InnoDB 采用两阶段锁定协议。在事务执行过程中，随时都可以执行锁定，锁只有在执行 commit 或者 rollback 的时候才会释放，并且所有的锁是在同一时刻被释放的。前面描述的都是
	      隐式锁定，InnoDB 会根据隔离级别在需要的时候自动加锁。另外，InnoDB 也支持通过特定的语句进行显式加锁。
	        select ... lock in share mode
	        select ...  for update
	        MySQL 也支持 lock tables 和 unlock tables 语句，这是在服务器层实现的，和存储引擎无关。它们有自己的用途，并不能替代事务处理。如果应用需要用到事务，还是应该选择事务型
	      存储引擎。
	        经常发现，应用已经将表从 MyISAM 转换到 InnoDB ，但还是显式的使用 lock tables 语句。这不但没有必要，还会严重影响性能，实际上 InnoDB 的行级锁工作的非常好。


	1.4 多版本并发控制
		MySQL 的大多数事务性存储引擎都不是简单的行级锁。基于提升并发性能的考虑，它们一般都同时实现了多版本并发控制(MVCC).
		MVCC 可以认为是行级锁的一个变种，但是它在大多数情况下避免了加锁操作，因此开销更低。虽然实现机制有所不同，但大都实现了非阻塞的读操作，写操作也只锁定必要的行。
		MVCC 的实现，是通过保存数据在某个时间点的快照来实现的。也就是说，不管需要执行多长时间，每个事物看到的数据都是一致的。根据事物开始的时间不同，每个事物对同一张
	  表，同一时刻看到的数据可能不一样。
	    前面说到不同的存储引擎的 MVCC 的实现是不同的，典型的有乐观的并发控制和悲观的并发控制。
	    InnoDB 的 MVCC ，是通过在每行记录后面保存两个隐藏的列来实现的。这2个列，一个是保存了行的创建时间，一个保存了行的过期时间。当然存储的并不是实际的时间，而是系统版本号。
	  每开始一个新的事务，系统版本号就会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。下面看下 repeatable read 隔离级别下，MVCC
	  具体是如何操作的：
	  	select : 
	  		InnoDB 会根据如下2个条件检查记录：
	  			a) InnoDB 只查找版本早于当前事务版本的数据行(也就是，行的版本号小于或者等于事务的系统版本号)，这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入
	  			   或者修改过的。
	  			b) 行的删除版本要么未定义，要么大于当前事务版本号。这样可以确保事务读取到的行，在事务开始之前已经被删除了。
	  		只有符号以上2个条件的记录，才能返回作为查询结果。
	  	insert :
	  		InnoDB 为新插入的每一行确保当前系统版本号作为行版本号
	  	delete :
	  		InnoDB 为删除的每一行保存当前系统版本号作为删除标志
	  	update :
	  		InnoDB 为插入的一行新记录，保存当前系统版本号作为行版本号，同时保持系统当前版本号到原来的行作为行删除标识。

	  	保存这2个额外的系统版本号，使大多数读操作都不用加锁。这样设计是的读数据操作很简单，性能很好，并且也能保证大多数读取到的符合标准的行。不足之处是每行记录都需要额外的存储空间，需要
	  做更多的行检查工作，以及一些额外的维护工作。
	  	MVCC 只在 rrepeatable read 和 read commited 两个隔离级别下工作。其他2个级别都和 MVCC 不兼容，因为 read uncommitted 总是读取最新的数据行，而不是符合当前事务版本的数据行。
	  而 serializable 则会对所有读取的行都加锁。


	1.5 MySQL 的存储引擎
		在文件系统中，MySQL 将每个数据库(也可以成为 schema) 保存为数据目录下的一个子目录。创建表时，MySQL 会在数据库子目录下创建一个和表同名的 .frm 文件保存表的定义。例如，创建一个
	  MyTable 的表，MySQL 会在MyTable.frm 文件中保存该表的定义。因为 MySQL 使用文件系统的目录和文件来保存数据库和表的定义，大小写敏感性和具体的平台密切相关。不同的存储引擎保存数据和索引
	  的方式是不同的，但表的定义则是在 MySQL 服务层统一处理的。
	  show table status like 'db'; // 查看表的状态

	  1.5.1 InnoDB 存储引擎
	  	InnoDB 存储引擎是 MySQL 默认的事务型引擎，也是最重要的，使用最广泛的，它被设计用来处理大陆的短期事务，短期事务大部分情况下是正常提交的，很少会被回滚。
	  	InnoDB 是数据存储在表空间中，表空间是由 InnoDB 管理的一个黑盒子，由一系列的数据文件组成。在 MySQL 4.1 以后的版本中，InnoDB 可以将每个表的数据和索引
	  存放在单独的文件中。InnoDB 也可以使用裸设备作为表空间的存储介质，但现代的文件系统使得裸设备不再是必要的选择。
	    InnoDB 采用 MVCC 来支持高并发，并实现4个标准的隔离级别。其默认的是 repeatable read，并且通过间隙锁策略防止出现幻读。间隙锁使得 InnoDB 不仅仅锁定查询
	  涉及的行，还会对索引中的间隙进行锁定，以防止幻影行的插入。
	    InnoDB 表是基于聚簇索引建立的。聚簇索引对主键查询有着很高的性能。不过它的二级索引(非主键索引)中必须包含主键索引，所以如果主键列很大的话，其他的索引都会很大。因此
	  若表上的索引较多的话，主键应该尽可能的小。 InnoDB 的存储格式是平台独立的，也就是说将数据和索引和索引文件从Intel平台复制到其他平台。
	    InnoDB 内部做了很多优化，包括从磁盘读取数据时采用的可预测性读，能够自动在内存中创建 hash 索引以加速读取操作的自适应哈希索引，以及能够加速插入操作的插入缓冲区等。

	  1.5.2 MyISAM 存储引擎
	  	MyISAM 提供了大量特性，包括全文索引，压缩，空间函数(GIS)等。但 MyISAM 不支持事务和行级锁，而且有一个毫无疑问的缺陷是崩溃后无法恢复。
	  	
	  	存储：
	  		MyISAM 会将表存储在两个文件中：数据文件和索引文件，分别以 .MYD 和 。MYI 为扩展名。MyISAM 表可以包含动态或者静态行。
	  	
	  	MyISAM 特性：
	  		加锁与并发 ： 对整张表进行加锁 
	  		修复 ： check table 表名; repair table 表名;
	  		索引特性
	  		延迟更新索引键
	  	
	  	MyISAM 压缩表：
	  		如果表在创建并导入数据以后，不会再进行任何修改操作，那么这样的表适合采用 MyISAM 压缩表。
	  		可以使用 myisampack 对 MyISAM 表进行压缩。压缩表是不能进行修改的。压缩表可以极大的减少磁盘空间的占用，因此也可以减少磁盘IO。

	 1.5.3 MySQL 内建的其他存储引擎
	 	archive 引擎
	 	blackhole 引擎
	 	csv 引擎
	 	federated 引擎
	 	memory 引擎
	 	merge 引擎
	 	NDB 集群引擎

	 1.5.4 第三方存储引擎
	 	OLTP 类引擎 : XtraDB
	 	面向列的存储引擎
	 	社区存储引擎
1.MySQL 逻辑架构
这里写图片描述