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;
