Python 是怎么和 MySQL 交互的
假设你现在要用 Python 开发一个书籍管理系统，让管理员能够对 MySQL 数据库中的书籍信息进行增删改查，那么你会怎么做呢？其实很简单，使用 web 框架编写一个服务，提供好相应的 API，当请求到来时，根据请求类型和参数拼接 SQL 语句，然后交给 MySQL 执行即可。
整个需求再简单不过了，但是 Python 程序在拼接好 SQL 语句之后，怎么交给 MySQL 执行呢？
Python 要想将 SQL 语句发给 MySQL 执行，那么首先要和 MySQL 建立连接，有了连接之后，才能将各种各样的 SQL 语句交给 MySQL 执行。并且在 MySQL 执行完毕之后，还能拿到执行结果，如果执行出错，也要能拿到 MySQL 抛出的错误。
而负责上述逻辑的，我们称之为驱动，Python 里面的 MySQL 驱动最常用的就是 pymysql，这是一个同步驱动，异步驱动的话则是 asyncmy。当然不同的语言都有相应的 MySQL 驱动，有了驱动，便可以和 MySQL 数据库建立连接，发送 SQL 语句给 MySQL 执行，然后获取执行结果。
当拿到执行结果后，连接可以销毁、也可以保留下来，我们一般会选择后者。因为在生产环境中，肯定不止一个连接访问数据库，那样同时能服务的用户量太少。但连接的建立是比较耗时的，如果每来一个请求就创建一次连接、执行完之后又销毁连接，那么效率会非常低下。
因此在生产中，我们的系统会维护一个连接池，池子里面有很多连接。当需要访问数据库时，就从池子里面取走一个，去和 MySQL 交互。当 MySQL 执行完毕、并拿到执行结果之后，再将连接放到池子里，方便下次使用。另外通过连接池，我们还可以限制同时访问 MySQL 的连接数，以防止 MySQL 压力过大。Python 里面的连接池可以通过 SQLAlchemy 实现，或者你也可以自己封装一个。
驱动和 MySQL 之间建立的连接走的是 TCP，应用程序通过连接去访问 MySQL，那么 MySQL 是不是也要创建连接来提供服务呢？答案是肯定的，每来一个客户端连接，MySQL 作为服务端也要创建一个连接与之交互。因此 MySQL 内部必然也会维护一个连接池，负责处理来自客户端的连接请求。
MySQL 的整体架构
MySQL 在面对一条 SQL 语句，需要做哪些工作呢？比如下面这条语句：
select * from student where age > 16;
这条语句的含义是查询表 student 中 age 大于 16 的数据，那么 MySQL 在执行时内部都做了哪些事情呢？下面就来解析一下。
连接器
首先要连接到 MySQL 数据库，这时候负责接待的就是 MySQL 的连接器，它内部维护了一个连接池，负责与客户端建立连接并进行管理。此外，还要根据用户名，判断客户端的权限。
# -h: ip # -P: 端口 # -u: 用户 # -p: 密码，按下回车之后会自动提示输入，当然也可以在控制台输入，但是不安全 mysql -h$ip -P$port -u$user -p # 比如: mysql -uroot -p123456，回车之后直接进入
连接命令中的 mysql 是客户端工具，和 Python 的 pymysql 驱动是等价的，都是用来跟服务端建立连接。在完成 TCP 三次握手后，连接器就要开始认证身份，这个时候用的就是我们输入的用户名和密码。
- 如果用户名或密码不对，你会收到一个 "Access denied for user" 的错误，然后客户端程序结束执行；
- 如果用户名密码认证通过，连接器会到权限表里面查找该用户拥有的权限。之后这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。这就意味着，一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已存在连接的权限。修改完成后，该用户只有重新建立连接，才会使用新的权限设置。
连接完成后，如果没有后续的动作，这个连接就处于空闲状态，你可以在 show processlist 命令中看到它。
图中显示的就是 show processlist 的结果，其中 Command 列显示为 Sleep 的这一行，表示现在系统里面有一个空闲连接。然后我们开启了两个终端，都使用 root 用户建立连接，所以上面显示有两个 root 用户。
但如果客户端太长时间没动静，连接器就会自动将它断开，这个时间是由参数 wait_timeout 控制的，默认值是 8 小时。如果在连接被断开之后，客户端再次发送请求的话，就会收到一个错误提醒：Lost connection to MySQL server during query。这时候你就需要重新连接，然后再执行请求了。
另外数据库里面有长连接和短连接，长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接；短连接则是指每次执行完很少的几次查询后就断开连接，下次查询再重新建立一个。
这两种策略都有利有弊，首先连接的建立本身比较耗时，因此为了保证效率，应该减少连接建立的动作，也就是使用长连接。但如果全部使用长连接，MySQL 占用的内存就会涨的特别快，这是因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的，而这些资源会在连接断开的时候才释放。
所以长连接累积的过多，可能导致内存占用太大，被系统强行杀掉（OOM），结果看起来就像是 MySQL 异常重启了。而解决这个问题有两种办法：
- 定期断开长连接，使用一段时间、或者执行过一个占用内存的大查询后，就断开连接，之后要查询的时候再重新连；
- 如果是 MySQL 5.7 以及之后的版本，可以在执行完内存占用较大的查询后，通过 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态；
查询缓存
连接建立成功并读到客户端发来的 SQL 语句之后，会先去查询缓存，看看之前是不是执行过这条语句。因为执行过的语句及其结果会以 key-value 的形式，被直接缓存在内存中。key 是查询语句，value 是查询结果。如果你的查询在这个缓存中已存在，那么会直接将对应的 value 返回给客户端。
如果语句不在缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被放入缓存中。如果查询命中缓存，MySQL 不需要执行后面的复杂操作，可以直接返回结果，这个效率会很高。
但是 MySQL 的缓存有一个问题，如果某张表更新了，那么该表的所有缓存都会被清空，所以它只适合更新频率非常低的表。因此 MySQL 8.0 版本直接将查询缓存的整块功能删掉了，从 8.0 开始彻底没有这个功能了，因此缓存这一块就无需太关注了。
分析器
如果没有命中缓存，或者是 MySQL 8.0 以及之后的版本，那么 SQL 语句会交给分析器。因为 SQL 语句本质上就是一堆文本，它要先进行解析，而解析的工作就交给分析器负责。
分析器内部包含词法分析器、语法分析器、预处理器。
1）首先词法分析器会对 SQL 语句进行分词，将整个文本切分成一个个的 token。
2）然后是语法分析器，会基于内部定义好的语法规则，在词法分析的基础上进行语法分析，也就是对 token 进行语法分析，然后生成语法解析树。这一步会进行语法检测，也就是判断客户端发送的 SQL 语句是否符合语法规则。
如果语句不对，就会收到 You have an error in your SQL syntax 的错误提醒，比如下面这个语句的 select 少打了开头的字母 s。
mysql> elect * from student where age > 16; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'elect * from student where age > 16' at line 1
一般语法错误会提示第一个出现错误的位置，所以需要关注的是紧接 "use near" 之后的内容。
另外关于词法分析和语法分析，基本上任何一门语言都会有两步。
3）如果语法正确，那么再交给预处理器，预处理器会进一步检测解析树的合法性。比如检测要查询的表、字段是否存在，别名是否有歧义等等，如果检测通过则生成新的语法解析树，然后交给接下来要说的优化器。
优化器
经过了分析器，MySQL 就知道你要做什么了。不过在开始执行之前，还要先经过优化器的处理。
优化器的作用是对你的 SQL 语句进行优化，比如在表里面有多个索引的时候，决定使用哪个索引。或者语句中有多表关联（join）的时候，决定各个表的连接顺序。比如执行两个表的 join：
select * from t1 join t2 using(id) where t1.c=10 and t2.d=20;
这条语句可以有两种解释：
- 先从表 t1 里面取出 c = 10 的记录，根据 id 值关联到表 t2，再筛选出 t2 里面 d = 20 的记录；
- 先从表 t2 里面取出 d = 20 的记录，根据 id 值关联到表 t1，再筛选出 t1 里面 c = 10 的记录；
这两种执行方案的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择哪一种方案。而优化器阶段完成后，这个语句的执行方案就确定下来了，然后进入执行器阶段。
执行器
MySQL 通过连接器拿到了 SQL 语句，通过分析器知道了你要做什么，通过优化器得出了最佳方案，也就是执行计划，然后就进入了执行器阶段，开始执行语句。
首先MySQL 可以分为 Server 层和存储引擎层两部分。
1）Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，比如内置函数，存储过程、触发器、视图等所有跨存储引擎的功能，都在这一层实现。
2）而存储引擎层负责数据的存储和提取，其架构模式是插件式的，支持 InnoDB, MyISAM, Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。
在执行 create table 建表的时候，如果不指定引擎类型，默认使用的就是 InnoDB。不过我们也可以通过指定存储引擎的类型来选择别的引擎，比如在 create table 语句中通过 engine=memory，来指定使用内存引擎创建表。不同存储引擎的表数据的存储方式不同，支持的功能也不同。
所以从图中不难看出，不同的存储引擎共用一个 Server 层，也就是从连接器到执行器的部分。
那么问题来了， 为什么要有存储引擎这一层呢？很简单，我们的数据既可以放在内存，也可以放在磁盘，如果 SQL 执行的时候，要去哪里找这些数据呢？是从内存里面找，还是从磁盘里面找？如果是从磁盘里面找，要从哪个磁盘文件开始找呢？
所以这个时候就需要存储引擎了，存储引擎其实就是执行 SQL 语句的，它会按照一定的步骤去查询内存数据，更新磁盘数据，查询磁盘数据等等，执行诸如此类的一系列操作。并且针对不同的操作，存储引擎都提供了相应的接口，而调用这些接口的就是 Server 层的执行器。
因此执行器就是不停地调用存储引擎的各种接口去完成优化器生成的执行计划。
但是调用之前会先判断用户是否具有相应的权限，如果没有，就会返回没有权限的错误：ERROR 1142 (42000): SELECT command denied to user 'xxx'@'localhost' for table 't'。另外这个权限，就是在建立连接时，由连接器读到的权限。
可能有人好奇了，为什么权限验证非要留在执行器阶段去做。其实这是必须的，因为 SQL 语句要操作的表不止字面上的那些，比如有个触发器，那么就必须在执行阶段才能确定。
如果有权限，就打开表继续执行。打开表的时候，执行器会根据表的引擎定义，去使用存储引擎提供的接口。定义的时候使用哪种引擎，查询的时候也使用哪种。
select * from student where age > 16;
比如我们这个例子中的表 student，存储引擎显然是 InnoDB，如果 age 字段没有索引，那么执行器的执行流程是这样的：
- 调用 InnoDB 引擎接口获取这个表的第一行，判断 age 值是否大于 16，如果为假则跳过，为真则将这行数据存在结果集中。另外我们这里是 select *，如果是 select name, age，那么只会选择 name 和 age 两个字段的值；
- 调用引擎接口获取下一行，重复相同的判断逻辑，直到取到这个表的最后一行；
- 执行器将上述遍历过程中所有满足条件的行组成的结果集返回给客户端；
至此，这个语句就执行完成了，逻辑还是很好理解的。就是不断地调用存储引擎接口，每调用一次，获取一行数据，如果满足 where 条件，则该行保留，否则跳过。
而对于使用了索引的查询，执行逻辑也差不多，只是稍有不同。该查询第一次调用的是 "取满足条件的第一行" 这个接口，之后循环调用 "取满足条件的下一行" 这个接口，这些接口都是引擎中已经定义好的。
所以对于没使用索引的查询，每调用一次接口，只扫描一行数据；对于使用了索引的查询，每调用一次接口，会扫描多行数据。
小结
现在我们对 MySQL 应该有了一个宏观的认识，说白了数据库本身也是用编程语言写出来的一个软件而已。在启动之后，也是一个进程，执行它内部的各种代码。但是基于 MySQL，我们能更方便地管理文件。
下一篇文章，我们来聊一聊 InnoDB 的架构设计，以及 MySQL 的 Buffer Pool 到底是个什么东西。
本文章深度参考自：
- 极客时间，丁奇《MySQL 实战 45 讲》
