sql优化40秒到0.1秒的奥秘

引言

项目上生产环境最近有个借口查询突然需要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	TheSELECTidentifier
select_type	TheSELECTtype
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表。

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

代码语言：javascript复制

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={"sourceType":"answer","sourceId":2492346578})` 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是相同，那么执行顺序从上到下

代码语言：javascript复制

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值越大的优先级越高，会更先被执行

代码语言：javascript复制

explain select * from emp e where e.deptno in (select d.deptno from dept d where d.dname = 'SALES');

3、id相同和不同的同时存在：相同的可以归为一组，从上往下顺序执行。 然后所有组中，id值越大，优先级越高，越先执行

代码语言：javascript复制

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类型有以下几种：

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

我们看几个简单的例子

代码语言：javascript复制

--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

代码语言：javascript复制

--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

显示可能应用在这张表中的索引，一个或多个，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用

代码语言：javascript复制

explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;

key

实际使用的索引，如果为null，则没有使用索引，查询中若使用了覆盖索引，则该索引和查询的select字段重叠。

代码语言：javascript复制

explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;

key_len

表示索引中使用的字节数，可以通过key_len计算查询中使用的索引长度，在不损失精度的情况下长度越短越好。

代码语言：javascript复制

explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;

ref

显示索引的哪一列被使用了，如果可能的话，是一个常数

代码语言：javascript复制

explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;

rows

根据表的统计信息及索引使用情况，大致估算出找出所需记录需要读取的行数，此参数很重要，直接反应的sql找了多少数据，在完成目的的情况下越少越好

代码语言：javascript复制

explain select * from emp;

extra

包含额外的信息。

代码语言：javascript复制

--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是如何处理查询操作的，从而发现查询中可能存在的性能问题，进而进行优化。

2. 比较不同查询方案的性能。

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

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

3. 优化查询语句。

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

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

4. 了解表结构对查询性能的影响。

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

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

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

EXPLAIN使用注意事项

1. EXPLAIN命令只能用于SELECT语句。

EXPLAIN命令不能用于INSERT、UPDATE、DELETE等语句。

2. EXPLAIN命令不能直接修改数据。

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

3. EXPLAIN命令只能查看当前用户有权限查看的表和字段。

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

4. EXPLAIN命令的输出结果可能会受到多种因素的影响

例如查询条件、表结构、索引状态等等。因此，在使用EXPLAIN命令时，需要综合考虑多种因素，才能得出正确的结论。

5. EXPLAIN命令可以使用不同的选项

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

总结

今天了不起就一个生产环境优化，带大家学习了EXPLAIN相关知识。

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

sql 数据 索引 性能 优化

0 人点赞