mysql执行计划字段解析_MySQL优化-(3)-explain分析执行计划字段说明

最新推荐文章于 2023-06-15 20:02:47 发布

张小庑

最新推荐文章于 2023-06-15 20:02:47 发布

阅读量397

点赞数

文章标签： mysql执行计划字段解析

本文链接：https://blog.csdn.net/weixin_33178459/article/details/113233679

版权

使用explain的12个字段说明

0. 前情提要: 用到的几个表说明:

0.1. goods表和goods2两个表结构和数据相同(复制的表)-test库mysql> show create table goods;

CREATE TABLE `goods` (

`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

`name` varchar(10) DEFAULT NULL,

`price` double DEFAULT NULL,

UNIQUE KEY `id` (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=150001 DEFAULT CHARSET=utf8

mysql> show create table goods2;

CREATE TABLE `goods2` (

`id` bigint(20) unsigned NOT NULL DEFAULT '0',

`name` varchar(10) CHARACTER SET utf8 DEFAULT NULL,

`price` double DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

mysql> select count(*) from goods;

+----------+

| count(*) |

+----------+

| 150000 |

+----------+

1 row in set (0.02 sec)

mysql> select count(*) from goods2;

+----------+

| count(*) |

+----------+

| 150000 |

+----------+

1 row in set (0.05 sec)

0.2. t2表是goods表中的前10条-test库mysql> select * from t2;

+----+---------+--------+

| id | name | price |

+----+---------+--------+

| 1 | 商品1 | 200.17 |

| 2 | 商品2 | 200.87 |

| 3 | 商品3 | 200.81 |

| 4 | 商品4 | 200.43 |

| 5 | 商品5 | 200.73 |

| 6 | 商品6 | 200.36 |

| 7 | 商品7 | 200.61 |

| 8 | 商品8 | 200.98 |

| 9 | 商品9 | 200.06 |

| 10 | 商品0 | 200.38 |

+----+---------+--------+

10 rows in set (0.00 sec)

mysql> show create table t2;

CREATE TABLE `t2` (

`id` bigint(20) unsigned NOT NULL DEFAULT '0',

`name` varchar(10) CHARACTER SET utf8 DEFAULT NULL,

`price` double DEFAULT NULL,

UNIQUE KEY `id` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

0.3. sakila库的film表:mysql> show create table film;

CREATE TABLE `film` (

`film_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,

`title` varchar(128) NOT NULL,

`description` text,

`release_year` year(4) DEFAULT NULL,

`language_id` tinyint(3) unsigned NOT NULL,

`original_language_id` tinyint(3) unsigned DEFAULT NULL,

`rental_duration` tinyint(3) unsigned NOT NULL DEFAULT '3',

`rental_rate` decimal(4,2) NOT NULL DEFAULT '4.99',

`length` smallint(5) unsigned DEFAULT NULL,

`replacement_cost` decimal(5,2) NOT NULL DEFAULT '19.99',

`rating` enum('G','PG','PG-13','R','NC-17') DEFAULT 'G',

`special_features` set('Trailers','Commentaries','Deleted Scenes','Behind the Scenes') DEFAULT NULL,

`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

PRIMARY KEY (`film_id`),

KEY `idx_title` (`title`),

KEY `idx_fk_language_id` (`language_id`),

KEY `idx_fk_original_language_id` (`original_language_id`),

CONSTRAINT `fk_film_language` FOREIGN KEY (`language_id`) REFERENCES `language` (`language_id`) ON UPDATE CASCADE,

CONSTRAINT `fk_film_language_original` FOREIGN KEY (`original_language_id`) REFERENCES `language` (`language_id`) ON UPDATE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8mb4;

1 row in set (0.00 sec)

mysql> select count(*) from film;

+----------+

| count(*) |

+----------+

| 1000 |

+----------+

1 row in set (0.00 sec)

0.4. sakila库的film_category表mysql> show create table film_actor;

CREATE TABLE `film_actor` (

`actor_id` smallint(5) unsigned NOT NULL,

`film_id` smallint(5) unsigned NOT NULL,

`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

PRIMARY KEY (`actor_id`,`film_id`),

KEY `idx_fk_film_id` (`film_id`),

CONSTRAINT `fk_film_actor_actor` FOREIGN KEY (`actor_id`) REFERENCES `actor` (`actor_id`) ON UPDATE CASCADE,

CONSTRAINT `fk_film_actor_film` FOREIGN KEY (`film_id`) REFERENCES `film` (`film_id`) ON UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

mysql> select count(*)from film_actor;

+----------+

| count(*) |

+----------+

| 5462 |

+----------+

1 row in set (0.00 sec)

0.5. sakila库的film_category表mysql> show create table film_category;

CREATE TABLE `film_category` (

`film_id` smallint(5) unsigned NOT NULL,

`category_id` tinyint(3) unsigned NOT NULL,

`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

PRIMARY KEY (`film_id`,`category_id`),

KEY `fk_film_category_category` (`category_id`),

CONSTRAINT `fk_film_category_category` FOREIGN KEY (`category_id`) REFERENCES `category` (`category_id`) ON UPDATE CASCADE,

CONSTRAINT `fk_film_category_film` FOREIGN KEY (`film_id`) REFERENCES `film` (`film_id`) ON UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

mysql> select count(*) from film_category;

+----------+

| count(*) |

+----------+

| 1000 |

+----------+

1 row in set (0.00 sec)

0.6. sakila库的actor表mysql> show create table actor;

CREATE TABLE `actor` (

`actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,

`first_name` varchar(45) NOT NULL,

`last_name` varchar(45) NOT NULL,

`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

PRIMARY KEY (`actor_id`),

KEY `idx_actor_last_name` (`last_name`)

) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8mb4

mysql> select count(*) from actor;

+----------+

| count(*) |

+----------+

| 200 |

+----------+

1 row in set (0.00 sec)

explain一共有12个字段, 下面具体介绍:

1. id=步骤编号: 表示当前执行计划的第几步执行计划可能分很多步, 每一行表示一个步骤, id=1表示是第1步;

如果编号id相同, 执行顺序就是从上到下;

id越大越先执行

id:1

1.1 id越大的越先执行mysql> explain select (select 1 from actor limit 1) from film;

+----+-------------+-------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+

+----+-------------+-------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+

+----+-------------+-------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+

2 rows in set, 1 warning (0.00 sec)

下面的 2.row id=2 最大, 第二步就先执行;

第二步:是子查询;

连接类型是 index;

用到的索引名是 idx_actor_last_name;

扫描的索引长度182;

可能会检测的行数200;

扩展信息: 使用了索引;

1.2 id相同的前面的先执行

2. select_type=查询类型: 表示是简单查询还是复杂查询select_type=primary 复杂查询=包含union查询或者包含子查询

select_type=simple 简单查询=不包含union, 也不包含子查询;

select_type=union

select_type=union result

select_type=dependent union

select_type=subquery

select_type=derived

select_type=materializaton

2.1. select_type=simple: 简单查询表示不需要 union 操作, 或者不包含子查询

有连接查询时, 外层的查询为simple, 且只有一个;

2.2. select_type=primary: 复杂查询(有union操作或有子查询)

复杂查询: 有union操作; 或者有子查询;

2.2.1 union查询实例:mysql> explain select film_id from film union all select film_id from film_actor;

+----+-------------+------------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+

+----+-------------+------------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+

+----+-------------+------------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+

2 rows in set, 1 warning (0.00 sec)

有union查询的步骤, 位于最外层的查询的select_type即为 primary, 且只有一个;

2.3 select_type=union 连接查询

union查询=连接查询, 连接的是2个select查询:

第一个查询是: derived派生的表; 除了一个之外, 第二个以后的表select_type都是union;

2.4 select_type=union result 此类型id为空(mysql5.7.30以后都没有了)

2.5 select_type=dependent union

此查询跟union 一样, 出现在union或union all 语句中, 但是这个查询要受外部查询的影响;mysql> explain select * from film_category where film_id in (select film_id from film union all select film_id from film_actor);

+----+--------------------+---------------+------------+--------+----------------+----------------+---------+------+------+----------+-------------+

+----+--------------------+---------------+------------+--------+----------------+----------------+---------+------+------+----------+-------------+

| 3 | DEPENDENT UNION | film_actor | NULL | ref | idx_fk_film_id | idx_fk_film_id | 2 | func | 5 | 100.00 | Using index |

+----+--------------------+---------------+------------+--------+----------------+----------------+---------+------+------+----------+-------------+

3 rows in set, 1 warning (0.00 sec)

2.6 select_type=subquery 子查询

除了from子句中包含的子查询外, 其他地方出现的子查询都可能是 subquery;mysql> explain select (select 1 from actor limit 1) from film;

+----+-------------+-------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+

+----+-------------+-------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+

+----+-------------+-------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+

2 rows in set, 1 warning (0.00 sec)

2.7 select_type=derived from子句中出现的子查询也叫派生表

但是在5.7以后的版本中没有 derived这个类型了, 做了优化;

5.6还是有的, 看: 5.7.30:mysql> explain select (select 1 from actor where film_id=1) from (select * from film where film_id=1) der;

+----+--------------------+-------+------------+-------+---------------+---------------------+---------+-------+------+----------+--------------------------+

+----+--------------------+-------+------------+-------+---------------+---------------------+---------+-------+------+----------+--------------------------+

+----+--------------------+-------+------------+-------+---------------+---------------------+---------+-------+------+----------+--------------------------+

2 rows in set, 2 warnings (0.00 sec)

2.8 select_type=materializaton 物化(具体化): 将子查询结果作为一个临时表来加快执行速度

正常来讲是常驻内存, 下次查询会再次引用临时表;

如果是一个大表, 大表作为一个子查询, 就会在第一次查询时生成一个临时表, 后面每次引用;mysql> explain select * from (select * from goods) s where id in (select id from goods2);

+----+--------------+-------------+------------+--------+---------------+------------+---------+------+--------+----------+-------------+

+----+--------------+-------------+------------+--------+---------------+------------+---------+------+--------+----------+-------------+

| 1 | SIMPLE | goods | NULL | ALL | id | NULL | NULL | NULL | 149548 | 100.00 | NULL |

| 1 | SIMPLE | | NULL | eq_ref | | | 8 | func | 1 | 100.00 | Using where |

+----+--------------+-------------+------------+--------+---------------+------------+---------+------+--------+----------+-------------+

3 rows in set, 1 warning (0.00 sec)

subquery3的子查询依赖3的物化的, 物化先执行, 然后是goods, 最后在是子查询;

物化前提是: 表记录比较多

3. table=表名: 表示当前这一步涉及的表都有哪些table表示查询使用的表名, 如果查询使用了别名, 显示的就是别名;

如果并不涉及数据表的操作, table=null;

如果显示为就表示这是个临时表, N就是步骤的id;

如果显示为也是个临时表, 表示, 是M, N两个步骤id的结果集;table:film

4. partitions=分区状况partitions:NULL

5. type=连接类型: 用的全表扫描/有没有走索引

type的效率由高到低排序:type=system 最高效;

type=const 用到索引: 且是唯一索引或主键且+ where返回了只有1行;

type=eq_ref 用到索引: 连接查询中前表每个结果, 都只匹配后表一行结果. 且比较是唯一索引, 查询效率较高.

type=ref 用到索引: 多表的 join 查询, 非唯一或非主键索引, 或者是使用了最左前缀规则索引的查询.

type=fulltext 用到全文索引

type=ref_or_null 用到索引, 相当于 ref+ xxx is null的条件

type=unique_subquery 用于在in形式查询,子查询返回不重复的唯一值(略)

type=index_subquery 用于in形式子查询用到辅助索引或者in常数列表,子查询可能返回重复值 (略)

type=range 字段上有索引的范围扫描: 常见在索引字段使用 >,

type=index_merge 使用了2个以上的索引, 常见: and, or的多个列都有索引, 根据索引查出来进行合并;

type=index 索引从头到尾扫一遍; 不用查表了; select name from t2; name上有索引, 但是不指定where条件;

type=all 性能最差: 全表扫描数据, 然后在server层返回数据过滤返回符合的数据;

5.1 type=system 最高效

type=system 表只有1行数据或是空表; 且只用于 myisam和memory表; innodb的type列通常为all或index;

5.2 type=const 唯一索引或主键+返回一行记录时

使用唯一索引或主键, 返回结果一定是1行记录的等值where条件时, type=const通常

先来一个普通索引:mysql> alter table t2 add index(id);

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> select * from t2 where id=2;

+----+---------+--------+

| id | name | price |

+----+---------+--------+

| 2 | 商品2 | 200.87 |

+----+---------+--------+

1 row in set (0.00 sec)

mysql> explain select * from t2 where id=2;

+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+

+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+

| 1 | SIMPLE | t2 | NULL | ref | id | id | 8 | const | 1 | 100.00 | NULL |

+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+

1 row in set, 1 warning (0.00 sec)

看到type=试试ref; 不是const;

改为唯一索引试试, 先删掉普通索引mysql> show index from t2;

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| t2 | 1 | id | 1 | id | A | 10 | NULL | NULL | | BTREE | | |

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

1 row in set (0.00 sec)

mysql> drop index id on t2;

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> show index from t2;

Empty set (0.00 sec)

新建唯一索引, 再试试:mysql> alter table t2 add unique(id);

Query OK, 0 rows affected (0.11 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> explain select * from t2 where id>3;

+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+

+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+

| 1 | SIMPLE | t2 | NULL | range | id | id | 8 | NULL | 7 | 100.00 | Using where |

+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+

1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t2 where id=2;

+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+-------+

+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+-------+

| 1 | SIMPLE | t2 | NULL | const | id | id | 8 | const | 1 | 100.00 | NULL |

+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+-------+

1 row in set, 1 warning (0.00 sec)id>3是 type=range; id=2时, 唯一索引, type=const

5.3 type=eq_ref 多表join查询时, 对于前表的每一行数据, 后表中只返回一行匹配

说明: t2和goods表中数据是一样的, 只是goods中有近15万条数据, t2中只有10条且t2表id有unique索引;mysql> explain select * from t2 left join goods on t2.id=goods.id;

+----+-------------+-------+------------+--------+---------------+------+---------+------------+------+----------+-------+

+----+-------------+-------+------------+--------+---------------+------+---------+------------+------+----------+-------+

| 1 | SIMPLE | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | NULL |

| 1 | SIMPLE | goods | NULL | eq_ref | id | id | 8 | test.t2.id | 1 | 100.00 | NULL |

+----+-------------+-------+------------+--------+---------------+------+---------+------------+------+----------+-------+

2 rows in set, 1 warning (0.00 sec)

mysql> explain select * from t2 right join goods on t2.id=goods.id;

+----+-------------+-------+------------+--------+---------------+------+---------+---------------+--------+----------+-------+

+----+-------------+-------+------------+--------+---------------+------+---------+---------------+--------+----------+-------+

| 1 | SIMPLE | t2 | NULL | eq_ref | id | id | 8 | test.goods.id | 1 | 100.00 | NULL |

+----+-------------+-------+------------+--------+---------------+------+---------+---------------+--------+----------+-------+

2 rows in set, 1 warning (0.00 sec)

mysql>

5.4 type=ref 多表的 join 查询, 非唯一或非主键索引, 或者是使用了最左前缀规则索引的查询.mysql> explain select * from film where title = 'film2';

+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+

+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+

| 1 | SIMPLE | film | NULL | ref | idx_title | idx_title | 514 | const | 1 | 100.00 | NULL |

+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+

1 row in set, 1 warning (0.00 sec)

mysql>

title列不是唯一索引, 也不是主键列; 但是它也是索引列:KEY idx_title (title),

5.5 type=fulltext 用到全文索引

全文索引的优先级很高, 如果全文索引和普通索引同时存在 ,mysql 不管性能代价, 会优先使用全文索引;

5.6 type=ref_or_nul 实际用的少: 类似ref, 但增加了null值的比较mysql> alter table t2 add index(name);

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> explain select * from t2 where name='商品1' or name is null;

+----+-------------+-------+------------+-------------+---------------+------+---------+-------+------+----------+-----------------------+

+----+-------------+-------+------------+-------------+---------------+------+---------+-------+------+----------+-----------------------+

| 1 | SIMPLE | t2 | NULL | ref_or_null | name | name | 33 | const | 2 | 100.00 | Using index condition |

+----+-------------+-------+------------+-------------+---------------+------+---------+-------+------+----------+-----------------------+

1 row in set, 1 warning (0.00 sec)

如果没有 null判断, 就是ref了;

5.7 type=unique_subquery 有索引:在where中in,子查询返回不重复的唯一值

5.8 type=index_subquery 用于in形式子查询用到辅助索引或者in常数列表,子查询可能返回重复值

5.9 type=range 有索引的范围扫描: 常见在索引字段使用 >,

5.10 type=index_merge 使用了2个以上的索引, 常见: and, or的多个列都有索引, 根据索引查出来进行合并;

5.11 type=index 索引从头到尾扫一遍; 不查表; select name from t2; name上有索引, 但是不指定where条件;

5.12 type=all 性能最差: 全表扫描数据, 然后在server层返回数据过滤返回符合的数据;

小结: 好的查询一般至少达到 range级别, 最好达到ref;

缩小下范围: 常见的有:

system/const/eq_ref/ref/range/index/all

6. possible_keys=可能用到的索引:当前这一步有可能用到的索引有哪些,都列出来possible_keys:NULL

7. key=索引: 确定用了的索引key:NULL

8. key_len=索引长度: 越小越好(越短越好)key_len:NULL

9. ref=索引具体在哪一列上ref:NULL

9.1 如果使用的常数等值查询, 会显示const;

9.2 如果是连接查询, 被驱动表的执行计划此处会显示驱动表的关联字段;mysql> explain select * from t2 left join goods on t2.id=goods.id;

+----+-------------+-------+------------+--------+---------------+------+---------+------------+------+----------+-------+

+----+-------------+-------+------------+--------+---------------+------+---------+------------+------+----------+-------+

| 1 | SIMPLE | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | NULL |

| 1 | SIMPLE | goods | NULL | eq_ref | id | id | 8 | test.t2.id | 1 | 100.00 | NULL |

+----+-------------+-------+------------+--------+---------------+------+---------+------------+------+----------+-------+

2 rows in set, 1 warning (0.00 sec)

9.3 如果是条件使用了表达式或函数, 或条件列发生了内部隐式转换, 此处显示为func;mysql> explain select * from film_category where film_id in (select film_id from film union all select film_id from film_actor);

+----+--------------------+---------------+------------+--------+----------------+----------------+---------+------+------+----------+-------------+

+----+--------------------+---------------+------------+--------+----------------+----------------+---------+------+------+----------+-------------+

| 3 | DEPENDENT UNION | film_actor | NULL | ref | idx_fk_film_id | idx_fk_film_id | 2 | func | 5 | 100.00 | Using index |

+----+--------------------+---------------+------------+--------+----------------+----------------+---------+------+------+----------+-------------+

10. rows=当前这一步可能会检测的行数rows:1000

估算行, 非精确值;

11. filtered=过滤后返回数据的百分比: 经过server过滤后实际返回客户端的百分比filtered:100.00

12. Extra=扩展信息: 有没有排序/有没有用临时表, 很多种类型Extra:NULL

12.1 no table usedmysql> explain select 1;

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+

1 row in set, 1 warning (0.00 sec)

12.2 using index 使用到了索引mysql> explain select name from t2 ;

+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+

+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+

| 1 | SIMPLE | t2 | NULL | index | NULL | name | 33 | NULL | 10 | 100.00 | Using index |

+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+

1 row in set, 1 warning (0.00 sec)

12.3 NULL: 查询到的列有未被索引覆盖到的(就是查了几个列, 其中有的没索引)mysql> explain select name, price from t2 ;

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

| 1 | SIMPLE | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | NULL |

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

1 row in set, 1 warning (0.00 sec)

不是纯粹用索引, 但是也用到了索引;

或者, 就是未被索引覆盖到; 查了没用索引的列, 也都是 NULL;

12.4 using where 查询的where条件是没有索引的mysql> explain select price from t2 where price=2;

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

| 1 | SIMPLE | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 10.00 | Using where |

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

1 row in set, 1 warning (0.00 sec)

mysql> explain select price from t2 where name='s' and price=2;

+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+

+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+

| 1 | SIMPLE | t2 | NULL | ref | name | name | 33 | const | 1 | 10.00 | Using where |

+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+

1 row in set, 1 warning (0.00 sec)

where 语句中有的列没有索引;

12.5 using where using index: 查询的符合索引,但是不是第一个列, 用不到索引mysql> alter table t2 add index(name, price);

Query OK, 0 rows affected (0.03 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> explain select * from t2 where price=2;

+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+--------------------------+

+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+--------------------------+

| 1 | SIMPLE | t2 | NULL | index | NULL | name_2 | 42 | NULL | 10 | 10.00 | Using where; Using index |

+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+--------------------------+

1 row in set, 1 warning (0.00 sec)

相当于又找到索引, 又用了where, 为什么呢? 有索引, 但是用不到, 最后还是用了where扫表;

12.6 using index condition: 与using where相似, 查询的列没有完全被索引覆盖

12.7 using temporary: 使用了临时表存储中间结果

12.8 using filesort: 也要考虑优化: 对结果使用了外部索引排序, 而不是按照索引次序从表里读数据行.

张小庑

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql执行计划字段解析_MySQL优化-(3)-explain分析执行计划字段说明

使用explain的12个字段说明0. 前情提要: 用到的几个表说明:0.1. goods表和goods2两个表结构和数据相同(复制的表)-test库mysql> show create table goods;CREATE TABLE `goods` (`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,`name` varchar(10) D...
复制链接

扫一扫