MySQL 性能优化 笔记

常用查询语句整理

-- 解决锁表问题，用这条命令查询数据库阻塞的进程
SELECT * FROM information_schema.innodb_trx 

找到后在根据下图这个字段：try_mysql_thread_id 作为这条数据的主键id执行这个sql进行删除： kill id ;(杀死对应id的进程).假设这里try_mysql_thread_id=277 的这条数据是锁了。我们执行 kill 277 删除就不在锁表了。

参照：https://www.cnblogs.com/zheng123/p/11363654.html


-- 查询正在使用的表
show OPEN TABLES where In_use > 0;

-- 显示哪些线程正在运行，查看锁表语句等；
show full PROCESSLIST;

-- 查看正在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
-- 查看正在等待锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
# 查看正在锁的事务
select * from information_schema.INNODB_TRX;

-- 使用以下语句可以探窥到锁情况，它会打印出所有innodb的信,这个命令会显示关于死锁事务的信息，谁在使用锁，谁在等待锁;
mysql> SHOW ENGINE INNODB STATUS;
-- 重点查看“WAITING FOR THIS LOCK TO BE GRANTED”和“HOLDS THE LOCKS.”部分

参考文章： https://www.awaimai.com/2534.html
参考文章：【MySql 锁表问题排查】 https://www.cnblogs.com/fycn01/p/13522649.html
参考文章：【mysql数据库应用优化和锁表排查】 https://www.cnblogs.com/z-macro/p/14680971.html
参考文章：【mysql事务中宕机_事务执行一半】https://blog.csdn.net/weixin_29474431/article/details/113602581

-- 如何查找MySQL中查询慢的SQL语句
MySQL5.7 查看此配置：
slow_query_log_file = /home/data/mysql/slow.log

MySQL高级篇（SQL优化、索引优化、锁机制、主从复制） https://blog.csdn.net/yuan2019035055/article/details/122310447

找出谁持有全局读锁

数据库全局锁定和解锁

-- 全局读锁定，执行之后库中所有的表都被锁定只读；
flush table with read lock;
-- 解锁语句
unlock tables;

第一个会话

-- 全局读锁定，执行之后库中所有的表都被锁定只读；
flush table with read lock;

-- 查询加锁线程的process_id，以便与后续排查过程对应
select CONNECTION_ID()

id:4

第二个会话

-- 执行一个update 操作
update a_test set name ='update' where id =1 
-- 查询线程id
select CONNECTION_ID()

id: 5

第三个会话

-- 查询线程id
select CONNECTION_ID()
-- 查询processlist信息，线程State为 Waiting for global read lock,表示正在等待全局读锁
show PROCESSLIST;

-- 查询以下 3个表 均为空
select * from information_schema.INNODB_LOCKS;
select * from information_schema.INNODB_LOCK_waits;
select * from information_schema.INNODB_TRX;

-- 再使用 show engine INNODB status; 查看(这里主要只需要查看TRANSACTION段落即可)，仍然无任何有用的锁信息
show engine INNODB status;

第四个会话

##### MySQL5.7版本开始提供了 performance_schema.metadata_locks表，该表记录了 各种Server层的锁信息（包括全局读锁和MDL锁等信息）； 通过performance_schema.metadata_locks表来排查谁持有全局读锁，全局读锁在该表中通常记录着同一个会话的OBJECT_TYPE为global和commit、LOCK_TYPE都为SHARED的两把显式锁

select * from performance_schema.metadata_locks where OWNER_THread_ID != sys.ps_thread_id(CONNECTION_ID())

Object_type:GLOBAL
lock_type: SHARED # 共享锁
lock_DURATION:EXPLICIT # 显式
lock_status:GRANTED # 已授予
owner_thread_id:94 #持有锁的内部线程ID为94

Object_type:COMMIT
lock_type: SHARED # 共享锁
lock_DURATION:EXPLICIT # 显式
lock_status:GRANTED # 已授予
owner_thread_id:94 #持有锁的内部线程ID为94


Object_type:GLOBAL
lock_type: INTENTION_EXCLUSIVE # 意向排他锁
lock_DURATION:STATEMENT # 语句
lock_status:PENDING # 状态为PENDING. 表示正在等待被授予
owner_thread_id:95 # 被阻塞的内部线程ID为95
owner_event_id:38

# 查看process id 为4 和5 的线程 各自对应的内部线程ID 是多少
select sys.ps_thread_id(4);
#如果process id为 4的线程对应的内部线程id正好是94，说明就是process id为 4的线程持有了全局读锁
select sys.ps_thread_id(5);
#如果process id为 5的线程对应的内部线程id正好是95，说明就是process id为5的线程正在等待全局读锁

附录：

MySQL 解决锁表问题【2021-12-01】

SELECT * FROM  information_schema.innodb_trx 命令是用来查看当前运行的所以事务：
在执行结果中可以看到是否有表锁等待或者死锁，如果有死锁发生，可以通过下面的命令来杀掉当前运行的事务：
KILL 165667 ;   KILL 后面的数字指的是 trx_mysql_thread_id 值。
参考文章：https://www.cnblogs.com/cheyunhua/p/13897428.html

MySQL 查询锁表语句详情 汇总

参照：https://blog.csdn.net/qq_40026782/article/details/100518617