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

本文涉及的产品
RDS PostgreSQL Serverless,0.5-4RCU 50GB 3个月
推荐场景:
对影评进行热评分析
云数据库 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版本链强强联合

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
打赏
0
13
13
0
109
分享
相关文章
【MySQL】SQL分析的几种方法
以上就是SQL分析的几种方法。需要注意的是,这些方法并不是孤立的,而是相互关联的。在实际的SQL分析中,我们通常需要结合使用这些方法,才能找出最佳的优化策略。同时,SQL分析也需要对数据库管理系统,数据,业务需求有深入的理解,这需要时间和经验的积累。
28 12
大数据新视界--大数据大厂之MySQL数据库课程设计:MySQL 数据库 SQL 语句调优方法详解(2-1)
本文深入介绍 MySQL 数据库 SQL 语句调优方法。涵盖分析查询执行计划,如使用 EXPLAIN 命令及理解关键指标;优化查询语句结构,包括避免子查询、减少函数使用、合理用索引列及避免 “OR”。还介绍了索引类型知识,如 B 树索引、哈希索引等。结合与 MySQL 数据库课程设计相关文章,强调 SQL 语句调优重要性。为提升数据库性能提供实用方法,适合数据库管理员和开发人员。
大数据新视界--大数据大厂之MySQL 数据库课程设计:MySQL 数据库 SQL 语句调优的进阶策略与实际案例(2-2)
本文延续前篇,深入探讨 MySQL 数据库 SQL 语句调优进阶策略。包括优化索引使用,介绍多种索引类型及避免索引失效等;调整数据库参数,如缓冲池、连接数和日志参数;还有分区表、垂直拆分等其他优化方法。通过实际案例分析展示调优效果。回顾与数据库课程设计相关文章,强调全面认识 MySQL 数据库重要性。为读者提供综合调优指导,确保数据库高效运行。
MySQL细节优化:关闭大小写敏感功能的方法。
通过这种方法,你就可以成功关闭 MySQL 的大小写敏感功能,让你的数据库操作更加便捷。
135 19
|
2月前
|
SQL
【YashanDB知识库】使用leading hint调整SQL执行计划后报错YAS-04522 invalid hint leading
【YashanDB知识库】使用leading hint调整SQL执行计划后报错YAS-04522 invalid hint leading
【YashanDB知识库】使用leading hint调整SQL执行计划后报错YAS-04522 invalid hint leading
如何优化SQL查询以提高数据库性能?
这篇文章以生动的比喻介绍了优化SQL查询的重要性及方法。它首先将未优化的SQL查询比作在自助餐厅贪多嚼不烂的行为,强调了只获取必要数据的必要性。接着,文章详细讲解了四种优化策略:**精简选择**(避免使用`SELECT *`)、**专业筛选**(利用`WHERE`缩小范围)、**高效联接**(索引和限制数据量)以及**使用索引**(加速搜索)。此外,还探讨了如何避免N+1查询问题、使用分页限制结果、理解执行计划以及定期维护数据库健康。通过这些技巧,可以显著提升数据库性能,让查询更高效流畅。
【YashanDB知识库】如何将mysql含有group by的SQL转换成崖山支持的SQL
本文探讨了在YashanDB(崖山数据库)中执行某些SQL语句时出现的报错问题,对比了MySQL的成功执行结果。问题源于SQL-92标准对非聚合列的严格限制,要求这些列必须出现在GROUP BY子句中,而SQL:1999及更高版本允许非聚合列直接出现在选择列中。YashanDB和Oracle遵循SQL-92标准,因此会报错。文章提供了两种解决方法:使用聚合函数处理非聚合列,或将GROUP BY与ORDER BY拆分为两层查询。最后总结指出,SQL-92标准更为严谨合理,建议开发者遵循此规范以避免潜在问题。
【YashanDB知识库】如何从内存中获取SQL语句的执行计划
【YashanDB知识库】如何从内存中获取SQL语句的执行计划
|
2月前
|
SQL
【YashanDB知识库】过期统计信息导致SQL执行计划变差
【YashanDB知识库】过期统计信息导致SQL执行计划变差
|
2月前
|
SQL
【YashanDB知识库】收集分区表统计信息采样率小于1导致SQL执行计划走偏
【YashanDB知识库】收集分区表统计信息采样率小于1导致SQL执行计划走偏

相关产品

  • 云数据库 RDS MySQL 版