MySQL 5.7 performance_schema库和sys库常用SQL

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;