文章目录
一、基础知识
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 | 删除操作的次数 |
1.3 统计 SQL 查询成本
- 它是作为
比较各个查询之间的开销的一个依据。 - 它只能检测比较简单的查询开销，对于包含子查询和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 | 显示交换次数开销信息 |
二、mysqldumpslow 慢日志分析
在生产环境中，如果要手工从慢日志中查找、分析 SQL 显然是个体力活，MySQL 提供了
慢查询日志分析工具 mysqldumpslow。
2.1 相关变量
- 启开状态
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_trace\G
*************************** 1. row ***************************
// 第1部分：查询语句
QUERY: select * from student where id < 10
// 第2部分：QUERY字段对应语句的跟踪信息
TRACE: {
"steps": [
{
"join_preparation": { // 预备工作
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ 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)"
}
] /* steps */
} /* join_preparation */
},
{
"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)"
}
] /* steps */
} /* condition_processing */
},
{
"substitute_generated_columns": { // 替换生成的列
} /* substitute_generated_columns */
},
{
"table_dependencies": [ // 表的依赖关系
{
"table": "`student`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
] /* depends_on_map_bits */
}
] /* table_dependencies */
},
{
"ref_optimizer_key_uses": [ // 使用键
] /* ref_optimizer_key_uses */
},
{
"rows_estimation": [ // 行判断
{
"table": "`student`",
"range_analysis": {
"table_scan": {
"rows": 3990175,
"cost": 406233
} /* table_scan */, // 扫描表
"potential_range_indexes": [ // 潜在的范围索引
{
"index": "PRIMARY",
"usable": true,
"key_parts": [
"id"
] /* key_parts */
},
{
"index": "name",
"usable": true,
"key_parts": [
"name",
"id"
] /* key_parts */
}
] /* potential_range_indexes */,
"setup_range_conditions": [
] /* setup_range_conditions */, // 设置范围条件
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
} /* group_index_range */,
"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"
}
] /* potential_skip_scan_indexes */
} /* skip_scan_range */,
"analyzing_range_alternatives": { // 分析范围选项
"range_scan_alternatives": [
{
"index": "PRIMARY",
"ranges": [
"id < 10"
] /* ranges */,
"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"
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
} /* analyzing_range_alternatives */,
"chosen_range_access_summary": { // 选择范围访问摘要
"range_access_plan": {
"type": "range_scan",
"index": "PRIMARY",
"rows": 9,
"ranges": [
"id < 10"
] /* ranges */
} /* range_access_plan */,
"rows_for_plan": 9,
"cost_for_plan": 1.62734,
"chosen": true
} /* chosen_range_access_summary */
} /* range_analysis */
}
] /* rows_estimation */
},
{
"considered_execution_plans": [ // 考虑执行计划
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`student`",
"best_access_path": { // 最佳访问路径
"considered_access_paths": [
{
"rows_to_scan": 9,
"access_type": "range",
"range_details": {
"used_index": "PRIMARY"
} /* range_details */,
"resulting_rows": 9,
"cost": 2.52734,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100, // 行过滤百分比
"rows_for_plan": 9,
"cost_for_plan": 2.52734,
"chosen": true
}
] /* considered_execution_plans */
},
{
"attaching_conditions_to_tables": { // 将条件附加到表上
"original_condition": "(`student`.`id` < 10)",
"attached_conditions_computation": [
] /* attached_conditions_computation */,
"attached_conditions_summary": [ // 附加条件概要
{
"table": "`student`",
"attached": "(`student`.`id` < 10)"
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
"finalizing_table_conditions": [
{
"table": "`student`",
"original_table_condition": "(`student`.`id` < 10)",
"final_table_condition ": "(`student`.`id` < 10)"
}
] /* finalizing_table_conditions */
},
{
"refine_plan": [ // 精简计划
{
"table": "`student`"
}
] /* refine_plan */
}
] /* steps */
} /* join_optimization */
},
{
"join_execution": { // 执行
"select#": 1,
"steps": [
] /* steps */
} /* join_execution */
}
] /* 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开头，展示等待事件的延迟情况。 |
4.2 视图使用场景
4.2.1 索引情况
#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;
1503
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
