mysql优化之 performance Schema常用查询sql

开启掘金成长之旅！这是我参与「掘金日新计划 · 12 月更文挑战」的第2天，点击查看活动详情

use `performance_schema`

--1、哪类的 SQL 执行最多?

SELECT
	DIGEST_TEXT,
	COUNT_STAR,
	FIRST_SEEN,
	LAST_SEEN
FROM
	events_statements_summary_by_digest
ORDER BY
	COUNT_STAR DESC

--2、哪类 SQL 的平均响应时间最多?

SELECT
	DIGEST_TEXT,
	AVG_TIMER_WAIT
FROM
	events_statements_summary_by_digest
ORDER BY
	COUNT_STAR DESC

-3、哪类SQL 排序记录数最多?

SELECT
	DIGEST_TEXT,
	SUM_SORT_ROWS
FROM
	events_statements_summary_by_digest
ORDER BY
	COUNT_STAR DESC

-4、哪类 SQL 扫描记录数最多?

SELECT
	DIGEST_TEXT,
	SUM_ROWS_EXAMINED
FROM
	events_statements_summary_by_digest
ORDER BY
	COUNT_STAR DESC

--5、哪类 SQL 使用临时表最多?

SELECT
	DIGEST_TEXT,
	SUM_CREATED_TMP_TABLES,
	SUM_CREATED_TMP_DISK_TABLES
FROM
	events_statements_summary_by_digest
ORDER BY
	COUNT_STAR DESC

--6、哪类 SQL 返回结果集最多?

SELECT
	DIGEST_TEXT,
	SUM_ROWS_SENT
FROM
	events_statements_summary_by_digest
ORDER BY
	COUNT_STAR DESC

--7、哪个表物理IO最多?

SELECT
	file_name,
	event_name,
	SUM_NUMBER_OF_BYTES_READ,
	SUM_NUMBER_OF_BYTES_WRITE
FROM
	file_summary_by_instance
ORDER BY
	SUM_NUMBER_OF_BYTES_READ + SUM_NUMBER_OF_BYTES_WRITE DESC

--8、哪个表逻辑 IO 最多?

SELECT
	object_name,
	COUNT_READ,
	COUNT_WRITE,
	COUNT_FETCH,
	SUM_TIMER_WAIT
FROM
	table_io_waits_summary_by_table
ORDER BY
	sum_timer_wait DESC

--9、哪个索引访问最多?

SELECT
	OBJECT_NAME,
	INDEX_NAME,
	COUNT_FETCH,
	COUNT_INSERT,
	COUNT_UPDATE,
	COUNT_DELETE
FROM
	table_io_waits_summary_by_index_usage
ORDER BY
	SUM_TIMER_WAIT DESC

--10、哪个索引从来没有用过 ?

SELECT
	OBJECT_SCHEMA,
	OBJECT_NAME,
	INDEX_NAME
FROM
	table_io_waits_summary_by_index_usage
WHERE
	INDEX_NAME IS NOT NULL
AND COUNT_STAR = 0
AND OBJECT_SCHEMA <> 'mysql'
ORDER BY
	OBJECT_SCHEMA,OBJECT_NAME;

--11、哪个等待事件消耗时间最多?

SELECT
	EVENT_NAME,
	COUNT_STAR,
	SUM_TIMER_WAIT,
	AVG_TIMER_WAIT
FROM
	events_waits_summary_global_by_event_name
WHERE
	event_name != 'idle'
ORDER BY
	SUM_TIMER_WAIT DESC

--12-1、剖析某条 SQL 的执行情况，包括 statement 信息，stege 信息，wait 信息

SELECT
	EVENT_ID,
	sql_text
FROM
	events_statements_history
WHERE
	sqL_text LIKE '%count(*)%';

--12 - 2、查看每个阶段的时间消耗

SELECT
	event_id,
	EVENT_NAME,
	SOURCE,
	TIMER_END - TIMER_START
FROM
	events_stages_history_long
WHERE
	NESTING_EVENT_ID = 1553

--12 - 3、查看每个阶段的锁等待情况

SELECT
	event_id,
	event_name,
	source,
	timer_wait,
	object_name,
	index_name,
	operation,
	nesting_event_id
FROM
	events_waits_history_long
WHERE
	nesting_event_id = 1553