SQL语句优化是建立在慢查询分析的基础上,通过慢查询定位有问题的SQL语句,关于慢查询的介绍及其分析工具,可以参考[mysql慢查询及慢查询日志分析工具]
一、通过explain查询
1 用法:explain sql
2 作用:用于分析sql语句
(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简单查询:
(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:
例3:type为ref:
例3:type为all:(这种是要避免和优化的)
(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查看分析结果
可以看出上图中的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(*)
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=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);
修改为关联查询的语句:
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
4、尽量避免使用select *
只获取必要的字段,不需要的字段可以不用获取。select * 查询一些不必要的数据,会造成系统资源的极大浪费,特别是查询数据量较多的数据时。
5、总结:在where条件、order by、group by中尽量使用已经索引的字段
查询必要的字段和限定的记录数(limit)
不要完全相信已有经验,需要自己根据explain和show profile来检测SQL的执行效率,进行针对性的优化