選擇合適的存儲引擎對性能至關重要。常見引擎特性對比:
| 引擎 | 事務支持 | 鎖粒度 | 典型場景 |
|---|---|---|---|
| InnoDB | ✅ 支持 | 行級鎖 | 高併發寫入、關鍵業務數據 |
| MyISAM | ❌ 不支持 | 表級鎖 | 只讀/多讀少寫、全文搜索 |
| Memory | ❌ 不支持 | 表級鎖 | 臨時表、緩存數據 |
| Archive | ❌ 不支持 | 行級鎖 | 日志歸檔、海量歷史數據 |
本文演示如何將數據庫中所有 MyISAM 表批量轉換為 InnoDB。
ALTER TABLE table_name ENGINE=InnoDB#!/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 行,運行時將提示輸入參數,適合臨時操作。
利用 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 交互開銷,大表轉換時明顯更快。
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