SQL语句优化是建立在慢查询分析的基础上,通过慢查询定位有问题的SQL语句,关于慢查询的介绍及其分析工具,可以参考[mysql慢查询及慢查询日志分析工具]
一、通过explain查询
1 用法:explain sql
2 作用:用于分析sql语句
(1)、id:执行explain的一个编号(没有实际意义)
(2)、table:查询的表名
(3)、select_type:查询类型,是单表查询、联合查询还是子查询等 可能会出现以下值:
例子使用的表结构:
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:simple简单查询:
(4). type:连接使用的类型(重要项) 显示连接使用的类型,按最 优到最差的类型排序
例2:type为const:
例3:type为ref:
例3:type为all:(这种是要避免和优化的)
(5). prossible_keys:能在该表中使用哪些索引有助于查询
(6). key:实际使用的索引
(7). key_len:索引的长度,在不损失精确性的情况 下,长度越短越好
(8). ref:索引的哪一列被使用了
(9). rows:返回的结果的行数
(10). Extra:其他说明
以下两种情况说明:MYSQL 根本不能使用索引,效率会受到重大影响。应尽可能对此进行优化
二、通过show profile查看SQL执行过程
1、执行步骤
首先得开启profiling
set profiling=1
其次是执行sql
最后通过show profile查看分析结果
可以看出上图中的Sending data是占用时间最长的部分,可能是原因的是查询的数据量过大
2、查看SQL执行各阶段的资源消耗情况:
用法:
show profile
参数说明:
1、count语句优化
(1)count(*)与count(col)的情况下:
第一种情况:在没有任何查询条件的情况下:
count(*)
count(region):region是一个普通字段,没有任何索引
count(country):country是一个普通索引
得出的结论:在没有where条件的情况下,速度由快到慢的是:(可以多次运行,查看结果) count(*) > count(索引字段) > count(未索引字段)第二种情况:在有查询条件的情况下:
从结果可以得出以下结论:
在有where条件的情况下,速度由快到慢的是: count(where条件中包含的字段) > count(*) > count(未索引字段)count语句优化结论:在有where条件的情况下,尽量使用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、尽量避免使用select *
只获取必要的字段,不需要的字段可以不用获取。select * 查询一些不必要的数据,会造成系统资源的极大浪费,特别是查询数据量较多的数据时。
5、总结:
在where条件、order by、group by中尽量使用已经索引的字段查询必要的字段和限定的记录数(limit)不要完全相信已有经验,需要自己根据explain和show profile来检测SQL的执行效率,进行针对性的优化