第09章 性能分析工具的使用

1. 统计SQL的查询成本：last_query_cost

  1. SHOW STATUS LIKE 'last_query_cost';

使用场景：它对于比较开销是非常有用的，特别是我们有好几种查询方式可选的时候。

SQL 查询是一个动态的过程，从页加载的角度来看，我们可以得到以下两点结论：

  1. 位置决定效率。如果页就在数据库缓冲池中，那么效率是最高的，否则还需要从内存或者磁盘中进行读取，当然针对单个页的读取来说，如果页存在于内存中，会比在磁盘中读取效率高很多。
  2. 批量决定效率。如果我们从磁盘中对单一页进行随机读，那么效率是很低的（差不多10ms），而采用顺序读取的方式，批量对页进行读取，平均一页的读取效率就会提升很多，甚至要快于单个页面在内存中的随机读取。

所以说，遇到I/O并不用担心，方法找对了，效率还是很高的。我们首先要考虑数据存放的位置，如果是经常使用的数据就要尽量放到缓冲池中，其次我们可以充分利用磁盘的吞吐能力，一次性批量读取数据，这样单个页的读取效率也就得到了提升。

2. 定位执行慢的SQL：慢查询日志

MySQL的慢查询日志，用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过long_query_time的值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上（不含10秒）的语句，认为是超出了我们的最大忍耐时间值。

默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。

2.1 开启慢查询日志参数

1. 开启slow_query_log

  1. set global slow_query_log='ON';

查看下慢查询日志是否开启，以及慢查询日志文件的位置：

  1. show variables like `%slow_query_log%`;

2. 修改long_query_time阈值

  1. show variables like '%long_query_time%';
  1. #测试发现：设置global的方式对当前session的long_query_time失效。对新连接的客户端有效。所以可以一并 执行下述语句 
  2. mysql > set global long_query_time = 1; 
  3. mysql> show global variables like '%long_query_time%'; 
  5. mysql> set long_query_time=1; 
  6. mysql> show variables like '%long_query_time%';

2.2 查看慢查询数目
  1. SHOW GLOBAL STATUS LIKE '%Slow_queries%';

2.3 慢查询日志分析工具：mysqldumpslow
  1. #得到返回记录集最多的10个SQL 
  2. mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log 
  3. #得到访问次数最多的10个SQL 
  4. mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log
  5. #得到按照时间排序的前10条里面含有左连接的查询语句 
  6. mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log 
  7. #另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现爆屏情况 
  8. mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more

2.4 关闭慢查询日志

方式1：永久性方式

  1. [mysqld] 
  2. slow_query_log=OFF
  3. #或
  4. [mysqld] 
  5. #slow_query_log =OFF

方式2：临时性方式

  1. SET GLOBAL slow_query_log=off;

3. 查看 SQL 执行成本：SHOW PROFILE

  1. show variables like 'profiling';
  2. #开启
  3. set profiling = 'ON';
  4. #查看
  5. show profiles;
  6. show profile cpu,block io for query 2;

4. 分析查询语句：EXPLAIN

4.1 基本语法
  1. EXPLAIN SELECT select_options 
  2. #或者
  3. DESCRIBE SELECT select_options

EXPLAIN 语句输出的各个列的作用如下：

列名 描述
id 在一个大的查询语句中每个SELECT关键字都对应一个唯一的id
select_type SELECT关键字对应的那个查询的类型
table 表名
partitions 匹配的分区信息
type 针对单表的访问方法
possible_keys 可能用到的索引
key 实际上使用的索引
key_len 实际使用到的索引长度
ref 当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows 预估的需要读取的记录条数
filtered 某个表经过搜索条件过滤后剩余记录条数的百分比
Extra 一些额外的信息

4.2 EXPLAIN各列作用

1. table

不论我们的查询语句有多复杂，包含了多少个表 ，到最后也是需要对每个表进行单表访问的，所以MySQL规定EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该表的表名（有时不是真实的表名字，可能是简称）。

2. id

  • id如果相同，可以认为是一组，从上往下顺序执行
  • 在所有组中，id值越大，优先级越高，越先执行
  • 关注点：id号每个号码，表示一趟独立的查询,一个sql的查询趟数越少越好

3. select_type

4. partitions

5. type（重点）

结果值从最好到最坏依次是： system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

SQL性能优化的目标：至少要达到 range级别，要求是ref级别，最好是consts级别。（阿里巴巴开发手册要求）

6. possible_keys和key

7. key_len（重点）

key_len的长度计算公式：

  1. varchar(10)变长字段且允许NULL = 10 * ( character set： utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段) 
  3. varchar(10)变长字段且不允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+2(变长字段)
  5. char(10)固定字段且允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL) 
  7. char(10)固定字段且不允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)

8. ref

9. rows（重点）

预估的需要读取的记录条数

10. filtered

11. Extra

5. EXPLAIN的进一步使用

5.1 EXPLAIN四种输出格式

这里谈谈EXPLAIN的输出格式。EXPLAIN可以输出四种格式：传统格式，JSON格式，TREE格式以及可视化输出。用户可以根据需要选择适用于自己的格式。

1. 传统格式

2. JSON格式

JSON格式：在EXPLAIN单词和真正的查询语句中间加上FORMAT=JSON。用于查看执行成本cost_info

3. TREE格式

TREE格式是8.0.16版本之后引入的新格式，主要根据查询的各个部分之间的关系和各部分的执行顺序来描述如何查询。

4. 可视化输出

可视化输出，可以通过MySQL Workbench可视化查看MySQL的执行计划。

5.2 SHOW WARNINGS的使用
  1. mysql> EXPLAIN SELECT s1.key1, s2.key1 FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.common_field IS NOT NULL;
  2. # 查看优化后的执行语句
  3. mysql> SHOW WARNINGS\G

6. 分析优化器执行计划：trace

  1. # 开启
  2. SET optimizer_trace="enabled=on",end_markers_in_json=on; 
  3. # 设置大小
  4. set optimizer_trace_max_mem_size=1000000;
  5. # 使用
  6. select * from student where id < 10;
  7. select * from information_schema.optimizer_trace\G

7. MySQL监控分析视图-sys schema

7.1 Sys schema视图使用场景

索引情况

  1. #1. 查询冗余索引 
  2. select * from sys.schema_redundant_indexes; 
  3. #2. 查询未使用过的索引 
  4. select * from sys.schema_unused_indexes; 
  5. #3. 查询索引的使用情况 
  6. select index_name,rows_selected,rows_inserted,rows_updated,rows_deleted from sys.schema_index_statistics where table_schema='dbname' ;

表相关

  1. # 1. 查询表的访问量 
  2. select table_schema,table_name,sum(io_read_requests+io_write_requests) as io from sys.schema_table_statistics group by table_schema,table_name order by io desc; 
  3. # 2. 查询占用bufferpool较多的表 
  4. select object_schema,object_name,allocated,data
  5. from sys.innodb_buffer_stats_by_table order by allocated limit 10; 
  6. # 3. 查看表的全表扫描情况 
  7. select * from sys.statements_with_full_table_scans where db='dbname';

语句相关

  1. #1. 监控SQL执行的频率 
  2. select db,exec_count,query from sys.statement_analysis order by exec_count desc; 
  3. #2. 监控使用了排序的SQL 
  4. select db,exec_count,first_seen,last_seen,query
  5. from sys.statements_with_sorting limit 1; 
  6. #3. 监控使用了临时表或者磁盘临时表的SQL 
  7. select db,exec_count,tmp_tables,tmp_disk_tables,query
  8. from sys.statement_analysis where tmp_tables>0 or tmp_disk_tables >0 order by (tmp_tables+tmp_disk_tables) desc;

IO相关

  1. #1. 查看消耗磁盘IO的文件 
  2. select file,avg_read,avg_write,avg_read+avg_write as avg_io
  3. from sys.io_global_by_file_by_bytes order by avg_read limit 10;

Innodb 相关

  1. #1. 行锁阻塞情况 
  2. select * from sys.innodb_lock_waits;