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 格式！！
1747
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
