mysql查询锁表和是否表锁

#查询是否有锁表 
show OPEN TABLES where In_use > 0;

1：查看当前活跃事务及执行时间

SELECT
trx_id, -- 事务ID
trx_started, -- 事务开始时间
TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS trx_duration_seconds, -- 时间间隔(秒)
trx_mysql_thread_id,-- 事务线程 ID
b.info --锁表sql
FROM information_schema.INNODB_TRX
JOIN information_schema.PROCESSLIST  b ON INNODB_TRX.trx_mysql_thread_id = b.id;

-- 若发现超长时间事务，可kill（谨慎操作）
KILL [线程ID];

2：字段解析
innodb_trx表：
trx_id：事务ID。
trx_state：事务状态，有以下几种状态：RUNNING、LOCK WAIT、ROLLING BACK 和 COMMITTING。
trx_started：事务开始时间。
trx_requested_lock_id：事务当前正在等待锁的标识，可以和 INNODB_LOCKS 表 JOIN 以得到更多详细信息。
trx_wait_started：事务开始等待的时间。
trx_weight：事务的权重。
trx_mysql_thread_id：事务线程 ID，可以和 PROCESSLIST 表 JOIN。
trx_query：事务正在执行的 SQL 语句。
trx_operation_state：事务当前操作状态。
trx_tables_in_use：当前事务执行的 SQL 中使用的表的个数。
trx_tables_locked：当前执行 SQL 的行锁数量。
trx_lock_structs：事务保留的锁数量。
trx_lock_memory_bytes：事务锁住的内存大小，单位为 BYTES。
trx_rows_locked：事务锁住的记录数。包含标记为 DELETED，并且已经保存到磁盘但对事务不可见的行。
trx_rows_modified：事务更改的行数。
trx_concurrency_tickets：事务并发票数。
trx_isolation_level：当前事务的隔离级别。
trx_unique_checks：是否打开唯一性检查的标识。
trx_foreign_key_checks：是否打开外键检查的标识。
trx_last_foreign_key_error：最后一次的外键错误信息。
trx_adaptive_hash_latched：自适应散列索引是否被当前事务锁住的标识。
trx_adaptive_hash_timeout：是否立刻放弃为自适应散列索引搜索 LATCH 的标识。

3：数据库调优参数
vi /etc/my.cnf
 
max_connections = 40000
max_user_connections = 20000
thread_cache_size = 2048
innodb_buffer_pool_size = 16G
innodb_buffer_pool_instances = 8
key_buffer_size = 256M
innodb_log_file_size = 1G
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 2
sync_binlog = 100
tmp_table_size = 256M
max_heap_table_size = 256M
sort_buffer_size = 8M
join_buffer_size = 8M
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_flush_method = O_DIRECT
optimizer_search_depth = 0
innodb_stats_on_metadata = 0
tmpdir = /tmp
table_open_cache = 4000
read_buffer_size = 4M
read_rnd_buffer_size = 4M
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_parallel_read_threads =4<br>
# 独立表空间（推荐）
innodb_file_per_table = ON
# InnoDB 线程并发控制（16核服务器推荐）
innodb_thread_concurrency = 16
open_files_limit = 10000


4：动态设置参数
-- 查看当前连接数
SHOW STATUS LIKE 'Threads_connected';
-- 查看连接使用历史峰值
SHOW STATUS LIKE 'Max_used_connections';
-- 查看当前最大连接数
SHOW VARIABLES LIKE 'max_connections';
-- 临时增加最大连接数（重启后会恢复）【其他值也可以设置和查看】
SET GLOBAL max_connections = 500;

-- 查询索引碎片并生成清理语句（适用于数据量>1MB的表）
SELECT
t.TABLE_NAME,
t.ENGINE,
-- 转换为MB显示，更直观
ROUND(t.DATA_LENGTH / 1024 / 1024, 2) AS data_size_mb,
ROUND(t.INDEX_LENGTH / 1024 / 1024, 2) AS index_size_mb,
ROUND(t.DATA_FREE / 1024 / 1024, 2) AS data_free_mb,
t.fragment_rate_percent,
-- 生成重建表的SQL语句
CONCAT('ALTER TABLE ', t.TABLE_NAME, ' FORCE;') AS rebuild_sql
FROM
(
SELECT
TABLE_NAME,
ENGINE,
DATA_LENGTH,
INDEX_LENGTH,
DATA_FREE,
-- 计算碎片率（仅对数据量>1MB的表有效）
ROUND(DATA_FREE / (DATA_LENGTH + INDEX_LENGTH) * 100, 2) AS fragment_rate_percent
FROM
information_schema.TABLES
WHERE
TABLE_SCHEMA = 'zovecenter_app_xg' -- 替换为你的数据库名
AND TABLE_TYPE = 'BASE TABLE' -- 只处理普通表
AND (DATA_LENGTH + INDEX_LENGTH) > 1024 * 1024 -- 数据+索引总大小>1MB
) t
-- 筛选出碎片率>30%的表（需要优化的表）
WHERE t.fragment_rate_percent > 30
ORDER BY t.fragment_rate_percent DESC;