mysql三层体系

Mysql:是单进程多线程数据库。

MySQL分层：

mysql分三层:网络连接层, sql层, 存储引擎层，而网络连接层与sql层合称server层，故mysql又分server层合储存引擎层。

第一层：处理网络链接，链接的网络认证。当客户端链接到服务器的时候，每个客户端链接都有一个线程，这个链接的查询只会在该线程中执行。

第二层：是SQL的查询解析，分析，和优化，缓存以及所有的内置函数，所有存储引擎的功能都在这一层实现，比如存储过程。MySQL在解析SQL的时候，会在服务器层创建解析树，然后通过查询重写，决定表的读取顺序，以及选择合适的索引等等。虽然优化策略是服务器层决定的，但是统计信息是存储引擎层提供的。

第三层：存储引擎层，存储引擎负责MySQL中数据的存储和提取，存储引擎不会互相通信。

 

第1层网络连接层介绍：网络连接层主要有连接池和线程池。

网络连接层的作用：

 连接与线程处理，比如连接处理、授权认证、安全等。（通信协议，线程，验证）

mysql的连接管理方式：

Mysql-Server同时支持3种连接管理方式，包括No-Threads，One-Thread-Per-Connection和Pool-Threads。
No-Threads：表示处理连接使用主线程处理，不额外创建线程，这种方式主要用于调试；
One-Thread-Per-Connection：是线程池出现以前最常用的方式，为每一个连接创建一个线程服务；
Pool-Threads：则是线程池方式。

为什么有连接池和线程池？

 每创建一个新的会话(或链接),mysql内部创建一个新的用户线程来提供服务,当连接被销毁,线程也被销毁.即一个连接有一个线程.这种创建链接和销毁链接都会消耗cpu性能.为了降低这种消耗,有了连接池和线程池.

连接池（connection pool）：

连接池:在客户端部署。客户端创建预先创建一定的连接，利用这些连接服务于客户端所有的DB请求。如果某一个时刻，空闲的连接数小于DB的请求数，则需要将请求排队，等待空闲连接处理。通过连接池可以复用连接，避免连接的频繁创建和释放，从而减少请求的平均响应时间，并且在请求繁忙时，通过请求排队，可以缓冲应用对DB的冲击。当连接断开,连接将回归连接池。

线程池（thread pool）：

 线程池:在服务器端部署。通过创建一定数量的线程服务DB请求，有了线程池，当有了新连接,可以直接从线程池里拿线程,断开时,也不销毁线程,而是回放进线程池。与一个线程服务一个连接的方式对比，线程池服务的最小单位是语句，即一个线程可以对应多个活跃的连接。通过线程池，可以将server端的服务线程数控制在一定的范围，减少了系统资源的竞争和线程上下切换带来的消耗，同时也避免出现高连接数导致的高并发问题。
说明：在mysql社区版无线程池功能. 在第三方perconadb 和mysql商业版有其功能.

线程池带来的问题1：调度死锁（解决方法是添加优先级队列）

引入线程池解决了多线程高并发的问题，但也带来一个隐患。假设，A，B两个事务被分配到不同的group中执行，A事务已经开始，并且持有锁，但由于A所在的group比较繁忙，导致A执行一条语句后，不能立即获得调度执行；而B事务依赖A事务释放锁资源，虽然B事务可以被调度起来，但由于无法获得锁资源，导致仍然需要等待，这就是所谓的调度死锁。由于一个group会同时处理多个连接，但多个连接不是对等的。比如，有的连接是第一次发送请求；而有的连接对应的事务已经开启，并且持有了部分锁资源。为了减少锁资源争用，后者显然应该比前者优先处理，以达到尽早释放锁资源的目的。因此在group里面，可以添加一个优先级队列，将已经持有锁的连接，或者已经开启的事务的连接发起的请求放入优先队列，工作线程首先从优先队列获取任务执行。

线程池带来的问题2：大查询处理（解决方法是设置thread_pool_oversubscribe）

某个group里面的连接都是大查询，那么group里面的工作线程数很快就会达到thread_pool_oversubscribe参数设置值，对于后续的连接请求，则会响应不及时(没有更多的连接来处理)，这时候group就发生了stall。通过前面分析知道，timer线程会定期检查这种情况，并创建一个新的worker线程来处理请求。如果长查询来源于业务请求，则此时所有group都面临这种问题，此时主机可能会由于负载过大，导致hang住的情况。这种情况线程池本身无能为力，因为源头可能是烂SQL并发，或者SQL没有走对执行计划导致，通过其他方法，比如SQL高低水位限流或者SQL过滤手段可以应急处理。但是，还有另外一种情况，就是dump任务。很多下游依赖于数据库的原始数据，通常通过dump命令将数据拉到下游，而这种dump任务通常都是耗时比较长，所以也可以认为是大查询。如果dump任务集中在一个group内，并导致其他正常业务请求无法立即响应，这个是不能容忍的，因为此时数据库并没有压力，只是因为采用了线程池策略，才导致了请求响应不及时，为了解决这个问题，我们将group中处理dump任务的线程不计入thread_pool_oversubscribe累计值，避免上述问题。

连接池和线程池说明：

连接池主要用来管理客户端的连接,避免重复的连接/断开操作,是将空闲的连接缓存起来,可以复用。从而减少了连接mysql server/断开mysql server的开销与成本,从而提升性能。

但是mysql的连接池不能获取mysql server的查询处理能力以及当前的负载情况。

线程池:线程池的操作是在mysql server端,并且设计就是用来管理当前并发的连接和查询。

thread pool到底能够提升多少性能?

根据Oracle Mysql官方的性能测试：

在并发达到128个连接以后.没有线程池的Mysql性能会迅速降低。使用线程池以后,性能不会出现波动,会一直保持在较好的状态运行。

在读写模式下,128个连接以后,有线程池的Mysql比没有线程池的Mysql性能高出60倍。

在只读模式下,512个连接以后,有线程池的Mysql比没有线程池的Mysql性能高出18倍。

什么时候可以考虑使用thread_pool？

 show global status like '%threads_running%';其值是mysql server当前并发执行语句的数量,如果这个值一直保持在40左右的区间,那么可以考虑使用thread pool。

如果你使用了innodb_thread_concurrency参数来控制并发的事物量,那么使用线程池将会获得更好的效果。

如果你的工作是有很多短连接组成的,那么使用线程池是有益的。

 

第2层sql处理层（SQL Layer）：主要有SQL Interface、Parser、Optimizer、Cache和Buffer

Sql层功能:

功能：解析器,授权,优化器,查询执行,查询高速缓存,查询日志记录，跨存储引擎功能。

1.解析器：解析SQL语法，形成语法树

2.授权:SQL的权限验证  *.*对于指定的库和表

3.优化器:CBO（基于成本的优化）,根据统计信息--> SQL改写 --->执行计划(即选哪种算法执行)

sql层处理数据流程：

用户传入sql-----查询缓存（命中缓存可直接返回结果）----解析器（生成sql解析树）----预处理器（可能sql等价改写）-----查询优化器（生成sql执行计划）----查询执行引擎----结果返回给用户。

 

SQL接口：（SQL Interface）

 功能：接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface

解析器：（Parser）--生成sql解析树

SQL命令传递到解析器的时候会被解析器验证和解析（进行语义和语法的分析，分解成数据结构，如果在分解构成中遇到错误，那么就说明这个sql语句是不合理的 ），生成sql解析树。解析器是由Lex和YACC实现的，是一个很长的脚本。

 

查询优化器：（Optimizer） --生成执行计划

 SQL语句在查询之前会使用查询优化器对查询进行优化，根据客户端请求的 query 语句，和数据库中的一些统计信息，在一系列算法的基础上进行分析，得出一个最优的策略，告诉后面的程序如何取得这个 query 语句的结果，即执行计划。查询优化器使用选取-投影-联接策略生成执行计划。

选取-投影-联接：

用一个例子就可以理解： select uid,name from user where gender = 1;

这个select 查询先根据where 语句进行选取，而不是先将表全部查询出来以后再进行gender过滤。

这个select查询先根据uid和name进行属性投影，而不是将属性全部取出以后再进行过滤。

将这两个查询条件联接起来生成最终查询结果。

 

查询缓存功能（Cache和Buffer）:（建议关闭）

当执行sql的时候,sql第一次被执行,然后再次执行的时候如果相同的sql,可以不进行解析，直接返回结果,提高查询效率.

关闭查询缓存：query_cache_type = 0    query_cache_size = 0

局限性比较大，任何查询结果有变更，都需要进行更新，对于mysql性能影响比较严重,整个更新过程的锁颗粒度的比较高,还持有全局锁，效率很低.

建议:是否使用查询缓存,不用.(在mysql8.0里没了查询缓存功能.)

问题：如何计算和提高查询缓存命中率？

 

第3层储存引擎层（StorEngine Layer）：

储存引擎层功能：

存储引擎，也称为表类型，真正的负责了MySQL中数据的存储和提取，储存引擎层由多种存储引擎共同组成。它们负责存储和获取所有存储在MySQL中的数据。就像Linux众多的文件系统 一样。每个存储引擎都有自己的优点和缺陷。服务器是通过存储引擎API来与它们交互的。存储引擎不能解析SQL，互相之间也不能通信。仅仅是简单的响应服务器 的请求。存储引擎不会互相通信。不同的存储引擎采用不同的技术（存储机制、索引机制、锁定机制）存储数据。
MySQL的存储引擎是插件式的，也就是说，用户可以随时切换MySQL的存储引擎：针对表或针对库都可（通过SQL语句命令）。MySQL集合了多种引擎：MyISAM、InnoDB、BDB、Merge、Memory等，默认的是InnoDB。

储存引擎层说明：

1、根据上层获取数据的方法（执行计划），将数据提取出来。

2、重新再交给SQL层。

3、是MYSQL数据库的核心，关系到数据库性能。

4、存储引擎是基于表的，而不是数据库。

常见的MySQL的存储引擎及特点：

 存储引擎                   特点

InnoDB          持事务安全。但是对比MyISAM引擎，写的处效率会差些

MyISAM          支持事务，插速度般innodb快一些

Memory          数据存储于内存之中

CSV            数据存储为CSV件格式，不进转换

查看mysql储存引擎：

mysql> show plugins;           ---查看插件及其状态
mysql> show engines;          ---查看目前支持的储存引擎

InnoDB和MyISAM区别:

 InnoDB                                                                                MyISAM

索引组织表                              堆表

锁                                  表锁

物理结构不同，数据索引在起(.frm)       物理结构不同，数据索引分开(.MYD .MYI)

支持事务                             不支持事务

支持外键                             不支持外键

MVCC多版本控制                           

INNODB缓存索引和数据                     MyISAM只缓存索引块

说明：

 通过对开关binlog先后的测试发现，其实MySIAM的插性能要好于INNODB，这和MySIAM不支持事务，锁开销也较有关。 但是MySiam的表锁让该引擎不能在并发下工作，因为会造成的锁冲突。在线业务OLTP业务，强烈不建议使MySIAM的存储引擎，并发效率很低。

创建表时指定储存引擎：

create table test1

(

id int,

name varchar(11)

)engine=innodb;

 create table test2

(

id int,

name varchar(11)

)engine=myisam ;

 

InnoDB的物理存储结构：

 test1.frm        #表结构文件

test1.ibd   #表数据文件（存储数据和索引）

MySIAM的物理存储结构：

 test2.frm        #表结构文件

test2.MYD   #表数据文件

test2.MYI   #表索引文件

修改MyISAM表结构到InnoDB表：

 alter table test2 engine = innodb;

show create table test2\G;

Create Table: CREATE TABLE `test2` (

`id` int(11) DEFAULT NULL,

`name` varchar(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

注意： MySQL 的系统表， user 等不能转化为 InnoDB 格式，他们必须采用 MyISAM 格式！！