">写在前面—通常SQL的调优过程
查询优化
批量数据脚本
Show Profile
- 是什么
- 分析步骤
  - 参数备注
  - 开发中要注意的status
全局查询日志
- 配置启用
- 编码启用

写在前面—通常SQL的调优过程

查询优化

永远小表驱动大表

类似嵌套循环Nested Loop(内循环)

Case

转化SQL的写法，达到让小表驱动大表，从而达到优化。通过in和exist的转换实现了驱动表的转换

前一种是返回实在的数据，普通情况，把小表先写入子查询
第二种是从交集中获取，先返回true/false，成立的条件是A.id = B.id，实现先对A进行

关于exist

SELECT ...FROM table WHERE EXISTS (subquery)
该语法可以理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果（TRUE或FALSE）来决定主查询的数据结果是否得以保留。
select * from A where exists (select 1 from B where B.id = A.id);
相当于把A表中数据放入子查询中检验，当A.id = B.id时，此记录被保留

提示

EXSTS(subquey)只返回TRUE或FALSE，因此子查询中的SELECT * 也可以是 SELECT 1 或select ‘X’，官方说法是实际执行时会忽略SELECT清单，因此没有区别。
EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担忧效率问题，可进行实际检验以确定是否有效率问题。
EXISTS子查询往往也可以用条件表达式，其他子查询或者JOIN来替代，何种最优需要具体问题具体分析

ORDER BY 关键字优化

使用index方式
对于ORDER BY子句，尽量使用Index方式排序，避免使用FileSort方式排序。
- MySQL支持两种方式的排序，FileSort和Index，Index效率高，它指MySQL扫描索引本身完成排序。FileSort方式效率较低。

建表sql：sql.txt，建立表tab1A，并建立索引：create index idx_A_ageBirth on tblA(age, birth);

遵循最佳左前缀原则

探讨何时产生file sort。下面是CASE

针对第1条，查找和排序都用到了索引
针对第2条，order by后的字段按索引顺序，所以也无须filesort
针对第3条，order by缺少了第一个索引age，直接对birth排序，这就导致了filesort
针对第4条，order by后字段的顺序和索引中建立的顺序不同，优化器并不会调整order by的顺序，所以导致filesort

针对第5条，order by后缺少age，导致filesort
针对第6条，理由同上
针对第7条，order by中只对age排序，且age是索引第一位，故没有发生filesort
针对第8条，由于order by默认升序，但是这里又改为降序，这就用不上之前建立的索引顺序（升序）

双路排序和单路排序

如果排序字段不在索引列上，则会造成文件排序，filesort有两种算法：双路排序、单路排序。

双路排序

双路排序（又叫回表排序模式）：先根据相应的条件取出相应的排序字段和可以直接定位行数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段；（MySQL4.1之前使用的是双路排序）
过程，举下面例子：
1. 从索引 category_id找到第一个满足 category_id = 1 的主键id
2. 根据主键 id 取出整行，把排序字段 category_id和主键 id 这两个字段放到 sort buffer(排序缓存) 中
3. 从索引 category_id 取下一个满足 category_id = 1 记录的主键 id
4. 重复 3、4 直到不满足 category_id= 1
5. 对 sort_buffer 中的字段 views和主键 id 按照字段 views进行排序
6. 遍历排序好的 id 和字段 views，按照 id 的值回到原表中取出所有字段的值返回给客户端 ```sql article表有category_id，comment，views字段，加上如下索引 create index idx_article_cv on article(category_id，comment，views,views); //只加id和views两个索引列

出现回表 explain SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1\G

出现回表 explain SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1\G

未出现回表 explain SELECT id, author_id FROM article WHERE category_id = 1\G

总结下来就是扫描两次磁盘，进行两次IO，第一次IO从磁盘里取排序字段，在buffer进行排序，第二次IO再根据已排序序列从磁盘取数据。<br />不足：取一批数据，要对磁盘进行两次扫描，因为I/O是很耗时的，所以在MySQL4.1之后，出现了第二种改进的算法 -> 单路排序。
<a name="eb8o9"></a>
#### 单路排序

1. 从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后 的列表进行输出。它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO,但是它会使用更多的空间，因为它把每一列都保存在内存中了。
1. 过程：根据上面的sql继续描述
      1. 从索引category_id找到第一个满足 category_id= 1 条件的主键 id
      1. 根据主键 id 取出整行，取出所有字段的值，存入 sort_buffer(排序缓存)中
      1. 从索引category_id找到下一个满足 category_id= 1 条件的主键 id
      1. 重复步骤 2、3 直到不满足 category_id= 1
      1. 对 sort_buffer 中的数据按照字段 views进行排序
      1. 返回结果给客户端
<a name="OoO29"></a>
#### 对比
对比两个排序模式，单路排序会把所有需要查询的字段都放到 sort buffer 中，而双路排序只会把主键 和需要排序的字段放到 sort buffer 中进行排序，然后再通过主键回到原表查询需要的字段。
<a name="ICJbc"></a>
#### 引申出的问题
由于单路排序算法后出，所以总体而言比双路的好。但是用单路有问题：在sort_buffer中，方法B比方法A要多占用很多空间，因为方法B是把所有字段都取出,所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序（创建tmp文件，多路合并)，排完再取取<br />sort_buffer容量大小，再排……从而多次I/O。<br />本来想省一次I/O操作，反而导致了大量的I/O操作，反而得不偿失。

---

<a name="alDuv"></a>
#### 面试题
为什么双路排序到单路排序是随机IO变为顺序IO？<br />答：双路是扫描磁盘两次，回表的每一次操作都是重新寻址，数据并不是连续的，所以是随机IO。单路是将数据加载到内存然后排序，这就只需要按排序后的顺序写出去即可，所以是顺序IO

---

<a name="gRg1N"></a>
#### 选型

1. 至于mysql优化器使用双路排序还是单路排序是有自己的算法判断的，如果查询的列字段大于max_length_for_sort_data变量，则会使用双路排序，反之则会使用单路排序，单路排序速度是更快的，不过比较占据内存，如果在内存空间允许的情况下想要使用单路排序的话，可以增加max_length_for_sort_data变量的大小，max_length_for_sort_data变量默认为1024字节。
1. max_length_for_sort_data指某个表的所有列长度总和
1. **注意**：如果全部使用sort_buffer内存排序一般情况下效率会高于磁盘文件排序，但不能因为这个就随便增 大sort_buffer(默认1M)，mysql很多参数设置都是做过优化的，不要轻易调整。

---

<a name="tF25S"></a>
### 优化策略

- 增大sort_buffer_size参数的设置
- 增大max_length_for_sort_data参数的设置

<a name="a7FSc"></a>
#### 面试题
为什么设置sort_buffer_size、max_length_for_sort_data参数能优化排序？<br />**答**：提高Order By的速度<br />**Order by时select * 是一个大忌**，select会把sort buffer 填满。要牢记只Query需要的字段，这点非常重要。在这里的影响是：当Query的字段大小总和小于max_length_for_sort_data，而且排序字段不是TEXT|BLOB类型时，会用改进后的算法——单路排序，否则用老算法——多路排序。<br />两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次IO，但是用单路排序算法的风险会更大一些，所以要提高sort_buffer__size。<br />尝试提高sort_buffer_size，不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的。<br />尝试提高max_length_for_sort_data，提高这个参数，会增加用改进算法的概率。但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率。
<a name="Trmbb"></a>
## GROUP BY关键字优化
除了以下三点，其余和优化情况和ORDER BY一致

1. group by实质是先排序后进行分组，遵照索引建的最佳左前缀。
1. 当无法使用索引列，增大max_length_for_sort_data参数的设置 + 增大sort_buffer_size参数的设置。
1. where高于having，能写在where限定的条件就不要去having限定了。

---

<a name="Tq5tG"></a>
# 慢查询日志
<a name="GGCX6"></a>
## 是什么

1. MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句。具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。
1. 具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上的语句。
1. 由他来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合之前explain进行全面分析。
<a name="vvvK4"></a>
## 怎么用
<a name="v9YWp"></a>
### 建议

   - **默认情况下，MySQL数据库没有开启慢查询日志**，需要我们手动来设置这个参数。
   - 当然，**如果不是调优需要的话，一般不建议启动该参数**，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。
<a name="eNKR3"></a>
### 开启与日志存放位置

1. 开启日志
```sql
默认 - SHOW VARIABLES LIKE '%slow_query_log%';
开启 - set global slow_query_log = 1;        1是开启，0是关闭。且只对当前数据库生效，如果MySQL重启后则会失效。

若想要永久生效就需要改mysql的配置文件my.cnf。[mysqld]下增加或修改参数slow_query_log和slow_query_log_file后，然后重启MySQL服务器

slow_query_log = 1
slow_query_log_file=/var/lib/mysqatguigu-slow.log            //这里是指定了文件名，若没有指定系统会使用默认文件名

/var/lib/mysql/49568061bba9-slow.log

关于慢查询的参数slow_query_log_file，它指定慢查询日志文件的存放路径，系统默认会给一个缺省的文件host_name-slow.log（如果没有指定参数slow_query_log_file的话）

开启慢查询日志后，什么样的SQL才会被记录到慢查询日志中呢？
- 这个是由参数long_query_time控制，默认情况下long_query_time的值为10秒，命令：SHOW VARIABLES LIKE ‘long_query_time%’; 注意这里是超过10秒，不是大于等于10秒

可以使用命令修改，也可以在my.cnf参数里面修改。
```
设置慢SQL阈值时间：set global long_query_time=3;
```
但是修改之后，使用 SHOW VARIABLES LIKE ‘long_query_time%’; 查看如下：发现并没有修改成功

就需要重新连接或新开一个会话才能看到修改值。
如不重开回话，我们可以用：SHOW global VARIABLES LIKE ‘long_query_time%’; 查看

使用select sleep(4)；进行测试，在slow log中查看超时记录

日志分析工具
在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow。
查看mysqldumpslow的帮助信息，mysqldumpslow —help。
参数介绍

s：是表示按照何种方式排序
c：访问次数
l：锁定时间
r：返回记录
t：查询时间
al：平均锁定时间
ar：平均返回记录数
at：平均查询时间
t：即为返回前面多少条的数据
g：后边搭配一个正则匹配模式，大小写不敏感的
常用语句参考

得到返回记录集最多的10个SQL
- mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log
得到访问次数最多的10个SQL
- mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log
得到按照时间排序的前10条里面含有左连接的查询语句
1. mysqldumpslow -s t -t 10 -g “left join” /var/lib/mysql/atguigu-slow.log
另外建议在使用这些命令时结合│和more 使用，否则有可能出现爆屏情况
1. mysqldumpslow -s r-t 10 /ar/lib/mysql/atguigu-slow.log | more
  批量数据脚本
  建表sql
  sql.txt 建立部门表dept，员工表emp
  设置参数
开启可以创建存储函数的权限，通过设置参数log_bin_trust_function_creators

创建函数，假如报错：This function has none of DETERMINISTIC…。这是由于开启过慢查询日志，就开启了bin-log，我们就必须为我们的function指定一个参数。

show variables like 'log_bin_trust_function_creators';
set global log_bin_trust_function_creators = 1;

这样添加了参数以后，如果mysqld重启，上述参数又会消失，永久方法修改配置文件：

windows下my.ini[mysqld]加上    log_bin_trust_function_creators=1

linux下/etc/my.cnf 下my.cnf[mysqld]加上    log_bin_trust_function_creators=1

创建函数

创建两个函数， rand_string分别随机产生字符串，rand_num随机产生部门编号
注：delimiter是分隔符，修改后；不是分隔符了

---------------------------------随机产生字符串--------------------
delimiter $$ # 两个 $$ 表示结束
create function rand_string(n int) returns varchar(255)
begin
    declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyz';
    declare return_str varchar(255) default '';
    declare i int default 0;
    while i < n do
        set return_str = concat(return_str,substring(chars_str,floor(1+rand()*52),1));
        set i=i+1;
    end while;
    return return_str;
end $$

-----------------------------检验--------------------
mysql> select rand_string(2);
    -> ;
    -> $$
+----------------+
| rand_string(2) |
+----------------+
| af             |
+----------------+
1 row in set (0.00 sec)

--------------------------------产生部门编号--------------------
delimiter $$
create function rand_num() returns int(5)
begin
    declare i int default 0;
    set i=floor(100+rand()*10);
    return i;
end $$


-----------------------------检验--------------------
mysql> select rand_num()$$
+------------+
| rand_num() |
+------------+
|        105 |
+------------+
1 row in set (0.00 sec)

创建存储过程

创建往emp表中插入数据的存储过程

delimiter $$
create procedure insert_emp(in start int(10),in max_num int(10))
begin
 declare i int default 0;
 set autocommit = 0;
 repeat
     set i = i+1;
     insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values((start+i),rand_string(6),'salesman',0001,curdate(),2000,400,rand_num());
     until i=max_num
     end repeat;
 commit;
end $$

创建往dept表中插入数据的存储过程
```
delimiter $$
create procedure insert_dept(in start int(10),in max_num int(10))
begin
 declare i int default 0;
 set autocommit = 0;
 repeat
     set i = i+1;
     insert into dept(deptno,dname,loc) values((start+i),rand_string(10),rand_string(8));
     until i=max_num
     end repeat;
 commit;
end $$
```
调用存储过程
- 定义函数和存储过程完毕，事先恢复以；为结束： DELIMITER ;
- 此处调用存储过程，存储过程中调用定义的函数 ```sql 往dept表中插入数据 mysql> DELIMITER ;
  mysql> CALL insert_dept(100, 10); Query OK, 0 rows affected (0.01 sec)

往emp表中插入50万数据 mysql> DELIMITER ; mysql> CALL insert_emp(10001, 50000); Query OK, 0 rows affected (27.00 sec)


   - 查看运行结果
```sql
mysql> select * from dept;
+----+--------+---------+--------+
| id | deptno | dname   | loc    |
+----+--------+---------+--------+
|  1 |    101 | mqgfy   | ck     |
|  2 |    102 | wgighsr | kbq    |
|  3 |    103 | gjgdyj  | brb    |
|  4 |    104 | gzfug   | p      |
|  5 |    105 | keitu   | cib    |
|  6 |    106 | nndvuv  | csue   |
|  7 |    107 | cdudl   | tw     |
|  8 |    108 | aafyea  | aqq    |
|  9 |    109 | zuqezjx | dpqoyo |
| 10 |    110 | pam     | cses   |
+----+--------+---------+--------+
10 rows in set (0.00 sec)

mysql> select * from emp limit 20;
+----+--------+-------+----------+-----+------------+---------+--------+--------+
| id | empno  | ename | job      | mgr | hiredate   | sal     | comm   | deptno |
+----+--------+-------+----------+-----+------------+---------+--------+--------+
|  1 | 100002 | xmbva | salesman |   1 | 2021-04-05 | 2000.00 | 400.00 |    108 |
|  2 | 100003 | aeq   | salesman |   1 | 2021-04-05 | 2000.00 | 400.00 |    109 |
|  3 | 100004 | cnjfz | salesman |   1 | 2021-04-05 | 2000.00 | 400.00 |    105 |
|  4 | 100005 | wwhd  | salesman |   1 | 2021-04-05 | 2000.00 | 400.00 |    100 |
|  5 | 100006 | e     | salesman |   1 | 2021-04-05 | 2000.00 | 400.00 |    107 |
|  6 | 100007 | yjfr  | salesman |   1 | 2021-04-05 | 2000.00 | 400.00 |    108 |
|  7 | 100008 | xlp   | salesman |   1 | 2021-04-05 | 2000.00 | 400.00 |    102 |
|  8 | 100009 | mp    | salesman |   1 | 2021-04-05 | 2000.00 | 400.00 |    102 |
|  9 | 100010 | tcdl  | salesman |   1 | 2021-04-05 | 2000.00 | 400.00 |    107 |
| 10 | 100011 | akw   | salesman |   1 | 2021-04-05 | 2000.00 | 400.00 |    106 |
| 11 | 100012 | re    | salesman |   1 | 2021-04-05 | 2000.00 | 400.00 |    108 |
| 12 | 100013 | qip   | salesman |   1 | 2021-04-05 | 2000.00 | 400.00 |    105 |
| 13 | 100014 | bvaf  | salesman |   1 | 2021-04-05 | 2000.00 | 400.00 |    108 |
| 14 | 100015 | g     | salesman |   1 | 2021-04-05 | 2000.00 | 400.00 |    106 |
| 15 | 100016 | qt    | salesman |   1 | 2021-04-05 | 2000.00 | 400.00 |    102 |
| 16 | 100017 | bzy   | salesman |   1 | 2021-04-05 | 2000.00 | 400.00 |    109 |
| 17 | 100018 | gf    | salesman |   1 | 2021-04-05 | 2000.00 | 400.00 |    102 |
| 18 | 100019 | r     | salesman |   1 | 2021-04-05 | 2000.00 | 400.00 |    108 |
| 19 | 100020 | ydokg | salesman |   1 | 2021-04-05 | 2000.00 | 400.00 |    100 |
| 20 | 100021 | ee    | salesman |   1 | 2021-04-05 | 2000.00 | 400.00 |    107 |
+----+--------+-------+----------+-----+------------+---------+--------+--------+
20 rows in set (0.00 sec)

Show Profile

是什么

比explain更细致的排查
Show Profile是mysql提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量。
官方文档：https://dev.mysql.com/doc/refman/8.0/en/show-profile.html
默认情况下，参数处于关闭状态，并保存最近15次的运行结果。

分析步骤

是否支持，看看当前的mysql版本是否支持。

mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | OFF   |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)

开启功能，默认是关闭，使用前需要开启。 ```sql mysql> set profiling=on; Query OK, 0 rows affected, 1 warning (0.00 sec)


3. 运行SQL
```sql
mysql> select * from emp group by id%10 limit 150000;
+----+--------+-------+----------+-----+------------+---------+--------+--------+
| id | empno  | ename | job      | mgr | hiredate   | sal     | comm   | deptno |
+----+--------+-------+----------+-----+------------+---------+--------+--------+
| 10 | 100011 | akw   | salesman |   1 | 2021-04-05 | 2000.00 | 400.00 |    106 |
|  1 | 100002 | xmbva | salesman |   1 | 2021-04-05 | 2000.00 | 400.00 |    108 |
|  2 | 100003 | aeq   | salesman |   1 | 2021-04-05 | 2000.00 | 400.00 |    109 |
|  3 | 100004 | cnjfz | salesman |   1 | 2021-04-05 | 2000.00 | 400.00 |    105 |
|  4 | 100005 | wwhd  | salesman |   1 | 2021-04-05 | 2000.00 | 400.00 |    100 |
|  5 | 100006 | e     | salesman |   1 | 2021-04-05 | 2000.00 | 400.00 |    107 |
|  6 | 100007 | yjfr  | salesman |   1 | 2021-04-05 | 2000.00 | 400.00 |    108 |
|  7 | 100008 | xlp   | salesman |   1 | 2021-04-05 | 2000.00 | 400.00 |    102 |
|  8 | 100009 | mp    | salesman |   1 | 2021-04-05 | 2000.00 | 400.00 |    102 |
|  9 | 100010 | tcdl  | salesman |   1 | 2021-04-05 | 2000.00 | 400.00 |    107 |
+----+--------+-------+----------+-----+------------+---------+--------+--------+
10 rows in set (0.55 sec)

mysql> select * from emp group by id%20 order by 5;
+----+--------+-------+----------+-----+------------+---------+--------+--------+
| id | empno  | ename | job      | mgr | hiredate   | sal     | comm   | deptno |
+----+--------+-------+----------+-----+------------+---------+--------+--------+
|  1 | 100002 | xmbva | salesman |   1 | 2021-04-05 | 2000.00 | 400.00 |    108 |
|  3 | 100004 | cnjfz | salesman |   1 | 2021-04-05 | 2000.00 | 400.00 |    105 |
|  5 | 100006 | e     | salesman |   1 | 2021-04-05 | 2000.00 | 400.00 |    107 |
|  7 | 100008 | xlp   | salesman |   1 | 2021-04-05 | 2000.00 | 400.00 |    102 |
|  9 | 100010 | tcdl  | salesman |   1 | 2021-04-05 | 2000.00 | 400.00 |    107 |
| 11 | 100012 | re    | salesman |   1 | 2021-04-05 | 2000.00 | 400.00 |    108 |
| 13 | 100014 | bvaf  | salesman |   1 | 2021-04-05 | 2000.00 | 400.00 |    108 |
| 15 | 100016 | qt    | salesman |   1 | 2021-04-05 | 2000.00 | 400.00 |    102 |
| 17 | 100018 | gf    | salesman |   1 | 2021-04-05 | 2000.00 | 400.00 |    102 |
| 19 | 100020 | ydokg | salesman |   1 | 2021-04-05 | 2000.00 | 400.00 |    100 |
|  2 | 100003 | aeq   | salesman |   1 | 2021-04-05 | 2000.00 | 400.00 |    109 |
|  4 | 100005 | wwhd  | salesman |   1 | 2021-04-05 | 2000.00 | 400.00 |    100 |
|  6 | 100007 | yjfr  | salesman |   1 | 2021-04-05 | 2000.00 | 400.00 |    108 |
|  8 | 100009 | mp    | salesman |   1 | 2021-04-05 | 2000.00 | 400.00 |    102 |
| 10 | 100011 | akw   | salesman |   1 | 2021-04-05 | 2000.00 | 400.00 |    106 |
| 12 | 100013 | qip   | salesman |   1 | 2021-04-05 | 2000.00 | 400.00 |    105 |
| 14 | 100015 | g     | salesman |   1 | 2021-04-05 | 2000.00 | 400.00 |    106 |
| 16 | 100017 | bzy   | salesman |   1 | 2021-04-05 | 2000.00 | 400.00 |    109 |
| 18 | 100019 | r     | salesman |   1 | 2021-04-05 | 2000.00 | 400.00 |    108 |
| 20 | 100021 | ee    | salesman |   1 | 2021-04-05 | 2000.00 | 400.00 |    107 |
+----+--------+-------+----------+-----+------------+---------+--------+--------+
20 rows in set (0.57 sec)

查看结果：show profiles;

mysql> show profiles;
+----------+------------+-----------------------------------------------+
| Query_ID | Duration   | Query                                         |
+----------+------------+-----------------------------------------------+
|        1 | 0.00204000 | show variables like 'profiling'               |
|        2 | 0.55134250 | select * from emp group by id%10 limit 150000 |
|        3 | 0.56902000 | select * from emp group by id%20 order by 5   |
+----------+------------+-----------------------------------------------+
3 rows in set, 1 warning (0.00 sec)

诊断SQL，show profile cpu,block io for query +上一步前面的问题SQL数字号码;

mysql> show profile cpu,block io for query 3;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000049 | 0.000000 |   0.000000 |         NULL |          NULL |
| checking permissions | 0.000005 | 0.000000 |   0.000000 |         NULL |          NULL |
| Opening tables       | 0.000012 | 0.000000 |   0.000000 |         NULL |          NULL |
| init                 | 0.000021 | 0.000000 |   0.000000 |         NULL |          NULL |
| System lock          | 0.000009 | 0.000000 |   0.000000 |         NULL |          NULL |
| optimizing           | 0.000003 | 0.000000 |   0.000000 |         NULL |          NULL |
| statistics           | 0.000017 | 0.000000 |   0.000000 |         NULL |          NULL |
| preparing            | 0.000008 | 0.000000 |   0.000000 |         NULL |          NULL |
| Creating tmp table   | 0.000045 | 0.000000 |   0.000000 |         NULL |          NULL |
| Sorting result       | 0.000004 | 0.000000 |   0.000000 |         NULL |          NULL |
| executing            | 0.000002 | 0.000000 |   0.000000 |         NULL |          NULL |
| Sending data         | 0.568704 | 0.546875 |   0.046875 |         NULL |          NULL |
| Creating sort index  | 0.000048 | 0.000000 |   0.000000 |         NULL |          NULL |
| end                  | 0.000003 | 0.000000 |   0.000000 |         NULL |          NULL |
| query end            | 0.000005 | 0.000000 |   0.000000 |         NULL |          NULL |
| removing tmp table   | 0.000006 | 0.000000 |   0.000000 |         NULL |          NULL |
| query end            | 0.000003 | 0.000000 |   0.000000 |         NULL |          NULL |
| closing tables       | 0.000004 | 0.000000 |   0.000000 |         NULL |          NULL |
| freeing items        | 0.000061 | 0.000000 |   0.000000 |         NULL |          NULL |
| cleaning up          | 0.000015 | 0.000000 |   0.000000 |         NULL |          NULL |
+----------------------+----------+----------+------------+--------------+---------------+
20 rows in set, 1 warning (0.00 sec)

参数备注

show profile cpu,block io for query 3; （标红的是要分析的参数）

ALL：显示所有的开销信息。
BLOCK IO：显示块lO相关开销。
CONTEXT SWITCHES ：上下文切换相关开销。
CPU：显示CPU相关开销信息。
IPC：显示发送和接收相关开销信息。
MEMORY：显示内存相关开销信息。
PAGE FAULTS：显示页面错误相关开销信息。
SOURCE：显示和Source_function，Source_file，Source_line相关的开销信息。
SWAPS：显示交换次数相关开销的信息。
开发中要注意的status
出现下面四个之一，就必须优化

converting HEAP to MyISAM ：查询结果太大，内存都不够用了往磁盘上搬了。
Creating tmp table ：创建临时表，拷贝数据到临时表，用完再删除
Copying to tmp table on disk：把内存中临时表复制到磁盘，危险!
locked
全局查询日志
永远不要在生产环境开启这个功能。
配置启用
配置文件启用。在mysql的my.cnf中，设置如下：
```
#开启
general_log=1
#记录日志文件的路径
general_log_file=/path/logfile
#输出格式
log_output=FILE
```
编码启用
```sql mysql> set global general_log=1; Query OK, 0 rows affected (0.01 sec)

mysql> set global log_output=’TABLE’; Query OK, 0 rows affected (0.00 sec)

<a name="d84Gu"></a>
## 查看
```sql
mysql> select * from mysql.general_log;
+----------------------------+------------------------------+-----------+-----------+--------------+---------------------------------+
| event_time                 | user_host                    | thread_id | server_id | command_type | argument                        |
+----------------------------+------------------------------+-----------+-----------+--------------+---------------------------------+
| 2021-04-05 19:57:28.182473 | root[root] @ localhost [::1] |         5 |         1 | Query        | select * from mysql.general_log |
+----------------------------+------------------------------+-----------+-----------+--------------+---------------------------------+
1 row in set (0.00 sec)

CS笔记

3_查询截取分析

写在前面—通常SQL的调优过程

查询优化

永远小表驱动大表

Case

关于exist

ORDER BY 关键字优化

使用index方式

遵循最佳左前缀原则

双路排序和单路排序

双路排序

日志分析工具

参数介绍

常用语句参考

批量数据脚本

建表sql

设置参数

创建函数

创建存储过程

调用存储过程

Show Profile

是什么

分析步骤

参数备注

开发中要注意的status

全局查询日志

配置启用

编码启用