MySQL进阶突击系列(07) 她气鼓鼓递来一条SQL | 怎么看执行计划、SQL怎么优化?

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS SQL Server,基础系列 2核4GB
云原生数据库 PolarDB 分布式版,标准版 2核8GB
简介: 在日常研发工作当中,系统性能优化,从大的方面来看主要涉及基础平台优化、业务系统性能优化、数据库优化。面对数据库优化,除了DBA在集群性能、服务器调优需要投入精力,我们研发需要负责业务SQL执行优化。当业务数据量达到一定规模后,SQL执行效率可能就会出现瓶颈,影响系统业务响应。掌握如何判断SQL执行慢、以及如何分析SQL执行计划、优化SQL的技能,在工作中解决SQL性能问题显得非常关键。

读书笔记:一个人能走到自信、热情这一步是非常不容易的。自信可以滋养自体;而热情可以滋养他人。全能感有四个基本变化:全能自恋、全能暴怒、彻底无助、被害妄想。当我们降服全能自恋,从孤独的想象世界进入关系的现实世界,你会发现这个世界充满爱、热情、和创造力。你会爱上这个世界,也会爱上自己。


一、前言背景

二、如何判断 OR 找到慢查询SQL

2.1 开启慢查询日志

2.2 多慢的SQL,算是慢SQL?

2.3 慢查询日志分析

2.4 透过执行计划判断慢SQL

三、如何看懂执行计划

3.1  id-执行优先级顺序

3.2 select_type-查询类型

3.3 type-执行计划关键指标-有没有走索引?

3.3.1 const的意义-命中主键或者唯一索引

3.3.2 eq_ref-命中主键或者唯一索引

3.3.3 ref-命中了非唯一性索引

3.3.4 index-查询结果列全是索引

3.3.5 range-范围查询

3.3.6 all-全表扫描

四、SQL如何优化


一、前言背景

    今天是大年初八,祝大家新年快乐,开工大吉!

    刚从HR部门转过来做研发没多少年的小美,今天她气鼓鼓的和你说,运维那边说她的这个sql,过年期间被监控发现,执行很慢。让你帮她看看,她的SQL怎么慢了?要怎么优化?

    人美心善的小美,心里一定很委屈。这时候你要不要帮帮她?

......

    在日常研发工作当中,系统性能优化,从大的方面来看主要涉及基础平台优化、业务系统性能优化、数据库优化。面对数据库优化,除了DBA在集群性能、服务器调优需要投入精力,我们研发需要负责业务SQL执行优化。当业务数据量达到一定规模后,SQL执行效率可能就会出现瓶颈,影响系统业务响应。

    掌握如何判断SQL执行慢、以及如何分析SQL执行计划、优化SQL的技能,在工作中解决SQL性能问题显得非常关键。

    此时,可以教小美怎么判断和找到慢SQL,以及详细教她看sql的执行计划,并耐心给她讲一些生产实战的SQL优化经验。

二、如何判断 OR 找到慢查询SQL

    MySQL支持记录慢查询SQL日志,不过默认是关闭的。可以通过命令查看以及开启:

show variables like '%slow_query_log%';

2.1 开启慢查询日志

    通过在客户端会话,采用set global  进行全局设置MySQL慢SQL查询记录。比如:

set global slow_query_log='on';

    然后再次查询,慢SQL记录已经打开。

2.2 多慢的SQL,算是慢SQL?

    MySQL默认执行时间大于10s,就是慢SQL。可以通过查看参数long_query_time,比如:

show variables like '%long_query_time%';

    在日常工作中,慢SQL就意味着客户体验差。对于系统来说,一条普通业务SQL查询,原则上最慢不得大于1s。而涉及对客展业的后台SQL,响应时间绝对不允许大于3s。对于高级别的系统,秒级响应的SQL实际就是慢SQL,需要进行SQL优化、技术架构优化。

2.3 慢查询日志分析

    慢SQL日志内容长什么样?

    为了方便验证,我们把参数long_query_time设置为0s。

    不过这里需要注意,在客户端会话中set GLOBAL long_query_time之后,需要关闭该会话客户端,重新打开才能看到long_query_time新值。以及需要在新会话窗口执行sql,才会被当做慢SQL记录到log日志。

修改为0,并重新打开新会话执行查询SQL,这样所有sql都被MySQL当中慢sql记录到我们的日志里:

/usr/local/var/mysql/ladingjieniu-slow.log。

在slow log里,我们可以看到慢查询sql内容,以及查询时间、查询的用户名、扫描数据行数、最后获得结果数据等信息。

2.4 透过执行计划判断慢SQL

    在未发版上线前,测试环境由于数据量有限,无法及时监控发现慢SQL。此时针对高等级核心业务场景接口SQL,可以通过explain 查看SQL执行计划的type字段来评估SQL性能。

    type值常见的有const、ref、eq_ref、all、range这些。一个查询sql的执行计划type要求至少是index才是优秀,如果是all就要考虑纳入优化。

    接下来,我们具体分析执行计划应该如何看。

三、如何看懂执行计划

    我们拿一个执行计划出来看一下。比如:

里面有id、select_type、table、type、key、ref等字段。每个都有它特定意义。

3.1 id-执行优先级顺序

    在SQL架构原理那一篇文章,我们说过,sql最终如何执行是由核心组件查询优化器决定。查询优化器通过询问存储引擎、sql索引情况来生成SQL执行树。一个复杂的SQL,里面有很多联合查询、子查询。具体哪个先执行,这里就可以通过id来看到查询优化器指定的顺序。

    比如上图,id值有【1、1、1、2】。这个执行顺序是2,然后是1。三个1从上往下执行。

    原则是:id值越大,优先级越高。会被优先执行。如果id值相等,在执行计划前面的先执行。

3.2 select_type-查询类型

    查询类型,常见的有simple、primary、subquery、derived、union、union all这些。看名字顾名思义,有简单查询、主查询、子查询、联合查询。具体展开:

simple:表示单表查询或者简单查询。比如:

primary:表示主查询。

     执行计划select type是primary,往往都是SQL是复杂查询,里面有子查询。primary和subquery或者derived一起出现。比如如下:

union: sql里有union,union关键字后的select 语句就被标注select type为【union】。

derived和subquery的区别,这里也说一下。

select列表,where 条件里的子查询是subquery。而如果子查询是在from结果集,select type就是derived。

比如:

explain select p.*,(select role_id from sys_user_role) as role_id from (select user_id from sys_user) t,sys_user_post p;

from后面的这个t结果集子查询就是derived类型,而select里的子查询【(select role_id from sys_user_role)】就是subquery类型。

3.3 type-执行计划关键指标-有没有走索引?

    常见的有const、all、range、index、ref、eq_ref这些。这里查询表现最好的按高到底排序:

const> eq_ref> ref > range > index > all。

3.3.1 const的意义-命中主键或者唯一索引

    当我们执行计划type为const,说明命中了主键索引或者唯一索引。比如如下sql:

explain select * from sys_user where user_id=1;

where条件是唯一索引user_id:

3.3.2 eq_ref-命中主键或者唯一索引

    当type为eq_ref时,说明也是命中了索引,而且和const类型,也是命中的主键或者唯一索引。sql里where条件,用到了主键索引进行多个等值匹配。

    比如如下sql的查询计划type出现了 eq_ref类型:

explain select * from sys_user where user_id in (select user_id from sys_user_role);

3.3.3 ref-命中了非唯一性索引

    而ref也是命中索引,但是和eq_ref不同的是,ref是命中了非唯一性索引。比如:

user_mvcc_demo表name是非唯一索引,执行sql:

explain select * from user_mvcc_demo where name='拉丁哥8';

    查询计划type就是ref。

3.3.4 index-查询结果列全是索引

    比如我们这个sys_user_role表,里面两个字段都是索引。查询计划,虽然是全部查询,但是tye就是index。

3.3.5 range-范围查询

    查询条件里使用了大于小于,between之类的查询。

3.3.6 all-全表扫描

    如果sql查询里,type有all,那就是触发了全表扫描,没有走索引。需要重点关注,尝试看能否优化。

    其他的顺便说一下,possible_keys就是可能用到的索引、key表示实际用的索引。

ref就是哪些列被用来关联索引查询。

rows,表示预计要读取多少行数据。

四、SQL如何优化

    其实SQL优化的前期慢sql定位、以及执行计划的分析,我们已经大概确定sql慢在哪里,尤其是复杂的sql。通过执行计划,可以清晰看到sql是怎么执行的,以及是否走索引,不管是select字段,from子集、中间结果表、where条件、还是排序order by,或者group by,还是having,每一部分都需要关注优化。

    而sql优化的核心,重点是让sql尽可能的走索引+合理创建索引。通过执行计划,我们可以很快发现,未及时创建的索引。而sql如何写,才会触发走索引呢?这里有几个原则:

1、最左匹配原则。

2、sql里尽量不要做类型转换、函数计算。

3、like、范围查询要谨慎。

4、尽量不要出现*。

    这几个原则非常朴实,有的大佬甚至整理了顺口溜方便大家做SQL优化。然而随着nosql、内存数据库的广泛应用,小公司数据量小sql优化需求不多;而大企业,有足够成熟的技术架构,用到sql优化的很多时候就是做大数据数仓的火伴,写sql就是写代码的数仓同学,需要对sql有深入理解。其他研发真正用到sql优化的,刚好就是中小企业研发同事,系统有一定的数据量,但是在基础设施投入有限,人力有限,就需要大家投入研发精力去进行SQL优化。

    sql优化之外,能继续深入优化的方法还有数据预处理计算、以及联合索引、应用缓存的合理搭配应用。

    最后,SQL优化就一句话:合理创建索引,且让SQL查询应用上索引。


推荐阅读拉丁解牛相关专题系列(欢迎交流讨论):

1、JVM进阶调优系列(3)堆内存的对象什么时候被回收?

2、JVM进阶调优系列(2)字节面试:JVM内存区域怎么划分,分别有什么用?

3、JVM进阶调优系列(1)类加载器原理一文讲透

4、JAVA并发编程系列(13)Future、FutureTask异步小王子

5、MySQL进阶突击系列(05)突击MVCC核心原理 | 左右护法ReadView视图和undoLog版本链强强联合

相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
相关文章
|
1月前
|
SQL 存储 关系型数据库
MySQL体系结构详解:一条SQL查询的旅程
本文深入解析MySQL内部架构,从SQL查询的执行流程到性能优化技巧,涵盖连接建立、查询处理、执行阶段及存储引擎工作机制,帮助开发者理解MySQL运行原理并提升数据库性能。
|
16天前
|
关系型数据库 MySQL 数据库
阿里云数据库RDS费用价格:MySQL、SQL Server、PostgreSQL和MariaDB引擎收费标准
阿里云RDS数据库支持MySQL、SQL Server、PostgreSQL、MariaDB,多种引擎优惠上线!MySQL倚天版88元/年,SQL Server 2核4G仅299元/年,PostgreSQL 227元/年起。高可用、可弹性伸缩,安全稳定。详情见官网活动页。
|
16天前
|
关系型数据库 分布式数据库 数据库
阿里云数据库收费价格:MySQL、PostgreSQL、SQL Server和MariaDB引擎费用整理
阿里云数据库提供多种类型,包括关系型与NoSQL,主流如PolarDB、RDS MySQL/PostgreSQL、Redis等。价格低至21元/月起,支持按需付费与优惠套餐,适用于各类应用场景。
|
1月前
|
SQL 监控 关系型数据库
SQL优化技巧:让MySQL查询快人一步
本文深入解析了MySQL查询优化的核心技巧,涵盖索引设计、查询重写、分页优化、批量操作、数据类型优化及性能监控等方面,帮助开发者显著提升数据库性能,解决慢查询问题,适用于高并发与大数据场景。
|
1月前
|
SQL 监控 关系型数据库
查寻MySQL或SQL Server的连接数,并配置超时时间和最大连接量
以上步骤提供了直观、实用且易于理解且执行的指导方针来监管和优化数据库服务器配置。务必记得,在做任何重要变更前备份相关配置文件,并确保理解每个参数对系统性能可能产生影响后再做出调节。
203 11
|
21天前
|
关系型数据库 MySQL 数据库
阿里云数据库RDS支持MySQL、SQL Server、PostgreSQL和MariaDB引擎
阿里云数据库RDS支持MySQL、SQL Server、PostgreSQL和MariaDB引擎,提供高性价比、稳定安全的云数据库服务,适用于多种行业与业务场景。
|
21天前
|
缓存 关系型数据库 BI
使用MYSQL Report分析数据库性能(下)
使用MYSQL Report分析数据库性能
57 3
|
27天前
|
关系型数据库 MySQL 数据库
自建数据库如何迁移至RDS MySQL实例
数据库迁移是一项复杂且耗时的工程,需考虑数据安全、完整性及业务中断影响。使用阿里云数据传输服务DTS,可快速、平滑完成迁移任务,将应用停机时间降至分钟级。您还可通过全量备份自建数据库并恢复至RDS MySQL实例,实现间接迁移上云。
|
2月前
|
存储 运维 关系型数据库
从MySQL到云数据库,数据库迁移真的有必要吗?
本文探讨了企业在业务增长背景下,是否应从 MySQL 迁移至云数据库的决策问题。分析了 MySQL 的优势与瓶颈,对比了云数据库在存储计算分离、自动化运维、多负载支持等方面的优势,并提出判断迁移必要性的五个关键问题及实施路径,帮助企业理性决策并落地迁移方案。

相关产品

  • 云数据库 RDS MySQL 版
  • 推荐镜像

    更多