【MySQL高级】索引优化分析(二)

摘要:索引对于良好的性能非常关键,尤其是当表中的数据量越来越大时,索引对性能的影响愈发重要。相对于查询优化,索引优化应该是对查询性能优化最有效的手段。本篇文章介绍了MySQL 查询优化器的处理过程、MySQL常见的瓶颈、Explain 执行计划。其中,使用EXPLAIN关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的。重点介绍了EXPLAIN关键字如何分析你的查询语句或是表结构的性能瓶颈。

性能分析

MySQL 查询优化器

MySQL中有专门负责优化SELECT语句的优化器模块,其主要功能:通过计算分析系统收集到的统计信息,为客户端请求的Query提供它认为最优的执行计划(MySQL认为最优的数据检索方式,但不见得是DBA认为最优的)。
当客户端向MySQL请求一条Query,命令解析器模块完成请求分类。区别出是SELECT并转发给MySQL查询优化器时,查询优化器首先会对整条Query进行优化,处理掉一些常量表达式的预算,直接换算成常量值,并对Query中的查询条件进行简化和转化,如去掉一些无用或显而易见的条件、结构调整等。接下来分析Query中的Hint信息,通过Hint信息是否可以完全确定该Query的执行计划,如果没有Hint信息或者Hint信息还不足以完全确定执行计划,则会读取所涉及对象的统计信息,根据Query进行相应的计算分析,最后得到最终的执行计划。

MySQL 常见的瓶颈

CPU瓶颈:数据装入内存或者从磁盘上读取数据,CPU会出现饱和的情况。
IO瓶颈:磁盘I/O瓶颈发生在装入数据远大于内存容量的时候。
服务器硬件的性能瓶颈:top,free,iostatvmstat来查看系统的性能状态。

Explain 执行计划

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的。通过EXPLAIN关键字可以分析你的查询语句或是表结构的性能瓶颈。以下为其作用:
① 表的读取顺序,通过id关键词来判断。
② 数据读取操作的操作类型,通过type关键词来判断。
③ 哪些索引可以使用,通过possible_keys关键词来判断。
④ 哪些索引被实际使用,通过key关键词来判断。
⑤ 表之间的引用,通过ref关键词来判断。
⑥ 每张表有多少行被优化器查询,通过rows关键词来判断。

执行计划的语法:Explain + SQL语句。
执行计划包含的字段及说明如下:

字段 说明
id 表的加载和读取顺序
select_type 数据的访问类型
table 所加载的表
type 数据的查询类型
possible_keys 可能使用的索引
key 实际使用的索引
key_len 索引的字节数
ref 索引引用的列
rows 读取的行数
extra 其他额外的信息

为了更好的分析每个字段的作用,以下为三个相关建表语句,目的仅用做分析使用:

CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `other_column` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='表t1';
CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `other_column` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='表t2';
CREATE TABLE `t3` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `other_column` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='表t3';

INSERT INTO t1 (id,`other_column`) VALUES (1,'');
INSERT INTO t1 (id,`other_column`) VALUES (2,'');
INSERT INTO t1 (id,`other_column`) VALUES (3,'');
INSERT INTO t2 (id,`other_column`) VALUES (1,'');
INSERT INTO t2 (id,`other_column`) VALUES (2,'');
INSERT INTO t2 (id,`other_column`) VALUES (3,'');
INSERT INTO t3 (id,`other_column`) VALUES (1,1);
INSERT INTO t3 (id,`other_column`) VALUES (2,2);
INSERT INTO t3 (id,`other_column`) VALUES (3,'');

id

SELECT查询的序列号,包含一组数字,表示查询中执行SELECT子句或操作表的顺序。

三种情况:

① id相同,执行顺序由上至下。

-- id相同,执行顺序由上至下
explain select t2.* from t1,t2,t3 where t1.id = t2.id and t1.id = t3.id and t1.other_column = '';

执行结果如下图所示:
index-optimization-01

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

-- id不同,如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行
explain select t2.* from t2 where t2.id = (select t1.id from t1 where t1.id = (select t3.id from t3 where t3.other_column = ''));

执行结果如下图所示:
index-optimization-02

③ id相同不同,同时存在。

id如果相同,可以认为是一组,从上往下顺序执行,在所有组中,id值越大,优先级越高,越先执行。

-- id相同又不同,同时存在
explain select t2.* from (select t3.id from t3 where t3.other_column = '') s1,t2 where s1.id = t2.id;

index-optimization-03

select_type

查询类型select_type主要用于区别普通查询、联合查询、子查询等复杂查询,分类和说明如下所示:

id 类型 说明
1 SIMPLE 查询中不包含子查询或者UNION
2 PRIMARY 查询中若包含任何复杂的子部分,最外层查询则被标记为PRIMARY
3 SUBQUERY 在SELECT或WHERE列表中包含了子查询,该子查询被标记为SUBQUERY
4 DERIVED 在FROM列表中包含的子查询被标记为:DERIVED(衍生)
5 UNION 若第二个SELECT出现在UNION之后,则被标记为UNION;若UNION包含在 FROM子句的子查询中,外层SELECT将被标记为:DERIVED
6 UNION RESULT 从UNION表获取结果的SELECT被标记为:UNION RESULT

table

table主要用于显示这一行的数据是关于哪张表,这里不做过多说明。

type

type显示查询使用了何种类型,总共七种类型,从最好到最差,具体类型及说明如下所示:

类型 说明
system 表只有一行记录(等于MySQL自带的系统表),这是const类型的特例,平时不会出现,可以忽略
const 表示通过索引一次就找到了,const用于primary key或者unique索引
eq_ref 唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配,常见于主键或唯一索引
ref 非唯一性索引扫描,返回匹配某个单独值得所有行
range 只检索给定范围的行,使用一个索引来选择行,属于范围扫描索引,一般出现在where语句中使用between,<,>,in等查询
index Full Index Scan,只遍历索引树
all Full Table Scan,将遍历全表以找到匹配的行

从最好到最差依次是system>const>eq_ref>ref>range>index>all

一般来说,要保证查询至少达到range级别,最好能达到ref

possible_keys

指出MySQL能使用哪个索引在表中找到行,查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询使用。

key

显示MySQL在查询中实际使用的索引,若没有使用索引,显示为NULL
index-optimization-04

查询中若使用了覆盖索引,则该索引和查询的SELECT字段重合。(覆盖索引指的是创建的索引字段和查询的字段一致)

key_len

表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下,长度越短越好。

key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的。

index-optimization-05

ref

该字段显示索引的哪一列被使用了,有可能为一个常量。简单来讲,就是指哪些列或常量被用于查找索引列上的值。

index-optimization-06

上图所示,根据tablekey字段可知,t1表的idx_col1_col2被充分使用;通过ref可知,t1表的col1匹配t2表的col1t1表的col2匹配一个常量,即const等于ac

rows

根据表统计信息及索引选用情况,大致估算出最终找到所需记录需要读取的行数。

index-optimization-07

通过以上示例,未建立复合索引idx_col1_col2前,MySQL优化器查询的行数为640+1=641条数据,建立复合索引后,可将优化器的查询行数降低至195+4=199条。

Extra

包含不适合在其他列中显示但十分重要的额外信息。主要包含以下内容:

类型 说明
Using filesort 文件排序
Using temporary 临时表排序
Using index 覆盖索引
Using where 使用条件过滤
Using join buffer 使用连接缓存
impossible where where子句值为false

① Using filesort

MySQL会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”。

index-optimization-08

由上图可知,虽然两次查询的结果是相同的,但实现方式却是有很大的区别。两次的区别在于第一次使用了Using filesort,第二次的效率较高。

② Using temporary

MySQL在对查询结果排序时,使用临时表保存中间结果,常见于order by 排序和group by分组查询。

index-optimization-09

使用临时表后的执行速度比文件内排序更慢。通过上图第一次中可见Using temporaryUsing filesort,原因是因为所创建的索引为col1col2group by时却直接用到了col2,没有按照索引的顺序进行排序,优化为第二次的语句,可见效率得到提高。

在使用group by时,应该考虑到所创建的索引尽量与group by后面的顺序及个数一致。这样,执行SQL语句的效率才不会被拖慢。

③ Using index

Using index表示相应的select操作中使用了覆盖索引(Convering Index),避免了表的数据行,效率比较好。
如果同时出现了Using where,表明索引被用来执行索引键值的查找。
如果没有同时出现Using where,表明索引被用来读取数据而非执行查找动作。

index-optimization-10

覆盖索引的两种理解方式:

方式一:MySQL可以利用索引返回select列表中的字段,而不必根据索引再次读取数据文件,换句话说,查询的列要被所建的索引覆盖。
包含所有满足查询需要的数据的索引称为覆盖索引(Covering Index)。

方式二:索引是高效找到行的一个方法,但是一般数据库也能使用索引找到一个列的数据,因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据,既然能通过读取索引就可以得到想要的数据,那就不需要再读取行。一个索引包含了(或覆盖了)满足查询结果的数据就叫做覆盖索引。

如果要使用覆盖索引,一定要注意select列表中只取出需要的列,不可select *,因为如果将所有字段一起做索引会导致索引文件过大,查询性能下降。
④ Using where

表示MySQL服务器在存储引擎搜到记录后进行“后过滤”(Post-filter),如果查询未能使用索引,Using where的作用只是提醒我们MySQL将用where子句来过滤结果集。

index-optimization-11

⑤ Using join buffer
使用了连接缓存。
⑥ impossible where
where子句的值总是false,不能获取任何元素,如一个人的性别既是男又是女。

案例分析:

针对以下案例,请分析出MySQL的执行顺序:

index-optimization-12

分析:
执行顺序1:id为4的行,select_typeunion,说明第四个selectunion里的第二个select,最先执行【select name,id from t2】。
执行顺序2:id为3的行,是整个查询中第三个select的一部分。因为查询包含在from中,所以为derived。【select id,name from t1 where other_column = ''】。
执行顺序3:id为2的行,select列表中的子查询select_typesubquery,为整个查询中的第二个select。【select id from t3】
执行顺序4:id为1的行,select_typeprimary,表示该查询为外层查询,table<derived3>,表示查询结果来自一个衍生表,其中derived3中的3代表该查询衍生自id为3的select查询。【select d1.name......】
执行顺序5:id为NULL,代表从union的临时表中读取行的阶段,table列中的<union1,4>表示用第1个和第4个select的结果进行union操作。【两个结果union操作】

相关推荐

微信扫一扫,分享到朋友圈

【MySQL高级】索引优化分析(二)
返回顶部

显示

忘记密码?

显示

显示

获取验证码

Close