如何查看mysql的运行状态

发布时间：2021-09-01 20:27:59 作者：chen
来源：亿速云阅读：405

本篇内容主要讲解“如何查看mysql的运行状态”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“如何查看mysql的运行状态”吧!

查看最近的top sql

通过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文本，还得依赖慢日志分析。

查看最近失败的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锁在等什么

# 查看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 \G

查看innodb_buffer_pool中热点数据有哪些

mysql> 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)

mysql系统库之统计信息表

# 将表和索引的统计信息数据存储到磁盘中，默认是开启的
(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的运行状态”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！