查询截取分析

慢查询日志

1. 是什么：mysql将执行时间超过long_query_time的sql写入慢查询日志中，以便用explain进行性能分析
2. 默认：默认该功能关闭，且long_query_time=10。
3. 查看是否开启慢查询日志功能以及日志存放位置：show variables like ‘%slow_query_log%’;
4. 查看long_query_time：show variables like ‘%long_query_time%’
5. 查看当前系统中有多少条慢查询日志：show global status like ‘%Slow_queries%’;
6. 设置开启慢查询日志功能和日志存放位置：
   1. 命令行方式：缺点：当mysql重启后无效，其他全局变量设置也是一样
      1. 开启慢查询日志功能：set global slow_query_log = 1;
      2. 设置日志存放位置：set global slow_query_log_file = ‘var/lib/mysql/主机名-slow.log’;
      3. 默认缺省日志存放位置：var/lib/mysql/主机名-slow.log
7. 设置long_query_time：默认10秒钟，set global long_query_time = 3;

查询时，要么show global variables like ‘%long_query_time%’ 要么重开一个客户端

1. 配置文件方式设置：永久生效

[mysqld]块下添加：
slow_query_log = 1
slow_query_log_file = var/lib/mysql/主机名-slow.low
long_query_time = 3
log_output = FILE

1. 慢查询日志工具：mysqldumpslow [选项] 慢查询日志文件

mysqldumpslow —help;

1. 测试慢查询：select sleep(n); 睡眠n秒钟

show profile 查看SQL完整生命周期

1. 是什么：mysql提供的用于分析当前会话中sql语句执行的资源消耗情况
2. 默认：功能关闭，且保存最近15次的运行结果
3. 查看是否开启：show variables like ‘%profiling%’;

mysql> show variables like ‘%profiling%’;

| Variable_name | Value |
+————————————+———-+
| have_profiling | YES |
| profiling | OFF |
| profiling_history_size | 15 |

1. 开启功能：set profiling = on;
2. 查看最近执行的sql：show profiles;

mysql> show profiles;
+—————+——————+——————————————————————————+
| QueryID | Duration | Query |
+—————+——————+——————————————————————————+
| 16 | 0.33399525 | select s., d._ from stu s cross join department d |
| 17 | 0.00038475 | select * from stu |
+—————+——————+——————————————————————————+

1. 诊断具体的SQL：show profile cpu, block io for query 5中具体的query_id

mysql> show profile cpu, block io for query 16;
+———————————+—————+—————+——————+———————+———————-+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+———————————+—————+—————+——————+———————+———————-+
| starting | 0.002787 | 0.000000 | 0.000000 | NULL | NULL |
| checking permissions | 0.000009 | 0.000000 | 0.000000 | NULL | NULL |
| checking permissions | 0.000003 | 0.000000 | 0.000000 | NULL | NULL |
| Opening tables | 0.000019 | 0.000000 | 0.000000 | NULL | NULL |
| init | 0.000012 | 0.000000 | 0.000000 | NULL | NULL |
| System lock | 0.000006 | 0.000000 | 0.000000 | NULL | NULL |
| optimizing | 0.000003 | 0.000000 | 0.000000 | NULL | NULL |
| statistics | 0.000010 | 0.000000 | 0.000000 | NULL | NULL |
| preparing | 0.001026 | 0.000000 | 0.000000 | NULL | NULL |
| executing | 0.000010 | 0.000000 | 0.000000 | NULL | NULL |
| Sending data | 0.329995 | 0.000000 | 0.000000 | NULL | NULL |
| end | 0.000009 | 0.000000 | 0.000000 | NULL | NULL |
| query end | 0.000010 | 0.000000 | 0.000000 | NULL | NULL |
| closing tables | 0.000011 | 0.000000 | 0.000000 | NULL | NULL |
| freeing items | 0.000075 | 0.000000 | 0.000000 | NULL | NULL |
| cleaning up | 0.000013 | 0.000000 | 0.000000 | NULL | NULL |
+———————————+—————+—————+——————+———————+———————-+

1. 其他可以查看的资源消耗情况：show profile all, memory, cpu, block io for query query_id;

其中，cpu, block io用的最多

1. show profile结果中需要非常关注的记录：
   1. converting heap to MyISAM：查询结果太大，数据从内存往磁盘上转移
   2. create tmp table：创建临时表（在内存中），主要两个步骤：拷贝数据到临时表和删除临时表
   3. copying to tmp table on disk：拷贝临时表到磁盘上
   4. locked：加锁

全局查询日志

1. 是什么：general_log，将所有SQL的执行情况均记录下来，因为记录信息太大，一般只在测试环境中使用。
2. 可以以配置文件方式和命令行方式设置：具体查看资料。