Explain工具使用与索引详解

最新推荐文章于 2024-08-03 21:25:25 发布

阿-牛

最新推荐文章于 2024-08-03 21:25:25 发布

阅读量588

点赞数 1

分类专栏： mysql 文章标签： mysql 数据库 database

本文链接：https://blog.csdn.net/u012267926/article/details/125023806

版权

mysql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

序言

我们一步步的深耕，必将挖掘出属于我们的宝藏。

Explain工具使用

先附上官网文档说明的地址，更深入的大家自行查阅：MySQL :: MySQL 5.7 Reference Manual :: 8.8.2 EXPLAIN Output Format

建表示例

CREATE TABLE `test_author` (
  `id` int(11) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `utime` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='作者表';

insert into test_author(id,name) values(1,'牛仔');
insert into test_author(id,name) values(2,'小海豚');

CREATE TABLE `test_boot` (
  `id` int(11) NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `common_index` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='出版书籍表';

insert into test_book(id,name)values(1,'西游记');
insert into test_book(id,name)values(2,'葫芦娃');

CREATE TABLE `author_id` (
  `id` int(11) NOT NULL,
  `author_id` int(11) DEFAULT NULL,
  `book_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `union_author_book` (`author_id`,`book_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into test_author_book values(1,1,1),(2,1,2);

使用方式

在我们正常的sql语句前面添加参数 explain

Explain列说明

id : SQL语句总有几个sql语句就会有几个id编号，编号值越高，执行越早。如果id为空，则最后执行。
select_type : 表示该语句是简单查询还是复杂查询。大体常用的有如下几种：

(1) simple : 简单查询，该查询不包含子查询和union。例如：
```
explain select * from test_author where id = 1; 
```
(2) primary: 复杂查询中最外层的select。
(3) subquery: select语句最为属性列的查询（非from之后的）。
(4) derived: from之后的表是由select语句产生的。
2,3,4示例如下：
(未关闭对衍生表的合并优化前：)
(关闭对衍生表的合并优化后：set session optimizer_switch='derived_merge=off';)

(5) union: union之后的所有select语句。例如：
table: 表示用的表是哪一个。
当from语句中有子查询时，名字可能是<derivedN>,其中N为id所对应行的表。
当有union时，名字可能是<union1,2>代表，id为1，2对应行的select。
partions: SQL所属的分区是哪一个。
type: SQL执行过程中有无用到索引。其值以及执行优先级为：
system>const>eq_ref>ref>range>index>all
我们优化一般都是尽量向range靠拢，最好达到ref.

NULL : 执行效率最高的。可以理解表都不用查。例如：
select max(id),min(id) from test_book; 在一个排好序的二叉树里面，最大，最小我们只需要获取开头或者结尾的数值即可。
CONST: 常量值查询。例如用主键或者唯一索引，只能得到一行。
SYSTEM: 可以理解成常量查询的特例。例如从常量查询结果集中查询（即只有一条记录的常量派生表）
EQ_REF: 关联表关联条件为主键或者唯一索引。且被关联表（test_book)所有字段都是索引。如果有一个字段是非索引，则类型会变成ALL.
REF: 使用普通索引查询或联合索引最左匹配原则。

RANGE: 一般出现在 in,between,>,< 或>= 或模糊查询如 like '葫%'

INDEX: 一般出现在覆盖索引所在的表中。如test_book表，通过查询二级索引就能获取所有数据。explain select * from test_book;

ALL: 即要全表扫描。
possible_keys: 即可能用到的索引。
key: 语句中实际用到的索引。如果为NULL，说明没有使用。
key_len: 用到索引所占用的字节数。规则如下：
char/varchar(n):如果非utf-8 则纯数字或者字母都是1个字节；汉字则是3个字节
char(n)=3n; varchar(n)=3n+2,其中2来存取字符串长度【因为varchar是变长字符串】；
tinyint:1字节；smallInt:2字节；int:4字节；bigint:8字节；
date:3字节；timestamp:字节；datetime:8字节；
如果索引允许为NULL，则额外需要1个字节存储；索引最大长度为768个字节，字符串过长，则会将前部分字符串作为索引。
ref: 这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量）,字段名（库名.test_author.id）
rows : mysql要检测的大概行数。
extra : 展示的额外信息。
Using Index:使用到了覆盖索引（要查询的字段为某个索引值或者联合索引的一部分）

Using Where:查询语句使用到了where,且查询条件是非索引字段。
Using index Condition: 查询语句使用到了where,且查询条件是索引字段。
Using temporary: mysql需要创建一张临时表来处理数据。

Using fileSort: 将用外部排序而不是内存排序

Select tables optimized away:使用聚合函数比如max,min来查询索引字段。如果非索引字段那么Extra为NULL