1.一条SQL语句是如何执行的？

1.1MySQL的分层

1.1.1概述

在MySQL的基础架构中，MySQL被分为Server层和存储引擎层。Server层又包括了连接器、查询缓存、分析器、优化器、执行器这几部分。而存储引擎包括了一些插件式的存储引擎，常用的存储引擎有InnoDB,MyISAM等。现在默认的存储引擎是InnoDB。

Server层完成了MySQL的绝大部分核心功能：如函数，存储过程，触发器等。
存储引擎负责提供一些接口完成对文件系统的存储和读取。

使用不同的存储引擎，表数据的存储方式不同，提供的功能也不同。

InnoDB在底层存储表信息时，使用 “表名.frm”类型的文件存储表的结构信息。使用“表名.ibd”类型的文件存储索引和数据。
MyISAM存储引擎使用“表名.frm”存储表结构。使用“表名.MYD”以及“表名.MYI”分别存储数据和索引。

1.2 SQL执行过程

1.2.1 连接器

当客户端请求建立TCP连接时，MySQL的连接器通过TCP连接获取客户端用户的账号和密码。然后进行进行账号和密码的验证。

如果账号或者密码错误，就会给客户端返回一个错误，客户端程序执行结束。
```
 Access denied for user
```
如果账号密码验证正确，那么此时就会从权限表中查询出此用户所持有的权限。此后在这个连接上的所有权限逻辑判断都依赖于此时查询出的权限。
- 这就表示了即使root用户在此时对这个用户的权限进行了修改，在当前连接也是不会生效的。只有在连接断开再重连时，修改后的权限才能生效。

在建立连接后，连接器会有一个连接池，用于与客户端进行交互，同时也防止连接的频繁创建以及限制连接的数目。一般连接池中的连接都是长连接。同时，连接还会从线程池中获取线程区执行下面的操作。

1)空闲时间

如果客户端连接后没有发送请求，那么此时这个来自就会处于空闲状态。可以通过使用show processist看到连接。如果在Command这一行是Sleep，则表示一个空闲连接。如果连接一段时间客户端不发出任何请求，连接器就会断开这个和客户端的连接，默认空闲时间是8小时。可以通过wait_timeout控制。

这时客户端再向服务端发送请求，服务器就会向客户端返回一个错误：

 Lost connection to MySQL server during query

2)长连接

当一个客户端和服务器建立连接后，客户端有持续的请求，都是用同一个连接，这样的连接被称为长连接。如果客户端在查询了几次之后就断开连接，下次查询在重新连接，这样的连接是短连接。

连接的建立过程是比较复杂的，所以我们要减少连接建立的次数，也就是说我们要尽量使用长连接。

(1)长连接带来的问题

如果一直使用长连接的话，当长连接达到一定数量时，这些长连接就会占用很大一部分内存和资源，最后可能会导致MySQL报出OOM异常重启。

(2)解决方法

①定期断开长连接。在建立长连接一段时间后或者在完成了一个比较大的操作之后，可以断开长连接。如果需要进行查询，在建立长连接。

②如果你使用的是MySQL5.7以及其更新版本，可以使用mysql_reset_connection来初始化连接。这个过程不需要断开连接，也不需要进行验证和权限获取。

③还可以使用TCP连接池，来防止TCP连接的频繁创建和销毁和限制长连接的数量。

1.2.2 查询缓存

在建立连接后，SQL语句的执行到达了第二个阶段：查询缓存。

查询缓存中的数据是以key-value的形式来存储的。这个缓存机制是由一系列小缓存组成的，例如表缓存，记录缓存，权限缓存，key缓存等。

MySQL在收到一个查询请求，以查询的SQL语句作为key，到查询缓存中查找是否有缓存。

注意：SQL语句要一模一样，才能够进行匹配，即使只差一个空格，这个SQL语句也不能命中缓存。
如果命中，那么value就是查询的结果，此时直接返回。

在MySQL8当中已经将查询缓存移除了。

1) 不建议使用查询缓存，这是为什么？

因为只要一个表中的一条数据被修改，那么所有有关这张表的缓存都会失效。因此，可能你费尽心思的把结果存储起来，但是还没有使用过，就失效了。查询缓存的命中率比较低。

除非你的业务中拥有一张静态表，修改这张静态表的频率十分的低，这时查询缓存命中的概率就比较高了。

2)按需使用查询缓存

MySQL提供了按需使用查询缓存的方式。你可以将参数query_cache_type设置成DEMAND。这样对于默认的SQL语句就不会使用查询缓存了。

如果想要使用查询缓存，可以使用SQL_CACHE显示指定：

 mysql> select SQL_CACHE * from T where ID=10；

1.2.3 分析器

①如果在查询缓存中没有找到SQL语句对应的缓存，那么在分析器就会对SQL语句进行词义分析。

例如：识别“select”，知道这是一条查询语句，识别表名，识别表中的列名。

②进行了词义分析后，根据MySQL的语法进行语义分析，看是否符合MySQL的语法。

如果不满足MySQL语法，就会向客户端报错：

 You have an error in your SQL syntax

③如果满足MySQL的语法，就会生成一颗语法树。

1.2.4 优化器

在经过了分析器的解析后，MySQL就知道这条SQL语句要做什么了。而优化器的作用就是为这条SQL语句选择一个最合适的执行计划。

例如，索引的选择，表连接的顺序等。
不同的执行计划得到的结果是相同的，但是执行效率却并不相同。

在优化器执行完毕后，这个SQL语句的执行方案就确定下来了。

1.2.5 执行器

通过分析器知道了这条SQL要做什么，在优化器知道了这条SQL语句要怎么做。在执行器，就是要执行SQL语句了。

①首先，在执行SQL语句前，需要检查用户有没有进行此操作的权限，如果没有，就会报错：

 ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'T'

②如果有权限，那么执行引器就会调用存储引擎提供的接口来查询结果。

③执行过程：

首先，找到这张表里的第一行数据，看是否满足条件，如果满足，把这条记录加入到结果集中。如果不满足，获取下一条记录。
重复上面的步骤知道最后一条记录。
将结果集返回给用户。

对于有索引的表，执行的逻辑也差不多。第一次调用的是“取满足条件的第一行”，然后是“满足条件的最后一行”，直到表结束。

你会在数据库的慢查询日志中看到一个rows_examined的字段，表示这个语句执行过程中扫描了多少行。这个值就是在执行器每次调用引擎获取数据行的时候累加的。

读MySQL45讲第一讲总结