MySQL 常用命令(二)--获取SQL运行状态和基础数据
1. 获取锁等待信息
如果 版本server_version < (8, 0, 1)
SELECT rtrx.`trx_state` AS "等待的状态", rtrx.`trx_started` AS "等待事务开始时间", rtrx.`trx_wait_started` AS "等待事务等待开始时间", lw.`requesting_trx_id` AS "等待事务ID", rtrx.trx_mysql_thread_id AS "等待事务线程ID", rtrx.`trx_query` AS "等待事务的sql", CONCAT(rl.`lock_mode`, '-', rl.`lock_table`, '(', rl.`lock_index`, ')') AS "等待的表信息", rl.`lock_id` AS "等待的锁id", lw.`blocking_trx_id` AS "运行的事务id", trx.trx_mysql_thread_id AS "运行的事务线程id", CONCAT(l.`lock_mode`, '-', l.`lock_table`, '(', l.`lock_index`, ')') AS "运行的表信息", l.lock_id AS "运行的锁id", trx.`trx_state` AS "运行事务的状态", trx.`trx_started` AS "运行事务的时间", trx.`trx_wait_started` AS "运行事务的等待开始时间", trx.`trx_query` AS "运行事务的sql" FROM information_schema.`INNODB_LOCKS` rl , information_schema.`INNODB_LOCKS` l , information_schema.`INNODB_LOCK_WAITS` lw , information_schema.`INNODB_TRX` rtrx , information_schema.`INNODB_TRX` trx WHERE rl.`lock_id` = lw.`requested_lock_id` AND l.`lock_id` = lw.`blocking_lock_id` AND lw.requesting_trx_id = rtrx.trx_id AND lw.blocking_trx_id = trx.trx_id;
或者
SELECT rtrx.`trx_state` AS "等待的状态", rtrx.`trx_started` AS "等待事务开始时间", rtrx.`trx_wait_started` AS "等待事务等待开始时间", lw.`REQUESTING_ENGINE_TRANSACTION_ID` AS "等待事务ID", rtrx.trx_mysql_thread_id AS "等待事务线程ID", rtrx.`trx_query` AS "等待事务的sql", CONCAT(rl.`lock_mode`, '-', rl.`OBJECT_SCHEMA`, '(', rl.`INDEX_NAME`, ')') AS "等待的表信息", rl.`ENGINE_LOCK_ID` AS "等待的锁id", lw.`BLOCKING_ENGINE_TRANSACTION_ID` AS "运行的事务id", trx.trx_mysql_thread_id AS "运行的事务线程id", CONCAT(l.`lock_mode`, '-', l.`OBJECT_SCHEMA`, '(', l.`INDEX_NAME`, ')') AS "运行的表信息", l.ENGINE_LOCK_ID AS "运行的锁id", trx.`trx_state` AS "运行事务的状态", trx.`trx_started` AS "运行事务的时间", trx.`trx_wait_started` AS "运行事务的等待开始时间", trx.`trx_query` AS "运行事务的sql" FROM performance_schema.`data_locks` rl , performance_schema.`data_locks` l , performance_schema.`data_lock_waits` lw , information_schema.`INNODB_TRX` rtrx , information_schema.`INNODB_TRX` trx WHERE rl.`ENGINE_LOCK_ID` = lw.`REQUESTING_ENGINE_LOCK_ID` AND l.`ENGINE_LOCK_ID` = lw.`BLOCKING_ENGINE_LOCK_ID` AND lw.REQUESTING_ENGINE_TRANSACTION_ID = rtrx.trx_id AND lw.BLOCKING_ENGINE_TRANSACTION_ID = trx.trx_id;
2.获取长事务
select trx.trx_started, trx.trx_state, trx.trx_operation_state, trx.trx_mysql_thread_id, trx.trx_tables_locked, trx.trx_rows_locked, trx.trx_rows_modified, trx.trx_is_read_only, trx.trx_isolation_level, p.user, p.host, p.db, TO_SECONDS(NOW()) - TO_SECONDS(trx.trx_started) trx_idle_time, p.time thread_time, IFNULL((SELECT GROUP_CONCAT(t1.sql_text SEPARATOR '; ') FROM performance_schema.events_statements_history t1 INNER JOIN performance_schema.threads t2 ON t1.thread_id = t2.thread_id WHERE t2.PROCESSLIST_ID = p.id), '') info FROM information_schema.INNODB_TRX trx INNER JOIN information_schema.PROCESSLIST p ON trx.trx_mysql_thread_id = p.id WHERE trx.trx_state = 'RUNNING' AND p.COMMAND = 'Sleep' AND p.time > {} #####请输入查询的时间阈值 ORDER BY trx.trx_started ASC;
3.获取连接信息
select id, user, host, db, command, time, state, ifnull(info,'') as info from information_schema.processlist;
4.查看正在执行的线程,并按 Time 倒排序
select * from information_schema.processlist where Command != 'Sleep' order by Time desc;
5.找出所有执行时间超过 5 分钟的线程,拼凑出 kill 语句,方便后面查杀 (此处 5分钟 可根据自己的需要调整SQL标红处)
select concat('kill ', id, ';') from information_schema.processlist where Command != 'Sleep' and Time > 300 order by Time desc;
6.获取表格索引信息
SELECT COLUMN_NAME as '列名', INDEX_NAME as '索引名', NON_UNIQUE as '唯一性', SEQ_IN_INDEX as '列序列', CARDINALITY as '基数', NULLABLE as '是否为空', INDEX_TYPE as '索引类型', COMMENT as '备注' FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = '{db_name}' AND TABLE_NAME = '{tb_name}';
7.获取表格字段信息
SELECT COLUMN_NAME as '列名', COLUMN_TYPE as '列类型', CHARACTER_SET_NAME as '列字符集', IS_NULLABLE as '是否为空', COLUMN_KEY as '索引列', COLUMN_DEFAULT as '默认值', EXTRA as '拓展信息', COLUMN_COMMENT as '列说明' FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = '{db_name}' AND TABLE_NAME = '{tb_name}' ORDER BY ORDINAL_POSITION;
8.获取表的元信息
SELECT TABLE_NAME as table_name, ENGINE as engine, ROW_FORMAT as row_format, TABLE_ROWS as table_rows, AVG_ROW_LENGTH as avg_row_length, round(DATA_LENGTH/1024, 2) as data_length, MAX_DATA_LENGTH as max_data_length, round(INDEX_LENGTH/1024, 2) as index_length, round((DATA_LENGTH + INDEX_LENGTH)/1024, 2) as data_total, DATA_FREE as data_free, AUTO_INCREMENT as auto_increment, TABLE_COLLATION as table_collation, CREATE_TIME as create_time, CHECK_TIME as check_time, UPDATE_TIME as update_time, TABLE_COMMENT as table_comment FROM information_schema.TABLES WHERE TABLE_SCHEMA='{db_name}' AND TABLE_NAME='{tb_name}
9.查看监控Innodb的阻塞
SELECT b.trx_mysql_thread_id AS '被阻塞线程', b.trx_query AS '被阻塞SQL', c.trx_mysql_thread_id AS '阻塞线程', c.trx_query AS '阻塞SQL',(UNIX_TIMESTAMP()-UNIX_TIMESTAMP(c.trx_started)) AS '阻塞时间' FROM information_schema.INNODB_LOCK_WAITS a JOIN information_schema.INNODB_TRX b ON a.requesting_trx_id=b.trx_id JOIN information_schema.INNODB_TRX c ON a.blocking_trx_id=c.trx_id WHERE (UNIX_TIMESTAMP()-UNIX_TIMESTAMP(c.trx_started)) > 60
标签:
MySQL 管理
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库