Mysql explain语句详解与实例展示

首先简单介绍sql：

SQL语言共分为四大类：数据查询语言DQL，数据操纵语言DML，数据定义语言DDL，数据控制语言DCL。

1. 数据查询语言DQL
数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE子句组成的查询块：SELECT <字段名表>FROM <表或视图名>WHERE <查询条件>

2 .数据操纵语言DML
数据操纵语言DML主要有三种形式：
1) 插入：INSERT
2) 更新：UPDATE
3) 删除：DELETE

3. 数据定义语言DDL
数据定义语言DDL用来创建数据库中的各种对象-----表、视图、索引、同义词、聚簇等如：CREATE TABLE / VIEW / INDEX / SYN / CLUSTER| 表 视图 索引 同义词 簇。DDL操作是隐性提交的！不能rollback

4. 数据控制语言DCL
数据控制语言DCL用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等。如：
1) GRANT：授权。
2) ROLLBACK [WORK] TO [SAVEPOINT]：回退到某一点。回滚---ROLLBACK回滚命令使数据库状态回到上次最后提交的状态。其格式为：SQL>ROLLBACK;
3) COMMIT [WORK]：提交。在数据库的插入、删除和修改操作时，只有当事务在提交到数据库时才算完成。在事务提交前，只有操作数据库的这个人才能有权看到所做的事情，别人只有在最后提交完成后才可以看到。

关于授权语句

数据库授权命令：

GRANT<权限> on 表名(或列名) to 用户 

正确答案选项B： insert,select: 权限 表名: user 用户：nkw

补充知识点-回收权限

REVOKE <权限> on 表名(或列名) FROM 用户 

explain 语句介绍

explain 语句相信大家都不陌生，作为查看执行计划的语句。explain在sql优化分析中会经常会用到。

这里值得注意的是：explain并没有真正执行语句，只是展示执行计划。

可以看到哪些信息？

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

基本语法介绍

EXPLAIN SELECT select具体语句
如：
EXPLAIN SELECT * FROM userpro

explain各个列的作用介绍

列名	描述	补充
id	每个SELECT关键字都对应一个id
select_type	SELECT关键字对应的查询类型
table	表名
partitions	匹配的分区信息
type	针对单表的访问方法
possible_keys	可能用到的索引
key	实际用到的索引
key_len	实际用到的索引长度
ref	当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows	预估需要读取的记录条数
filtered	经过搜索条件过滤后剩余记录条数的百分比
Extra	额外信息

EXPLAIN各列详细介绍

1，id

每个SELECT关键字都对应一个id

id值越大，优先级越高，越先执行

id如果相同，可以认为是一组，从上往下顺序执行

id号每个号码，表示一次独立的查询, 一个sql的查询趟数越少越好

2，select_type

类别	说明
SIMPLE	单表查询，没有子查询或者 UNION 查询。
PRIMARY	查询中最外层的 SELECT 语句。
SUBQUERY	在 WHERE 子句中使用了子查询。
DERIVED	在 FROM 子句中包含的子查询，MySQL会将其标记为 DERIVED（派生），并且会为其结果集生成一个临时表，以供外层查询使用。
UNION	在 UNION 查询中第二个及以后的查询语句。
UNION RESULT	UNION 查询的结果集。
DEPENDENT SUBQUERY	子查询的结果依赖外层查询的值，并且对于每个外层查询中的行都执行一次子查询
DEPENDENT UNION	UNION 查询的第二个及以后的查询语句，且其结果依赖于外层查询的值
UNCACHEABLE SUBQUERY	子查询不能被缓存，每次引用时都会执行

3，table列代表着该表的表名（有时不是真实的表名字，可能是简称）。

4，partitions (可略)

5. type ☆

常见type如下

type	说明
system	表中只有一行，通常为 SELECT ... FROM DUAL 查询优化。
const	查询通过索引一次就找到了，仅有一行结果（常量表）。
eq_ref	使用唯一索引或主键从其他表中找出一行。
ref	使用非唯一索引从其他表中找出一行或多行。
range	使用索引返回一个范围内的行。
index	完全扫描索引以找到行，而非扫描整个表。
all	全表扫描，对表中的每一行进行检查。

一般情况尽量避免all

6， key和possible_keys

1. key：

   • key 字段显示了查询实际使用的索引。如果该字段的值为 NULL，则表示没有使用索引。如果该字段有值，则表示 MySQL 使用了指定的索引来执行查询。

2. possible_keys：

   • possible_keys 字段显示了 MySQL 能够使用的索引列表。这些索引是查询中可以考虑的索引，但不一定会被实际使用。通常，possible_keys 中列出的索引是根据查询条件和表结构来决定的。

• 如果 key 字段有一个索引名，而 possible_keys 中列出了多个索引名，表示 MySQL 选择了 key 字段列出的索引来执行查询，而其他索引列在 possible_keys 中表示也可以考虑，但最终没有使用。

• 如果 key 字段为 NULL，而 possible_keys 中列出了多个索引名，表示 MySQL 在执行查询时没有使用任何索引，这可能导致全表扫描或者其它非索引优化访问方法。

7，key_len

key_len 是描述索引键长度的一个字段。它指示了 MySQL 在使用特定索引执行查询时，索引的使用情况和索引键的长度。

1. 单列索引：

   • 如果使用了单列索引，并且该列的类型是固定长度的（例如 INT），则 key_len 的值就是该列的长度。
   • 如果使用了变长字段（例如 VARCHAR），则 key_len 的值是该字段的最大长度。

2. 复合索引：

   • 对于复合索引（即包含多个列的索引），key_len 表示索引中所有列的总长度。

3. 组合索引：

   • 如果查询中使用了多个列的组合索引，key_len 是组合索引中所有列的总长度。

4. 索引前缀：

   • 在某些情况下，MySQL 可能只使用索引的一部分。例如，可以使用索引的前缀作为索引的一部分来执行查询。在这种情况下，key_len 将显示实际使用的索引部分的长度。

8，ref

ref 是描述表之间的连接条件或者使用非唯一索引进行查找的一个字段。它指示了 MySQL 在执行查询时使用了哪些连接条件或者哪些索引来访问表。

9，rows

rows 是一个估计值，表示执行查询时所访问的行数或者要检查的行数。

• 对于简单的 SELECT 查询，rows 表示估计的返回行数。
• 对于连接查询（JOIN）或者子查询，rows 可能表示连接操作期间访问的行数。
• 对于表扫描（全表扫描或索引扫描），rows 可能表示扫描的行数。

10，filtered

表示根据 WHERE 条件和索引条件过滤后的行的百分比。filtered反映了优化器估计的查询优化效果。

当 filtered 接近 100% 时，表示查询条件有效地过滤了大部分不符合条件的行，这通常是一个好的优化指标。

反之，如果 filtered 值较低，可能表示查询条件不够精确或者优化器未能有效地利用索引来过滤数据。

11，Extra

额外信息字段

以下是一些常见的 Extra 字段及其含义：

1. Using index：

   • 表示查询使用了覆盖索引，即查询的结果可以完全通过索引返回，而不需要访问表的实际数据行。

2. Using where：

   • 表示MySQL服务器将在存储引擎检索行之后再进行条件过滤，而不是在索引中完成。

3. Using temporary：

   • 表示MySQL在内存中创建了临时表来处理查询。常见于排序操作或者包含聚合函数的查询。

4. Using filesort：

   • 表示MySQL执行了文件排序以处理查询。这通常发生在无法使用索引完成排序的情况下。

5. Range checked for each record (index map: ...)：

   • 表示MySQL使用了索引来检查每个记录是否在指定的范围内。这通常发生在 range 查询类型中。

6. Full scan on NULL key：

   • 表示MySQL在某个索引中进行了全表扫描，以找到匹配 NULL 值的行。

7. Distinct：

   • 表示MySQL将在找到第一匹配的行之后停止查找重复的行。

8. Using join buffer (Block Nested Loop)：

   • 表示MySQL正在使用连接缓冲区来处理连接操作，通常在连接表数量较多或者连接表大小较大时出现。

9. Impossible WHERE：

   • 表示MySQL优化器确定了 WHERE 子句中的条件不可能满足，因此不会扫描任何行。

10. No tables used：

    • 表示查询不涉及任何表，例如 SELECT NOW()。

explain实际执行展示

数据准备

建表s1

CREATE TABLE s1 (
id INT AUTO_INCREMENT,
key1 VARCHAR(100),
key2 INT,
key3 VARCHAR(100),
key_part1 VARCHAR(100),
key_part2 VARCHAR(100),
key_part3 VARCHAR(100),
common_field VARCHAR(100),
PRIMARY KEY (id),
INDEX idx_key1 (key1),
UNIQUE INDEX idx_key2 (key2),
INDEX idx_key3 (key3),
INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=INNODB CHARSET=utf8;

建表s2

CREATE TABLE s2 (
id INT AUTO_INCREMENT,
key1 VARCHAR(100),
key2 INT,
key3 VARCHAR(100),
key_part1 VARCHAR(100),
key_part2 VARCHAR(100),
key_part3 VARCHAR(100),
common_field VARCHAR(100),
PRIMARY KEY (id),
INDEX idx_key1 (key1),
UNIQUE INDEX idx_key2 (key2),
INDEX idx_key3 (key3),
INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=INNODB CHARSET=utf8;

数据自行准备。

样例执行与结果

简单查询

单表查询

EXPLAIN SELECT * FROM `s1`;

连接查询

EXPLAIN SELECT * FROM `s1` INNER JOIN `s2`;

子查询

EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key2 FROM s2 WHERE common_field
= 'a');

特别展示

展示select_type

#Union 去重
EXPLAIN SELECT * FROM `s1` UNION SELECT * FROM `s2`;
#Union 全查
EXPLAIN SELECT * FROM `s1` UNION ALL SELECT * FROM `s2`;

最后一步为去重操作，所以会使用临时表进行。而UNION ALL则为全部查询，则不会出现临时表查消息。

type类

const（索引一次就找到，仅有一行结果）

EXPLAIN SELECT * FROM s1 WHERE id = 10002;

eq_ref（使用唯一索引或主键从其他表中找出一行）

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;

ref（使用非唯一索引从其他表中找出一行或多行）

EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';

range（使用索引返回一个范围内的行）

EXPLAIN SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c');

index（完全扫描索引以找到行）

EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a';

其它展示

filtered小数时

EXPLAIN SELECT * FROM s1 WHERE key1 > 'za' AND common_field = 'la'