MySQL命令执行过程和存储引擎概述

上集回顾

上集我们说了视图和存储程序，它们都可以方便我们复用某些语句。其中，视图是一种虚拟表，本质上是另一个查询语句的别名；存储程序是许多语句的一个封装，根据调用方式的不同又被分为存储例程、触发器和事件。

迄今为止，我们所唠叨的东西都是如何使用MySQL来对数据进行增删改查的操作，如果把MySQL当作类似OFFICE一类的软件，其实如果我们掌握了前边唠叨的东西，那我们已经是个不错的软件使用者了！但是我们是程序员，大兄弟，我们不仅仅要知道怎么使用一个软件，也要理解这个软件背后的原理从而更好的使用这个软件，这个不仅体现在出了问题能够及时定位并修复，更牛逼的地方是如果有机会的话，我们能够借鉴MySQL中的实现原理到我们自己的程序中！所以从本集开始，将更深入的介绍一下MySQL的各种原理以及一些更高级的使用方法。

MySQL命令执行过程

数据库实例

我们回过头来再仔细想想使用MySQL的完整过程：

1. 启动MySQL服务器程序。
2. 启动MySQL客户端程序并连接到服务器程序。
3. 在客户端程序中输入一些命令语句发送到服务器程序，服务器程序收到这些请求后，会根据请求的内容来操作具体的数据。

也就是说，MySQL服务器程序才是真实数据的管理者，它负责解析各个客户端发来的各种请求并返回相应的执行结果！

我们知道计算机很牛逼，在一台计算机上可以同时运行多个程序，比如微信、QQ、音乐播放器、文本编辑器啥的，每一个运行着的程序也被称为一个进程。我们的MySQL服务器程序本质上就是计算机上的一个进程，这个代表着MySQL服务器程序的进程也被称为MySQL数据库实例，简称数据库实例。

每个进程都有一个唯一的编号，称为进程号，英文名叫PID，这个编号是在我们启动程序的时候由操作系统随机分配的。比如你打开了计算机中的QQ程序，那么操作系统会为它分配一个唯一的进程号，如果你把这个程序关掉了，那操作系统就会把这个进程号回收后重新分配给别的进程，下一次再启动QQ程序的时候分配的可能是另一个编号。每个进程都有一个名称，这个名称是编写程序的人自己定义的，比如我们启动的MySQL服务器进程的名称为mysqld，而MySQL客户端进程的名称为mysql。

小贴士：

由于我们是在唠叨`MySQL`，更多关于进程相关的信息可以参考操作系统相关的书籍。

什么？操作系统的相关书籍写的都贼恶心，看不懂？没关系，等我～

客户端与服务器连接的过程

我们知道每启动一个客户端程序也是在计算机中启动一个进程，客户端程序向服务器程序发送请求并得到回复的过程本质上是一个进程间通信的过程！MySQL支持下边三种客户端程序和服务器程序的通信方式。

TCP/IP

在网络环境下，每台计算机都有一个唯一的IP地址，如果某个进程有进行网络通信方面的需求，可以向操作系统申请一个端口号，这是一个整数值，它的取值范围是0~65535。这样在网络中的其他进程就可以通过IP地址 + 端口号来确定这个进程，这样进程之间就可以通过网络进行通信了。

小贴士：

`TCP/IP`网络体系结构是我们现在通用的一种网络体系结构，其中的`TCP`和`IP`是体系结构中两个非常重要的网络协议，如果你并不知道协议是什么，或者并不知道网络是什么，那恐怕兄弟你来错地方了，找本计算机网络的书去瞅瞅吧！

什么？计算机网络的书写的都贼恶心，看不懂？没关系，等我～

如果我们不手动的指定端口号的话，默认使用3306作为数据库实例的端口号。如果3306端口号已经被别的进程占用了或者我们单纯的想自定义该数据库实例的端口号，那我们可以在启动服务器的命令行里添加--port 3307来明确指定一下端口号，比如这样：

mysql.server start --port 3307

如果客户端程序使用TCP/IP网络来连接到服务器程序的话，我们必须使用IP地址来作为登陆命令中的主机名，如果客户端程序和服务器程序在一台计算机中的话，我们可以使用127.0.0.1来代表本机的IP地址。我们可以使用-P（大写的P）在启动客户端的命令中指定连接服务器程序的端口号，就像这样：

mysql -h127.0.0.1 -uroot -P3307 -p

多说一句，一般的工作环境中，MySQL服务器程序通常会被运行到一个独立的机器中，所以其他客户端程序只能通过TCP/IP网络来与服务器程序进行通信。

命名管道和共享内存

如果我们的服务器程序和客户端程序都运行在同一台操作系统为Windows的机器上的话，我们可以下边这两种方式来通信：

• 使用命名管道来进行线程间通信，不过需要在启动服务器程序的命令中加上--enable-named-pipe参数，然后在启动客户端程序的命令中加入--protocal=pipe参数。
• 使用共享内存来进行线程间通信，不过需要在启动服务器程序的命令中加上--shared-memory参数，在成功启动服务器后，共享内存便成为本地客户端程序的默认连接方式，不过我们也可以在启动客户端程序的命令中加入--protocal=pipe参数来显式的指定使用共享内存进行通信。

小贴士：

啥是个命名管道？啥是个共享内存？别问我，我也不知道，这一段儿纯属从别的书上抄过来的，唠叨这个纯属为了内容的完整性～ 等我之后有空研究研究啥是个命名管道，啥是个共享内存，不过不妨碍我们介绍`MySQL`的知识，不了解忽略它就好了～

Unix域套接字

如果我们的服务器程序和客户端程序都运行在同一台操作系统为类Unix的机器上的话，我们可以使用Unix域套接字来进行线程间通信。

如果我们在启动客户端程序的时候指定的主机名为localhost，或者指定了--protocal=socket的启动参数，那服务器程序和客户端程序之间就可以通过Unix域套接字来进行通信了。这个所谓的Unix域套接字其实是一个文件，它的默认文件路径是\tmp/mysql.sock，其实通信的过程就是一个进程往文件中写数据，另一个从文件中读数据，这就起到了通信的效果。如果你不想用Unix域套接字的默认文件路径，可以在启动客户端程序的时候指定-S参数来明确指定它的路径，比如这样：

mysql -hlocalhost -uroot -S /Users/wangqingfeng/mysql.sock -p

这样该客户端程序和服务器程序就可以通过路径为/Users/wangqingfeng/mysql.sock的Unix域套接字文件进行通信了。

服务器处理客户端请求

其实不论客户端程序和服务器程序是采用哪种方式进行通信，最后实现的效果都是：客户端程序向服务器程序发送一段文本（MySQL语句），服务器程序处理后再向客户端返回一段文本（处理结果）。那服务器程序对客户端发送的请求做了什么处理，才能产生最后的处理结果呢？我们以比较复杂的查询请求为例来画个图展示一下大致的过程：

image_1c8d26fmg1af0ms81cpc7gm8lv39.png-97.9kB

从图中我们可以看出，服务器程序处理来自客户端的查询请求大致需要经过三个部分，分别是连接管理、解析与优化、存储引擎。下边我们来详细看一下这三个部分都干了什么。

连接管理

客户端程序可以采用我们上边介绍的TCP/IP、命名管道和共享内存、Unix域套接字这几种方式之一来与服务器程序建立连接，服务器程序会缓存一些线程，每当有客户端连接进来的时候，会为这个客户端程序分配一个线程来处理它发过来的请求。

小贴士：

创建和销毁线程会耗费很多性能，所以在服务器端维护许多线程可以减少这部分的性能损耗。但是服务器端维护的线程数目是有限的，如果在短时间内有超级多的客户端连接进来的话，会有一部分因为获取不到线程而进入等待状态，直到有的客户端断开连接后这些等待的客户端才可以被分配到线程。

在客户端程序发起连接的时候，需要携带主机信息、用户名、密码，服务器程序会对客户端程序提供的这些信息进行认证，如果认证失败，服务器程序会拒绝连接。另外，如果客户端程序和服务器程序不运行在一台计算机上，我们还可以采用使用了SSL（安全套接字）的网络连接进行通信，来保证数据传输的安全性。

优化与执行

当客户端程序成功的与服务器程序建立连接之后，就可以把文本命令发送到服务器程序了。这个部分大致需要需要查询缓存、语法解析、查询优化这几个步骤来完成，我们详细来看。

查询缓存

如果我问你9+8×16-3×2×17的值是多少，你可能会用计算器去算一下，或者牛逼一点用心算，最终得到了结果35，如果我再问你一遍9+8×16-3×2×17的值是多少，你还用再傻呵呵的算一遍么？我们刚刚已经算过了，直接说答案就好了。MySQL服务器程序处理查询请求的过程也是这样，会把刚刚处理过的查询请求和结果缓存起来，如果下一次有一模一样的请求过来，直接从缓存中查找结果就好了，就不用再傻呵呵的去底层的表中查找了。

当然，MySQL服务器并没有人聪明，如果两个查询请求在任何字符上的不同（例如：空格、注释），都会导致缓存不会命中。另外，如果查询请求中包含系统函数、存储函数、自定义变量、mysql库中的系统表，那这个请求就不会被缓存，以函数举例，可能同样的函数的两次调用会产生不一样的结果，比如函数NOW，每次调用都会产生最新的当前时间，如果在一个查询请求中调用了这个函数，那即使查询请求的文本信息都一样，那不同时间的两次查询也应该得到不同的结果，如果在第一次查询时就缓存了，那第二次查询的时候直接使用第一次查询的结果就是错误的！

不过既然是缓存，那就有它缓存失效的时候。MySQL的缓存系统会监测涉及到的每张表，只要该表的结构或者数据被修改，那与该表有关的缓存都会失效！

语法解析

如果缓存没有命中，接下来就需要进入正式的查询阶段了。首先客户端程序发送过来的请求只是一段文本而已，MySQL服务器程序首先要对这段文本做分析，判断请求的语法是否正确，然后从文本中要查询的表、各种查询条件都提取出来。

如何从指定的文本中提取出我们需要的信息，这其实是一个编译问题，这个过程首先会把指定的文本根据语法规则来验证和解析成一颗语法树，吧啦吧啦，啥是个编译？咋弄出来的树？这些问题不属于我们讨论的范畴，如果想知道更多，等我～

查询优化

根据语法解析，服务器程序获得到了需要的信息，比如查询列表是什么，表是哪个，搜索条件是什么等等，但光有这些是不够的，因为我们写的MySQL语句可能执行起来效率并不是很高，MySQL的优化程序会对我们的语句做一些优化，如外连接转换为内连接、表达式简化、子查询的转为连接、使用索引吧啦吧啦的一堆东西，这部分我们后边会详细唠叨，现在你只需要知道在MySQL服务器程序处理请求的过程中有这么一个步骤就好了。

存储引擎

截止到服务器程序完成了查询优化为止，还没有真正的去访问真实的数据表，MySQL把数据的存储和提取操作都封装到了一个叫存储引擎的模块里，我们知道表是由一行一行的记录组成的，但这只是一个逻辑上的概念，物理上如何表示记录，怎么从表中读取数据，怎么把数据写入具体的物理存储器上，这都是存储引擎负责的事情。为了实现不同的功能，MySQL提供了各式各样的存储引擎，不同存储引擎管理的表结构可能不同，采用的存取算法也可能不同。不过这些存储引擎都向上边的服务层提供统一的调用接口，也就是对于我们使用者来说，如果我们需要使用某个存储引擎提供的特定功能，只需要简单的切换表的存储引擎就可以了。

小贴士：

为什么叫`引擎`呢？因为装逼呗～ 其实这个存储引擎以前叫做`表处理器`，后来人们觉得太土，就改成了`存储引擎`的叫法，它的功能就是接收上层传下来的指令，然后对表中的数据进行提取或写入操作。

所以在服务器程序完成了查询优化后，只需调用底层存储引擎提供的调用接口，获取到数据后返回给客户端程序就好了。

常用存储引擎

MySQL支持非常多种存储引擎，我这先列举一些：

用与数据存档（行被插入后不能再修改）

这么多我们怎么挑啊，哈哈，你多虑了，其实我们最常用的就是InnoDB和MyISAM，有时会提一下Memory。其中InnoDB是MySQL默认的存储引擎，我们之后会详细唠叨这个存储引擎的各种功能，现在先看一下一些存储引擎对于某些功能的支持情况：

密密麻麻列了这么多，看的头皮都发麻了，其实这些东西大家没必要立即就给记住，我列出来的目的就是想让大家明白不同的存储引擎支持不同的功能，这些功能我们会在后边的唠叨中慢慢让大家理解的～

关于存储引擎的一些操作

查看当前服务器程序支持的存储引擎

我们可以用下边这个命令来查看当前服务器程序支持的存储引擎：

SHOW ENGINES;

来看一下调用效果：

image_1c8etid0jl5s3u1gbj1lV**l49.png-166.5kB

其中的Support列表示该存储引擎是否可用，DEFAULT值代表是当前服务器程序的默认存储引擎。Comment列是对存储引擎的一个描述，英文的，将就着看吧。Transactions列代表该存储引擎是否支持事务处理。XA列代表着该存储引擎是否支持分布式事务。Savepoints代表着该列是否支持部分事务回滚。

小贴士：

好吧，也许你并不知道什么是个事务、更别提分布式事务了，这些内容我们在后边的章节会详细唠叨，现在瞅一眼看个新鲜就得了。

设置表的存储引擎

我们前边说过，存储引擎是负责对表中的数据进行提取和写入工作的，我们可以为不同的表设置不同的存储引擎，也就是说不同的表可以有不同的物理存储结构，不同的提取和写入方式。

创建表时指定存储引擎

我们之前创建表的语句都没有指定表的存储引擎，那就会使用默认的存储引擎InnoDB（当然这个默认的存储引擎也是可以修改的，我们在后边的章节中再说怎么改）。如果我们想显式的指定一下表的存储引擎，那可以这么写：

CREATE TABLE 表名(
    建表语句;
) ENGINE = 存储引擎名称;

比如我们想创建一个存储引擎为MyISAM的表可以这么写：

mysql> CREATE TABLE engine_demo_table(
    ->     i int
    -> ) ENGINE = MyISAM;
Query OK, 0 rows affected (0.02 sec)

mysql>

修改表的存储引擎

如果表已经建好了，我们也可以使用下边这个语句来修改表的存储引擎：

ALTER TABLE 表名 ENGINE = 存储引擎名称;

比如我们修改一下engine_demo_table表的存储引擎：

mysql> ALTER TABLE engine_demo_table ENGINE = InnoDB;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

这时我们再查看一下engine_demo_table的表结构：

mysql> SHOW CREATE TABLE engine_demo_table\G
*************************** 1. row ***************************
       Table: engine_demo_table
Create Table: CREATE TABLE `engine_demo_table` (
  `i` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

mysql>

可以看到该表的存储引擎已经改为InnoDB了。

总结

1. 以查询请求为例，服务器程序处理客户端发送过来的请求大致分为三部分：
   • 查询缓存
   • 语法解析
   • 查询优化
   • 第一部分：连接管理。 主要是负责连接的建立与信息的认证。
   • 第二部分：解析与优化。 这一部分分为三个步骤处理：
   • 第三部分：存储引擎。 主要负责对底层数据表中的数据进行提取和写入工作。

2. MySQL支持的存储引擎有好多好多种，它们在完成不同的功能上各有优劣，我们常用的就是InnoDB和MyISAM，其中InnoDB是服务器程序的默认存储引擎。
3. 一些常用的关于存储引擎的用法如下：
   • 查看当前服务器程序支持的存储引擎： SHOW ENGINES;
   • 创建表时指定表的存储引擎： CREATE TABLE 表名( 建表语句; ) ENGINE = 存储引擎名称;
   • 修改表的存储引擎： ALTER TABLE 表名 ENGINE = 存储引擎名称;