MYSQL性能排查

本文摘自https://www.cnblogs.com/goodtest2018/p/9167089.html

#0.MySQL数据库连接

mysql -u### -p### 

##1.查询表的所属以及有多少行 SELECTTABLE_SCHEMA,TABLE_NAME,TABLE_ROWSFROMinformation_schema.`TABLES`WHERETABLE_NAME='PRODUCT';

##2.查看当前应用连接，连接数突增排查SELECTuser,SUBSTRING_INDEX(HOST,':',1)asip,count(*)ascount, db FROM information_schema.`PROCESSLIST`WHERE`HOST`NOTIN('localhost')AND`USER`NOTIN('replicater')GROUPBYipORDERBYcount;

##3.查看表碎片，是否需要整理表释放物理空间SELECTTABLE_NAME,TABLE_ROWS ,concat(ROUND(DATA_LENGTH/1024/1024,2),'MB')ASsize, DATA_FREE/1024/1024AS DATA_FREE_MBFROM information_schema.`TABLES`WHERETABLE_SCHEMA='db库名'ORDERBYDATA_LENGTHDESC;

##4.当前有没有锁SELECT*FROM information_schema.INNODB_LOCKS;

##5.当前锁堵塞情况SELECT*FROM information_schema.INNODB_LOCK_WAITS;

##6.当前锁等待详细信息selectit.trx_mysql_thread_id, il.lock_id, il.lock_table, il.lock_mode, il.lock_type, it.trx_state, pl.USER||'@'||pl.HOSTasuser_host, pl.db, pl.command, pl.info, it.trx_started, it.trx_wait_started, now()-trx_wait_startedas wait_seconds, il.lock_index, it.trx_weight, it.trx_rows_locked, it.trx_rows_modified from information_schema.INNODB_TRX it,information_schema.innodb_locks il,information_schema.processlist pl whereit.trx_id=il.lock_trx_idandit.trx_mysql_thread_id= pl.id;

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

show engine innodb status;

##8.过滤无用线程信息可用pager

show processlist;

##9.查看当前运行的详细SQLSELECT*FROMINFORMATION_SCHEMA.PROCESSLISTWHEREinfoisnotnull;

##10.查看某条sql各阶段执行时间，可开启profiling功能setglobal profiling=on;

##11.查看用户信息selectuser,host,passwordfrommysql.usergroupbyuser;

##12.分表时批量生成sql语句selectconcat("selectIPas",TABLE_NAME,"from",TABLE_SCHEMA,".",TABLE_NAME,"groupby id;") from information_schema.TABLES whereTABLE_NAMElike'table_%';

##13.查看哪些sql执行最多SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEEN FROM performance_schema.events_statements_summary_by_digest whereSCHEMA_NAMEisnotnullandSCHEMA_NAME!='information_schema'ORDERBYCOUNT_STARdescLIMIT1;

##14.哪个SQL平均响应时间最多SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,AVG_TIMER_WAIT,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEEN FROM performance_schema.events_statements_summary_by_digest whereSCHEMA_NAMEisnotnullandSCHEMA_NAME!='information_schema'ORDERBYAVG_TIMER_WAITdescLIMIT1;

##15.哪个SQL扫描的行数最多(IO消耗)SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,AVG_TIMER_WAIT,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEEN FROM performance_schema.events_statements_summary_by_digest whereSCHEMA_NAMEisnotnullandSCHEMA_NAME!='information_schema'ORDERBYSUM_ROWS_EXAMINEDdescLIMIT1;

##16.哪个SQL使用的临时表最多SELECT SCHEMA_NAME,DIGEST_TEXT,SUM_CREATED_TMP_DISK_TABLES,SUM_CREATED_TMP_TABLES,FIRST_SEEN,LAST_SEEN FROM performance_schema.events_statements_summary_by_digest whereSCHEMA_NAMEisnotnullandSCHEMA_NAME!='information_schema'ORDERBYSUM_CREATED_TMP_DISK_TABLESdescLIMIT1;

##17.哪个SQL返回的结果集最多(net消耗)SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,SUM_ROWS_SENT,SUM_ROWS_SENT,FIRST_SEEN,LAST_SEEN FROM performance_schema.events_statements_summary_by_digest whereSCHEMA_NAMEisnotnullandSCHEMA_NAME!='information_schema'ORDERBYSUM_ROWS_SENTdescLIMIT1;

##18.哪个SQL排序数最多(CPU消耗)SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,SUM_ROWS_SENT,SUM_SORT_ROWS,FIRST_SEEN,LAST_SEEN FROM performance_schema.events_statements_summary_by_digest whereSCHEMA_NAMEisnotnullandSCHEMA_NAME!='information_schema'ORDERBYSUM_SORT_ROWSdescLIMIT5;

#19.哪个表、文件逻辑IO最多（热数据）SELECTFILE_NAME,EVENT_NAME,COUNT_READ,SUM_NUMBER_OF_BYTES_READ,COUNT_WRITE,SUM_NUMBER_OF_BYTES_WRITE FROM performance_schema.file_summary_by_instance ORDERBYSUM_NUMBER_OF_BYTES_READ+SUM_NUMBER_OF_BYTES_WRITEDESCLIMIT2;

##20.哪个索引使用最多SELECTOBJECT_NAME, INDEX_NAME, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE FROM performance_schema.table_io_waits_summary_by_index_usage ORDERBYSUM_TIMER_WAITDESClimit1;

##21.哪个索引没有使用过SELECTOBJECT_SCHEMA,OBJECT_NAME, INDEX_NAME FROM performance_schema.table_io_waits_summary_by_index_usage WHEREINDEX_NAMEISNOTNULLANDCOUNT_STAR=0ANDOBJECT_SCHEMA<>'mysql'ORDERBYOBJECT_SCHEMA,OBJECT_NAME;

##22.哪个等待事件消耗的时间最多SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT, AVG_TIMER_WAIT FROM events_waits_summary_global_by_event_name WHEREevent_name!='idle'ORDERBYSUM_TIMER_WAITDESCLIMIT1;

##23.通过performance_schema库得到数据库运行的统计信息，更好分析定位问题和完善监控信息

#打开标准的innodb监控：CREATETABLEinnodb_monitor (aINT) ENGINE=INNODB;

#打开innodb的锁监控：CREATETABLEinnodb_lock_monitor (aINT) ENGINE=INNODB;

#打开innodb表空间监控：CREATETABLEinnodb_tablespace_monitor (aINT) ENGINE=INNODB;

#打开innodb表监控：CREATETABLEinnodb_table_monitor (aINT) ENGINE=INNODB;

##24.添加主键altertablexxxaddconstraintprimarykey(id);

##25.删除外键altertabletestdropforeignkey FK_XXX;

##26.QPS

SHOW GLOBAL STATUS LIKE'Questions';

SHOW GLOBAL STATUS LIKE'Uptime';

#@27.TPS

SHOW GLOBAL STATUS LIKE'Com_commit';

SHOW GLOBAL STATUS LIKE'Com_rollback';

SHOW GLOBAL STATUS LIKE'Uptime';

(Com_commit +Com_rollback)/Uptime

##28.selecttable_name,table_rows,concat(round(DATA_LENGTH/1024/1024,2),'MB')assize,DATA_FREE/1024/1024AS data_free_MB from information_schema.TABLES wheretable_schema='库名'orderbyDATA_LENGTHdesc;

##29.清理binlog

PURGE BINARYLOGSTO'XXX';

PURGE BINARYLOGS 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;