一、基础知识
1.1 数据库服务器的优化步骤1.2 查看系统性能参数1.3 统计 SQL 查询成本1.4 查看 SQL 执行成本 二、mysqldumpslow 慢日志分析
2.1 相关变量2.2 案例准备2.3 测试2.4 工具使用 三、trace 分析优化器执行计划
3.1 相关参数3.2 使用分析 四、sys.schema 监控分析视图
4.1 视图摘要4.2 视图使用场景
4.2.1 索引情况4.2.2 表相关4.2.3 语句相关4.2.4 IO 相关4.2.5 Innodb 相关
一、基础知识 1.1 数据库服务器的优化步骤整个流程划分成了观察（Show status）和行动（Action）两个部分。字母 S 的部分代表观察（会使用相应的分析工具），字母 A 代表的部分是行动（对应分析可以采取的行动）。
优化步骤
代价及成效
1.2 查看系统性能参数在MySQL中，可以使用SHOW STATUS语句查询一些MySQL数据库服务器的性能参数、执行频率。
mysql> SHOW STATUS LIKE 'Slow_queries'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Slow_queries | 0 | +---------------+-------+ 1 row in set (0.00 sec) mysql> SHOW STATUS LIKE 'Innodb_rows_read'; +------------------+---------+ | Variable_name | Value | +------------------+---------+ | Innodb_rows_read | 8792854 | +------------------+---------+ 1 row in set (0.00 sec) mysql>
| 常用参数 | 说明 |
|---|---|
| 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 | 删除操作的次数 |
- 它是作为比较各个查询之间的开销的一个依据。它只能检测比较简单的查询开销，对于包含子查询和union的查询是测试不出来的。当我们执行查询的时候，MySQL会自动生成一个执行计划，也就是query plan，而且通常有很多种不同的实现方式，它会选择最低的那一个，而这个cost值就是开销最低的那一个。它对于比较我们的开销是非常有用的，特别是我们有好几种查询方式可选的时候。
mysql> SELECT COUNT(DISTINCT(student_id)) FROM `student_info`; +-----------------------------+ | COUNT(DISTINCT(student_id)) | +-----------------------------+ | 198058 | +-----------------------------+ 1 row in set (0.67 sec) mysql> SHOW STATUS LIKE 'last_query_cost'; +-----------------+---------------+ | Variable_name | Value | +-----------------+---------------+ | Last_query_cost | 100458.549000 | +-----------------+---------------+ 1 row in set (0.00 sec) mysql>1.4 查看 SQL 执行成本
开启 profiling
mysql> show variables like 'profiling'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | profiling | OFF | +---------------+-------+ 1 row in set (0.05 sec) mysql> set profiling = 'ON'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> SELECt * FROM student WHERe stuno = 3455655; +---------+---------+--------+------+---------+ | id | stuno | name | age | classId | +---------+---------+--------+------+---------+ | 3355654 | 3455655 | uWAJVB | 89 | 777 | +---------+---------+--------+------+---------+ 1 row in set (1.97 sec) mysql>
使用 profiling
mysql> SELECt * FROM student WHERe stuno = 3455655; ... 1 row in set (1.97 sec) mysql> SELECt * FROM student WHERe name = 'JsJtPT'; ... 8 rows in set (0.00 sec) mysql> show profiles; +----------+------------+---------------------------------------------+ | Query_ID | Duration | Query | +----------+------------+---------------------------------------------+ | 1 | 1.98446300 | SELECt * FROM student WHERe stuno = 3455655 | | 2 | 0.00596225 | SELECt * FROM student WHERe name = 'JsJtPT' | +----------+------------+---------------------------------------------+ 2 rows in set, 1 warning (0.00 sec) # 默认展示最近的一条记录，即 ‘Query_ID = 2’ mysql> show profile; +--------------------------------+----------+ | Status | Duration | +--------------------------------+----------+ | starting | 0.000091 | | Executing hook on transaction | 0.000006 | | starting | 0.000009 | | checking permissions | 0.000007 | | Opening tables | 0.000053 | | init | 0.000006 | | System lock | 0.000010 | | optimizing | 0.000011 | | statistics | 0.001088 | | preparing | 0.000023 | | executing | 0.004580 | | end | 0.000015 | | query end | 0.000005 | | waiting for handler commit | 0.000013 | | closing tables | 0.000011 | | freeing items | 0.000022 | | cleaning up | 0.000015 | +--------------------------------+----------+ 17 rows in set, 1 warning (0.00 sec) # 定制化查询某条记录的某些指标 mysql> show profile cpu,block io for query 1; +--------------------------------+----------+----------+------------+--------------+---------------+ | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | +--------------------------------+----------+----------+------------+--------------+---------------+ | starting | 0.000096 | 0.000068 | 0.000019 | 0 | 0 | | Executing hook on transaction | 0.000016 | 0.000007 | 0.000002 | 0 | 0 | | starting | 0.000008 | 0.000006 | 0.000002 | 0 | 0 | | checking permissions | 0.000007 | 0.000005 | 0.000001 | 0 | 0 | | Opening tables | 0.000040 | 0.000031 | 0.000009 | 0 | 0 | | init | 0.000007 | 0.000006 | 0.000002 | 0 | 0 | | System lock | 0.000021 | 0.000011 | 0.000003 | 0 | 0 | | optimizing | 0.000012 | 0.000010 | 0.000002 | 0 | 0 | | statistics | 0.000020 | 0.000015 | 0.000005 | 0 | 0 | | preparing | 0.000020 | 0.000016 | 0.000004 | 0 | 0 | | executing | 1.963674 | 1.342543 | 0.115670 | 316896 | 0 | | end | 0.000023 | 0.000016 | 0.000004 | 0 | 0 | | query end | 0.000007 | 0.000005 | 0.000002 | 0 | 0 | | waiting for handler commit | 0.000013 | 0.000011 | 0.000003 | 0 | 0 | | closing tables | 0.000012 | 0.000009 | 0.000002 | 0 | 0 | | freeing items | 0.000132 | 0.000024 | 0.000007 | 0 | 0 | | logging slow query | 0.020331 | 0.000175 | 0.000049 | 416 | 8 | | cleaning up | 0.000026 | 0.000020 | 0.000006 | 0 | 0 | +--------------------------------+----------+----------+------------+--------------+---------------+ 18 rows in set, 1 warning (0.00 sec) mysql>
常用查询参数
| 选项 | 说明 |
|---|---|
| ALL | 显示所有的开销信息 |
| BLOCK IO | 显示块IO开销 |
| ConTEXT SWITCHES | 上下文切换开销 |
| CPU | 显示CPU开销信息 |
| IPC | 显示发送和接收开销信息 |
| MEMORY | 显示内存开销信息 |
| PAGE FAULTS | 显示页面错误开销信息 |
| SOURCE | 显示和 Source_function, Source_file, Source_line 相关的开销信息 |
| SWAPS | 显示交换次数开销信息 |
2.1 相关变量在生产环境中，如果要手工从慢日志中查找、分析 SQL 显然是个体力活，MySQL 提供了慢查询日志分析工具 mysqldumpslow。
启开状态
mysql> SHOW VARIABLES LIKE 'slow_query_log%'; +---------------------+--------------------------------------+ | Variable_name | Value | +---------------------+--------------------------------------+ | slow_query_log | OFF | | slow_query_log_file | /var/lib/mysql/5157698acdb3-slow.log | +---------------------+--------------------------------------+ 2 rows in set (0.00 sec) mysql>
记录阈值
mysql> SHOW VARIABLES LIKE '%long_query_time%'; +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | long_query_time | 10.000000 | +-----------------+-----------+ 1 row in set (0.00 sec) mysql>
慢查询数目
mysql> SHOW GLOBAL STATUS LIKE '%Slow_queries%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Slow_queries | 0 | +---------------+-------+ 1 row in set (0.01 sec) mysql>2.2 案例准备
- 建表
CREATE TABLE `student` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `stuno` INT NOT NULL , `name` VARCHAR(20) DEFAULT NULL, `age` INT(3) DEFAULT NULL, `classId` INT(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
- 设置参数 log_bin_trust_function_creators
# 创建函数，假如报错： This function has none of DETERMINISTIC...... # 命令开启：允许创建函数设置： set global log_bin_trust_function_creators=1;
- 创建函数
# 函数1：创建随机产生字符串函数
DELIMITER //
CREATE FUNCTION rand_string(n INT)
RETURNS VARCHAR(255) #该函数会返回一个字符串
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
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 //
DELIMITER ;
#函数2：创建随机数函数
DELIMITER //
CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(from_num + RAND() * (to_num - from_num + 1)) ;
RETURN i;
END //
DELIMITER ;
- 创建存储过程
DELIMITER //
CREATE PROCEDURE insert_stu1( START INT , max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0; #设置手动提交事务
REPEAT #循环
SET i = i + 1; #赋值
INSERT INTO student (stuno, NAME ,age ,classId )
VALUES ((START+i), rand_string(6), rand_num(10,100), rand_num(10,1000));
UNTIL i = max_num
END REPEAT;
COMMIT; #提交事务
END //
DELIMITER ;
- 调用存储过程
#调用刚刚写好的函数, 4000000条记录,从100001号开始 CALL insert_stu1(100001,4000000);2.3 测试
- 打开慢日志记录并调低慢查询的记录阈值
mysql> SET GLOBAL slow_query_log=on; Query OK, 0 rows affected (0.32 sec) mysql> set global long_query_time = 1; Query OK, 0 rows affected (0.00 sec) mysql>
- 测试
mysql> SELECt * FROM student WHERe stuno = 3455655; +---------+---------+--------+------+---------+ | id | stuno | name | age | classId | +---------+---------+--------+------+---------+ | 3355654 | 3455655 | uWAJVB | 89 | 777 | +---------+---------+--------+------+---------+ 1 row in set (1.90 sec) mysql> SELECt * FROM student WHERe name = 'JsJtPT'; +---------+---------+--------+------+---------+ | id | stuno | name | age | classId | +---------+---------+--------+------+---------+ | 242278 | 342279 | JsJtPT | 100 | 352 | | 611433 | 711434 | jSJTpT | 51 | 683 | | 1000005 | 1100006 | JsJtPT | 99 | 279 | | 1612477 | 1712478 | jsjTPt | 57 | 474 | | 2240808 | 2340809 | JSjtpt | 98 | 729 | | 2970233 | 3070234 | JSjtpt | 97 | 709 | | 2992184 | 3092185 | JSjtpt | 98 | 747 | | 3679784 | 3779785 | jSJTpT | 52 | 707 | +---------+---------+--------+------+---------+ 8 rows in set (1.68 sec) mysql>
- 记录
mysql> show status like 'slow_queries'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Slow_queries | 2 | +---------------+-------+ 1 row in set (0.12 sec) mysql>2.4 工具使用
工具简介
root@5157698acdb3:/# mysqldumpslow --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
Parse and summarize the MySQL slow query log. Options are
--verbose verbose
--debug debug
--help write this text to standard output
-v verbose
-d debug
-s ORDER what to sort by (al, at, ar, c, l, r, t), 'at' is default # 表示按照何种方式排序
al: average lock time # 平均锁定时间
ar: average rows sent # 平均返回记录
at: average query time # 平均查询时间 （默认方式）
c: count # 访问次数
l: lock time # 锁定时间
r: rows sent # 返回记录
t: query time # 查询时间
-r reverse the sort order (largest last instead of first)
-t NUM just show the top n queries # 即为返回前面多少条的数据
-a don't abstract all numbers to N and strings to 'S' # 不将数字抽象成N，字符串抽象成S
-n NUM abstract numbers with at least n digits within names
-g PATTERN grep: only consider stmts that include this string # 后边搭配一个正则匹配模式，大小写不敏感的
-h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard),
default is '*', i.e. match all
-i NAME name of server instance (if using mysql.server startup script)
-l don't subtract lock time from total time
root@5157698acdb3:/#
按照查询时间排序，查看前五条 SQL 语句
root@5157698acdb3:/# mysqldumpslow -s t -t 5 -a /var/lib/mysql/5157698acdb3-slow.log Reading mysql slow query log from /var/lib/mysql/5157698acdb3-slow.log Count: 1 Time=1.67s (1s) Lock=0.00s (0s) Rows=8.0 (8), root[root]@localhost SELECt * FROM student WHERe name = 'JsJtPT' Count: 1 Time=1.53s (1s) Lock=0.00s (0s) Rows=1.0 (1), root[root]@localhost SELECt * FROM student WHERe stuno = 3455655 Died at /usr/bin/mysqldumpslow line 162, <> chunk 2. root@5157698acdb3:/#
mysql> ALTER TABLE student ADD INDEX(name); Query OK, 0 rows affected (21.23 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SELECt * FROM student WHERe name = 'JsJtPT'; +---------+---------+--------+------+---------+ | id | stuno | name | age | classId | +---------+---------+--------+------+---------+ | 242278 | 342279 | JsJtPT | 100 | 352 | | 611433 | 711434 | jSJTpT | 51 | 683 | | 1000005 | 1100006 | JsJtPT | 99 | 279 | | 1612477 | 1712478 | jsjTPt | 57 | 474 | | 2240808 | 2340809 | JSjtpt | 98 | 729 | | 2970233 | 3070234 | JSjtpt | 97 | 709 | | 2992184 | 3092185 | JSjtpt | 98 | 747 | | 3679784 | 3779785 | jSJTpT | 52 | 707 | +---------+---------+--------+------+---------+ 8 rows in set (0.02 sec) mysql>
其它常用分析语句
# 得到返回记录集最多的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条里面含有左连接的查询语句 mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log # 另外建议在使用这些命令时结合 | 和 less 使用 ，否则有可能出现爆屏情况 mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | less三、trace 分析优化器执行计划 3.1 相关参数
查询
mysql> select @@optimizer_trace; +--------------------------+ | @@optimizer_trace | +--------------------------+ | enabled=off,one_line=off | +--------------------------+ 1 row in set (0.00 sec) mysql> select @@end_markers_in_json; +-----------------------+ | @@end_markers_in_json | +-----------------------+ | 0 | +-----------------------+ 1 row in set (0.00 sec) # 最大能够使用的内存大小 mysql> select @@optimizer_trace_max_mem_size; +--------------------------------+ | @@optimizer_trace_max_mem_size | +--------------------------------+ | 1048576 | +--------------------------------+ 1 row in set (0.00 sec) mysql>
开启
mysql> SET optimizer_trace="enabled=on",end_markers_in_json=on; Query OK, 0 rows affected (0.00 sec) mysql> set optimizer_trace_max_mem_size=1000000; Query OK, 0 rows affected (0.00 sec) mysql>3.2 使用分析
mysql> select * from student where id < 10;
+----+--------+--------+------+---------+
| id | stuno | name | age | classId |
+----+--------+--------+------+---------+
| 1 | 100002 | ACnHyt | 51 | 170 |
| 2 | 100003 | xNvPNu | 70 | 166 |
| 3 | 100004 | PBlBXi | 10 | 547 |
| 4 | 100005 | JOSWhT | 94 | 45 |
| 5 | 100006 | uSfZFc | 47 | 915 |
| 6 | 100007 | rYRpXS | 48 | 551 |
| 7 | 100008 | yKesCJ | 52 | 466 |
| 8 | 100009 | Vjkvxc | 91 | 332 |
| 9 | 100010 | WLPOBp | 94 | 726 |
+----+--------+--------+------+---------+
9 rows in set (0.00 sec)
mysql> select * from information_schema.optimizer_traceG
*************************** 1. row ***************************
// 第1部分：查询语句
QUERY: select * from student where id < 10
// 第2部分：QUERY字段对应语句的跟踪信息
TRACE: {
"steps": [
{
"join_preparation": { // 预备工作
"select#": 1,
"steps": [
{
"expanded_query": " select `student`.`id` AS `id`,`student`.`stuno` AS `stuno`,`student`.`name` AS `name`,`student`.`age` AS `age`,`student`.`classId` AS `classId` from `student` where (`student`.`id` < 10)"
}
]
}
},
{
"join_optimization": { // 进行优化
"select#": 1,
"steps": [
{
"condition_processing": { // 条件处理
"condition": "WHERe",
"original_condition": "(`student`.`id` < 10)",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(`student`.`id` < 10)"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(`student`.`id` < 10)"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "(`student`.`id` < 10)"
}
]
}
},
{
"substitute_generated_columns": { // 替换生成的列
}
},
{
"table_dependencies": [ // 表的依赖关系
{
"table": "`student`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
]
}
]
},
{
"ref_optimizer_key_uses": [ // 使用键
]
},
{
"rows_estimation": [ // 行判断
{
"table": "`student`",
"range_analysis": {
"table_scan": {
"rows": 3990175,
"cost": 406233
} , // 扫描表
"potential_range_indexes": [ // 潜在的范围索引
{
"index": "PRIMARY",
"usable": true,
"key_parts": [
"id"
]
},
{
"index": "name",
"usable": true,
"key_parts": [
"name",
"id"
]
}
] ,
"setup_range_conditions": [
] , // 设置范围条件
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
} ,
"skip_scan_range": {
"potential_skip_scan_indexes": [
{
"index": "PRIMARY",
"usable": false,
"cause": "query_references_nonkey_column"
},
{
"index": "name",
"usable": false,
"cause": "query_references_nonkey_column"
}
]
} ,
"analyzing_range_alternatives": { // 分析范围选项
"range_scan_alternatives": [
{
"index": "PRIMARY",
"ranges": [
"id < 10"
] ,
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"in_memory": 0.386281,
"rows": 9,
"cost": 1.62734,
"chosen": true
},
{
"index": "name",
"chosen": false,
"cause": "no_valid_range_for_this_index"
}
] ,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
}
} ,
"chosen_range_access_summary": { // 选择范围访问摘要
"range_access_plan": {
"type": "range_scan",
"index": "PRIMARY",
"rows": 9,
"ranges": [
"id < 10"
]
} ,
"rows_for_plan": 9,
"cost_for_plan": 1.62734,
"chosen": true
}
}
}
]
},
{
"considered_execution_plans": [ // 考虑执行计划
{
"plan_prefix": [
] ,
"table": "`student`",
"best_access_path": { // 最佳访问路径
"considered_access_paths": [
{
"rows_to_scan": 9,
"access_type": "range",
"range_details": {
"used_index": "PRIMARY"
} ,
"resulting_rows": 9,
"cost": 2.52734,
"chosen": true
}
]
} ,
"condition_filtering_pct": 100, // 行过滤百分比
"rows_for_plan": 9,
"cost_for_plan": 2.52734,
"chosen": true
}
]
},
{
"attaching_conditions_to_tables": { // 将条件附加到表上
"original_condition": "(`student`.`id` < 10)",
"attached_conditions_computation": [
] ,
"attached_conditions_summary": [ // 附加条件概要
{
"table": "`student`",
"attached": "(`student`.`id` < 10)"
}
]
}
},
{
"finalizing_table_conditions": [
{
"table": "`student`",
"original_table_condition": "(`student`.`id` < 10)",
"final_table_condition ": "(`student`.`id` < 10)"
}
]
},
{
"refine_plan": [ // 精简计划
{
"table": "`student`"
}
]
}
]
}
},
{
"join_execution": { // 执行
"select#": 1,
"steps": [
]
}
}
]
}
// 第3部分：跟踪信息过长时，被截断的跟踪信息的字节数。
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0 // 丢失的超出最大容量的字节
// 第4部分：执行跟踪语句的用户是否有查看对象的权限。当不具有权限时，该列信息为1且TRACE字段为空，一般在
// 调用带有SQL SECURITY DEFINER的视图或者是存储过程的情况下，会出现此问题。
INSUFFICIENT_PRIVILEGES: 0 //缺失权限
1 row in set (0.03 sec)
mysql>
四、sys.schema 监控分析视图
4.1 视图摘要
| 视图 | 说明 |
|---|---|
| 主机相关 | 以host_summary开头，主要汇总了IO延迟的信息。 |
| Innodb相关 | 以innodb开头，汇总了innodb buffer信息和事务等待innodb锁的信息。 |
| I/o相关 | 以io开头，汇总了等待I/O、I/O使用量情况。 |
| 内存使用情况 | 以memory开头，从主机、线程、事件等角度展示内存的使用情况 |
| 连接与会话信息 | processlist和session相关视图，总结了会话相关信息。 |
| 表相关 | 以schema_table开头的视图，展示了表的统计信息。 |
| 索引信息 | 统计了索引的使用情况，包含冗余索引和未使用的索引情况。 |
| 语句相关 | 以statement开头，包含执行全表扫描、使用临时表、排序等的语句信息。 |
| 用户相关 | 以user开头的视图，统计了用户使用的文件I/O、执行语句统计信息。 |
| 等待事件相关信息 | 以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' ;4.2.2 表相关
# 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';4.2.3 语句相关
#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;4.2.4 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;4.2.5 Innodb 相关
# 1. 行锁阻塞情况 select * from sys.innodb_lock_waits;
