专栏首页moon聊技术mysql┃explain 都不会用?怎么优化?

mysql┃explain 都不会用?怎么优化?

目录


  • 目录
  • 前言
  • 正文
    • 什么是explain?
    • explaing该怎么使用
    • explain输出格式字段详解
  • 结语

前言


现在的java开发人员越来越多,竞争也越来越激烈,moon在某钩招聘网站上发布了一个岗位需求,不到短短1天就收到20多份简历,大部分都是应届一年两年的,新鲜血液越来越多,我们也要不断的提升自己才能够不被挤下去,大家可以看下各大网站的java岗位3年以上的招聘需求:

<<< 左右滑动见更多 >>>

大部分java开发岗位都会有写到数据库相关,会把sql优化作为一个重要的岗位要求之一,由此可见sql优化的重要性。 mysql调优是一块很大的挑战,并且有很多维度可以优化比如事务方面,表结构方面等等。 今天我们就来聊一聊最基本的,如何针对某条sql语句优化,以及explain执行计划的使用。

正文


什么是explain?

explain是MYSQL提供的一个命令,它可以用来分析select语句,并且输出相应的分析结果,使得开发人员可以针对输出结果来对sql语句进行特定的优化。

explaing该怎么使用

explain的使用很简单,只需要在s需要执行的语句前加上explain就可以了

例如

explain select gab_table_id from gravity_plan_operate_record where ID = 7

explain输出格式字段详解

我们准备两张表

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL DEFAULT '',
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `order` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) DEFAULT '0',
  `product_name` varchar(50) NOT NULL DEFAULT '',
  `productor` varchar(30) DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `idx_user_id_product_name` (`user_id`,`product_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

我们在这两张表中都插入一些数据

INSERT INTO user (name, age) VALUES ('xy是的s', 11);
INSERT INTO user (name, age) VALUES ('a的撒风', 10);
INSERT INTO user (name, age) VALUES ('asdfb', 13);
INSERT INTO user (name, age) VALUES ('csadf', 20);
INSERT INTO user (name, age) VALUES ('dsaf', 14);
INSERT INTO user (name, age) VALUES ('exzcv', 44);
INSERT INTO user (name, age) VALUES ('fgw', 18);
INSERT INTO user (name, age) VALUES ('gbht', 17);
INSERT INTO user (name, age) VALUES ('hbfd', 14);
INSERT INTO user (name, age) VALUES ('ibdfsb', 15);
INSERT INTO tuya_gravity.order (user_id, product_name, productor) VALUES (111, 'qqq', 'aaa');
INSERT INTO tuya_gravity.order (user_id, product_name, productor) VALUES (111, '234', 'aaa');
INSERT INTO tuya_gravity.order (user_id, product_name, productor) VALUES (1333, 'qqq', 'cc');
INSERT INTO tuya_gravity.order (user_id, product_name, productor) VALUES (222, '444', 'aaa');
INSERT INTO tuya_gravity.order (user_id, product_name, productor) VALUES (2222, '555', 'cc');
INSERT INTO tuya_gravity.order (user_id, product_name, productor) VALUES (333, '444', 'dd');
INSERT INTO tuya_gravity.order (user_id, product_name, productor) VALUES (333, 'qerwq', 'cc');
INSERT INTO tuya_gravity.order (user_id, product_name, productor) VALUES (643, 'vdvd', 'qq');
INSERT INTO tuya_gravity.order (user_id, product_name, productor) VALUES (922, 'vdvd', 'qq');

左边为 order表 右边为 user表

我们看下explain到底会输出哪些东西

explain select * from user where id = 4

下图就是explain输出的内容,我们来一一解析下

各列的含义如下:

  • id: SELECT 查询的标识符
  • select_type: SELECT 查询的类型.
    • simple:进行不需要Union操作或不含子查询的简单select查询时,响应查询语句的select_type 即为simple
    • primary:一个需要Union操作或含子查询的select查询执行计划中,位于最外层的select_type即为primary。与simple一样,select_type为primary的单位select查询也只存在1个,位于查询最外侧的select单位查询的select_type为primary
    • union:由union操作联合而成的单位select查询中,除第一个外,第二个以后的所有单位select查询的select_type都为union。union的第一个单位select的select_type不是union,而是DERIVED。它是一个临时表,用于存储联合(Union)后的查询结果。
    • DEPENDENT UNION dependent:与UNION select_type一样,dependent union出现在union或union all 形成的集合查询中。此处的dependent表示union或union all联合而成的单位查询受外部影响。下列查询中,两个select 查询用union联合起来,所一union出阿信在select_type中,从in所包含的子查询中可以看到,两个查询通过union连接在一起。MariaDB中,不会在默认优化器模式下先处理IN(subquery)查询内部的子查询,而是读取外部的employees数据表,再执行子查询时,dependent关键字就会出现在select_type中。
    • union result:union result为包含union结果的数据表。MariaDB中,union all或union(DISTINCT)查询会将所有union结果创建为临时表。执行计划中,该临时表所在行为select_type为union result。由于union result在实际查询中不是单位查询,所以没有单独的id值。
    • SUBQUERY:子查询中第一个select
    • DEPENDENT SUBQUERY:子查询中的第一个 SELECT, 取决于外面的查询. 即子查询依赖于外层查询的结果.
  • table: 查询的是哪个表
  • partitions: 匹配的分区
  • type: 通过 type 字段, 我们判断此次查询是 全表扫描 还是 索引扫描 等.
    • system:表中只有一条数据. 这个类型是特殊的 const 类型.
    • const:针对主键或唯一索引的等值查询扫描, 最多只返回一行数据. const 查询速度非常快, 因为它仅仅读取一次即可. 例如下面的这个查询, 它使用了主键索引, 因此 type 就是 const 类型的.
explain select * from user where id = 4
  • eq_ref:此类型通常出现在多表的 join 查询, 表示对于前表的每一个结果, 都只能匹配到后表的一行结果. 并且查询的比较操作通常是 =, 查询效率较高. 例如:
EXPLAIN SELECT * FROM user, tuya_gravity.order WHERE user.id = tuya_gravity.order.user_id
  • ref:此类型通常出现在多表的 join 查询, 针对于非唯一或非主键索引, 或者是使用了 最左前缀 规则索引的查询. 例如下面这个例子中, 就使用到了 ref 类型的查询:
EXPLAIN SELECT * FROM user, tuya_gravity.order WHERE user.id = tuya_gravity.order.user_id AND tuya_gravity.order.user_id = 5
  • range:表示使用索引范围查询, 通过索引字段范围获取表中部分数据记录. 这个类型通常出现在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中. 当 type 是 range 时, 那么 EXPLAIN 输出的 ref 字段为 NULL, 并且 key_len 字段是此次查询中使用到的索引的最长的那个.
EXPLAIN SELECT * FROM user WHERE id BETWEEN 1 AND 11
  • index:表示全索引扫描(full index scan), 和 ALL 类型类似, 只不过 ALL 类型是全表扫描, 而 index 类型则仅仅扫描所有的索引, 而不扫描数据. index 类型通常出现在: 所要查询的数据直接在索引树中就可以获取到, 而不需要扫描数据. 当是这种情况时, Extra 字段 会显示 Using index.
EXPLAIN SELECT name FROM user
  • all:表示全表扫描, 这个类型的查询是性能最差的查询之一. 通常来说, 我们的查询不应该出现 ALL 类型的查询, 因为这样的查询在数据量大的情况下, 对数据库的性能是巨大的灾难. 如一个查询是 ALL 类型查询, 那么一般来说可以对相应的字段添加索引来避免. 下面是一个全表扫描的例子, 可以看到, 在全表扫描时, possible_keys 和 key 字段都是 NULL, 表示没有使用到索引, 并且 rows 十分巨大, 因此整个查询效率是十分低下的.
EXPLAIN SELECT age FROM user WHERE age = 20
  • possible_keys: 此次查询中可能选用的索引
  • key: 此次查询中确切使用到的索引.
  • key_len: 表示查询优化器使用了索引的字节数. 这个字段可以评估组合索引是否完全被使用, 或只有最左部分字段被使用到.
  • ref: 哪个字段或常数与 key 一起被使用
  • rows: 显示此查询一共扫描了多少行. 这个是一个估计值.
  • filtered: 表示此查询条件所过滤的数据的百分比
  • extra: 额外信息
    • Using filesort:当 Extra 中有 Using filesort 时, 表示 MySQL 需额外的排序操作, 不能通过索引顺序达到排序效果. 一般有 Using filesort, 都建议优化去掉, 因为这样的查询 CPU 资源消耗大.
EXPLAIN SELECT id FROM tuya_gravity.order ORDER BY product_name

这个时候我们是没有用到索引的,如果改成ORDER BY user_id, product_name就可以利用我们的最左前缀原则用到索引了,例如

EXPLAIN SELECT id FROM tuya_gravity.order ORDER BY user_id,product_name
  • Using index:"覆盖索引扫描", 表示查询在索引树中就可查找所需数据, 不用扫描表数据文件, 往往说明性能不错
  • Using temporary:查询有使用临时表, 一般出现于排序, 分组和多表 join 的情况, 查询效率不高, 建议优化.

结语

今天moon和大家聊了在sql优化中expalin该去怎么使用,每个字段的意义,也和大家简单的聊了下语句的优化方案,当然,这只是sql优化的第一步而已,在很多复杂语句的优化上要考虑很多因素,也有很多方式,比如拆字段,分表,索引重构等等,而每一种解决方案都会面临着不同的问题,以后有机会也会和大家聊到这方面。

本文分享自微信公众号 - moon聊技术(onetraveller_llxz),作者:moon聊技术

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-10-30

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 学弟问我:explain 很重要吗?

    哈喽,小伙伴们好呀。我是狗哥,今天打算跟大家聊聊一个很基础的 MySQL 命令 —— explain。这个命令相信很多小伙伴都熟悉并且几乎每天都会使用,反正我是...

    一个优秀的废人
  • explain | 索引优化的这把绝世好剑,你真的会用吗?

    对于互联网公司来说,随着用户量和数据量的不断增加,慢查询是无法避免的问题。一般情况下如果出现慢查询,意味着接口响应慢、接口超时等问题。如果是高并发的场景,可能会...

    苏三说技术
  • explain | 索引优化的这把绝世好剑,你真的会用吗?

    对于互联网公司来说,随着用户量和数据量的不断增加,慢查询是无法避免的问题。一般情况下如果出现慢查询,意味着接口响应慢、接口超时等问题。如果是高并发的场景,可能会...

    PHP开发工程师
  • 大厂面试系列(八):数据库mysql相关

    zhaozhen
  • 如何分析一条sql的性能

    网上其实已经有非常多的文章都很详细的介绍了 explain 的使用,这篇文章将实例和原理结合起来,尽量让你有更好的理解,相信我,认真看完你应该会有特别的收获。

    JAVA葵花宝典
  • 如何分析一条sql的性能

    网上其实已经有非常多的文章都很详细的介绍了 explain 的使用,这篇文章将实例和原理结合起来,尽量让你有更好的理解,相信我,认真看完你应该会有特别的收获。

    谭小谭
  • 如何分析一条sql的性能

    网上其实已经有非常多的文章都很详细的介绍了 explain 的使用,这篇文章将实例和原理结合起来,尽量让你有更好的理解,相信我,认真看完你应该会有特别的收获。

    秃头哥编程
  • MySQL 查询分析

    本文主要由一个案例引发对 MySQL 性能问题的思考,主要讲述 MySQL 慢查询和 explain 工具这两个定位 MySQL 性能瓶颈的方法。

    谢庆玲
  • MySQL - 索引优化案例实操

    当然了,也不是所有的情况都不走索引, MySQL会基于Cost选择一个合适的 ,如果没有走索引,可能mysql内部可能觉得第一个字段就用范围,结果集应该很大,回...

    小小工匠
  • MySQL 5.6,5.7的优化器对于count(*)的处理方式

    最近看了很多阿里同学的MySQL文章,阿里内核同学的文章一言不合就上代码,不光让我们看到了结果,还能有代码可读,如果碰到了类似的问题,这样的解读确实是很难...

    jeanron100
  • MySQL优化的奇技淫巧之STRAIGHT_JOIN

    通过「SHOW FULL PROCESSLIST」语句很容易就能查到问题SQL,如下:

    后端技术探索
  • MySQL之Explain详解

    一条查询语句在经过MySQL查询优化器的各种基于成本和规则的优化会后生成一个所谓的执行计划,这个执行计划展示了接下来具体执行查询的方式,比如多表连接的顺序是什么...

    Bug开发工程师
  • 搞懂 MySQL Explain 命令之前不要说自己会SQL优化

    MySQL explain 命令是查询性能优化不可缺少的一部分,该文主要讲解 explain 命令的使用及相关参数说明。

    Guide哥
  • mysql执行计划看是否最优

    介绍   本篇主要通过汇总网上的大牛的知识,简单介绍一下如何使用mysql的执行计划,并根据执行计划判断如何优化和是否索引最优。   执行计划可显示估计查询语...

    用户1217611
  • 不会看 Explain执行计划,劝你简历别写熟悉 SQL优化

    昨天中午在食堂,和部门的技术大牛们坐在一桌吃饭,作为一个卑微技术渣仔默默的吃着饭,听大佬们高谈阔论,研究各种高端技术,我TM也想说话可实在插不上嘴。

    程序员内点事
  • 面试官:不会看 Explain执行计划,简历敢写 SQL 优化?

    昨天中午在食堂,和部门的技术大牛们坐在一桌吃饭,作为一个卑微技术渣仔默默的吃着饭,听大佬们高谈阔论,研究各种高端技术,我TM也想说话可实在插不上嘴。

    猿天地
  • 大数据技术之_29_MySQL 高級面试重点串讲_02

      MySQL 是一个关系型数据库管理系统,由瑞典 MySQL AB 公司开发,目前属于 Oracle 公司。

    黑泽君
  • java架构之路(三)Mysql之Explain使用详解

      上篇博客,我们详细的说明了mysql的索引存储结构,也就是我们的B+tree的变种,是一个带有双向链表的B+tree。那么我今天来详细研究一下,怎么使用索引...

    小菜的不能再菜
  • MySQL SQL 优化命令行&问题 SQL 抓取方式

    对于数据库来说安装,部署几乎是一次性的。后期的管理和优化是持续性的工作。 对于MySQL来说,可以说90%问题都在SQL语句上面。从问题SQL的筛选和优化,在M...

    数据和云

扫码关注云+社区

领取腾讯云代金券