chatgpt github_SQL性能优化

前言

　　开心一刻

　　　　某人养了一头猪，烦了想放生，可是猪认识回家的路，放生几次它都自己回来了。一日，这个人想了个狠办法，开车带着猪转了好多路进山区放生，放生后又各种打转，然后掏出电话给家里人打了个电话，问道：“猪回去了吗？”，家里人：“早回来了，你在哪了，怎么还没回来？”，他大怒道：“让它来接我，我特么迷路了！！！”

还不如我了

背景

　　某一天，楼主打完上班卡，坐在工位逛园子的时候，右下角的 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 

代码100分-- 查看 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、示例代码

　　　　快点我