专栏首页MySQL故障优化案例MySQL Cases-执行计划向Oracle看齐(FORMAT=tree)
原创

MySQL Cases-执行计划向Oracle看齐(FORMAT=tree)

MySQL的执行计划一直遭人诟病,哪一步先执行,哪一步后执行,终于在 MySQL 8.0 版本中,通过命令 EXPLAIN 的额外选项,FORMAT=tree可以得到结果。

如下SQL语句,两个表关联,找出用户Customer#000001901的消费记录明细

select c.c_name,c.c_phone,o.o_totalprice,o.o_orderdate
    from tpch.orders o,tpch.customer c
    where c.c_custkey = o.o_custkey
    and c.c_name = 'Customer#000001901';

会SQL优化的你一定知道要在o.o_custkey或者c.c_custkey创建被驱动表的索引,就看哪个选择性更好,代价更小,查看表结构customer表如下,c_custkey为主键

CREATE TABLE `customer` (
  `c_custkey` int(11) NOT NULL,
  `C_NAME` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `C_ADDRESS` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `C_NATIONKEY` int(11) NOT NULL,
  `C_PHONE` char(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `C_ACCTBAL` decimal(15,2) NOT NULL,
  `C_MKTSEGMENT` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `C_COMMENT` varchar(117) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`c_custkey`),
  KEY `customer_fk1` (`C_NATIONKEY`),
  CONSTRAINT `customer_ibfk_1` FOREIGN KEY (`C_NATIONKEY`) REFERENCES `nation` (`n_nationkey`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

orders表如下,o_custkey为外键,同时要看下o_custkey的数据分布

CREATE TABLE `orders` (
  `o_orderkey` int(11) NOT NULL,
  `O_CUSTKEY` int(11) NOT NULL,
  `O_ORDERSTATUS` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `O_TOTALPRICE` decimal(15,2) NOT NULL,
  `O_ORDERDATE` date NOT NULL,
  `O_ORDERPRIORITY` char(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `O_CLERK` char(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `O_SHIPPRIORITY` int(11) NOT NULL,
  `O_COMMENT` varchar(79) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`o_orderkey`),
  KEY `orders_fk1` (`O_CUSTKEY`),
  CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`O_CUSTKEY`) REFERENCES `customer` (`c_custkey`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


select o_custkey,count(*) from orders group by o_custkey order by 2 desc;

数据分布均匀,且选择性很高,表数据6000000,,所以o_custkey作为被驱动表的关联列也很好

查看执行计划

explain select c.c_name,c.c_phone,o.o_totalprice,o.o_orderdate
    from tpch.orders o,tpch.customer c
    where c.c_custkey = o.o_custkey
    and c.c_name = 'Customer#000001901';

如下形式,看到这样的执行计划,在不分析SQL语句的情况下,哪一步先执行呢,一般情况下很难直接给出答案,尤其是之前做Oracle的DBA,如果3个表关联,甚至更多就更不好辨别了。

下面使用format=tree查看,树形结构和Oracle的执行计划就一样了,先执行c表 Table scan on c是不是类似于table access full

=_=哈哈,当然这个SQL需要优化,c.c_name的选择性也很好,需要在上面创建索引消除Filter c.C_NAME再过滤

explain format=tree select c.c_name,c.c_phone,o.o_totalprice,o.o_orderdate
    from tpch.orders o,tpch.customer c
    where c.c_custkey = o.o_custkey
    and c.c_name = 'Customer#000001901';

-> Nested loop inner join  (cost=804056.84 rows=806270)
    -> Filter: (tpch.c.C_NAME = 'Customer#000001901')  (cost=56580.20 rows=54858)
        -> Table scan on c  (cost=56580.20 rows=548582)
    -> Index lookup on o using orders_fk1 (O_CUSTKEY=tpch.c.c_custkey)  (cost=12.16 rows=15)

创建索引

create index i_customer_c_name on customer(c_name);

创建完索引后再次查看执行计划

-> Nested loop inner join  (cost=12.14 rows=15)
    -> Index lookup on c using i_customer_c_name (C_NAME='Customer#000001901')  (cost=0.35 rows=1)
    -> Index lookup on o using orders_fk1 (O_CUSTKEY=c.c_custkey)  (cost=11.79 rows=15)

看到了吗,cost也从804056.84降低到12.14,到这里有的同学会问,c.c_custkey是否需要创建索引呢,这就需要系统的学习优化知识了,先给出答案是不需要的。

关于8.0版本的format=tree的执行计划你学废了吗~是不是又有升级到8.0的冲动了~

更多文章欢迎关注本人公众号,搜dbachongzi或扫二维码

作者:姚崇 Oracle OCM、MySQL OCP、Oceanbase OBCA、PingCAP PCTA认证,擅长基于Oracle、MySQL Performance Turning及多种关系型 NoSQL数据库

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

关注作者,阅读全部精彩内容

我来说两句

0 条评论
登录 后参与评论

相关文章

  • MySQL Cases-SQL导致CPU使用率100%处理

    看到这个条SQL写法还是有问题的,我按照他的意思做了改写,我们先分析他要的语句的逻辑

    姚崇
  • MYSQL with Explain analyze 好马配好鞍?

    随着曾经的一期MYSQL来自“旧金山的信息”中,MYSQL 8 大举更改数据库的优化器的事情已经是在目前版本上大举实现的事情了。而上期说的一些ORACLE 认为...

    AustinDatabases
  • MySQL 8.0之hash join

    首先对于熟悉Oracle 的DBA 来说,hash join并不陌生,尤其涉及到多个表join时 执行计划出现 hash join ,一般来说hash join...

    用户1278550
  • MySQL8.0发布,你熟悉又陌生的Hash Join?

    昨天下午在查资料的时候,无意间点到了MySQL的doc。发现MySQL发布了一个新版本。

    王知无-import_bigdata
  • MySQL 8.0 新特性:引人注目的哈希连接(Hash Join)

    blog.csdn.net/horses/article/details/102690076

    肉眼品世界
  • 如何快速定位当前数据库消耗 CPU 最高的 SQL 语句?

    来源:https://www.toutiao.com/i6923526305795293707?wid=1623686217615

    用户1516716
  • MySQL Case-带你感受Oracle与MySQL下SQL执行效率

    Oracle中表结构如下,表中600万数据,测试表为分区表,这没关系,因为查询表中所有的数据,和单表一样

    姚崇
  • MySQL EXPLAIN ANALYZE

    MySQL8.0.18刚刚发布,它包含一个全新的功能EXPLAIN ANALYZE,用来分析和理解查询如何执行。

    MySQLSE
  • EXPLAIN FORMAT=json和EXPLAIN ANALYZE查询计划解读

    通常EXPLAIN用于获取QEP,而DESCRIBE、DESC用于获取表结构信息。

    chenchenchen
  • 查看MySQL执行计划的几种方法以及格式说明

    我们知道,执行计划是关系型数据库诊断SQL性能问题很重要的一种手段,Oracle中获取执行计划有很多种方式,不同方式有各自的优缺点,可以参考《查询执行计划的几种...

    bisal
  • 查看MySQL执行计划的几种方法以及格式说明

    我们知道,执行计划是关系型数据库诊断SQL性能问题很重要的一种手段,Oracle中获取执行计划有很多种方式,不同方式有各自的优缺点,可以参考《查询执行计划的几种...

    bisal
  • 【图文动画详解原理系列】1.MySQL 索引原理详解

    MySQL是一个开放源代码的关系数据库管理系统。原开发者为瑞典的MySQL AB公司,最早是在2001年MySQL3.23进入到管理员的视野并在之后获得广泛的应...

    一个会写诗的程序员
  • PostgreSql explain 三观正,挺好用

    查看数据库中执行SQL的执行计划,及相关信息是每个数据库都有的功能,PostgreSQL explain 的功能很丰富,下面就看看,这个explain 有什么过...

    AustinDatabases
  • HIVE入门_2

    HIVE 是数据仓库,本质上也是数据库。 数据仓库 概念 就是一个数据库。 数据仓库是一个面向主题的(商品的推荐系统内容是商品的信息)、集成的(分散型地...

    用户1147754
  • Christina问我:你都是如何设计索引的?

    数据库系列更新到现在我想大家对所有的概念都已有个大概认识了,这周我在看评论的时候我发现有个网友的提问我觉得很有意思:帅丙如何设计一个索引?你们都是怎么设计索引的...

    敖丙
  • MySQL 8.0.16 release notes

    步入正题,我们了解一下MySQL最新版本的功能特性以及Percona 发布的备份工具的最新版本有哪些特性。

    用户1278550
  • 小白学习MySQL - 查询会锁表?

    我们知道,Oracle中除了使用select ... for update,其他查询语句不会出现锁,即没有读锁,读一致性通过多版本解决的,可以保证在不加锁的情况...

    bisal
  • MYSQL explain 可没有那么简单,explain的猫腻与函数

    explain 到底会不会执行命令着一点很多人应该是不置可否的任务,他不能执行命令,而仅仅是对语句进行评估然后反馈执行的计划。

    AustinDatabases
  • Apache Calcite项目简介

    Apache Calcite是一个动态数据管理框架,它具备很多典型数据库管理系统的功能,比如SQL解析、SQL校验、SQL查询优化、SQL生成以及数据连接查询等...

    叁金

扫码关注云+社区

领取腾讯云代金券