MySQL5.7performance_schema库和sys库常用SQL

performance_schema库常用SQL：

创新互联自2013年创立以来，是专业互联网技术服务公司，拥有项目网站建设、成都网站设计网站策划，项目实施与项目整合能力。我们以让每一个梦想脱颖而出为使命，1280元缙云做网站,已为上家服务,为缙云各地企业和个人服务,联系电话:18980820575

查看没有主键的表:

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;

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

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

| 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;

名称栏目：MySQL5.7performance_schema库和sys库常用SQL
URL网址：https://www.cdcxhl.com/article34/gcjdpe.html

成都网站建设公司_创新互联，为您提供App设计、网站设计、品牌网站制作、网站内链、响应式网站、网站制作

声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源：创新互联

猜你还喜欢下面的内容