乐趣区

关于java:给隔壁的妹子讲『一个SQL语句是如何执行的』

jiezi

前言

SQL 作为 Web 开发是永远来到不的一个话题，天天写 SQL，可是你晓得一个 SQL 是如何执行的吗？

select name from user where id = 1;

下面是一个简略的查问语句，交给数据库去执行，而后返回 name。看起来很简略，可是外部的执行过程却很多人都不晓得。

明天就把 MySQL 拆开看看，看一下它到底是怎么工作的。

SQL 根本架构

从上图能够看出，MySQL 分为 Server 层和 存储引擎层

Server 层

连接器

连接器次要是与客户端建设连贯，蕴含本地 socket 和大多数基于客户端 / 服务端工具实现的相似于 tcp/ip 的通信。连贯胜利之后会同时校验用户的权限，等相干平安计划。如咱们罕用的建设连贯形式

mysql -h ip -P 3306 -u root -p

连贯是能够在 - p 前面输出明码，然而思考到平安问题 不倡议这样操作，-P 后是端口号，-p 是明码。留神大小写。

登录胜利之后，会校验当然登录账号的权限。后续所有的数据库操作都被以后权限所限度。因而，管理员批改用户权限时，不会立刻失效，须要从新连贯才会失效。

MySQL 默认状况下，当一个链接闲暇超过 8（60 60 8）小时之后会主动断开连接。然而连接池则认为该被断开的连贯仍然无效。这个时候如果客户端代码发送申请时，连接池会把曾经生效的代码返回至客户端。这样就会导致代码异样。

通过 show global variables like '%timeout%' 可进行查看，缺省状况下是应用 wait_timeout 这个字段。

另外能够用show processlist; 用来显示用户正在运行的线程。

留神：除了 root 用户能看到所有正在运行的线程外，其余用户都只能看到本人正在运行的线程，看不到其它用户正在运行的线程。除非独自个这个用户赋予了 PROCESS 权限。

mysql> show processlist;
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
| Id | User            | Host      | db   | Command | Time | State                  | Info             |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
|  4 | event_scheduler | localhost | NULL | Daemon  |  461 | Waiting on empty queue | NULL             |
| 13 | root            | localhost | NULL | Query   |    0 | starting               | show processlist |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+

查问缓存

当咱们建设连贯之后，执行 SQL 语句时，会先进行缓存查问（如果开启了缓存查问）。如果之前执行了雷同的 SQL 语句，则会从缓存中间接返回后果。这个过程能够了解为 SQL 文本和查问后果的映射。

然而查问缓存真的能晋升效率吗？实践上，不倡议开启查问缓存

因为缓存和生效都会有额定的资源耗费，数据产生扭转或者表构造产生扭转时，都会导致缓存生效。最差的状况就是你刚建设了一份缓存，另外一边又有人批改数据。这样导致缓存生效，从新建设了一份新的缓存。

有这些 INSERT、UPDATE、DELETE、TRUNCATE、ALTER TABLE、DROP TABLE 或 DROP DATABASE 会导致缓存数据生效。所以查问缓存适宜有大量雷同查问的利用，不适宜有大量数据更新的利用

在 MySQL8.0 的版本删除了查问缓存的性能。

如果你是 8.0 之前的版本，能够通过以下办法敞开查问缓存：

1、长期敞开，间接执行命令行

set global query_cache_size=0

set global query_cache_type=0

2、永恒敞开，批改配置文件 my.cnf , 增加上面的配置即可。

query_cache_type=0

query_cache_size=0

分析器

当在查问缓存中没有找到对象的查问后果时，这时候就须要分析器对 SQL 进行解析。比方解析出响应的关键词。如 Select（查问）、Delete（删除）等等，同时也会把相应的表明、字段名都剖析进去。如果 SQL 语法错误，会通知咱们 You have an error in your SQL syntax

优化器

SQL 理论的执行程序不肯定就是咱们写的程序。在通过分析器的解析，数据库晓得了咱们要做什么。而后会依照肯定的规定重写 SQL。当有多个索引的时候，优化器也会决定去应用哪个索引；当多表关联查问的时候，也会去决定各个表的链接程序。总之，优化器会通过一系列的算法规定去给出一个最优的执行策略。

执行器

SQL 通过分析器晓得要做什么，通过优化器晓得该怎么做。最初通过执行器就进入了执行阶段。

首先会依据连贯的账号查看是否有操作该表的权限。如果没有，则返回权限谬误。如果有权限，则继续执行。

关上表的时候，执行器会依据表的引擎 去应用该引擎提供的接口。

存储引擎层

存储引擎层负责数据的存储和提取。

可通过 show engines 查看 MySQL 的存储引擎。存储引擎有 InnoDB 、 MylSAM 、 MEMORY 、 MERGE 等等..

然而咱们罕用的根本是 InnoDB 和MylSAM。

InnoDB 在 5.5.5 版本之后为默认的存储引擎

InnoDB

InnoDB 是一个 事务型 的存储引擎，有行级锁定和外键束缚, 提供了具备提交，回滚和解体复原的事务平安，然而比照 MyLSAM 引擎，写的效率会比差一些，并且会占用更多的磁盘空间以保持数据和索引。

特点:

  1. 更新多的表，适宜解决多重并发的更新申请。
  2. 反对事务。
  3. 能够从劫难中复原（通过 bin-log 日志等）。
  4. 外键束缚。只有他反对外键。
  5. 反对主动减少列属性 auto_increment。

MylSAM

Mylsam 存储引擎独立于操作系统，简略说就是可用在 windows 上应用，也可用将数据转移到 Lunex 操作系统上。零碎兼容性很好！！！。这种存储引擎在建表的时候，它会创立 3 个文件。别离是（.frm, .MYD, .MYI），简略阐明一下：.frm 存储表的定义（也就是表构造啦），.MYD 就是表外面的数据，.MYD 存储索引。这样的划分操作系统对大文件的操作是比较慢的，这样将表分为三个文件，那么.MYD 这个文件独自来存放数据天然能够优化数据库的查问等操作。

特点:

1、不反对事务

2、不反对外键

3、查问速度很快。如果数据库 insert 和 update 的操作比拟多的话采纳表锁效率低（倡议应用 innodb）。

4、对表进行加锁

总结

Server 层涵盖了 MySQL 执行的大多数的外围性能，以及各种各样的内置函数，比方工夫、日期等，不论应用的是什么存储引擎，它的 Server 层是一样的。以上就是对一个 SQL 执行流程的简略介绍。感激大家的浏览！

文末福利

肝了全网，43 份 Java 思维导图，须要自取！！!

《Java 面试手册》V1.0 版本，高清 PDF 收费获取

退出移动版