MySQL执行计划解析

本文涉及的产品
对象存储 OSS,20GB 3个月
云备份 Cloud Backup,100GB 3个月
阿里云盘企业版 CDE,企业版用户数5人 500GB空间
简介: 前言 在实际数据库项目开发中,由于我们不知道实际查询时数据库里发生了什么,也不知道数据库是如何扫描表、如何使用索引的,因此,我们能感知到的就只有SQL语句的执行时间。尤其在数据规模比较大的场景下,如何写查询、优化查询、如何使用索引就显得很重要了。

前言

在实际数据库项目开发中,由于我们不知道实际查询时数据库里发生了什么,也不知道数据库是如何扫描表、如何使用索引的,因此,我们能感知到的就只有SQL语句的执行时间。尤其在数据规模比较大的场景下,如何写查询、优化查询、如何使用索引就显得很重要了。

那么,问题来了,在查询前有没有可能估计下查询要扫描多少行、使用哪些索引呢?

答案是肯定的。以MySQL为例,MySQL通过explain命令输出执行计划,对要执行的查询进行分析。

什么是执行计划呢?

简单来说,就是SQL在数据库中执行时的表现情况,通常用于SQL性能分析、优化等场景。

本文从MySQL的逻辑结构讲解,过渡到MySQL的查询过程,然后给出执行计划的例子并重点介绍执行计划的输出参数,从而理解为什么我们会选择文中建议的方案。

MySQL逻辑架构

MySQL逻辑架构分为三层,如下图。

mysql_arch

  • 客户端

    • 如,连接处理、授权认证、安全等功能
  • 核心服务

    • MySQL大多数核心服务均在这一层
    • 包括查询解析、分析、优化、缓存、内置函数(如,时间、数学、加密等)
    • 所有的跨存储引擎的功能也在这一层,如,存储过程、触发器、视图等
  • 存储引擎

    • 负责MySQL中的数据存储和读取
    • 中间的服务层通过API与存储引擎通信,这些API屏蔽了不同存储引擎间的差异

重点解释下查询缓存:对于select语句,在解析查询之前,服务器会先检查查询缓存(Query Cache)。如果命中,服务器便不再执行查询解析、优化和执行的过程,而是直接返回缓存中的结果集。

MySQL查询过程

如果能搞清楚MySQL是如何优化和执行查询的,对优化查询一定会有帮助。很多查询优化实际上就是遵循一些原则让优化器能够按期望的合理的方式运行。

下图是MySQL执行一个查询的过程。实际上每一步都比想象中的复杂,尤其优化器,更复杂也更难理解。本文只给予简单的介绍。

mysql_query

MySQL查询过程如下:

  • 客户端将查询发送到MySQL服务器
  • 服务器先检查查询缓存,如果命中,立即返回缓存中的结果;否则进入下一阶段
  • 服务器对SQL进行解析、预处理,再由优化器生成对象的执行计划
  • MySQL根据优化器生成的执行计划,调用存储引擎API来执行查询
  • 服务器将结果返回给客户端,同时缓存查询结果

执行计划

优化与执行

MySQL会解析查询,并创建内部数据结构(解析树),并对其进行各种优化,包括重写查询、决定表的读取顺序、选择合适的索引等。

用户可通过关键字提示(hint)优化器,从而影响优化器的决策过程。也可以通过通过优化器解释(explain)优化过程的各个因素,使用户知道数据库是如何进行优化决策的,并提供一个参考基准,便于用户重构查询和数据库表的schema、修改数据库配置等,使查询尽可能高效。

例子

看个例子。

mysql> explain select name, nickname, ctime from dt_user where city = 'shanghai' order by name;
+----+-------------+------------+-------+--------------------------+---------------+---------+--------+---------+-----------------------+
| id | select_type | table      | type  | possible_keys            | key           | key_len | ref    | rows    | Extra                 |
+----+-------------+------------+-------+--------------------------+---------------+---------+--------+---------+-----------------------+
|  1 | SIMPLE      | dt_user    | range | PRIMARY,idx_city_name    | idx_city_name | 2945    | NULL   | 55183   | Using index condition |
+----+-------------+------------+-------+--------------------------+---------------+---------+--------+---------+-----------------------+
1 row in set (0.00 sec)

这个执行计划给出的信息是,该查询通过一个简单的给定范围的扫描,共扫描55183行,使用index condition条件在dt_user表中筛选出,扫描过程中使用PRIMARY和idx_city_name索引。

输出参数

输出各字段解释如下。更详细的信息请参考https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

  • id

    • select查询序列号
    • id相同,执行顺序由上至下;id不同,id值越大优先级越高,越先被执行
  • select_type
    查询数据的操作类型,有如下

explain_1

  • table
    显示该行数据是关于哪张表
  • partitions
    匹配的分区
  • type
    表的连接类型,其值、性能由高到底排列如下

explain_type

前5种情况都是理想的索引的情况。通常优化至少到range级别,最好能优化到ref。

  • possible_keys
    指出 MySQL 使用哪个索引在该表找到行记录。如果该值为 NULL,说明没有使用索引,可以建立索引提高性能
  • key
    显示 MySQL 实际使用的索引。如果为 NULL,则没有使用索引查询
  • key_len
    表示索引中使用的字节数,通过该列计算查询中使用的索引的长度。在不损失精确性的情况下,长度越短越好显示的是索引字段的最大长度,并非实际使用长度
  • ref
    显示该表的索引字段关联了哪张表的哪个字段
  • rows
    根据表统计信息及选用情况,大致估算出找到所需的记录或所需读取的行数,数值越小越好
  • filtered
    返回结果的行数占读取行数的百分比,值越大越好
  • extra
    包含不适合在其他列中显示但十分重要的额外信息。常见的值如下

explain_3

小结

数据库性能优化很多,本文只简单了介绍MySQL逻辑结构、查询过程和执行计划参数。根据执行计划输出的索引使用情况、扫描的行数可以预估查询效率,帮助我们重构查询、优化表结构或者索引,从而尽可能提供查询效率。

Reference

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
2月前
|
SQL 关系型数据库 MySQL
MySQL进阶突击系列(07) 她气鼓鼓递来一条SQL | 怎么看执行计划、SQL怎么优化?
在日常研发工作当中,系统性能优化,从大的方面来看主要涉及基础平台优化、业务系统性能优化、数据库优化。面对数据库优化,除了DBA在集群性能、服务器调优需要投入精力,我们研发需要负责业务SQL执行优化。当业务数据量达到一定规模后,SQL执行效率可能就会出现瓶颈,影响系统业务响应。掌握如何判断SQL执行慢、以及如何分析SQL执行计划、优化SQL的技能,在工作中解决SQL性能问题显得非常关键。
|
4月前
|
SQL 关系型数据库 MySQL
深入解析MySQL的EXPLAIN:指标详解与索引优化
MySQL 中的 `EXPLAIN` 语句用于分析和优化 SQL 查询,帮助你了解查询优化器的执行计划。本文详细介绍了 `EXPLAIN` 输出的各项指标,如 `id`、`select_type`、`table`、`type`、`key` 等,并提供了如何利用这些指标优化索引结构和 SQL 语句的具体方法。通过实战案例,展示了如何通过创建合适索引和调整查询语句来提升查询性能。
763 9
|
15天前
|
SQL 运维 关系型数据库
MySQL Binlog 日志查看方法及查看内容解析
本文介绍了 MySQL 的 Binlog(二进制日志)功能及其使用方法。Binlog 记录了数据库的所有数据变更操作,如 INSERT、UPDATE 和 DELETE,对数据恢复、主从复制和审计至关重要。文章详细说明了如何开启 Binlog 功能、查看当前日志文件及内容,并解析了常见的事件类型,包括 Format_desc、Query、Table_map、Write_rows、Update_rows 和 Delete_rows 等,帮助用户掌握数据库变化历史,提升维护和排障能力。
|
2月前
|
SQL 存储 关系型数据库
MySQL原理简介—10.SQL语句和执行计划
本文介绍了MySQL执行计划的相关概念及其优化方法。首先解释了什么是执行计划,它是SQL语句在查询时如何检索、筛选和排序数据的过程。接着详细描述了执行计划中常见的访问类型,如const、ref、range、index和all等,并分析了它们的性能特点。文中还探讨了多表关联查询的原理及优化策略,包括驱动表和被驱动表的选择。此外,文章讨论了全表扫描和索引的成本计算方法,以及MySQL如何通过成本估算选择最优执行计划。最后,介绍了explain命令的各个参数含义,帮助理解查询优化器的工作机制。通过这些内容,读者可以更好地理解和优化SQL查询性能。
|
4月前
|
存储 关系型数据库 MySQL
double ,FLOAT还是double(m,n)--深入解析MySQL数据库中双精度浮点数的使用
本文探讨了在MySQL中使用`float`和`double`时指定精度和刻度的影响。对于`float`,指定精度会影响存储大小:0-23位使用4字节单精度存储,24-53位使用8字节双精度存储。而对于`double`,指定精度和刻度对存储空间没有影响,但可以限制数值的输入范围,提高数据的规范性和业务意义。从性能角度看,`float`和`double`的区别不大,但在存储空间和数据输入方面,指定精度和刻度有助于优化和约束。
740 5
|
5月前
|
监控 关系型数据库 MySQL
MySQL自增ID耗尽应对策略:技术解决方案全解析
在数据库管理中,MySQL的自增ID(AUTO_INCREMENT)属性为表中的每一行提供了一个唯一的标识符。然而,当自增ID达到其最大值时,如何处理这一情况成为了数据库管理员和开发者必须面对的问题。本文将探讨MySQL自增ID耗尽的原因、影响以及有效的应对策略。
348 3
|
5月前
|
存储 关系型数据库 MySQL
MySQL 字段类型深度解析:VARCHAR(50) 与 VARCHAR(500) 的差异
在MySQL数据库中,`VARCHAR`类型是一种非常灵活的字符串存储类型,它允许存储可变长度的字符串。然而,`VARCHAR(50)`和`VARCHAR(500)`之间的差异不仅仅是长度的不同,它们在存储效率、性能和使用场景上也有所不同。本文将深入探讨这两种字段类型的区别及其对数据库设计的影响。
211 2
|
30天前
|
关系型数据库 MySQL Java
【YashanDB知识库】原生mysql驱动配置连接崖山数据库
【YashanDB知识库】原生mysql驱动配置连接崖山数据库
【YashanDB知识库】原生mysql驱动配置连接崖山数据库
|
1月前
|
关系型数据库 MySQL 数据库连接
docker拉取MySQL后数据库连接失败解决方案
通过以上方法,可以解决Docker中拉取MySQL镜像后数据库连接失败的常见问题。关键步骤包括确保容器正确启动、配置正确的环境变量、合理设置网络和权限,以及检查主机防火墙设置等。通过逐步排查,可以快速定位并解决连接问题,确保MySQL服务的正常使用。
285 82
|
5天前
|
SQL 关系型数据库 MySQL
大数据新视界--大数据大厂之MySQL数据库课程设计:MySQL 数据库 SQL 语句调优方法详解(2-1)
本文深入介绍 MySQL 数据库 SQL 语句调优方法。涵盖分析查询执行计划,如使用 EXPLAIN 命令及理解关键指标;优化查询语句结构,包括避免子查询、减少函数使用、合理用索引列及避免 “OR”。还介绍了索引类型知识,如 B 树索引、哈希索引等。结合与 MySQL 数据库课程设计相关文章,强调 SQL 语句调优重要性。为提升数据库性能提供实用方法,适合数据库管理员和开发人员。

推荐镜像

更多