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
CONCAT('kill ',trx_mysql_thread_id,';') kill_sql
FROM information_schema.INNODB_TRX
JOIN information_schema.PROCESSLIST b ON INNODB_TRX.trx_mysql_thread_id = b.id
ORDER BY trx_started;

SELECT
t.trx_id,
t.trx_started,
TIMESTAMPDIFF(SECOND, t.trx_started, NOW()) AS trx_duration_seconds,
t.trx_mysql_thread_id,
b.info,
CONCAT('kill ', t.trx_mysql_thread_id, ';') AS kill_sql
FROM (
-- 子查询只拿最小字段做排序，不带大字段info，大幅降低临时表占用
SELECT
trx_id,
trx_started,
trx_mysql_thread_id
FROM information_schema.INNODB_TRX
ORDER BY trx_started
) t
LEFT JOIN information_schema.PROCESSLIST b
ON t.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

# 独立表空间（推荐）
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;

5：表碎片
-- 查询表碎片并生成清理语句  
SELECT * FROM (
SELECT
TABLE_NAME AS `表名`,
TABLE_ROWS AS `行数`,
DATA_LENGTH AS `数据字节`,
INDEX_LENGTH AS `索引字节`,
DATA_FREE AS `空闲碎片字节`,
ROUND(DATA_FREE / (DATA_LENGTH + INDEX_LENGTH) * 100, 2) AS `碎片率%`,
CONCAT(ROUND((DATA_LENGTH + INDEX_LENGTH)/1024/1024,2),'M') AS `总占用`,
CONCAT(ROUND(DATA_FREE/1024/1024,2),'M') AS `碎片大小`,
CONCAT('OPTIMIZE TABLE ',TABLE_NAME,';')AS `碎片整理SQL`
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'zovecenter_app_xg'
AND ENGINE = 'InnoDB'
) t
WHERE t.`碎片率%` > 1
ORDER BY t.`碎片率%` DESC;