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=InnoDB AUTO_INCREMENT=300004 DEFAULT 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 列显示使用了哪个索引。当使用=、 <>、>、>=、<、<=、IS NULL、<=>、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、执行步骤
首先得开启profiling
set profiling=1
其次是执行sql
最后通过show profile查看分析结果
可以看出上图中的Sending data是占用时间最长的部分，可能是原因的是查询的数据量过大
2、查看SQL执行各阶段的资源消耗情况：
用法：show profile <type> [for QUERY <NUM>] ;
参数说明：
<type>表示显示的资源类型,可以使用以下的值：
| type选项值 | 说明 |
|---|---|
| ALL | 显示所有性能信息 |
| BLOCK IO | 显示IO操作次数 |
| CONTEXT SWITCHES | 上下文切换次数 |
| CPU | 用户和系统占用的CPU时间 |
| IPC | 显示发送和接收的消息数量 |
| MEMORY | 占用的内存大小 |
| PAGE FAULTS | 显示页错误数量 |
| SOURCE | 显示源码中的函数名称与位置 |
| SWAPS | 显示swap交换内存大小 |
<NUM>表示显示哪条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=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的执行效率，进行针对性的优化