EXPLAIN：解说一条简单 SQL 语句的执行计划-低调大师

EXPLAIN：解说一条简单 SQL 语句的执行计划

2024-12-12 225

在MySQL里，当通过各种手段抓取到影响系统性能的慢SQL语句后，此刻想知道这条SQL语句为何会慢的答案。

>作者：杨涛涛，爱可生技术专家。 > >爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。

在MySQL里，当通过各种手段抓取到影响系统性能的慢SQL语句后，此刻想知道这条SQL语句为何会慢的答案。大致有以下方式：

凭借个人已知的SQL优化经验进行SQL改写。
对照公司严格要求的开发规范进行SQL改写。
查看对应SQL 语句的执行计划，从结果中分析SQL是否使用合适的索引、是否走了合适的表关联顺序、是否走了合适的表关联算法等。

通过以上三种方式审阅后，是否需要反馈给业务方来修改SQL语句的逻辑或者说是变更SQL 语句涉及到的表结构。

虽然以上几种方式一般都需要互相结合来验证SQL的优化效果，仅凭一种方式来直接定位出SQL是否足够优化不太可能，但是每种方式都需要依赖第三种方式，通过直接和数据库交互来查看数据库的执行计划，能够比较直观的展示SQL执行的过程。在MySQL里查看SQL 语句执行计划的方式是使用EXPLAIN 语句。

EXPLAIN（对应同义词：DESC、DESCRIBE）语句可以用来查看一条SQL是否匹配到合适的索引、是否使用到中间临时表、是否需要额外的排序等等不利因素；并且EXPLAIN 语句还有几种定制化的输出格式（表格、JSON、树状），每种格式输出内容的详细程度不一样；当一条SQL 语句被带入EXPLAIN 执行后，从执行结果就能大致判断这条SQL目前能否做进一步优化。

本篇使用 EXPLAIN 来验证一条简单的SQL 语句，来帮助我们进行后续调优。以下是此语句的EXPLAIN 执行结果：

debian-ytt1:ytt&gt;desc  select * from t1 where r1 = 4\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref
possible_keys: idx_r1,idx_u1
          key: idx_u1
      key_len: 5
          ref: const
         rows: 29324
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.01 sec)

debian-ytt1:ytt&gt;show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `ytt`.`t1`.`id` AS `id`,`ytt`.`t1`.`r1` AS `r1`,`ytt`.`t1`.`r2` AS `r2`,`ytt`.`t1`.`r3` AS `r3`,`ytt`.`t1`.`log_date` AS `log_date` from `ytt`.`t1` where (`ytt`.`t1`.`r1` = 4)
1 row in set (0.00 sec)

上面是对一条极其简单的SQL语句执行EXPLAIN的结果，有几个最直观的点需要关注：

possible_keys: 表示这条SQL 可能会用到的索引， MySQL 会把所有这条SQL有可能会用到的索引罗列到这里，最终基于当前关系表的统计信息、当前索引表的统计信息选择一个最优的索引来执行。
key: 表示这条SQL使用到的最终索引，如果不是NULL，就表示执行计划使用了指定的索引来执行这条SQL。比如这里用了索引idx_u1：这条索引是一个联合索引。
rows：表是执行这条SQL 需要大致扫描多少行才能出来结果，这里扫描了2.9W行。
Extra：额外的信息。这条SQL的EXTRA栏里是NULL，表示没有额外信息展示出来。
warnings: warnings 结果表示最终这条语句在MySQL的SQL解析层最终被转换后的SQL形式。

读到这里，可能会启发几个问题：

虽然这条SQL可以正确用到索引，但是查询性能依然没有达到我的要求，有没有什么方法可以做进一步优化？
虽然从结果可以看到这条SQL 扫描的大致行数，但是此值依然很大。比如这里是2.9W行，有办法减少吗？
还有就是如果EXTRA栏里的内容我知道了，我该怎么去二次优化？

针对这条SQL语句，如果单纯从语句写法上来看，我觉得已经无法二次优化，因为它已经匹配到了索引。现在的问题是MySQL用到的这个索引是否足够好？从执行计划结果来猜测：这个索引本身的选择性太差，导致仅执行一行等值检索，就要耗费大量的资源去扫描接近3W行的数据记录。

接下来，我们给这条语句做些变化，让他执行计划输出的EXTRA栏里有点内容，而且还是看起来需要优化的内容。比如"using filesort"：给这条SQL加条排序子句。

debian-ytt1:ytt&gt;desc  select * from t1 where r1 = 4 order by id desc\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref
possible_keys: idx_r1,idx_u1
          key: idx_u1
      key_len: 5
          ref: const
         rows: 29324
     filtered: 100.00
        Extra: Using filesort
1 row in set, 1 warning (0.00 sec)

接下来回答之前提到的几个问题：

第一个问题：选择了索引，这条SQL执行时间依然很长？

其实这个在我的专栏里索引设计那一块有讲过，并不是说SQL 语句使用了索引，查询性能就一定会很好，使用索引和查询性能提升两者并没有什么必然联系。比如这条SQL语句，执行计划结果显示用了索引idx_u1，但是基于这个索引还得扫描2.9W行记录才行。那我们来进一步看看这条索引在表t1里的基数值：

debian-ytt1:ytt&gt;select table_name,index_name,column_name,cardinality 
    -&gt; from information_schema.statistics 
    -&gt; where table_schema='ytt' and table_name='t1';
+------------+--------------+-------------+-------------+
| TABLE_NAME | INDEX_NAME   | COLUMN_NAME | CARDINALITY |
+------------+--------------+-------------+-------------+
| t1         | idx_log_date | log_date    |         335 |
| t1         | idx_r1       | r1          |           5 |
| t1         | idx_r2       | r2          |         100 |
| t1         | idx_r3       | r3          |         100 |
| t1         | idx_u1       | r1          |           5 |
| t1         | idx_u1       | r2          |         599 |
| t1         | idx_u1       | r3          |       50118 |
| t1         | PRIMARY      | id          |      102046 |
+------------+--------------+-------------+-------------+
8 rows in set (0.00 sec)

可以看到，索引idx_u1 是一个组合索引，字段r1在此索引里的基数只有5，而主键的基数是10W。索引idx_u1或者索引idx_r1的基数都非常低，也就是说对每一条对应的r1字段匹配，都会扫描很多行记录出来，进而导致基于字段r的索引可选择性非常差。所以此时针对这条SQL的优化，单单从语句层面已经没有办法继续下去，这种情况就需要DBA和业务一起联动来进一步讨论能否选择其他字段来做过滤条件、减少这条SQL的运行频次等手段来进行非常规优化。

第二个问题：如果索引选择合适或者说过滤条件很优化，那扫描行数也会相应减少。这条SQL 过滤条件非常简单，只有一个等值检索，对于关系表来讲，如果业务逻辑不改，还是用原来的索引来过滤，也算是优化到了尽头。

最后来到第三个问题：针对EXTRA栏里显示的必须要优化的信息进行二次优化。比如我们修改过的SQL， EXTRA栏显示 "using filesort" ，避免排序只需要把被排序字段加现有索引上即可，这样的好处是排序直接走索引表本身的顺序而不用二次排序。

debian-ytt1:ytt&gt;alter table t1 add key idx_r1_id_desc (r1,id desc);
Query OK, 0 rows affected (1.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

我们再重新看下执行计划：

debian-ytt1:ytt&gt;desc  select  * from t1  where r1 = 4 order by id desc\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref
possible_keys: idx_r1,idx_u1,idx_r1_id_desc
          key: idx_u1
      key_len: 5
          ref: const
         rows: 29324
     filtered: 100.00
        Extra: Using filesort
1 row in set, 1 warning (0.00 sec)

虽然我们加了新索引，不过MySQL依然选择索引idx_u1，"using filesort" 并没有拿掉。

这里只能证明一点：基于表t1当前的统计信息，执行计划选择了走索引idx_u1，而不是索引idx_r1_id_desc。也就是说针对这条SQL来讲，优化器综合判断排序比不排序反而要高效。至于为什么排序更高效，我们继续来看。

那强制用这个新索引的执行计划又是什么样呢？

debian-ytt1:ytt&gt;desc  select  * from t1  force index(idx_r1_id_desc) where r1 = 4 order by id desc\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref
possible_keys: idx_r1_id_desc
          key: idx_r1_id_desc
      key_len: 5
          ref: const
         rows: 32176
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

从执行计划结果来看，虽然避免了排序，但是扫描行数比用索引idx_u1要多。这里就在于要消耗更多CPU来排序、还是消耗更多IO来扫更多行两者之间做个权衡。从 EXPLAIN 结果暂时还看不到这些信息，可以更进一步用 EXPLAIN ANALYZE 语句来看这条SQL 选择不同索引的成本信息来做个对比：

debian-ytt1:ytt&gt;desc analyze select  * from t1  force index(idx_r1_id_desc) where r1 = 4 order by id desc\G
*************************** 1. row ***************************
EXPLAIN: -&gt; Index lookup on t1 using idx_r1_id_desc (r1=4)  (cost=3434.35 rows=32176) (actual time=0.225..32.290 rows=17093 loops=1)

1 row in set (0.04 sec)

debian-ytt1:ytt&gt;desc analyze select  * from t1  where r1 = 4 order by id desc\G
*************************** 1. row ***************************
EXPLAIN: -&gt; Sort: t1.id DESC  (cost=3149.15 rows=29324) (actual time=59.961..62.682 rows=17093 loops=1)
    -&gt; Index lookup on t1 using idx_u1 (r1=4)  (actual time=0.046..42.779 rows=17093 loops=1)

1 row in set (0.07 sec)

很显然，避免排序的成本为 3434.25，而排序的成本是3149.15，MySQL基于成本生成了最优匹配的执行计划，那我们新加的这个索引就没有意义，可以删掉了。

本篇就进行到这里，欢迎继续关注我的专栏，我将继续更新更多的SQL优化知识。

更多技术文章，请访问：https://opensource.actionsky.com/

关于 SQLE

SQLE 是一款全方位的 SQL 质量管理平台，覆盖开发至生产环境的 SQL 审核和管理。支持主流的开源、商业、国产数据库，为开发和运维提供流程自动化能力，提升上线效率，提高数据质量。

✨ Github：https://github.com/actiontech/sqle

📚 文档：https://actiontech.github.io/sqle-docs/

💻 官网：https://opensource.actionsky.com/sqle/

👥 微信群：请添加小助手加入 ActionOpenSource

🔗 商业支持：https://www.actionsky.com/sqle

微信关注我们

原文链接：https://my.oschina.net/actiontechoss/blog/16684752

转载内容版权归作者及来源网站所有！

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。

【直播预告】开发 AI 应用不难，FastGPT 作者手把手教

开源中国 OSCHINA的直播栏目《开源项目老牌与新秀》第1期，邀请了 FastGPT 的作者余金隆，手把手教大家怎么用 FastGPT 构建 AI 应用。 FastGPT 是一个基于 LLM 大模型的开源 AI 知识库构建平台，提供了开箱即用的数据处理、模型调用、RAG 检索、可视化 AI 工作流编排等能力，帮助用户轻松构建复杂的 AI 应用。自去年 3 月份开源以来，FastGPT 在 GitHub 的 Star 数直接冲到了 18.9K，相当火爆。直播主题：构建复杂 AI 应用，有开源项目 FastGPT 就够了直播时间：12月13日周五 19:00-20:00 直播平台：视频号“OSC 开源社区” 直播嘉宾：FastGPT 作者余金隆直播亮点： Star 高达 18.9k， FastGPT 是如何养成的？数据处理、模型调用、RAG 检索、可视化工作流编排，FastGPT 有多强？只需 15分钟，手把手教你写 AI 日报机器人定制 AI 客服，长字幕翻译，Dalle3 绘图，用 FastGPT 能做什么？揭秘 FastGPT 的未来迭代计划，展望技术发展新方向 Q&...

2024-12-12

278

当地时间12月10日，人工智能领域全球顶级学术会议 NeurIPS (Annual Conference on Neural Information Processing Systems) 在加拿大温哥华正式召开。官方数据显示，NeurIPS 2024共收到超一万五千篇有效论文投稿，投稿量创新高，参会人数超万人规模。阿里云 PAI 团队的论文《PertEval: Unveiling Real Knowledge Capacity of LLMs with Knowledge-Invariant Perturbations》被 NeurIPS D&B Track 2024 收录，并被列为 Spotlight，本年度 NeurIPS 的 Spotlight 论文录取率仅为3%。论文通过对大语言模型真实知识能力的评估，揭示现有静态评测集指标的可信性问题以及大语言模型知识掌握中的各种弱点，并指导改进的发展。同时，在本次会议中，PAI 团队为参会者带来主题演讲分享、AI 工程化平台产品能力 Demo。演讲分享的议题为“可信 AI 的技术解读与最佳实践”，深入研讨在大语言模型广泛应...

2024-12-12

251

资源下载

更多资源

优质分享App

近一个月的开发和优化，本站点的第一个app全新上线。该app采用极致压缩，本体才4.36MB。系统里面做了大量数据访问、缓存优化。方便用户在手机上查看文章。后续会推出HarmonyOS的适配版本。

腾讯云软件源

为解决软件依赖安装时官方源访问速度慢的问题，腾讯云为一些软件搭建了缓存服务。您可以通过使用腾讯云软件源站来提升依赖包的安装速度。为了方便用户自由搭建服务架构，目前腾讯云软件源站支持公网访问和内网访问。

Nacos

Nacos /nɑ:kəʊs/ 是 Dynamic Naming and Configuration Service 的首字母简称，一个易于构建 AI Agent 应用的动态服务发现、配置管理和AI智能体管理平台。Nacos 致力于帮助您发现、配置和管理微服务及AI智能体应用。Nacos 提供了一组简单易用的特性集，帮助您快速实现动态服务发现、服务配置、服务元数据、流量管理。Nacos 帮助您更敏捷和容易地构建、交付和管理微服务平台。

Rocky Linux

Rocky Linux（中文名：洛基）是由Gregory Kurtzer于2020年12月发起的企业级Linux发行版，作为CentOS稳定版停止维护后与RHEL（Red Hat Enterprise Linux）完全兼容的开源替代方案，由社区拥有并管理，支持x86_64、aarch64等架构。其通过重新编译RHEL源代码提供长期稳定性，采用模块化包装和SELinux安全架构，默认包含GNOME桌面环境及XFS文件系统，支持十年生命周期更新。