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

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

4 ##2.查看当前应用连接，连接数突增排查5

6 SELECT user,SUBSTRING_INDEX(HOST,':',1) as ip, count(*) as count, db7 FROMinformation_schema.`PROCESSLIST`8 WHERE `HOST` NOT IN ('localhost') AND `USER` NOT IN('replicater')9 GROUP BY ip ORDER BY count;10

11 ##3.查看表碎片，是否需要整理表释放物理空间12 SELECT TABLE_NAME,TABLE_ROWS ,concat(ROUND(DATA_LENGTH/1024/1024,2),'MB') AS size, DATA_FREE/1024/1024 ASDATA_FREE_MB13 FROMinformation_schema.`TABLES`14 WHERE TABLE_SCHEMA='db库名'

15 ORDER BY DATA_LENGTH DESC;16

17 ##4.当前有没有锁18 SELECT * FROMinformation_schema.INNODB_LOCKS;19

20 ##5.当前锁堵塞情况21 SELECT * FROMinformation_schema.INNODB_LOCK_WAITS;22

23 ##6.当前锁等待详细信息24 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_modified25 frominformation_schema.INNODB_TRX it,information_schema.innodb_locks il,information_schema.processlist pl26 where it.trx_id = il.lock_trx_id and it.trx_mysql_thread_id =pl.id;27

28 ##7.最近一次死锁、未提交事物、CHECKPIONT、BUFFER POOL等29 show engine innodb status;30

31 ##8.过滤无用线程信息可用pager32 show processlist;33

34 ##9.查看当前运行的详细SQL35 SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE info is not null;36

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

40 ##11.查看用户信息41 select user,host,password from mysql.user group by user;42

43 ##12.分表时批量生成sql语句44 select concat("select IP as ",TABLE_NAME," from ",TABLE_SCHEMA,".",TABLE_NAME," group byid;")45 frominformation_schema.TABLES46 where TABLE_NAME like 'table_%';47

48 ##13.查看哪些sql执行最多49 SELECTSCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEEN50 FROMperformance_schema.events_statements_summary_by_digest51 where SCHEMA_NAME is not null and SCHEMA_NAME !='information_schema'

52 ORDER BY COUNT_STAR desc LIMIT 1;53

54 ##14.哪个SQL平均响应时间最多55 SELECTSCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,AVG_TIMER_WAIT,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEEN56 FROMperformance_schema.events_statements_summary_by_digest57 where SCHEMA_NAME is not null and SCHEMA_NAME !='information_schema'

58 ORDER BY AVG_TIMER_WAIT desc LIMIT 1;59

60 ##15.哪个SQL扫描的行数最多(IO消耗)61 SELECTSCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,AVG_TIMER_WAIT,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEEN62 FROMperformance_schema.events_statements_summary_by_digest63 where SCHEMA_NAME is not null and SCHEMA_NAME !='information_schema'

64 ORDER BY SUM_ROWS_EXAMINED desc LIMIT 1;65

66 ##16.哪个SQL使用的临时表最多67 SELECTSCHEMA_NAME,DIGEST_TEXT,SUM_CREATED_TMP_DISK_TABLES,SUM_CREATED_TMP_TABLES,FIRST_SEEN,LAST_SEEN68 FROMperformance_schema.events_statements_summary_by_digest69 where SCHEMA_NAME is not null and SCHEMA_NAME !='information_schema'

70 ORDER BY SUM_CREATED_TMP_DISK_TABLES desc LIMIT 1;71

72 ##17.哪个SQL返回的结果集最多(net消耗)73 SELECTSCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,SUM_ROWS_SENT,SUM_ROWS_SENT,FIRST_SEEN,LAST_SEEN74 FROMperformance_schema.events_statements_summary_by_digest75 where SCHEMA_NAME is not null and SCHEMA_NAME !='information_schema'

76 ORDER BY SUM_ROWS_SENT desc LIMIT 1;77

78 ##18.哪个SQL排序数最多(CPU消耗)79 SELECTSCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,SUM_ROWS_SENT,SUM_SORT_ROWS,FIRST_SEEN,LAST_SEEN80 FROMperformance_schema.events_statements_summary_by_digest81 where SCHEMA_NAME is not null and SCHEMA_NAME !='information_schema'

82 ORDER BY SUM_SORT_ROWS desc LIMIT 5;83

84 #19.哪个表、文件逻辑IO最多(热数据)85 SELECT FILE_NAME,EVENT_NAME,COUNT_READ,SUM_NUMBER_OF_BYTES_READ,COUNT_WRITE,SUM_NUMBER_OF_BYTES_WRITE86 FROMperformance_schema.file_summary_by_instance87 ORDER BY SUM_NUMBER_OF_BYTES_READ+SUM_NUMBER_OF_BYTES_WRITE DESC LIMIT 2;88

89 ##20.哪个索引使用最多90 SELECT OBJECT_NAME, INDEX_NAME, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE91 FROMperformance_schema.table_io_waits_summary_by_index_usage92 ORDER BY SUM_TIMER_WAIT DESC limit 1;93

94 ##21.哪个索引没有使用过95 SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME96 FROMperformance_schema.table_io_waits_summary_by_index_usage97 WHERE INDEX_NAME IS NOT NULL AND COUNT_STAR = 0 AND OBJECT_SCHEMA <> 'mysql'

98 ORDER BY OBJECT_SCHEMA,OBJECT_NAME;99

100 ##22.哪个等待事件消耗的时间最多101 SELECTEVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT, AVG_TIMER_WAIT102 FROMevents_waits_summary_global_by_event_name103 WHERE event_name != 'idle'

104 ORDER BY SUM_TIMER_WAIT DESC LIMIT 1;105

106 ##23.通过performance_schema库得到数据库运行的统计信息，更好分析定位问题和完善监控信息107 #打开标准的innodb监控：108 CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB;109 #打开innodb的锁监控：110 CREATE TABLE innodb_lock_monitor (a INT) ENGINE=INNODB;111 #打开innodb表空间监控：112 CREATE TABLE innodb_tablespace_monitor (a INT) ENGINE=INNODB;113 #打开innodb表监控：114 CREATE TABLE innodb_table_monitor (a INT) ENGINE=INNODB;115

116 ##24.添加主键117 alter table xxx add constraint primary key(id);118

119 ##25.删除外键120 alter table test drop foreign keyFK_XXX;121

122 ##26.QPS123 SHOW GLOBAL STATUS LIKE 'Questions';124 SHOW GLOBAL STATUS LIKE 'Uptime';125

126 #@27.TPS127 SHOW GLOBAL STATUS LIKE 'Com_commit';128 SHOW GLOBAL STATUS LIKE 'Com_rollback';129 SHOW GLOBAL STATUS LIKE 'Uptime';130 (Com_commit + Com_rollback)/Uptime131

132

133 ##28.134 select table_name,table_rows,concat(round(DATA_LENGTH/1024/1024, 2), 'MB') as size,DATA_FREE/1024/1024 ASdata_free_MB135 frominformation_schema.TABLES136 where table_schema='库名'

137 order by DATA_LENGTH desc;138

139 ##29.清理binlog140 PURGE BINARY LOGS TO 'XXX';141 PURGE BINARY LOGS BEFORE '2018-06-10 00:00:00';142

143 ##30.外键隔离级别等信息144 select @@FOREIGN_KEY_CHECKS;145 select @@global.tx_isolation,@@tx_isolation;146 select @@character_set_database;147 select @@GLOBAL.sql_mode;