mysql show profilke_使用explain和show profile来分析SQL语句实现优化SQL语句

  • Post author:
  • Post category:mysql


SQL语句优化是建立在慢查询分析的基础上,通过慢查询定位有问题的SQL语句,关于慢查询的介绍及其分析工具,可以参考[mysql慢查询及慢查询日志分析工具]

一、通过explain查询

1 用法:explain sql

2 作用:用于分析sql语句

b1f2ae8f3a38e421b7e4fcb4767f77ab.png
(1)、id:执行explain的一个编号(没有实际意义)(2)、table:查询的表名(3)、select_type:查询类型,是单表查询、联合查询还是子查询等 可能会出现以下值:查询类型

说明

SIMPLE

简单的 select 查询,不使用 union 及子查询

PRIMARY

最外层的 select 查询(使用到主键作为查询条件)

UNION UNION

中的第二个或随后的 select 查询,不 依赖于外部查询的结果集

DEPENDENT UNION

UNION 中的第二个或随后的 select 查询,依 赖于外部查询的结果集

SUBQUERY

子查询中的第一个 select 查询,不依赖于外 部查询的结果集

DEPENDENT SUBQUERY

子查询中的第一个 select 查询,依赖于外部 查询的结果集

DERIVED

用于 from 子句里有子查询的情况。 MySQL 会 递归执行这些子查询, 把结果放在临时表里。

UNCACHEABLE SUBQUERY

结果集不能被缓存的子查询,必须重新为外 层查询的每一行进行评估。

UNCACHEABLE UNION UNION

中的第二个或随后的 select 查询,属 于不可缓存的子查询

例子使用的表结构:

CREATE TABLE`mall_user`(

`uid`int(12)NOT NULL AUTO_INCREMENT COMMENT’用户ID’,

`account`varchar(20)NOT NULL COMMENT’账号’,

`email`varchar(70)NOT NULL DEFAULT”COMMENT’邮箱’,

`password`char(32)NOT NULL DEFAULT”COMMENT’密码’,

`mobile`char(11)NOT NULL DEFAULT”COMMENT’手机号’,

`nickname`varchar(20)NOT NULL DEFAULT”COMMENT’昵称’,

`status`enum(‘UNCHECKED’,’DISABLED’,’INACTIVED’,’ACTIVED’)NOT NULL DEFAULT’UNCHECKED’COMMENT’状态’,

`create_time`timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT’加入时间’,

PRIMARY KEY(`uid`),

UNIQUE KEY`idx_account`(`account`),

UNIQUE KEY`idx_email`(`email`),

UNIQUE KEY`idx_mobile`(`mobile`),

UNIQUE KEY`idx_nickname`(`nickname`),

KEY`idx_status`(`status`),

KEY`idx_ctime`(`create_time`)

)ENGINE=InnoDBAUTO_INCREMENT=300004DEFAULT CHARSET=utf8 COMMENT=’用户表’;

例1:simple简单查询:

8ae412da2be0bb42e94c7e14808bc066.png
(4). type:连接使用的类型(重要项) 显示连接使用的类型,按最 优到最差的类型排序type

说明

system

表仅有一行(=系统表)。这是 const 连接类型的一个特例。

const

const 用于用常数值比较 PRIMARY KEY 时。当 查询的表仅有一行时,使用 System。

eq_ref

const 用于用常数值比较 PRIMARY KEY 时。当 查询的表仅有一行时,使用 System。

ref

连接不能基于关键字选择单个行,可能查找 到多个符合条件的行。 叫做 ref 是因为索引要 跟某个参考值相比较。这个参考值或者是一 个常数,或者是来自一个表里的多表查询的 结果值

ref_or_null

如同 ref, 但是 MySQL 必须在初次查找的结果 里找出 null 条目,然后进行二次查找。

index_merge

说明索引合并优化被使用了。

unique_subquery

在某些 IN 查询中使用此种类型,而不是常规的 ref:value IN (SELECT primary_key FROM single_table WHERE some_expr)

index_subquery

在 某 些 IN 查 询 中 使 用 此 种 类 型 , 与 unique_subquery 类似,但是查询的是非唯一 性索引: value IN (SELECT key_column FROM single_table WHERE some_expr)

range

只检索给定范围的行,使用一个索引来选择 行。key 列显示使用了哪个索引。当使用=、 <>、>、>=、、BETWEEN 或者 IN 操作符,用常量比较关键字列时,可 以使用 range。

index

全表扫描,只是扫描表的时候按照索引次序 进行而不是行。主要优点就是避免了排序, 但是开销仍然非常大。

all

最坏的情况,从头到尾全表扫描。

例2:type为const:

eed93e361e921dbbd9df0ad98b602579.png

例3:type为ref:

7b6291df75e251f497d553deb178ec52.png

例3:type为all:(这种是要避免和优化的)

286c9a5ccf3d5b98fc7a7e657bd66ce6.png
(5). prossible_keys:能在该表中使用哪些索引有助于查询(6). key:实际使用的索引(7). key_len:索引的长度,在不损失精确性的情况 下,长度越短越好(8). ref:索引的哪一列被使用了(9). rows:返回的结果的行数(10). Extra:其他说明

以下两种情况说明:MYSQL 根本不能使用索引,效率会受到重大影响。应尽可能对此进行优化

Extra

说明

Using filesort

表示 MySQL 会对结果使用一个外部索引排序,而不是从表里按索引次序读到相关内容。可能在内存或者磁盘上进行排序。MySQL 中无法利用索引完成的排序操作称为“文件排序”

Using temporary

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

二、通过show profile查看SQL执行过程

1、执行步骤首先得开启profilingsetprofiling=1

其次是执行sql最后通过show profile查看分析结果

4f98e3853f494a60f26d98084489336f.png

可以看出上图中的Sending data是占用时间最长的部分,可能是原因的是查询的数据量过大

2、查看SQL执行各阶段的资源消耗情况:

用法:show profile [for QUERY ] ;

参数说明:

表示显示的资源类型,可以使用以下的值:

type选项值

说明

ALL

显示所有性能信息

BLOCK IO

显示IO操作次数

CONTEXT SWITCHES

上下文切换次数

CPU

用户和系统占用的CPU时间

IPC

显示发送和接收的消息数量

MEMORY

占用的内存大小

PAGE FAULTS

显示页错误数量

SOURCE

显示源码中的函数名称与位置

SWAPS

显示swap交换内存大小

表示显示哪条SQL(这个数字可以通过SHOW profiles的中取得,for Query 不填写则表示最近执行的SQL)

通过show profile查找出最消耗性能的阶段,针对其进行优 化

三、SQL语句优化

1、count语句优化(1)count(*)与count(col)的情况下:

第一种情况:在没有任何查询条件的情况下:

count(*)

b81c8d717d5073dd21f6d04b05634bbb.png
count(region):region是一个普通字段,没有任何索引

6904a20744071fb7406267e0ad478c8d.png
count(country):country是一个普通索引

75f8e8fa5b005ff0ac18eb2c0731b727.png
得出的结论:在没有where条件的情况下,速度由快到慢的是:(可以多次运行,查看结果) count(*) > count(索引字段) > count(未索引字段)

第二种情况:在有查询条件的情况下:

f1d0098711bb9c0076ee13e4da0c8715.png

从结果可以得出以下结论:

在有where条件的情况下,速度由快到慢的是: count(where条件中包含的字段) > count(*) > count(未索引字段)

count语句优化结论:在有where条件的情况下,尽量使用count(where条件中的查询条件字段),其次是使用count(),避免使用count(col);在没有where条件的情况下,尽量使用count()

2、max语句优化 在max字段上添加索引,可以提高其查询效率

实验: 在没有添加的索引的字段使用max

20674198a9cce9ee2adf1b17809d3f1d.png

添加索引后:

alter table population add index idx_population(population);

45faafefe16bb4253aa64b56193ebeb9.png

发现:添加索引后的,如果带where条件,而且where条件与max的字段不一致时,所花时间竟然比不带where的多了几个数量级的,而且比之间不加索引花的时间更多。这个得考虑一下是什么原因。

3、子查询优化 根据实际情况而定:如果查询的条件是以左表的主键为查询条件的,使用IN子查询更高效 – 如果查询的条件不是以左表的主键为查询条件的,使用联接查询更高效

以一个用户表和用户与组关系表为例:

用户表结构如下:

CREATE TABLE`mall_user`(

`uid`int(12)NOT NULL AUTO_INCREMENT COMMENT’用户ID’,

`account`varchar(20)NOT NULL COMMENT’账号’,

`email`varchar(70)NOT NULL DEFAULT”COMMENT’邮箱’,

`password`char(32)NOT NULL DEFAULT”COMMENT’密码’,

`mobile`char(11)NOT NULL DEFAULT”COMMENT’手机号’,

`nickname`varchar(20)NOT NULL DEFAULT”COMMENT’昵称’,

`status`enum(‘UNCHECKED’,’DISABLED’,’INACTIVED’,’ACTIVED’)NOT NULL DEFAULT’UNCHECKED’COMMENT’状态’,

`create_time`timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT’加入时间’,

PRIMARY KEY(`uid`),

UNIQUE KEY`idx_account`(`account`),

UNIQUE KEY`idx_email`(`email`),

UNIQUE KEY`idx_mobile`(`mobile`),

UNIQUE KEY`idx_nickname`(`nickname`),

KEY`idx_status`(`status`),

KEY`idx_ctime`(`create_time`)

)ENGINE=InnoDBAUTO_INCREMENT=300004DEFAULT CHARSET=utf8 COMMENT=’用户表’;

用户与组关系表结构:

CREATE TABLE`mall_user_group_relation`(

`group_id`int(12)NOT NULL COMMENT’组ID’,

`uid`int(12)NOT NULL COMMENT’用户ID’,

`remark`varchar(50)NOT NULL DEFAULT”COMMENT’备注’,

PRIMARY KEY(`group_id`,`uid`)

)ENGINE=InnoDBDEFAULT CHARSET=utf8;

原查询语句:

select*frommall_user AS userwhereuidin(selectdistinct uidfrommall_user_group_relation);

47957876ee75e0481601326efaa84ccb.png

修改为关联查询的语句:

selectdistinct(user.uid),account,email,password,mobile,nickname,status,create_timefrommall_user AS user RIGHT JOIN mall_user_group_relation AS relation ON user.uid=relation.uid

80e6f19f4c80a637a1e1db0040dd68a7.png

4、尽量避免使用select *

只获取必要的字段,不需要的字段可以不用获取。select * 查询一些不必要的数据,会造成系统资源的极大浪费,特别是查询数据量较多的数据时。

3e6050b2a8b220ef4c182bff04963fc9.png

5、总结:在where条件、order by、group by中尽量使用已经索引的字段

查询必要的字段和限定的记录数(limit)

不要完全相信已有经验,需要自己根据explain和show profile来检测SQL的执行效率,进行针对性的优化



版权声明:本文为weixin_30604459原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。