mysql 执行路径 执行计划 慢查询

最新推荐文章于 2023-09-10 16:33:04 发布
最新推荐文章于 2023-09-10 16:33:04 发布
阅读量570 收藏
点赞数 1
分类专栏： mysql 性能优化
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/xiewenfeng520/article/details/100050283
版权

文章目录

mysql 执行路径

系列文章

查询执行的路径

[外链图片转存失败(img-7lZT5vSM-1566615456752)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\1566205549590.png)]
mysql的执行路径大致分为5步

  1. mysql 客户端/服务端通信
  2. 查询缓存
  3. 查询解析，优化处理
  4. 查询执行引擎
  5. 返回客户端

阶段一 mysql 客户端/服务端通信

通信方式是"半双工"

  • 客户端可以发送数据给服务端，服务端也可以发送数据给客户端
  • 不能同时发送，客户端发送的时候，服务端必须等客户端发送完，反之亦然

mysql通信特点和限制

  • 客户端一旦开始发送消息，另一端要接收完整个消息才能响应
  • 客户端一旦开始接收数据没法停下来发送指令

mysql 客户端/服务端的通信-连接状态

对于一个mysql连接，或者说一个线程，时刻都有一个状态来标识这个连接正在做什么

查看命令

show full processlist;
show processlist

常见的状态

Sleep线程正在等待客户端发送数据
Query连接线程正在执行查询
Locked现在正在等待表锁的释放
Sorting result线程正在对结果进行排序
Sending data向请求端返回数据

一般线程状态：全部请见官网

mysql官网一般线程状态

应用

通过show processlist命名查看线程的id/Time/info等信息
在这里插入图片描述

  • time表示当前进程在这个状态的时间

  • info表示执行的sql

  • id可以找到执行很久的sql语句或者死锁的进程，通过kill id(进程号)可以关闭进程，mysql中的kill命令

阶段二 查询缓存

mysql缓存机制是默认关闭

工作原理

  1. 缓存select操作的结果集合sql语句

  2. 新的select语句，先查询缓存，判断是否存在可用的结果集

判断标准

与缓存的sql语句是否完全一样

show VARIABLES like 'query_cache%'

在这里插入图片描述
默认关闭，如果要修改，只能修改my.cnf文件 query_cache_type=1

值意义
1开启，sql语句加上sql_no_cache可以使这条sql语句不使用缓存
0关闭
2按需开启，sql语句加上sql_cache可以使这条sql语句使用缓存

query_cache_limit表示单次查询结果大于这个值，就不会使用缓存

show status like 'Qcache%';

在这里插入图片描述

参数值意义
Qcache_hits命中次数
Qcache_inserts插入缓存次数

不会使用缓存的情况

  • 查询字段中含有函数
  • sql中使用sql_no_cache
  • sql返回结果超过单次最大限制
  • 查询不涉及表
  • 查询的是系统表

阶段三 查询解析，优化处理

  • 解析sql

    通过lex词法分析，yacc语法分析将sql语句解析成解析树

  • 预处理阶段

    根据mysql的语法规则进一步检查解析树的合法性，如检查数据的表和列是否存在，解析名字和别名的设置，进行权限的验证

  • 查询优化器

    优化器的主要作用就是找到最优的执行计划

    查询优化器如何找到最优的执行计划?

    • 基于成本计算的原则。尝试各种执行计划，随机读取一个4K的数据块进行分析

    • 使用等价变化规则

    • 将可转换的外连接转换成内连接

    • 优化count、min、max等函数

    • 覆盖索引扫描

    • 子查询优化

    • 提前终止查询

    • IN的优化（in的条件中的值会采用二分查找进行对比）

    • …

执行计划(重点)
explain select * from eval_leaders t where t.id = 100268;

在sql语句前加explain

或者点击数据库客户端工具中的解释按钮
在这里插入图片描述

每个列的解释
1. id

select查询的序列号，表示执行的顺序

  • id相同，执行顺序由上而下
  • id不同，如果是子查询，id的序号会增大，id值越大优先级越高，越先被执行
  • id相同又不同两种情况同时存在，id如果相同可以认为是一组，从上往下执行；所有组中，id值越大，优先级越高，越先执行
  • id为null，最后执行；select_type类型为UNION RESULT类型
2. select_type

查询的类型，主要是用于区分普通查询、联合查询、子查询等等

类型意义
SIMPLE简单的select查询，查询中不包括或者union
PRIMARY查询中包含子部分，最外层查询则被标记为primary
SUBQUERY表示在select或者where列表中包含了子查询
MATERIALIZEDwhere后面in条件的子查询
UNION若第二个select出现在union之后，则被标记为union
UNION RESULT从union表获取结果的select
3. table

查询涉及的表

直接显示表名或者别名

代码意义
<unionM,N>由ID为M,N查询union产生的结果
< subqueryN>有ID为N查询产生的结果
4. type

访问类型，sql查询优化中一个很重要的指标，结果值从好到坏依次是

system>const>eq_ref>ref>range>index>ALL

代码意义
system表只有一行记录（等于系统表），const类型的特例，基本不会出现
const通过索引依次就找到了，const用于比较primary key或者unique索引
eq_ref唯一索引扫描，对应每个索引键，表中只有一条记录与之匹配。常见primary key或者unique索引
ref非唯一性索引扫描，返回匹配某个单独值的所有行，本质是也是一种索引访问
range只检索给定范围的行，使用一个索引来选择行
indexFull Index Scan，索引全表扫描，把索引从头到尾扫一遍
ALLFull Table Scan，遍历全表以找到匹配行
5. 其他列
列名意义
possible_keys查询过程中有可能用到的索引
key实际使用的索引，如果为null，则没有使用索引
rows根据表统计信息或者索引选用情况，大致估算出找到所需的记录所需要读取的行数
filtered返回结果的行占需要读到行（rows）的百分比，值越大越好
6. Extra

十分重要的额外信息

代码意义
Using filesortmysql对数据使用一个外部文件内容进行排序，而不是按照表内的索引进行排序读取
Using temporary使用临时表保存中间结果，也就是说mysql在对查询结果排序时使用临时表，常见于order by或者group by
Using index表示相应的select操作中使用了覆盖索引（Covering Index），避免了访问表的数据行，效率高
Using where使用了where过来条件
select tables optimized away基于索引优化MIN/MAX操作或者Myisam存储引擎优化count（*）操作，不必等到执行阶段在进行计算，查询执行计划生成的阶段即可完成优化

阶段四 查询执行引擎

调用插件式的存储引擎得到原子API的功能进行执行计划的执行

阶段五 返回客户端

  • 有需要做缓存的，执行缓存操作
  • 增量的返回结果
    • 开始生产第一条结果时，mysql就开始往请求方逐步返回数据
    • 好处：mysql服务器无须保存过多的数据，浪费内存，用户体验好，马上就能拿到数据

如何定位慢sql

  • 业务驱动
  • 测试驱动
  • 慢查询日志

查看慢查询日志参数

show variables like 'slow_query_log%';

在这里插入图片描述
开启

set global slow_query_log = on;
set global slow_query_log_file = '/var/lib/mysql/mysql-slow.log';
#没有命中索引的记录到慢查询
set global log_queries_not_using_indexes = on;
#超过多少秒进入慢查询
set global long_query_time = 0.1;#(秒)

shell命令

vi  /var/lib/mysql/mysql-slow.log

或者是拷贝到本地用文本编辑器打开
在这里插入图片描述

代码意义
Time日志记录的时间
User@Host执行的用户及主机
Query_time查询耗费时间 Lock_time 锁表时间 Rows_sent 发送给请求方的记录条数 Rows_examined 语句扫描的记录条数
SET timestamp语句执行的时间点
select…执行的具体语句

慢查询日志分析工具

  1. mysqldumpslow
    按query_timeo平均查询时间的顺序取出前10条的慢查询日志
    在这里插入图片描述
mysqldumpslow -s at -t 10 /var/lib/mysql/mysql-slow.log

  1. mysqlsla

  2. pt-query-digest

参考

  1. 腾讯课堂-咕泡学院-seven老师-mysql性能优化
  2. mysql官网
mysql 实现最优路径的选择,基于Dijkstra算法思想的实例展示
weixin_42825317的博客
07-31 569
任务需求：给定两个节点，按路线从一个节点到另一个节点，请给出最优路线。 文章目录 前言 一、pandas是什么？ 二、使用步骤 1.引入库 2.读入数据 总结 前言 Dijkstra(迪杰斯特拉)算法是典型的单源最短路径算法，用于计算一个节点到其他所有节点的最短路径。主要特点是以起始点为中心向外层层扩展，直到找到目标终点为止。 一、实现概要 主要通过建立3个数据表来实现，表`pb_city_neb1`用于存放各个节点与其相连的节点；表`pd_...
mysql 慢查询日志路径_mysql的慢查询日志
weixin_29214559的博客
02-01 2485
慢查询日志概念MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10S以上的语句。默认情况下，Mysql数据库并不启动慢查询日志，需要我们手动来设置这个参数，当然，如果不是调优需要的话，一般不建议启动该参数...
mysql查询执行路径
lighter613的博客
12-04 390
查询执行路径 1.客户端发送一条查询给服务器 2.服务器先检查查询缓存,如果命中了缓存,则立刻返回存储再缓存中的结果.否则进入下一步阶段. 3.服务器端进行SQL解析,预处理,再由优化器生成对应的执行计划 4.MySQL根据优化器生成的执行计划,调用存储引擎的API来执行查询 5.将结果返回给客户端 ...
MYSQL的慢查询
最新发布
qq_63431773的博客
09-10 673
通过查询SQL的执行频次，我们就能够知道当前数据库到底是增删改为主，还是查询为主。那假如说是以查询为主，次数我们可以借助于慢查询日志。接下来，我们就来介绍一下MySQL中的慢查询日志。慢查询日志记录了所有执行时间超过指定参数（，单位：秒，默认10秒）的所有SQL语句的日志。
mysql慢查询定位 执行计划分析 sql执行顺序
chudaxiakkk的专栏
09-03 203
1.慢查询定位： mysql已经内置了慢查询记录功能。默认关闭，需要打开的话，通过系统参数配置。 1.修改配置文件my.ini, [mysqld] log_output='FILE,TABLE' slow_query_log='ON' long_query_time=0.001 2.命令设置，但是mysql重启后会丢失该配置 SET GLOBAL slow_query_log = 'ON'; SET GLOBAL log_output = 'FILE,TABLE'; SET GLOBAL long_qu
mysql可执行程序在什么路径_MySQL的可执行程序在什么路径里？
weixin_39691233的博客
01-21 376
【判断题】文字工具输入的文字，只能作为放样的路径，而不能作为放样的截面。×【单选题】类风湿结节【单选题】Let me introduce the new professor to you. ________【单选题】下列哪一条体育锻炼的原则是错误的【判断题】骨骼系统在缺省情况下即可被直接渲染。【单选题】MySQL的可执行程序在什么路径里？【判断题】可以向已经存在的组中增加对象。【判断题】安全距离是...
MySQL执行计划的深入分析
09-09
通过查看执行计划，我们可以识别慢查询的问题所在，如不必要的全表扫描、缺少索引或使用了低效的连接策略。然后，我们可以通过创建合适的索引、调整查询结构或改变表设计来提高查询速度。 总之，深入理解MySQL执行...
Mysql 服务 1067 错误 的解决方法:修改mysql可执行文件路径
01-19
设置使用系统账户也无法启动mysql，后面认证看了系统的配置信息，发现启动文件也就是mysql安装路径是之前的（也说明之前安装mysql，没去卸载直接安装新的会出错），于是打算修改修改mysql可执行文件路径，换成现在的...
根据mysql慢日志监控SQL语句执行效率
09-11
1. `log-slow-queries=/path/slow.log`：指定慢查询日志的保存路径，你需要创建这个文件，并确保MySQL服务有写入权限。 2. `long_query_time=2`：设置慢查询的时间阈值，单位为秒，这里设置为2秒，意味着所有运行...
Mysql中 查询慢的 Sql语句的记录查找
热门推荐
gymaisyl的博客
11-16 1万+
慢查询日志 slow_query_log，是用来记录查询比较慢的sql语句，通过查询日志来查找哪条sql语句比较慢，这样可以对比较慢的sql可以进行优化。 1. 登陆我们的mysql数据库： 2. 查看一下当前的慢查询是否开启，以及慢查询所规定的时间： show variables like 'slow_query_log'; show variables like 'long_query_t...
mysql怎么查询慢查询，及杀死对应进
qq_41081716的博客
07-17 2074
命令需要有足够的权限，通常只有管理员或具有特定权限的用户才能执行此操作。通过以上步骤，你可以查询慢查询并杀死对应的进程，以帮助调优和管理 MySQL 数据库的性能。参数设置查询执行时间的阈值。慢查询日志记录了执行时间超过阈值的查询语句。这将显示当前正在执行的查询进程列表，其中包括每个进程的 ID。这将显示慢查询日志中的查询语句、执行时间等信息。在 MySQL 中，可以通过设置。参数来启用慢查询日志，并配置。替换为要杀死的进程的 ID。
mysql 查看慢查询中执行慢的记录
放弃很容易，坚持很难。
04-23 106
mysql 分析慢查询中执行慢的记录
知识点
weixin_33810302的博客
01-07 77
数据结构与算法 PHP 数据库 MySQL MySQL查询执行路径 Redis Web Server http|https|http2 Https协议详解
Mysql——》慢查询
小仙~
05-01 317
参考链接：   官网Slow Query Log 1、慢查询日志的开关 -- slow_query_log：慢查询开关（on：开启，off：关闭，默认：关闭） -- slow_query_log_file：慢查询日志的文件存放路径 show variables like 'slow_query%'; -- 动态修改参数（重启后失效）1 开启，0 关闭 set @@global.slow_query_log=1; 2、慢查询的时间 -- 默认：10s show variables like '%l.
mysql数据库--数据路径、配置文件、慢查询
choumianshan2269的博客
07-28 316
linux中mysql的安装、数据、配置文件路径： /usr/ ：安装地址 /usr/lib/mysql :默认数据库地址，可改动到 /data/mysql（需要同时修改配置文件中[mysqld]参数datadir=/data/mysql、socket=/data/mysql/mysql....
编写的SQL太慢？快来看看MySQL的优化之执行计划（explain）
Hicodden的博客
07-02 414
MySQL的架构图 连接层 服务层 引擎层 MyISAM 和 InnoDB引擎的特性介绍 对比项 MyISAM InnoDB 主外键 不支持 支持 事务 不支持 支持 锁 支持表锁，操作一条记录的就会锁住曾哥表，不适合高并发的操作 支持行锁，只锁定操作该行的数据，不对影响其他行的操作，适合高并发操作 缓存 只支持缓存索引，不支持缓存真实数据 支持缓存索引和真实数据，对内存要求高，而且内存大小对性能有决定性的影响 表空间 小 大 关注点 性能 事务 默认安装 是 是
mysql 简单定位慢查询并分析SQL执行效率
wuyundong123的博客
06-26 3316
日常项目开发过程中慢sql定位和执行效率分析
Mysql - 慢查询和执行计划
it_lihongmin的博客
04-07 630
慢查询的相关参数设置： long_query_time： 当查询时间超过多少秒才会被记录为慢查询，默认值为 10，可以设置为 0~10，可以指定设置为微秒； slow_query_log： 慢查询日志开关，默认值是 0 表示关闭， 设置为 1 表示开启； slow_query_log_file：host_name-slow.log，定义慢日志的保存地址，默认是主机名-slow.log；
mysql 语句效率监控
博“文”强识
05-19 186
explain select surname,first_name form a,b where a.id=b.id EXPLAIN列的解释： table：显示这一行的数据是关于哪张表的 type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL possible_keys：显示可能应用在这张表中的索引。...
MySQL执行计划详解：洞察查询内部机制
**MySQL执行计划的调用方式** 1. 使用`EXPLAIN SELECT…`命令可以查看查询的执行计划。这是最基础的调用方式，它会显示查询的执行步骤和顺序。 2. `EXPLAIN EXTENDED SELECT…`不仅显示执行计划，还会将查询语句“反...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值