sql优化40秒到0.1秒的奥秘

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
云数据库 RDS PostgreSQL,高可用系列 2核4GB
简介: sql执行计划

引言
项目上生产环境最近有个借口查询突然需要45秒左右。

了不起看到这个问题很疑惑,什么情况,这个接口之前好像没有出现过任何问题吧。

经和运维配合查看,发现是SQL语句问题,有个sql查询脚本执行竟然消耗了40秒,我拿出来自己执行发现亦是如此。

sql大致情况就是有个left join了一张表,有10几万数据,使用explain查看,就是这个表执行消耗了近40秒。

解决
由于是生产环境,涉及数据隐私,具体sql就不贴了,解决思路就是使用explain+SQL语句查看哪个执行是全表扫描。

进而定位到问题,将那张表重新写了下,加了一个业务过滤条件,效率直接从40秒到0.1秒了。

说到这儿,可能有些小伙伴不知道explain执行计划,那我们就一起简单了解下吧,在实际解决问题和面试都会有用的。

EXPLAIN执行计划
官网地址: https://dev.mysql.com/doc/refman/5.5/en/explain-output.html

执行计划中包含的信息
Column Meaning
id The SELECTidentifier
select_type The SELECTtype
table The table for the output row
partitions The matching partitions
type The join type
possible_keys The possible indexes to choose
key The index actually chosen
key_len The length of the chosen key
ref The columns compared to the index
rows Estimate of rows to be examined
filtered Percentage of rows filtered by table condition
extra Additional information
表结构准备
表结构准备是为了在MySQL数据库中创建表并插入数据,以便后续的SQL查询练习。

在这个过程中,我们创建了四个表:dept、emp、emp2和salgrade,以及一个用于测试的t_job表。

这些表中包含了各种类型的数据,例如员工信息、部门信息、薪资等级信息等等。

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS dept;
CREATE TABLE dept (
DEPTNO int NOT NULL,
DNAME varchar(14) DEFAULT NULL,
LOC varchar(13) DEFAULT NULL,
PRIMARY KEY (DEPTNO)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO dept VALUES ('10', 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept VALUES ('20', 'RESEARCH', 'DALLAS');
INSERT INTO dept VALUES ('30', 'SALES', 'CHICAGO');
INSERT INTO dept VALUES ('40', 'OPERATIONS', 'BOSTON');

DROP TABLE IF EXISTS emp;
CREATE TABLE emp (
EMPNO int NOT NULL,
ENAME varchar(10) DEFAULT NULL,
JOB varchar(9) DEFAULT NULL,
MGR int DEFAULT NULL,
HIREDATE date DEFAULT NULL,
SAL double(7,2) DEFAULT NULL,
COMM double(7,2) DEFAULT NULL,
DEPTNO int DEFAULT NULL,
PRIMARY KEY (EMPNO),
KEY idx_job (JOB),
KEY jdx_mgr (MGR),
KEY jdx_3 (DEPTNO),
KEY idx_3 (DEPTNO)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO emp VALUES ('7369', 'SMITH', 'CLERK', '7902', '1980-12-17', '800.00', null, '20');
INSERT INTO emp VALUES ('7499', 'ALLEN', 'SALESMAN', '7698', '1981-02-20', '1600.00', '300.00', '30');
INSERT INTO emp VALUES ('7521', 'WARD', 'SALESMAN', '7698', '1981-02-22', '1250.00', '500.00', '30');
INSERT INTO emp VALUES ('7566', 'JONES', 'MANAGER', '7839', '1981-02-02', '2975.00', null, '20');
INSERT INTO emp VALUES ('7654', 'MARTIN', 'SALESMAN', '7698', '1981-09-28', '1250.00', '1400.00', '30');
INSERT INTO emp VALUES ('7698', 'BLAKE', 'MANAGER', '7839', '1981-01-05', '2850.00', null, '30');
INSERT INTO emp VALUES ('7782', 'CLARK', 'MANAGER', '7839', '1981-09-06', '2450.00', null, '10');
INSERT INTO emp VALUES ('7839', 'KING', 'PRESIDENT', null, '1981-11-17', '5000.00', null, '10');
INSERT INTO emp VALUES ('7844', 'TURNER', 'SALESMAN', '7698', '1981-09-08', '1500.00', '0.00', '30');
INSERT INTO emp VALUES ('7900', 'JAMES', 'CLERK', '7698', '1981-12-03', '950.00', null, '30');
INSERT INTO emp VALUES ('7902', 'FORD', 'ANALYST', '7566', '1981-12-03', '3000.00', null, '20');
INSERT INTO emp VALUES ('7934', 'MILLER', 'CLERK', '7782', '1982-01-23', '1300.00', null, '10');

DROP TABLE IF EXISTS emp2;
CREATE TABLE emp2 (
id int NOT NULL AUTO_INCREMENT,
empno int DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
INSERT INTO emp2 VALUES ('1', '111');
INSERT INTO emp2 VALUES ('2', '222');

DROP TABLE IF EXISTS salgrade;
CREATE TABLE salgrade (
GRADE int NOT NULL,
LOSAL double DEFAULT NULL,
HISAL double DEFAULT NULL,
PRIMARY KEY (GRADE)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO salgrade VALUES ('1', '700', '1200');
INSERT INTO salgrade VALUES ('2', '1201', '1400');
INSERT INTO salgrade VALUES ('3', '1401', '2000');
INSERT INTO salgrade VALUES ('4', '2001', '3000');
INSERT INTO [salgrade](https://www.zhihu.com/search?q=salgrade&search_source=Entity&hybrid_search_source=Entity&hybrid_search_extra=%7B%22sourceType%22%3A%22answer%22%2C%22sourceId%22%3A2492346578%7D) VALUES ('5', '3001', '9999');

DROP TABLE IF EXISTS t_job;
CREATE TABLE t_job (
id int NOT NULL AUTO_INCREMENT,
job varchar(9) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
PRIMARY KEY (id),
KEY j (job)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
id
select查询的序列号,包含一组数字,表示查询中执行select子句或者操作表的顺序

在MySQL中执行SELECT查询时,会生成一个查询计划,其中包含一组数字,称为ID。

ID号的作用是表示查询中执行SELECT子句或操作表的顺序。

id号分为三种情况:
1、id是相同,那么执行顺序从上到下

explain select * from emp e join dept d on e.deptno = d.deptno join salgrade sg on e.sal between sg.losal and sg.hisal;
2、如果id是不同的,是子查询的,id的序号将递增,id值越大的优先级越高,会更先被执行

explain select * from emp e where e.deptno in (select d.deptno from dept d where d.dname = 'SALES');
3、id相同和不同的同时存在:相同的可以归为一组,从上往下顺序执行。

然后所有组中,id值越大,优先级越高,越先执行

explain select * from emp e join dept d on e.deptno = d.deptno join salgrade sg on e.sal between sg.losal and sg.hisal where e.deptno in (select d.deptno from dept d where d.dname = 'SALES');
select_type
主要用来分辨查询的类型,是普通查询还是联合查询还是子查询

select_type是一个非常重要的字段,它表示MySQL执行查询时的查询类型,不同的查询类型会影响到MySQL的执行计划和优化方式。

select_typeValue Meaning
SIMPLE Simple SELECT (not using UNION or subqueries)
PRIMARY Outermost SELECT
UNION Second or later SELECT statement in a UNION
DEPENDENT UNION Second or later SELECT statement in a UNION, dependent on outer query
UNION RESULT Result of a UNION.
SUBQUERY First SELECT in subquery
DEPENDENT SUBQUERY First SELECT in subquery, dependent on outer query
DERIVED Derived table
UNCACHEABLE SUBQUERY A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query
UNCACHEABLE UNION The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY)
常见的select_type类型有以下几种:

SIMPLE:简单SELECT查询,不包含子查询或UNION查询等,查询中也没有使用UNION ALL、DISTINCT、GROUP BY、HAVING、LIMIT等关键字。
PRIMARY:表示查询中包含一个或多个子查询。MySQL会先执行主查询,再执行子查询。
SUBQUERY:表示查询中的第一个子查询,子查询包含在SELECT列表中的子查询或WHERE子句中的子查询。
DERIVED:表示查询中的子查询,派生表。MySQL会先执行子查询,然后将结果存储在一个临时表中,再执行主查询。
UNION:表示查询中的UNION操作,UNION操作会将多个查询的结果集合并成一个结果集。
UNION RESULT:表示查询中的UNION操作的结果集。
DEPENDENT SUBQUERY:表示查询中的子查询依赖于外部查询的结果集。MySQL会根据外部查询的结果集来执行子查询。
DEPENDENT UNION:表示查询中的UNION操作依赖于外部查询的结果集。MySQL会根据外部查询的结果集来执行UNION操作。
DEPENDENT UNION RESULT:表示查询中的UNION操作的结果集依赖于外部查询的结果集。MySQL会根据外部查询的结果集来执行UNION操作。
我们看几个简单的例子

--sample:简单的查询,不包含子查询和union
explain select * from emp;

--primary:查询中若包含任何复杂的子查询,最外层查询则被标记为Primary
explain select staname,ename supname from (select ename staname,mgr from emp) t join emp on t.mgr=emp.empno ;

--union:若第二个select出现在union之后,则被标记为union
explain select from emp where deptno = 10 union select from emp where sal >2000;

--dependent union:跟union类似,此处的depentent表示union或union all联合而成的结果会受外部表影响
explain select * from emp e where e.empno in ( select empno from emp where deptno = 10 union select empno from emp where sal >2000)

--union result:从union表获取结果的select
explain select from emp where deptno = 10 union select from emp where sal >2000;

--subquery:在select或者where列表中包含子查询
explain select * from emp where sal > (select avg(sal) from emp) ;

--dependent subquery:subquery的子查询要受到外部表查询的影响
explain select * from emp e where e.deptno in (select distinct deptno from dept);

--DERIVED: from子句中出现的子查询,也叫做派生类,
explain select staname,ename supname from (select ename staname,mgr from emp) t join emp on t.mgr=emp.empno ;

--UNCACHEABLE SUBQUERY:表示使用子查询的结果不能被缓存
explain select * from emp where empno = (select empno from emp where deptno=@@sort_buffer_size);

--uncacheable union:表示union的查询结果不能被缓存:sql语句未验证
table
对应行正在访问哪一个表,表名或者别名,可能是临时表或者union合并结果集
1、如果是具体的表名,则表明从实际的物理表中获取数据,当然也可以是表的别名
2、表名是derivedN的形式,表示使用了id为N的查询产生的衍生表
3、当有union result的时候,表名是union n1,n2等的形式,n1,n2表示参与union的id

type
type显示的是访问类型,访问类型表示我是以何种方式去访问我们的数据,最容易想的是全表扫描,直接暴力的遍历一张表去寻找需要的数据,效率非常低下,访问的类型有很多,效率从最好到最坏依次是:

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般情况下,得保证查询至少达到range级别,最好能达到ref

--all:全表扫描,一般情况下出现这样的sql语句而且数据量比较大的话那么就需要进行优化。
explain select * from emp;

--index:全索引扫描这个比all的效率要好,主要有两种情况,一种是当前的查询时覆盖索引,即我们需要的数据在索引中就可以索取,或者是使用了索引进行排序,这样就避免数据的重排序
explain select empno from emp;

--range:表示利用索引查询的时候限制了范围,在指定范围内进行查询,这样避免了index的全索引扫描,适用的操作符: =, <>, >, >=, <, <=, IS NULL, BETWEEN, LIKE, or IN()
explain select * from emp where empno between 7000 and 7500;

--index_subquery:利用索引来关联子查询,不再扫描全表
explain select * from emp where emp.job in (select job from t_job);

--unique_subquery:该连接类型类似与index_subquery,使用的是唯一索引
explain select * from emp e where e.deptno in (select distinct deptno from dept);

--index_merge:在查询过程中需要多个索引组合使用,没有模拟出来

--ref_or_null:对于某个字段即需要关联条件,也需要null值的情况下,查询优化器会选择这种访问方式
explain select * from emp e where e.mgr is null or e.mgr=7369;

--ref:使用了非唯一性索引进行数据的查找
create index idx_3 on emp(deptno);
explain select * from emp e,dept d where e.deptno =d.deptno;

--eq_ref :使用唯一性索引进行数据查找
explain select * from emp,emp2 where emp.empno = emp2.empno;

--const:这个表至多有一个匹配行,
explain select * from emp where empno = 7369;

--system:表只有一行记录(等于系统表),这是const类型的特例,平时不会出现
possible_keys
显示可能应用在这张表中的索引,一个或多个,查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询实际使用

explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;
key
实际使用的索引,如果为null,则没有使用索引,查询中若使用了覆盖索引,则该索引和查询的select字段重叠。

explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;
key_len
表示索引中使用的字节数,可以通过key_len计算查询中使用的索引长度,在不损失精度的情况下长度越短越好。

explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;
ref
显示索引的哪一列被使用了,如果可能的话,是一个常数

explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;
rows
根据表的统计信息及索引使用情况,大致估算出找出所需记录需要读取的行数,此参数很重要,直接反应的sql找了多少数据,在完成目的的情况下越少越好

explain select * from emp;
extra
包含额外的信息。

--using filesort:说明mysql无法利用索引进行排序,只能利用排序算法进行排序,会消耗额外的位置
explain select * from emp order by sal;

--using temporary:建立临时表来保存中间结果,查询完成之后把临时表删除
explain select ename,count(*) from emp where deptno = 10 group by ename;

--using index:这个表示当前的查询时覆盖索引的,直接从索引中读取数据,而不用访问数据表。如果同时出现using where 表名索引被用来执行索引键值的查找,如果没有,表面索引被用来读取数据,而不是真的查找
explain select deptno,count(*) from emp group by deptno limit 10;

--using where:使用where进行条件过滤
explain select * from t_user where id = 1;

--using join buffer:使用连接缓存,情况没有模拟出来

--impossible where:where语句的结果总是false
explain select * from emp where empno = 7469;
EXPLAIN使用场景

  1. 查看查询的执行计划。
    使用EXPLAIN命令可以查看查询的执行计划,包括查询中使用的索引、表的连接方式、数据读取方式等等。

通过查看执行计划,可以了解MySQL是如何处理查询操作的,从而发现查询中可能存在的性能问题,进而进行优化。

  1. 比较不同查询方案的性能。
    使用EXPLAIN命令可以比较不同查询方案的性能,例如在多个索引中选择最优索引、使用不同的连接方式等等。

通过比较不同查询方案的执行计划,可以找到最优的查询方案,从而提高查询性能。

  1. 优化查询语句。
    使用EXPLAIN命令可以发现查询语句中可能存在的性能问题,例如没有使用索引、使用了不必要的子查询等等。

通过优化查询语句,可以让MySQL选择更优的查询方案,从而提高查询性能。

  1. 了解表结构对查询性能的影响。
    使用EXPLAIN命令可以了解表结构对查询性能的影响,例如表中是否存在大字段、字段类型是否匹配等等。

通过了解表结构对查询性能的影响,可以进行相应的优化,提高查询性能。

其实在大多数时候,使用这个命令一般都是排查慢查询原因,这个是用的最多的。

EXPLAIN使用注意事项

  1. EXPLAIN命令只能用于SELECT语句。
    EXPLAIN命令不能用于INSERT、UPDATE、DELETE等语句。

  2. EXPLAIN命令不能直接修改数据。
    EXPLAIN命令只是用来查看查询的执行计划,不能直接修改查询结果或数据库中的数据。

  3. EXPLAIN命令只能查看当前用户有权限查看的表和字段。
    如果当前用户没有权限查看某些表或字段,那么在使用EXPLAIN命令时,这些表或字段的信息将不会显示。

  4. EXPLAIN命令的输出结果可能会受到多种因素的影响
    例如查询条件、表结构、索引状态等等。因此,在使用EXPLAIN命令时,需要综合考虑多种因素,才能得出正确的结论。

  5. EXPLAIN命令可以使用不同的选项
    例如EXTENDED、PARTITIONS等等。通过使用不同的选项,可以得到更详细的执行计划信息,从而更好地了解MySQL是如何处理查询操作的。

总结
今天了不起就一个生产环境优化,带大家了解下EXPLAIN相关知识。

希望大家在接下来的开发生涯中,持续使用这个命令,解决和优化各种数据库慢查询问题。

相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。 &nbsp; 相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/mysql&nbsp;
相关文章
|
7月前
|
SQL 关系型数据库 MySQL
MySQL进阶突击系列(07) 她气鼓鼓递来一条SQL | 怎么看执行计划、SQL怎么优化?
在日常研发工作当中,系统性能优化,从大的方面来看主要涉及基础平台优化、业务系统性能优化、数据库优化。面对数据库优化,除了DBA在集群性能、服务器调优需要投入精力,我们研发需要负责业务SQL执行优化。当业务数据量达到一定规模后,SQL执行效率可能就会出现瓶颈,影响系统业务响应。掌握如何判断SQL执行慢、以及如何分析SQL执行计划、优化SQL的技能,在工作中解决SQL性能问题显得非常关键。
|
4月前
|
SQL 存储 自然语言处理
SQL的解析和优化的原理:一条sql 执行过程是什么?
SQL的解析和优化的原理:一条sql 执行过程是什么?
SQL的解析和优化的原理:一条sql 执行过程是什么?
|
6月前
|
SQL 关系型数据库 MySQL
如何优化SQL查询以提高数据库性能?
这篇文章以生动的比喻介绍了优化SQL查询的重要性及方法。它首先将未优化的SQL查询比作在自助餐厅贪多嚼不烂的行为,强调了只获取必要数据的必要性。接着,文章详细讲解了四种优化策略:**精简选择**(避免使用`SELECT *`)、**专业筛选**(利用`WHERE`缩小范围)、**高效联接**(索引和限制数据量)以及**使用索引**(加速搜索)。此外,还探讨了如何避免N+1查询问题、使用分页限制结果、理解执行计划以及定期维护数据库健康。通过这些技巧,可以显著提升数据库性能,让查询更高效流畅。
|
7月前
|
SQL 关系型数据库 MySQL
基于SQL Server / MySQL进行百万条数据过滤优化方案
对百万级别数据进行高效过滤查询,需要综合使用索引、查询优化、表分区、统计信息和视图等技术手段。通过合理的数据库设计和查询优化,可以显著提升查询性能,确保系统的高效稳定运行。
268 9
|
8月前
|
SQL Oracle 关系型数据库
如何在 Oracle 中配置和使用 SQL Profiles 来优化查询性能?
在 Oracle 数据库中,SQL Profiles 是优化查询性能的工具,通过提供额外统计信息帮助生成更有效的执行计划。配置和使用步骤包括:1. 启用自动 SQL 调优;2. 手动创建 SQL Profile,涉及收集、执行调优任务、查看报告及应用建议;3. 验证效果;4. 使用 `DBA_SQL_PROFILES` 视图管理 Profile。
|
9月前
|
SQL Oracle 数据库
使用访问指导(SQL Access Advisor)优化数据库业务负载
本文介绍了Oracle的SQL访问指导(SQL Access Advisor)的应用场景及其使用方法。访问指导通过分析给定的工作负载,提供索引、物化视图和分区等方面的优化建议,帮助DBA提升数据库性能。具体步骤包括创建访问指导任务、创建工作负载、连接工作负载至访问指导、设置任务参数、运行访问指导、查看和应用优化建议。访问指导不仅针对单条SQL语句,还能综合考虑多条SQL语句的优化效果,为DBA提供全面的决策支持。
230 11
|
10月前
|
SQL 缓存 监控
大厂面试高频:4 大性能优化策略(数据库、SQL、JVM等)
本文详细解析了数据库、缓存、异步处理和Web性能优化四大策略,系统性能优化必知必备,大厂面试高频。关注【mikechen的互联网架构】,10年+BAT架构经验倾囊相授。
大厂面试高频:4 大性能优化策略(数据库、SQL、JVM等)
|
8月前
|
SQL 分布式计算 Java
Spark SQL向量化执行引擎框架Gluten-Velox在AArch64使能和优化
本文摘自 Arm China的工程师顾煜祺关于“在 Arm 平台上使用 Native 算子库加速 Spark”的分享,主要内容包括以下四个部分: 1.技术背景 2.算子库构成 3.算子操作优化 4.未来工作
951 0
|
10月前
|
SQL 缓存 数据库
SQL慢查询优化策略
在数据库管理和应用开发中,SQL查询的性能优化至关重要。慢查询优化不仅可以提高应用的响应速度,还能降低服务器负载,提升用户体验。本文将详细介绍针对SQL慢查询的优化策略。
|
10月前
|
SQL 存储 BI
gbase 8a 数据库 SQL合并类优化——不同数据统计周期合并为一条SQL语句
gbase 8a 数据库 SQL合并类优化——不同数据统计周期合并为一条SQL语句

热门文章

最新文章