MySQL中一条SQL查询语句是如何执行的(2)-木庄网络博客

当前第2页返回上一页

词法解析和语法分析是无法知道数据库里有什么表，有哪些字段的。要知道这些信息还需要解析阶段的另一个工具——预处理器。

它会检查生成的解析树，解决解析器无法解析的语义。比如，它会检查表和列名是否存在，检查名字和别名，保证没有歧义。预处理之后得到一个新的解析树。

本质上，解析和预处理是一个编译过程，涉及到词法解析、语法和语义分析，更多细节我们不会探究，感兴趣的读者可以看一下编译原理方面的书籍。

2.3 查询优化器（Optimizer）与查询执行计划

到了这一步，MySQL终于知道我们想查询的表和列以及相应的搜索条件了，是不是可以直接进行查询了？

还不行。MySQL作者担心我们写的SQL太垃圾，所以有设计出一个叫做查询优化器的东东，辅助我们提高查询效率。

2.3.1 什么是查询优化器？

一条 SQL语句是不是只有一种执行方式？或者说数据库最终执行的 SQL是不是就是我们发送的 SQL?

不是。一条 SQL 语句是可以有很多种执行方式的，最终返回相同的结果，他们是等价的。

举一个非常简单的例子，比如你执行下面这样的语句：

SELECT?*?FROM?t1,?t2?WHERE?t1.id?=?10?AND?t2.id?=?20

既可以先从表 t1 里面取出 id=10 的记录，再根据 id 值关联到表 t2，再判断 t2 里面 id 的值是否等于 20。
也可以先从表 t2 里面取出 id=20 的记录，再根据 id 值关联到表 t1，再判断 t1 里面 id 的值是否等于 10。

这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，如果有这么多种执行方式，这些执行方式怎么得到的？最终选择哪一种去执行？根据什么判断标准去选择？

这个就是 MySQL的查询优化器的模块（Optimizer）的工作。

查询优化器的目的就是根据解析树生成不同的执行计划（Execution Plan），然后选择一种最优的执行计划，MySQL 里面使用的是基于开销（cost）的优化器，哪种执行计划开销最小，就用哪种。

2.3.2 优化器究竟做了什么？

举两个简单的例子∶

当我们对多张表进行关联查询的时候，以哪个表的数据作为基准表。
有多个索引可以使用的时候，选择哪个索引。

实际上，对于每一种数据库来说，优化器的模块都是必不可少的，他们通过复杂的算法实现尽可能优化查询效率。

往细节上说，查询优化器主要做了下面几方面的优化：

子查询优化
等价谓词重写
条件化简
外连接消除
嵌套连接消除
连接消除
语义优化

本文不会对优化的细节展开讲解，大家先对MySQL的整体架构有所了解就可以了，具体细节之后单独开篇介绍

但是优化器也不是万能的，如果SQL语句写得实在太垃圾，再牛的优化器也救不了你了。因此大家在编写SQL语句的时候还是要有意识地进行优化。

2.3.3 执行计划

优化完之后，得到一个什么东西呢？优化器最终会把解析树变成一个查询执行计划。

查询执行计划展示了接下来执行查询的具体方式，比如多张表关联查询，先查询哪张表，在执行查询的时候有多个索引可以使用，实际上该使用哪些索引。

MySQL提供了一个查看执行计划的工具。我们在 SQL语句前面加上 EXPLAIN就可以看到执行计划的信息。

mysql>?EXPLAIN?SELECT?*?FROM?t_user?WHERE?user_name?=?'';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|?id?|?select_type?|?table??|?partitions?|?type?|?possible_keys?|?key??|?key_len?|?ref??|?rows?|?filtered?|?Extra???????|
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|??1?|?SIMPLE??????|?t_user?|?NULL???????|?ALL??|?NULL??????????|?NULL?|?NULL????|?NULL?|????1?|???100.00?|?Using?where?|
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+

如果要得到更加详细的信息，还可以用FORMAT=JSON，或者开启optimizer trace。

mysql&gt;?EXPLAIN?FORMAT=JSON?SELECT?*?FROM?t_user?WHERE?user_name?=?'';

文本不会带大家详细了解执行计划的每一个参数，内容很庞杂，大家先对MySQL的整体架构有所了解就可以了，具体细节之后单独开篇介绍

3. 存储引擎

经历千辛万苦，MySQL终于算出了最终的执行计划，然后就可以直接执行了吗？

好吧。。。依然还不可以。

我们知道，表是由一行一行的记录组成的，但这只是逻辑上的概念，或者说只是看上去是这样而已。

3.1 什么是存储引擎

到底该把数据存储在什么位置，是内存还是磁盘？怎么从表里读取数据，以及怎么把数据写入具体的表中，这都是存储引擎负责的事情。

好吧，看到这里或许你还不知道存储引擎到底是什么。毕竟存储引擎这个名字听起来太玄乎了，它的前身叫做表处理器，是不是就接地气了许多呢？

3.2 为什么需要存储引擎

因为存储的需求不同。

试想一下：

如果一张表，需要很高的访问速度，而不需要考虑持久化的问题，是不是最好把数据放在内存呢？
如果一张表，是用来做历史数据存档的，不需要修改，也不需要索引，那是不是要支持数据的压缩?
如果一张表用在读写并发很多的业务中，是不是要支持读写互不干扰，而且要保证比较高的数据一致性呢?

大家应该明白了，为什么要支持这么多的存储引擎，因为一种存储引擎不能提供所有的特性。

存储引擎是计算机抽象的典型代表，它的功能就是接受上层指令，然后对表中数据进行读取和写入，而这些操作对上层完全是屏蔽的。你甚至可以查阅MySQL文档定义自己的存储引擎，只要对外实现同样的接口就可以了。

存储引擎就是MySQL对数据进行读写的插件而已，可以根据不同目的随意更换（插拔）

3.3 存储引擎怎么用

3.3.1 创建表的时候指定存储引擎

在创建表的时候可以指定当前表的存储引擎，如果没有指定，默认的存储引擎为InnoDB，如果想显式指定存储引擎，可以这样

CREATE?TABLE?`t_user_innodb`?(
??`id`?int(11)?NOT?NULL?AUTO_INCREMENT,
??PRIMARY?KEY?(`id`)
)?ENGINE=innodb?DEFAULT?CHARSET=utf8mb4;

3.3.2 修改表的存储引擎

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

3.4 存储引擎底层区别

下面我们分别创建3张设置了不同存储引擎的表，t_user_innodb、t_user_myisam、t_user_memory

我们看一下不同存储引擎在底层存储方面的差异，首先找到MySQL的数据存储目录

mysql>?show?variables?like?'datadir';
+---------------+-----------------+
|?Variable_name?|?Value???????????|
+---------------+-----------------+
|?datadir???????|?/var/lib/mysql/?|
+---------------+-----------------+

进入到目标目录之后，找到当前数据库对应的目录（MySQL会为一个数据库创建一个同名的目录），数据库中表的存储结构如下

不同的存储引擎存放数据的方式不一样，产生的文件数量和格式也不一样，InnoDB文件包含2个，MEMORY文件包含1个，MYISAM文件包含3个。

3.5 常见存储引擎比较

首先我们查看一下当前MySQL服务器支持的存储引擎都有哪一些。

mysql>?SHOW?ENGINES;
+--------------------+---------+--------------+------+------------+
|?Engine?????????????|?Support?|?Transactions?|?XA???|?Savepoints?|
+--------------------+---------+--------------+------+------------+
|?InnoDB?????????????|?DEFAULT?|?YES??????????|?YES??|?YES????????|
|?MRG_MYISAM?????????|?YES?????|?NO???????????|?NO???|?NO?????????|
|?MEMORY?????????????|?YES?????|?NO???????????|?NO???|?NO?????????|
|?BLACKHOLE??????????|?YES?????|?NO???????????|?NO???|?NO?????????|
|?MyISAM?????????????|?YES?????|?NO???????????|?NO???|?NO?????????|
|?CSV????????????????|?YES?????|?NO???????????|?NO???|?NO?????????|
|?ARCHIVE????????????|?YES?????|?NO???????????|?NO???|?NO?????????|
|?PERFORMANCE_SCHEMA?|?YES?????|?NO???????????|?NO???|?NO?????????|
|?FEDERATED??????????|?NO??????|?NULL?????????|?NULL?|?NULL???????|
+--------------------+---------+--------------+------+------------+

其中，

Support表示该存储引擎是否可用；
DEFAULT表示当前MySQL服务器默认的存储引擎；
Transactions表示该存储引擎是否支持事务；
XA表示该存储引擎是否支持分布式事务；
Savepoints表示该存储引擎是否支持事务的部分回滚。

3.5.1 MylSAM

应用范围比较小，表级锁定限制了读/写的性能，因此在Web和数据仓库配置中，通常用于只读或以读为主的工作。

特点:

支持表级别的锁（插入和更新会锁表），不支持事务；
拥有较高的插入（insert）和查询（select）速度；
存储了表的行数（count速度更快）。

怎么快速向数据库插入100万条数据？

可以先用MylSAM插入数据，然后修改存储引擎为InnoDB。

3.5.2 InnoDB

MySQL 5.7及更新版中的默认存储引擎。InnoDB是一个事务安全（与ACID兼容）的MySQL 存储引擎，它具有提交、回滚和崩溃恢复功能来保护用户数据。InnoDB行级锁（不升级为更粗粒度的锁）和Oracle风格的一致非锁读提高了多用户并发性。InnoDB将用户数据存储在聚集索引中，以减少基于主键的常见查询的I/O。为了保持数据完整性，InnoDB还支持外键引用完整性约束。

特点：

支持事务，支持外键，因此数据的完整性、一致性更高；
支持行级别的锁和表级别的锁；
支持读写并发，写不阻塞读（MVCC）；
特殊的索引存放方式，可以减少IO，提升査询效率。

番外：InnoDB本来是InnobaseOy公司开发的，它和MySQL AB公司合作开源了InnoDB的代码。但是没想到MySQL的竞争对手Oracle把InnobaseOy收购了。后来08年Sun公司（开发Java语言的Sun）收购了MySQL AB，09年Sun公司又被Oracle收购了，所以MySQL和 InnoDB又是一家了。有人觉得MySQL越来越像Oracle,其实也是这个原因。

3.5.3 Memory

将所有数据存储在RAM中，以便快速访问。这个引擎以前被称为堆引擎。

特点：

把数据放在内存里面，读写的速度很快，但是数据库重启或者崩溃，数据会全部消失；
只适合做临时表。

3.5.4 CSV

它的表实际上是带有逗号分隔值的文本文件。csv表允许以CSV格式导入或转储数据, 以便与读写相同格式的脚本和应用程序交换数据。因为CSV表没有索引，所以通常在正常操作期间将数据保存在InnoDB表中，只在导入或导出阶段使用csv表。

特点：

不允许空行，不支持索引；
格式通用，可以直接编辑，适合在不同数据库之间导入导出。

3.5.5 Archive

专用与存档，空间经过压缩，用于存储和检索大量很少引用的信息。

特点：

不支持索引；
不支持update、delete。

3.6 如何选择存储引擎

如果对数据一致性要求比较高，需要事务支持，可以选择InnoDB。
如果数据查询多更新少，对查询性能要求比较高，可以选择MyISAM。
如果需要一个用于查询的临时表，可以选择Memory。

如果所有的存储引擎都不能满足你的需求，并且技术能力足够，可以根据官网内部手册用C语言开发一个存储引擎：https://dev.mvsql.com/doc/internals/en/custom-engine.html

总结

到此这篇关于MySQL中一条SQL查询语句是如何执行的文章就介绍到这了,更多相关MySQL SQL查询语句执行内容请搜索

标签：SQL

返回前面的内容

数据库系统概念第6版

￥41.1元 机械工业出版社

本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。

转载请注明出处：木庄网络博客 » MySQL中一条SQL查询语句是如何执行的

MySQL中一条SQL查询语句是如何执行的