分析Mysql表读写、索引等操作的sql语句效率优化问题
上次我们说到mysql的一些sql查询方面的优化,包括查看explain执行计划,分析索引等等。今天我们分享一些分析mysql表读写、索引等等操作的sql语句。
闲话不多说,直接上代码:
反映表的读写压力
SELECTfile_nameASfile, count_read, sum_number_of_bytes_readAStotal_read, count_write, sum_number_of_bytes_writeAStotal_written, (sum_number_of_bytes_read+sum_number_of_bytes_write)AStotal FROMperformance_schema.file_summary_by_instance ORDERBYsum_number_of_bytes_read+sum_number_of_bytes_writeDESC;
反映文件的延迟
SELECT(file_name)ASfile, count_starAStotal, CONCAT(ROUND(sum_timer_wait/3600000000000000,2),'h')AStotal_latency, count_read, CONCAT(ROUND(sum_timer_read/1000000000000,2),'s')ASread_latency, count_write, CONCAT(ROUND(sum_timer_write/3600000000000000,2),'h')ASwrite_latency FROMperformance_schema.file_summary_by_instance ORDERBYsum_timer_waitDESC;
table的读写延迟
SELECTobject_schemaAStable_schema, object_nameAStable_name, count_starAStotal, CONCAT(ROUND(sum_timer_wait/3600000000000000,2),'h')astotal_latency, CONCAT(ROUND((sum_timer_wait/count_star)/1000000,2),'us')ASavg_latency, CONCAT(ROUND(max_timer_wait/1000000000,2),'ms')ASmax_latency FROMperformance_schema.objects_summary_global_by_type ORDERBYsum_timer_waitDESC;
查看表操作频度
SELECTobject_schemaAStable_schema, object_nameAStable_name, count_starASrows_io_total, count_readASrows_read, count_writeASrows_write, count_fetchASrows_fetchs, count_insertASrows_inserts, count_updateASrows_updates, count_deleteASrows_deletes, CONCAT(ROUND(sum_timer_fetch/3600000000000000,2),'h')ASfetch_latency, CONCAT(ROUND(sum_timer_insert/3600000000000000,2),'h')ASinsert_latency, CONCAT(ROUND(sum_timer_update/3600000000000000,2),'h')ASupdate_latency, CONCAT(ROUND(sum_timer_delete/3600000000000000,2),'h')ASdelete_latency FROMperformance_schema.table_io_waits_summary_by_table ORDERBYsum_timer_waitDESC;
索引状况
SELECTOBJECT_SCHEMAAStable_schema, OBJECT_NAMEAStable_name, INDEX_NAMEasindex_name, COUNT_FETCHASrows_fetched, CONCAT(ROUND(SUM_TIMER_FETCH/3600000000000000,2),'h')ASselect_latency, COUNT_INSERTASrows_inserted, CONCAT(ROUND(SUM_TIMER_INSERT/3600000000000000,2),'h')ASinsert_latency, COUNT_UPDATEASrows_updated, CONCAT(ROUND(SUM_TIMER_UPDATE/3600000000000000,2),'h')ASupdate_latency, COUNT_DELETEASrows_deleted, CONCAT(ROUND(SUM_TIMER_DELETE/3600000000000000,2),'h')ASdelete_latency FROMperformance_schema.table_io_waits_summary_by_index_usage WHEREindex_nameISNOTNULL ORDERBYsum_timer_waitDESC;
全表扫描情况
SELECTobject_schema, object_name, count_readASrows_full_scanned FROMperformance_schema.table_io_waits_summary_by_index_usage WHEREindex_nameISNULL ANDcount_read>0 ORDERBYcount_readDESC;
没有使用的index
SELECTobject_schema, object_name, index_name FROMperformance_schema.table_io_waits_summary_by_index_usage WHEREindex_nameISNOTNULL ANDcount_star=0 ANDobject_schemanotin('mysql','v_monitor') ANDindex_name<>'PRIMARY' ORDERBYobject_schema,object_name;
糟糕的sql问题摘要
SELECT(DIGEST_TEXT)ASquery, SCHEMA_NAMEASdb, IF(SUM_NO_GOOD_INDEX_USED>0ORSUM_NO_INDEX_USED>0,'*','')ASfull_scan, COUNT_STARASexec_count, SUM_ERRORSASerr_count, SUM_WARNINGSASwarn_count, (SUM_TIMER_WAIT)AStotal_latency, (MAX_TIMER_WAIT)ASmax_latency, (AVG_TIMER_WAIT)ASavg_latency, (SUM_LOCK_TIME)ASlock_latency, format(SUM_ROWS_SENT,0)ASrows_sent, ROUND(IFNULL(SUM_ROWS_SENT/NULLIF(COUNT_STAR,0),0))ASrows_sent_avg, SUM_ROWS_EXAMINEDASrows_examined, ROUND(IFNULL(SUM_ROWS_EXAMINED/NULLIF(COUNT_STAR,0),0))ASrows_examined_avg, SUM_CREATED_TMP_TABLESAStmp_tables, SUM_CREATED_TMP_DISK_TABLESAStmp_disk_tables, SUM_SORT_ROWSASrows_sorted, SUM_SORT_MERGE_PASSESASsort_merge_passes, DIGESTASdigest, FIRST_SEENASfirst_seen, LAST_SEENaslast_seen FROMperformance_schema.events_statements_summary_by_digestd whered ORDERBYSUM_TIMER_WAITDESC limit20;
掌握这些sql,你能轻松知道你的库那些表存在问题,然后考虑怎么去优化。
总结
以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,谢谢大家对毛票票的支持。如果你想了解更多相关内容请查看下面相关链接