温馨提示×

温馨提示×

您好,登录后才能下订单哦!

密码登录×
登录注册×
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》

MySQL 5.7 performance_schema库和sys库常用SQL

发布时间:2020-08-10 08:49:48 来源:ITPUB博客 阅读:240 作者:chenfeng 栏目:MySQL数据库

performance_schema库常用SQL:

查看没有主键的表:

    SELECT DISTINCT t.table_schema, t.table_name

      FROM information_schema.tables AS t

      LEFT JOIN information_schema.columns AS c ON t.table_schema = c.table_schema 

AND t.table_name = c.table_name AND c.column_key = "PRI"

     WHERE t.table_schema NOT IN ('information_schema', 'mysql', 'performance_schema')

       AND c.table_name IS NULL AND t.table_type != 'VIEW';


例如:

mysql> SELECT DISTINCT t.table_schema, t.table_name

    ->       FROM information_schema.tables AS t

    ->       LEFT JOIN information_schema.columns AS c ON t.table_schema = c.table_schema 

AND t.table_name = c.table_name  AND c.column_key = "PRI"

    ->      WHERE t.table_schema NOT IN ('information_schema', 'mysql', 'performance_schema')

    ->        AND c.table_name IS NULL AND t.table_type != 'VIEW';


+--------------+---------------------------+

| table_schema | table_name                |

+--------------+---------------------------+

| S85          | dsf                       |

| test         | innodb_lock_monitor       |

| test         | innodb_monitor            |

| test         | innodb_table_monitor      |

| test         | innodb_tablespace_monitor |

| zhwp102      | t_orgpriority             |

| zhwp102      | t_task_ext                |

| zhwp102      | t_web_common              |

| zhwp111      | t_orgpriority             |

| zhwp111      | t_task_ext                |

| zhwp111      | t_web_common              |

| zhwp111      | t_weibo                   |

| zhwp_prod    | t_orgpriority             |

| zhwp_prod    | t_task_ext                |

| zhwp_prod    | t_web_common              |

| zhwp_prod    | t_weibo                   |

| zhwpzj111    | t_orgpriority             |

| zhwpzj111    | t_task_ext                |

| zhwpzj111    | t_web_common              |

| zhwpzj111    | t_weibo                   |

+--------------+---------------------------+

20 rows in set (1 min 27.55 sec)


没有主键:

mysql> desc S85.dsf;    

+------------+----------------------+------+-----+-------------------+-------+

| Field      | Type                 | Null | Key | Default           | Extra |

+------------+----------------------+------+-----+-------------------+-------+

| sourceDay  | date                 | YES  |     | NULL              |       |

| sourceTime | datetime             | NO   |     | CURRENT_TIMESTAMP |       |

| affections | smallint(5) unsigned | NO   |     | 1                 |       |

+------------+----------------------+------+-----+-------------------+-------+

3 rows in set (0.00 sec)


查看是谁创建的临时表


    SELECT user, host, event_name, count_star AS cnt, sum_created_tmp_disk_tables AS tmp_disk_tables, 

sum_created_tmp_tables AS tmp_tables

      FROM performance_schema.events_statements_summary_by_account_by_event_name

     WHERE sum_created_tmp_disk_tables > 0

        OR sum_created_tmp_tables > 0 ;



没有正确关闭数据库连接的用户

    SELECT ess.user, ess.host

         , (a.total_connections - a.current_connections) - ess.count_star as not_closed

         , ((a.total_connections - a.current_connections) - ess.count_star) * 100 /

           (a.total_connections - a.current_connections) as pct_not_closed

      FROM performance_schema.events_statements_summary_by_account_by_event_name ess

      JOIN performance_schema.accounts a on (ess.user = a.user and ess.host = a.host)

     WHERE ess.event_name = 'statement/com/quit'

       AND (a.total_connections - a.current_connections) > ess.count_star ;


DDL元数据锁跟踪

1.打开跟踪:

UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE 

NAME = 'wait/lock/metadata/sql/mdl';

UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE

 NAME = 'global_instrumentation';

2.查询metadata lock:

select  * from performance_schema.metadata_locks;

select  * from performance_schema.metadata_locks where LOCK_STATUS like 'PENDING%';

select ID from information_schema.processlist where Info  like '%20190416%' \G

SELECT OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,LOCK_STATUS,processlist_id 

    FROM performance_schema.metadata_locks mdl

    INNER JOIN performance_schema.threads thd ON mdl.owner_thread_id = thd.thread_id 

    WHERE processlist_id <> @@pseudo_thread_id;


3.关闭跟踪:

UPDATE performance_schema.setup_instruments SET ENABLED = 'NO' WHERE 

NAME = 'wait/lock/metadata/sql/mdl';    

   

DDL执行进度跟踪

1.打开跟踪:

UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'stage/innodb/alter%';

UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%stages%';

2.查看DDL执行进度:

SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED,(WORK_COMPLETED/WORK_ESTIMATED)*100 

as COMPLETED FROM performance_schema.events_stages_current;


sys库常用SQL:

查看表访问量

select table_schema,table_name,sum(io_read_requests+io_write_requests) io from sys.schema_table_statistics 

group by table_schema,table_name order by io desc limit 10;


查看数据库连接情况

select * from sys.processlist \G

select * from sys.session limit 10 \G

select * from sys.x$processlist \G

select * from sys.x$session \G


查看冗余索引

select table_schema,table_name,redundant_index_name,redundant_index_columns,dominant_index_name,

dominant_index_columns  from sys.schema_redundant_indexes;


查看未使用索引

select * from sys.schema_unused_indexes;


表自增ID监控

select * from sys.schema_auto_increment_columns limit 10;


查看实际消耗磁盘IO的文件

select file,avg_read+avg_write as avg_io from sys.io_global_by_file_by_bytes order by avg_io desc limit 10;


向AI问一下细节

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

AI