MySql explain怎么执行

发布时间：2021-12-04 14:13:09 作者：iii
来源：亿速云阅读：174

本篇内容介绍了“MySql explain怎么执行”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

通过执行计划可以了解查询方式、索引使用情况、需要扫描的数据量以及是否需要临时表或排序操作等信息。

查询执行计划：explain + SQL 语句

id: 编号

select_type: 查询类型

table：表

type: 类型

possible_keys: 预测用到的索引

key: 实际使用的索引

key_len: 实际使用索引的长度

ref: 表之间的引用

rows: 通过索引查询到的数据量

Extra：额外的信息

创建如下表：

CREATE TABLE course(cid INT(3),cname VARCHAR(20),tid INT(3));
CREATE TABLE teacher(tid INT(3),tname VARCHAR(20),tcid INT(3));
CREATE TABLE teacherCard(tcid INT(3),tcdesc VARCHAR(200));

INSERT INTO course VALUES(1,'java',1);INSERT INTO course VALUES(2,'html',1);INSERT INTO course VALUES(3,'sql',2);INSERT INTO course VALUES(4,'web',3);
INSERT INTO teacher VALUES(1,'tz',1);INSERT INTO teacher VALUES(2,'tw',2);INSERT INTO teacher VALUES(3,'tl',3);
INSERT INTO teacherCard VALUES(1,'tzdesc');INSERT INTO teacherCard VALUES(2,'twdesc');INSERT INTO teacherCard VALUES(3,'tldesc');

MySql explain怎么执行

上面这些数据可以使用很多方法查询出来：

第一种方法：

SELECT t.* FROM teacher t,course c,teacherCard tc WHERE t.tid = c.tid AND t.tcid = tc.tcid AND (c.cid = 2 OR tc.tcid = 3)

查看执行计划：

MySql explain怎么执行

1. id 编号

<1> id ：id值相同，从上往下，顺序执行，t3-tc3-c4

下面我们往teacher表中再插入一点数据：

INSERT INTO teacher VALUES(4,'tz4',4);INSERT INTO teacher VALUES(5,'tw5',5);INSERT INTO teacher VALUES(6,'tl6',6);

再来看执行计划：

MySql explain怎么执行

我们发现执行计划变了，变成了tc3-c4-t6.

表的执行顺序，因数量的个数改变而改变的原因：笛卡尔积

数据量小的表优先查询

<2> id值不同：id值越大越优先查询。（本质：在嵌套子查询时，先查内层，再查外层）

查询教授SQL课程的老师描述(desc)

EXPLAIN SELECT tc.tcdesc FROM teacherCard tc ,course c ,teacher t WHERE t.tid = c.tid AND t.tcid = tc.tcid AND c.cname = 'sql'

将以上多表查询改成子查询

EXPLAIN SELECT tc.tcdesc FROM teacherCard tc WHERE tc.tcid = (SELECT t.tcid FROM teacher t WHERE t.tid = (SELECT c.cid FROM course c WHERE c.cname = 'sql'))

改成子查询+多表：

EXPLAIN SELECT t.tname ,tc.tcdesc FROM teacher t, teacherCard tc WHERE t.tcid = tc.tcid AND t.tid = (SELECT c.tid FROM course c WHERE c.cname = 'sql')

<3> id值有相同，又有不同：id值越大越优先；id值相同，从上往下，顺序执行

2. select_type:

<1>PRIMARY: 包含子查询SQL中的主查询（最外层）

<2>SUBQUERY: 包含子查询SQL中的子查询（非最外层）

<3>SIMPLE：简单查询（不包含子查询、union）

<4>DERIVED: 衍生查询（使用到了临时表）

a: 在from 子查询中只有一张表

EXPLAIN SELECT cr.cname FROM (SELECT * FROM course WHERE tid IN (1,2)) cr;

b: 在from 子查询中，如果有table1 union table2，则table1就是derived，

table2就是union

EXPLAIN SELECT cr.cname FROM (SELECT * FROM course WHERE tid = 1 UNION SELECT * FROM course WHERE tid = 2) cr;

MySql explain怎么执行

<5>UNION: 上例

<6>UNION RESULT：告知开发人员，哪些表之间存在union查询

3. type：索引类型、类型

常见的如下：越往左边，性能越高，排序如下

system>const>eq_ref>ref>range>index>all

其中：system、const只是理想情况；实际能达到ref、range

system（忽略）:只有一条数据的系统表或衍生表只有一条数据的主查询。

const：仅仅能查到一条数据的SQL，用户Primary Key或unique索引

eq_ref: 唯一性索引：对于每个索引键的查询，返回匹配唯一行数据（有且只有1个，不能多、不能0）,常见于唯一索引和主键索引。

ref: 非唯一性索引：对于每个索引键的查询，返回匹配的所有行（0，多个）

range: 检索指定范围的行，where后面是一个范围查询（between， < , > 特殊：in有时候会失效，从而转化为无索引all）

index：查询全部索引的数据，查询的字段是索引

EXPLAIN SELECT tid FROM test01;

all: 查询全部表中数据，查询的字段不是索引，就要全表扫描

EXPLAIN SELECT tname FROM test01;

system和const: 结果只有一条数据

eq_ref: 结果多条，但是每条数据是唯一的

ref: 结果多条，但是每条数据是0或者多条

4. possible_keys: 可能遇到的索引，不准，是一种预测

5. key：实际使用到的索引

6. key_len: 索引的长度，作用是用于判断复合索引是否被完全使用。

CREATE TABLE test_k1(  NAME CHAR(20) NOT NULL DEFAULT '');ALTER TABLE test_k1 ADD INDEX index_name(NAME);
EXPLAIN SELECT * FROM test_k1 WHERE NAME = '';

MySql explain怎么执行

在utf-8中，一个字符占3个字节，因为name是20的长度，所以是key_len为60，因为name字段不能为null，所以是60字节，如果name字段可以为null，则会使用一个字节用于标识，则key_len是61字节，

utf8: 1个字符占3字节

gbk：1个字符占2字节

mysql用2个字节标识可变长度

7. ref ：作用是指明当前表所参照的字段

select ... from ... where a.c = b.x （其中b.x可以是常量，const）

8. rows：被索引优化查询的数据个数

9. Extra：

<1>using filesort: 性能消耗大，需要"额外"的一次排序（查询），常见于order by语句中

对于单索引，如果排序和查找是同一个字段，则不会出现using filesort，如果排序和查找不是同一个字段，则会出现using filesort。

复合索引：不能跨列(最佳左前缀)

ALTER TABLE test_k1 ADD INDEX name_name1_name2_index (NAME,name1,name2);EXPLAIN SELECT * FROM test_k1 WHERE name1 = '' ORDER BY name2; --Using filesort EXPLAIN SELECT * FROM test_k1 WHERE NAME1 = '' ORDER BY name2; --Using filesort EXPLAIN SELECT * FROM test_k1 WHERE NAME = '' ORDER BY name1;

where 和order by 按照复合索引的顺序使用，不要跨列或无序使用。

<2>using temporary：性能损耗大，用到了临时表。一般出现在group by语句中

EXPLAIN SELECT NAME FROM test_k1 WHERE NAME IN ('1','2','3') GROUP BY NAME;EXPLAIN SELECT NAME FROM test_k1 WHERE NAME IN ('1','2','3') GROUP BY NAME1; --Using TEMPORARY

注意：查询哪些列，就用哪些列分组

<3>using index: 性能提升了，索引覆盖（覆盖索引），原因是不读取原文件，只从索引文件中获取数据（不需要回表查询）

索引覆盖：只要使用到的列全部都在索引中，就是索引覆盖

ALTER TABLE test_k1 ADD INDEX name_name1_index (NAME,name1);
EXPLAIN SELECT NAME FROM test_k1 WHERE NAME = '' ;--Using INDEX 不需要回表查询EXPLAIN SELECT NAME1 FROM test_k1 WHERE NAME = '' ;--Using INDEX 不需要回表查询
EXPLAIN SELECT NAME2 FROM test_k1 WHERE NAME = '';   -- 需要回表查询

如果用到了索性覆盖（using index）,会对possible_keys和key造成影响

a. 如果没有where,则索引只出现在key中

b. 如果有where,则索引出现在key和possible_keys中

<4> using where(需要回表查询)

EXPLAIN SELECT name2 FROM test_k1 WHERE NAME2 = '';   -- 需要回表查询

<5> Using index condition 在5.6版本后加入的新特性

查询列不完全被索引覆盖，查询条件完全可以使用到索引

“MySql explain怎么执行”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！

MySql explain怎么执行

相关阅读