前言
开心一刻
某人养了一头猪,烦了想放生,可是猪认识回家的路,放生几次它都自己回来了。一日,这个人想了个狠办法,开车带着猪转了好多路进山区放生,放生后又各种打转,然后掏出电话给家里人打了个电话,问道:“猪回去了吗?”,家里人:“早回来了,你在哪了,怎么还没回来?”,他大怒道:“让它来接我,我特么迷路了!!!”
还不如我了
背景
某一天,楼主打完上班卡,坐在工位逛园子的时候,右下角的 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(50) 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(50) 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.160.60",4, "2019-10-12 10:23:45"),
("王雁云", "104.69.160.61",4, "2019-10-16 20:23:45"),
("李凝雪", "104.69.160.62",4, "2019-10-17 20:23:45"),
("许侍霜", "104.69.160.63",4, "2019-10-18 20:23:45"),
("叶留痕", "104.69.160.64",4, "2019-10-19 20:23:45"),
("王雁云", "104.69.160.65",4, "2019-10-20 20:23:45"),
("叶留痕", "104.69.160.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、示例代码
快点我