侧边栏壁纸
博主头像
孔子说JAVA博主等级

成功只是一只沦落在鸡窝里的鹰,成功永远属于自信且有毅力的人!

  • 累计撰写 297 篇文章
  • 累计创建 134 个标签
  • 累计收到 4 条评论

目 录CONTENT

文章目录

Mysql执行计划让你的SQL飞起来

孔子说JAVA
2022-05-04 / 0 评论 / 0 点赞 / 104 阅读 / 17,494 字 / 正在检测是否收录...

实际项目开发中,由于我们不知道实际查询的时候数据库里发生了什么事情,数据库软件是怎样扫描表、怎样使用索引的,我们能感知到的就只有sql语句运行的时间,在数据规模不大时,查询是瞬间的,因此,在写sql语句的时候就很少考虑到性能的问题。但是当数据规模增大,如达到千万、亿数量级的时候,我们运行同样的sql语句时却发现迟迟没有结果,这个时候才知道数据规模已经限制了我们查询的速度。所以,查询优化和索引也就显得很重要了。

1、执行计划简介

1.1 执行计划是什么

执行计划是指一条 SQL 语句在经过 MySQL 查询优化器的优化后,具体的执行方式。一条SQL语句,在数据库中实际执行的时候,会经过很多步骤,其中有些步骤是有多种方式来执行的,mysql优化器会在众多的执行路径中选择一条成本最低的路径作为执行计划,也就是sql执行步骤的集合。所以理解执行计划对于我们排查慢sql有着至关重要的作用。

MySQL 提供了 EXPLAIN 命令,来获取执行计划的相关信息。该命令可以查看查询优化器是如何决定执行查询的主要方法,从它的查询结果中可以知道一个SQL语句的执行顺序,数据查询操作的操作类型,具体每一步是如何执行的,都经历了些什么,有没有用到索引,哪些字段用到了什么样的索引,每个数据表有多少行记录被查询,是否有一些可以优化的地方等,这些信息都是我们SQL优化的依据。

  • 需要注意的是,EXPLAIN 语句并不会真的去执行相关的语句,而是通过查询优化器对语句进行分析,找出最优的查询方案,并显示对应的信息。

1.2 执行计划的使用场景

执行计划通常用于 SQL 性能分析、优化等场景。在数据库性能优化中,执行计划是非常重要的,通过 EXPLAIN 命令,根据执行计划找到存在性能问题的SQL语句,为我们优化SQL提供方向和依据,帮助我们更加明确的来进行SQL优化。

  • EXPLAIN 执行计划支持 SELECT、DELETE、INSERT、REPLACE 以及 UPDATE 语句。我们一般多用于分析 SELECT 查询语句。

2、执行计划的选择依据

不管我们执行单表查询还是多表查询,都有多种执行计划可供选择。MySQL会根据一个查询语句的多个执行计划评估它的成本,然后根据成本评估一个成本最低的执行计划,以保证最佳的查询速度。一条SQL语句执行时的成本包括全表扫描的总成本与索引查询的总成本。

2.1 SQL语句的成本分类

跑一个SQL语句的成本一般包括 IO成本CPU成本

  1. IO成本:首先是那些数据如果在磁盘里,需要先从磁盘里把数据读出来。从磁盘读取数据到内存就是IO成本,而且MySQL里都是一页一页读的,读一页的成本的约定值是 1.0
  2. CPU成本:拿到数据之后,还需要对数据做一些运算,比如验证它是否符合搜索条件,或者进行排序分组等。这些都是耗费CPU资源的,属于CPU成本,一般约定读取和检测一条数据是否符合条件的成本是 0.2

这里的 1.0 和 0.2 是Mysql自定义的一个成本值,代表的意思就是一个数据页IO成本就是 1.0,一条数据检测的CPU成本是 0.2

2.2 单表查询成本计算方式

sql查询数据的方式有2种,一个是全表扫描,另一种是通过索引查询

  • 全表扫描:将表中所有数据从头到尾查一遍,直到找到需要的数据为止。比如查字典中的"中",如果没有拼音或笔画索引,就要从第一页往后依次查,直到找到"中"为止。而且即使找到也不确定后面是不是还有(假定字典是无序状态的),因此还得往后找,知道整本字典翻完,才确定“哦,原来刚才找到的那个记录就是想要的结果了”。
  • 通过索引查询:使用索引,只需要扫描一部分数据就可以得到结果,可以避免全表扫描。比如查字典中的"中",在索引页的z序列找到"中"的具体页数,然后直接跳到相应的页码就能找到我们要的结果了,这样就能大大减少查询的时间。

下面我们分别估算一下 全表扫描执行计划的成本索引查询成本

2.2.1 全表扫描执行计划的成本

首先我们要计算全表扫描执行计划的成本,得先拿到这条SQL语句对应表的统计信息(你对某张表进行增删改的操作时,MYSQL都会维护这个表的统计信息),根据这些统计信息就可以计算对应的 IO成本CPU成本。执行以下命令就可以获取到表的统计信息:

show table status like "表名"

image-1651579176830

从上图这条语句的返回结果可以看到 rowsdata_length 两个信息。对于innodb来说,这个rows是个估计值。

  • rows: 就是表里的行记录数。;pppoooik
  • data_length: 就是表的聚簇索引的字节数大小,data_length/1024 = x (kb)单位的大小,然后再除以16kb(默认一页的大小),就是有多少页。
  • 数据页数 = data_length/1024/16

从上述结果中我们可以得到 数据页数和行记录数,根据这2个值我们可以计算出IO成本和CPU成本,最终就可以计算出全表扫描的总成本了。

  • IO成本就是:数据页数量 * 1.0 + 微调值
  • CPU成本就是:行记录数 * 0.2 + 微调值
  • 全表扫描的总成本 = IO成本 + CPU成本 = 数据页数量 * 1.0 + 行记录数 * 0.2 + 微调值

假如一个表有数据页100页,记录数有2万条,此时全表扫描总成本值大致就是100 + 4000 =4100左右。

2.2.2 索引查询总成本

除了全表扫描外,还可能有多个索引都可以使用,但是同时一般只能用一个索引,所以不同索引的使用成本都得计算一下。使用索引访问数据的方式其实很简单,除非你直接根据主键查,那就直接走一个聚簇索引就ok了;否则就是普通索引,一般都是两步走,先从二级索引查询一波数据,再根据这波数据的主键去聚簇索引回表查询:

  1. IO成本:假设根据某个值从二级索引查出来的数据是在两个区间的(100~200 ,400~600),一般一个范围就可以等同于一个数据页。我们假定是两个范围,此时的IO成本为 2,IO成本 = 范围区间个数 * 1.0+微调值
  2. CPU成本:当二级索引数据页到内存里之后,还得根据搜索条件去拿出来一波数据,拿这波数据的过程就是根据搜索条件在二级索引里搜索的过程。此时MySQL会根据一个不怎么准确的算法去估算一下根据查询条件可能会在二级索引里查出多少条数据来,比如估算可能会查到100条数据,此时从二级索引里查询数据的CPU成本就是 100 * 0.2 = 20,CPU成本 = 行记录数 * 0.2+微调值
  3. 拿到这100条数据后,就得回表到聚簇索引里去查询完整数据,此时先估算回表到聚簇索引的IO成本,这里比较粗暴的直接默认1条数据就得回表到聚簇索引查询一个数据页,所以100条数据就是100个数据页的IO成本,也就是100 * 1.0 + 微调值,大致是100左右,IO成本 = 数据页数 * 1.0+微调值
  4. 因为在二级索引里搜索到的数据是100条,然后通过IO成本最多回表到聚簇索引访问100个数据页之后,就可以拿到这100条数据的完整值了,此时就可以针对这100条数据去判断,他们是否符合其他查询条件了,这里耗费的CPU成本就是 100 * 0.2 + 微调值,就是20左右,CPU成本 = 行记录数 * 0.2+微调值
  5. 就可以拿到这50条数据的完整值了,此时就可以针对这50条数据去判断,他们是否符合其他查询条件了,这里耗费的CPU成本就是 50 * 0.2,就是10左右,CPU成本 = 行记录数 * 0.2+微调值
  6. 上述索引查询总成本 = 2 + 20 + 100 + 20 = 142

根据上述估算可以发现,全表扫描成本是4100左右,根据索引查找可能就只有141,所以,很多时候,使用索引和全表扫描,他的成本差距是非常之大的。一般就会针对全表扫描和各个索引的成本,都进行估算,然后比较一下,选择一个成本最低的执行计划。

由上面的分析可以看出,Mysql执行计划的选择是根据数据页,索引以及统计信息等来推断出的,但是在某些情况下,Mysql可能会选错最佳的执行计划

2.3 多表查询成本计算方式

举个例子:select * from t1 join t2 on t1.x1=t2.x1 where t1.x2=xxx and t1.x3=xxx and t2.x4=xxx and t2.x5=xxx

执行过程如下: 一般都会先选择一个驱动表,比如t1作为驱动表,此时就需要根据t1.x2=xxx和t1.x3=xxx这个条件从表里查询一一批符合条件的数据出来,此时就有一个问题了,这里用到了t1的两个字段来筛选数据,可能x2和x3字段都建立索引了,此时到底应该选择哪个索引呢?或者干脆是全表扫描?

此时就会按照之前讲的那套方法来计算针对t1表查询的全表扫描和不同索引的成本,选择一个针对t1表的最佳访问方式,用最低成本从t1表里查出符合条件的数据来,接着就根据这批数据去t2表里查数据,按照连接条件t1.x1=t2.x2去查,同时要符合t2.x4=xxx和t2.x5=xxx这两个条件。

此时一样会根据之前讲解的办法去估算,针对t2表的全表扫描以及基于x4、x5、x1几个字段不同索引的访问的成本,挑选一个成本最低的方法,然后从t2表里把数据给查找出来,就可以,这就完成了多表关联!

所以其实多表关联的成本估算以及执行计划选择方式,跟单表关联基本上是差不多的,也是分为全表扫描+索引扫描的情况分别计算成本值的,只不过多表关联要多查几个表罢了,最后综合比较各种执行路径的成本值,然后使用成本值最低的一个作为执行计划。

3、执行计划详解

当一个SQL发送到MySQL执行时,需要经过内部优化器进行优化,而使用 explain 关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理SQL的,即SQL的执行计划; 如果我们想要查看某条查询语句具体的执行计划,可以在语句前加explain运行,输出的内容就是具体的执行计划。语法如下:

  • EXPLAIN + SQL语句

查询全表数据的执行计划

image-1651639540658

根据主键查询的执行计划

image-1651639618294

对比上述2张图片发现,EXPLAIN的结果总是有相同的列,每一列代表着不同的含义,可变的只是行数和内容。从上面的例子中,我们看到返回的有很多列,为了更加清楚的了解每一列的含义,便于我们更好的完成优化SQL。下面我们就分别介绍一下这些输出字段的涵义。

列名 涵义
id id列,表示查询中执行select子句或操作表的顺序。
select_type 查询类型,主要是用于区分普通查询、联合查询、子查询等复杂的查询。
table 表明对应行正在访问的是哪个表。
partitions 查询涉及到的分区,对于未分区的表,值为 NULL。
type 访问类型,决定如何查找表中的行。
possible_keys 查询可以使用哪些索引。
key 实际使用的索引,如果为NULL,则没有使用索引。
key_len 索引中使用的字节数,查询中使用的索引的长度(最大可能长度),并非实际使用长度,理论上长度越短越好。
ref 显示索引的那一列被使用。
rows 估算出找到所需行而要读取的行数。
filtered 按表条件过滤后,返回结果的行数占读取行数的百分比,值越大越好。
Extra 附加信息

3.1 id列

id列是一个编号,用于标识SELECT查询的序列号,表示执行SQL查询过程中SELECT子句或操作表的顺序。如果该行引用其他行的并集结果,则值可以为 NULL 。

  • 如果在SQL中没有子查询或关联查询,那么id列都将显示一个1。否则,内层的SELECT语句一般会顺序编号。

id 相同时,执行顺序由上向下;当 id 不同时,id 值越大,优先级越高,越先执行。id列分为三种情况:

1)id相同

如下普通查询,没有子查询。

explain select f.* from film f,film_actor fa,actor a where f.film_id = fa.film_id and fa.actor_id = a.actor_id and a.first_name = 'NICK';

image-1651644591071

2)id不同

如果存在子查询,id的序号会递增,id值越大优先级越高,越先被执行。

explain select * from film where film_id = (select film_id from film_actor where actor_id = 2 limit 1);

image-1651644708460

3)id部分相同

前面两种情况同时存在。id如果相同,认为是一组,从上往下执行。在所有组中,id值越大,优先级越高,越先执行。

3.2 select_type列

select_type列表示对应行的查询类型,是简单查询还是复杂查询,主要用于区分普通查询、联合查询、子查询等复杂的查询。

查询的类型,常见的值有:

  1. SIMPLE:简单查询,不包含 UNION 或者子查询。
  2. PRIMARY:查询中如果包含子查询或其他部分,外层的 SELECT 将被标记为 PRIMARY。
  3. SUBQUERY:在select 或where列表中包含了子查询
  4. UNION:在 UNION 语句中,UNION 之后出现的 SELECT。
  5. DERIVED:在 FROM 中出现的子查询将被标记为 DERIVED。MySQL会递归执行并将结果放到一个临时表中,称其为“派生表”,因为该临时表是从子查询中派生而来的。
  6. UNION RESULT:UNION 查询的结果(即从UNION表获取结果的select)。

3.3 table列

table列表示对应行正在执行的哪张表(即查询用到的表名),指代对应表名,或者该表的别名(如果SQL中定义了别名)。每行都有对应的表名,表名除了正常的表之外,也可能是以下列出的值:

  1. 本行引用了 id 为 N 的表所产生的的派生表结果。派生表有可能产生自 FROM 语句中的子查询。
  2. 本行引用了 id 为 N 的表所产生的的物化子查询结果。

3.4 partitions列

查询涉及到的分区。

3.5 type列

查询执行的类型/访问类型,描述了MySQL查询是如何执行的。该值是SQL查询优化中一个很重要的指标,拥有很多值,所有值的顺序从最优到最差排序为:

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

常见的几种类型具体含义如下:

1)system:

如果表使用的引擎对于表行数统计是精确的(如:MyISAM),且表中只有一行记录的情况下,访问方法是 system ,是 const 的一种特例。

  • 表中只有一行记录,这是const类型的特例,比较少见,如:系统表。

2)const:

表中最多只有一行匹配的记录,通过索引一次查询就可以找到,const用于比较primary key 或者unique索引。因为只需匹配一行数据,所以很快。

  • 常用于使用主键或唯一索引的所有字段作为查询条件。
  • 如果将主键置于where列表中,mysql就能将该查询转换为一个const。

3)eq_ref:

唯一索引扫描。当连表查询时,前一张表的行在当前这张表中只有一行与之对应。是除了 system 与 const 之外最好的 join 方式。

  • 常用于使用主键或唯一索引的所有字段作为连表条件。

4)ref:

非唯一性索引扫描,返回匹配某个单独值的所有行。使用普通索引作为查询条件,查询结果可能找到多个符合条件的行。

  • 本质是也是一种索引访问,它返回所有匹配某个单独值的行,然而它可能会找到多个符合条件的行,所以它属于查找和扫描的混合体。
  • 此类型只有当使用非唯一索引或者唯一索引的非唯一性前缀时,才会发生。

5)index_merge:

当查询条件使用了多个索引时,表示开启了 Index Merge 优化,此时执行计划中的 key 列列出了使用到的索引。

6)range:

对索引列进行范围查询,即只检索给定范围的行,使用一个索引来选择行。执行计划中的 key 列表示哪个索引被使用了。

  • 一般就是在where语句中出现了bettween、<、>、in等的查询。这种索引列上的范围扫描比全索引扫描index要好。

7)index:

查询遍历了整棵索引树,与 ALL 类似,只不过扫描的是索引,而索引一般在内存中,速度更快。扫描表时按索引次序进行,而不是按行扫描,即:只遍历索引树。

  • index与ALL虽然都是读全表,但index是从索引中读取,而ALL是从硬盘读取。显然,index性能上优于ALL,合理的添加索引将有助于性能的提升。

举例:

explain select title from film;
explain select description from film;

image-1651652294545

通过explain结果来看,只查询表film中字段title时,是按照索引扫描的(type列为index),倘若查询字段description,却是按照全表扫描的(type列为ALL)。这是为何呢?

接下来,我们不妨看看表film的结构:

image-1651652335268

从desc film结果来看,字段title创建的有索引,而字段description没有,所以select title from film是按索引扫描,而select description from film按全表扫描。

从上面的举例对比中,也充分印证了索引的重要性。

8)ALL:

众所周知的全表扫描,表示通过扫描整张表来找到匹配的行,很显然这样的方式查询速度很慢。

  • 这种情况,性能最差,在写SQL时尽量避免此种情况的出现。

举例:explain select * from film;

image-1651652101452

所以在平时写SQL时,避免使用 select * ,就不难理解了。换言之,是为了避免全表扫描,因为全表扫描是性能最差的。

type 类型举例

create table t_type1(id int auto_increment primary key,uid int,name varchar(20)) engine='myISAM';
insert into t_type1 values(1,1,'cym');

create table t_type2 (id int auto_increment primary key,uid int,name varchar(20)) engine='myISAM';
insert into t_type2 values(1,1,'cym');
insert into t_type2 values(2,2,'cym');

-- ALL
desc select * from t_type2;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t_type2 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+

-- const
desc select * from t_type2 where id=1;
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t_type2 | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

-- system
desc select * from t_type1;
+----+-------------+---------+------------+--------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table   | partitions | type   | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+---------+------------+--------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t_type1 | NULL       | system | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+--------+---------------+------+---------+------+------+----------+-------+

-- index
desc select id from t_type2;
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_type2 | NULL       | index | NULL          | PRIMARY | 4       | NULL |    2 |   100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

-- range
alter table t_type2 add key(name);
desc select * from t_type2 where id>1;
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t_type2 | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    2 |   100.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+

--ref
desc select * from t_type2 where name='cym';
+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t_type2 | NULL       | ref  | name          | name | 83      | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+

-- eq_ref
alter table t_type2 add unique key(uid);
desc select t1.id,t2.name from t_type1 t1,t_type2 t2 where t1.uid=t2.uid and t1.id>1;
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+------------------------------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref        | rows | filtered | Extra                              |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | range  | PRIMARY       | PRIMARY | 4       | NULL       |    2 |   100.00 | Using index condition; Using where |
|  1 | SIMPLE      | t2    | NULL       | eq_ref | uid           | uid     | 5       | cym.t1.uid |    1 |   100.00 | NULL                               |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+------------------------------------+
-- 非唯一列关联时
desc select t1.id,t2.name from t_type1 t1,t_type2 t2 where t1.name=t2.name and t1.id>1;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+---------------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                                                   |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+---------------------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    2 |   100.00 | Using index condition                                   |
|  1 | SIMPLE      | t2    | NULL       | index | name          | name    | 83      | NULL |    2 |    50.00 | Using where; Using index; Using join buffer (hash join) |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+---------------------------------------------------------+
desc select t1.id,t2.name from t_type1 t1,t_type2 t2 where t1.name=t2.name and t2.name='cym';
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+---------------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                                                   |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+---------------------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    2 |   100.00 | Using index condition                                   |
|  1 | SIMPLE      | t2    | NULL       | index | name          | name    | 83      | NULL |    2 |    50.00 | Using where; Using index; Using join buffer (hash join) |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+---------------------------------------------------------+

-- - ref_or_null
desc select * from t_type2 where uid=2 or uid is null;
+----+-------------+---------+------------+-------------+---------------+------+---------+-------+------+----------+-----------------------+
| id | select_type | table   | partitions | type        | possible_keys | key  | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------------+---------------+------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | t_type2 | NULL       | ref_or_null | uid           | uid  | 5       | const |    2 |   100.00 | Using index condition |
+----+-------------+---------+------------+-------------+---------------+------+---------+-------+------+----------+-----------------------+

3.6 possible_keys列

possible_keys 列表示 MySQL 执行查询时可能用到的索引。如果这一列为 NULL ,则表示没有可能用到的索引;这种情况下,需要检查 WHERE 语句中所使用的的列,看是否可以通过给这些列中某个或多个添加索引的方法来提高查询性能。

3.7 key列

key 列表示 MySQL 实际使用到的索引。如果为 NULL,则表示未用到索引。查询中如果使用了覆盖索引,则该索引仅出现在key列中。

possible_keys列表明哪一个索引有助于更高效的查询,而key列表明实际优化采用了哪一个索引可以更加高效。

举例:

show index from film_actor;
explain select actor_id,film_id from film_actor;

image-1651652935487

3.8 key_len列

key_len 列表示索引中使用的字节数, MySQL查询中实际使用的索引的最大可能长度,并非实际使用长度,理论上长度越短越好。

  • 当使用到联合索引时,有可能是多个列的长度和。在满足需求的前提下越短越好。如果 key 列显示 NULL ,则 key_len 列也显示 NULL 。

3.9 ref列

表示在key列记录的索引中查找值,所用的列或常量const。

3.10 rows列

rows 列表示根据表统计信息及选用情况,大致估算出找到所需的记录需要读取的行数,数值越小越好。

  • 这个数字是内嵌循环关联计划里的循环数,它并不是最终从表中读取出来的行数,而是MySQL为了找到符合查询的那些行而必须读取行的平均数,只能作为一个相对数来进行衡量。

3.11 filtered列

返回结果的行数占读取行数的百分比,值越大越好。

举例:

image-1651653199423

表film_actor中actor_id为1的记录有19条,而SQL查询时扫描了19行(rows:19),19条符合条件(filtered: 100 19/19)

3.12 Extra:

这列包含了 MySQL 解析查询的额外信息,但又十分重要。通过这些信息,可以更准确的理解 MySQL 到底是如何执行查询的。常见的值如下:

Using filesort:在排序时使用了外部的索引排序,没有用到表内索引进行排序。也就是说MySQL无法利用索引完成的排序操作成为“文件排序”。

  • 看到这个的时候,查询就需要优化了。mysql需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行。

Using temporary:MySQL 需要创建临时表来存储查询的结果,常见于 ORDER BY 和 GROUP BY。如对查询结果排序时,使用了一个临时表。

  • 看到这个的时候,查询需要优化了。这里,mysql需要创建一个临时表来存储结果,这通常发生在对不同的列集进行ORDER BY上,而不是GROUP BY上。

Using index:表明查询使用了覆盖索引,不用回表,查询效率非常高。

  • 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的,这发生在对表的全部的请求列都是同一个索引的部分的时候。

Using index condition:表示查询优化器选择使用了索引条件下推这个特性。

Using where:表明查询使用了 WHERE 子句进行条件过滤。一般在没有使用到索引的时候会出现。

Using join buffer (Block Nested Loop):连表查询的方式,表示当被驱动表的没有使用索引的时候,MySQL 会先将驱动表读出来放到 join buffer 中,再遍历被驱动表与驱动表进行查询。

这里提醒下,当 Extra 列包含 Using filesort 或 Using temporary 时,MySQL 的性能可能会存在问题,需要尽可能避免。

弄明白了explain语法返回的每一项结果,我们就能知道查询大致的运行时间了,如果查询里没有用到索引、或者需要扫描的行过多,那么可以感到明显的延迟。因此需要改变查询方式或者新建索引。

4、执行计划使用场景

4.1 数据库突然卡住,消耗大量资源

  1. 登录数据库,show processlist,查看阻塞原因
  2. 对找到的SQL语句,执行计划解析,查看使用索引情况
  3. 依据分析情况,在业务低谷期间,修改索引

4.2 常规性的卡顿

  1. 开启slowlog,分析slowlog
  2. 执行计划分析slowlog中的语句,查看索引使用情况
  3. 依据分析情况,在业务低谷期间,修改索引

4、总结

通过上述对执行计划的了解,我们能够从中得到什么?

  • SQL如何使用索引
  • 复杂SQL的执行顺序
  • 查询扫描的数据函数
  • ……

mysql中的explain语法可以帮助我们改写查询,优化表的结构和索引的设置,从而最大地提高查询效率。当然,在大规模数据量时,索引的建立和维护的代价也是很高的,往往需要较长的时间和较大的空间,如果在不同的列组合上建立索引,空间的开销会更大。因此索引最好设置在需要经常查询的字段中。

当面临不够优秀的SQL时,我们首先要查看其执行计划,根据执行计划结果来分析可能存在哪些问题,从而帮助、指导我们是否添加索引、是否调整SQL顺序、是否避免不应该的书写方式等等。

拓展: optimize trace

explain可以输出某条查询语句的具体的执行计划,但是有时候我们想更进一步知道优化器选择执行方案的过程,那就需要用到 optimize trace。

  • optimize trace是mysql 5.6以后的版本中才提供的功能,该功能的开启和关闭是由系统变量optimize_trace来控制的。

在后续的文章中我们将详细介绍 optimize trace 的用法。

5、附录

5.1 建立索引的原则(运维规范)

  1. 必须要有主键,一般是无关列,自增长
  2. 为常在where,order by ,group by,join on,distinct条件后的列做索引
  3. 最好使用唯一值多的列做为联合索引的前导列,就是将唯一值多的列,优先使用,其他的按照联合索引的优化细节来做
  4. 列值长度较长的索引列,使用前缀索引
  5. 降低索引条目,不要创建无效的索引,删除无效索引,使用pt-duplicate-key-checker,percona tookit工具排查
  6. 索引维护要避开业务高峰期
  7. 小表(万条记录以下)不要建立索引

5.2 不走索引的情况(开发规范)

  1. 没有查询条件,或者查询条件没有建立索引
  2. 查询结果集是原表中的大部分数据,在1/4以上,可能不会使用索引
  3. 索引本身失败,统计数据不真实。(实际情况,同一个SQL语句,昨天运行很快,今天很慢。原因可能就是索引失效了)
  4. 查询条件使用函数操作索引列,或对索引列进行运算,包括 + , - , * , /
  5. 隐式转换导致索引失效
  6. <> , not in 不走索引(辅助索引)
  7. like '%aa' 百分号在最前面不走
  8. 联合索引

5.3 索引失效的情况

索引查询的成本相比全表扫描的成本是数量级的差距。所以我们要适当的建立索引,并使它们在执行过程中生效。下面列出索引查询失效,导致全表扫描的几种情况:

(1) 模糊查询like以%开头,索引无效

like 以 % 开头,索引无效;当like前缀没有 %,后缀有 % 时,索引有效。

替代方案: 使用 instr,比如 SELECT * FROM biz_service_iaas WHERE INSTR(id, '2d3b') > 0 等效于 id like '%2d3b%'

(2) 不等于(<>、!=)会引起索引失效,导致全表扫描

替代方案:通过把不等于操作符改成or,可以使用索引,避免全表扫描。例如,把 column<>'aaa' 改成 column < 'aaa' or column > 'aaa',就可以使用索引了。

(3) or语句前后没有同时使用索引会引起索引失效

当or左右查询字段只有一个是索引,该索引失效,只有当or左右查询字段均为索引时,才会生效。

(4) not in会引起索引失效,导致全表扫描

替代方案:使用 not exists

(5) 组合索引,不是使用第一列索引,索引失效

(6) 如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引

select * from emp where job=123,如果job是字符串类型,而这里查询条件123没有用双引号括起来(“123”),会导致数据类型出现隐式转化,有可能会自动转换为int型,使索引无效,产生全表扫描。

(7) 在索引列上使用 IS NULL 或 IS NOT NULL操作,索引有可能失效

索引是不索引空值的,所以这样的操作不能使用索引,可以用其他的办法处理,例如:数字类型,判断大于0,字符串类型设置一个默认值,判断是否等于默认值即可。(此处是错误的!)

(8) 对索引字段进行计算操作、字段上使用函数等,索引失效

(9) 当全表扫描速度比索引速度快时,mysql会使用全表扫描,此时索引失效。

0

评论区