Mysql 优化——分析表读写和sql效率问题

转载自：https://blog.csdn.net/moneyshi/article/details/72828575

　　-- 反映表的读写压力  

1. SELECT file_name AS file,  
2.        count_read,  
3.        sum_number_of_bytes_read AS total_read,  
4.        count_write,  
5.        sum_number_of_bytes_write AS total_written,  
6.        (sum_number_of_bytes_read + sum_number_of_bytes_write) AS total  
7.   FROM performance_schema.file_summary_by_instance  
8. ORDER BY sum_number_of_bytes_read+ sum_number_of_bytes_write DESC;  
9. -- 反映文件的延迟  
10. SELECT (file_name) AS file,  
11.        count_star AS total,  
12.        CONCAT(ROUND(sum_timer_wait / 3600000000000000, 2), 'h') AS total_latency,  
13.        count_read,  
14.         CONCAT(ROUND(sum_timer_read / 1000000000000, 2), 's') AS read_latency,  
15.        count_write,  
16.        CONCAT(ROUND(sum_timer_write / 3600000000000000, 2), 'h')AS write_latency  
17.   FROM performance_schema.file_summary_by_instance  
18. ORDER BY sum_timer_wait DESC;  
19. -- table 的读写延迟  
20. SELECT object_schema AS table_schema,  
21.              object_name AS table_name,  
22.              count_star AS total,  
23.               CONCAT(ROUND(sum_timer_wait / 3600000000000000, 2), 'h') as total_latency,  
24.               CONCAT(ROUND((sum_timer_wait / count_star) / 1000000, 2), 'us') AS avg_latency,  
25.               CONCAT(ROUND(max_timer_wait / 1000000000, 2), 'ms') AS max_latency  
26.   FROM performance_schema.objects_summary_global_by_type  
27.        ORDER BY sum_timer_wait DESC;  
28. -- 查看表操作频度  
29. SELECT object_schema AS table_schema,  
30.             object_name AS table_name,  
31.             count_star AS rows_io_total,  
32.             count_read AS rows_read,  
33.             count_write AS rows_write,  
34.             count_fetch AS rows_fetchs,  
35.             count_insert AS rows_inserts,  
36.             count_update AS rows_updates,  
37.             count_delete AS rows_deletes,  
38.              CONCAT(ROUND(sum_timer_fetch / 3600000000000000, 2), 'h') AS fetch_latency,  
39.              CONCAT(ROUND(sum_timer_insert / 3600000000000000, 2), 'h') AS insert_latency,  
40.              CONCAT(ROUND(sum_timer_update / 3600000000000000, 2), 'h') AS update_latency,  
41.              CONCAT(ROUND(sum_timer_delete / 3600000000000000, 2), 'h') AS delete_latency  
42.      FROM performance_schema.table_io_waits_summary_by_table  
43.         ORDER BY sum_timer_wait DESC ;  
44. -- 索引状况  
45. SELECT OBJECT_SCHEMA AS table_schema,  
46.                OBJECT_NAME AS table_name,  
47.                INDEX_NAME as index_name,  
48.                COUNT_FETCH AS rows_fetched,  
49.                CONCAT(ROUND(SUM_TIMER_FETCH / 3600000000000000, 2), 'h') AS select_latency,  
50.                COUNT_INSERT AS rows_inserted,  
51.                CONCAT(ROUND(SUM_TIMER_INSERT / 3600000000000000, 2), 'h') AS insert_latency,  
52.                COUNT_UPDATE AS rows_updated,  
53.                CONCAT(ROUND(SUM_TIMER_UPDATE / 3600000000000000, 2), 'h') AS update_latency,  
54.                COUNT_DELETE AS rows_deleted,  
55.                 CONCAT(ROUND(SUM_TIMER_DELETE / 3600000000000000, 2), 'h')AS delete_latency  
56. FROM performance_schema.table_io_waits_summary_by_index_usage  
57. WHERE index_name IS NOT NULL  
58. ORDER BY sum_timer_wait DESC;  
59. -- 全表扫描情况  
60. SELECT object_schema,  
61.        object_name,  
62.        count_read AS rows_full_scanned  
63.   FROM performance_schema.table_io_waits_summary_by_index_usage  
64. WHERE index_name IS NULL  
65.    AND count_read > 0  
66. ORDER BY count_read DESC;  
67. -- 没有使用的index  
68. SELECT object_schema,  
69.         object_name,  
70.         index_name  
71.    FROM performance_schema.table_io_waits_summary_by_index_usage  
72.   WHERE index_name IS NOT NULL  
73.     AND count_star = 0  
74.     AND object_schema not in  ('mysql','v_monitor')  
75.    AND index_name <> 'PRIMARY'  
76.   ORDER BY object_schema, object_name;  
77. -- 糟糕的sql问题摘要  
78. SELECT (DIGEST_TEXT) AS query,  
79.         SCHEMA_NAME AS db,  
80.         IF(SUM_NO_GOOD_INDEX_USED > 0 OR SUM_NO_INDEX_USED > 0, '*', '') AS full_scan,  
81.         COUNT_STAR AS exec_count,  
82.         SUM_ERRORS AS err_count,  
83.         SUM_WARNINGS AS warn_count,  
84.         (SUM_TIMER_WAIT) AS total_latency,  
85.         (MAX_TIMER_WAIT) AS max_latency,  
86.         (AVG_TIMER_WAIT) AS avg_latency,  
87.         (SUM_LOCK_TIME) AS lock_latency,  
88.         format(SUM_ROWS_SENT,0) AS rows_sent,  
89.         ROUND(IFNULL(SUM_ROWS_SENT / NULLIF(COUNT_STAR, 0), 0)) AS rows_sent_avg,  
90.         SUM_ROWS_EXAMINED AS rows_examined,  
91.         ROUND(IFNULL(SUM_ROWS_EXAMINED / NULLIF(COUNT_STAR, 0), 0))  AS rows_examined_avg,  
92.         SUM_CREATED_TMP_TABLES AS tmp_tables,  
93.         SUM_CREATED_TMP_DISK_TABLES AS tmp_disk_tables,  
94.         SUM_SORT_ROWS AS rows_sorted,  
95.         SUM_SORT_MERGE_PASSES AS sort_merge_passes,  
96.         DIGEST AS digest,  
97.         FIRST_SEEN AS first_seen,  
98.         LAST_SEEN as last_seen  
99.    FROM performance_schema.events_statements_summary_by_digest d  
100. where d  
101. ORDER BY SUM_TIMER_WAIT DESC  
102. limit 20;