explain命令详解sql优化准备

最新推荐文章于 2023-06-20 11:56:56 发布

OnlyloveCuracao

最新推荐文章于 2023-06-20 11:56:56 发布

阅读量573

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/onlylovecuracao/article/details/104385449

版权

MySQL 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

前提：在Mysql数据库下进行的实践，版本5.7.20（其它版本执行可能会有不同，SQL优化器问题）

explain：分析SQL的执行计划，可以模拟SQL优化器执行SQL语句，从而让开发人员知道自己编写的SQL状况

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

例如：explain select * from money;

打印结果

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: money
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 139618
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

1、id：编号

2、select_type 查询类型

3、table 使用哪些表

4、partitions SQL分区

5、type 类型，索引类型

6、possible_keys 预测使用到的索引

7、key 实际用到的索引

8、key_len实际使用的索引长度（不精确，根据数据库使用的编码有关）

9、ref 表之间的引用

10、rows 通过索引查询到的数据量（优化器预估的记录扫描行数）

11、filtered 按表条件过滤的行的百分比

12、Extra 额外的信息

准备数据：新建三张表

course（课程表）：cid课程编号，cname课程名称，tid（外键），教师编号

teacher（教师表）：tid教师编号，tname教师姓名，tcid教师证编号

teacherCard（教师证表）：tcid教师证编号，tcdesc 教师描述

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') ;

查询课程编号为2 或教师证编号为3 的老师信息（多表连接查询）

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)

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | NULL                                               |
|  1 | SIMPLE      | tc    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | c     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+

id

id值相同，从上往下顺序执行，执行顺序 teacher3 ->teacherCard3 ->course4 （其中t表3条数据，tc表3条数据，c表4条数据）

对teacher表增加3条数据

insert into teacher values(4,'ll',4);
insert into teacher values(5,'ss',5);
insert into teacher values(6,'ws',6);

再次执行上面的查询SQL

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | tc    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | NULL                                               |
|  1 | SIMPLE      | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |    16.67 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | c     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
3 rows in set, 1 warning (0.00 sec)

执行顺序变成了： teacherCard3 -> teacher6 ->course4（在Mysql5.5版本中，执行顺序为tc3->c4->t6）

Mysql优化器对每个表的执行顺序进行了优化，笛卡尔积原理

查询教授SQL课程的老师的描述（使用子查询）

explain select tc.tcdesc
from teacherCard tc
where tc.tcid = 
        (select t.tid 
         from teacher t 
         where tid = 
            (select c.tid from course c where c.cname = 'sql'))

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | tc    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
|  2 | SUBQUERY    | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |    16.67 | Using where |
|  3 | SUBQUERY    | c     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

id值不同：id值越大越优先查询 (本质：在嵌套子查询时，先查内层再查外层)

查询教授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 cname = 'sql');


+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | PRIMARY     | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |    16.67 | Using where                                        |
|  1 | PRIMARY     | tc    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where; Using join buffer (Block Nested Loop) |
|  2 | SUBQUERY    | c     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where                                        |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
3 rows in set, 1 warning (0.00 sec)

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

总结：

id值相同，从上往下顺序执行

d值不同：id值越大越优先查询

id值有相同，又有不同： id值越大越优先；id值相同(可以认为是一组)，从上往下顺序执行

select_type:查询类型

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

SUBQUERY：包含子查询SQL中的子查询（非最外层）

simple:简单查询（不包含子查询、union）例如：select * from teacher

derived:衍生查询(使用到了临时表)
a.在from子查询中只有一张表（MySQL5.7标识为简单查询，5.5为衍生查询）
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

union:上例

union result :告知开发人员，那些表之间存在union查询

type：类型（索引类型）

越左性能越好，前提：必须要有索引

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

实际比较常见的（在其中：system，const只是理想状态，实际只能达到ref>range）

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

新建一张表，并插入数据，增加索引（主键索引）

create table test01
(
	tid int(3),
	tname varchar(20)
);
insert into test01 values(1,'a') ;

alter table test01 add constraint tid_pk primary key(tid) ;

system：只有一条数据的系统表

const：仅仅能查到一条数据的SQL ,用于Primary key 或unique索引（类型与索引类型有关）

explain select tid from test01 where tid =1 ;

+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | test01 | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

删除主键索引，新增单值索引，再次查询

alter table test01 drop primary key ;

create index test01_index on test01(tid) ;

explain select tid from test01 where tid =1 ;

+----+-------------+--------+------------+------+---------------+--------------+---------+-------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key          | key_len | ref   | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+--------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | test01 | NULL       | ref  | test01_index  | test01_index | 4       | const |    1 |   100.00 | Using index |
+----+-------------+--------+------------+------+---------------+--------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

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

在teacher表和teacherCard表中演示（表中暂时无索引）

select * from teacherCard;
+------+--------+
| tcid | tcdesc |
+------+--------+
|    1 | tzdesc |
|    2 | twdesc |
|    3 | tldesc |
+------+--------+
3 rows in set (0.00 sec)

select * from teacher;
+------+-------+------+
| tid  | tname | tcid |
+------+-------+------+
|    1 | tz    |    1 |
|    2 | tw    |    2 |
|    3 | tl    |    3 |
|    4 | ll    |    4 |
|    5 | ss    |    5 |
|    6 | ws    |    6 |
+------+-------+------+
6 rows in set (0.00 sec)

给teacherCard表增加主键tcid

alter table teacherCard add constraint pk_tcid primary key(tcid);

给teacher表的tcid列增加唯一索引

alter table teacher add constraint uk_tcid unique index(tcid) ;

将teacher表和teacherCard表根据tcid连接，查询tcid

explain select t.tcid from teacher t,teacherCard tc where t.tcid = tc.tcid ;

+----+-------------+-------+------------+-------+---------------+---------+---------+---------------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref           | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+---------------+------+----------+-------------+
|  1 | SIMPLE      | tc    | NULL       | index | PRIMARY       | PRIMARY | 4       | NULL          |    3 |   100.00 | Using index |
|  1 | SIMPLE      | t     | NULL       | ref   | uk_tcid       | uk_tcid | 5       | words.tc.tcid |    1 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+---------------+------+----------+-------------+

观察SQL，我们使用的索引是teacher表中的tcid字段，查看打印输出，发现type并不是eq_ref，原因：我们违背了“返回匹配唯一行数据”，在teacher表中有六条数据，teacherCard表中只有三条数据。而SQL的输出是三条数据，跟teacher表中的六条数据对不上

删除teacher表中多余的三条数据，再次执行上条SQL,发现type为eq_ref（注意：如果是teacher表查询到的是1,2,3，而连接查询是1,2,4，这也对不上，不是eq_ref）

delete from teacher where tcid > 3;

explain select t.tcid from teacher t,teacherCard tc where t.tcid = tc.tcid ;

+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+--------------------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref          | rows | filtered | Extra                    |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+--------------------------+
|  1 | SIMPLE      | t     | NULL       | index  | uk_tcid       | uk_tcid | 5       | NULL         |    3 |   100.00 | Using where; Using index |
|  1 | SIMPLE      | tc    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | words.t.tcid |    1 |   100.00 | Using index              |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+--------------------------+
2 rows in set, 1 warning (0.00 sec)

eq_ref是可遇不可求的，不可能因为提高性能，而删除数据（开句玩笑，硬核优化：删数据）

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

查看teacher表和teacherCard表，分别插入一行数据

insert into teacher values(4,'tz',4) ;

insert into teacherCard values(4,'tz222');

select * from teacher;

+------+-------+------+
| tid  | tname | tcid |
+------+-------+------+
|    1 | tz    |    1 |
|    2 | tw    |    2 |
|    3 | t1    |    3 |
|    4 | tz    |    4 |
+------+-------+------+
4 rows in set (0.00 sec)

select * from teacherCard;

+------+--------+
| tcid | tcdesc |
+------+--------+
|    1 | tzdesc |
|    2 | twdesc |
|    3 | t1desc |
|    4 | tz222  |
+------+--------+
4 rows in set (0.00 sec)

teacher表的tname字段有重复的tz，为tname字段增加单值索引

alter table teacher add index index_name (tname) ;

查询，发现此处索引失效了，原因，可能是数据量过少

explain select * from teacher 	where tname = 'tz';

+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | teacher | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   11 |    10.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

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

为teacher表的tid字段增加单值索引

alter table teacher add index tid_index (tid) ;

查询

explain select t.* from teacher t where t.tid in (1,2) ;

+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t     | NULL       | range | tid_index     | tid_index | 5       | NULL |    2 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

explain select t.* from teacher t where t.tid <3 ;

+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t     | NULL       | range | tid_index     | tid_index | 5       | NULL |    2 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

index：查询全部索引中数据
explain select tid from teacher ; --假设tid 是索引，只需要扫描索引表，不需要所有表中的所有数据

all：查询全部表中的数据
explain select cid from course ; --假设cid不是索引，需要全表所有，即需要所有表中的所有数据

结论：

system/const: 结果只有一条数据
eq_ref:结果多条；但是每条数据是唯一的；
ref：结果多条；但是每条数据是0或多条；

possible_keys

可能用到的索引，是一种预测，不准确

key

实际使用的索引

如果possible_keys或key为NULL，则说明未使用索引

key_len

索引的长度(不同的数据库编码长度不一样，当前我的数据库编码为utf-8)，一般用在判断复合索引是否完全被使用

创建一张表，并给name字段加上单值索引

create table test_kl
(
    name char(20) not null default ''
);

alter table test_kl add index index_name(name) ;

使用这个索引，查看key_len，发现key_len为60，原因：utf-8编码一个字符为三个字节，在创建表的时候指定20个字符，所以为60

explain select * from test_kl where name ='' ;

+----+-------------+---------+------------+------+---------------+------------+---------+-------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | test_kl | NULL       | ref  | index_name    | index_name | 60      | const |    1 |   100.00 | Using index |
+----+-------------+---------+------------+------+---------------+------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

增加一个字段name1，不加not null约束，并加上索引

alter table test_kl add column name1 char(20) ;

alter table test_kl add index index_name1(name1) ;

使用name1字段的索引进行查找，发现key_len为61，原因：当字段可以为null时，则会使用一个字节进行标识

explain select * from test_kl where name1 ='' ;

+----+-------------+---------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test_kl | NULL       | ref  | index_name1   | index_name1 | 61      | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

删除name和name1字段的索引，增加一个复合索引

drop index index_name on test_kl ;
drop index index_name1 on test_kl ;

alter table test_kl add index name_name1_index (name,name1) ;

尝试以下SQL，查看结果，第一个SQL查找name1字段，其中要使用name和name1，将复合索引完全使用了，所以为121，第二个SQL，只使用到了name，所以为60（最左原则）

explain select * from test_kl where name1 = '' ;

+----+-------------+---------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| id | select_type | table   | partitions | type  | possible_keys | key              | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+---------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | test_kl | NULL       | index | NULL          | name_name1_index | 121     | NULL |    1 |   100.00 | Using where; Using index |
+----+-------------+---------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

explain select * from test_kl where name = '' ;

+----+-------------+---------+------------+------+------------------+------------------+---------+-------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------+------------+------+------------------+------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | test_kl | NULL       | ref  | name_name1_index | name_name1_index | 60      | const |    1 |   100.00 | Using index |
+----+-------------+---------+------------+------+------------------+------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

再增加一个字段name2，字段类型为varchar，并设置单值索引

alter table test_kl add column name2 varchar(20) ;

alter table test_kl add index name2_index (name2) ;

再次使用name2的字段的索引进行查询，key_len为63，原因：utf8一个字符占三个字节（60），可以为null（1），varchar用2个字节来标识可变长度，60+1+2 = 63

explain select * from test_kl where name2 = '' ;

+----+-------------+---------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test_kl | NULL       | ref  | name2_index   | name2_index | 63      | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

utf8:1个字符3个字节
gbk:1个字符2个字节
latin:1个字符1个字节

小结：key_len

当字段可以为null时，则会使用一个字节进行标识
varchar用2个字节来标识可变长度
key_len经常用来查看复合索引的使用情况

ref

注意与type中的ref值区分

该ref的作用是：指明当前表所参照的字段，如果参照的字段是常数，则ref为const

使用teacher和teacherCard表来验证

explain select t.* 
from teacher t,teacherCard tc 
where t.tcid = tc.tcid 
    and tc.tcdesc = 'tz222';

+----+-------------+-------+------------+------+---------------+---------+---------+---------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref           | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+---------+---------+---------------+------+----------+-------------+
|  1 | SIMPLE      | tc    | NULL       | ALL  | PRIMARY       | NULL    | NULL    | NULL          |    3 |    33.33 | Using where |
|  1 | SIMPLE      | t     | NULL       | ref  | uk_tcid       | uk_tcid | 5       | words.tc.tcid |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+------+---------------+---------+---------+---------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

执行发现，t（teacher）表中，ref确实是指向了words（数据库名称）.tc.tcid，但是tc（teacherCard）表却为null，原因：我发现tc表的key为null，说明没有使用到索引，查看之后发现，确实tc表的tcdesc字段没有设置索引

alter table teacherCard add index index_tcdesc (tcdesc);

+----+-------------+-------+------------+------+----------------------+--------------+---------+---------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys        | key          | key_len | ref           | rows | filtered | Extra       |
+----+-------------+-------+------------+------+----------------------+--------------+---------+---------------+------+----------+-------------+
|  1 | SIMPLE      | tc    | NULL       | ref  | PRIMARY,index_tcdesc | index_tcdesc | 603     | const         |    1 |   100.00 | Using index |
|  1 | SIMPLE      | t     | NULL       | ref  | uk_tcid              | uk_tcid      | 5       | words.tc.tcid |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+------+----------------------+--------------+---------+---------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

设置好索引再执行，发现tc表的ref值为const

rows

被索引优化查询的数据个数 (实际通过索引而查询到的数据个数)

filtered

该列指示将按表条件过滤的表行的估计百分比。最大值为100，这意味着不会对行进行过滤。值从100开始减少表示过滤量增加。row显示检查的估计行数，row×filtered表示将与下表连接的行数。例如，如果 row是1000并且 filtered是50.00（50％），则使用下表连接的行数是1000×50％= 500。

Extra

此列包含有关MySQL如何解析查询的其他信息。

OnlyloveCuracao

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
explain命令详解sql优化准备

前提：在Mysql数据库下进行的实践explain：分析SQL的执行计划，可以模拟SQL优化器执行SQL语句，从而让开发人员知道自己编写的SQL状况查询执行计划：explain + SQL语句例如：explain select * from money;打印结果*************************** 1. ro...
复制链接

扫一扫