# MySQL各种小技巧助你解决疑难杂症
# 查看mysql是否存在锁表
show OPEN TABLES where In_use > 0;
# 查询当前是否有正在执行的事务,或者等待锁释放的事务
#当前产生的锁
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
#当前产生的锁等待
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
#正在执行/打开的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
# 数据库事务隔离级别的查询/设置
show variables like '%isolation%';
# @@global指系统隔离级别
select @@global.tx_isolation, @@tx_isolation;
# 查询事务隔离级别
SELECT @@session.tx_isolation;
SELECT @@global.tx_isolation;
# 设置事务隔离级别的简便方式 READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ
set @@session.tx_isolation = 'READ-UNCOMMITTED'
# 设置read uncommitted级别：
set session transaction isolation level read uncommitted;
# 设置read committed级别：
set session transaction isolation level read committed;
# 设置repeatable read级别：
set session transaction isolation level repeatable read;
# 设置serializable级别：
set session transaction isolation level serializable;
# 以上是设置session级别, 如需设置全局, 则将session改为global
set global transaction isolation level repeatable read;
# 查看innodb的monitor来了解各种状态
show engine innodb status
# 手动开启事务
# 开启事务
START TRANSACTION;
# 或者
begin;
# 或者 开启事务时创建维持整个事务期间的一致性视图快照
start transaction with consistent snapshot;
# 提交事务
commit;
# 回滚事务
rollback;
# 查询/设置mysql的autocommit
#ON代表开启autocommit
show variables like 'autocommit';
#单次生效,重启后恢复,如果代码中显示设置,则是单线程生效
set autocommit = 0;
#永久生效(还可以在mysql配置文件my.conf或my.ini中配置autocommit=0)
set @@autocommit=0
# 查询/设置事务锁等待时间
#通过show查询，可以用GLOBAL或者session来指定环境
SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout';
#通过set设置，通过可以使用环境变量global或者session
SET GLOBAL innodb_lock_wait_timeout=120;
#也可以使用@@+变量指定对应变量
set @@innodb_lock_wait_timeout=30;
set @@global.innodb_lock_wait_timeout=30;
#也可以使用@@+变量方式查询
select @@global.innodb_lock_wait_timeout;
# 查询/设置Innodb超时是否回滚
默认情况下，InnoDB存储引擎不会回滚超时引发的异常，除死锁外。
show variables like 'innodb_rollback_on_timeout';
# 查询表相关信息
# 查询指定表的相关参数与状态
SHOW TABLE STATUS LIKE '指定表';
#另一个种写法，一般常用like，使用from时会需要使用where条件，使用上更麻烦。
SHOW TABLE STATUS from 指定库 where `name`='指定表';
# 查询指定表的列信息（包括注释）
能显示表中所有列信息
#包括注释
show full columns from '指定表'
#以下两种用法效果等同，但不会显示注释
desc '指定表';
show columns from '指定表';
# 显示表的DDL SQL语句
show create table '指定表';
# 对表进行重命名
对表的重命名操作很少用到，但是极少数场景我也碰见了，通常在MySQL的客户端工具上直接操作了，但在要求规范和制度的时候，是没有机会能操作到数据库的。
RENAME TABLE `指定库`.`指定表` TO `指定库`.`指定表的新表名`;
# 查询MySQL版本
select @@version;
# 查询当前数据库线程状态
//能看到四个指标：Threads_cached，Threads_connected，Threads_created，Threads_running
show status like 'Threads%';
# 查询connect相关参数
//其中包括max_connections最大连接数等参数配置。
show variables like '%connect%';
# 监控长事务
# 查询数据库中长事务
select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>20
# 在上一句的基础上增加与processlist表的关联, 获取到长事务的当前db和状态
select trx_state,trx_mysql_thread_id,b.*,TIME_TO_SEC(timediff(now(),trx_started)) from information_schema.innodb_trx a
inner join information_schema.PROCESSLIST b on b.ID = a.trx_mysql_thread_id
where TIME_TO_SEC(timediff(now(),trx_started))>5
# 查看指定表的索引情况
show index from '指定表';
# 查看innodb的相关信息
对诊断死锁，事务锁竞争等具有参考价值
show engine innodb status;