Mysql优化基础之Explain工具

2022/6/3 2:20:24

本文主要是介绍Mysql优化基础之Explain工具,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

字段解释


  • id:代表sql中查询语句的序列号,序列号越大则执行的优先级越高,序号一样谁在前谁先执行。id为null则最后执行
  • select_type:查询类型,表示当前被分析的sql语句的查询的复杂度。这个字段有多个值。
  1. SIMPLE:表示简单查询
  2. PRIMARY:表示复杂查询中的最外层的select查询语句
  3. SUBQUERY:表是子查询语句 跟在select 关键字后面的select查询语句;
  4. derived: 派生查询,跟在一个select查询语句的from关键字后面的select查询语句
  • table:表示当前访问的表的名称。
  • partitions:返回的是数据分区的信息(不常用)
  • type:这个字段决定mysql如何查找表中的数据,查找数据记录的大概范围。
  • possible_keys:这个字段显示的是sql在查询时可能使用到的索引,但是不一定真的使用,只是一种可能。

如果这一列有值,但是key列为null,那是因为mysql觉得可能会使用索引,但是又因为表中的数据很少,使用索引反而没有全表扫描效率高,那么mysql就不会使用索引查找

该列如果是NULL,则没有相关的索引,考虑where条件语句的字段加索引

  • key:SQL执行中真正用到的索引字段。
  • key_len:用到的索引字段的长度,通过这个字段可以显示具体使用到了索引字段中的哪些列(主要针对组合索引
  • ref:表示那些列或常量被用于查找索引列上的值
  • rows:表示在查询过程中检索了多少列 但是并不一定就是返回这么多列数据。
  • Extra:展示一些额外信息,标识着表的索引使用情况。

详情参考 http://blog.wingflare.com/2019/10/y2x1d5wv3q6prl3m.html

https://blog.csdn.net/it_tifarmer/article/details/111566731


TYPE详解


有以下几张表的情况:

-- 演员表
 CREATE TABLE `actor` (
 `id` INT ( 11 ) NOT NULL,
 `name` VARCHAR ( 45 ) DEFAULT NULL,
 `update_time` datetime DEFAULT NULL,
 PRIMARY KEY ( `id` )  
) ENGINE = INNODB DEFAULT CHARSET = utf8;
 
INSERT INTO `actor` (`id`, `name`, `update_time`) VALUES (1,'a','2017‐12‐22 15:27:18'), (2,'b','20 17‐12‐22 15:27:18'), (3,'c','2017‐12‐22 15:27:18');
 
-- 电影表
CREATE TABLE `film` (
 `id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
 `name` VARCHAR ( 10 ) DEFAULT NULL,
 PRIMARY KEY ( `id` ),
 KEY `idx_name` ( `name` )  
) ENGINE = INNODB DEFAULT CHARSET = utf8;
 
INSERT INTO `film` (`id`, `name`) VALUES (3,'film0'),(1,'film1'),(2,'film2');
 
-- 演员和电影中间表
CREATE TABLE `film_actor` (
 `id` INT ( 11 ) NOT NULL,
 `film_id` INT ( 11 ) NOT NULL,
 `actor_id` INT ( 11 ) NOT NULL,
 `remark` VARCHAR ( 255 ) DEFAULT NULL,
 PRIMARY KEY ( `id` ),
 KEY `idx_film_actor_id` ( `film_id`, `actor_id` )  
) ENGINE = INNODB DEFAULT CHARSET = utf8;
 
INSERT INTO `film_actor` (`id`, `film_id`, `actor_id`) VALUES (1,1,1),(2,1,2),(3,2,1);

从好到差的顺序为:system > const > eq_ref > ref > range > index > all; 一般来说我们优化到range就可以了 最好到ref

  1. null:type字段的值如果为null,那么表示当前的查询语句不需要访问表,只需要从索引树中就可以获取我们需要的数据

    explain select id from actor where id =1;
    
  2. system/const:用户主键索引或者唯一索引查询时,只能匹配1条数据 一般可以对sql查询语句优化成一个常量,那么type一般就是system或者const,system是const的一个特例。

    explain select * from (select * from film where id = 1) tmp;
    
  3. eq_ref:在进行连接查询时,例如left join 时,如果是使用主键索引或者唯一索引连接查询 ,结果返回一条数据,则type的值为一般为eq_ref。

  4. ref:相比较eq_ref,不使用主键索引或者唯一索引,使用的是普通索引或者唯一索引的部分前缀,索引与一个值进行比较后可能获取到多个符合条件的行,不在是唯一的行了。

    唯一索引的部分前缀:组合索引是唯一索引的一类,这里指组合索引的前缀部分入组合索引a_b_c,那么a和a_b是唯一索引a_b_c的前缀部分

  5. range:通常使用范围查找,例如between,in,<,>,>=等使用索引进行范围检索。

     explain select * from film where id >2;
    
  6. index:扫描索引树就能获取到的数据,一般是扫描二级索引,并且不会从根节点扫描,一般直接扫描二级索引的叶子节点,速度比较慢。因为二级索引叶子节点不保存表中其他字段数据 只保存主键,所以二级索引还是比较小的,扫描速度相比All还是很快的。这里用到了覆盖索引,什么是覆盖索引:可以直接遍历索引树就能获取数据叫做覆盖索引。这里遍历name索引树就可以获取到主键id的值就是覆盖索引。

    explain select id from film ;
    
  7. ALL:这是一种效率最低的type,需要扫描主键索引树的叶子节点,获取数据是表中其他列的数据,即全表扫描(扫描主索引树的叶子节点)。

    explain select remark from film ;
    

key_len计算


字符串
  • char(n):n字节长度
  • varchar(n):如果是utf-8,则长度 3n + 2 字节,加的2字节用来存储字符串长度
数值类型
  • tinyint:1字节
  • smallint:2字节
  • int:4字节
  • bigint:8字节
时间类型
  • date:3字节
  • timestamp:4字节
  • datetime:8字节

注意:如果字段允许为 NULL,需要多1字节记录是否为 NULL

假如为某个表的一下三个字段建立组合索引(name_age_phone)

name varchar(50) DEFAULT NULL COMMENT '姓名',

age int(11) DEFAULT NULL COMMENT '年龄',

phone varchar(12) DEFAULT NULL,

则完全使用索引的key_len计算为:key_len=name(50*3+2+1=153)+age(4+1)+phone(12*3+2+1=39)



这篇关于Mysql优化基础之Explain工具的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!


扫一扫关注最新编程教程