mysql之一条mysql语句时如何执行的
-
请把连接器的功能说明一下?
连接器负责建立客户端和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)
- 如何解决长连接占用内存的情况
- 定期断开长连接
- 客户端主动重置连接
怎么解决长连接占用内存的问题？
有两种解决方式。
第一种，定期断开长连接。既然断开连接后就会释放连接占用的内存资源，那么我们可以定期断开长连接。
第二种，客户端主动重置连接。MySQL 5.7 版本实现了 mysql_reset_connection() 函数的接口，注意这是接口函数不是命令，那么当客户端执行了一个很大的操作后，在代码里调用 mysql_reset_connection 函数来重置连接，达到释放内存的效果。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态
7. mysql数据包的内容
查询缓存
- 在mysql 8.0以前,服务器端接收到刻画段传送来的命令,如果是查询命令,会去查询查询缓存,查询缓存是k-v结构,k为查询命令,如果找到,返回给客户端.
为什么8.0废弃.
因为只要表变动了,关于这个表的所有查询缓存都要删除.如果是经常变动的表,查询缓存命中率很低.
解析sql
先进行词法分析,构建SQL语法树,根据构建的语法树,进行语法分析,根据语法规则,判断输入的SQL语句书否正确.
执行sql
- select语句的执行分为几个阶段
- prepare阶段–预处理阶段
- optimize阶段—查询优化阶段
- execute阶段
- prepare阶段做了什么事情(8.0)
- 检查表是否存在,检查要查询的各列是否在表中实现机制是什么
- 将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. 索引下推
第一条记录–>执行引擎判断是否符合其它条件–>符合回表,返回执行器,不符合查询下一条
多加一个判断,减少了回表操作