MySQL(一)：整体架构

1、整体概述

  

　　MySQL是由 连接池、管理工具和服务、SQL接口、解析器、优化器、缓存、存储引擎、文件系统 组成。

1.1、Connection Pool - 连接池

　　创建数据库连接是一个耗时的操作，连接池的作用就是将这些连接缓存下来，再次访问数据库时，可以直接用已经建立好的连接，提升服务器性能。

1.2、Management Services & Utilities 管理工具和服务

　　系统管理和控制工具，例如备份恢复、Mysql复制、集群等

1.3、SQL Interface - SQL接口

　　接受用户的SQL命令，并且返回用户需要查询的结果。select/update/delete/insert 都是SQL命令。

1.4、Parser - 解析器

　　SQL命令传递到解析器的时候会被解析器验证和解析。

　　解析器主要功能：将SQL语句分解成数据结构，后续步骤的传递和处理就是基于这个结构的。

1.5、Optimizer - 优化器

　　查询优化器，SQL语句在查询之前会使用查询优化器对查询进行优化。

1.6、Caches & Buffers - 缓存器

　　查询缓存，如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等。

1.7、Storage Engines - 存储引擎

1.8、File System -文件系统

2、体系架构

2.1、连接层

　　当MySQL启动（MySQL服务器就是一个进程），等待客户端连接，每一个客户端连接请求，服务器进程会创建一个线程专门处理与这个客户端的交互。当客户端与该服务器断开之后，不会立即撤销线程，只会把他缓存起来放到连接池中，等待下一个客户端请求连接的时候将其分配给该客户端。每个线程独立，拥有各自的内存处理空间。

   

　　客户端连接到MySQL服务器，MySQL服务器需要对其进行验证，也就是用户名、IP、密码验证，一旦连接成功，还要验证是否具有执行某个特定查询的权限（例如，是否允许客户端对某个数据库某个表的某个操作）。

    

2.2、Server层(SQL处理层)

2.2.1、SQL执行流程

　　Server层的主要功能有：SQL语句的解析、优化，缓存的查询，MySQL内置函数的实现，跨存储引擎功能（引擎需对外提供接口），例如：存储过程、触发器、视图等。

SQL执行流程如下：

　　

       

　　1、若是查询语句（select语句），首先会查询缓存是否已有相应结果，有则返回结果，无则进行下一步（如果不是查询语句，同样调到下一步）

　　2、解析查询，创建一个内部数据结构（解析树），这个解析树主要用来SQL语句的语义与语法解析；

　　3、优化：优化SQL语句，例如重写查询，决定表的读取顺序，以及选择需要的索引等。这一阶段用户是可以查询的，查询服务器优化器是如何进行优化的，便于用户重构查询和修改相关配置，达到最优化。这一阶段还涉及到存储引擎，优化器会询问存储引擎，比如某个操作的开销信息、是否对特定索引有查询优化等。

2.2.2、MySQL8.0不再使用查询缓存

　　从8.0开始，MySQL不再使用查询缓存，那么放弃它的原因是什么呢？

　　MySQL查询缓存是查询结果缓存。它将以SEL开头的查询与哈希表进行比较，如果匹配，则返回上一次查询的结果。进行匹配时，查询必须逐字节匹配，例如 SELECT * FROM e1; 不等于select * from e1;

　　一些不确定的查询结果无法被缓存，任何对表的修改都会导致这些表的所有缓存无效。因此，适用于查询缓存的最理想的方案是只读，特别是需要检查数百万行后仅返回数行的复杂查询。如果你的查询符合这样一个特点，开启查询缓存会提升你的查询性能。

　　·查询缓存的效果取决于缓存的命中率，只有命中缓存的查询效果才能有改善，因此无法预测其性能。

　　·查询缓存的另一个大问题是它受到单个互斥锁的保护。在具有多个内核的服务器上，大量查询会导致大量的互斥锁争用。

　　缓存的启用造成的问题比它解决问题要多的多，弊大于利，所以在8.0直接砍掉此功能。

2.3、存储引擎层

　　MySQL数据库区别于其他数据库的最重要的一个特点就是其 插件式 的表存储引擎。

　　MySQL插件式的存储引擎架构提供了一系列标准的管理和服务支持，这些标准与存储引擎本身无关，可能是每个数据库系统本身都必需的，如SQL分析器和优化器等，而存储引擎是底层物理结构和实际文件读写的实现，每个存储引擎开发者可以按照自己的意愿来进行开发。

　　注意：存储引擎是基于表的，而不是数据库。

　　插件式存储引擎的好处：每个存储引擎都有各自的特点，能够根据具体的应用建立不同存储引擎表。用户可以根据MySQL预定义的存储引擎接口编写自己的存储引擎。

2.3.1、InnoDB存储引擎

　　InnoDB是MySQL的默认事务型引擎，用来处理大量的短期(short-lived)事务，短期事务大部分情况是正常提交的，很少会被回滚。

　　InnoDB的性能和自动崩溃恢复特性，适用于非事务型存储。

2.3.2、MylSAM存储引擎

　　在MySQL 5.1及之前的版本，MyISAM是默认的存储引擎。MyISAM提供了大量的特性，包括全文索引、压缩、空间函数（GIS）等，但MyISAM不支持事务和行级锁且崩溃后无法安全恢复。

　　对于只读的数据，或者表比较小、可以忍受修复（repair）操作，则可以使用MyISAM。

　　MyISAM对整张表加锁，而不是针对行。读取时会对需要读到的所有表加共享锁,写入时则对表加排他锁。

　　MyISAM很容易因为表锁的问题导致典型的的性能问题。

2.3.3、Archive引擎

　　Archive存储引擎只支持INSERT和SELECT操作，在MySQL 5.1之前也不支持索引。

　　Archive引擎会缓存所有的写并利用zlib对插入的行进行压缩，所以比MyISAM表的磁盘I/O更少。但每次SELECT查询都需要执行全表扫描。所以Archive表适合日志和数据采集类应用，这类应用做数据分析时往往需要全表扫描。

　　Archive引擎不是一个事务型的引擎，而是一个针对高速插入和压缩做了优化的简单引擎。

2.3.4、Blackhole引擎

　　Blackhole引擎没有实现任何的存储机制，它会丢弃所有插入的数据，不做任何保存。但服务器会记录Blackhole表的日志，可以用于复制数据到备库，或者只是简单地记录到日志。

　　这种特殊的存储引擎可以在一些特殊的复制架构和日志审核时发挥作用。

2.3.5、CSV引擎

　　CSV引擎可以将普通的CSV文件(逗号分割值的文件）作为MySQL的表来处理，但这种表不支持索引。

　　CSV引擎可以在数据库运行时拷入或者拷出文件。可以将Excel等的数据存储为CSV文件，然后复制到MySQL数据目录下，就能在MySQL 中打开使用。同样，如果将数据写入到一个　　CSV引擎表，其他的外部程序也能立即从表的数据文件中读取CSV格式的数据。

　　CSV引擎可以作为一种数据交换的机制。

2.3.6、Memory 引擎

　　如果需要快速地访问数据，并且这些数据不会被修改，重启以后丢失也没有关系，那么使用Memory表(以前也叫做HEAP表）。

　　Memory表至少比MyISAM 表要快一个数量级，因为每个基于MEMORY存储引擎的表实际对应一个磁盘文件。该文件的文件名与表名相同，类型为frm类型。该文件中只存储表的结构。而其数据文件，都是存储在内存中，这样有利于数据的快速处理，提高整个表的效率，不需要进行磁盘I/O。

　　Memory表的结构在重启以后还会保留，但数据会丢失。

　　Memory表支持 Hash索引，因此查找操作非常快。虽然Memory表的速度非常快，但还是无法取代传统的基于磁盘的表。

　　Memroy表是表级锁，因此并发写入的性能较低。它不支持BLOB或TEXT类型的列，并且每行的长度是固定的，所以即使指定了VARCHAR 列，实际存储时也会转换成CHAR，这可能导致部分内存的浪费。

2.3.7、NDB集群引擎

　　使用MySQL服务器、NDB集群存储引擎，以及分布式的、share-nothing 的、容灾的、高可用的NDB数据库的组合，被称为MySQL集群（(MySQL Cluster)。

2.3.8、InnoDB与MylSAM比较

特性	MyISAM	InnoDB
主外键	不支持	支持
事务	不支持	支持
行表锁	表锁，即使操作一条记录也会锁住整个表 不适合高并发的操作	行锁，操作时只锁某一行，不对其它行有影响 适合高并发的操作
缓存	只缓存索引，不缓存真实数据	不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响
表空间	小	大
关注点	性能	事务
默认安装	Y	Y

2.4、文件系统

　　InnoDB、MyISAM存储引擎把表存储在磁盘上，操作系统通过文件系统来管理磁盘，即，InnoDB、MyISAM存储引擎会把表存储在文件系统上。当读取数据时，这些存储引擎会从文件系统中把数据读出来并返回，当写入数据时，这些存储引擎会把数据写回文件系统。

2.4.1、表在文件系统中的表示

　　数据以记录的形式插入表中，每个表的信息又可分为 表结构定义(DDL) 和 表中数据(DML)。

　　表结构包含表名称、表中的列及各列对应的数据类型，约束条件和索引，字符集和规则各种信息。

　　MySQL数据库用 描述表结构的文件，文件名称格式：表名.frm。

2.4.2、InnoDB存储表数据

　　InnoDB的数据放在一个表空间或者文件空间 (table space 或 file space)，该表空间是一个抽象概念，可以对应文件系统上一个或多个真实文件(不同表空间对应的文件数量可能不同)。每一个表空间可以被划分为诸多页，表数据就存放在某个表空间下的某些页中。

2.1、系统表空间(system tablespace)

　　系统表空间可以对应文件系统上一个或多个实际文件，默认情况下，InnoDB会在数据目录下创建一个名为ibdata1、大小为12M的文件，该文件就是对应的系统表空间在文件系统上的表示。

  

　　ibdata1文件是自扩展文件，即，当文件大小不够用时会自动增加文件大小。

　　用户可以自定义该文件名称和对应文件系统上的文件，只需要在MySQL配置文件中即可。

　　注意：在一个MySQL服务器中，系统表空间只有一份。从MySQL5.5.7到MySQL5.6.6之间的各个版本中，表中的数据都会被默认存储到这个系统表空间。

2.2、独立表空间(file-per-table tablespace)

　　MySQL5.6.6及以后版本中，InnoDB不会默认把各个表的数据存储到系统表空间里，会为每一个表建立一个独立表空间，即，创建了多少个表，就有多少个独立表空间。

　　使用独立表空间来存储表数据，会在该表所属数据库对应的子目录下创建一个表示该独立表空间的文件，文件名为 表名.ibd。

　　如使用独立表空间存储 mall 数据库下的 order 表，则该表所在数据库对应的 mall 目录下会为 order 表创建两个文件：order.frm 和 order.ibd，order.ibd 是用来存储 order 表中的数据和索引。

　　用户可自定义使用系统表空间还是独立表空间，通过参数 innodb_file_per_table 控制。当imodb_file_per table的值为0时，代表使用系统表空间;当innodb_file_per table的值为1时，代表使用独立表空间，该参数只对新建的表起作用。

2.4.3、MyISAM存储表数据

　　MyISAM存储引擎中，数据和索引分开存放，在文件系统中使用不同的文件来存储数据文件和索引文件。MyISAM没有表空间的概念，表数据都存放到对应数据库子目录中。

　　如 mall 数据库下的 order 表，使用MyISAM存储引擎，那么在它所在数据库对应的 mall 目录下会为 order 表创建三个文件: order.frm、order.MYD、order.MYI。

　　MYD 代表的是数据文件，MYI代表的是索引文件。

2.4.4、日志文件

　　日志文件记录MySQL数据库的各种类型活动，常见的日志文件有：错误日志(error log)、慢查询日志(slow query log)、查询日志(query log)、二进制文件(bin log)。

4.1、错误日志

　　错误日志文件对MySQL的启动、运行、关闭过程进行了记录，记录所有的错误信息，也会记录一些警告信息。

　　当MySQL不能正常启动时，可通过查看错误日志文件，排查问题。

　　查看错误日志文件位置：

show variables like 'log_error'

4.2、慢查询日志

　　慢查询日志可用于定位可能存在的问题的SQL语句，进而从SQL语句层面进行优化。默认是关闭的，需手动开启。

　　慢查询日志存放位置：

show VARIABLES like '%slow_query_log_file%';

　　查看慢查询是否开启，ON - 开启；OFF - 关闭。

show variables like '%slow_query_log%';

　　慢查询时间阈值参数 long_query_time，SQL运行时间超过该值则会被记录到慢查询日志中，默认为10，表示10秒。

show variables like '%long_query_time%';

　　若运行的SQL语句没有使用索引，MySQL数据库也可以将这条SQL语句记录到慢查询日志文件，控制参数是：

show variables like '%log_queries_not_using_indexes%';

4.3、查询日志

　　查看当前的通用日志文件：

show variables like '%general%';

4.4、二进制日志

　　二进制日志记录了除数据查询语句select外的所有DDL和DML语句，以事件形式记录，还包含语句所执行的消耗的事件，MySQL的二进制日志是事务安全型的。

二进制日志的作用：

recovery(恢复)	某些数据恢复需要二进制日志
replication(复制)	通过复制和执行二进制日志使一台远程MySQL数据库(一般称slave或standby)与一台MySQL数据库(一般称master或primary)进行实时同步
audit(审计)	通过二进制日志中的信息进行审计，判断是否有对数据库进行注入的攻击

　　log-bin 参数用来控制是否开启二进制日志，默认为关闭，需要手动指定参数来启动。开启这个选项会对MySQL的性能造成影响，根据MySQL官方手册中的测试指明，开启二进制日志会使性能下降1%。

show variables like '%log_bin%';