萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

Shell 批量修改 MySQL 存儲引擎:兩大高效方案詳解

MySQL 存儲引擎速覽

選擇合適的存儲引擎對性能至關重要。常見引擎特性對比:

引擎事務支持鎖粒度典型場景
InnoDB✅ 支持行級鎖高併發寫入、關鍵業務數據
MyISAM❌ 不支持表級鎖只讀/多讀少寫、全文搜索
Memory❌ 不支持表級鎖臨時表、緩存數據
Archive❌ 不支持行級鎖日志歸檔、海量歷史數據

本文演示如何將數據庫中所有 MyISAM 表批量轉換為 InnoDB。

方案一:Shell 腳本循環執行 ALTER TABLE

核心邏輯

  1. 查詢指定數據庫下特定引擎的所有表名
  2. 循環執行 ALTER TABLE table_name ENGINE=InnoDB
  3. 記錄轉換結果日志

完整腳本(可直接使用)

#!/bin/bash
# 配置區域 - 請根據實際環境修改
DB="your_database"
USER="root"
PASSWD="your_password"
HOST="127.0.0.1"
MYSQL_BIN="/usr/local/mysql/bin"
SRC_ENGINE="MyISAM"
DEST_ENGINE="InnoDB"

# 導出待轉換表名
$MYSQL_BIN/mysql -h$HOST -u$USER -p$PASSWD $DB -e \
  "SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA='$DB' AND ENGINE='$SRC_ENGINE';" \
  | grep -v "TABLE_NAME" > tables.txt

# 循環轉換
for t_name in $(cat tables.txt); do
  echo "正在轉換表: $t_name ..."
  $MYSQL_BIN/mysql -h$HOST -u$USER -p$PASSWD $DB -e "ALTER TABLE \`$t_name\` ENGINE=$DEST_ENGINE"
  
  if [ $? -eq 0 ]; then
    echo "$(date) - 表 $t_name 轉換成功" >> convert.log
  else
    echo "$(date) - 表 $t_name 轉換失敗" >> convert.log
  fi
  sleep 1  # 避免過載
done

echo "批量轉換完成,詳見 convert.log"

交互式版本

取消腳本中註釋的 read 行,運行時將提示輸入參數,適合臨時操作。

方案二:生成 SQL 文件批量執行(更高效)

利用 information_schema 直接生成所有 ALTER 語句:

SELECT CONCAT('ALTER TABLE `', TABLE_NAME, '` ENGINE=InnoDB;')
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_db' AND ENGINE = 'MyISAM'
INTO OUTFILE '/tmp/alter_engine.sql';

然後在 MySQL 中執行:

SOURCE /tmp/alter_engine.sql;

優勢:減少 Shell 與 MySQL 交互開銷,大表轉換時明顯更快。

方案三:存儲過程實現(純 SQL 方案)

DELIMITER $$
CREATE PROCEDURE `sp_alter_db_engine`(
  IN db_name VARCHAR(255), 
  IN new_engine VARCHAR(255)
)
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE tbl_name VARCHAR(255);
  DECLARE cur CURSOR FOR 
    SELECT TABLE_NAME FROM information_schema.TABLES 
    WHERE TABLE_SCHEMA = db_name AND ENGINE != new_engine;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO tbl_name;
    IF done THEN LEAVE read_loop; END IF;
    
    SET @sql = CONCAT('ALTER TABLE `', db_name, '`.`', tbl_name, '` ENGINE=', new_engine);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END LOOP;
  CLOSE cur;
END$$
DELIMITER ;

-- 調用示例
CALL sp_alter_db_engine('your_db', 'InnoDB');

驗證與注意事項

  • 轉換後執行:SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA='your_db'; 確認結果
  • 大表轉換會鎖表,建議低峰期操作或使用 pt-online-schema-change
  • 轉換前務必備份數據
  • 外鍵約束表需先刪除外鍵再轉換
copyright © 萬盛學電腦網 all rights reserved