分析Mysql表读写、索引等操作的sql语句效率优化问题

上次我们说到mysql的一些sql查询方面的优化，包括查看explain执行计划，分析索引等等。今天我们分享一些 分析mysql表读写、索引等等操作的sql语句。

闲话不多说，直接上代码：

反映表的读写压力

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;

反映文件的延迟

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;

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;

查看表操作频度

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 ;

索引状况

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;

全表扫描情况

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

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;

糟糕的sql问题摘要

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;

掌握这些sql，你能轻松知道你的库那些表存在问题，然后考虑怎么去优化。   

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，谢谢大家对的支持。如果你想了解更多相关内容请查看下面相关链接