MYSQL执行计划

01、目标

掌握和了解explain执行计划

02、作用

使用explain关键字,可以模拟优化器执行的SQL语句
从而知道MYSQL是如何处理sql语句的
通过Explain可以分析查询语句或表结构的性能瓶颈

explain：分析SQL语句执行的过程，是否走索引规则和叛变你写SQL语句是否是最优一种机制。

具体作用：

• 查看表的读取顺序
• 数据读取操作的操作类型
• 查看哪些索引可以使用
• 查看哪些索引被实际使用
• 查看表之间的引用
• 查看每张表有多少行被优化器执行

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fyAWBBnu-1629391457225)(assets/kuangstudy1929e07c-3d59-42e3-989b-2f6527a127e9.png)]

03、使用语法

#使用Explain关键字 放到sql语句前
explain select cus_id from testemployee where cus_id > 10

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0Sutmd9M-1629391457227)(assets/image-20210725201747115.png)]

通过explain执行计划：可以看到整个SQL语句执行的状态信息其中就包括了如下：

03-01、id

id

注意：这里的这个id并不是主键之类含义。它是代表SQL语句执行的一个顺序。值越大越优先执行。越小就越后面执行，如果相同依次按顺序执行

select查询的序列号，包含一组数字,表示查询中执行select子句或操作表的顺序    
 值分为三种情况
**id值相同**
    执行顺序由上到下
**id不同**
    如果是子查询,id的序号会递增,id值越大优先级越高,优先被执行
**id相同不同,同时存在**
    可以认为是一组,从上往下顺序执行
    在所有组中,id值越大,优先级越高,越先执行

id相同情况：

EXPLAIN SELECT * from employee a,department b,customer c where a.dep_id = b.id and a.cus_id = c.id;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-E2K5bNmM-1629391457229)(assets/image-20210725202814534.png)]

面试题：from a,b,c 在执行过程中，a，b，c执行顺序是什么？

我并不能直接给你具体的执行顺序，一定要通过查询计划或者explain执行计划，通过分析以后，具体看id得值，如果相同，由上至下运行，id如果不同，大先执行，id相同不同：大先执行，相同由上至下运行。

id不相同情况：（子查询）

EXPLAIN SELECT * from department WHERE id = (SELECT id from employee WHERE id=(SELECT id from customer WHERE id = 1))

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PbkLr4oX-1629391457231)(assets/image-20210725202730977.png)]

id相同不同的情况

-- id相同不同
EXPLAIN select * from department d, (select * from employee group by dep_id) t where d.id = t.dep_id;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cCe4kp1G-1629391457232)(assets/image-20210725203039055.png)]

课堂查询SQL语句

select 
    ku.userid,
    ku.nickname,
    (select  count(1) from kss_user_fans kuf where kuf.userid= ku.userid) as fanscount
from kss_user ku  

统计查询用户的相关信息

-- 用户的关注数，浏览数、粉丝数、购买数、评论数、阅读量等
-- 用程序---每个统计都对应一个count语句，然后用map装载返回
-- 
select 
 (select count(1) from kss_fans where userid = 1) as count1,
 (select count(1) from kss_bbs where userid = 1) as count2,
 (select count(1) from kss_bss_comment where userid = 1) as count3,
 (select count(1) from kss_orders where userid = 1) as count4
FROM dual;

– 子查询使用在列上，必须保证是唯一的返回

就告诉你要给道理：子查询可以用在列上，表上，查询条件上。

– 优化SQL。你可以考虑先用子查询把数据过滤一部分以后，在和另外一个表进行关联。

03-02、select_type

查询类型,主要用于区别普通查询,联合查询,子查询等复杂查询
结果值
SIMPLE
    简单select查询,查询中不包含子查询或者UNION
PRIMARY
    查询中若包含任何复杂的子查询,最外层查询则被标记为primary
SUBQUERY
    在select或where中包含了子查询
DERIVED
    在from列表中包含的子查询被标记为derived(衍生)把结果放在临时表当中        
UNION
    若第二个select出现的union之后,则被标记为union
    若union包含在from子句的子查询中,外层select将被标记为deriver
UNION RESULT
    从union表获取结果select,两个UNION合并的结果集在最后

SIMPLE 示例

-- SIMPLE
EXPLAIN SELECT * FROM kss_user WHERE id  = 1

SUBQUERY 示例

-- SUBQUERY 示例
EXPLAIN SELECT * FROM department WHERE id = (SELECT dep_id FROM employee WHERE id=1)

DERIVED 示例
在from列表中包含的子查询被标记为derived(衍生)把结果放在临时表当中

-- DERIVED 示例
EXPLAIN select * from department d, (select * from employee group by dep_id) t where d.id = t.dep_id;

UNION 示例

-- UNION
-- 需求，查询用户表中性别男和女分别是多少人
EXPLAIN
SELECT count(1) as num,'man' as msg FROM kss_user WHERE male = 1
UNION
SELECT count(1) as num,'woman' as msg FROM kss_user WHERE male = 0

MATERIALIZED物化子查询
针对一些范围的查询比如：in，etwwen and , >,>=,<,<=等可能会出现这种物化查询的情况。

EXPLAIN SELECT * FROM kss_user WHERE userid IN (SELECT userid FROM kss_user_course WHERE userid >10)

UNCACHEABLE SUBQUERY查询缓存子查询
从MYSQL5.7.20开始，查询缓存就被弃用了。并在MYSQL8.0中被删除。
uncacheable subquery结果集不能被缓存的子查询，不可物化每次都需要计算（动态计算，耗时操作）。

EXPLAIN SELECT * FROM kss_user WHERE id = (SELECT max(LAST_INSERT_ID()) FROM kss_user)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VFzzr4FH-1629391457232)(assets/kuangstudy828d9b2a-44a6-4b2a-8327-cbf8bfa06627.png)]

• 简单查询
• 子查询
• union查询

03-03、table

查询表

03-04、partions

表分区

drop table if exists kss_user_areatb;CREATE TABLE `kss_user_areatb` (    `uid` INT (11) NOT NULL AUTO_INCREMENT,    `account` VARCHAR (100),  `age` INT (3) NULL DEFAULT NULL,  `openid` varchar (32) NOT NULL,   PRIMARY KEY (`uid`) ,     UNIQUE KEY (`uid`,`openid`)  )  ENGINE = INNODB  default charset=utf8mb4PARTITION BY RANGE (uid) PARTITIONS 5 (    PARTITION p0 VALUES LESS THAN (6), -- 小于6 0 <= p<6    PARTITION p1 VALUES LESS THAN (11),-- 小于11 6<= p <11    PARTITION p2 VALUES LESS THAN (16),-- 小于16 11<= p <16    PARTITION p3 VALUES LESS THAN (21),-- 小于21 16<= p <21    partition p4 VALUES LESS THAN MAXVALUE -- 剩下的 >=21)-- 追加分区 （但是这里需要注意一个问题就是：如果已经增加了LESS THAN MAXVALUE后续的就没有意义了）alter table kss_user_areatb add PARTITION(PARTITION p4 VALUES LESS THAN (31));insert into kss_user_areatb (uid,account,age,openid) values(1,'张三',1,'1');insert into kss_user_areatb (uid,account,age,openid) values(2,'李四',5,'2');insert into kss_user_areatb (uid,account,age,openid) values(3,'王五',10,'3');insert into kss_user_areatb (uid,account,age,openid) values(4,'赵六',15,'4');insert into kss_user_areatb (uid,account,age,openid) values(5,'田七',20,'5');insert into kss_user_areatb (uid,account,age,openid) values(6,'飞哥',33,'6');insert into kss_user_areatb (uid,account,age,openid) values(26,'飞哥',33,'7');EXPLAIN SELECT * from kss_user_areatb where uid = 5;EXPLAIN SELECT * from kss_user_areatb where uid = 6;EXPLAIN SELECT * from kss_user_areatb where uid = 26;

03-04、type

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YudDlpCE-1629391457233)(assets/kuangstudy1929e07c-3d59-42e3-989b-2f6527a127e9.png)]

-- access_type：表查询链接类型，由上到下，性能逐步变低-- Null 不访问任何一个表explain select 1 from dual;-- 2：system根据主键查询系统表且这个表只有一条记录【特殊的const场景】-- 3：const常量查询非常的快，主键或者唯一索引的常量查询，表格最多只有一行记录符合查询。explain select * from kss_user where userid = 1;-- 4：eq_ref: 使用primarkey或者unique和前面的结果集匹配explain select * from kss_user ku,kss_user_course kc where ku.userid = kc.userid;-- 5：ref非聚集索引的常量查询 explain select * from kss_user where email = 'xuke @163.com';-- 6：fulltext 查询的过程中，只有5.6版本之后才支持 set optimizer_switch="materialization=off";explain select * from `fulltext_db` WHERE MATCH(`remark`) against('TONY');-- 7：ref_of_null 和ref查询类似，在ref的查询基础上，多家一个null值的查询条件explain select * from kss_user where email = 'xuke @163.com' or email is null;-- 8：index_merge 索引合并（分别两个查询条件的结果，再合并）explain select * from kss_user where email = 'xuke @163.com' or userid = 1;-- 9：unique_subquery IN子查询的结果由聚簇索引或唯一索引覆盖 userid是主键索引，email是二级索引，-- 这个时候userid触发到了覆盖索引，不会按照email的索引去查询set optimizer_switch="materialization=off";explain select * from kss_user where userid not in (select userid from kss_user where email like '%xu%');set optimizer_switch="materialization=on";-- 10：index_subquery 和 unique_subquery类似，但是用的是二级索引。-- 这个时候userid触发到了覆盖索引，不会按照email的索引去查询set optimizer_switch="materialization=off";explain select * from kss_user where email not in (select email from kss_user where email like '%xu%');set optimizer_switch="materialization=on";-- 11：range 是指sql语句条件中包含的：= 、<>、>=、<=、is null、between、in、<==>、like都可能会引发-- 这个时候userid触发到了覆盖索引，不会按照email的索引去查询explain select * from kss_user where userid > 10;-- 12：index 执行full_index scan的实话直接从索引中体区想要的结果数据，也就是可以避免回表explain select userid,email from kss_user;-- 13：ALL 执行full table scan这是最差一种方式explain select nickname,birthday from kss_user

面试题：or查询会不会走索引呢？

explain select * from kss_user where nickname = 'zhangsan' or nickname is null;

错误思维：or会造成索引失效。、

而一定是通过explain执行计划来进行一个判别才能够得到结论。

总结

system > const >== eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

type：表示表的连接类型，下面按照最佳到最差类型顺序介绍各种类型   system：系统表，并且只有一行，比如const表   const： 数据表最多只有一个匹配行，它将在查询开始时被读取，并在余下的查询优化中作为常量对           待，const表查询速度很快，因为它们只被读取一次，const用于常数值比较primary key或           unionq 索引的所有部分的场合   eq_ref: 对于每个来自前面的表的行组合，从该表中读取一行，当一个索引的所有部分都在查询中，使            用并且索引是unique 或 primarykey 时，就可以使用这种类型。                select *               from a               inner join b  on  a.id=b.id                  ref:对于来自前面表的任意组合，将从该表中读取所有匹配的行，这种类型用于索引即不是unique 也          不是primary key的情况，或者查询中使用了索引列的左子集，即索引中左边的部分列的组合，           ref可以使用= <=>操作符的带索引的列    ref_or_null: 该连接类型如同ref，但是添加了mysql可以专门搜索包含null值的行，在解决子查询            中经常使用该连接类型的优化              select * from ref_table where key_column=expr or  key_column is null               index_merge：该类型表示使用索引合并优化，使用到多个索引        unique_subquery: 该类型替换了下面形式的in子查询的ref    valeus in( select  primary_key               from  sigle_table where some_expr), unique_subquery  是一个索引查找函数，可              以完全替换子查询，效率更高    index_subquery:          该连接类型类似于unique_subquery,可以替换in子查询，但只适合下列形式的子查询中的非唯          一索引value in （select key_column from  single_table where some_expr）    range:          只检索给定范围的行，使用一个索引来选择行，key列显示使用了那个索引，key_len 包含所有           使用索引的最长关键元素。    index：          该连接类型与all相同，除了只扫描索引树，但这通常比all快，因为索引文件通常比数据文件           小    all：      对于前面表的任意行组合，进行完整表扫描，如果表是第一个没有标记const的表，这样性能会不好

参考文章：https://blog.csdn.net/qq_39674002/article/details/107124573

IN查询转换union查询

CREATE INDEX dep_id_index ON employee(dep_id);EXPLAIN SELECT * from employee  WHERE dep_id in(1,2,3,4)-- 转换思维EXPLAIN SELECT * from employee  WHERE dep_id = 1UNIONSELECT * from employee  WHERE dep_id = 2UNIONSELECT * from employee  WHERE dep_id = 3UNIONSELECT * from employee  WHERE dep_id = 4

LIKE查询

SHOW INDEX FROM kss_user;-- 会EXPLAIN SELECT * FROM kss_user WHERE nickname like 'zhang';-- 会走索引吗？不会   不会%开头不明确的值EXPLAIN SELECT * FROM kss_user WHERE nickname like '%san';-- 会走索引吗？ 会   %结尾明确的值，会把明确的值算出hash值然后去索引中去命中。EXPLAIN SELECT * FROM kss_user WHERE nickname like 'zhang%';-- 会走索引吗？不会   不会%开头不明确的值EXPLAIN SELECT * FROM kss_user WHERE nickname like '%zhang%';

有没办法让like查询%% 或者%开头去走索引呢？

还可以，我们可以把查询列用索引列，就可以走覆盖索引

EXPLAIN SELECT id FROM kss_user WHERE nickname like '%zhang%';# 修改如下EXPLAIN select * from kss_user ku where ku.id in (SELECT id FROM kss_user WHERE nickname like '%zhang%')

10、什么列的适合建立索引？为什么？

• 不经常修改的列。
• 值的跨度一定要大。不重复越好。