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

通过performance_schema.events_statements_history表，查看数据库最近执行的一些sql语句：

(root@localhost)[(none)]>SELECTthread_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_examinedFROMperformance_schema.events_statements_historyWHEREcurrent_schema!='performance_schema'ORDERBYtimer_waitDESClimit10\G***************************1.row***************************thread_id:561166event_name:statement/sql/selectsource:socket_connection.cc:101sys.format_time(timer_wait):53.64mssys.format_time(lock_time):429.00ussql_text:select*fromoa_v_position_listcurrent_schema:oa_2016message_text:NULLrows_affected:0rows_sent:4rows_examined:18051***************************2.row***************************thread_id:153896event_name:statement/sql/selectsource:socket_connection.cc:101sys.format_time(timer_wait):51.76mssys.format_time(lock_time):96.00ussql_text:selectcount(id)asnumfromformmain_2477where(field0003='SJCL-201911008'andifnull(field0003,'0')!='0')current_schema:oa_2016message_text:NULLrows_affected:0rows_sent:1rows_examined:222***************************3.row***************************....

我们可以使用performance_schema.events_statements_summary_by_digest表查询经过统计之后的top sql语句：

(root@localhost)[(none)]>SELECTschema_name,digest_text,count_star,sys.format_time(sum_timer_wait)ASsum_time,sys.format_time(min_timer_wait)ASmin_time,sys.format_time(avg_timer_wait)ASavg_time,sys.format_time(max_timer_wait)ASmin_time,sys.format_time(sum_lock_time)ASsum_lock_time,sum_rows_affected,sum_rows_sent,sum_rows_examinedFROMperformance_schema.events_statements_summary_by_digestWHEREschema_nameISNOTNULLORDERBYcount_starDESClimit10\G***************************1.row***************************schema_name:oa_2016digest_text:SET`autocommit`=?count_star:1604399319sum_time:13.57hmin_time:2.00nsavg_time:30.46usmin_time:39.87ssum_lock_time:0pssum_rows_affected:0sum_rows_sent:0sum_rows_examined:0***************************2.row***************************schema_name:oa_2016digest_text:COMMITcount_star:368723348sum_time:4.15hmin_time:10.07usavg_time:40.52usmin_time:21.54ssum_lock_time:40.27ssum_rows_affected:0sum_rows_sent:0sum_rows_examined:0***************************3.row***************************schema_name:oa_2016digest_text:SELECT*FROM`jk_JOB_DETAILS`WHERE`SCHED_NAME`=?AND`JOB_NAME`=?AND`JOB_GROUP`=?count_star:361183117sum_time:19.88hmin_time:5.00nsavg_time:198.10usmin_time:4.97ssum_lock_time:4.93hsum_rows_affected:0sum_rows_sent:361182289sum_rows_examined:361182289......

提示：performance_schema.events_statements_summary_by_digest记录的sql并不完整，默认情况下只截取了1024字节，所以该表提供的数据只能算作慢日志分析的一个补充。如果需要完整sql文本，还得依赖慢日志分析。

mysql>SELECTthread_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_examinedFROMperformance_schema.events_statements_historyWHEREerrors>0\G***************************1.row***************************thread_id:6172541event_name:statement/sql/selectsource:socket_connection.cc:101sys.format_time(timer_wait):135.89ussys.format_time(lock_time):0pssql_text:SELECTid,waybill_num,oms_order_statusFROMstore_orderWHEREis_sync_css_status_endISNULLORis_sync_css_status_end!='01'ORDERBYcreate_timeASC,css_search_timeASCLIMIT0,200current_schema:omsmessage_text:Table'oms.store_order'doesn'texistrows_affected:0rows_sent:0rows_examined:01rowinset(0.02sec)查看是MDL锁在等什么

#查看MDL锁等待事件的instrments（采集器）是否开启mysql>SELECT*FROMperformance_schema.setup_instrumentsWHEREnameLIKE'%metadata/sql/mdl%';+----------------------------+---------+-------+|NAME|ENABLED|TIMED|+----------------------------+---------+-------+|wait/lock/metadata/sql/mdl|NO|NO|+----------------------------+---------+-------+1rowinset(0.00sec)#启用与MDL锁等待事件相关的instruments（采集器）mysql>updateperformance_schema.setup_instrumentssetENABLED='YES'wherenamelike'%metadata/sql/mdl%';QueryOK,1rowaffected(0.00sec)Rowsmatched:1Changed:1Warnings:0mysql>updateperformance_schema.setup_instrumentssetTIMED='YES'wherenamelike'%metadata/sql/mdl%';QueryOK,1rowaffected(0.00sec)Rowsmatched:1Changed:1Warnings:0mysql>select*fromperformance_schema.setup_instrumentswherenamelike'%metadata/sql/mdl%';+----------------------------+---------+-------+|NAME|ENABLED|TIMED|+----------------------------+---------+-------+|wait/lock/metadata/sql/mdl|YES|YES|+----------------------------+---------+-------+1rowinset(0.01sec)#然后使用sys.schema_table_lock_wait视图进行查询（注意：请自行模拟一个会话事务不提交，另外一个会话发生DDL的操作，就可以查看到MDL锁等待的内容)。mysql>select*fromsys.schema_table_lock_waits\G查看innodb_buffer_pool中热点数据有哪些

mysql>select*fromsys.innodb_buffer_stats_by_tableorderbyallocateddesclimit10;+---------------+--------------------+------------+------------+-------+--------------+-----------+-------------+|object_schema|object_name|allocated|data|pages|pages_hashed|pages_old|rows_cached|+---------------+--------------------+------------+------------+-------+--------------+-----------+-------------+|mysql|help_keyword|96.00KiB|43.99KiB|6|6|6|283||InnoDBSystem|SYS_COLUMNS|80.00KiB|44.50KiB|5|5|5|696||mdm|employee_jz|64.00KiB|19.77KiB|4|4|4|177||mysql|innodb_index_stats|64.00KiB|28.20KiB|4|4|4|288|查看数据库中是否有表使用了外键

mysql>SELECT*FROMinformation_schema.key_column_usageWHEREconstraint_schema='oms'ANDreferenced_table_schemaISNOTNULL\G;***************************1.row***************************CONSTRAINT_CATALOG:defCONSTRAINT_SCHEMA:omsCONSTRAINT_NAME:qrtz_blob_triggers_ibfk_1TABLE_CATALOG:defTABLE_SCHEMA:omsTABLE_NAME:qrtz_blob_triggersCOLUMN_NAME:SCHED_NAMEORDINAL_POSITION:1POSITION_IN_UNIQUE_CONSTRAINT:1REFERENCED_TABLE_SCHEMA:omsREFERENCED_TABLE_NAME:qrtz_triggersREFERENCED_COLUMN_NAME:SCHED_NAME***************************2.row***************************CONSTRAINT_CATALOG:defCONSTRAINT_SCHEMA:oms

通常在开发规范中禁止使用外键。

mysql>SELECTTABLE_SCHEMA,TABLE_NAME,INDEX_NAME,COLUMN_NAME,CARDINALITYFROMinformation_schema.STATISTICSGROUPBYTABLE_SCHEMA,TABLE_NAMElimit100;+--------------+---------------------------+-------------------------+------------------+-------------+|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*frominformation_schema.partitionswherepartition_nameisnotnull;Emptyset(0.02sec)mysql系统库之统计信息表

#将表和索引的统计信息数据存储到磁盘中，默认是开启的(root@localhost)[mysql]>showvariableslike'innodb_stats_persistent';+-------------------------+-------+|Variable_name|Value|+-------------------------+-------+|innodb_stats_persistent|ON|+-------------------------+-------+1rowinset(0.00sec)#持久化信息被存储在mysql数据库的如下两张表中：(root@localhost)[mysql]>showtablesfrommysqllike'%stats%';+---------------------------+|Tables_in_mysql(%stats%)|+---------------------------+|innodb_index_stats||innodb_table_stats|+---------------------------+2rowsinset(0.00sec)#innodb_stats_auto_recalc变量控制是否启用统计信息的自动重新计算功能，默认是开启的。如果启用，当表中的数据量超过10%时会触发统计信息自动重新计算功能(root@localhost)[mysql]>showvariableslike'innodb_stats_auto_recalc'->;+--------------------------+-------+|Variable_name|Value|+--------------------------+-------+|innodb_stats_auto_recalc|ON|+--------------------------+-------+1rowinset(0.00sec)

到此，相信大家对“如何查看mysql的运行状态”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！