MySQL|MySQL-性能分析概述 MySQL|mysql

一、数据库服务器的优化步骤整个流程划分成了观察（Show status）和行动（Action）两个部分。字母 S 的部分代表观察（会使用相应的分析工具），字母 A 代表的部分是行动（对应分析可以采取的行动）。

文章图片

二、查看系统参数在MySQL中，可以使用 SHOW STATUS 语句查询一些MySQL数据库服务器的性能参数、执行频率。

SHOW [GLOBAL|SESSION] STATUS LIKE '参数';

? Connections：连接MySQL服务器的次数。
? Uptime：MySQL服务器的上线时间。
? Slow_queries：慢查询的次数。
? Innodb_rows_read：Select查询返回的行数
? Innodb_rows_inserted：执行INSERT操作插入的行数
? Innodb_rows_updated：执行UPDATE操作更新的行数
? Innodb_rows_deleted：执行DELETE操作删除的行数
? Com_select：查询操作的次数。
? Com_insert：插入操作的次数。对于批量插入的 INSERT 操作，只累加一次。
? Com_update：更新操作的次数。
? Com_delete：删除操作的次数。

三、统计SQL的查询成本

SHOW STATUS LIKE 'last_query_cost'; #显示最后一次查询用了多少个页

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

#1. 开启slow_query_log set global slow_query_log='ON'; #2. 修改long_query_time阈值 show global variables like '%long_query_time%'; set global long_query_time = 1; #3. 查看慢查询数目 SHOW GLOBAL STATUS LIKE '%Slow_queries%'; #4. 使用慢查询日志分析工具：mysqldumpslowmysqldumpslow 命令的具体参数如下： -a: 不将数字抽象成N，字符串抽象成S -s: 是表示按照何种方式排序： c: 访问次数 l: 锁定时间 r: 返回记录 t: 查询时间 al:平均锁定时间 ar:平均返回记录数 at:平均查询时间（默认方式） ac:平均查询次数 -t: 即为返回前面多少条的数据； -g: 后边搭配一个正则匹配模式，大小写不敏感的；#举例 #得到返回记录集最多的10个SQL mysqldumpslow -s r -t 10 /var/lib/mysql/slow.log#得到访问次数最多的10个SQL mysqldumpslow -s c -t 10 /var/lib/mysql/slow.log#得到按照时间排序的前10条里面含有左连接的查询语句 mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/slow.log#另外建议在使用这些命令时结合 | 和more 使用，否则有可能出现爆屏情况 mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more#5.关闭慢查询日志 set global slow_query_log='OFF'; #6.删除慢查询日志 show variables like 'slow_query_log%'; #手动进入目录删除

五、查看SQL执行成本-SHOW PROFILE

#开启 show variables like 'profiling'; set profiling = 'ON'; #然后执行相关的查询语句。接着看下当前会话都有哪些 profiles，使用下面这条命令：show profiles; #如果我们想要查看最近一次查询的开销，可以使用： show profile; #可以查看指定 query id 的开销，以及不同类型的开销，例如 query id 为 2的 show profile cpu,block io for query 2;

show profile的常用查询参数：
① ALL：显示所有的开销信息。
② BLOCK IO：显示块IO开销。
③ CONTEXT SWITCHES：上下文切换开销。
④ CPU：显示CPU开销信息。
⑤ IPC：显示发送和接收开销信息。
⑥ MEMORY：显示内存开销信息。
⑦ PAGE FAULTS：显示页面错误开销信息。
⑧ SOURCE：显示和Source_function，Source_file， Source_line相关的开销信息。
⑨ SWAPS：显示交换次数开销信息。

六、分析查询语句-EXPLAIN

EXPLAIN SELECT select_options或者DESCRIBE SELECT select_options# MYSQL 5.6.3 以后就可以使用 EXPLAIN SELECT/UPDATE/DELETE xxx

6.1 输出结构（略）输出各列的结构：

文章图片

select_type:
一个大的查询语句可能包含若干SELECT关键字，这个属性就表明每个SELECT子句在整个大查询中的角色。

文章图片

*type（访问方法）:
【MySQL|MySQL-性能分析概述】system ， const ， eq_ref ， ref ， fulltext ， ref_or_null ， index_merge ， unique_subquery ， index_subquery ， range ， index ， ALL 。
结果值从最好到最坏依次是： system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL 其中比较重要的几个提取出来（见蓝色）。SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，最好是 consts级别。（阿里巴巴开发手册要求）
6.2 四种输出格式 6.2.1 传统格式

EXPLAIN SELECT s1.key1, s2.key1 FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.common_field IS NOT NULL;

文章图片

6.2.2 JSON格式

EXPLAIN FORMAT=JSON SELECT ....

6.2.3 TREE格式

EXPLAIN FORMAT=tree SELECT ....

6.2.4 可视化输出
可以通过MySQL Workbench可视化查看MySQL的执行计划。通过点击Workbench的放大镜图标，即可生成可视化的查询计划。
七、分析优化器执行计划-trace

SET optimizer_trace="enabled=on",end_markers_in_json=on; #开启 set optimizer_trace_max_mem_size=1000000; #内存限制

开启后，可分析如下语句：
SELECT
INSERT
REPLACE
UPDATE
DELETE
EXPLAIN
SET
DECLARE
CASE
IF
RETURN
CALL

#执行语句后，查询 information_schema.optimizer_trace 就可以知道MySQL是如何执行SQL的：select * from information_schema.optimizer_trace;

八、MySQL监控分析视图-sys schema

1. 主机相关：以host_summary开头，主要汇总了IO延迟的信息。
2. Innodb相关：以innodb开头，汇总了innodb buffer信息和事务等待innodb锁的信息。
3. I/o相关：以io开头，汇总了等待I/O、I/O使用量情况。
4. 内存使用情况：以memory开头，从主机、线程、事件等角度展示内存的使用情况
5. 连接与会话信息：processlist和session相关视图，总结了会话相关信息。
6. 表相关：以schema_table开头的视图，展示了表的统计信息。
7. 索引信息：统计了索引的使用情况，包含冗余索引和未使用的索引情况。
8. 语句相关：以statement开头，包含执行全表扫描、使用临时表、排序等的语句信息。
9. 用户相关：以user开头的视图，统计了用户使用的文件I/O、执行语句统计信息。
10. 等待事件相关信息：以wait开头，展示等待事件的延迟情况。

#索引情况 #1. 查询冗余索引 select * from sys.schema_redundant_indexes; #2. 查询未使用过的索引 select * from sys.schema_unused_indexes; #3. 查询索引的使用情况 select index_name,rows_selected,rows_inserted,rows_updated,rows_deleted from sys.schema_index_statistics where table_schema='dbname' ; #表相关 # 1. 查询表的访问量 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; # 2. 查询占用bufferpool较多的表 select object_schema,object_name,allocated,data from sys.innodb_buffer_stats_by_table order by allocated limit 10; # 3. 查看表的全表扫描情况 select * from sys.statements_with_full_table_scans where db='dbname'; #语句相关 #1. 监控SQL执行的频率 select db,exec_count,query from sys.statement_analysis order by exec_count desc; #2. 监控使用了排序的SQL select db,exec_count,first_seen,last_seen,query from sys.statements_with_sorting limit 1; #3. 监控使用了临时表或者磁盘临时表的SQL select db,exec_count,tmp_tables,tmp_disk_tables,query from sys.statement_analysis where tmp_tables>0 or tmp_disk_tables >0 order by (tmp_tables+tmp_disk_tables) desc; #IO相关 #1. 查看消耗磁盘IO的文件 select file,avg_read,avg_write,avg_read+avg_write as avg_io from sys.io_global_by_file_by_bytes order by avg_read limit 10; #Innodb 相关 #1. 行锁阻塞情况 select * from sys.innodb_lock_waits;