Analysieren Sie die Probleme der SQL-Anweisungseffizienzoptimierung beim Lesen, Schreiben, Indizieren und anderen Vorgängen in MySQL-Tabellen

SELECT Dateiname AS Datei,
    Anzahl der Lesevorgänge,
    Summe der gelesenen Bytes AS Gesamtlesung,
    zählen_schreiben,
    Summe_Anzahl_der_geschriebenen_Bytes AS total_written,
    (Summe der gelesenen Bytes + Summe der geschriebenen Bytes) AS gesamt
 VON performance_schema.file_summary_by_instance
ORDER BY Summe_Anzahl_der_gelesenen_Bytes+ Summe_Anzahl_der_geschriebenen_Bytes DESC;

SELECT (Dateiname) AS-Datei,
    count_star AS insgesamt,
    CONCAT(ROUND(Summe_Wartezeit / 3600000000000000, 2), 'h') AS Gesamtlatenz,
    Anzahl der Lesevorgänge,
    CONCAT(ROUND(Summe_Timer_Read / 1000000000000, 2), 's') AS Leselatenz,
    zählen_schreiben,
    CONCAT(ROUND(Summe_Timer_Schreiben / 3600000000000000, 2), 'h') AS Schreiblatenz
 VON performance_schema.file_summary_by_instance
ORDER BY sum_timer_wait DESC;

SELECT Objektschema AS Tabellenschema,
       Objektname AS Tabellenname,
       count_star AS insgesamt,
       CONCAT(ROUND(sum_timer_wait / 3600000000000000, 2), 'h') als Gesamtlatenz,
       CONCAT(ROUND((sum_timer_wait / count_star) / 1000000, 2), 'us') AS avg_latency,
       CONCAT(ROUND(max_timer_wait / 1000000000, 2), 'ms') AS max_latency
 VON performance_schema.objects_summary_global_by_type
    ORDER BY sum_timer_wait DESC;

SELECT Objektschema AS Tabellenschema,
      Objektname AS Tabellenname,
      count_star AS rows_io_total,
      Anzahl_gelesener Zeilen als gelesene Zeilen,
      count_write AS rows_write,
      count_fetch AS rows_fetchs,
      count_insert AS rows_inserts,
      count_update AS rows_updates,
      count_delete AS rows_deletes,
       CONCAT(ROUND(Summe_Timer_Fetch / 3600000000000000, 2), 'h') AS Fetch_Latenz,
       CONCAT(ROUND(sum_timer_insert / 3600000000000000, 2), 'h') AS insert_latency,
       CONCAT(ROUND(Summe_Timer_Update / 3600000000000000, 2), 'h') AS Update_Latenz,
       CONCAT(ROUND(Summe_Timer_Löschen / 3600000000000000, 2), 'h') AS Löschlatenz
   VON performance_schema.table_io_waits_summary_by_table
    BESTELLE NACH sum_timer_wait DESC;

Wählen Sie OBJECT_SCHEMA AS table_schema,
        OBJECT_NAME AS Tabellenname,
        INDEX_NAME als Indexname,
        COUNT_FETCH AS abgerufene Zeilen,
        CONCAT(ROUND(SUM_TIMER_FETCH / 3600000000000000, 2), 'h') AS select_latency,
        COUNT_INSERT AS rows_inserted,
        CONCAT(ROUND(SUM_TIMER_INSERT / 3600000000000000, 2), 'h') AS insert_latency,
        COUNT_UPDATE AS Zeilen_aktualisiert,
        CONCAT(ROUND(SUM_TIMER_UPDATE / 3600000000000000, 2), 'h') AS update_latency,
        COUNT_DELETE AS gelöschte Zeilen,
        CONCAT(ROUND(SUM_TIMER_DELETE / 3600000000000000, 2), 'h') AS delete_latency
VON performance_schema.table_io_waits_summary_by_index_usage
Wobei Indexname nicht NULL ist
ORDER BY sum_timer_wait DESC;

SELECT Objektschema,
    Objektname,
    Anzahl der gelesenen Zeilen als vollständig gescannt
 VON performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NULL
  UND Anzahl der gelesenen Daten > 0
ORDER BY Anzahl_Lesen DESC;

SELECT Objektschema,
    Objektname,
    Indexname
  VON performance_schema.table_io_waits_summary_by_index_usage
 Wobei Indexname nicht NULL ist
  UND count_star = 0
  UND Objektschema nicht in ('mysql', 'v_monitor')
  UND Indexname <> 'PRIMARY'
 ORDER BY Objektschema, Objektname;

SELECT (DIGEST_TEXT) AS-Abfrage,
    SCHEMA_NAME AS db,
    WENN (SUM_NO_GOOD_INDEX_USED > 0 ODER SUM_NO_INDEX_USED > 0, '*', '') ALS full_scan,
    COUNT_STAR AS exec_count,
    SUM_ERRORS AS err_count,
    SUM_WARNINGS AS warn_count,
    (SUM_TIMER_WAIT) AS Gesamtlatenz,
    (MAX_TIMER_WAIT) AS max_latency,
    (AVG_TIMER_WAIT) AS avg_latency,
    (SUM_LOCK_TIME) AS Sperrlatenz,
    format(SUM_ROWS_SENT,0) AS rows_sent,
    RUNDEN(WENNNULL(SUM_ROWS_SENT / NULLWENN(ANZAHL_STAR, 0), 0)) AS rows_sent_avg,
    SUM_ROWS_EXAMINED AS Zeilen_untersucht,
    RUNDEN(WENNNULL(SUMME_UNTERSUCHTE_ZEILEN / NULLWENN(ANZAHL_STAR, 0), 0)) ALS Durchschnitt_untersuchter_Zeilen,
    SUM_CREATED_TMP_TABLES AS tmp_tables,
    SUM_CREATED_TMP_DISK_TABLES AS tmp_disk_tables,
    SUM_SORT_ROWS AS rows_sorted,
    SUM_SORT_MERGE_PASSES AS sort_merge_passes,
    DIGEST AS Digest,
    ERSTES_GESEHEN ALS erstes_gesehen,
    LAST_SEEN als zuletzt gesehen
  VON performance_schema.events_statements_summary_by_digest d
wobei d
ORDER BY SUM_TIMER_WAIT DESC
Grenze 20;