MySQL查询优化分析

我不是欧拉_

已于 2022-11-21 15:29:27 修改

阅读量577

点赞数

文章标签： mysql

于 2022-08-17 16:23:49 首次发布

本文链接：https://blog.csdn.net/m0_38033199/article/details/126348012

版权

SQL执行慢的原因

1. 从sql来说

没有创建索引
索引失效
关联查询太多的join

2. 从服务器来说

服务器磁盘空间不足
服务器调优配置参数设置不合理

索引失效场景

1. like以%开头，索引失效。当like前缀没有%，后缀有%时，索引有效。

select * from table_name where a like 'As%'; // 前缀都是排好序的，走索引查询
select * from table_name where  a like '%As'; // 全表查询
select * from table_name where  a like '%As%'; // 全表查询

2. 语句前后没有同时使用索引。当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时（一个索引满足最左前缀匹配原则，一个索引满足主键索引或唯一键索引），才会生效。

select * from table_name where 索引字段 = '' or 非索引字段 = ''; // 索引失效

3. 组合索引，不是使用第一列索引，索引失效（即不满足最左前缀法则）。

最左前缀法则：最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。

假如建立联合索引（a,b,c）

select * from table where

1：a = '' 可以命中索引，b = '' 不可以命中索引，c = '' 不可以命中索引。

2：a = '' and b = '' 可以命中，a = '' and c = '' 可以命中索引。

3：b = '' and a = '' 可以命中索引，b = '' and c = '' 不可以命中索引。

4：c = '' and a = '' 可以命中索引，c = '' and b = '' 不可命中索引。

1：select * from table group by a, b, c 可以命中索引

2：select a, b, c from table group by a, b, c 可以命中索引

3：select * from table group by a, c 不可以命中索引，会产生Using temporary; Using filesort

4：select a, b, c from table group by a, c 可以命中索引，但是会产生Using temporary; Using filesort

5：select * from table order by a, b, c 不可以命中索引，会产生Using filesort

6：select a, b, c from table order by a, b, c 可以命中索引

7：select a, b, c from table order by a, c 可以命中索引，但是会产生Using filesort

原理：索引的底层是一颗B+树，那么联合索引当然还是一颗B+树，只不过联合索引的健值数量不是一个，而是多个。构建一颗B+树只能根据一个值来构建，因此数据库依据联合索引最左的字段来构建B+树。

例子：假如创建一个（a,b)的联合索引，那么它的索引树是这样的

可以看到a的值是有顺序的，1，1，2，2，3，3，而b的值是没有顺序的1，2，1，4，1，2。所以b = 2这种查询条件没有办法利用索引，因为联合索引首先是按a排序的，b是无序的。

4. 数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描。

5. 在索引列上使用 IS NULL 或 IS NOT NULL操作。索引是不索引空值的，所以这样的操作不能使用索引，可以用其他的办法处理，例如：数字类型，判断大于0，字符串类型设置一个默认值，判断是否等于默认值即可。

6. 在索引字段上使用not，<>，!=。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。优化方法： key<>0 改为 key>0 or key<0。

7. 对索引字段进行计算操作或使用使用函数会导致索引失效。

8. 当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效。

索引优化

1. 避免使用select *，如果需要select * ，where 后面字段满足最左前缀匹配原则。

如果不满足最左前缀匹配原则，select 字段最好和索引字段一致。

-- name，gender，birthday 是 a 表的组合索引，id 是 a 表的主键

-- 下面的写法是可以接受的

select * from a where a.name = '' 

select * from a where a.gender = '' and a.name = ''

select * from a where a.name= '' or a.id = ''

select name, gender, birthday from a 

select name, gender, birthday from a where a.gender = ''

2. 在无法避免使用 like 以%开头的情况下，可以考虑使用覆盖索引，即查询的字段和 like 条件字段被索引包含。

3. 用union all替代union，union all不去重，但union是去重的：需要增加排序和比较的时间花费。

4. 小表驱动大表
核心思想：即用少数据量作为条件去查询表
==>如果sql语句包含了in关键词，那么它会优先执行in里面的子查询，然后才执行外面的查询；
==>如果sql语句包含了exist关键词，那么它优先执行exist左边的查询，然后才执行外面的查询；
总结：假如查询语句左边是大表，右边是小表，明显使用in关键词的查询速度要更快，因为先进行的是in里面的子查询，根据这些条件查询速度会更快；而使用exist先查大表，把它作为
条件明显数据更多，再去匹配右边的，查询速度相对要慢。所以，如果左边是大表右边是小表则优先使用in关键词，反之则优先使用exist关键词。

联表优化

Nested Loop Join （NLJ）算法:

指嵌套循环算法，my.oschina.net 上面有一段代码对NLJ做出了说明：

for each row in t1 matching range {                //外层循环
  for each row in t2 matching reference key {      //次内层循环
    for each row in t3 {                           //最内层循环
      if row satisfies join conditions,            //进行条件匹配，若满足，发给client
      send to client
    }
  }

// 假如t1,t2,t3的行数分别为x,y,z，那么扫描行数n=xyz。

将驱动表/外部表的结果集作为循环基础数据，然后循环从该结果集每次一条获取数据作为下一个表的过滤条件查询数据，然后合并结果。如果有多表join，则将前面的表的结果集作为循环数据，取到每行再到联接的下一个表中循环匹配，获取结果集返回给客户端。（此处仅对两层循环分析，多层循环可以将最内层循环看作一层，将其余的看作一层进行分析）

循环过程：内层表看作被驱动表，外层表看作驱动表。每次join时，从驱动表中先拿出一条数据和被驱动表进行条件匹配，若匹配成功，则将数据连接后放入结果集。接着，外层的驱动表扫描获取第二条记录，并和被驱动表进行条件匹配，将成功的记录连接后放入结果集，剩余数据以此类推。最后，将结果集发给请求的客户端。

Block Nested Loop Join （BNLJ）算法:

BNLJ，块嵌套循环。BNLJ是优化版的NLJ，BNLJ区别于NLJ的地方是它加入了缓冲区join buffer，它的作用是外层驱动表可以先将一部分数据事先存到join buffer中，然后再和内层的被驱动表进行条件匹配，匹配成功的记录将会连接后存入结果集，等待全部循环结束后，将结果集发给client即完成一次join。

以下是my.oschina.net 上一段对BNLJ的说明：

for each row in t1 matching range {
  for each row in t2 matching reference key {
    store used columns from t1, t2 in join buffer      //将t1，t2的记录放入join buffer
    if buffer is full {                                //如果buffer不为空
      for each row in t3 {                             //t3和t1，t2的combination 进行匹配
        for each t1, t2 combination in join buffer {
          if row satisfies join conditions,
          send to client
        }
      }
      empty buffer                                     //将buffer置空
    }
  }
}
 
if buffer is not empty {
  for each row in t3 {
    for each t1, t2 combination in join buffer {
      if row satisfies join conditions,
      send to client
    }
  }
}


// 假如t1,t2,t3的行数分别为x,y,z，joinbuffer一次可容纳j条记录，那么扫描行数n=xyz/j

BNLJ相对于NLJ的优点在于，驱动层可以先将部分数据加载进buffer，这种方法的直接影响就是将大大减少内层循环的次数，提高join的效率。

所以针对join的优化，一般核心的在于它的内外循环。

1. 用小结果集驱动大的结果集

这里驱动表即为外层循环表，使用小表作为驱动表，可以减少被驱动表的检索次数。当然，此优化的前提条件是通过Join条件对各个表的每次访问的资源消耗差别不是太大。如果访问存在较大的差别的时候（一般都是因为索引的区别），我们就不能简单的通过结果集的大小来判断需要Join语句的驱动顺序，而是要通过比较循环次数和每次循环所需要的消耗的乘积的大小来得到如何驱动更优化。

2. 优先优化内层循环

内层循环是循环中执行次数最多的，每次循环节约很小的资源，在整个循环中就能节约很大的资源。

3. 保证内层循环中的筛选条件使用到内表的索引

正如第二点所说，利用索引减少每次内层循环消耗的资源，这也正是优化内层循环的实际优化方法

4. 当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下，不要太吝惜JoinBuffer的设置

当在某些特殊的环境中，我们的Join必须是All，Index，range或者是index_merge类型的时候，JoinBuffer就会派上用场了。在这种情况下，JoinBuffer的大小将对整个Join语句的消耗起到非常关键的作用。设置参数 join_buffer_size ，默认值是 256k。

SQL性能分析工具

1. Explain

MySQL查看sql执行计划的关键字，使用explain关键字可以模拟优化器执行sql查询语句，从而得知MySQL 是如何处理sql语句。

explain <sql语句>

执行结果

结果说明

1. id：select 查询的序列号，包含一组可以重复的数字，表示查询中执行sql语句的顺序。分两种情况：id相同，执行顺序从上往下；id不同，id值越大，优先级越高，越先执行。

select查询的数字序列号，表示查询中执行select子句、或多表联合查询时操作表的顺序；
· （1）id相同，执行顺序由上至下

从t1、t2、t3三张表中查询t2表的全部，查询条件是：t1.id=t2.id 、t1.id=t3.id、t1的其他字段是空；
优化器对于同一个where内部的条件，默认是从右往左读取的；
所以三张表的执行顺序是：t1 -> t3 -> t2；

（2）id不同，id越大执行优先级越高

如果是子查询，id的序列号会递增，越是在里面的子查询越优先执行，类似于递归的思想；( )里的语句具有优先级

从t2表中查询t2表的全部，查询条件是id，查询条件是三层嵌套：
从表t1查询id——id是从表t3中查询到的t3表的id——t3表的其他字段为空；
用递归的思想，很容易判断，表的执行顺序：t3 -> t1 -> t2

（3）id同时存在相同和不同的情况，id不同的id大的优先执行，id相同的从上到下执行

这里需要引入一个概念，衍生虚表查询(或者叫临时表)（Derived）：
从原表中截取部分信息，组成的结果集可以构成一张新表，这张表并不实际存在，作为一张虚表被调用；这种查询就叫衍生虚表查询；

（从表t3中查询t3表的id，查询条件是其他字段为空）将括号中t3表的查询条件，的结果集作为一张虚表s1，
从虚表s1、表t2中，查询表t2的全部，查询条件是s1.id=t2.id；
所以，查询的顺序是：t3 -> derived2 -> t2
derived2指，由id=2时，即表t3的结果集衍生构成的虚表s1；

2. select_type：select 查询的类型，主要是用于区别普通查询，联合查询，嵌套的复杂查询，主要包含以下几种类型

SIMPLE：简单查询，查询中不包含子查询或者UNION
PRIMARY：主查询，若查询中包含复杂的子查询部分，则最外层查询被标记为PRIMARY
SUBQUERY：子查询，在SELECT或者WHERE列表中包含了的子查询
DERIVED：衍生查询，在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表里。
UNION：联合查询，有UNION的第二个和以后的查询
UNION RESULT：从UNION表获取结果的SELECT

3. table：显示这一行的数据是关于哪张表的，对应衍生查询则表名为derived+id

4. type：对表访问方式，主要包含以下几种

system：表中只有一行数据，这是const类型的特例。
const：表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。如将主键至于where列表中，MySQL就能将该查询转换为一个常量。
eq_ref：唯一性索引，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描。简单来说，就是多表连接中使用primary key或者 unique key作为关联条件。
ref：非唯一索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而它可能会找到多个符合条件的行。
range：只检索给定范围的行，使用一个索引来选择行。一般出现在where语句中出现了between、<、>、in等的查询，这种范围扫描索引扫描比全表扫描要好，因为他只需要开始索引的某一点，而结束语另一点，不用扫描全部索引。
index：Full Index Scan。index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）
all：Full Table Scan。将遍历全表以找到匹配的行

性能从最优到最差的排序：system > const > eq_ref > ref > range > index > all，一般来说，尽量优化查询达到range级别，最好达到ref。

5. possible_keys：显示查询语句可能用到的索引(一个或多个或为null)，不一定被查询实际使用。仅供参考使用

6. key：显示查询语句实际使用的索引。若为null，则表示没有使用索引。

7. key_len：显示索引中使用的字节数，可通过key_len计算查询中使用的索引长度。在不损失精确性的情况下索引长度越短越好。key_len 显示的值为索引字段的最可能长度，并非实际使用长度，即key_len是根据表定义计算而得，并不是通过表内检索出的。可以通过key_len看出索引字段的个数，74指1个，78指2个，140指3个；

8. ref：显示使用索引的是哪个字段，可以是一个const常量；与type中的ref类型对应。

从表t1和表t2中查询所有，查询条件：t1.col1=t2.col1、t1.col2=‘ac’;
同一个where列表中，优化器从右到左执行，索引有两个字段；
优先执行字段值’ac’，是const常量；
然后执行数据库shared中表t2中的字段col1，即shared.t2.col1；

9. rows：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数

10. filtered：表示通过查询条件获取的最终记录行数占通过type字段指明的搜索方式搜索出来的记录行数的百分比，filtered大小并不能反映出性能问题。（如：filtered = 1，搜索出来的记录 / ROWS = 1 %）

11. Extra：包含不适合在其他列中显示但十分重要的额外信息，主要包括以下几种：

Using filesort：说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序” 。出现这个就要立刻优化sql。
Using temporary：使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。出现这个更要立刻优化sql
Using index：表示相应的select 操作中使用了覆盖索引（Covering index），避免访问了表的数据行，效果不错！如果同时出现Using where，表明索引被用来执行索引键值的查找。如果没有同时出现Using where，表示索引用来读取数据而非执行查找动作。覆盖索引（Covering Index）：也叫索引覆盖，就是select 的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select 列表中的字段，而不必根据索引再次读取数据文件。
Using where：表明使用了where 过滤
Using join buffer：表明使用了连接缓存
Impossible where：where 语句的值总是false，不可用，不能用来获取任何元素
select tables optimized away：在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
distinct：优化distinct，在找到第一匹配的元组后即停止找同样值的工作

补充说明

EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
EXPLAIN不考虑各种Cache
EXPLAIN不能显示MySQL在执行查询时所作的优化工作
部分统计信息是估算的，并非精确值
EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划

Sql优化案例

1. like以%开头，索引失效
select * from a where a.name like '%xx%';

优化：

select * from a where a.name like 'xx%';

-- select * from 表名 where LOCATE('字符', 字段),如果包含，返回>0的数（字符所在的第一个索引），否则返回0
select * from a where locate('xx', a.name) > 0 ; 
2. 组合索引不满足最左前缀法则，索引失效
-- 假如建立联合索引（a,b,c）

seclect * from table where 

1：a = '' 可以命中索引，b = '' 不可以命中索引，c = '' 不可以命中索引。

2：a = '' and b = '' 可以命中，a = '' and c = ''  可以命中索引。

3：b = '' and a = '' 可以命中索引，b = '' and c = ''  不可以命中索引。

4：c = '' and a = '' 可以命中索引，c = '' and  b = '' 不可命中索引。
3. 数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型，使索引无效
-- id 类型为varchar，此时1没加单引号会自动转换为int，索引失效
select * from a where a.id = 1

优化：

select * from a where a.id = '1'
4. 对索引字段进行计算操作或使用使用函数会导致索引失效
select * from a where left(a.birthday, 4) = '2022'

优化：

select * from a where a.birthday like '2022%'
5. explain sql 产生type = all 或者 Extra包含Using filesort、Using temporary

type = all：

type的优先级：system > const > eq_ref > ref > range > index > all

type最好达到ref级别，type = all 一般是没有用索引（或者返回表中30%内的数据会走索引，超过就全表扫描。30%，只是一个大概的范围），建索引即可解决。

Using filesort：文件排序

产生原因：涉及到order by语句且索引使用不当

5.1 原生的数据表，没有新建任何索引，除非是用主键作为排序字段，否则无论你怎么排序都会出现using filesort

5.2 在建立索引的情况下，排序字段不符合最左前缀匹配原则也会出现using filesort

5.3 符合最左前缀匹原则但是排序字段属于的表，中的筛选字段不匹配排序索引字段

5.4 order by 的字段不是来自 JOIN 语句第一个表
-- name, gender, birthday 为 a 表的索引字段
-- name 为 b 表的索引字段
-- 以下情况均会出现using filesort

select name, gender, birthday from a order by gender, birthday

select name, gender, birthday from a order by name, birthday

select * from a order by name, gender, birthday

select a.*, b.name from a left join b on a.name = b.name order by a.name

select a.name, b.name from a left join b on a.name = b.name order by b.name

-- 以下情况不会出现using filesort

select a.name, b.* from a left join b on a.name = b.name order by a.name

select a.gender, b.* from a left join b on a.name = b.name order by a.name, a.gender
Using temporary：使用临时表

产生原因：group by 语句且索引使用不当

5.1 group by 的字段没有索引，产生临时表。

5.2 group by 的字段有索引，但是不满足最左前缀匹配原则，产生临时表。

5.3 group by 的字段有索引，order by 的字段没索引，产生临时表。
5.4 group by 的字段和order by 的字段不一样，即使都有索引也会产生临时表。
5.5 group by或order by 的字段不是来自 JOIN 语句第一个表，会产生临时表。

mysql会自动将数据量较小的表设置为驱动表或者能走索引的表设置为被驱动表（即如果是用inner join，where 等连接表，第一个表有可能被优化成被驱动表或者数据量小的表有可能被优化成第一个表），这时我们的排序字段或者分组字段还是用第一个表的字段话，就会导致group by或order by 的字段不是来自 JOIN 语句第一个表。

解决方法：使用left join，可以阻止小表被优化为驱动表。
5.6 distinct 字段没有索引或者有索引没有满足最左前缀匹配原则，产生临时表。

5.7 符合最左前缀匹原则但是分组字段属于的表，中的筛选字段不匹配分组索引字段
-- name, gender, birthday 为a表索引字段
-- name 为b表的索引字段
-- 以下情况均会出现using temporary

select * from a group by id

select * from a group by gender

select * from a group by name, birthday

select * from a group by name order by id

select * from a group by name order by gender

select * from a left join b on a.id = b.id group by b.name

select a.*, b.name from a left join b on a.id = b.id group by a.name

select distinct(id) from a 

select distinct(gender) from a

-- 这种情况不会出现 using temporary

select a.name, b.* from a left join b on a.id = b.id group by a.name
6. join优化，例子如下：
# a表emp_no是索引，b表(id, emp_no)联合索引

# 1
explain
select
	*
from
	t_certification_exam_result a
left join t_emp_delineation_baseline_info b on
	a.emp_no = b.emp_no

# 2
explain
select
	*
from
	t_emp_delineation_baseline_info b
left join t_certification_exam_result a on
	b.emp_no = a.emp_no 

# 3
explain
select
	*
from
	t_certification_exam_result a
inner join 
t_emp_delineation_baseline_info b on
	a.emp_no = b.emp_no

# 4
explain
select
	*
from
	t_emp_delineation_baseline_info b
inner join
t_certification_exam_result a on
	b.emp_no = a.emp_no

# 5
explain
select
	*
from
	t_certification_exam_result a,
	t_emp_delineation_baseline_info b where 
	a.exam_room_id  = b.uuid 

# 6
explain
select
	*
from
	t_emp_delineation_baseline_info b,
	t_certification_exam_result a where 
	b.uuid = a.exam_room_id 
执行结果分别如下：

从执行计划可以看出：

1，2句sql 使用 left join （right join）固定了驱动表和被驱动表。

1 的驱动表是a走全表扫描，被驱动表是b，虽然b表有联合索引，但是不满足最左前缀匹配原则，所以没有走索引而是全表扫描，type都为all。

2 的驱动表是b走全表扫描，被驱动表是a，a表是单索引，满足走索引条件，所以type为ref。

3，4句sql 使用inner join，mysql会自动将能走索引的表设置为被驱动表，其实也能理解，inner join 和 where 不管左右连，数据集是一样的，而被驱动表是循环中执行次数最多的，通过索引每次循环节约很小的资源，在整个循环中就能节约很大的资源。所以3，4的驱动表都是b，被驱动表是a。

5，6句sql 使用where，mysql会自动将数据量较小的表设置为驱动表，满足小表驱动大表，所以5，6的驱动表都是数据量小的a，被驱动表是b

总结：join 优化 on 连接条件尽量走索引，且最好满足最左前缀原则。查询的字段尽量使用索引字段，避免 select *，避免回表查询。

多表关联时，字段与字段之间的关联顺序也很有讲究，从执行计划中我们可以看到，mysql会首先确定驱动表，然后按照驱动表与其他表的字段关联顺序，依次join，从而决定了mysql会使用到哪些索引。这就是很影响性能的地方，因为此处可能会用到错误的索引（不满足最左前缀匹配原则），所以如果字段关联的顺序发生变化，就会影响索引的选择。

7. sum()、min()、max()等函数，在这些统计字段上建索引。

2. 慢查询日志

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，即具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。

默认情况下，Mysql数据库是关闭慢查询日志，需要我们手动来设置这个参数，当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。

# 查询慢查询是否开启和日志存储路径
mysql> show variables like 'slow_query%';
+---------------------+------------------------------+
| Variable_name       | Value                        |
+---------------------+------------------------------+
| slow_query_log      | OFF                          |
| slow_query_log_file | /var/lib/mysql/vm01-slow.log |
+---------------------+------------------------------+
2 rows in set (0.00 sec)

# 查询慢查询阈值
mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

可以看到MySQL默认关闭慢查询日志，默认的日志存储地址为/var/lib/mysql/vm01-slow.log（默认文件名是hostname-slow.log），且默认的慢查询阈值是10s

方法一：临时配置

# 开启慢查询
set global slow_query_log='ON';
# 设置慢查询日志存放路径，当然你也可以使用默认路径
set global slow_query_log_file='/var/lib/mysql/vm01-slow.log';
# 设置慢查询时间阈值，超过这个值就记录，单位为s
set global long_query_time=2;

方法二：永久配置

# 修改配置文件my.cnf，在[mysqld]下的下方加入
slow_query_log = ON
slow_query_log_file = /var/lib/mysql/vm01-slow.log
long_query_time = 1

日志分析工具mysqldumpslow

MySQL提供了日志分析工具mysqldumpslow。

# 获取返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 <慢查询sql存储路径>

# 得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 <慢查询sql存储路径>

# 得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g “left join” <慢查询sql存储路径>

# 另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现刷屏的情况
mysqldumpslow -s r -t 20 <慢查询sql存储路径> | more

3. Show profiles

Query Profiler是MYSQL自带的一种query诊断分析工具，通过它可以分析出一条SQL语句的性能瓶颈在什么地方。explain和slow query log无法做到精确分析，但是Query Profiler却可以定位出一条SQL语句执行的各种资源消耗情况，比如CPU，IO等，以及该SQL执行所耗费的时间等。不过该工具只有在MYSQL 5.0.37以及以上版本中才有实现。

默认的情况下，Show profiles功能是关闭的，需要自己手动启动。Profile 功能由MySQL会话变量profiling控制，即只能在当前会话中使用。

查看是否开启profiles性能分析功能

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)

0表示关闭，1表示开启。

开启profiles功能

mysql> SET profiling = 1;
Query OK, 0 rows affected (0.00 sec) # 只能在当前会话使用。

查询分析列表

# 执行sql
mysql> select sleep(3);
+----------+
| sleep(3) |
+----------+
|        0 |
+----------+
1 row in set (3.00 sec)

# 查询分析列表
mysql> show profiles;
+----------+------------+-----------------+
| Query_ID | Duration   | Query           |
+----------+------------+-----------------+
|        1 | 3.00040300 | select sleep(3) |
+----------+------------+-----------------+
1 row in set (0.00 sec)

查询列表中某一条sql的执行细节

# 查询分析列表
mysql> show profile for query 1;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000012 |
| Waiting for query cache lock   | 0.000002 |
| checking query cache for query | 0.000035 |
| checking permissions           | 0.000005 |
| Opening tables                 | 0.000005 |
| init                           | 0.000007 |
| optimizing                     | 0.000002 |
| executing                      | 0.000006 |
| User sleep                     | 3.000226 |
| end                            | 0.000012 |
| query end                      | 0.000002 |
| closing tables                 | 0.000003 |
| freeing items                  | 0.000035 |
| logging slow query             | 0.000004 |
| logging slow query             | 0.000047 |
| cleaning up                    | 0.000003 |
+--------------------------------+----------+
16 rows in set (0.00 sec)

# 可指定资源类型查询
mysql> show  profile cpu ,swaps for query 1;
+--------------------------------+----------+----------+------------+-------+
| Status                         | Duration | CPU_user | CPU_system | Swaps |
+--------------------------------+----------+----------+------------+-------+
| starting                       | 0.000012 | 0.000004 |   0.000007 |     0 |
| Waiting for query cache lock   | 0.000002 | 0.000000 |   0.000000 |     0 |
| checking query cache for query | 0.000035 | 0.000012 |   0.000024 |     0 |
| checking permissions           | 0.000005 | 0.000001 |   0.000003 |     0 |
| Opening tables                 | 0.000005 | 0.000002 |   0.000003 |     0 |
| init                           | 0.000007 | 0.000002 |   0.000004 |     0 |
| optimizing                     | 0.000002 | 0.000000 |   0.000001 |     0 |
| executing                      | 0.000006 | 0.000002 |   0.000004 |     0 |
| User sleep                     | 3.000226 | 0.000650 |   0.001300 |     0 |
| end                            | 0.000012 | 0.000003 |   0.000005 |     0 |
| query end                      | 0.000002 | 0.000000 |   0.000001 |     0 |
| closing tables                 | 0.000003 | 0.000001 |   0.000002 |     0 |
| freeing items                  | 0.000035 | 0.000012 |   0.000024 |     0 |
| logging slow query             | 0.000004 | 0.000001 |   0.000002 |     0 |
| logging slow query             | 0.000047 | 0.000016 |   0.000032 |     0 |
| cleaning up                    | 0.000003 | 0.000001 |   0.000002 |     0 |
+--------------------------------+----------+----------+------------+-------+
16 rows in set (0.00 sec)

上述的Status分析结果一般出现以下情况代表SQL可能需要进行优化：

1. converting HEAP to MyISAM

查询结果太大，内存不够，数据往磁盘上搬了。建议：优化索引，可以调整max_heap_table_size

2. Creating tmp table

创建临时表。先拷贝数据到临时表，用完后再删除临时表。消耗内存，数据来回拷贝删除，消耗时间，建议：优化索引

3. Copying to tmp table on disk

把内存中临时表复制到磁盘上，危险！！！建议：优化索引，可以调整tmp_table_size参数，增大内存临时表大小

4. locked

使用到了锁，可能是影响性能的因素。

5. Sending data

解释：从server端发送数据到客户端，也有可能是接收存储引擎层返回的数据，再发送给客户端，数据量很大时尤其经常能看见。

优化：Sending Data不是网络发送，是从硬盘读取，发送到网络是Writing to net。建议：通过索引或加上LIMIT，减少需要扫描并且发送给客户端的数据量