如何查看mysql的运行状态

本篇内容主要讲解"如何查看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:101sys.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:101sys.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_examinedFROM performance_schema.events_statements_summary_by_digestWHERE schema_name IS NOT NULLORDER 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 pssum_rows_affected: 0    sum_rows_sent: 0sum_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 ssum_rows_affected: 0    sum_rows_sent: 0sum_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 hsum_rows_affected: 0    sum_rows_sent: 361182289sum_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:101sys.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: 01 row in set (0.02 sec)

查看是MDL锁在等什么

# 查看MDL锁等待事件的instrments（采集器）是否开启mysql> SELECT *FROM performance_schema.setup_instrumentsWHERE 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: 0mysql> 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: 0mysql> 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_usageWHERE 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: 1POSITION_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        ,CARDINALITYFROM information_schema.STATISTICSGROUP 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的运行状态"有了更深的了解，不妨来实际操作一番吧！这里是网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！