神奇的 SQL 之 MySQL 性能分析神器 → EXPLAIN,SQL 起飞的基石!

  • 时间:
  • 浏览:0
  • 来源:大发pk10_pk10IOS下载_大发pk10IOS下载

前言

  开心一刻

    某人养了一头猪,烦了想放生,假如有一天 猪认识回家的路,放生几块它都全都人回来了。一日,你这全都人想了个狠方法,开车带着猪转了好多路进山区放生,放生后又各种打转,假如有一天掏出电话我想要家人打了个电话,问道:“猪回去什么刚刚?”,你家人:“早回来了,你在哪了,要怎样会会还没回来?”,他大怒道:“让它来接我,我特么迷路了!!!”

还不如我了

背景

  某一天,楼主打完上班卡,坐在工位逛园子的我想要,右下角的 QQ 闪了起来,假如有一天还是个美女头像!我又惊又喜,脑中闪过我所认识的机会联系我的老婆,得出个结论:她们这会不机会联系我呀,图像也没映象,到底是谁了?打开聊天窗口聊了起来

  她:您好,我是公司客服某某某,请问 xxx后台 是您负责的吗?

  我:您好,是我负责的,有哪几块问题报告 吗?

  她:我发现 xxx 页面点查询后,老但是 加载中... ,数据一直出不来,能帮忙看看吗?

  我:是都有您的姿势不对?

  她:我想要 xxx,假如有一天点查询

  我:骚等下,我试试,觉得有点硬慢,很长时间不可不后能 出来

  她:是的,太慢了,出不来,都急死我了,能快点吗?

  我:肯定能、上可不后能 !您觉得哪几块速率单位让您觉得最舒服?

  她:太快了 越好吧

  我:呃...,是吗,我先看看是哪几块问题报告 ,解决好了告诉您,保证让您觉得舒服!

  她:好的,谢谢!

  公司没法专门的搜索服务,都有直接从 MySQL 查询,做简单的数据解决后返回给页面,慢的是因为肯定假如有一天 SQL 查询了。找到对应的查询 SQL ,假如有一天一个表的联表查询,连接键都有索引,WHERE 条件不可不后能 走索引,要怎样会会会慢了?假如有一天我用 EXPLAIN 想看 下这条 SQL 的执行计划,找到了慢的是因为,具体是因为中间揭晓(谁我想要都有猪脚!)

EXPLAIN 是哪几块

  它是 MySQL 的一个命令,用来查看 SQL 的执行计划(SQL 要怎样执行),根据其输出结果,我门我门不想可不后能 知道以下信息:表的读取顺序,数据读取类型,哪几块索引可不后能 使用,哪几块索引实际使用了,表之间的连接类型,每张表有几块行被优化器查询等信息,根据哪几块信息,我门我门可不后能 找出 SQL 慢的是因为,并做针对性的优化

  MySQL 5.6 我想要的版本,EXPLAIN 非要用于查看 SELECT 的执行计划,而从 MySQL 5.6 结束英语 ,可不后能 查看 SELECT 、 DELETE 、 INSERT 、 REPLACE 和 UPDATE 的执行计划,这可都有我瞎掰,不信的可不后能 去 MySQL 的官网查看:Understanding the Query Execution Plan

  EXPLAIN 使用方法非常简单,简单的你都有敢相信,假如有一天在我门我门常写的 SELECT 、 DELETE 、 INSERT 、 REPLACE 和 UPDATE 一段话我想要再加 EXPLAIN 即可

EXPLAIN SELECT * FROM mysql.`user`;

EXPLAIN DELETE FROM t_user WHERE user_name = '123';

  莫看 EXPLAIN 短,但它胖呀

觉得有点硬婴儿肥,但也掩不住我逼人的帅气!

  觉得 EXPLAIN 使用起来非常简单,但它的输出结果中信息量非常大,觉得我胖,但我肚包含货呀!

环境和数据准备

  MySQL 版本是 5.7.2 ,存储引擎是 InnoDB 

-- 查看 MySQL 版本
SELECT VERSION();

-- MySQL 提供哪几块存储引擎
SHOW ENGINES;

-- 查看默认存储引擎
SHOW VARIABLES LIKE '%storage_engine%';

  准备两张表:用户表 tbl_user 和用户登录记录表 tbl_user_login_log ,并初始化每项每项数据

-- 表创建与数据初始化
DROP TABLE IF EXISTS tbl_user;
CREATE TABLE tbl_user (
  id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  user_name VARCHAR(30) NOT NULL COMMENT '用户名',
  sex TINYINT(1) NOT NULL COMMENT '性别, 1:男,0:女',
  create_time datetime NOT NULL COMMENT '创建时间',
  update_time datetime NOT NULL COMMENT '更新时间',
    remark VARCHAR(255) NOT NULL DEFAULT '' COMMENT '备注',
  PRIMARY KEY (id)
) COMMENT='用户表';

DROP TABLE IF EXISTS tbl_user_login_log;
CREATE TABLE tbl_user_login_log (
  id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  user_name VARCHAR(30) NOT NULL COMMENT '用户名',
  ip VARCHAR(15) NOT NULL COMMENT '登录IP',
  client TINYINT(1) NOT NULL COMMENT '登录端, 1:android, 2:ios, 3:PC, 4:H5',
  create_time datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (id)
) COMMENT='登录日志';
INSERT INTO tbl_user(user_name,sex,create_time,update_time,remark) VALUES
('何天香',1,NOW(), NOW(),'朗眉星目,一表人材'),
('薛沉香',0,NOW(), NOW(),'天星楼的总楼主薛摇红的女儿,也是天星楼的少总楼主,体态丰盈,乌发飘逸,指若春葱,袖臂如玉,风姿卓然,高贵典雅,人称“天星绝香”的武林第一大美女'),
('慕容兰娟',0,NOW(), NOW(),'武林东南西北四大世家之北世家慕容长明的独生女儿,生得玲珑剔透,粉雕玉琢,脾气却是刚烈无比,又喜着火红,全都人送绰号“火凤凰”,是除天星楼薛沉香之外的武林第二大美女'),
('苌婷',0,NOW(), NOW(),'当今皇上最宠爱的侄女,北王府的郡主,腰肢纤细,遍体罗绮,眉若墨画,唇点樱红;虽无沉香之雅重,兰娟之热烈,却别现出两种空灵'),
('柳含姻',0,NOW(), NOW(),'武林四绝之一的添愁仙子董婉婉的徒弟,体态窈窕,姿容秀丽,真个是秋水为神玉为骨,芙蓉如面柳如腰,眉若墨画,唇若点樱,不弱西子半分,更胜玉环一筹; 摇红楼、听雨轩,琵琶一曲值千金!'),
('李凝雪',0,NOW(), NOW(),'李相国的女儿,神采奕奕,英姿飒爽,爱憎分明'),
('周遗梦',0,NOW(), NOW(),'音神传人,湘妃竹琴的拥有者,云髻高盘,穿了一身黑色蝉翼纱衫,愈觉得冰肌玉骨,粉面樱唇,格外娇艳动人'),
('叶留痕',0,NOW(), NOW(),'圣域圣女,肤白如雪,白衣飘飘,宛如仙女一般,微笑中带着说不在

的柔和之美'),
('郭疏影',0,NOW(), NOW(),'扬灰右使的徒弟,秀发细眉,玉肌丰滑,娇润脱俗'),
('钟钧天',0,NOW(), NOW(),'天界,玄天九部 - 钧天部的部主,超凡脱俗,仙气逼人'),
('王雁云',0,NOW(), NOW(),'尘缘山庄二小姐,刁蛮任性'),
('许侍霜',0,NOW(), NOW(),'药王谷谷主女儿,医术高明'),
('冯黯凝',0,NOW(), NOW(),'桃花门门主,娇艳如火,千娇百媚');
INSERT INTO tbl_user_login_log(user_name, ip, client, create_time) VALUES
('薛沉香', '10.53.56.78',2, '2019-10-12 12:23:45'),
('苌婷', '10.53.56.78',2, '2019-10-12 22:23:45'),
('慕容兰娟', '10.53.56.12',1, '2018-08-12 22:23:45'),
('何天香', '10.53.56.12',1, '2019-10-19 10:23:45'),
('柳含姻', '198.11.132.198',2, '2018-05-12 22:23:45'),
('冯黯凝', '198.11.132.198',2, '2018-11-11 22:23:45'),
('周遗梦', '198.11.132.198',2, '2019-06-18 22:23:45'),
('郭疏影', '220.181.38.148',3, '2019-10-21 09:45:56'),
('薛沉香', '220.181.38.148',3, '2019-10-26 22:23:45'),
('苌婷', '104.69.130.30',4, '2019-10-12 10:23:45'),
('王雁云', '104.69.130.61',4, '2019-10-16 20:23:45'),
('李凝雪', '104.69.130.62',4, '2019-10-17 20:23:45'),
('许侍霜', '104.69.130.63',4, '2019-10-18 20:23:45'),
('叶留痕', '104.69.130.64',4, '2019-10-19 20:23:45'),
('王雁云', '104.69.130.65',4, '2019-10-20 20:23:45'),
('叶留痕', '104.69.130.66',4, '2019-10-21 20:23:45');

SELECT * FROM tbl_user;
SELECT * FROM tbl_user_login_log;
View Code

EXPLAIN 输出格式概览

  楼主再不讲重点,估计全都看官老爷找他的 2 米长的大砍刀去了

  没法滴,我门我门先来看看 EXPLAIN 输出结果的为宜,是都有长得满脸麻子,我想要门望而生畏 ?

  白白净净的,挺好,关键长啊! 官方解释如下

EXPLAIN 输出格式详解

  EXPLAIN 的输出字段觉得有点硬多,但常关注的就没法几块,但楼主秉着负责的态度,都给我门我门讲一下,可不后能 重点关注的字段,楼主也会标明滴

  EXPLAIN 支持的 SQL 一段话有好几种,但工作中用的最多的还是 SELECT ,全都楼主就偷个懒,以 SELECT 来讲解 EXPLAIN,有兴趣的老爷去试试全都的

  id

    输出的是整数,用来标识整个 SQL 的执行顺序。id 机会相同,从上往下依次执行;id不同,id 值越大,执行优先级越高,越先被执行;机会行引用全都行的并集结果,则该值可不后能 为NULL

    不重要,有所了解就好(觉得非常简单,想看 基本就能记住了)

  select_type

    查询的类型,官方说明如下

    简单帮我门我门翻译一下(有能力的去读官网,毕竟那是原配,最具权威性)

    SIMPLE:简单的 SELECT 查询,没法 UNION 机会子查询,包括单表查询机会多表 JOIN 查询

    PRIMARY: 最外层的 select 查询,常见于子查询或 UNION 查询 ,最外层的查询被标识为 PRIMARY

    UNION:UNION 操作的第六个或我想要的 SELECT,不依赖于内控 查询的结果集(内控 查询指的假如有一天 PRIMARY 对应的 SELECT

    DEPENDENT UNION:UNION 操作的第六个或我想要的 SELECT,依赖于内控 查询的结果集

    UNION RESULT:UNION 的结果(机会是 UNION ALL 则无此结果)

    SUBQUERY:子查询中的第一个 SELECT 查询,不依赖于内控 查询的结果集

    DEPENDENT SUBQUERY:子查询中的第一个select查询,依赖于内控 查询的结果集

    DERIVED:派生表(临时表),常见于 FROM 子句包含子查询的具体情况

      注意:MySQL5.7 中对 Derived table 做了一个新形状,该形状允许将符合条件的 Derived table 中的子表与父查询的表合并进行直接JOIN,从而僵化 僵化 了执行计划,一齐也提高了执行速率单位;默认具体情况下,MySQL5.7 中你你什儿 形状是开启的,全都默认具体情况下,中间的 SQL 的执行计划应该是假如有一天 的

      可通过 SET SESSION optimizer_switch='derived_merge=on|off' 来开启或关闭当前 SESSION 的该形状。貌似扯的有点硬远了(楼主你是都有在随性发挥?),更多详情可不后能 去查阅官网

    MATERIALIZED:被物化的子查询,MySQL5.6 引入的两种新的 select_type,主假如有一天优化 FROM 或 IN 子句中的子查询,更多详情请查看:Optimizing Subqueries with Materialization

    UNCACHEABLE SUBQUERY:对于外层的主表,子查询不可被缓存,每次都可不后能 计算

    UNCACHEABLE UNION:这类于 UNCACHEABLE SUBQUERY,假如有一天再次再次出现在 UNION 操作中

    SIMPLLE、PRIMARY、SUBQUERY、DERIVED 这 4 个在实际工作中碰到的会比较多,看得懂这 4 个就行了,至于全都的,碰到了再去查资料就好了(我也想完整性记住,但用的少,太容易忘记了,我也很无赖呀)

  table

    显示了对应行正在访问哪个表(有别名就显示别名),都会有 <union2,3> 、 <subquery2> 、 <derived2> (这里的 2,3、2、2 指的是 id 列的值)这类的值,具体可不后能 往上看,这里就不演示了(再演示就太长了,我门我门想看 不下去了,假如有一天 都有白忙乎了 ?)

  partitions

    查询进行匹配的分区,对于非分区表,该值为NULL。大多数具体情况下用非要分区,全都你你什儿 列我门我门不想关注

  type

    关联类型机会访问类型,它指明了 MySQL 决定要怎样查找表中符合条件的行,这是我门我门判断查询是有无高效的重要方法(type 之于 EXPLAIN,就好比三围之于老婆!),完整性介绍请看:explain-join-types

    其值有多种,我门我门以性能好到性能差的顺序一个一个来看     

    system

      该表非要一行(=系统表),是 const 类型的特例

    const

      确定非要一行匹配的我想要,mysql 优化器会在查询前读取它假如有一天只读取一次,速率单位非常快。用于 primary key 或 unique 索引包含常亮值比较的具体情况

    eq_ref

      对于每个来自于前面的表的行,从该表最多只返回一条符合条件的记录。当连接使用的索引是 PRIMARY KEY 或 UNIQUE NOT NULL 索引时使用,非常高效

    ref

      索引访问,也称索引查找,它返回所有匹配某个单个值的行。此类型通常再次再次出现在多表的 JOIN 查询, 针对于非 UNIQUE 或非 PRIMARY KEY, 机会是使用了最左前缀规则索引的查询,换句话说,机会 JOIN 非要基于关键字确定单个行一段话,则使用ref

    fulltext

      当使用全文索引都会用到,你你什儿 索引一般用非要,会用专门的搜索服务(solr、elasticsearch等)来替代

    ref_or_null

      这类ref,假如有一天再加了可不后能 专门搜索 NULL 的行

      你你什儿 是有前提条件的,前提为 weapon 列有索引,且 weapon 列发生  NULL 

    index_merge

      该访问类型使用了索引合并优化方法

      你你什儿 同样也是有条件的, id 列和 weapon 列都有单列索引。机会再次再次出现 index_merge,假如有一天这类 SQL 后期使用较频繁,可不后能 考虑把单列索引换为组合索引,假如有一天 速率单位更高

    unique_subquery

      这类于两表连接中被驱动表的 eq_ref 访问方法,unique_subquery 是针对在全都包含 IN 子查询的查询一段话中,机会查询优化器决定将 IN 子查询转换为 EXISTS 子查询,假如有一天子查询可不后能 使用到主键机会唯一索引进行等值匹配时,则会使用 unique_subquery

    index_subquery

      index_subquery 与 unique_subquery这类,只不过访问子查询中的表时使用的是普通的索引

    range

      使用索引来检索给定范围的行,当使用 =、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN 机会 IN 操作符,用常量比较关键字列时,则会使用 rang

      前提是可不后能 基于索引,也假如有一天 id 上可不后能 有索引

    index

      当我门我门可不后能 使用索引覆盖,但可不后能 扫描完整性的索引记录时,则会使用 index;进行统计时非常常见

    ALL

      我门我门熟悉的全表扫描

  possible_keys

    展示在你你什儿 SQL 中,机会用到的索引有哪几块,但不一定在查询时使用。若为空则表示没法可不后能 使用的索引,此时可不后能 通过检查 WHERE 一段话看是有无可不后能 引用全都列机会新建索引来提高性能

  key

    展示你你什儿 SQL 实际使用的索引,机会没法确定索引,则此列为null,要想强制 MySQL 使用或忽视 possible_keys 列中的索引,在查询中使用 FORCE INDEX、USE INDEX 机会I GNORE INDEX

  key_len

    展示 MySQL 决定使用的键长度(字节数)。机会 key 是 NULL,则长度为 NULL。在不损失精确性的具体情况下,长度越短越好

  ref

    展示的是与索引列作等值匹配的东东是个啥,比如假如有一天一个常数机会是某个列。它显示的列的名字(或const),此列多数我想要为 Null

  rows

    展示的是 mysql 解析器认为执行此 SQL 时预计可不后能 扫描的行数。此数值为一个预估值,都有具体值,通常比实际值小

  filtered

    展示的是返回结果的行数所占可不后能 读到的行(rows 的值)的比例,当然是越小越好啦

  extra

    表示不在 全都列但都有点硬要的额外信息。取值有全都,我门我门挑全都比较常见的过一下

    using index

      表示 SQL 使用了使用覆盖索引,而不想回表去查询数据,性能非常不错

    using where

      表示存储引擎搜到记录后进行了后过滤(POST-FILTER),机会查询未能使用索引,using where 的作用假如有一天提醒我门我门 mysql 要用 where 条件过滤结果集

    using temporary

      表示 mysql 可不后能 使用临时表来存储结果集,常见于排序和分组查询

    using filesort

      表示 mysql 无法利用索引直接完成排序(排序的字段都有索引字段),此都会用到缓冲空间(内存机会磁盘)来进行排序;一般再次再次出现该值,则表示 SQL 要进行优化了,它对 CPU 的消耗是比较大的

    impossible where

      查询一段话的WHERE子句永远为 FALSE 时机会提示该额外信息

     当然还有全都的,不常见,等碰到了我门我门再去查吧(现在半夜 1 点,我觉得是太困了!)

总结

  1、背景问题报告

    还记得客服小姐姐的问题报告 吗,她嫌我门我门太慢,具体是因为下篇再完整性介绍,这里简单提一下:连表查询的 连接键 类型不一致,一个 INT 类型,一个 VARCHAR 类型,是因为 type 是 ALL(这谁设计的呀,坑死人呀! 难道是我 ?)

  2、思维导图

    假如有一天 是想全都人画个思维导图的,可上网一搜,发现了两全都人家画好了的思维导图,我想要偷个懒借用下:MySQL优化正餐之_EXPLAIN执行计划,中间描述的很完整性,一齐也包括了各种示例,真香!

  3、肚中精华

    EXPLAIN 的输出内容全都,我门我门没必要完整性掌握,重点我机会帮我门我门划好

    type,就像 RMB 一样重要

    key,也像 RMB 一样重要

    extra,还像 RMB 一样重要

    说白了还是 RMB 最重要,都有,我的意思是 type、key、extra 都有点硬要,全都的用到了再去买吧

  4、示例代码

    快点我

猜你喜欢

B. Figueroa数据,B. Figueroa新闻,B. Figueroa视频,B. Figueroa身价

B.FigueroaB.Figueroa俱乐部:克雷塔罗国籍:墨西哥身高:176CM位置:中场年龄:20岁体重:72KG号码:7号生日:1999-05-28惯用脚:右脚赛季俱乐

2020-02-19

易网云授权管理系统|易网云授权管理系统 v6.0下载

网站系统授权综合管理系统系统采用ASP+ACCESS开发,轻松稳定,防攻击和防下载补救。不必安装,假如上传到支持ASP的空间中即可使用。系统亮点:只需一段JS代码,就都也能远程

2020-02-19

5分钟看懂iPhone Xs/Max!一文扫尽苹果秋季新品发布会所有干货

2018年9月13日深更深更半夜01:00(美国加州时间9月12日上午10:00),苹果机在圣何塞ApplePark的SteveJobsTheater举办了一年一度的秋季新品发

2020-02-19

有港人於港珠澳大橋遭拘捕 粵公安廳:涉走私手機案潛逃7年

大公文匯全媒體報道:廣東省公安廳官方微博今日(16日)表示,珠海市公安局與珠海出入境邊防檢查總站上周五(13日)下午3時左右,在港珠澳大橋東人工島安全檢查站進行聯合執法時,邊檢

2020-02-19

三日逾六萬港人搭高鐵北上

【大公報訊】記者謝瑩瑩報道:新年即將到來,為躲避四出作惡的黑衣暴徒,港人紛紛取道高鐵西九龍站離港「避難」,入境處數據顯示,12月24至26日三日內逾六萬人經高鐵西九龍站出境,較

2020-02-19