Mysql体系架构以及执行过程

一、基本概念

1. MySQL是一个单进程多线程架构的数据库。也就是说MySQL数据库实例在系统中表现形式就是一个进程

2. 与Oracle的区别

（1）Oracle没有offet,limit
（2）oracle建表时，没有auto_increment，所有要想让表的一个字段自增，要自己添加序列。插入时，把序列的值插入进去
（3）Oracle是大型数据库收费而Mysql是中小型数据库免费
（4）Mysql安装完后才152M而Oracle有3G左右，且使用的时候Oracle占用特别大的内存空间和其他机器性能
（5）MySQL使用单进程多线程，而Oracle和PostgreSQL使用多进程	
（6）对事务的提交：MySQL默认是自动提交，而Oracle默认不自动提交，需要用户手动提交，需要在写commit;指令或者点击commit按钮
（7）分页查询: MySQL是直接在SQL语句中写"select... from ...where...limit  x, y",有limit就可以实现分页;而Oracle则是需要用到伪列ROWNUM和嵌套查询
（8）对事务的支持：MySQL在innodb存储引擎的行级锁的情况下才可支持事务，而Oracle则完全支持事务

---

二、体系架构

1. 网络连接层（Client Connectors）

（1）主要负责连接管理、授权认证、安全等等
（2）每个客户端连接都对应着服务器上的一个线程。服务器上维护了一个线程池，避免为每个连接都创建销毁一个线程
（3）当客户端连接到 MySQL 服务器时，服务器对其进行认证。可以通过用户名与密码认证，也可以通过 SSL 证书进行认证。登录认证后，服务器还会验证客户端是否有执行某个查询的操作权限。这一层并不是 MySQL 所特有的技术

2. 服务层（MySQL SERVER）（第二层服务层是 MySQL 的核心，跨存储引擎的功能都在这一层实现：存储过程，触发器，视图等）

（1）连接池（Connection Pool）
	a. 管理、缓冲用户的连接，线程处理等需要缓存的需求
（2）理服务和工具组件（Services & utilities）
	a. 系统管理和控制工具，例如备份恢复、MySQL 复制、集群等
（3）SQL 接口（SQL Interface）	
	a. 接受用户的 SQL 命令，并且返回用户需要查询的结果
（4）查询解析器（Parser）
	a. SQL 命令传递到解析器的时候会被解析器验证和解析（权限、语法结构）
（5）查询优化器（Optimizer）
	a. SQL 语句在查询之前会使用查询优化器对查询进行优化
（6）缓存（Caches）
	a. 如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据

3. 存储引擎层（Pluggable Storage Engines）

（1）概念
	a. 负责 MySQL 中数据的存储与提取
	b. 服务器中的查询执行引擎通过 API 与存储引擎进行通信，通过接口屏蔽了不同存储引擎之间的差异
	c. MySQL 采用插件式的存储引擎。MySQL 为我们提供了许多存储引擎，每种存储引擎有不同的特点。我们可以根据不同的业务特点，选择最适合的存储引擎。如果对于存储引擎的性能不满意，可以通过修改源码来得到自己想要达到的性能
（2）特点
	a. MySQL 采用插件式的存储引擎
	b. 存储引擎是针对于表的而不是针对库的（一个库中不同表可以使用不同的存储引擎），服务器通过 API 与存储引擎进行通信，用来屏蔽不同存储引擎之间的差异
	c. 不管表采用什么样的存储引擎，都会在数据区，产生对应的一个 的一个 frm 文件（表结构定义描述文件）

4. 系统文件层（File System NTFS ext4 SAN NAS）

1）概念

a. 文件系统，数据、日志（redo，undo）、索引、错误日志、查询记录、慢查询等
b. 比如该层将数据库的数据存储在文件系统之上，并完成与存储引擎的交互	

2）物理文件

a. 日志文件	
	I. 错误日志（Error log）
		MySQL 错误日志记录 MySQL 运行过程中较为严重的警告和错误信息，以及 MySQL 每次启动和关闭的详细信息。MySQL 错误日志默认是开启的
		可以通过 MySQL 配置文件中的 log-error=/var/log/mysqld.log 配置，修改错误日志的配置信息
		可以通过 show variables like '%log_err%' 查看错误日志的详细信息
	II. 通用查询日志（General query log）
		记录建立的客户端连接和执行的语句	
		可以通过 SHOW VARIABLES LIKE '%general%' 查看当前的通用日志是否开启
	III. 二进制日志（Binary log） 	
		MySQL 的二进制日志（binary log）是一个二进制文件，主要用于记录修改数据或有可能引起数据变更的 MySQL 语句
		二进制日志（binary log）中记录了对 MySQL 数据库执行更改的所有操作，并且记录了语句发生时间、执行时长、操作数据等其它额外信息，但是它不记录 SELECT、SHOW 等那些不修改数据的 SQL 语句
		二进制日志（binary log）主要用于数据库恢复和主从复制，以及审计（audit）操作
	IV. 慢查询日志（Slow query log）	
		记录所有执行时间超过 long_query_time 秒的查询 SQL 或者没有使用索引的查询 SQL
		默认情况下，MySQL 不开启慢查询日志，long_query_time 的默认值为10，即运行时间超过 10s 的语句是慢查询语句
b. 数据文件 		
	I. 获取硬盘中数据存储的地址: SHOW VARIABLES LIKE 'datadir'
c. db.opt 文件
	I. 该文件记录这个库的默认使用的字符集和校验规，文件存放在所属数据库的目录下 	
d. FRM 文件	
	I. 不论使用什么存储引擎，每一张表都会有一个以表名命名的 .frm 文件，与表相关的元数据（meta）信息都存放在此文件中，包括表结构的定义信息等，文件存放在所属数据库的目录下
e. MYD 文件
	I. MyISAM 存储引擎专用，存放 MyISAM 表的数据（data)。每一张 MyISAM 表都会有一个 .MYD 文件，文件存放在所属数据库的目录下
f. MYI 文件
	I. 也是 MyISAM 存储引擎专用，存放 MyISAM 表的索引相关信息。每一张 MyISAM  表对应一个 .MYI 文件，文件存放在所属数据库的目录下		
g. IBD 文件和 IBDATA 文件
	I. 存放 InnoDB 的数据文件（包括索引）。InnoDB 存储引擎有两种表空间方式：独享表空间和共享表空间
	II. 独享表空间：使用 .ibd 文件来存放数据，且每一张 InnoDB 表对应一个 .ibd 文件，文件存放在所属数据库的目录下
	III. 共享表空间：使用 .ibdata 文件，所有表共同使用一个（或多个，自行配置）.ibdata 文件	
h. ibdata1 文件
	I. 系统表空间（数据文件）undo 段，文件存放在 datadir 目录下
i. ib_logfile0、ib_logfile1 文件	
	I. redlog 文件，文件存放在 datadir 目录下
j. pid 文件
	I.pid 文件是 mysqld 应用程序在 Unix/Linux 环境下的一个进程文件，和许多其他 Unix/Linux 服务端程序一样，它存放着自己的进程 id
k. socket 文件 	
	I. socket 文件也是在 Unix/Linux 环境下才有的，用户在 Unix/Linux 环境下客户端连接可以不通过 TCP/IP 网络而直接使用 Unix Socket 来连接 MySQL

---

三、不同的存储引擎

1. CSV 存储引擎 —— 数据存储以 CSV 文件

（1）特点
	a. 不能定义索引、列定义必须为 NOT NULL、不能设置自增列， 不适用大表或者数据的在线处理
	b. CSV 数据的存储用逗号隔开，可直接编辑 CSV 文件进行数据的编排，数据安全性低。编辑之后，要生效使用 flush table XXX 命令	
（2）应用场景
	a. 数据的快速导出导入
	b. 表格直接转换成 CSV 

2. Archive 存储引擎 —— 压缩协议进行数据的存储，数据存储为 ARZ 文件格式

（1）特点
	a. 只支持 insert 和 select 两种操作
	b. 只允许自增 ID 列建立索引
	c. 行级锁
	d. 不支持事务
	e. 数据占用磁盘少
（2）应用场景
	a. 日志系统
	b. 大量的设备数据采集

3. Memory 存储引擎 —— 数据都是存储在内存中，IO 效率要比其他引擎高很多，服务重启数据会丢失，内存数据表默认只有 16M，一般我们不会使用到 Memory 存储引擎

（1）特点
	a. 支持 hash 索引，B tree 索引，默认 hash（查找复杂度 0(1)）
	b. 字段长度都是固定长度 varchar(32)=char(32)
	c. 不支持大数据存储类型字段如 blog，text
	d. 表级锁
（2）应用场景
	a. 等值查找热度较高数据
	b. 在排序、分组等操作中，当数据量小于16M（默认大小），由查询优化器建立的临时表就是 Memory 类型

4. MyISAM 存储引擎 —— MySQL5.5 版本之前的默认存储引擎，较多的系统表也还是使用这个存储引擎，系统临时表也会用到 MyISAM 存储引擎

（1）特点			  							
	a. select count(*) from table 无需进行数据的扫描
	b. 数据（MYD）和索引（MYI）分开存储
	c. 表级锁
	d. 不支持事务
（2）应用场景
	a. 在排序、分组等操作中，当数量超过一定大小之后，由查询优化器建立的临时表就是 MyISAM 类型
	b. 报表，数据仓库

5. InnoDB 存储引擎 —— MySQL 5.5 及以后版本的默认存储引擎

（1）特点
	a. 事务 ACID
	b. 行级锁
	c. 聚集索引（主键索引）方式进行数据存储
	d. 支持外键关系保证数据完整性		

---

四、MySQL 语句执行过程详解

1. 重要概念

1）MySQL 客户端/服务端通信机制

a. MySQL 客户端与服务端的通信方式是 “ 半双工 ”
	I. 全双工：双向通信，发送同时也可以接收
	II. 半双工：双向通信，同时只能接收或者是发送，无法同时做操作
	III. 单工：只能单一方向传送
b. 一旦一端开始发送消息，另一端要接收完整个消息才能响应它，所以我们无法也无须将一个消息切成小块独立发送，也没有办法进行流量控制
c. 客户端用一个单独的数据包将查询请求发送给服务器，所以当查询语句很长的时候，需要设置 max_allowed_packet 参数
d. 如果查询实在是太大，服务端会拒绝接收更多数据并抛出异常
e. 与之相反的是，服务器响应给用户的数据通常会很多，由多个数据包组成。但是当服务器响应客户端请求时，客户端必须完整的接收整个返回结果，而不能简单的只取前面几条结果，然后让服务器停止发送
f. 因而在实际开发中，尽量保持查询简单且只返回必需的数据，减小通信间数据包的大小和数量是一个非常好的习惯，这也是查询中尽量避免使用 SELECT * 以及加上 LIMIT 限制的原因之一	

2）连接状态（对于一个 MySQL 的连接，或者说一个线程，时刻都有一个状态来标识这个连接正在做什么）

a. Sleep：线程正在等待客户端发送数据
b. Query：连接线程正在执行查询
c. Locked：线程正在等待表锁的释放
d. Sorting result：线程正在对结果进行排序
e. Sending data：向请求端返回数据
ps:对于出现问题的连接可以通过 kill {id} 的方式进行杀掉

3）查询缓存

a. 工作原理
	I .缓存 SELECT 操作的结果集和 SQL 语句
	新的 SELECT 语句，先去查询缓存，判断是否存在可用的记录集，需要注意的是在判断的时候，要求 SQL 语句完全一样（SQL 两端允许存在空格）才会匹配到缓存数据
b. 缓存参数
	I. query_cache_type 		是否开启查询缓存
	II. query_cache_size		    总的缓存池的大小，允许设置 query_cache_size 的值最小为40K，默认1M，推荐设置为64M/128M
	III. query_cache_limit  		指定单个查询能够使用的缓冲区大小，默认设置为1M
c. 缓存执行情况
	I. 通过  show status like 'Qcache%' 来查询
d. 不会缓存的情况
	I. 查询语句中设置了 SQL_NO_CACHE，则不会被缓存
	II. 当查询语句中有一些不确定的数据时，则不会被缓存。如包含函数 NOW() ，CURRENT_DATE() 等类似的函数，或者用户自定义的函数，存储函数，用户变量等都不会被缓存
	III. 当查询的结果大于 query_cache_limit 设置的值时，结果不会被缓存
	IV. 对于 InnoDB 引擎来说，当一个语句在事务中修改了某个表，那么在这个事务提交之前，所有与这个表相关的查询都无法被缓存。因此长时间执行事务，会大大降低缓存命中率
	V. 查询的表是系统表
	VI. 查询语句不涉及到表 	
e. 缺点
	I. 在查询之前必须先检查是否命中缓存，浪费计算资源
	II. 如果这个查询可以被缓存，那么执行完成后，MySQL 发现查询缓存中没有这个查询，则会将结果存入查询缓存，这会带来额外的系统消耗
	III. 针对表进行写入或更新数据时，将对应表的所有缓存都设置失效
	IV. 如果查询缓存很大或者碎片很多时，这个操作可能带来很大的系统消耗
f. 适用场景
	I. 以读为主的业务，数据生成之后就不常改变的业务，比如门户类、新闻类、报表类、论坛类

4）查询优化处理

a. 过程
	I. 解析 SQL 		通过 lex 语法分析，yacc 语法分析将 SQL 语句解析成解析树
	II. 预处理阶段 		根据 MySQL 的语法的规则进一步检查解析树的合法性，如：检查数据的表和列是否存在，解析名字和别名的设置。还会进行权限的验证
	III. 查询优化器	    优化器的主要作用就是找到最优的执行计划
b. 查询优化器如何找到最优执行计划
	I. 使用等价变化规则
		I. 5 = 5 and a > 5 改写成 a > 5
		II. a < b and a = 5 改写成 b > 5 and a = 5
		III. 基于联合索引，调整条件位置等
	II. 优化 count、min、max 等函数		
		I. InnoDB 引擎 min 函数只需找索引最左边
		II. InnoDB 引擎 max 函数只需找索引最右边
		III. MyISAM 引擎 count(*)，不需要计算，直接返回
	III. 覆盖索引扫描
	IV. 子查询优化	 
		select * from (select * from user where id = 1) as t;，会被优化成一级查询
	V. 提前终止查询	
		I. 用了 limit 关键字或者使用不存在的条件，获取到 limit 所需要的数据后，就不再遍历接下来的数据
	VI. IN 的优化	
		MySQL 对于 IN 的查询，会先进性排序，再采用二分查找的方式查找数据
		比如表中的数据是 1,2,3,4,5，where 条件是 id IN(2,1,3)，在进行 IN 操作的时候，会先对 IN 中的数据排序，变成 1,2,3，然后取出一条数据1先和2比较，1<2，则往2的左边查找，进而找到1，接下来就是再获取一条数据重复上面的查找步骤
		其他关系型数据库不会采用二分查找的方式，而是和 or 的方式一样，where id=1 or id=2 or id=3，从表中获取一条数据和 where 条件中的 or 的数据一个一个比对
	PS：MySQL 的查询优化器是基于成本计算的原则，它会尝试各种执行计划，数据抽样的方式进行试验（随机的读取一个 4K 的数据块进行分析）

2. Mysql语句的执行过程

1）连接数据库

a. 客户端发起一条Query请求，服务器端的‘连接管理模块’接收请求
b. 将请求转发到‘连接进/线程模块’
c. 调用‘用户模块’来进行授权检查
d. 通过检查后，‘连接进/线程模块’从‘线程连接池’中取出空闲的被缓存的连接线程和客户端请求对接，如果失败则创建一个新的连接请求

2）处理请求

a. 查询缓存
	I. 通过一个大小写敏感的哈希查找判断查询是否命中查询缓存的数据
	II. 命中查询缓存，用户权限没有问题，MySQL直接从缓存中拿结果返回给客户端
b. 查询优化处理（解析SQL、预处理、优化SQL的执行计划）
	I. 解析和预处理：生成一棵解析树，MySQL按照其语法对解析树进行验证和解析查询。判断语法是否合法
	II. 优化器和执行计划: 将语法树转化为执行计划（子任务），并选择成本尽量小的执行计划,执行计划 MySQL会生成一个指令树，然后通过存储引擎完成这棵树并返回结果	
c. 查询执行引擎
	I. 查询执行引擎则根据执行计划来完成整个查询。在执行计划时，存储引擎通过调用实现的接口来完成	

3）返回结果

a. 如果查询可以被缓存，MySQL将结果存放到查询缓存里
b. 增量的返回执行结果，开始生成第一条结果时，MySQL 就开始往请求方逐步返回数据，这样做的好处是 MySQL 服务器无须保存过多的数据，浪费内存，用户体验好，马上就拿到了数据

---

五、MySQL 执行计划详解

1. id —— 执行计划的 id

（1）select 查询的序列号，标识执行的顺序
（2）id 相同，执行顺序由上至下
（3）id 不同，如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行

2. select_type —— 查询的类型，主要是用于区分普通查询、联合查询、子查询等

（1）SIMPLE：简单的 select 查询，查询中不包含子查询或者 union
（2）PRIMARY：查询中包含子部分，最外层查询则被标记为 primary 	
（3）SUBQUERY：表示在 select 或 where 列表中包含了子查询
（4）MATERIALIZED：表示 where 后面 in 条件的子查询
（5）UNION：表示 union 中的第二个或后面的 select 语句
（6）UNION RESULT：union 的结果

3. table —— 查询涉及到的表

（1）直接显示表名或者表的别名
（2）<unionM,N> 由 ID 为 M，N 查询 union 产生的结果
（3）<subqueryN> 由 ID 为 N 查询产生的结果	

4. type —— 访问类型，SQL 查询优化中一个很重要的指标，结果值从好到坏依次是：system > const > eq_ref > ref > range > index > ALL

（1）system：系统表，少量数据，往往不需要进行磁盘IO
（2）const：常量连接
	a. 命中主键（primary key）或者唯一（unique）索引
	b. 被连接的部分是一个常量（const）值
（3）eq_ref：主键索引（primary key）或者非空唯一索引（unique not null）等值扫描	（对于前表的每一行（row），后表只有一行被扫描）
	a. join 查询
	b. 命中主键（primary key）或者非空唯一（unique not null）索引
	c. 等值连接 
（4）ref：非主键非唯一索引等值扫描（对于前表的每一行（row），后表可能有多于一行的数据被扫描）
	a. ref 扫描，可能出现在 join 里，也可能出现在单表普通索引里，每一次匹配可能有多行数据返回，虽然它比 eq_ref 要慢，但它仍然是一个很快的 join 类型
（5）range：范围扫描
	a. range 扫描就比较好理解了，它是索引上的范围查询，它会在索引上扫码特定范围内的值
	b. between，in，> 都是典型的范围（range）查询
（6）index：索引树扫描
	a. explain count (*) from user : id 是主键，该 count 查询需要通过扫描索引上的全部数据来计数，它仅比全表扫描快一点
（7）ALL：全表扫描（full table scan）
	a. 如果 id 上不建索引，对于前表的每一行（row），后表都要被全表扫描

5. possible_keys —— 查询过程中有可能用到的索引

6. key —— 实际使用的索引，如果为 NULL ，则没有使用索引

7. rows —— 根据表统计信息或者索引选用情况，大致估算出找到所需的记录所需要读取的行数

8. filtered —— 表示返回结果的行数占需读取行数的百分比， filtered 的值越大越好

9. Extra —— 十分重要的额外信息

（1）Using filesort：MySQL 对数据使用一个外部的文件内容进行了排序，而不是按照表内的索引进行排序读取
	a. Extra 为 Using filesort 说明，得到所需结果集，需要对所有记录进行文件排序
	b. 这类 SQL 语句性能极差，需要进行优化
	c. 典型的，在一个没有建立索引的列上进行了 order by，就会触发 filesort，常见的优化方案是，在 order by 的列上添加索引，避免每次查询都全量排序
（2）Using temporary：使用临时表保存中间结果，也就是说 MySQL 在对查询结果排序时使用了临时表，常见于order by 或 group by
	a. Extra 为 Using temporary 说明，需要建立临时表（temporary table）来暂存中间结果
	b. 这类 SQL 语句性能较低，往往也需要进行优化
	c. 典型的 group by 和 order by 同时存在，且作用于不同的字段时，就会建立临时表，以便计算出最终的结果集
	d. 临时表存在两种引擎，一种是 Memory 引擎，一种是 MyISAM 引擎，如果返回的数据在 16M 以内（默认），且没有大字段的情况下，使用 Memory 引擎，否则使用 MyISAM 引擎
（3）Using index：表示 SQL 操作中使用了覆盖索引（Covering Index），避免了访问表的数据行，效率高
	a. Extra 为 Using index 说明，SQL 所需要返回的所有列数据均在一棵索引树上，而无需访问实际的行记录
	b. 这类 SQL 语句往往性能较好
（4）Using index condition：表示 SQL 操作命中了索引，但不是所有的列数据都在索引树上，还需要访问实际的行记录
	a. Extra 为 Using index condition 说明，确实命中了索引，但不是所有的列数据都在索引树上，还需要访问实际的行记录
	b. 这类 SQL 语句性能也较高，但不如 Using index
（5）Using where：表示 SQL 操作使用了 where 过滤条件
（6）Select tables optimized away：基于索引优化 MIN/MAX 操作或者 MyISAM 存储引擎优化 COUNT(*) 操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即可完成优化
（7）Using join buffer (Block Nested Loop)：表示 SQL 操作使用了关联查询或者子查询，且需要进行嵌套循环计算
	a. 这类 SQL 语句性能往往也较低，需要进行优化
	b. 典型的两个关联表 join，关联字段均未建立索引，就会出现这种情况。常见的优化方案是，在关联字段上添加索引，避免每次嵌套循环计算

---

参考网址

注：文章是经过参考其他的文章然后自己整理出来的，有可能是小部分参考，也有可能是大部分参考，但绝对不是直接转载，觉得侵权了我会删，我只是把这个用于自己的笔记，顺便整理下知识的同时，能帮到一部分人。
ps : 有错误的还望各位大佬指正,小弟不胜感激