mysql 性能 定位_mysql 性能定位，性能排查sql

##1.查詢表的所屬以及有多少行SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_ROWS FROM information_schema.`TABLES` WHERE TABLE_NAME='PRODUCT';

##2.查看當前應用連接，連接數突增排查SELECT user,SUBSTRING_INDEX(HOST,':',1) as ip, count(*) as count, dbFROMinformation_schema.`PROCESSLIST`WHERE `HOST` NOT IN ('localhost') AND `USER` NOT IN('replicater')GROUP BY ip ORDER BY count;

##3.查看表碎片，是否需要整理表釋放物理空間SELECT TABLE_NAME,TABLE_ROWS ,concat(ROUND(DATA_LENGTH/1024/1024,2),'MB') AS size, DATA_FREE/1024/1024 ASDATA_FREE_MBFROMinformation_schema.`TABLES`WHERE TABLE_SCHEMA='db庫名'

ORDER BY DATA_LENGTH DESC;

##4.當前有沒有鎖SELECT * FROMinformation_schema.INNODB_LOCKS;

##5.當前鎖堵塞情況SELECT * FROMinformation_schema.INNODB_LOCK_WAITS;

##6.當前鎖等待詳細信息select it.trx_mysql_thread_id, il.lock_id, il.lock_table, il.lock_mode, il.lock_type, it.trx_state, pl.USER||'@'||pl.HOST as user_host, pl.db, pl.command, pl.info, it.trx_started, it.trx_wait_started, now()-trx_wait_started aswait_seconds, il.lock_index, it.trx_weight, it.trx_rows_locked, it.trx_rows_modifiedfrominformation_schema.INNODB_TRX it,information_schema.innodb_locks il,information_schema.processlist plwhere it.trx_id = il.lock_trx_id and it.trx_mysql_thread_id =pl.id;

##7.最近一次死鎖、未提交事物、CHECKPIONT、BUFFER POOL等

show engine innodb status;

##8.過濾無用線程信息可用pager

show processlist;

##9.查看當前運行的詳細SQLSELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE info is not null;

##10.查看某條sql各階段執行時間，可開啟profiling功能set global profiling=on;

##11.查看用戶信息select user,host,password from mysql.user group by user;

##12.分表時批量生成sql語句select concat("select IP as ",TABLE_NAME," from ",TABLE_SCHEMA,".",TABLE_NAME," group byid;")frominformation_schema.TABLESwhere TABLE_NAME like 'table_%';

##13.查看哪些sql執行最多SELECTSCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEENFROMperformance_schema.events_statements_summary_by_digestwhere SCHEMA_NAME is not null and SCHEMA_NAME !='information_schema'

ORDER BY COUNT_STAR desc LIMIT 1;

##14.哪個SQL平均響應時間最多SELECTSCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,AVG_TIMER_WAIT,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEENFROMperformance_schema.events_statements_summary_by_digestwhere SCHEMA_NAME is not null and SCHEMA_NAME !='information_schema'

ORDER BY AVG_TIMER_WAIT desc LIMIT 1;

##15.哪個SQL掃描的行數最多(IO消耗)SELECTSCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,AVG_TIMER_WAIT,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEENFROMperformance_schema.events_statements_summary_by_digestwhere SCHEMA_NAME is not null and SCHEMA_NAME !='information_schema'

ORDER BY SUM_ROWS_EXAMINED desc LIMIT 1;

##16.哪個SQL使用的臨時表最多SELECTSCHEMA_NAME,DIGEST_TEXT,SUM_CREATED_TMP_DISK_TABLES,SUM_CREATED_TMP_TABLES,FIRST_SEEN,LAST_SEENFROMperformance_schema.events_statements_summary_by_digestwhere SCHEMA_NAME is not null and SCHEMA_NAME !='information_schema'

ORDER BY SUM_CREATED_TMP_DISK_TABLES desc LIMIT 1;

##17.哪個SQL返回的結果集最多(net消耗)SELECTSCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,SUM_ROWS_SENT,SUM_ROWS_SENT,FIRST_SEEN,LAST_SEENFROMperformance_schema.events_statements_summary_by_digestwhere SCHEMA_NAME is not null and SCHEMA_NAME !='information_schema'

ORDER BY SUM_ROWS_SENT desc LIMIT 1;

##18.哪個SQL排序數最多(CPU消耗)SELECTSCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,SUM_ROWS_SENT,SUM_SORT_ROWS,FIRST_SEEN,LAST_SEENFROMperformance_schema.events_statements_summary_by_digestwhere SCHEMA_NAME is not null and SCHEMA_NAME !='information_schema'

ORDER BY SUM_SORT_ROWS desc LIMIT 5;

#19.哪個表、文件邏輯IO最多(熱數據)SELECT FILE_NAME,EVENT_NAME,COUNT_READ,SUM_NUMBER_OF_BYTES_READ,COUNT_WRITE,SUM_NUMBER_OF_BYTES_WRITEFROMperformance_schema.file_summary_by_instanceORDER BY SUM_NUMBER_OF_BYTES_READ+SUM_NUMBER_OF_BYTES_WRITE DESC LIMIT 2;

##20.哪個索引使用最多SELECT OBJECT_NAME, INDEX_NAME, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETEFROMperformance_schema.table_io_waits_summary_by_index_usageORDER BY SUM_TIMER_WAIT DESC limit 1;

##21.哪個索引沒有使用過SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAMEFROMperformance_schema.table_io_waits_summary_by_index_usageWHERE INDEX_NAME IS NOT NULL AND COUNT_STAR = 0 AND OBJECT_SCHEMA <> 'mysql'

ORDER BY OBJECT_SCHEMA,OBJECT_NAME;

##22.哪個等待事件消耗的時間最多SELECTEVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT, AVG_TIMER_WAITFROMevents_waits_summary_global_by_event_nameWHERE event_name != 'idle'

ORDER BY SUM_TIMER_WAIT DESC LIMIT 1;

##23.通過performance_schema庫得到數據庫運行的統計信息，更好分析定位問題和完善監控信息

#打開標准的innodb監控：CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB;

#打開innodb的鎖監控：CREATE TABLE innodb_lock_monitor (a INT) ENGINE=INNODB;

#打開innodb表空間監控：CREATE TABLE innodb_tablespace_monitor (a INT) ENGINE=INNODB;

#打開innodb表監控：CREATE TABLE innodb_table_monitor (a INT) ENGINE=INNODB;

##24.添加主鍵alter table xxx add constraint primary key(id);

##25.刪除外鍵alter table test drop foreign keyFK_XXX;

##26.QPS

SHOW GLOBAL STATUSLIKE 'Questions';

SHOW GLOBAL STATUSLIKE 'Uptime';

#@27.TPS

SHOW GLOBAL STATUSLIKE 'Com_commit';

SHOW GLOBAL STATUSLIKE 'Com_rollback';

SHOW GLOBAL STATUSLIKE 'Uptime';

(Com_commit+ Com_rollback)/Uptime

##28.select table_name,table_rows,concat(round(DATA_LENGTH/1024/1024, 2), 'MB') as size,DATA_FREE/1024/1024 ASdata_free_MBfrominformation_schema.TABLESwhere table_schema='庫名'

order by DATA_LENGTH desc;

##29.清理binlog

PURGEBINARY LOGS TO 'XXX';

PURGEBINARY LOGS BEFORE '2018-06-10 00:00:00';

##30.外鍵隔離級別等信息select @@FOREIGN_KEY_CHECKS;select @@global.tx_isolation,@@tx_isolation;select @@character_set_database;select @@GLOBAL.sql_mode;