MariaDB 10.2表无法解锁及查询冻结问题求助

针对你用MariaDB 10.2操作6000万行的signals表时遇到的冻结、表被锁问题，我整理了分步解决和优化方案：

一、先解锁被占用的signals表 #

当show open tables where In_use = 1显示表被占用时，先找到并终止相关进程：

1. 查看所有活跃进程，定位和signals表相关的锁定进程：

   SHOW PROCESSLIST;
   

   重点看Info列包含signals、State列显示Locked的进程，记下它的Id值。
2. 终止该进程（如果有多个锁定进程，逐个执行）：

   KILL [进程Id];
   

   执行后再重新运行show open tables where In_use = 1，确认表已释放。

二、排查操作冻结的核心原因（大表必看） #

你的批量插入和count(*)查询冻结，大概率是索引缺失或内存配置不足导致的，这是大表操作的常见坑：

• 检查signal_num的索引：如果这个字段没有索引，不管是insert ... where还是count(*) where都会触发全表扫描，6000万行的数据量直接会把资源耗尽。执行以下命令查看索引：

  SHOW INDEX FROM signals;
  SHOW INDEX FROM signals_tmp;
  

  如果signal_num没有索引，赶紧补上（注意：大表建索引耗时较长，选业务低峰期操作）：

  CREATE INDEX idx_signal_num ON signals(signal_num);
  CREATE INDEX idx_tmp_signal_num ON signals_tmp(signal_num);
  

• 调整MariaDB内存配置：打开my.cnf（Windows是my.ini），重点优化这两个参数：
  • innodb_buffer_pool_size：建议设为服务器内存的50%-70%（比如8G内存的机器设为4G-5.6G），这是InnoDB缓存大表数据的核心参数。
  • innodb_log_file_size：建议设为256M-1G，避免批量插入时频繁切换日志文件拖慢速度。
    修改后重启mysqld服务生效。

三、修复可能损坏的表（重启后仍异常的情况） #

如果重启服务后表还是异常，可能是InnoDB表在冻结时出现了损坏，尝试修复：

1. 先停止mysqld服务。
2. 启动时添加--innodb_force_recovery=1（从1开始尝试，最高到6，数值越高风险越大，尽量从低到高试），强制启动服务。
3. 检查表状态：

   CHECK TABLE signals;
   

4. 如果检查出损坏，执行修复：

   REPAIR TABLE signals;
   

   修复完成后，去掉启动参数，正常重启服务。

四、后续大表操作的优化建议 #

为了避免再遇到类似问题，给你两个实用建议：

• 批量插入分批次执行：不要一次性插入全量数据，每次插1万条左右，循环直到完成：

  INSERT INTO signals SELECT * FROM signals_tmp WHERE signal_num = 21 LIMIT 10000;
  

• 估算行数替代精确count(*)：如果不需要绝对精确的行数，可以用表统计数据快速获取，避免全表扫描：

  SELECT TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'phd' AND TABLE_NAME = 'signals';
  

内容的提问来源于stack exchange，提问作者swmfg