我是如何用2个Unix命令给MariaDB SQL提速的
来源：互联网 2026-04-14 13:02:31译者 | 薛命灯 近期，在处理一个基于MariaDB（兼容MySQL）的简单连接查询时，我们遇到了极其严重的性能挑战。原本的查询计划预估耗时高达380小时，但最终借助两个经典的Unix命令，成功将实际执行时间压缩至12小时以内。下面将还原整个问题的解决思路与操作过程。 该查询隶属于GHTorrent
译者 | 薛命灯
近期，在处理一个基于MariaDB（兼容MySQL）的简单连接查询时，我们遇到了极其严重的性能挑战。原本的查询计划预估耗时高达380小时，但最终借助两个经典的Unix命令，成功将实际执行时间压缩至12小时以内。下面将还原整个问题的解决思路与操作过程。
该查询隶属于GHTorrent分析项目，通过simple-rolap这一关系型在线分析处理框架执行。查询语句的结构本身并不复杂：
长期稳定更新的攒劲资源： >>>点此立即查看<<<
select distinct project_commits.project_id, date_format(created_at, ‘%x%v1') as week_commit from project_commits left join commits on project_commits.commit_id = commits.id;
尽管参与连接的两个字段均已建立索引，但通过EXPLAIN命令分析执行计划发现，MariaDB依然选择对project_commits表进行全表扫描，并尝试使用索引去匹配commits表。这种操作方式，在庞大的数据量面前，逐渐暴露出性能瓶颈。
问题的症结在于数据规模：project_commits表约有50亿行记录，commits表也高达8.47亿行。而服务器的内存容量仅为16GB。显然，如此海量的索引无法被内存容纳，导致大量的磁盘I/O操作，这成为了拖慢查询速度的根本原因。从pmonitor工具对临时表的监控数据来看，该查询已运行半天，但预估剩余时间竟长达373小时。
/home/mysql/ghtorrent/project_commits#P#p0.MYD 6.68% ETA 373:38:11
这一预估时间显然不尽合理。从理论上分析，如果采用排序合并连接（sort-merge join）策略，其I/O开销应该比当前执行计划低一个数量级。为此，我们曾在dba.stackexchange.com上向技术社区寻求帮助，并获得了一些优化建议。然而，在尝试首个建议后效果并不明显，且每个建议的验证周期都需要半天左右。鉴于时间紧迫，我们决定启动一套更有把握的备选方案。
核心思路十分直接：将两个表的相关数据导出为文本文件，利用Unix系统自带的join命令进行外部连接，然后通过uniq命令去除结果中的重复行，最后再将处理后的数据导回数据库。整个数据导入与索引重建过程从晚间20:41开始，持续到次日上午9:53结束。具体操作可分为三个步骤：
1. 将数据库表导出为文本文件
首先，导出连接操作所需的字段，并按照连接字段进行排序。为了确保与后续Unix命令行工具兼容，需要将数值型的ID字段转换为字符类型。
执行以下SQL语句，并将输出结果保存至commits_week.txt文件：
select cast(id as char) as cid, date_format(created_at, ‘%x%v1') as week_commit from commits order by cid;
执行以下SQL语句，并将输出结果保存至project_commits.txt文件：
select cast(commit_id as char) as cid, project_id from project_commits order by cid;
最终，我们得到了两个体积庞大的文本文件：
-rw-r–r– 1 dds dds 15G Aug 4 21:09 commits_week.txt
-rw-r–r– 1 dds dds 93G Aug 5 00:36 project_commits.txt
需要注意，在运行mysql客户端命令时，我们添加了--quick选项。这个选项可以防止客户端在输出全部结果前尝试缓存所有记录，从而避免可能发生的内存溢出问题。
2. 使用 Unix 命令行工具处理文件
接下来，就是Unix工具展现高效能力的时刻。我们使用join命令来连接两个已排序的文本文件。该命令会线性扫描双方文件，依据第一字段（即cid）进行匹配与组合，其处理速度主要取决于磁盘的I/O性能。之后，将连接结果通过管道传递给uniq命令，轻松剔除重复行，这恰好等效于原SQL语句中的DISTINCT操作。对于已经排序的数据，去重同样只是一次高效的线性扫描。
我们执行的命令简洁而有力：
join commits_week.txt project_commits.txt | uniq > joined_commits.txt
大约一小时后，目标文件顺利生成：
-rw-r–r– 1 dds dds 133G Aug 5 01:40 joined_commits.txt
3. 将文本文件导回数据库
最后一步，将处理完毕的数据导入到新的数据库表中：
create table half_life.week_commits_all ( project_id INT(11) not null, week_commit CHAR(7) ) ENGINE=MyISAM; load data local infile ‘joined_commits.txt' into table half_life.week_commits_all fields terminated by ‘ ';
结语
理想情况下，MariaDB的优化器应当能够智能地选择排序合并连接这类高效策略，并在预测到当前执行计划耗时过高时主动进行切换。然而，在数据库内核尚未完善此功能之前，这套源于上世纪70年代的Unix工具组合，已经为我们提供了一个高效且可靠的解决方案。这再次印证了一个道理：有时，最简单的工具恰恰能攻克最复杂的难题。
侠游戏发布此文仅为了传递信息，不代表侠游戏网站认同其观点或证实其描述
