om/数据库/mysql/线程占用.md

information_schema这张数据表保存了MySQL服务器所有数据库的信息。如数据库名，数据库的表，表栏的数据类型与访问权限等。再简单点，这台MySQL服务器上，到底有哪些数据库、各个数据库有哪些表，每张表的字段类型是什么，各个数据库要什么权限才能访问，等等信息都保存在information_schema表里面。

1. innodb_trx 当前运行的所有事务
2. innodb_locks 当前出现的锁
3. innodb_lock_waits 锁等待的对应关系

-- 查看当前连接数
SHOW STATUS LIKE 'Threads_connected';
-- 查看系统设置连接数
SHOW VARIABLES LIKE 'max_connections';
-- 查看那个表被锁住
SHOW OPEN TABLES WHERE In_use > 0;
-- 查看哪个事物占用锁
select * from information_schema.innodb_trx;
-- 杀死进程
kill trx_mysql_thread_id;
select trx_mysql_thread_id from information_schema.innodb_trx where trx_state ='LOCK WAIT ';
-- 查看所有进程
SELECT * FROM information_schema.processlist
show full processlist;

-- sql 执行时间
show global variables where Variable_name = 'max_execution_time'；
SET GLOBAL MAX_EXECUTION_TIME=1000;
SET SESSION MAX_EXECUTION_TIME=1000;
SELECT max_execution_time=1000 SLEEP(10), a.* from test a;

select * from information_schema.COLUMNS where TABLE_SCHEMA='platform3_dev' and COLLATION_NAME='utf8mb4_unicode_ci' and TABLE_NAME like 'bfm_ems_equipment_maintenance%' and COLUMN_NAME LIKE '%id%' limit 1000;

连接超时

The last packet successfully received from the server was 235,944,824 milliseconds ago. The last packet sent successfully to the server was 235,944,824 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.