第9章 sys系统库应用示例荟萃

9.1　查看慢SQL语句慢在哪里

# 首先需要启用与等待事件相关的instruments和consumers，否则last_wait字段值可能为NULL
mysql> call sys.ps_setup_enable_instrument('wait');
......
1 row in set (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

mysql> call sys.ps_setup_enable_consumer('wait');
......
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

# 然后，使用session视图进行查询（这里只查询command为query的线程信息，代表正在执行查询）
mysql> select * from session where command='query' and conn_id!=connection_id()\G
......
1 row in set (0.15 sec)

9.2　查看是否有事务锁等待

mysql> select * from innodb_lock_waits\G
......
1 row in set, 3 warnings (0.00 sec)

SELECT r.trx_wait_started AS wait_started,
      TIMEDIFF(NOW(), r.trx_wait_started) AS wait_age,
      TIMESTAMPDIFF(SECOND, r.trx_wait_started, NOW()) AS wait_age_secs,
      rl.lock_table AS locked_table,
      rl.lock_index AS locked_index,
      rl.lock_type AS locked_type,
      r.trx_id AS waiting_trx_id,
      r.trx_started as waiting_trx_started,
      TIMEDIFF(NOW(), r.trx_started) AS waiting_trx_age,
      r.trx_rows_locked AS waiting_trx_rows_locked,
      r.trx_rows_modified AS waiting_trx_rows_modified,
      r.trx_mysql_thread_id AS waiting_pid,
      sys.format_statement(r.trx_query) AS waiting_query,
      rl.lock_id AS waiting_lock_id,
      rl.lock_mode AS waiting_lock_mode,
      b.trx_id AS blocking_trx_id,
      b.trx_mysql_thread_id AS blocking_pid,
      sys.format_statement(b.trx_query) AS blocking_query,
      bl.lock_id AS blocking_lock_id,
      bl.lock_mode AS blocking_lock_mode,
      b.trx_started AS blocking_trx_started,
      TIMEDIFF(NOW(), b.trx_started) AS blocking_trx_age,
      b.trx_rows_locked AS blocking_trx_rows_locked,
      b.trx_rows_modified AS blocking_trx_rows_modified,
      CONCAT('KILL QUERY ', b.trx_mysql_thread_id) AS sql_kill_blocking_query,
      CONCAT('KILL ', b.trx_mysql_thread_id) AS sql_kill_blocking_connection
  FROM information_schema.innodb_lock_waits w
      INNER JOIN information_schema.innodb_trx b    ON b.trx_id = w.blocking_trx_id
      INNER JOIN information_schema.innodb_trx r    ON r.trx_id = w.requesting_trx_id
      INNER JOIN information_schema.innodb_locks bl ON bl.lock_id = w.blocking_lock_id
      INNER JOIN information_schema.innodb_locks rl ON rl.lock_id = w.requested_lock_id
ORDER BY r.trx_wait_started;

9.3　查看是否有MDL锁等待

# 首先需要启用与MDL锁等待事件相关的instruments
mysql> call sys.ps_setup_enable_instrument('wait/lock/ metadata/sql/mdl');
......
1 row in set (0.01 sec)

mysql> select * from schema_table_lock_waits\G
......
2 rows in set (0.00 sec)

SELECT g.object_schema AS object_schema,
      g.object_name AS object_name,
      pt.thread_id AS waiting_thread_id,
      pt.processlist_id AS waiting_pid,
      sys.ps_thread_account(p.owner_thread_id) AS waiting_account,
      p.lock_type AS waiting_lock_type,
      p.lock_duration AS waiting_lock_duration,
      sys.format_statement(pt.processlist_info) AS waiting_query,
      pt.processlist_time AS waiting_query_secs,
      ps.rows_affected AS waiting_query_rows_affected,
      ps.rows_examined AS waiting_query_rows_examined,
      gt.thread_id AS blocking_thread_id,
      gt.processlist_id AS blocking_pid,
      sys.ps_thread_account(g.owner_thread_id) AS blocking_account,
      g.lock_type AS blocking_lock_type,
      g.lock_duration AS blocking_lock_duration,
      CONCAT('KILL QUERY ', gt.processlist_id) AS sql_kill_blocking_query,
      CONCAT('KILL ', gt.processlist_id) AS sql_kill_blocking_connection
  FROM performance_schema.metadata_locks g
INNER JOIN performance_schema.metadata_locks p
    ON g.object_type = p.object_type
  AND g.object_schema = p.object_schema
  AND g.object_name = p.object_name
  AND g.lock_status = 'GRANTED'
  AND p.lock_status = 'PENDING'
INNER JOIN performance_schema.threads gt ON g.owner_thread_id = gt.thread_id
INNER JOIN performance_schema.threads pt ON p.owner_thread_id = pt.thread_id
  LEFT JOIN performance_schema.events_statements_current gs ON g.owner_thread_id = gs.thread_id
  LEFT JOIN performance_schema.events_statements_current ps ON p.owner_thread_id = ps.thread_id
WHERE g.object_type = 'TABLE';

9.4　查看InnoDB缓冲池中的热点数据有哪些

mysql> select * from innodb_buffer_stats_by_schema;
......
5 rows in set (0.43 sec)

SELECT IF(LOCATE('.', ibp.table_name) = 0, 'InnoDB System', REPLACE (SUBSTRING_INDEX(ibp. table_name, '.', 1), '`', '')) AS object_schema,
      sys.format_bytes(SUM(IF(ibp.compressed_size = 0, 16384, compressed_size))) AS allocated,
      sys.format_bytes(SUM(ibp.data_size)) AS data,
      COUNT(ibp.page_number) AS pages,
      COUNT(IF(ibp.is_hashed = 'YES', 1, NULL)) AS pages_hashed,
      COUNT(IF(ibp.is_old = 'YES', 1, NULL)) AS pages_old,
      ROUND(SUM(ibp.number_records)/COUNT(DISTINCT ibp.index_name)) AS rows_cached
  FROM information_schema.innodb_buffer_page ibp
WHERE table_name IS NOT NULL
GROUP BY object_schema
ORDER BY SUM(IF(ibp.compressed_size = 0, 16384, compressed_size)) DESC;

9.5　查看冗余索引

mysql> select * from schema_redundant_indexes limit 1\G
......
1 row in set (0.01 sec)

9.6 查看未使用的索引

mysql> select * from schema_unused_indexes limit 3;
......
3 rows in set (0.00 sec)

SELECT object_schema,
      object_name,
      index_name
  FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
  AND count_star = 0
  AND object_schema != 'mysql'
  AND index_name != 'PRIMARY'
ORDER BY object_schema, object_name;

9.7 查询表的增、删、改、查数据量和I/O耗时统计信息

mysql> select * from schema_table_statistics_with_buffer limit 1\G
......
1 row in set (2.21 sec)

9.8　查看MySQL磁盘文件产生的磁盘流量与读写比例

mysql> select * from io_global_by_file_by_bytes limit 3;
......
3 rows in set (0.00 sec)

9.9　查看哪些语句使用了全表扫描

mysql> select * from statements_with_full_table_scans limit 1\G
......
1 row in set (0.01 sec)

SELECT sys.format_statement(DIGEST_TEXT) AS query,
      SCHEMA_NAME as db,
      COUNT_STAR AS exec_count,
      sys.format_time(SUM_TIMER_WAIT) AS total_latency,
      SUM_NO_INDEX_USED AS no_index_used_count,
      SUM_NO_GOOD_INDEX_USED AS no_good_index_used_count,
      ROUND(IFNULL(SUM_NO_INDEX_USED / NULLIF(COUNT_STAR, 0), 0) * 100) AS no_index_used_pct,
      SUM_ROWS_SENT AS rows_sent,
      SUM_ROWS_EXAMINED AS rows_examined,
      ROUND(SUM_ROWS_SENT/COUNT_STAR) AS rows_sent_avg,
      ROUND(SUM_ROWS_EXAMINED/COUNT_STAR) AS rows_examined_avg,
      FIRST_SEEN as first_seen,
      LAST_SEEN as last_seen,
      DIGEST AS digest
  FROM performance_schema.events_statements_summary_by_digest
WHERE (SUM_NO_INDEX_USED > 0
    OR SUM_NO_GOOD_INDEX_USED > 0)
  AND DIGEST_TEXT NOT LIKE 'SHOW%'
ORDER BY no_index_used_pct DESC, total_latency DESC;

9.10　查看哪些语句使用了文件排序

mysql> select * from statements_with_sorting limit 1\G
......
1 row in set (0.00 sec)

SELECT sys.format_statement(DIGEST_TEXT) AS query,
      SCHEMA_NAME db,
      COUNT_STAR AS exec_count,
      sys.format_time(SUM_TIMER_WAIT) AS total_latency,
      SUM_SORT_MERGE_PASSES AS sort_merge_passes,
      ROUND(IFNULL(SUM_SORT_MERGE_PASSES / NULLIF(COUNT_STAR, 0), 0)) AS avg_sort_merges,
      SUM_SORT_SCAN AS sorts_using_scans,
      SUM_SORT_RANGE AS sort_using_range,
      SUM_SORT_ROWS AS rows_sorted,
      ROUND(IFNULL(SUM_SORT_ROWS / NULLIF(COUNT_STAR, 0), 0)) AS avg_rows_sorted,
      FIRST_SEEN as first_seen,
      LAST_SEEN as last_seen,
      DIGEST AS digest
  FROM performance_schema.events_statements_summary_by_digest
WHERE SUM_SORT_ROWS > 0
ORDER BY SUM_TIMER_WAIT DESC;

9.11　查看哪些语句使用了临时表

mysql> select * from statements_with_temp_tables limit 1\G
......
1 row in set (0.00 sec)

SELECT sys.format_statement(DIGEST_TEXT) AS query,
      SCHEMA_NAME as db,
      COUNT_STAR AS exec_count,
      sys.format_time(SUM_TIMER_WAIT) as total_latency,
      SUM_CREATED_TMP_TABLES AS memory_tmp_tables,
      SUM_CREATED_TMP_DISK_TABLES AS disk_tmp_tables,
      ROUND(IFNULL(SUM_CREATED_TMP_TABLES / NULLIF(COUNT_STAR, 0), 0)) AS avg_tmp_ tables_per_query,
      ROUND(IFNULL(SUM_CREATED_TMP_DISK_TABLES / NULLIF(SUM_CREATED_TMP_TABLES, 0), 0) * 100) AS tmp_tables_to_disk_pct,
      FIRST_SEEN as first_seen,
      LAST_SEEN as last_seen,
      DIGEST AS digest
  FROM performance_schema.events_statements_summary_by_digest
WHERE SUM_CREATED_TMP_TABLES > 0
ORDER BY SUM_CREATED_TMP_DISK_TABLES DESC, SUM_CREATED_TMP_TABLES DESC;

• 温馨提示：关于sys系统库更详细的内容，可参阅微信公众号“沃趣技术”，其中我们用15个章节对其进行了全方位的介绍。或详见开源文档：sys schema详解

---

上一篇：第8章　sys系统库配置表 | 下一篇：第10章　information_schema初相识