本篇内容主要讲解“如何查看mysql的运行状态”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“如何查看mysql的运行状态”吧!
通过performance_schema.events_statements_history表，查看数据库最近执行的一些sql语句：
计算
安全
数据库
网络和加速
企业服务
(root@localhost)[(none)]> SELECT thread_id
,event_name
,source
,sys.format_time(timer_wait)
,sys.format_time(lock_time)
,sql_text
,current_schema
,message_text
,rows_affected
,rows_sent
,rows_examined
FROM performance_schema.events_statements_history
WHERE current_schema != 'performance_schema'
ORDER BY timer_wait DESC limit 10 \G
*************************** 1. row ***************************
thread_id: 561166
event_name: statement/sql/select
source: socket_connection.cc:101
sys.format_time(timer_wait): 53.64 ms
sys.format_time(lock_time): 429.00 us
sql_text: select * from oa_v_position_list
current_schema: oa_2016
message_text: NULL
rows_affected: 0
rows_sent: 4
rows_examined: 18051
*************************** 2. row ***************************
thread_id: 153896
event_name: statement/sql/select
source: socket_connection.cc:101
sys.format_time(timer_wait): 51.76 ms
sys.format_time(lock_time): 96.00 us
sql_text: select count(id) as num from formmain_2477 where (field0003 = 'SJCL-201911008' and ifnull(field0003, '0') != '0')
current_schema: oa_2016
message_text: NULL
rows_affected: 0
rows_sent: 1
rows_examined: 222
*************************** 3. row ***************************
....我们可以使用performance_schema.events_statements_summary_by_digest表查询经过统计之后的top sql语句：
(root@localhost)[(none)]> SELECT schema_name
,digest_text
,count_star
,sys.format_time(sum_timer_wait) AS sum_time
,sys.format_time(min_timer_wait) AS min_time
,sys.format_time(avg_timer_wait) AS avg_time
,sys.format_time(max_timer_wait) AS min_time
,sys.format_time(sum_lock_time) AS sum_lock_time
,sum_rows_affected
,sum_rows_sent
,sum_rows_examined
FROM performance_schema.events_statements_summary_by_digest
WHERE schema_name IS NOT NULL
ORDER BY count_star DESC limit 10 \G
*************************** 1. row ***************************
schema_name: oa_2016
digest_text: SET `autocommit` = ?
count_star: 1604399319
sum_time: 13.57 h
min_time: 2.00 ns
avg_time: 30.46 us
min_time: 39.87 s
sum_lock_time: 0 ps
sum_rows_affected: 0
sum_rows_sent: 0
sum_rows_examined: 0
*************************** 2. row ***************************
schema_name: oa_2016
digest_text: COMMIT
count_star: 368723348
sum_time: 4.15 h
min_time: 10.07 us
avg_time: 40.52 us
min_time: 21.54 s
sum_lock_time: 40.27 s
sum_rows_affected: 0
sum_rows_sent: 0
sum_rows_examined: 0
*************************** 3. row ***************************
schema_name: oa_2016
digest_text: SELECT * FROM `jk_JOB_DETAILS` WHERE `SCHED_NAME` = ? AND `JOB_NAME` = ? AND `JOB_GROUP` = ?
count_star: 361183117
sum_time: 19.88 h
min_time: 5.00 ns
avg_time: 198.10 us
min_time: 4.97 s
sum_lock_time: 4.93 h
sum_rows_affected: 0
sum_rows_sent: 361182289
sum_rows_examined: 361182289
......提示：performance_schema.events_statements_summary_by_digest记录的sql并不完整，默认情况下只截取了1024字节，所以该表提供的数据只能算作慢日志分析的一个补充。如果需要完整sql文本，还得依赖慢日志分析。
mysql> SELECT thread_id
,event_name
,source
,sys.format_time(timer_wait)
,sys.format_time(lock_time)
,sql_text
,current_schema
,message_text
,rows_affected
,rows_sent
,rows_examined
FROM performance_schema.events_statements_history
WHERE errors>0 \G
*************************** 1. row ***************************
thread_id: 6172541
event_name: statement/sql/select
source: socket_connection.cc:101
sys.format_time(timer_wait): 135.89 us
sys.format_time(lock_time): 0 ps
sql_text: SELECT id,waybill_num,oms_order_status FROM store_order
WHERE is_sync_css_status_end IS NULL OR is_sync_css_status_end!='01' ORDER BY create_time ASC, css_search_time ASC LIMIT 0,200
current_schema: oms
message_text: Table 'oms.store_order' doesn't exist
rows_affected: 0
rows_sent: 0
rows_examined: 0
1 row in set (0.02 sec)# 查看MDL锁等待事件的instrments（采集器）是否开启
mysql> SELECT *
FROM performance_schema.setup_instruments
WHERE name LIKE '%metadata/sql/mdl%';
+----------------------------+---------+-------+
| NAME | ENABLED | TIMED |
+----------------------------+---------+-------+
| wait/lock/metadata/sql/mdl | NO | NO |
+----------------------------+---------+-------+
1 row in set (0.00 sec)
# 启用与MDL锁等待事件相关的instruments（采集器）
mysql> update performance_schema.setup_instruments set ENABLED='YES' where name like '%metadata/sql/mdl%';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update performance_schema.setup_instruments set TIMED='YES' where name like '%metadata/sql/mdl%';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from performance_schema.setup_instruments where name like '%metadata/sql/mdl%';
+----------------------------+---------+-------+
| NAME | ENABLED | TIMED |
+----------------------------+---------+-------+
| wait/lock/metadata/sql/mdl | YES | YES |
+----------------------------+---------+-------+
1 row in set (0.01 sec)
# 然后使用sys.schema_table_lock_wait视图进行查询（注意：请自行模拟一个会话事务不提交，
另外一个会话发生DDL的操作，就可以查看到MDL锁等待的内容)。
mysql> select * from sys.schema_table_lock_waits \Gmysql> select * from sys.innodb_buffer_stats_by_table order by allocated desc limit 10 ;
+---------------+--------------------+------------+------------+-------+--------------+-----------+-------------+
| object_schema | object_name | allocated | data | pages | pages_hashed | pages_old | rows_cached |
+---------------+--------------------+------------+------------+-------+--------------+-----------+-------------+
| mysql | help_keyword | 96.00 KiB | 43.99 KiB | 6 | 6 | 6 | 283 |
| InnoDB System | SYS_COLUMNS | 80.00 KiB | 44.50 KiB | 5 | 5 | 5 | 696 |
| mdm | employee_jz | 64.00 KiB | 19.77 KiB | 4 | 4 | 4 | 177 |
| mysql | innodb_index_stats | 64.00 KiB | 28.20 KiB | 4 | 4 | 4 | 288 |mysql> SELECT *
FROM information_schema.key_column_usage
WHERE constraint_schema = 'oms'
AND referenced_table_schema IS NOT NULL \G;
*************************** 1. row ***************************
CONSTRAINT_CATALOG: def
CONSTRAINT_SCHEMA: oms
CONSTRAINT_NAME: qrtz_blob_triggers_ibfk_1
TABLE_CATALOG: def
TABLE_SCHEMA: oms
TABLE_NAME: qrtz_blob_triggers
COLUMN_NAME: SCHED_NAME
ORDINAL_POSITION: 1
POSITION_IN_UNIQUE_CONSTRAINT: 1
REFERENCED_TABLE_SCHEMA: oms
REFERENCED_TABLE_NAME: qrtz_triggers
REFERENCED_COLUMN_NAME: SCHED_NAME
*************************** 2. row ***************************
CONSTRAINT_CATALOG: def
CONSTRAINT_SCHEMA: oms 通常在开发规范中禁止使用外键。
mysql> SELECT TABLE_SCHEMA
,TABLE_NAME
,INDEX_NAME
,COLUMN_NAME
,CARDINALITY
FROM information_schema.STATISTICS
GROUP BY TABLE_SCHEMA
,TABLE_NAME limit 100;
+--------------+---------------------------+-------------------------+------------------+-------------+
| TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | COLUMN_NAME | CARDINALITY |
+--------------+---------------------------+-------------------------+------------------+-------------+
| mdm | department | index_unique_department | deptid | 381 |
| mdm | employee | PRIMARY | sn | 1544 |
| mdm | employee_jz | index_unique_employee | sn | 1626 |
.......mysql> select * from information_schema.partitions where partition_name is not null;
Empty set (0.02 sec)# 将表和索引的统计信息数据存储到磁盘中，默认是开启的
(root@localhost)[mysql]> show variables like 'innodb_stats_persistent';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| innodb_stats_persistent | ON |
+-------------------------+-------+
1 row in set (0.00 sec)
# 持久化信息被存储在mysql数据库的如下两张表中：
(root@localhost)[mysql]> show tables from mysql like '%stats%';
+---------------------------+
| Tables_in_mysql (%stats%) |
+---------------------------+
| innodb_index_stats |
| innodb_table_stats |
+---------------------------+
2 rows in set (0.00 sec)
# innodb_stats_auto_recalc变量控制是否启用统计信息的自动重新计算功能，默认是开启的。如果启用，当表中的数据量超过10%时会
触发统计信息自动重新计算功能
(root@localhost)[mysql]> show variables like 'innodb_stats_auto_recalc'
-> ;
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_stats_auto_recalc | ON |
+--------------------------+-------+
1 row in set (0.00 sec)到此，相信大家对“如何查看mysql的运行状态”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！
亿速云「云数据库 MySQL」免部署即开即用，比自行安装部署数据库高出1倍以上的性能，双节点冗余防止单节点故障，数据自动定期备份随时恢复。点击查看>>
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
原文链接：http://blog.itpub.net/28916011/viewspace-2663649/
