MySql逻辑架构

一、MySQL逻辑架构

1、MySQL逻辑架构整体分为三层，最上层为客户端层，并非MySQL所独有，诸如：连接处理、授权认证、安全等功能均在这一层处理。

2、MySQL大多数核心服务均在中间这一层，包括查询解析、分析、优化、缓存、内置函数(比如：时间、数学、加密等函数)。所有的跨存储引擎的功能也在这一层实现：存储过程、触发器、视图等。

3、最下层为存储引擎，其负责MySQL中的数据存储和提取。和Linux下的文件系统类似，每种存储引擎都有其优势和劣势。中间的服务层通过API与存储引擎通信，这些API接口屏蔽了不同存储引擎间的差异。不同引擎只会简单的响应上层服务器的请求，而不会相互通信。

二、连接和安全性

1、对于每个客户端连接，服务器都会在进程中新建一个线程处理(如果是线程池的话，则是分配一个空的线程)，这个连接的查询只会在这个单独的线程中执行(每个线程相互独立)，该线程只能轮流在某个CPU核心(多核CPU)或者CPU中运行。服务器会负责缓存线程，因此不需要为每个新建的连接创建或者销毁线程(线程的重用和销毁都由服务器控制)。

2、当客户端连接到MySQL服务器时，服务器需要对其进行认证，如基于用户名、原始主机信息和密码;一旦连接成功，服务器会继续验证客户端是否具有执行某个特定查询的权限。

三、MySQL查询优化与执行

我们总是希望MySQL能够获得更高的查询性能，最好的办法是弄清楚MySQL是如何优化和执行查询的。一旦理解了这一点，就会发现：很多的查询优化工作实际上就是遵循一些原则让MySQL的优化器能够按照预想的合理方式运行而已。

1、客户端/服务端通信协议

MySQL客户端/服务端通信协议是“半双工”的：在任一时刻，要么是服务器向客户端发送数据，要么是客户端向服务器发送数据，这两个动作不能同时发生。一旦一端开始发送消息，另一端要接收完整个消息才能响应它，所以我们无法也无须将一个消息切成小块独立发送，也没有办法进行流量控制。

客户端用一个单独的数据包将查询请求发送给服务器，所以当查询语句很长的时候，需要设置max_allowed_packet参数。但是需要注意的是，如果查询实在是太大，服务端会拒绝接收更多数据并抛出异常（MySQL server has gone away）。

与之相反的是，服务器响应给用户的数据通常会很多，由多个数据包组成。但是当服务器响应客户端请求时，客户端必须完整的接收整个返回结果，而不能简单的只取前面几条结果，然后让服务器停止发送。因而在实际开发中，尽量保持查询简单且只返回必需的数据，减小通信间数据包的大小和数量是一个非常好的习惯，这也是查询中尽量避免使用SELECT *以及加上LIMIT限制的原因之一。

可以使用 SHOW [FULL] PROCESSLIST 命令来查看哪些线程正在运行，及其查询状态，Command列显示了状态。

2、查询缓存

在解析一个查询语句前，如果查询缓存是打开的，那么MySQL会检查这个查询语句是否命中查询缓存中的数据。如果当前查询恰好命中查询缓存，在检查一次用户权限后直接返回缓存中的结果。这种情况下，查询不会被解析，也不会生成执行计划，更不会执行。

MySQL将缓存存放在一个引用表（不要理解成table，可以认为是类似于HashMap的数据结构），通过一个哈希值索引，这个哈希值通过查询本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息计算得来。所以两个查询在任何字符上的不同（例如：空格、注释），都会导致缓存不会命中。

如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、mysql库中的系统表，其查询结果都不会被缓存。比如函数NOW()或者CURRENT_DATE()会因为不同的查询时间，返回不同的查询结果，再比如包含CURRENT_USER或者CONNECION_ID()的查询语句会因为不同的用户而返回不同的结果，将这样的查询结果缓存起来没有任何的意义。

既然是缓存，就会失效，那查询缓存何时失效呢？MySQL的查询缓存系统会跟踪查询中涉及的每个表，如果这些表（数据或结构）发生变化，那么和这张表相关的所有缓存数据都将失效。正因为如此，在任何的写操作时，MySQL必须将对应表的所有缓存都设置为失效。如果查询缓存非常大或者碎片很多，这个操作就可能带来很大的系统消耗，甚至导致系统僵死一会儿。而且查询缓存对系统的额外消耗也不仅仅在写操作，读操作也不例外：

任何的查询语句在开始之前都必须经过检查，即使这条SQL语句永远不会命中缓存；

如果查询结果可以被缓存，那么执行完成后，会将结果存入缓存，也会带来额外的系统消耗。

基于此，我们要知道并不是什么情况下查询缓存都会提高系统性能，缓存和失效都会带来额外消耗，只有当缓存带来的资源节约大于其本身消耗的资源时，才会给系统带来性能提升。但要如何评估打开缓存是否能够带来性能提升是一件非常困难的事情，也不在本文讨论的范畴内。如果系统确实存在一些性能问题，可以尝试打开查询缓存，并在数据库设计上做一些优化，比如：

用多个小表代替一个大表，注意不要过度设计；

批量插入代替循环单条插入；

合理控制缓存空间大小，一般来说其大小设置为几十兆比较合适；

可以通过SQL_CACHE和SQL_NO_CACHE来控制某个查询语句是否需要进行缓存。

最后的忠告是不要轻易打开查询缓存，特别是写密集型应用。如果你实在是忍不住，可以将query_cache_type设置为DEMAND，这时只有加入SQL_CACHE的查询才会走缓存，其他查询则不会，这样可以非常自由地控制哪些查询需要被缓存。

关于SQL_CACHE与SQL_NO_CACHE

       MySql中可以在SQL中指定SQL_CACHE和SQL_NO_CACHE来控制某个查询语句是否需要进行缓存。

       关于 query_cache_type变量

       mysql是根据query_cache_type这个变量来决定要不要把查询结果放到查询缓存中。
       这个变量有三个取值：0,1,2，分别代表了off、on、demand。mysql默认为开启 on。

      例如在my.ini中增加一行 ：query_cache_type=2

      当query_cache_type=0，query cache 是关闭的。

      当query_cache_type=1，那么查询总是先到查询缓存中查找，即使使用了sql_no_cache仍然查询缓存，因为sql_no_cache只是不缓存查询结  果，而不是不使用查询结果。

      当query_cache_type=2，demand。则只有加入SQL_CACHE的查询才会走缓存。

      注：修改变量配置，需要重启mysql服务

当然查询缓存系统本身是非常复杂的，这里讨论的也只是很小的一部分，其他更深入的话题，比如：缓存是如何使用内存的？如何控制内存的碎片化？事务对查询缓存有何影响等等，读者可以自行阅读相关资料，这里权当抛砖引玉吧。

3、语法解析和预处理

MySQL通过关键字将SQL语句进行解析，并生成一颗对应的（内部数据结构）解析树。这个过程解析器主要通过语法规则来验证和解析。比如SQL中是否使用了错误的关键字或者关键字的顺序是否正确等等。预处理则会根据MySQL规则进一步检查解析树是否合法。比如检查要查询的数据表和数据列是否存在等等。

4、查询优化

经过前面的步骤生成的语法树被认为是合法的了，并且由优化器将其转化成查询计划。多数情况下，一条查询可以有很多种执行方式，最后都返回相应的结果。优化器的作用就是找到这其中最好的执行计划。

MySQL使用基于成本的优化器，它尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。在MySQL可以通过查询当前会话的last_query_cost的值来得到其计算当前查询的成本。

  1. mysql> select * from t_message limit 10;  
  2. ...省略结果集  
  3.  
  4. mysql> show status like 'last_query_cost';  
  5. +-----------------+-------------+  
  6. | Variable_name   | Value       |  
  7. +-----------------+-------------+  
  8. | Last_query_cost | 6391.799000 |  
  9. +-----------------+-------------+  

示例中的结果表示优化器认为大概需要做6391个数据页的随机查找才能完成上面的查询。这个结果是根据一些列的统计信息计算得来的，这些统计信息包括：每张表或者索引的页面个数、索引的基数、索引和数据行的长度、索引的分布情况等等。

有非常多的原因会导致MySQL选择错误的执行计划，比如统计信息不准确、不会考虑不受其控制的操作成本（用户自定义函数、存储过程）、MySQL认为的最优跟我们想的不一样（我们希望执行时间尽可能短，但MySQL值选择它认为成本小的，但成本小并不意味着执行时间短）等等。

MySQL的查询优化器是一个非常复杂的部件，它使用了非常多的优化策略来生成一个最优的执行计划：

重新定义表的关联顺序（多张表关联查询时，并不一定按照SQL中指定的顺序进行，但有一些技巧可以指定关联顺序）；

优化MIN()和MAX()函数（找某列的最小值，如果该列有索引，只需要查找B+Tree索引最左端，反之则可以找到最大值，具体原理见下文）；

提前终止查询（比如：使用Limit时，查找到满足数量的结果集后会立即终止查询）；

优化排序（在老版本MySQL会使用两次传输排序，即先读取行指针和需要排序的字段在内存中对其排序，然后再根据排序结果去读取数据行，而新版本采用的是单次传输排序，也就是一次读取所有的数据行，然后根据给定的列排序。对于I/O密集型应用，效率会高很多）。

随着MySQL的不断发展，优化器使用的优化策略也在不断的进化，这里仅仅介绍几个非常常用且容易理解的优化策略，其他的优化策略，大家自行查阅吧。通常，应该让优化器按照自己的方式来优化查询。

可以通过EXPLAIN EXTENDED SELECT ... ... ; SHOW WARNINGS; 查看最终优化后的执行sql。

5、查询执行引擎

在完成解析和优化阶段以后，MySQL会生成对应的执行计划，查询执行引擎根据执行计划给出的指令逐步执行得出结果。整个执行过程的大部分操作均是通过调用存储引擎实现的接口来完成，这些接口被称为handler API。查询过程中的每一张表由一个handler实例表示。实际上，MySQL在查询优化阶段就为每一张表创建了一个handler实例，优化器可以根据这些实例的接口来获取表的相关信息，包括表的所有列名、索引统计信息等。存储引擎接口提供了非常丰富的功能，但其底层仅有几十个接口，这些接口像搭积木一样完成了一次查询的大部分操作。

6、返回结果给客户端

查询执行的最后一个阶段就是将结果返回给客户端。即使查询不到数据，MySQL仍然会返回这个查询的相关信息，比如该查询影响到的行数以及执行时间等等。

如果查询缓存被打开且这个查询可以被缓存，MySQL也会将结果存放到缓存中。

结果集返回客户端是一个增量且逐步返回的过程。有可能MySQL在生成第一条结果时，就开始向客户端逐步返回结果集了。这样服务端就无须存储太多结果而消耗过多内存，也可以让客户端第一时间获得返回结果。需要注意的是，结果集中的每一行都会以一个满足①中所描述的通信协议的数据包发送，再通过TCP协议进行传输，在传输过程中，可能对MySQL的数据包进行缓存然后批量发送。

回头总结一下MySQL整个查询执行过程，总的来说分为5个步骤：

（1）客户端向MySQL服务器发送一条查询请求；

（2）服务器首先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段；

（3）服务器进行SQL解析、预处理、再由优化器生成对应的执行计划；

（4）MySQL根据执行计划，调用存储引擎的API来执行查询；

（5）将结果返回给客户端，同时缓存查询结果。

四、存储引擎

1、并发控制

无论何时，只要有多个查询需要在同一时刻修改数据，都会产生并发控制的问题。在处理并发读或者写的时候，可以通过实现一个由两种类型的锁组成的锁系统来解决问题。这两种类型的锁通常被称为共享锁(读锁)和排它锁(写锁)。

读锁是共享的，多个客户在同一时刻可以同时读取一个资源，互不干扰;而写锁是排他的，也就是说一个写锁会阻塞其他的写锁和读锁，这样才能保证数据安全。

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

但加锁也需要消耗资源，如果花费大量时间和资源来管理所而不是存储数据，那就得不偿失了。所以需要一种锁策略，在锁的开销和数据的安全性之间寻求平衡。

MySQL的每种存储引擎都可以实现自己的锁策略，其中有两种最重要的锁策略：表锁和行锁。

表锁是MySQL中最基本的锁策略，并且是开销最小的策略，他会锁定整张表;在特定场景中表锁可以有良好的性能。另外写锁也比读锁有更高的优先级，一个写锁请求可能会被插到读锁队列的前面。

行锁可以最大程度的支持并发，但同时开销也是最大，在InnoDB中实现的就是行锁(在存储引擎层实现)。

2、多版本并发控制

MySQL的大多数事务性存储引擎实现的都不是简单的行级锁。基于提升并发性能的考虑，他们一般都同时实现了多版本并发控制(MVCC)，他可以认为是行级锁的一种变种，在很多情况下避免了加锁操作，所以开销更低。

MVCC的实现，是通过保存数据在某个时间点的快照来实现的，不管需要执行多长时间，每个事务看到的数据都是一致的。根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。

下面我们通过InnoDB的简化版行为来说明MVCC是如何工作的。

InnoDB的MVCC是通过在每行记录后面保存两个隐藏列来实现：一个保存了行的创建时间，另一个保存行的过期时间(并不是实际时间值，而是系统版本号)。每开始一个新的事务，系统版本号就会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。当在默认可重复读隔离级别下时：

SELECT：InnoDB会根据以下两个条件检查每行记录：

>InnoDB只查找版本早于当前事务版本的数据行，这样可以确保事务读取的行，要么是在事务开始之前已经存在的，要么是事务自身插入或者修改过的。

>行的删除版本要么未定义，要么大于当前事务版本号。这可以确保事务读取到的行，在事务开始之前未被删除。

只有符合上述两个条件的记录，才能返回作为查询结果。

INSERT：InnoDB为新插入的每一行保存当前系统版本号作为行版本号。

DELETE：为删除的每一行保存当前系统版本号作为行删除标识。

UPDATE：InnoDB为插入一行新纪录，保存当前版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识。

保存了这两个额外系统版本号，可以使大多数读操作都可以不用加锁，使得读数据操作很简单，性能很好，并且也能保证只会读取到符合标准的行。不足之处是每行记录都需要额外的存储空间，需要做更多的行检查工作以及一些额外的维护工作。

MVCC只在可重复读和读写提交两个隔离级别下工作。读未提交下总是读取最新的数据行，而不是符合当前事务版本的数据行;而序列化则会对所有读取的行都是加锁，所以这两个隔离级别与MVCC不兼容。

3、InnoDB存储引擎

InnoDB的数据存储在表空间(tablespace)中，表空间是由InnoDB管理的一个黑盒子，由一系列的数据文件组成。在MySQL4.1后，InnoDB可以将每个表的数据和索引存放在单独的文件中。

InnoDB采用MVCC来支持高并发，并且实现了四个标准的隔离界别，默认是可重复读，并且通过间隙锁策略防止幻读的出现。间隙锁使得InnoDB不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定，防止幻影行的插入。

InnoDB表是基于聚簇索引建立的(后面再详细介绍)，对主键查询有很高的性能。不过他的二级索引(非主键索引)中必须包含主键列，所以如果主键列很大的话，其他的所有索引都会很大。

InnoDB内部做了很多优化，包括从磁盘读取时间时采用的可预测性预读，能够自动在内存中创建hash索引以加速读操作的自适应哈希索引，以及能够加速插入操作的插入缓冲区等，这些之后再具体分析其实现。同时作为事务型的存储引擎，InnoDB通过一些机制和工具支持真正的热备份。

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

InnoDB采用的是两阶段锁定协议，即在事务执行过程中，随时都可以执行锁定，锁只有在执行COMMIT或者ROLLBACK的时候才会释放，并且所有的锁是在同一时刻被释放的，InnDB会根据隔离级别在需要的时候自动加锁。

4、MyISAM存储引擎

在MySQL5.1及之前的版本MyISAM是默认的存储引擎，他提供了大量的特性如全文索引、压缩、空间函数等，但他不支持事务和行级锁，而且崩溃后无法安全恢复。对于只读的数据，或者表比较小、可以忍受修复操作的，依然可以继续使用。

>加锁与并发：MyISAM对整张表加锁，读取时会对需要读到的所有表加共享锁，写入时加排它锁。但在表有读取查询的同时，也可以往表中插入新的记录(并发插入)。

>修复：对于MyISAM表，可以手动或者自动执行检查和修复工作，但会造成一些数据丢失，而且修复操作很慢。

>索引特性：对于MyISAM即使是BLOB和TEXT字段也可以基于前500个字符创建索引。他也支持全文索引(基于分词创建的索引)，可以支持复杂的查询。

>延迟更新索引键：在创建表时，如果指定了DELAY_KEY_WRITE，在每次修改执行完成时，不会立刻将修改的数据写入磁盘，而是会写到内存中的键缓冲区，只有在清理缓冲区或者关闭表的时候才会将对应的索引块写入磁盘。这样可以极大提升写入性能，但遇到数据库或服务器崩溃时会造成索引损坏。

如果表创建并导入数据行不会再进行修改操作，这时可以采用MyISAM压缩表(myisampack)。这样可以极大减少磁盘空间占用、减少磁盘I/O，从而提升查询性能。压缩表支持索引，但索引也都是只读。

posted @ 2019-06-03 10:34  tt&yy  阅读(404)  评论(0)  编辑  收藏  举报