MySQL 的体系结构
- Connectors：用来支持各种语言和 SQL 的交互，比如 PHP、Python、Java 的 JDBC 等
- Management Serveices & Utilities：系统管理和控制工具，包括备份恢复、MySQL 复制、集群等
- Connection Pool：连接池，管理需要缓冲的资源，包括用户密码、权限、线程等
- SQL Interface：用来接收用户的 SQL 命令，返回用户需要的查询结果
- Parser：用来解析 SQL 语句
- Optimizer：查询优化器
- Cache and Buffer：查询缓存，除了行记录的缓存之外，还有表缓存、key 缓存、权限缓存等
- Pluggable Storage Engines：插件式存储引擎，它提供 API 给服务层使用，跟具体的文件打交道
在上述结构的基础上，我们可以大致将 MySQL 分为三层，分别是连接层、服务层以及存储引擎层。
- 连接层：负责管理客户端与 MySQL 服务器的所有连接，包括验证客户端的身份和权限
- 服务层：实现了 MySQL 的大多数核心服务功能，包括查询解析、分析、优化、缓存以及其他所有的内置函数、所有的跨存储引擎的功能都在这一层实现（比如存储过程、触发器、视图等）。当连接层将 SQL 语句转交给服务层时，服务层会做出进一步的处理，对我们的 SQL 语句进行词法分析和语法分析（比如关键字识别、别名识别、语法检查等），然后就是优化器再根据一定的规则对我们的 SQL 语句进行优化，最后再交给执行器去执行
- 存储引擎层：真正存放数据的地方，再往下就是内存或者磁盘
MySQL 支持不同的存储引擎（包括 MyISAM、InnoDB、Memory 等），负责数据的存储和提取。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始就成为了默认的存储引擎。也就是说，当我们执行 create table 语句创建数据表时，如果没有显式地使用engine = memory来指定什么类型的存储引擎，那么 MySQL 默认为我们使用的就是 InnoDB。
不同的存储引擎，表数据的存取方式各有不同，所支持的功能也不相同，但是它们都共用一个 Server 层。Server 层通过存储引擎 API 来与它们进行交互，这些接口屏蔽了不同存储引擎之间的差异，使得这些差异对于上层的查询而言尽可能地透明。这些 API 包含几十个底层函数，用于执行诸如“开始一个事务”或者“根据主键提取一行记录”等操作。
存储引擎不能解析 SQL，互相之间也不能通信，只是单纯地响应上层服务器的请求罢了。
SQL 语句的执行过程
一、使用连接器与客户端建立连接
执行 SQL 语句操作数据库的前提是要先建立连接，连接器在这里负责与客户端建立连接、获取权限、维持和管理连接。
在完成了经典的 TCP 握手之后，连接器就要开始认证你的身份了，这时候用的就是你输入的用户名和密码：
- 如果用户名或密码不正确，MySQL 会报
Access denied for user的错误，然后客户端程序结束执行 - 如果用户名和密码都认证通过，连接器就会到权限表里去查询你所拥有的权限。之后在这个连接里用到的权限判断逻辑都是基于此时读到的权限。也就是说，即使你用管理员账号对该用户的权限作出了修改，也不会影响到已经存在的连接的权限，只会影响到后续新建的连接
连接完成后，如果你没有后续的操作，那么这个连接就会处于空闲状态，而当客户端如果太长时间没有操作，连接器就会自动将它断开，这个时间由参数 wait_timeout 来控制，默认是 8 小时，我们也可以用 SQL 语句来查看默认时间。
show global variables like 'wait_timeout';
如果在连接被断开之后，客户端再次发送请求，就会收到一个错误提示Lost connection to MySQL server during query，这时候就需要重连再执行请求了。
在数据库中有两种连接：
- 短连接：操作完毕后马上 close 掉，下次查询时再重新建立一个
- 长连接：保持打开，减少服务端创建和释放连接的消耗，后面的程序访问的时候还可以再使用这个连接
由于建立连接的过程通常是比较复杂的，首先要发送请求，然后要去验证账号密码，验证通过后还得去查看你所拥有的权限，因此建议在使用中尽量要减少连接建立的动作，也就是尽量使用长连接。
但是全部使用长连接后，你可能会发现有时候 MySQL 占用的内存涨的特别快，这是因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源只会在连接断开的时候才释放，所以如果长连接累积下来，可能会导致内存占用太大，从而被系统强行杀掉（OOM），从表象上看就是 MySQL 异常重启了。
可以从以下几个方面解决这个问题：
- 定期断开长连接。在使用一段时间后，或者程序判断执行过一个占用内存的大查询后，断开连接，之后要查询时再重连
- 如果使用的是 MySQL 5.7 或者更新的版本，可以在每次执行完一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源，这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态
二、查询缓存，有则直接返回
连接建立完成之后，就可以开始执行 SQL 语句了，这里便会进入第二步：查询缓存。
MySQL 内部自带了一个缓存模块，MySQL 在拿到一个查询请求后，首先会到查询缓存中查看之前是不是执行过这条语句，之前执行过的语句及其结果可能会以 key-value 键值对的形式被直接缓存在内存中（key 是查询的语句，value 是查询的结果）。如果你的查询能够直接在这个缓存中找到 key，那么这个 value 就会被直接返回给客户端；如果语句不在查询缓存中，就会继续后面的执行阶段，执行完成后，执行结果会被存入查询缓存中。
可以看到，如果查询命中了缓存，MySQL 甚至不需要执行后面的复杂操作，就可以直接返回结果，这样效率会很高。
我们可以将参数 query_cache_type 设置成 DEMAND，这样对于默认的 SQL 语句就都不会使用查询缓存，而对于确定要使用查询缓存的语句，可以用 SQL_CACHE 显示来指定。
select SQL_CACHE * from table where id = 1;
但是大多数情况下并不建议这么做，为什么呢？
因为查询缓存往往弊大于利。
首先查询缓存失效得非常频繁，只要有对一个表的更新，那么这个表上所有的查询缓存都会被清空。因此很可能你费劲地把结果存起来，还没来得及使用就被另一个更新给全清空了。对于更新压力大的数据库来说，查询缓存的命中率会非常低，除非你的业务就是有一张静态表，很长时间才会更新一次（比如系统配置表），那这张表上的查询才适合使用查询缓存。
其次，查询缓存要求 SQL 语句必须是一模一样的，中间多一个空格或者字母大小写不同都会被认为是不同的 SQL。所以缓存还是交给 ORM 框架（比如 MyBatis 默认是开启了一级缓存）或者独立的缓存服务（比如 Redis ）来处理更加合适。
注意：在 MySQL 8.0 版本中，查询缓存的整个功能已经被移除了！
三、语法解析和预处理
这一步要做的事情就是 MySQL 的解析器和预处理模块对语句进行基于 SQL 语法的词法、语法分析和语义的解析。
解析器如果没有命中查询缓存，就要开始真正执行 SQL 语句了。首先 MySQL 需要知道你要做什么，因此需要对 SQL 语句做解析。
解析器先会做“词法分析”，把一个完整的 SQL 语句打碎成一个个的单词。假设你输入的是由多个字符串和空格组成的一条 SQL 语句，对于 MySQL 而言，它需要识别出里面的字符串分别是什么、代表什么。MySQL 从你输入的 select 这个关键字中识别出这是一个查询语句，同时它也要把字符串“T”识别成表名 T，把字符串“id”识别成主键 id。
做完上述这些识别分析后，MySQL 就要开始做“语法分析”，根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法（比如单引号有没有闭合等），然后就会根据 SQL 语句生成一个数据结构，这个数据结构我们一般称之为解析树（select_lex）。如果你的语句不对，就会收到You have an error in your SQL syntax的错误提示。
在完成了上述两步的分析识别后，解析环节中的预处理器会检查生成的解析树，解决解析器无法解析的语义，比如它会检查表名或者字段是否存在、检查名字和别名是否有歧义等，预处理过后会得到一个新的解析树。
四、查询优化器确定 SQL 语句的执行方案
经过了解析环节，MySQL 就知道你要做什么了，但在真正开始执行 SQL 前，我们还需要先经过优化器的处理。
一条 SQL 语句可以有很多种执行方式，但它们最终返回的结果都是相同等价的。这些执行方式是怎么得到的？最终又应该选择哪一种去执行？根据什么样的判断标准去选择？
这个就是 MySQL 的查询优化器的模块（Optimizer）需要做的事了。
查询优化器的目的就是根据解析树生成不同的执行计划（Execution Plan），然后选择一种最优的执行计划，MySQL 使用的是基于开销（cost）的优化器，即哪种执行计划开销最小就用哪种。
我们可以使用命令来查看查询的开销。
show status like 'Last_query_cost';
对于每一种数据库来说，优化器的模块都是必不可少的，它们可以通过复杂的算法实现尽可能优化查询效率的目标。
优化器在数据表中存在多个索引的时候，决定使用哪个索引，或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。但优化器也不是万能的，并不是再垃圾的 SQL 语句都能自动优化，也不是每次都能选择到最优的执行计划，所以在编写 SQL 语句的时候还是要注意。
优化器最终会把解析树变成一个执行计划，执行计划是一个数据结构。当然这个执行计划并不一定是最优的执行计划，因为 MySQL 也有可能会覆盖不到所有的执行计划。
那么我们怎么查看 MySQL 的执行计划呢？比如存在多张表关联查询时，先查询哪张表？在执行查询的时候可能会用到哪些索引？实际上又用到了哪些索引？
这里 MySQL 为我们提供了一个查看执行计划的工具，我们可以在 SQL 语句前加上 explain，就能看到执行计划的信息了。
explain select * from table;
在优化器阶段完成后，这个语句的执行方案就确定下来了。
五、执行器执行 SQL 语句
MySQL 通过解析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶段，开始执行语句。
开始执行的时候，首先要判断你对这个表有没有执行查询的权限：
- 如果没有，就会返回没有权限的错误。如果命中了查询缓存，会在查询缓存返回结果的时候，做权限验证。查询也会在优化器之前调用 precheck 验证权限
- 如果有权限，就打开表继续执行。打开表的时候，执行器会根据表的存储引擎定义，去调用这个存储引擎所提供的 API 接口
在数据库的慢查询日志中我们可以看到一个 rows_examined 的字段，表示这个语句在执行过程中扫描了多少行。这个值就是在执行器每次调用存储引擎获取数据行的时候累加的。但在有些场景下，执行器调用一次，在存储引擎内部却扫描了多行，因此存储引擎扫描行数和 rows_examined 并不是完全相同的。
select * from table where age = 20;
执行上面的 SQL 语句，当 age 字段没有索引时，执行器的执行流程是这样的：
- 调用存储引擎 API 接口获取这个数据表的第一行，判断 age 值是不是等于 20，如果不是则跳过，如果是则将这行存在结果集里
- 调用存储引擎 API 接口获取下一行，重复进行相同的逻辑判断，直到取到这个数据表的最后一行
- 执行器将上述遍历得到的符合条件的行组成的结果集返回给客户端
而对于有索引的情况，执行的逻辑也大同小异：
- 第一次调用的是“获取符合条件的第一行”这个 API 接口
- 之后再循环调用这个 API 接口，这些接口都是在存储引擎中事先定义好的
存储引擎
在关系型数据库里，数据是放在数据表中的，我们可以把这个表理解成 Excel 电子表格的形式。所以我们的表在存储数据的同时还要组织数据的存储结构，这个存储结构就是由我们的存储引擎所决定的，所以我们也可以把存储引擎叫做表类型。
在 MySQL 里支持多种存储引擎，它们是可以替换的，所以也叫做插件式的存储引擎。
那为什么要支持这么多的存储引擎呢？一种还不够用吗？
在 MySQL 里，每一张数据表都可以指定它的存储引擎，而不是一个数据库只能使用一个存储引擎。存储引擎的使用是以数据表为单位的，而且即使在创建完表之后也可以修改它的存储引擎。
如何选择存储引擎？
- 如果对数据的一致性要求比较高，并且需要事务支持，可以选择 InnoDB
- 如果数据的查询多更新少，并且对查询性能要求比较高，可以选择 MyISAM
- 如果需要一个用于查询的临时表，可以选择 Memory
- 如果所有的存储引擎都不能满足你的需求，并且你的技术能力足够强，那么你还可以根据官网内部手册（https://dev.mysql.com/doc/internals/en/custom-engine.html）用 C 语言自行开发一个存储引擎
explain
explain 通过模拟优化器执行 SQL 语句，从而知道 MySQL 是如何处理 SQL 语句的，用于分析查询语句或者表结构的性能瓶颈。
explain 的作用
- 表的读取顺序
- 数据读取操作的操作类型
- 哪些索引可以使用
- 哪些索引被实际使用
- 表之间的引用
- 每张表有多少行被优化器查询
explain 得到的信息有 10 列
-
id：选择标识符，查询的序号，包含一组数字，表示查询中执行 select 子句或者操作表的顺序
- id 相同，执行顺序从上往下
- id 不同，id 值越大，优先级越高，越先执行
-
select_type：表示查询的类型，主要用于区别普通查询、联合查询、子查询等复杂查询
- simple，简单的 select 查询，查询中不包含子查询或者 union
- primary，查询中若包含任何复杂的子部分，最外层查询被标记
- subquery，在 select 或 where 列表中包含了子查询
- derived，在 from 列表中包含的子查询被标记为 derived（衍生），MySQL 会递归执行这些子查询，并把结果放到临时表中
- union，如果第二个 select 出现在 union 之后，则被标记为 union，如果 union 包含在 from 子句的子查询中，外层 select 会被标记为 derived
- union result，union 的结果
-
table：输出的行所引用的表
-
type：表示表的连接类型，按照从最佳到最坏类型排序，一般需要保证查询至少能达到 range 级别，最好能达到 ref
- system，表中仅有一行（等于系统表），这是 const 连接类型的一个特例
- const，表示通过索引一次就找到，const 用于比较 primary key 或者 unique 索引。因为只匹配一行数据，所以如果将主键置于 where 列表中，MySQL 能将该查询转换为一个常量
- eq_ref，唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于唯一索引或者主键扫描
- ref，非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，可能会找到多个符合条件的行，属于查找和扫描的混合体
- range，只检索给定范围的行，使用一个索引来选择行。 key 列显示使用了哪个索引，一般是 where 语句中出现了 between、in 等范围查询。这种范围扫描索引比全表扫描要好，因为它开始于索引的某一个点，而结束于另一个点，不用进行全表扫描
- index，index 与 all 的区别在于 index 类型只遍历索引树。通常比 all 更快，因为索引文件比数据文件小很多
- all，遍历全表以找到匹配的行
-
possible_keys：表示查询时可能使用的索引
-
key：表示实际使用的索引，如果没有选择索引则键是 null，查询中如果使用了覆盖索引，则该索引和查询的 select 字段重叠
-
key_len：索引字段的长度，该列计算查询中使用的索引的长度，在不损失精度的情况下，长度越短越好。如果键是 null 则长度也为 null。该字段显示索引字段的最大可能长度，并非是实际使用长度
-
ref：列与索引的比较，显示索引的哪一列被使用了，有可能是一个常数，表示哪些列或者常量被用于查询索引列上的值
-
rows：扫描出的行数（估算的行数），根据表统计信息以及索引选用情况，大致估算出找到记录所需要读取的行数
-
Extra：执行情况的描述和说明，包含不适合在其他列中显示，但又是十分重要的额外信息
- using filesort，说明 MySQL 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL 中无法利用索引完成的排序操作，称为“文件排序”
- using temporary，使用了临时表保存中间结果，MySQL 在查询结果排序时使用了临时表。常见于 order by 排序和 group by 分组查询
- using index，表示相应的 select 操作使用了覆盖索引，避免访问了表的数据行。如果同时出现 using where，表明索引被用来执行索引键值的查找，如果没有同时出现 using where，表明索引用来读取数据而非执行查询动作
- using where，表明使用 where 过滤
- using join buffer，使用了连接缓存
- impossible where，where 子句的值总是 false，不能用来获取任何元组
- select tables optimized away，在没有 group by 子句的情况下，基于索引优化 min、max 操作或者对于 MyISAM 存储引擎进行优化
- count(*)，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化
- distinct，优化 distinct 操作，在找到第一个匹配的元组后即停止找同样值的动作
extended 关键字
仅对 select 语句有效，在 explain 后使用 extended 关键字，可以显示 filtered 列（按表条件过滤的行百分比）。
explain 小结
- explain 不会告诉你关于触发器、存储过程的信息或者用户自定义函数对查询的影响情况
- explain 不会考虑各种 Cache
- explain 不能显示 MySQL 在执行查询时所作的优化工作
- 部分统计信息是估算的，并非精确值
- explain 只能解释 select 操作，其他操作要重写为 select 之后再查看执行计划
207
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
