查询 mysql 执行时间长的语句

CREATE PROCEDURE `kill_long_time_query`()
    COMMENT '查询执行时间长的语句'
BEGIN
    select concat('kill ',i.trx_mysql_thread_id,';') from information_schema.innodb_trx i,
  (select 
         id, time
     from
         information_schema.processlist
     where
         time = (select 
                 max(time)
             from
                 information_schema.processlist
             where
                 state = 'Waiting for table metadata lock'
                     and substring(info, 1, 5) in ('alter' , 'optim', 'repai', 'lock ', 'drop ', 'creat'))) p
  where timestampdiff(second, i.trx_started, now()) > p.time
  and i.trx_mysql_thread_id  not in (connection_id(),p.id);
END;

* 创建好之后,通过执行 call kill_long_time_query(); 来获取执行时间过长的语句