mysql基础问题三问(底层逻辑;正在执行;日志观察) - Mrwhite86
source link: https://www.cnblogs.com/mrwhite2020/p/16812199.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
mysql基础问题三问(底层逻辑;正在执行;日志观察)
背景:经常面试会遇到且实际工作中也会应用到的三个场景:
- - - - - - - - - -分割线- - - - - - - - - - -
一.mysql查询时的底层原理是什么?
mysql底层使用的组件主要分别两个部分:包含Server层与Store层
Server层
主要包括连接器、查询缓存、分析器、优化器、执行器等,涵盖 MySQL 的大多数核心服务功能,以及所有的内置函数(如日期、时间、数学和加密函数等),所有跨存储引擎的功能都在这一层实现,比如存储过程、触发器、视图等。
Store层
存储引擎层负责数据的存储和提取。其架构模式是插件式的,支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB,它从 MySQL 5.5.5 版本开始成为了默认存储引擎。也就是说如果我们在create table时不指定表的存储引擎类型,默认会给你设置存储引擎为InnoDB。
1.连接器:
使用各种客户端连接mysql时会使用到连接器,这些客户端要向mysql发起通信都必须先跟Server端建立通信连接,而建立连接的工作就是有连接器完成的。
连接器负责跟客户端建立连接、获取权限、维持和管理连接。连接命令一般是这么写的:
[root@192 ~]# mysql -h host[数据库地址] -u root[用户] -p root[密码] -P 3306
mysql -u root -p
连接器就要开始认证你的身份,这个时候用的就是你输入的用户名和密码;如果用户名或密码不对,你就会收到一个"Access denied for user"的错误,然后客户端程序结束执行
连接器会到权限表里面查出你拥有的权限。之后,这个连接里面的权限判断逻辑,都将依赖于此时读到的权限,连接成功如下
show grants for root@"%"; 查看当前用户的权限
MySQL 拿到一个查询请求后,会先到查询缓存看看,之前是不是执行过这条语句。之前执行过的语句及其结果可能会以 key-value 对的形式,被直接缓存在内存中。key 是查询的语句,value 是查询的结果。如果你的查询能够直接在这个缓存中找到 key,那么这个 value 就会被直接返回给客户端。
查看当前mysql实例是否开启缓存机制:
mysql> show global variables like "%query_cache_type%";
监控查询缓存信息:
mysql> show status like'%Qcache%'; //查看运行的缓存信息
如果语句不在查询缓存中,就会继续后面的执行阶段。执行完成后,执行结果会被存入查询缓存中。你可以看到,如果查询命中缓存,MySQL 不需要执行后面的复杂操作,就可以直接返回结果,这个效率会很高建议大家在静态表里使用查询缓存,什么叫静态表呢?就是一般我们极少更新的表。比如,一个系统配置表、字典表,那这张表上的查询才适合使用查询缓存。好在 MySQL 也提供了这种“按需使用”的方式。你可以将my.cnf参数 query_cache_type 设置成 DEMAND。
my.cnf #query_cache_type有3个值 0代表关闭查询缓存OFF,1代表开启ON,2(DEMAND)代表当sql语句中有SQL_CACHE关键词时才缓存 query_cache_type=2
2.分析器
分析器分成6个步骤:
(1)词法分析 (2)语法分析 (3)语义分析 (4)生成执行树 (5)生成执行计划(6)计划执行
3.优化器
优化器决定了要使用的索引,各个表的连接顺序
(1)调用 InnoDB 引擎接口取这个表的第一行,判断 ID 值是不是 10,如果不是则跳过,如果是则将这行存在结果集中;
(2)调用引擎接口取“下一行”,重复相同的判断逻辑,直到取到这个表的最后一行。
(3)执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。
配置开启binlog log-bin=/usr/local/mysql/data/binlog/mysql-bin 注意5.7以及更高版本需要配置本项:server-id=123454(自定义,保证唯一性); #binlog格式,有3种statement,row,mixed binlog-format=ROW #表示每1次执行写入就与硬盘同步,会影响性能,为0时表示,事务提交时mysql不做刷盘操作,由系统决定 sync-binlog=1 复制代码
mysql> show variables like '%log_bin%'; 查看bin-log是否开启 mysql> flush logs; 会多一个最新的bin-log日志 mysql> show master status; 查看最后一个bin-log日志的相关信息 mysql> reset master; 清空所有的bin-log日志
二.如何查看正在执行的mysql语句?
首先:processlist表位于information_schema库中,主要是存储的MySQL线程的一些基本信息。我们使用
desc information_schema.processlist来查看表结构:
ID: 线程的id
USER: 线程属于哪一个用户
HOST:客户端的host信息:hostname+端口
DB:线程在哪一个数据库下
COMMAND:线程使用哪一种命令在执行,空闲的线程状态为sleep
TIME:线程已经运行的时间,秒为单位
STATE:线程正在做什么:当前的状态,行为,或者事件
INFO:线程正在执行的语句,但是这个并不是很准确,所以需要使用其他的方式来完成我们的目标。
使用show processlist 或者 select * from information_schema.processlist查看processlist表
并且可以直接kill掉卡死的进程信息
show processlist
select * from information_schema.processlist
1.拿到正在执行的processlist_id
select id from information_schema.processlist
2.拿到与processlist_id对应的thread_id
select thread_id from performance_schema.threads where processlist_id in (上一步拿到的processlist_id列表)
3.拿到正在执行的sql语句
select thread_id, sql_text from performance_schema.events_statements_current where thread_id in (上一步拿到的thread_id列表)
4.完整的sql语句如下:
SELECT a.*, c.thread_id, c.sql_text from information_schema.processlist a LEFT JOIN performance_schema.threads b on a.id = b.PROCESSLIST_ID LEFT JOIN performance_schema.events_statements_current c on c.THREAD_ID = b.THREAD_ID;
三.如何观察mysql运行过程中的日志信息?
查看主要之前说bin-log日志信息。
1.查看是否启用了日志:
show variables like 'log_bin';
2.查看当前的日志
show master status;
3.查看日志文件内容:
mysqlbinlog binlog.000029
4.mysql有以下几种日志:
错误日志: -log-err
查询日志: -log
慢查询日志: -log-slow-queries
更新日志: -log-update
二进制日志: -log-bin
- - - - - - - - - -分割线- - - - - - - - - - -
参考文档:
MySQL底层一条SQL语句是如何执行的:https://www.cnblogs.com/Maggies/p/16457105.html
mysql-SQL底层执行原理详解:https://blog.csdn.net/qq_21575929/article/details/122153353
MySQL查看正在运行的SQL:https://blog.csdn.net/weixin_40920359/article/details/125992280
Recommend
-
3
导语摩根士丹利:我们正站在中国经济和资本市场一个重要的转折点上。新目标带来的经济底层逻辑的重置以对互联网巨头监管为标志,中国正在转换经济发展的底层逻辑:从增速优先转向兼顾公平,对于部分高垄断---进而带来高ROE---行业可能...
-
6
事实、观点、立场和信仰 一个人的表述,通常分为两种:事实和观点;但细究起来,还可以细分为四种:事实、观点、立场和信仰 # 事实 事实,是独立于人的判断的客观存在 比如,“今天很热”,这样的...
-
8
flask实现python方法转换服务 一.flask安...
-
3
windows10:vscode下go语言的适配 ps:...
-
4
GIt后悔药:还原提交的操作(谨慎操作)
-
10
python数据精度问题 一、python运算时精度...
-
2
liunx标准输入与输出 一、Linux提供了三种...
-
6
postman7种断言的使用 导航:
-
4
liunx的三个时间atime,mtime,ctime详细说明与使用场景 ...
-
7
liunx文件定期本地备份、异地备份、删除备份脚本 ...
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK