多sed语句处理大SQL文件时运行缓慢甚至失败的优化咨询

我有多个大尺寸（12G、5.9G、1.1G、57M）的SQL文件，需处理后才能通过MySQL-Shell成功导入。这些文件的生成方式不可控，是压缩后交付给我的。仅使用1-2条sed语句处理时效果良好，但使用多条sed语句（具体脚本如下）处理时，运行会变得极其缓慢，有时甚至失败，且所有这些sed语句都是必需的。我使用的是配备40核CPU、252G内存的高性能机器，因此排除硬件问题。恳请有人推荐实现大量替换操作的最佳方式，或是合适的替代工具，非常感谢。

处理脚本：

ls -Sr ${_WORKING_DIR} | grep -P "^_insert_.*_.*.sql$" \
 | grep -v "_indexlog\|_sessions" \
 | xargs grep -l "),(" \
 | while read F; do \
 sed -E -i \
 -e 's/\),\(/\n/g' \
 -e "s/','/', '/g" \
 -e "s/^INSERT\sINTO\s\`.*\`\sVALUES\s*\((.*)\);$/\1/g" \
 -e "s/, null/, NULL/g" \
 -e 's/,NULL,/, NULL,/g' \
 -e "s/NULL,'/NULL, '/g" \
 -e "s/NULL,NULL/NULL, NULL/g" \
 -e "s/,NULL$/, NULL/g" \
 -e "s/',NULL/', NULL/g" \
 -e "s/',1,'/', 1, '/g" \
 -e "s/',0,'/', 0, '/g" \
 -e "s/('.*',)([0-9])/\1 \2/g" \
 -e "s/^([0-9]{0,9},)('.*)$/\1 \2/g" \
 -e "s/^(.*',)[^\s]([0-9]{0,9}.*)$/\1 \2/g" \
 -e "s/,1,/, 1, /g" \
 -e "s/(.*[0-9]{1}),'/\1, '/g" \
 -e "s/([0-9]{1}),([0-9]{1})/\1, \2/g" \
 -e "s/NULL,([[:digit:]])/NULL, \1/g" \
 $F; \
 echo "File has been worked with sed: $F" >> ${_LOG_FILE}; \
 done

示例文件内容：

INSERT INTO `sessions` VALUES ('6799bfac-e716-4a23-b5f3-ac4aac4811d1', '9Du3Bn3cNPmKVZqqDgRz', 'null', 'null', '1', '2023-04-25',null, NULL),('f9f30fe2-d88c-4420-afc0-769fc1f745fb', '9Du3Bn3cNPmKVZqqDgRz', 'null', 'null','1', '2023-04-25', null, '{HUGE_JSON_ENTRY}');
INSERT INTO `sessions` VALUES ('1b9b0452-1c53-4466-92b2-c3373ce4ab67', '9Du3Bn3cNPmKVZqqDgRz', null, 'null', '1', '2023-04-25',null, 'null'),('1ef7d5d7-b795-4e4c-a29d-e16ba880a522', '9Du3Bn3cNPmKVZqqDgRz', 'null','null', '1', '2023-04-25', null, '{HUGE_JSON_ENTRY}');
INSERT INTO `sessions` VALUES ('d6529933-0e18-426c-8793-9d1711d0c0aa', '4EBdY2RT5xC9fc9rfqnz', 'null', null,'1', '2023-04-25', null, 'null');
....

核心问题是sed每条-e指令都会重新扫描整个文件，18条指令意味着文件被重复读写18次，大文件下效率必然低下。以下是几种优化方案：

1. 改用awk实现单次扫描替换 #

awk可以在一次遍历中完成所有替换，避免多次文件IO，效率提升明显。GNU awk 4.1.0+支持-i inplace原地修改文件，和sed的-i效果一致：

ls -Sr ${_WORKING_DIR} | grep -P "^_insert_.*_.*.sql$" \
| grep -v "_indexlog\|_sessions" \
| xargs grep -l "),(" \
| while read F; do \
awk -i inplace '
BEGIN {
    rules[1] = ["\\),\\(", "\n"];
    rules[2] = ["'\'','\''", "'\'', '\''"];
    rules[3] = ["^INSERT\\sINTO\\s`.*`\\sVALUES\\s*\\((.*)\\);$", "\\1"];
    rules[4] = [", null", ", NULL"];
    rules[5] = [",NULL,", ", NULL,"];
    rules[6] = ["NULL,'\''", "NULL, '\''"];
    rules[7] = ["NULL,NULL", "NULL, NULL"];
    rules[8] = [",NULL$", ", NULL"];
    rules[9] = ["'\'',NULL", "'\'', NULL"];
    rules[10] = ["'\'',1,'\''", "'\'', 1, '\''"];
    rules[11] = ["'\'',0,'\''", "'\'', 0, '\''"];
    rules[12] = ["('\''.*'\'',)([0-9])", "\\1 \\2"];
    rules[13] = ["^([0-9]{0,9},)('\''.*)$", "\\1 \\2"];
    rules[14] = ["^(.*'\'',)[^\\s]([0-9]{0,9}.*)$", "\\1 \\2"];
    rules[15] = [",1,", ", 1, "];
    rules[16] = ["(.*[0-9]{1}),'\''", "\\1, '\''"];
    rules[17] = ["([0-9]{1}),([0-9]{1})", "\\1, \\2"];
    rules[18] = ["NULL,([[:digit:]])", "NULL, \\1"];
}
{
    line = $0;
    for (i in rules) {
        gsub(rules[i][1], rules[i][2], line);
    }
    print line;
}' "$F";
echo "File has been processed with awk: $F" >> ${_LOG_FILE}; \
done

2. 合并sed的替换规则（次优方案） #

如果坚持用sed，把同类型的替换规则用分号合并到同一条-e指令中，减少文件扫描次数：

ls -Sr ${_WORKING_DIR} | grep -P "^_insert_.*_.*.sql$" \
| grep -v "_indexlog\|_sessions" \
| xargs grep -l "),(" \
| while read F; do \
sed -E -i \
-e 's/\),\(/\n/g' \
-e "s/','/', '/g" \
-e "s/^INSERT\sINTO\s\`.*\`\sVALUES\s*\((.*)\);$/\1/g" \
-e "s/, null/, NULL/g; s/,NULL,/, NULL,/g; s/NULL,'/NULL, '/g; s/NULL,NULL/NULL, NULL/g; s/,NULL$/, NULL/g; s/',NULL/', NULL/g" \
-e "s/',1,'/', 1, '/g; s/',0,'/', 0, '/g" \
-e "s/('.*',)([0-9])/\1 \2/g" \
-e "s/^([0-9]{0,9},)('.*)$/\1 \2/g" \
-e "s/^(.*',)[^\s]([0-9]{0,9}.*)$/\1 \2/g" \
-e "s/,1,/, 1, /g" \
-e "s/(.*[0-9]{1}),'/\1, '/g" \
-e "s/([0-9]{1}),([0-9]{1})/\1, \2/g" \
-e "s/NULL,([[:digit:]])/NULL, \1/g" \
$F; \
echo "File has been worked with sed: $F" >> ${_LOG_FILE}; \
done

3. 改用Perl实现高效替换 #

Perl的正则处理性能优异，支持原地修改，语法和sed接近，适合批量替换：

ls -Sr ${_WORKING_DIR} | grep -P "^_insert_.*_.*.sql$" \
| grep -v "_indexlog\|_sessions" \
| xargs grep -l "),(" \
| while read F; do \
perl -i -pe '
s/\),\(/\n/g;
s/\'\'\,\'\'/\'\'\,\ \'\'/g;
s/^INSERT\sINTO\s`.*`\sVALUES\s*\((.*)\);$/$1/g;
s/\, null/\, NULL/g;
s/\,NULL\,/\, NULL\,/g;
s/NULL\,\'\'/NULL\,\ \'\'/g;
s/NULL\,NULL/NULL\,\ NULL/g;
s/\,NULL$/\, NULL/g;
s/\'\'\,NULL/\'\'\,\ NULL/g;
s/\'\'\,1\,\"\'/\'\'\,\ 1\,\ \'\'/g;
s/\'\'\,0\,\"\'/\'\'\,\ 0\,\ \'\'/g;
s/(\'\'\.*\'\'\,)([0-9])/$1 $2/g;
s/^([0-9]{0,9}\,)(\'\'\.*)$/$1 $2/g;
s/^(.*\'\'\,)[^\s]([0-9]{0,9}.*)$/$1 $2/g;
s/\,1\,/\, 1\, /g;
s/(.*[0-9]{1})\,\'\'/$1\,\ \'\'/g;
s/([0-9]{1})\,([0-9]{1})/$1\,\ $2/g;
s/NULL\,([[:digit:]])/NULL\,\ $1/g;
' "$F";
echo "File has been processed with perl: $F" >> ${_LOG_FILE}; \
done

• 替换ls | grep | xargs组合为find命令，更高效且避免文件名含特殊字符的问题：

  find ${_WORKING_DIR} -type f -name "_insert_*_*.sql" ! -name "*_indexlog*" ! -name "*_sessions*" -exec grep -l "),(" {} + | while read F; do ...; done
  

• 超大文件建议先解压再处理，避免边解压边处理的额外开销；也可以用split按行分块处理后再合并，注意保证SQL语句完整性。

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