mysql explain笔试题_MySQL EXPLAIN

最新推荐文章于 2024-03-30 23:40:21 发布

麦子9014

最新推荐文章于 2024-03-30 23:40:21 发布

阅读量181

点赞数

文章标签： mysql explain笔试题

本文链接：https://blog.csdn.net/weixin_42560530/article/details/113968035

版权

EXPLAIN

EXPLAIN 输出大致内容如下

mysql> explain select * from user_info where id = 2\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: user_info

partitions: NULL

type: const

possible_keys: PRIMARY

key: PRIMARY

key_len: 8

ref: const

rows: 1

filtered: 100.00

Extra: NULL

1 row in set, 1 warning (0.02 sec)

各列的含义如下:

id: SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符.

select_type: SELECT 查询的类型.

table: 查询的是哪个表

partitions: 匹配的分区

type: 联接类型

possible_keys: 此次查询中可能选用的索引

key: 此次查询中确切使用到的索引.

ref: 哪个字段或常数与 key 一起被使用

rows: 显示此查询一共扫描了多少行. 这个是一个估计值.

filtered: 表示此查询条件所过滤的数据的百分比

extra: 额外的信息

详细介绍

select_type

表示了查询的类型, 它的常用取值有:

SIMPLE, 简单的SELECT(不使用UNION或子查询)

PRIMARY, 表示此查询是最外层的查询

UNION, 表示此查询是 UNION 的第二或随后的查询

DEPENDENT UNION, UNION 中的第二个或后面的查询语句, 取决于外面的查询

UNION RESULT, UNION 的结果

SUBQUERY, 子查询中的前 SELECT个

DEPENDENT SUBQUERY: 子查询中的第一个 SELECT, 取决于外面的查询. 即子查询依赖于外层查询的结果.

mysql> EXPLAIN (SELECT * FROM user_info WHERE id IN (1, 2, 3)) UNION (SELECT * FROM user_info WHERE id IN (3, 4, 5));

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

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

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

3 rows in set, 1 warning (0.02 sec)

table

表示查询涉及的表或衍生表

partitions

查询将从中匹配记录的分区，对于非分区 table，该值为NULL

type

它提供了判断查询是否高效的重要依据依据. 通过 type 字段, 我们判断此次查询是全表扫描还是索引扫描等.

system 该 table 只有一行(=系统 table)。

const 该 table 最多具有一个匹配行，该行在查询开始时读取。因为只有一行，所以优化器的其余部分可以将这一行中列的值视为常量。

eq_ref 此类型通常出现在多表的 join 查询, 表示对于前表的每一个结果, 都只能匹配到后表的一行结果. 并且查询的比较操作通常是=, 查询效率较高

ref 针对于非唯一或非主键索引, 或者是使用了最左前缀规则索引的查询.

index_merge 使用索引合并优化

range 表示使用索引范围查询, 通过索引字段范围获取表中部分数据记录. 这个类型通常出现在 =, <>, >, >=, , BETWEEN, IN() 操作中.

index 表示全索引扫描(full index scan), 和 ALL 类型类似, 只不过 ALL 类型是全表扫描, 而 index 类型则仅仅扫描所有的索引, 而不扫描数据.

ALL: 表示全表扫描, 这个类型的查询是性能最差的查询之一.

fulltext 使用全文索引执行连接。

mysql> explain select * from user_info where id = 2\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: user_info

partitions: NULL

type: const

possible_keys: PRIMARY

key: PRIMARY

key_len: 8

ref: const

rows: 1

filtered: 100.00

Extra: NULL

1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM user_info, order_info WHERE user_info.id = order_info.user_id\G;

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: order_info

partitions: NULL

type: index

possible_keys: user_product_detail_index

key: user_product_detail_index

key_len: 254

ref: NULL

rows: 9

filtered: 100.00

Extra: Using where; Using index

*************************** 2. row ***************************

id: 1

select_type: SIMPLE

table: user_info

partitions: NULL

type: eq_ref

possible_keys: PRIMARY

key: PRIMARY

key_len: 8

ref: cmdb.order_info.user_id

rows: 1

filtered: 100.00

Extra: NULL

2 rows in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM user_info WHERE id BETWEEN 2 AND 8 \G;

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: user_info

partitions: NULL

type: range

possible_keys: PRIMARY

key: PRIMARY

key_len: 8

ref: NULL

rows: 7

filtered: 100.00

Extra: Using where

1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT name FROM user_info \G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: user_info

partitions: NULL

type: index

possible_keys: NULL

key: name_index

key_len: 152

ref: NULL

rows: 10

filtered: 100.00

Extra: Using index

mysql> EXPLAIN SELECT age FROM user_info WHERE age = 20 \G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: user_info

partitions: NULL

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 10

filtered: 10.00

Extra: Using where

type 性能排序

ALL < index < range ~ index_merge < ref < eq_ref < const < system

possible_keys

表示 MySQL 在查询时, 能够使用到的索引.

key

此字段是 MySQL 在当前查询时所真正使用到的索引.

key_len

表示查询优化器使用了索引的字节数. 这个字段可以评估组合索引是否完全被使用, 或只有最左部分字段被使用到.

key_len 的计算规则如下:

字符串

char(n): n 字节长度

varchar(n): 如果是 utf8 编码, 则是 3 n + 2字节; 如果是 utf8mb4 编码, 则是 4 n + 2 字节.

数值类型:

TINYINT: 1字节

SMALLINT: 2字节

MEDIUMINT: 3字节

INT: 4字节

BIGINT: 8字节

时间类型

DATE: 3字节

TIMESTAMP: 4字节

DATETIME: 8字节

mysql> EXPLAIN SELECT * FROM order_info WHERE user_id < 3 AND product_name = 'p1' AND productor = 'WHH' \G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: order_info

partitions: NULL

type: range

possible_keys: user_product_detail_index

key: user_product_detail_index

key_len: 9

ref: NULL

rows: 5

filtered: 11.11

Extra: Using where; Using index

联合索引

KEY `user_product_detail_index` (`user_id`, `product_name`, `productor`)

此查询语句 WHERE user_id < 3 AND product_name = 'p1' AND productor = 'WHH' 中, 因为先进行 user_id 的范围查询, 而根据最左前缀匹配原则, 当遇到范围查询时, 就停止索引的匹配, 因此实际上我们使用到的索引的字段只有 user_id, 因此在 EXPLAIN 中, 显示的 key_len 为 9. 因为 user_id 字段是 BIGINT, 占用 8 字节, 而 NULL 属性占用一个字节, 因此总共是 9 个字节. 若我们将user_id 字段改为 BIGINT(20) NOT NULL DEFAULT '0', 则 key_length 应该是8.

mysql> EXPLAIN SELECT * FROM order_info WHERE user_id = 1 AND product_name = 'p1' \G;

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: order_info

partitions: NULL

type: ref

possible_keys: user_product_detail_index

key: user_product_detail_index

key_len: 161

ref: const,const

rows: 2

filtered: 100.00

Extra: Using index

1 row in set, 1 warning (0.00 sec)

算一下为什么 key_len 的值为 161 ？

9 + 50 * 3 + 2 = 161

ref

显示将哪些列或常量与key列中命名的索引进行比较，以从 table 中选择行。

如果值为func，则使用的值是某个函数的结果。

rows

估算 SQL 要查找到结果集需要扫描读取的数据行数.

这个值非常直观显示 SQL 的效率好坏, 原则上 rows 越少越好.

Extra

Explain 中的很多额外的信息会在 Extra 字段显示

常见：

Using filesort 表示 MySQL 需额外的排序操作, 不能通过索引顺序达到排序效果. 一般有 Using filesort, 都建议优化去掉, 因为这样的查询 CPU 资源消耗大.

Using index "覆盖索引扫描", 表示查询在索引树中就可查找所需数据, 不用扫描表数据文件, 往往说明性能不错

Using temporary 查询有使用临时表, 一般出现于排序, 分组和多表 join 的情况, 查询效率不高, 建议优化.

Using where mysql服务器将在存储引擎检索行后再进行过滤。

mysql> EXPLAIN SELECT * FROM order_info ORDER BY product_name \G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: order_info

partitions: NULL

type: index

possible_keys: NULL

key: user_product_detail_index

key_len: 254

ref: NULL

rows: 9

filtered: 100.00

Extra: Using index; Using filesort

1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM order_info ORDER BY user_id, product_name \G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: order_info

partitions: NULL

type: index

possible_keys: NULL

key: user_product_detail_index

key_len: 254

ref: NULL

rows: 9

filtered: 100.00

Extra: Using index

1 row in set, 1 warning (0.00 sec)

涉及到的表结构整理

mysql> show create table user_info\G;

Table: user_info

Create Table: CREATE TABLE `user_info` (

`id` bigint(20) NOT NULL AUTO_INCREMENT,

`name` varchar(50) NOT NULL DEFAULT '',

`age` int(11) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `name_index` (`name`)

) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8

mysql> show create table order_info\G;

Table: order_info

Create Table: CREATE TABLE `order_info` (

`id` bigint(20) NOT NULL AUTO_INCREMENT,

`user_id` bigint(20) DEFAULT NULL,

`product_name` varchar(50) NOT NULL DEFAULT '',

`productor` varchar(30) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `user_product_detail_index` (`user_id`,`product_name`,`productor`)

) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8

麦子9014

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql explain笔试题_MySQL EXPLAIN

EXPLAINEXPLAIN 输出大致内容如下mysql> explain select * from user_info where id = 2\G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: user_infopartitions: NULLtyp...
复制链接

扫一扫