通过使用 performance schema database 来诊断 MySQL 性能相关的问题。
以下是一些可用于帮助解决由于长时间运行的进程或事务、较大开销查询进程和索引而导致的问题的查询。
查看当前MySQL 运行中的进程和事务详细信息
以下查询结合了进程列表信息和 InnoDB 事务元数据，以获取有关服务器上运行的进程的更多详细信息。
SELECT
p.id AS session_id, p.user,
p.host, p.db,
p.command, p.time,
p.state, substring(p.info, 1, 50) AS info,
t.trx_started, unix_timestamp(now()) - unix_timestamp(t.trx_started) AS trx_age_seconds,
t.trx_rows_modified, t.trx_isolation_level
FROM
information_schema.processlist p
LEFT JOIN
information_schema.innodb_trx t
ON
p.id = t.trx_mysql_thread_id;查看尚未完结的事务
下面的查询显示了数据库中正在执行的未结事务。
SELECT
trx_id, trx_mysql_thread_id,
trx_state, Unix_timestamp() - (To_seconds(trx_started) - To_seconds('1970-01-01 00:00:00')) AS trx_age_seconds,
trx_weight, trx_query,
trx_tables_in_use, trx_tables_locked,
trx_lock_structs, trx_rows_locked,
trx_rows_modified, trx_isolation_level,
trx_unique_checks, trx_is_read_only
FROM
information_schema.innodb_trx
ORDER BY
trx_started ASC;从进程列表输出中识别与会话关联的事务，以查看当前状态和花费的时间。
建议
- 通过将大型或长时间运行的事务分解为较小的事务来避免这些事务。
- 创建CPU使用率监控，以便在系统超过任何指定阈值时收到通知。
- 启用SQL查询监控，识别任何有问题或运行缓慢的查询，然后对其进行优化。
查看开销排名前 10 的 query
下面的查询显示了数据库中正在执行的未结事务。
SELECT
REPLACE(event_name, 'statement/sql/', '') AS statement, count_star AS all_occurrences,
Concat(Round(sum_timer_wait / 1000000000000, 2), ' s') AS total_latency, Concat(Round(avg_timer_wait / 1000000000000, 2), ' s') AS avg_latency,
Concat(Round(sum_lock_time / 1000000000000, 2), ' s') AS total_lock_time, sum_rows_affected AS sum_rows_changed,
sum_rows_sent AS sum_rows_selected, sum_rows_examined AS sum_rows_scanned,
sum_created_tmp_tables, sum_created_tmp_disk_tables,
IF(sum_created_tmp_tables = 0, 0, Concat(Truncate(sum_created_tmp_disk_tables / sum_created_tmp_tables * 100, 0))) AS tmp_disk_tables_percent, sum_select_scan,
sum_no_index_used, sum_no_good_index_used
FROM
performance_schema.events_statements_summary_global_by_event_name
WHERE
event_name LIKE 'statement/sql/%' AND count_star > 0
ORDER BY
sum_timer_wait DESC
LIMIT 10;查看开销排名前 10 的 query digest
以下查询列出了前 10 个开销最大的查询，其中包含详细信息，例如查询生成的临时表数、扫描的行数。
SELECT
digest_text AS normalized_query, count_star AS all_occurrences,
Concat(Round(sum_timer_wait / 1000000000000, 3), ' s') AS total_time, Concat(Round(min_timer_wait / 1000000000000, 3), ' s') AS min_time,
Concat(Round(max_timer_wait / 1000000000000, 3), ' s') AS max_time, Concat(Round(avg_timer_wait / 1000000000000, 3), ' s') AS avg_time,
Concat(Round(sum_lock_time / 1000000000000, 3), ' s') AS total_locktime, sum_rows_affected AS sum_rows_changed,
sum_rows_sent AS sum_rows_selected, sum_rows_examined AS sum_rows_scanned,
sum_created_tmp_tables, sum_created_tmp_tables,
sum_select_scan, sum_no_index_used,
sum_no_good_index_used
FROM
performance_schema.events_statements_summary_by_digest
ORDER BY
sum_timer_wait DESC
LIMIT 10;建议
- 使用
覆盖索引来加快查询速度。 - 使用 EXPLAIN 语句来分析查询并对其进行优化。
MySQL 中的数据库索引能加速 SELECT 查询语句的性能。
索引可以提高具有大量数据的表的性能。
检查索引是否正在被使用
下面的查询将显示所有正在使用的索引，以帮助我们缩小表上的索引是否正在使用的范围，以进一步的去删除任何未使用的索引。
SELECT
object_schema, object_name,
index_name
FROM
performance_schema.table_io_waits_summary_by_index_usage
WHERE
index_name IS NOT NULL AND count_star = 0
ORDER BY
object_schema, object_name;查看最常用的索引
下面的查询显示服务器上最常用的索引。
SELECT
object_schema AS table_schema, object_name AS table_name,
index_name, count_star AS all_accesses,
count_read, count_write,
Concat(Truncate(count_read / count_star * 100, 0), ':', Truncate(count_write / count_star * 100, 0)) AS read_write_ratio, count_fetch AS rows_selected,
count_insert AS rows_inserted, count_update AS rows_updated,
count_delete AS rows_deleted, Concat(Round(sum_timer_wait / 1000000000000, 2), ' s') AS total_latency,
Concat(Round(sum_timer_fetch / 1000000000000, 2), ' s') AS select_latency, Concat(Round(sum_timer_insert / 1000000000000, 2), ' s') AS insert_latency,
Concat(Round(sum_timer_update / 1000000000000, 2), ' s') AS update_latency, Concat(Round(sum_timer_delete / 1000000000000, 2), ' s') AS delete_latency
FROM
performance_schema.table_io_waits_summary_by_index_usage
WHERE
index_name IS NOT NULL
AND count_star > 0
ORDER BY
sum_timer_wait DESC;以上方法，仅供大家在诊断遇到的问题时参考。