查看当前正在执行sql语句

--正在执行语句

select a.thd_id,a.db,a.time,a.current_statement,a.full_scan,a.current_memory,a.statement_latency from sys.session a  WHERE  conn_id!=connection_id() and trx_state='ACTIVE'

select * from information_schema.PROCESSLIST where command='Query' and time >1 order by time desc LIMIT 10;

查看已执行完语句相关信息

select * from sys.session WHERE conn_id!=connection_id() and trx_state='COMMITTED'

--kill线程

KILL 41515;

--查看行锁
SELECT
b.trx_query,
c.trx_query,
c.OBJECT_SCHEMA,
OBJECT_NAME,
INDEX_NAME
FROM
performance_schema.data_lock_waits a
LEFT JOIN
information_schema.INNODB_TRX b ON a.REQUESTING_ENGINE_TRANSACTION_ID = b.trx_id
LEFT JOIN
information_schema.INNODB_TRX c ON a.BLOCKING_ENGINE_TRANSACTION_ID = c.trx_id
LEFT JOIN
performance_schema.data_locks c ON a.REQUESTING_ENGINE_LOCK_ID = c.ENGINE_LOCK_ID;

--查看没提交的事物
SELECT thr.processlist_id AS mysql_thread_id,
concat(PROCESSLIST_USER,'@',PROCESSLIST_HOST) User,
command,
FORMAT_PICO_TIME(trx.timer_wait) AS trx_duration,
current_statement as `latest_statement`
FROM performance_schema.events_transactions_current trx
INNER JOIN performance_schema.threads thr USING (thread_id)
LEFT JOIN sys.processlist p ON p.thd_id=thread_id
WHERE thr.processlist_id IS NOT NULL
AND PROCESSLIST_USER IS NOT NULL
AND trx.state = 'ACTIVE'
GROUP BY thread_id, timer_wait
ORDER BY TIMER_WAIT DESC LIMIT 10;

--查询某张表列的信息
select * from information_schema.columns where table_schema ='db' and table_name = 'test’;
--查询某张表的索引
select * from information_schema.STATISTICS where  table_name = 'bd_position'

--已有的表上加分区
alter table testpartition partition by RANGE/List (或者加上column表示非整数分区)(id)
(
PARTITION p4 VALUES LESS THAN/IN (4) ,
PARTITION p5 VALUES LESS THAN/IN (6) ,
);
-- 删除分区数据
alter table testpartition drop partition p20250414
-- 查看某张表的 分区情况
select * from information_schema.PARTITIONS where table_name=‘testpartition’