MySQL篇—执行计划介绍(第二篇,总共三篇)

2024-03-02 09:04

本文主要是介绍MySQL篇—执行计划介绍(第二篇,总共三篇),希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

☘️博主介绍☘️

✨又是一天没白过,我是奈斯,DBA一名✨

✌️擅长Oracle、MySQL、SQLserver、Linux,也在积极的扩展IT方向的其他知识面✌️

❣️❣️❣️大佬们都喜欢静静的看文章,并且也会默默的点赞收藏加关注❣️❣️❣️

    士别三日,自上次分享以来,大家应该对上篇的文章内容进行了深入消化与理解。今天给大家带来第二篇的内容——执行计划。在上篇文章中我们有了解到optimizer优化器根据统计信息对每个sql语句执行最优的执行计划(执行计划受统计信息影响)。并且通过执行计划能够帮助我们了解数据库在执行查询时采用的具体策略、使用的索引以及各种操作的执行顺序等信息,因此对于SQL查询的优化非常重要。

    因为统计信息和执行计划涉及到的内容过多,为了使大家更好消化,我将分成三篇文章来进行介绍,以便大家因为篇幅过长而感到阅读疲惫。三篇的内容分别如下,让大家先做了解:

第一篇:持久化和非持久化统计信息介绍

第二篇:执行计划介绍(当前篇)

第三篇:执行计划之覆盖索引Using index和条件过滤Using where详细介绍


目录

查看SQL的执行计划

explain语法一:explain + SQL语句(默认FORMAT = TRADITIONAL输出格式为表格)

explain语法二:explain FORMAT = JSON + SQL语句

explain语法三:explain FORMAT = TREE + SQL语句

explain语法四:explain analyze + SQL语句


废话不多说,让我们开始今天的内容。

    MySQL中的SQL执行计划能够帮助我们了解数据库在执行查询时采用的具体策略、使用的索引以及各种操作的执行顺序等信息,因此对于SQL查询的优化非常重要。下面是SQL执行计划在SQL查询优化中的作用:

1)评估查询性能:SQL 执行计划可以让我们了解到 MySQL 在执行查询时所采用的具体策略和每个步骤所需的时间,从而评估查询的性能表现。比如,我们可以查看每个操作使用的索引类型或临时表的创建情况,有助于我们确定查询是否需要进行优化,以及应该优化哪些部分。

2)定位性能问题:如果SQL查询执行缓慢,我们可以通过 SQL 执行计划来定位性能问题所在。例如,我们可以查看查询语句中是否存在不必要的排序、全表扫描、临时表创建等问题,从而确定性能瓶颈并进行调整。

3)判断索引是否有效:SQL 执行计划可以让我们了解到 MySQL 是否使用了正确的索引来执行查询,进而判断我们为表设置的索引是否有效。如果 MySQL 没有使用索引,那么可能是我们设置的索引有问题,需要重新考虑索引的创建方式。

4)选择正确的查询方案:在SQL查询优化中,有时候我们需要选择不同的查询方案来完成同样的查询操作。SQL 执行计划可以让我们了解到MySQL计划使用哪种查询方案,并可以根据不同的情况调整查询方案或者SQL语句结构。

总之,SQL 执行计划是 SQL 查询优化的重要工具,可以帮助我们找到问题所在,优化查询性能并提高数据库的运行效率。

              

查看SQL的执行计划

    通过explain查看执行计划的方式有多种,今天主要是详细介绍语法一,其他的方式我这里不多做介绍,因为都是大同小异,有兴趣的小伙伴可以私信我。

EXPLAIN语法:

官方文档对EXPLAIN的介绍:MySQL :: MySQL 8.0 Reference Manual :: 15.8.2 EXPLAIN Statement

{EXPLAIN | DESCRIBE | DESC}

    tbl_name [col_name | wild]

{EXPLAIN | DESCRIBE | DESC}

    [explain_type]

    {explainable_stmt | FOR CONNECTION connection_id}

{EXPLAIN | DESCRIBE | DESC} ANALYZE [FORMAT = TREE] select_statement

explain_type: {

    FORMAT = format_name

}

format_name: {

  | TRADITIONAL

  | JSON

  | TREE

}

explainable_stmt: {

  | SELECT statement

  | TABLE statement

  | DELETE statement

  | INSERT statement

  | REPLACE statement

  | UPDATE statement

}

EXPLAIN [options] FOR CONNECTION connection_id:获取在命名连接中执行的可解释语句的执行计划。意思就是在另一个会话上去查看其他会话正在执行SQL的执行计划,通常的做法是在另一个会话上输入show porcesslist(或者其他查询SQL的语句),有正在执行的SQL那么通过EXPLAIN [options] FOR CONNECTION加上show processlist输出的ID,那么可以看到相关SQL的执行计划。

FORMAT = format_name:选项可用于选择输出格式。默认以表格格式显示输出(FORMAT = TRADITIONAL)。可以指定其他输出格式,JSON格式以JSON格式显示信息(FORMAT = JSON)。在MySQL 8.0.16及更高版本中,TREE提供了树状输出(FORMAT = TREE),比传统格式更精确地描述了查询处理,并且它是唯一显示哈希连接用法的格式,和EXPLAIN ANALYZE输出的内容大致相同。在MySQL 8.0.32中添加的explain_format系统变量在用于获取表列信息时,影响对explain的输出,参数的值包括TRADITIONAL (DEFAULT)、JSON、TREE。

explainable_stmt:EXPLAIN适用于SELECT、DELETE、INSERT、REPLACE和UPDATE语句的执行计划的解析。在MySQL 8.0.19及更高版本中,它还可以使用TABLE语句,TABLE语句是MySQL 8.0.19中引入的DML语句,它返回指定表的行和列,和SELECT查询表有些类似,但功能又没SELECT多。

注意:SQL语句加上explain不会真正执行SQL语句,它仅会模拟MySQL在执行该语句时所做的操作,并返回MySQL在执行该语句时使用的查询计划信息。

            

explain语法一:explain + SQL语句(默认FORMAT = TRADITIONAL输出格式为表格)

mysql> explain select * from tb t1 join tb2 t2 on t1.id=t2.id;

需要特别关注的字段type、possible_keys、key、key_len、ref、rows、Extra。官方文档解释输出列:MySQL :: MySQL 8.0 Reference Manual :: 10.8.2 EXPLAIN Output Format

id(JSON名称:select_id):SELECT标识符。这是查询中SELECT的序列号。如果该行引用其他行的并集结果,则该值可以为NULL。在这种情况下,表列显示一个类似于<union M,N>的值,表示该行指的是id值为M和N的行的并集。

select_type(JSON名称:无):SELECT的类型JSON格式的EXPLAIN将SELECT类型公开为query_block的属性,除非它是SIMPLE或PRIMARY。类型比较多,参考官方文档。

table(JSON名称:table_name)输出行所引用的表的名称。

partitions(JSON名称:partitions查询将从中匹配记录的分区。对于未分区的表该值为NULL。

type(JSON名称:access_type)联接类型。类型比较多,参考官方文档。

possible_keys(JSON名称:possible_ keys):possible_keys列表示MySQL可以从中选择查找该表中的行的索引。请注意,此列完全独立于EXPLAIN输出中显示的表的顺序。这意味着possible_keys中的一些键在实际中可能无法使用生成的表顺序。如果此列为NULL(或在JSON格式的输出中未定义),则不存在相关索引。在这种情况下,您可以通过检查WHERE子句来检查它是否引用了适合进行索引的一个或多个列,从而提高查询的性能。如果是,请创建一个适当的索引ALTER TABLE语句,并再次使用EXPLAIN检查查询。要查看表的索引,请使用SHOW INDEX FROM tbl_name。

keyJSON名称key):这key列表示MySQL实际决定使用的键(索引)。如果MySQL决定使用possible_keys索引来查找行,该索引被列为键值。有可能key可以命名一个不在possible_keys价值。如果没有一个possible_keys索引适合于查找行,但是查询选择的所有列都是其他索引的列。也就是说,命名索引覆盖了所选的列,因此尽管它不用于确定要检索哪些行,但索引扫描比数据行扫描更有效。

为InnoDB,即使查询也选择了主键,辅助索引也可能会覆盖选定的列,因为InnoDB存储每个辅助索引的主键值。如果key是NULL,MySQL找不到索引来更有效地执行查询。要强制MySQL使用或忽略possible_keys列,使用FORCE INDEX, USE INDEX,或者IGNORE INDEX在您的查询中。看见第8.9.4节,“索引提示”.

为MyISAM表格,运行ANALYZE TABLE帮助优化器选择更好的索引。为MyISAM表格,myisamchk -分析做同样的事。看见13.7.3.1,“分析表语句”一节,以及第7.6节,“MyISAM表维护和故障恢复”.

key_len(JSON名称:key_length):key_len列表示MySQL决定使用的密钥的长度。key_len的值使您能够确定MySQL实际使用多部分密钥的多少部分。如果key列表示NULL,那么key_len列也表示NULL。由于密钥存储格式的原因,可以为NULL的列的密钥长度比NOT NULL列的密钥长一个。

ref(JSON名称:ref):ref列显示将哪些列或常量与键列中命名的索引进行比较,以便从表中选择行。如果该值是func,则使用的值是某个函数的结果。要查看哪个函数,请在EXPLAIN后面使用SHOW WARNINGS来查看扩展的EXPLAIN输出。函数实际上可能是一个运算符,例如算术运算符。

rows(JSON名称:rowsrows列表示MySQL认为执行查询必须检查的行数。这里的行数和自动更新持久化统计信息是一致的,所以会出现与实际count(*)数据量差距较大,可以这个文档的2、统计信息的案例“(6)解决统计信息差别较大的问题(执行计划受统计信息影响,统计信息不准会导致执行计划不准)”

filtered(JSON名称:filtered)已筛选列表示按表条件筛选的表行的估计百分比。最大值为100,这意味着没有对行进行筛选。从100开始递减的值表示过滤量的增加。rows显示检查的估计行数,rows×filtered显示与下表连接的行数。例如,如果行数为1000,过滤后的行数为50.00(50%),则与下表连接的行数是1000×50%=500。

Extra(JSON名称:无):本列包含有关MySQL如何解析查询的其他信息没有一个JSON属性对应于Extra列;但是,此列中可能出现的值将作为JSON属性或消息属性的文本公开。类型比较多,参考官方文档。

                  

explain语法二:explain FORMAT = JSON + SQL语句

mysql> explain FORMAT = JSON select * from tb t1 join tb2 t2 on t1.id=t2.id\G;

            

explain语法三:explain FORMAT = TREE + SQL语句

mysql> explain FORMAT = TREE select * from tb t1 join tb2 t2 on t1.id=t2.id\G;

               

explain语法四:explain analyze + SQL语句

mysql> explain analyze select * from tb t1 join tb2 t2 on t1.id=t2.id\G;

    今天执行计划的内容就介绍到这里,只是对执行计划输出的内容做了介绍,下一篇我会用实例执行的执行计划的案例来介绍。

这篇关于MySQL篇—执行计划介绍(第二篇,总共三篇)的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!


原文地址:
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.chinasem.cn/article/765605

相关文章

MySQL双主搭建+keepalived高可用的实现

《MySQL双主搭建+keepalived高可用的实现》本文主要介绍了MySQL双主搭建+keepalived高可用的实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,... 目录一、测试环境准备二、主从搭建1.创建复制用户2.创建复制关系3.开启复制,确认复制是否成功4.同

MyBatis 动态 SQL 优化之标签的实战与技巧(常见用法)

《MyBatis动态SQL优化之标签的实战与技巧(常见用法)》本文通过详细的示例和实际应用场景,介绍了如何有效利用这些标签来优化MyBatis配置,提升开发效率,确保SQL的高效执行和安全性,感... 目录动态SQL详解一、动态SQL的核心概念1.1 什么是动态SQL?1.2 动态SQL的优点1.3 动态S

Mysql表的简单操作(基本技能)

《Mysql表的简单操作(基本技能)》在数据库中,表的操作主要包括表的创建、查看、修改、删除等,了解如何操作这些表是数据库管理和开发的基本技能,本文给大家介绍Mysql表的简单操作,感兴趣的朋友一起看... 目录3.1 创建表 3.2 查看表结构3.3 修改表3.4 实践案例:修改表在数据库中,表的操作主要

mysql出现ERROR 2003 (HY000): Can‘t connect to MySQL server on ‘localhost‘ (10061)的解决方法

《mysql出现ERROR2003(HY000):Can‘tconnecttoMySQLserveron‘localhost‘(10061)的解决方法》本文主要介绍了mysql出现... 目录前言:第一步:第二步:第三步:总结:前言:当你想通过命令窗口想打开mysql时候发现提http://www.cpp

MySQL大表数据的分区与分库分表的实现

《MySQL大表数据的分区与分库分表的实现》数据库的分区和分库分表是两种常用的技术方案,本文主要介绍了MySQL大表数据的分区与分库分表的实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有... 目录1. mysql大表数据的分区1.1 什么是分区?1.2 分区的类型1.3 分区的优点1.4 分

MySQL错误代码2058和2059的解决办法

《MySQL错误代码2058和2059的解决办法》:本文主要介绍MySQL错误代码2058和2059的解决办法,2058和2059的错误码核心都是你用的客户端工具和mysql版本的密码插件不匹配,... 目录1. 前置理解2.报错现象3.解决办法(敲重点!!!)1. php前置理解2058和2059的错误

Mysql删除几亿条数据表中的部分数据的方法实现

《Mysql删除几亿条数据表中的部分数据的方法实现》在MySQL中删除一个大表中的数据时,需要特别注意操作的性能和对系统的影响,本文主要介绍了Mysql删除几亿条数据表中的部分数据的方法实现,具有一定... 目录1、需求2、方案1. 使用 DELETE 语句分批删除2. 使用 INPLACE ALTER T

MySQL INSERT语句实现当记录不存在时插入的几种方法

《MySQLINSERT语句实现当记录不存在时插入的几种方法》MySQL的INSERT语句是用于向数据库表中插入新记录的关键命令,下面:本文主要介绍MySQLINSERT语句实现当记录不存在时... 目录使用 INSERT IGNORE使用 ON DUPLICATE KEY UPDATE使用 REPLACE

MySQL Workbench 安装教程(保姆级)

《MySQLWorkbench安装教程(保姆级)》MySQLWorkbench是一款强大的数据库设计和管理工具,本文主要介绍了MySQLWorkbench安装教程,文中通过图文介绍的非常详细,对大... 目录前言:详细步骤:一、检查安装的数据库版本二、在官网下载对应的mysql Workbench版本,要是

mysql数据库重置表主键id的实现

《mysql数据库重置表主键id的实现》在我们的开发过程中,难免在做测试的时候会生成一些杂乱无章的SQL主键数据,本文主要介绍了mysql数据库重置表主键id的实现,具有一定的参考价值,感兴趣的可以了... 目录关键语法演示案例在我们的开发过程中,难免在做测试的时候会生成一些杂乱无章的SQL主键数据,当我们