1、批量杀会话
select concat('kill ',id,';'),host,user,command,time,state,info from information_schema.processlistwhere command !='Sleep' and user not in('repl','system user','event_scheduler') order by time desc limit 10;
锁信息查询：
启动锁监控： update performance_schema.setup_instruments set enabled='YES'where name like'%lock%';
查询
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; show status like '%key_read%'; show status like 'Qcache%'; select table_schema,table_name,table_type from information_schema.tables where table_schema not in ('information_schema','mysql','performance_schema','sys') and table_name not in ( select table_name from ( select table_name,index_name from information_schema.statistics where table_schema not in ('information_schema','mysql','performance_schema','sys') group by table_name,index_name) tab_ind_cols group by table_name );
2.检查会话阻塞
检查
show processlist;show full processlist;select id,user,host,db,command,time,state,info from information_schema.processlist order by time;
查看事务
select * from information_schema.innodb_trx\G;
查看正在锁的事务
select * from information_schema.innodb_locks;
查看等待锁的事务
select * from information_schema.innodb_lock_waits;
检查 metadata lock
select * from performance_schema.setup_instruments where name like '%lock%'; select * from performance_schema.metadata_locks;
查看锁源头
select * from performance_schema.metadata_locks\G;
查看锁源头最近一次执行的SQL
select * from performance_schema.events_statements_current where thread_id=XXX\G;
查询thread_id和processlist_id对应关系
select name,thread_id,processlist_id ,thread_os_id from performance_schema.threads where thread_id in (XXX);
分析锁源头
select locked_schema, locked_table, locked_type, waiting_processlist_id, waiting_age, waiting_query, waiting_state, blocking_processlist_id, blocking_age, substring_index(sql_text,"transaction_begin;" ,-1) as blocking_query, sql_kill_blocking_connectionfrom ( select b.owner_thread_id as granted_thread_id, a.object_schema as locked_schema, a.object_name as locked_table, "metadata lock" as locked_type, c.processlist_id as waiting_processlist_id, c.processlist_time as waiting_age, c.processlist_info as waiting_query, c.processlist_state as waiting_state, d.processlist_id as blocking_processlist_id, d.processlist_time as blocking_age, d.processlist_info as blocking_query, concat('kill ', d.processlist_id) as sql_kill_blocking_connection from performance_schema.metadata_locks a join performance_schema.metadata_locks b on a.object_schema = b.object_schema and a.object_name = b.object_name and a.lock_status = 'pending' and b.lock_status = 'granted' and a.owner_thread_id <> b.owner_thread_id and a.lock_type = 'exclusive' join performance_schema.threads c on a.owner_thread_id = c.thread_id join performance_schema.threads d on b.owner_thread_id = d.thread_id ) t1, ( select thread_id, group_concat( case when event_name = 'statement/sql/begin' then "transaction_begin" else sql_text end order by event_id separator ";" ) as sql_text from performance_schema.events_statements_history group by thread_id ) t2where t1.granted_thread_id = t2.thread_id \G;
存储引擎信息
show engine innodb status\G;
3.数据库大小查询
按库
select table_schema as DB_NAME,concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as DB_DATA_SIZE,concat(round(sum(INDEX_LENGTH/1024/1024),2),'MB') as DB_INDEX_SIZE,concat(round(sum((DATA_LENGTH + INDEX_LENGTH)/1024/1024),2),'MB') as DB_TOTAL_SIZE from information_schema.TABLES group by table_schema order by sum(DATA_LENGTH + INDEX_LENGTH) desc ;
按表
select TABLE_NAME,DATA_LENGTH/1024/1024 as TAB_SIZE,INDEX_LENGTH/1024/1024 as INDEX_SIZE,(DATA_LENGTH + INDEX_LENGTH)/1024/1024 as TOTAL_SIZE from information_schema.TABLES where table_schema='OSCHECK' AND TABLE_NAME IN ('cjc01','cjc02');
全部
select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as DB_DATA_SIZE,concat(round(sum(INDEX_LENGTH/1024/1024),2),'MB') as DB_INDEX_SIZE,concat(round(sum((DATA_LENGTH + INDEX_LENGTH)/1024/1024),2),'MB') as DB_TOTAL_SIZE from information_schema.TABLES;
4.查询表字符集
SELECTT.TABLE_SCHEMA,T.TABLE_NAME,T.ENGINE,CCSA.character_set_nameFROM information_schema.`TABLES` T, information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSAWHERE CCSA.collation_name = T.table_collation ANDT.table_schema in ('xxx') ORDER BY 1,2;
5.查询行格式为Compact，并且列数>20的表
selectconcat ('alter table ',a.TABLE_SCHEMA,'.',a.TABLE_NAME,' ROW_FORMAT=DYNAMIC;') row_changefrominformation_schema.tables a,(select TABLE_SCHEMA,TABLE_NAME,count(*) from COLUMNS group by TABLE_SCHEMA,TABLE_NAME having count(*)>20) bwherea.TABLE_SCHEMA=b.TABLE_SCHEMA anda.TABLE_NAME=b.TABLE_NAME andROW_FORMAT ='Compact';
6.页压缩查询
页压缩的表数据文件，在某些文件系统下，cp后，会导致表放大，占用更多的磁盘空间
查询哪些表配置了页压缩属性
SELECT TABLE_NAME, TABLE_SCHEMA, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE CREATE_OPTIONS LIKE '%COMPRESSION=%';
