Shulou(Shulou.com)06/01 Report--

Last time we talked about some sql query optimizations for mysql, including viewing explain execution plans, analyzing indexes, and so on. Today we share some sql statements that analyze mysql table read and write, index, and so on.

Don't talk too much, just go to the code:

Reflect the reading and writing pressure of the meter

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_instanceORDER 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 / 10000000000000, 2),'s') AS read_latency, count_write, CONCAT (ROUND (sum_timer_write / 36000000000000,2),'h') AS write_latency FROM performance_schema.file_summary_by_instanceORDER BY sum_timer_wait DESC

Read and 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 (max_timer_wait / 1000000000, 2) 'ms') AS max_latency FROM performance_schema.objects_summary_global_by_type ORDER BY sum_timer_wait DESC

View 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 / 36000000000000, 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 condition

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 / 36000000000000, 2),'h') AS delete_latencyFROM performance_schema.table_io_waits_summary_by_index_usageWHERE index_name IS NOT NULLORDER BY sum_timer_wait DESC

Full table scan

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

Unused index

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 dwhere dORDER BY SUM_TIMER_WAIT DESClimit 20

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

Summary

The above is the whole content of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. Thank you for your support. If you want to know more about it, please see the relevant links below.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.