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

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

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

mysql慢查询及慢查询日志分析工具

一、通过查询

1 用法： sql

2 作用：用于分析sql语句

(1)、id:执行的一个编号(没有实际意义）

(2)、table:查询的表名

(3)、:查询类型，是单表查询、联合查询还是子查询等 可能会出现以下值：

例子使用的表结构：

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=InnoDB AUTO_INCREMENT=300004 DEFAULT CHARSET=utf8 COMMENT='用户表';

例1：简单查询：

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

例2：type为const:

例3：type为ref:

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

(5). :能在该表中使用哪些索引有助于查询

(6). key:实际使用的索引

(7). ：索引的长度使用explain和show profile来分析SQL语句实现优化SQL语句，在不损失精确性的情况 下,长度越短越好

(8). ref：索引的哪一列被使用了

(9). rows:返回的结果的行数

(10). Extra:其他说明

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

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

1、执行步骤

首先得开启

set =1

其次是执行sql

最后通过show 查看分析结果

可以看出上图中的 data是占用时间最长的部分使用explain和show profile来分析SQL语句实现优化SQL语句，可能是原因的是查询的数据量过大

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

用法：

show profile <type> [for QUERY <NUM>] ;

参数说明：

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

NUM表示显示哪条SQL(这个数字可以通过SHOW 的中取得，for Query 不填写则表示最近执行的SQL)通过show 查找出最消耗性能的阶段，针对其进行优 化

三、SQL语句优化

1、count语句优化

(1)count(*)与count(col)的情况下：

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

count(*)

count()：是一个普通字段，没有任何索引

count():是一个普通索引

结论： 在没有where条件的情况下，速度由快到慢的是：(可以多次运行mysql sql语句优化工具，查看结果) count(*) > count(索引字段) > count(未索引字段)

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

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

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

count语句优化结论：在有where条件的情况下mysql sql语句优化工具，尽量使用count(where条件中的查询条件字段)，其次是使用count(),避免使用count(col)；在没有where条件的情况下，尽量使用count()

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

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

添加索引后：

alter table population add index idx_population(population);

发现：添加索引后的，如果带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=InnoDB AUTO_INCREMENT=300004 DEFAULT 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=InnoDB DEFAULT CHARSET=utf8;

原查询语句：

select*from mall_user AS user where uid in(select distinct uid from mall_user_group_relation);

修改为关联查询的语句：

select distinct(user.uid),account,email,password,mobile,nickname,status,create_time 
from mall_user AS user 
RIGHT JOIN mall_user_group_relation AS relation ON user.uid=relation.uid

4、尽量避免使用 *

只获取必要的字段mysql sql语句优化工具，不需要的字段可以不用获取。 * 查询一些不必要的数据，会造成系统资源的极大浪费，特别是查询数据量较多的数据时。

5、总结：

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

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

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