ITPub博客

首页 > 数据库 > MySQL > 查看mysql运行状态的一些sql

查看mysql运行状态的一些sql

原创 MySQL 作者:czxin788 时间:2019-12-03 14:32:49 0 删除 编辑

    声明:本文内容是笔者阅读《千金良方 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)





来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28916011/viewspace-2663649/,如需转载,请注明出处,否则将追究法律责任。

全部评论

注册时间:2014-06-03

  • 博文量
    201
  • 访问量
    640986