mysql之一条mysql语句时如何执行的

弈师亦友

于 2023-01-16 21:49:22 发布

阅读量156

点赞数

分类专栏： mysql 文章标签： mysql 数据库服务器

本文链接：https://blog.csdn.net/qq_43218521/article/details/128633211

版权

mysql 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

文章详细阐述了MySQL连接器在客户端与服务器之间的角色，包括TCP三次握手建立连接、权限验证和连接管理。同时，讨论了查询缓存的废弃原因，解析SQL的过程，以及执行SQL的阶段，如预处理、查询优化和执行。文章还举例说明了不同类型的查询（如主键查询、全表扫描和索引下推）以及如何通过选择合适的索引来提高查询效率。

摘要由CSDN通过智能技术生成

在这里插入图片描述

请把连接器的功能说明一下?
连接器负责建立客户端和mysql服务器之间的连接.当客户端在中断输入连接命令(mysql -h -u -p),传输层使用tcp协议,通过三次握手建立连接.
如果mysql服务服务没有启动,会报错:

如果mysql服务正常启动,完成三次握手,在传输层建立连接后,会进行密码验证,如果密码错误,会报错

如果密码没有问题,会获取用户权限,然后保存.本连接内所有后续操作都会根据这个权限.
为什么建立连接后,修改权限,并不生效.
因为建立本次连接后,用户的权限会被保存在服务器,后续权限修改并不会影响本次连接.下一次连接才能生效.
使用抓包工具抓取一下mysql建立连接的过程
查看mysql的连接数目

mysql> show processlist;
+----+------+-----------------------+------+---------+------+-------+------------------+
| Id | User | Host                  | db   | Command | Time | State | Info             |
+----+------+-----------------------+------+---------+------+-------+------------------+
|  2 | root | DESKTOP-VIMUU8E:64247 | NULL | Query   |    0 | NULL  | show processlist |
|  4 | root | localhost:64503       | NULL | Sleep   |   59 |       | NULL             |
+----+------+-----------------------+------+---------+------+-------+------------------+
2 rows in set (0.00 sec)

其中任意一个客户端(有权限)可以强制断开其它(或自己的)连接,断开后并不会通知那个用户

kill connection+2;

2 用户再次执行查询,先先显示错误信息,然后重新连接

mysql> show processlist;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    5
Current database: *** NONE ***

+----+------+-----------------------+------+---------+------+-------+------------------+
| Id | User | Host                  | db   | Command | Time | State | Info             |
+----+------+-----------------------+------+---------+------+-------+------------------+
|  4 | root | localhost:64503       | NULL | Sleep   |    5 |       | NULL             |
|  5 | root | DESKTOP-VIMUU8E:64594 | NULL | Query   |    0 | NULL  | show processlist |
+----+------+-----------------------+------+---------+------+-------+------------------+
2 rows in set (0.17 sec)

查看mysql的醉的连接数

```mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 100   |
+-----------------+-------+
1 row in set (0.01 sec)

如何解决长连接占用内存的情况
1. 定期断开长连接
2. 客户端主动重置连接
  怎么解决长连接占用内存的问题？

有两种解决方式。

第一种，定期断开长连接。既然断开连接后就会释放连接占用的内存资源，那么我们可以定期断开长连接。

第二种，客户端主动重置连接。MySQL 5.7 版本实现了 mysql_reset_connection() 函数的接口，注意这是接口函数不是命令，那么当客户端执行了一个很大的操作后，在代码里调用 mysql_reset_connection 函数来重置连接，达到释放内存的效果。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态
7. mysql数据包的内容
在这里插入图片描述

查询缓存

在mysql 8.0以前,服务器端接收到刻画段传送来的命令,如果是查询命令,会去查询查询缓存,查询缓存是k-v结构,k为查询命令,如果找到,返回给客户端.
为什么8.0废弃.
因为只要表变动了,关于这个表的所有查询缓存都要删除.如果是经常变动的表,查询缓存命中率很低.

解析sql

先进行词法分析,构建SQL语法树,根据构建的语法树,进行语法分析,根据语法规则,判断输入的SQL语句书否正确.
在这里插入图片描述

执行sql

select语句的执行分为几个阶段
1. prepare阶段–预处理阶段
2. optimize阶段—查询优化阶段
3. execute阶段
prepare阶段做了什么事情(8.0)
1. 检查表是否存在,检查要查询的各列是否在表中实现机制是什么
2. 将select * 中的*转换为列
优化器的作用
查询优化器将SQL语句的执行顺序确定下来,选择成本低的索引.
实验: 不同的查询语句,查看不同的执行计划
创建数据库表插入数据

CREATE DATABASE `xiaolin`; 
CREATE TABLE `xiaolin`.`user`( `id` INT NOT NULL AUTO_INCREMENT, `name` VARCHAR(255), `age` INT, PRIMARY KEY (`id`) );
/*[19:17:47][6 ms]*/ INSERT INTO `xiaolin`.`user` (`id`, `name`, `age`) VALUES ('1', '张三', '10'); 
/*[19:17:58][33 ms]*/ INSERT INTO `xiaolin`.`user` (`name`, `age`) VALUES ('李四', '12');

查看执行计划

mysql> use xiaolin;
Database changed
mysql> explain select * from user where id=1;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | user  | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

可以看到,使用主键作为索引.(因为只有一个索引)
现在把name作为二级索引,并且查询列为主键列(不会进行回表操作)

ALTER TABLE `xiaolin`.`user` ADD INDEX `name_index` (`name`); 
mysql> explain select id from user where id>=1 and name='张三';
+----+-------------+-------+------+--------------------+------------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys      | key        | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+--------------------+------------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | user  | ref  | PRIMARY,name_index | name_index | 768     | const |    1 | Using where; Using index |
+----+-------------+-------+------+--------------------+------------+---------+-------+------+--------------------------+
1 row in set, 1 warning (0.01 sec)

Using index 是使用覆盖索引(要查询的列被使用的索引覆盖)
使用全表扫描的查询

mysql> explain select * from user where age =1;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.01 sec)

执行器的三种执行类型
1. 主键索引查询 (因为主键唯一),执行引擎找到符合条件的第一条记录后,交给执行器,执行期判断是否否和查询条件(因为可能还有其它限制条件),如果符合,返回给客户端,接下来继续执行或终止执行
2. 全表扫描查询
第一条记录–>返回给执行期,符合返回客户端,不符合跳过–>第二条记录
3. 索引下推
第一条记录–>执行引擎判断是否符合其它条件–>符合回表,返回执行器,不符合查询下一条
多加一个判断,减少了回表操作