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;