mysql 命令笔记

当中慢sql或锁sql

select * from information_schema.processlist where command <>'Sleep' order by time DESC limit 5; 

SELECT p2.`HOST` Blockedhost,  
p2.`USER` BlockedUser,  
r.trx_id BlockedTrxId,      
        r.trx_mysql_thread_id BlockedThreadId,      
        TIMESTAMPDIFF(      
            SECOND,      
            r.trx_wait_started,      
            CURRENT_TIMESTAMP      
        ) WaitTime,      
        r.trx_query BlockedQuery,      
        l.lock_table BlockedTable,    
        m.`lock_mode` BlockedLockMode,  
        m.`lock_type` BlockedLockType,  
        m.`lock_index` BlockedLockIndex,  
        m.`lock_space` BlockedLockSpace,  
        m.lock_page BlockedLockPage,  
        m.lock_rec BlockedLockRec,  
        m.lock_data BlockedLockData,   
        p.`HOST` blocking_host,   
        p.`USER` blocking_user,  
        b.trx_id BlockingTrxid,      
        b.trx_mysql_thread_id BlockingThreadId,  
        b.trx_query BlockingQuery,  
        l.`lock_mode` BlockingLockMode,  
        l.`lock_type` BlockingLockType,  
        l.`lock_index` BlockingLockIndex,  
        l.`lock_space` BlockingLockSpace,  
        l.lock_page BlockingLockPage,  
        l.lock_rec BlockingLockRec,  
        l.lock_data BlockingLockData,           
       IF (p.COMMAND = 'Sleep', CONCAT(p.TIME,' seconds'), 0) idel_in_trx               
    FROM      
        information_schema.INNODB_LOCK_WAITS w      
    INNER JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id      
    INNER JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id      
    INNER JOIN information_schema.INNODB_LOCKS l ON w.blocking_lock_id = l.lock_id  AND l.`lock_trx_id`=b.`trx_id`  
      INNER JOIN information_schema.INNODB_LOCKS m ON m.`lock_id`=w.`requested_lock_id` AND m.`lock_trx_id`=r.`trx_id`  
    INNER JOIN information_schema. PROCESSLIST p ON p.ID = b.trx_mysql_thread_id     
 INNER JOIN information_schema. PROCESSLIST p2 ON p2.ID = r.trx_mysql_thread_id   
    ORDER BY      
        WaitTime DESC;

批量杀线程

select CONCAT('kill ',id,';') from information_schema.processlist where host='%' ;

锁语句

select *  from information_schema.processlist where command <>'Sleep' order by time DESC limit 5;  

连接数

select *  from information_schema.processlist

连接数分组

SELECT substring_index(host, char(58), 1) ip , count(*) count2 FROM information_schema.processlist GROUP BY ip

腾讯云连接库查询方式

select TABLE_NAME,MAX(PARTITION_NAME)
from information_schema.PARTITIONS
where TABLE_SCHEMA=’database_name’
and PARTITION_METHOD=’RANGE’
and TABLE_NAME not like ‘%201%’
group by TABLE_NAME ;

查询即将过期的分区表

需要拥有select对象information_schema权限。 database_name要改成项目自己的数据库名，不然查不到数据。

 

查看sql_mode

select @@global.sql_mode;