SQL开发知识:MySQL中一条SQL查询语句是如何履行的
前言
MySQL是典型的C/S架构(客户端/服务器架构),客户端进程向服务端进程发送一段文本(MySQL指令),服务器进程进行语句处理然后返回履行结果。
问题来了。服务器进程对客户端发送的要求究竟做了甚么处理呢?本文以查询要求为例,讲授MySQL服务器进程的处理流程。
以下图所示,服务器进程在处理客户端要求的时候,大致需要进行3个步骤:
-
处理连接
-
解析与优化
-
存储引擎
接下来我们来详细了解一下这3步具体都做了甚么。
1. 处理连接
客户端向服务器发送要求并终究收到响应,本质上是一个进程间通讯的进程。
MySQL有专门用于处理连接的模块——连接器。
1.1 客户端和服务真个通讯方式
1.1.1 TCP/IP协议
TCP/IP协议是MySQL客户端和服务器最经常使用的通讯方式。
我们平时所说的MySQL服务器默许监听的端口是3306,这句话的条件是客户端进程和服务器进程使用的是TCP/IP协议进行通讯。
我们在使用mysql命令启动客户端程序时,只要在-h参数后跟随IP地址作为服务器进程所在的主机地址,那末通讯方式便是TCP/IP协议。
如果客户端进程和服务器进程位于同一台主机,且要使用TCP/IP协议进行通讯,则IP地址需要指定为127.0.0.1,而不能使用localhost
1.1.2 UNIX域套接字
如果客户端进程和服务器进程都位于类UNIX操作系统(MacOS、Centos、Ubuntu等)的主机之上,并且在启动客户端程序时没有指定主机名,或指定的主机名为localhost,又或指定了–protocol=socket的启动参数,那末客户端进程和服务器进程就会使用UNIX域套接字进行进程间通讯。
MySQL服务器进程默许监听的UNIX域套接字文件为/temp/mysql.sock,客户端进程启动时也默许会连接到这个UNIX域套接字文件之上。
如果不明白UNIX域套接字究竟是甚么也没关系,只要知道这是进程之间的一种通讯方式就能够了,这里提及的主要目的是希望读者知晓MySQL客户端和进程通讯方式不止于TCP/IP协议
1.1.3 命名管道和同享内存
如果你的MySQL是安装在Windows主机之上,客户端和服务器进程可使用命名管道和同享内存的方式进行通讯。
不过使用这些通讯方式需要在服务端和客户端启动时添加一些启动参数。
-
使用命名管道进行通讯。需要在启动服务器时添加–enable-named-pipe参数,同时在启动客户端进程时添加–pipe或–protocol=pipe参数
-
使用同享内存进行通讯。需要在启动服务器时添加–shared-memory参数,启动成功后,同享内存便成为本地客户端程序的默许连接方式;也能够在启动客户端进程的命令中加上–protocol=memory参数明确指定使用同享内存进行通讯
如果不明白命名管道和同享内存究竟是甚么没关系,只要知道这是进程之间的一种通讯方式就能够了,这里提及的主要目的是希望读者知晓MySQL客户端和进程通讯方式不止于TCP/IP协议
1.2 权限验证
确认通讯方式并且成功建立连接以后,连接器就要开始验证你的身份了,使用的信息就是你的用户名和密码。
-
如果用户名或密码毛病,客户端连接会立即断开
-
如果用户名密码认证通过,连接器会到权限表里面查出当前登陆用户具有的权限。以后这个连接里面的权限判断逻辑,都将依赖于此时读到的权限。
1.3 查看MySQL连接
每当一个客户端连接到服务端时,服务端进程都会创建一个单独的线程来处应当前客户真个交互操作。
那末怎么查看MySQL当前所有的连接?
+——————-+——-+
| Variable_name | Value |
+——————-+——-+
| Threads_cached | 0 |
| Threads_connected | 1 |
| Threads_created | 1 |
| Threads_running | 1 |
+——————-+——-+
各字段含义以下表
字段 | 含义 |
---|---|
Threads_cached | 缓存中的线程连接数 |
Threads_connected | 当前打开的连接数 |
Threads_created | 为处理连接创建的线程数 |
Threads_running | 非睡眠状态的连接数,通常指并发连接数 |
建立连接以后,除非客户端主动断开连接,否则服务器会等待客户端发送要求。但是线程的创建和保持是需要消耗服务器资源的,因此服务器会把长时间不活动的客户端连接断开。
有2个参数控制这个自动断开连接的行动,每一个参数都默许为28800秒,8小时。
mysql> show global variables like ‘wait_timeout’;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| wait_timeout | 28800 |
+—————+——-+
— 交互式超时时间,如数据库查看工具Navicat等
mysql> show global variables like ‘interactive_timeout’;
+———————+——-+
| Variable_name | Value |
+———————+——-+
| interactive_timeout | 28800 |
+———————+——-+
既然连接消耗资源,那是不是是MySQL的最大连接数也有默许限制呢?没错!默许最大连接数为151。
+—————–+——-+
| Variable_name | Value |
+—————–+——-+
| max_connections | 151 |
+—————–+——-+
题外话:仔细的读者可能会发现MySQL某些查询语句带有global关键字,这个关键字有甚么含义呢?
MySQL的系统变量有两个作用范围(不辨别大小写),分别是
-
GLOBAL(全局范围):变量的设置影响服务器和所有客户端
-
SESSION(会话范围):变量的设置仅影响当前连接(会话)
但是并不是每一个参数都具有两个作用范围,比如允许同时连接到服务器的客户真个数量max_connections就只有全局级别。
当没有带作用范围关键字时,默许是SESSION级别,包括查询和修改操作。
比如修改一个参数以后,在当前窗口生效了,但是在其他窗口却没有生效
set autocommit = on;
因此,如果只是临时修改,请使用SESSION级别,如果需要当前设置在其他会话中生效,需要使用GLOBAL关键字。
到此为止,服务器进程已和客户端进程建立了连接,下一步将处理客户端传来的要求了。
2. 解析与优化
服务器收到客户端传来的要求以后,还需要经过查询缓存、词法语法解析和预处理、查询优化的处理。
2.1 查询缓存
如果我们两次都履行同一条查询指令,第二次的响应时间是不是比第一次的响应时间短一些?
之前使用过Redis缓存工具的读者应当会有这个很自然的想法,MySQL收到查询要求以后应当先到缓存中查看一下,看一下之前是不是是履行过这条指令。如果缓存命中,则直接返回结果;否则重新进行查询,然后加入缓存。
MySQL确切内部自带了一个缓存模块。
现在有一张500W行且没有添加索引的数据表,我履行以下命令两次,第二次是不是变得很快?
其实不会!说明缓存没有生效,为何?MySQL默许是关闭本身的缓存功能的,查看一下query_cache_type变量设置。
+——————————+———+
| Variable_name | Value |
+——————————+———+
| query_cache_type | OFF |
+——————————+———+
默许关闭就意味着不推荐,MySQL为何不推荐用户使用自己的缓存功能呢?
-
MySQL自带的缓存系统利用场景非常有限,它要求SQL语句一定要如出一辙,多一个空格,变一个大小写都被认为是两条区别的SQL语句
-
缓存失效非常频繁。只要一个表的数据有任何修改,针对该表的所有缓存都会失效。对更新频繁的数据表而言,缓存命中率非常低!
所以缓存的功能或者交给专业的ORM框架(比如MyBatis默许开启一级缓存)或独立的缓存服务Redis更加合适。
MySQL8.0已完全移除缓存功能
2.2 解析器 & 预处理器(Parser & Preprocessor)
现在跳过缓存这一步了,接下来需要做甚么了?
如果我随意在客户端终端里输入一个字符串chanmufeng,服务器返回了一个1064的毛病
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 ‘chanmufeng’ at line 1
服务器是怎样判断出我的输入是毛病的呢?这就是MySQL的Parser解析器的作用了,它主要包括两步,分别是词法解析和语法分析。
2.2.1 词法解析
以下面的SQL语句为例
分析器先会做“词法分析”,就是把一条完全的SQL语句打碎成一个个单词,比如一条简单的SQL语句,会打碎成8个符号,每一个符号是甚么类型,从哪里开始到哪里结束。
MySQL 从你输入的SELECT这个关键字辨认出来,这是一个查询语句。它也要把字符串t_user识 别成“表名 t_user”,把字符串user_name辨认成“列 user_name"。
2.2.2 语法分析
做完词法解析,接下来需要做语法分析了。
根据词法分析的结果,语法分析器会根据语法规则,判断你输入的这个 SQL 语句会不会满足 MySQL 语法,比如单引号会不会闭合,关键词拼写会不会正确等。
解析器会根据SQL语句生成一个数据结构,这个数据结构我们成为解析树。
我故意拼错了SELECT关键字,MySQL报了语法毛病,就是在语法分析这一步。
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 t_user WHERE user_name = ‘蝉沐风” at line 1
词法语法分析是一个非常基础的功能,Java 的编译器、百度搜索引擎如果要辨认语句,一定要也要有词法语法分析功能。
任何数据库的中间件,要解析 SQL完成路由功能,也一定要要有词法和语法分析功能,比如 Mycat,Sharding-JDBC(用到了Druid Parser)等都是如此。在市面上也有很多的开源的词法解析的工具,比如 LEX,Yacc等。
2.2.3 预处理器
如果我们写了一条语法和词法都没有问题的SQL,但是字段名和表名却不存在,这个毛病是在哪个阶段爆出的呢?
词法解析和语法分析是没法知道数据库里有甚么表,有哪几种字段的。要知道这些信息还需要解析阶段的另外一个工具——预处理器。
它会检查生成的解析树,解决解析器没法解析的语义。比如,它会检查表和列名会不会存在,检查名字和别名,保证没有歧义。预处理以后得到一个新的解析树。
本质上,解析和预处理是一个编译进程,触及到词法解析、语法和语义分析,更多细节我们不会探究,感兴趣的读者可以看一下编译原理方面的书籍。
2.3 查询优化器(Optimizer)与查询履行计划
到了这一步,MySQL终究知道我们想查询的表和列和相应的搜索条件了,是不是是可以直接进行查询了?
还不行。MySQL作者担心我们写的SQL太垃圾,所以有设计出一个叫做查询优化器的东东,辅助我们提高查询效力。
2.3.1 甚么是查询优化器?
一条 SQL语句是不是是只有一种履行方式?或说数据库终究履行的 SQL是不是是就是我们发送的 SQL?
不是。一条 SQL 语句是可以有很多种履行方式的,终究返回相同的结果,他们是等价的。
举一个非常简单的例子,比如你履行下面这样的语句:
-
既可以先从表 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就能够看到履行计划的信息。
+—-+————-+——–+————+——+—————+——+———+——+——+———-+————-+
| 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的整体架构有所了解就能够了,具体细节以后单独开篇介绍
3. 存储引擎
经历千辛万苦,MySQL终究算出了终究的履行计划,然后就能够直接履行了吗?
好吧。。。仍然还不可以。
我们知道,表是由一行一行的记录组成的,但这只是逻辑上的概念,或说只是看上去是这样而已。
3.1 甚么是存储引擎
到底该把数据存储在甚么位置,是内存或者磁盘?怎样从表里读取数据,和怎样把数据写入具体的表中,这都是存储引擎 负责的事情。
好吧,看到这里也许你还不知道存储引擎究竟是甚么。毕竟存储引擎这个名字听起来太玄乎了,它的前身叫做表处理器,是不是是就接地气了许多呢?
3.2 为何需要存储引擎
由于存储的需求区别。
试想一下:
-
如果一张表,需要很高的访问速度,而不需要斟酌持久化的问题,是不是是最好把数据放在内存呢?
-
如果一张表,是用来做历史数据存档的,不需要修改,也不需要索引,那是不是是要支持数据的紧缩?
-
如果一张表用在读写并发很多的业务中,是不是是要支持读写互不干扰,而且要保证比较高的数据一致性呢?
大家应当明白了,为何要支持这么多的存储引擎,由于一种存储引擎不能提供所有的特性。
存储引擎是计算机抽象的典型代表,它的功能就是接受上层指令,然后对表中数据进行读取和写入,而这些操作对上层完全是屏蔽的。你乃至可以查阅MySQL文档定义自己的存储引擎,只要对外实现一样的接口就能够了。
存储引擎就是MySQL对数据进行读写的插件而已,可以根据区别目的随便更换(插拔)
3.3 存储引擎怎样用
3.3.1 创建表的时候指定存储引擎
在创建表的时候可以指定当前表的存储引擎,如果没有指定,默许的存储引擎为InnoDB,如果想显式指定存储引擎,可以这样
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=innodb DEFAULT CHARSET=utf8mb4;
3.3.2 修改表的存储引擎
3.4 存储引擎底层区分
下面我们分别创建3张设置了区别存储引擎的表,t_user_innodb、t_user_myisam、t_user_memory
我们看一下区别存储引擎在底层存储方面的差异,首先找到MySQL的数据存储目录
+—————+—————–+
| Variable_name | Value |
+—————+—————–+
| datadir | /var/lib/mysql/ |
+—————+—————–+
进入到目标目录以后,找到当前数据库对应的目录(MySQL会为一个数据库创建一个同名的目录),数据库中表的存储结构以下
区别的存储引擎寄存数据的方式不一样,产生的文件数量和格式也不一样,InnoDB文件包括2个,MEMORY文件包括1个,MYISAM文件包括3个。
3.5 常见存储引擎比较
首先我们查看一下当前MySQL服务器支持的存储引擎都有哪一些。
+——————–+———+————–+——+————+
| 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
总结
到此这篇关于SQL开发知识:SQL开发知识:SQL开发知识:MySQL中一条SQL查询语句是如何履行的文章就介绍到这了,更多相关MySQL SQL查询语句履行内容请搜索之前的文章或继续浏览下面的相关文章希望大家以后多多支持!
文章来源:丸子建站
文章标题:SQL开发知识:MySQL中一条SQL查询语句是如何履行的
https://www.wanzijz.com/view/81651.html