深入解析 explain 的用处:优化 SQL 语句执行效率的关键

MySQL数据库的查询效率决定着项目的”脉搏”速率。试设想,若查询如同蜗牛爬行,工作必将步履维艰。幸运的是,借助于`explain`指令,我们可洞悉MySQL如何巧妙处理SQL查询请求。让我们共同揭示其秘密,使数据查询化繁为简,乐趣无穷。

CREATE TABLE `demo`.`emp`  (
  `emp_id` bigint(20) NOT NULL,
  `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '姓名',
  `empno` int(20) NOT NULL COMMENT '工号',
  `deptno` int(20) NOT NULL COMMENT '部门编号',
  `sal` int(11) NOT NULL DEFAULT 0 COMMENT '销售量',
  PRIMARY KEY (`emp_id`) USING BTREE,
  INDEX `u1`(`deptno`) USING BTREE,
  UNIQUE INDEX `u2`(`empno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

CREATE TABLE `demo`.`dept`  (
  `id` bigint(20) NOT NULL,
  `deptno` int(20) NOT NULL COMMENT '部门编码',
  `dname` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '部门名称',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `dept_u1`(`deptno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

1.探索`explain`命令的奥秘

CREATE TABLE `demo`.`salgrade`  (
  `id` bigint(20) NOT NULL,
  `losal` int(20) NULL DEFAULT NULL,
  `hisal` int(20) NULL DEFAULT NULL,
  `emp_id` bigint(20) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

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

首先,领略’explain’之神效。当以此指令输入,将宛如得到智者指引,详述MySQL如何实施你的查询。此非普通命令,更似一扇深入理解MySQL的窗口。

图片[1]-深入解析 explain 的用处:优化 SQL 语句执行效率的关键-AIGC社区

图片[2]-深入解析 explain 的用处:优化 SQL 语句执行效率的关键-AIGC社区

2.解读查询序列号的重要性

mysql> 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 wheree.deptno = (select d.deptno from dept d where d.dname = 'SALES');

图片[3]-深入解析 explain 的用处:优化 SQL 语句执行效率的关键-AIGC社区

查验序列号看似单调乏味,实则如同影片剧本的序号,指引我们查证过程中的每一步顺序。打开正确的序章,如精心编排的影片一样引人入胜,大大提升查证效率。

图片[4]-深入解析 explain 的用处:优化 SQL 语句执行效率的关键-AIGC社区

深入掌握`union`和`uncacheableunion`的含义。

图片[5]-深入解析 explain 的用处:优化 SQL 语句执行效率的关键-AIGC社区

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

“联合查询”与”不可缓存联合查询”虽如孪生兄弟般相似,却有着迥异的特性。前者帮助实现多重查询结果的结合,而后者提示某些查询结果无法再CACHE中存储。这类无法抄录或储存的记忆也是同样道理。

图片[6]-深入解析 explain 的用处:优化 SQL 语句执行效率的关键-AIGC社区

4.全索引扫描的两种面貌

图片[7]-深入解析 explain 的用处:优化 SQL 语句执行效率的关键-AIGC社区

索引扫描如同图书馆寻书,而此处之”书”实为数据库内数据。全索引扫描包含两类情形,其一通过索引发掘所需信息,其二借助索引导航,省却数据重排步骤。似于图书馆中,既可迅速检索书本,亦可便捷地查找以字母顺序排列的书籍。

mysql> explain select * from emp e where e.empno  in ( select empno from emp where deptno = 10 union select empno from emp where sal >2000)

5.范围查询的智慧运用

图片[8]-深入解析 explain 的用处:优化 SQL 语句执行效率的关键-AIGC社区

mysql> explain select * from emp where deptno = 10 union select * from emp where sal >2000;

域值检索犹如设立边界,使数据得以在设定范围顺畅运行,规避繁重的全索引扫描任务,起到控制水流量的”节制闸门”作用。

图片[9]-深入解析 explain 的用处:优化 SQL 语句执行效率的关键-AIGC社区

6.非唯一性索引的探索

mysql> explain select * from emp where sal > (select avg(sal) from emp) ;

关于’ref’与’eq_ref’,代表着两种不同的索引应用情境。前者表示利用非唯一性索引;后者则是对齐主键索引或者唯一性非空索引以执行等值联表查询。犹如不同的钥匙对应不同的锁,各司其职,各尽其用。

图片[10]-深入解析 explain 的用处:优化 SQL 语句执行效率的关键-AIGC社区

7.`const`与`system`:极速查询的秘密

mysql> explain select e1.* from emp e1 WHERE e1.deptno = (SELECT deptno FROM emp e2 WHERE e1.empno = e2.empno);

`const`与`system`是提升查询效率之利器。前者仅匹配一条数据,后者适用于单行记录情形,二者皆为高速查询路线,犹如高速公路上的专线快车,迅速抵达指定地点。

图片[11]-深入解析 explain 的用处:优化 SQL 语句执行效率的关键-AIGC社区

8.索引的选择与应用

mysql> explain select * from ( select emp_id,count(*) from emp group by emp_id ) e;

选用索引堪比选取适用之工具,有时可见待选索引清单,但实际应用所参考之索引却需依据查询具体情况抉择。犹如工具箱里精选允用之工具,每种工具皆具备专属功能。

图片[12]-深入解析 explain 的用处:优化 SQL 语句执行效率的关键-AIGC社区

9.排序与临时表:优化查询的关键

mysql> explain select * from emp where empno = (select empno from emp where deptno=@@sort_buffer_size);

在查询优化中,务必关注`usingfilesort`与`usingtemporary`语句。前者代表MySQL无法借助索引实现排序,后者则创建临时表储存中间数据。这两者皆是优化的关键之处,如同调整汽车引擎以提升行驶性能及稳定性。

图片[13]-深入解析 explain 的用处:优化 SQL 语句执行效率的关键-AIGC社区

10.覆盖索引与查询效率

在此专有术语下,“覆盖索引”乃一出色应用,其作用在于查询可直接从索引中获取数据,而无需访问实际数据表。此举犹如持掌便捷之VIP通行证,省却冗长流程,直达目标信息。

mysql> explain select * from emp;

图片[14]-深入解析 explain 的用处:优化 SQL 语句执行效率的关键-AIGC社区

    © 版权声明
    THE END
    喜欢就支持一下吧
    点赞10赞赏 分享
    评论 抢沙发
    头像
    欢迎您留下宝贵的见解!
    提交
    头像

    昵称

    取消
    昵称表情代码图片

      暂无评论内容