This paper analyzes the efficiency optimization of sql statements in Mysql table reading writing and indexing operations

Last time we talked about some sql query optimization of mysql, including viewing explain execution plan, analyzing index and so on. Today, we share some sql statements that analyze mysql table reading, writing, indexing, and so on.

Don't say much gossip, go directly to the code:

Reflect the reading and writing pressure of the table

SELECT file_name AS file,
    count_read,
    sum_number_of_bytes_read AS total_read,
    count_write,
    sum_number_of_bytes_write AS total_written,
    (sum_number_of_bytes_read + sum_number_of_bytes_write) AS total
 FROM performance_schema.file_summary_by_instance
ORDER BY sum_number_of_bytes_read+ sum_number_of_bytes_write DESC;

Reflect the delay of the file

SELECT (file_name) AS file,
    count_star AS total,
    CONCAT(ROUND(sum_timer_wait / 3600000000000000, 2), 'h') AS total_latency,
    count_read,
    CONCAT(ROUND(sum_timer_read / 1000000000000, 2), 's') AS read_latency,
    count_write,
    CONCAT(ROUND(sum_timer_write / 3600000000000000, 2), 'h')AS write_latency
 FROM performance_schema.file_summary_by_instance
ORDER BY sum_timer_wait DESC;

Read-write delay of table

SELECT object_schema AS table_schema,
       object_name AS table_name,
       count_star AS total,
       CONCAT(ROUND(sum_timer_wait / 3600000000000000, 2), 'h') as total_latency,
       CONCAT(ROUND((sum_timer_wait / count_star) / 1000000, 2), 'us') AS avg_latency,
       CONCAT(ROUND(max_timer_wait / 1000000000, 2), 'ms') AS max_latency
 FROM performance_schema.objects_summary_global_by_type
    ORDER BY sum_timer_wait DESC;

Viewing table operation frequency

SELECT object_schema AS table_schema,
      object_name AS table_name,
      count_star AS rows_io_total,
      count_read AS rows_read,
      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(sum_timer_fetch / 3600000000000000, 2), 'h') AS fetch_latency,
       CONCAT(ROUND(sum_timer_insert / 3600000000000000, 2), 'h') AS insert_latency,
       CONCAT(ROUND(sum_timer_update / 3600000000000000, 2), 'h') AS update_latency,
       CONCAT(ROUND(sum_timer_delete / 3600000000000000, 2), 'h') AS delete_latency
   FROM performance_schema.table_io_waits_summary_by_table
    ORDER BY sum_timer_wait DESC ;

Index status

SELECT OBJECT_SCHEMA AS table_schema,
        OBJECT_NAME AS table_name,
        INDEX_NAME as index_name,
        COUNT_FETCH AS rows_fetched,
        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 rows_updated,
        CONCAT(ROUND(SUM_TIMER_UPDATE / 3600000000000000, 2), 'h') AS update_latency,
        COUNT_DELETE AS rows_deleted,
        CONCAT(ROUND(SUM_TIMER_DELETE / 3600000000000000, 2), 'h')AS delete_latency
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
ORDER BY sum_timer_wait DESC;

Full table scanning

SELECT object_schema,
    object_name,
    count_read AS rows_full_scanned
 FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NULL
  AND count_read > 0
ORDER BY count_read DESC;

index not used

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
  AND object_schema not in ('mysql','v_monitor')
  AND index_name <> 'PRIMARY'
 ORDER BY object_schema, object_name;

Summary of Bad sql Problems

SELECT (DIGEST_TEXT) AS query,
    SCHEMA_NAME AS db,
    IF(SUM_NO_GOOD_INDEX_USED > 0 OR SUM_NO_INDEX_USED > 0, '*', '') AS full_scan,
    COUNT_STAR AS exec_count,
    SUM_ERRORS AS err_count,
    SUM_WARNINGS AS warn_count,
    (SUM_TIMER_WAIT) AS total_latency,
    (MAX_TIMER_WAIT) AS max_latency,
    (AVG_TIMER_WAIT) AS avg_latency,
    (SUM_LOCK_TIME) AS lock_latency,
    format(SUM_ROWS_SENT,0) AS rows_sent,
    ROUND(IFNULL(SUM_ROWS_SENT / NULLIF(COUNT_STAR, 0), 0)) AS rows_sent_avg,
    SUM_ROWS_EXAMINED AS rows_examined,
    ROUND(IFNULL(SUM_ROWS_EXAMINED / NULLIF(COUNT_STAR, 0), 0)) AS rows_examined_avg,
    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,
    FIRST_SEEN AS first_seen,
    LAST_SEEN as last_seen
  FROM performance_schema.events_statements_summary_by_digest d
where d
ORDER BY SUM_TIMER_WAIT DESC
limit 20;

By mastering these sql, you can easily know which tables in your library have problems, and then consider how to optimize them.

Summarize