数据库 单表 13 亿记录创建索引需要多长时间？
这个问题来自 twitter 上一个网友的问题：
https://twitter.com/laixintao/status/1312963759346860037
万推，对 13 亿的 IP 地址（已经转成 int 存储）在 MySQL 中建立索引最快的方法是什么啊？不要求在线，可以锁表可以停机。机械硬盘... 64C256G 配置...
有生之年跑不完的样子...
一堆网友说要分表，还有网友给出了分表之后估算时间，64C256G 的服务器要 24 小时跑完。
13 亿 IP 地址，必须要做读写分离了，读库也应该分表了。建议 5000W 一张表。如果 sharding 算法速度快的话，建议 1000W 一张表。1000W 数据停机建立索引，10 分钟应该可以了。你 64C，估算 130 张表 24 小时可以跑完。
最后原 PO 给出了在自己的 64C256G 服务器上跑的结果，34 分钟左右跑完：
这个话题不错，拿来测试一下挺有意思，在 vultr 上买了一个 8C32G 的服务器，大概每小时 0.2 刀，也不贵...
顺便记录一下测试的脚本，方便感兴趣的可以验证。
安装 Postgres 13 和 Ubuntu 20.04：https://hackershare.dev/cn/bookmarks/4978
初始化表结构：
CREATE unlogged TABLE "test" (
"id" SERIAL PRIMARY KEY NOT NULL,
"ip" integer NOT NULL,
"domain" varchar DEFAULT 'drawerd.com'
);
插入 13 亿条记录：
insert into test(ip) select generate_series(1,1280000000);
插入过程耗时挺久的，大概一个多小时，这里图简单，没做什么优化。感兴趣的可以优化插入速度，比如先把主键索引关掉，用脚本生成 csv，再 copy。
\dt+ test
List of relations
Schema | Name | Type | Owner | Persistence | Size | Description
--------+-------+-------+----------+-------------+-------+-------------
public | test | table | postgres | unlogged | 62 GB |
创建索引：
SET maintenance_work_mem to '30GB';
SET max_parallel_maintenance_workers to 8;
ALTER TABLE test SET (parallel_workers = 8);
执行创建语句：
CREATE INDEX idx ON test (ip);
最终 24 分钟跑完。如果和原 PO 同样配置 64C256G 的话，开 64 个并行，按线性算大概 3 分钟跑完，但实际应该达不到，并行切换和调度还会有很大开销，但至少比目前的 8C32G 的要快很多。
最后又测了一下 Postgres 内建的 partition，看看对创建索引会不会起到加速的作用，测试的结果是 18 分钟左右：
CREATE unlogged TABLE "ptest" (
"id" SERIAL PRIMARY KEY NOT NULL,
"ip" integer NOT NULL,
"domain" varchar DEFAULT 'drawerd.com'
) partition by hash(id);
create table dept_1 partition of ptest FOR VALUES WITH (MODULUS 8, REMAINDER 0);
create table dept_2 partition of ptest FOR VALUES WITH (MODULUS 8, REMAINDER 1);
create table dept_3 partition of ptest FOR VALUES WITH (MODULUS 8, REMAINDER 2);
create table dept_4 partition of ptest FOR VALUES WITH (MODULUS 8, REMAINDER 3);
create table dept_5 partition of ptest FOR VALUES WITH (MODULUS 8, REMAINDER 4);
create table dept_6 partition of ptest FOR VALUES WITH (MODULUS 8, REMAINDER 5);
create table dept_7 partition of ptest FOR VALUES WITH (MODULUS 8, REMAINDER 6);
create table dept_8 partition of ptest FOR VALUES WITH (MODULUS 8, REMAINDER 7);
除了建表语句不一样，其他都和上面一样操作。
"insert into test(ip) select generate_series(1,1280000000);"
这个地方插入的时候实际上可以认为是有序的，对性能可能有正面影响，order by random 乱序之后可能测试的更真实一些。
不过对于 pg 的 heap 表存储来说应该影响不大，对于 mysql 的 b 树来说可能会有一定的影响
其实导入数据确实是最麻烦的，我花了 4 个小时，方法是这样的，不知道大家有没有更好的方法：
数据是公开的，压缩的是 11G，解压是 ~129G，每一行是一个 json，就 ip，domain，type 这几个字段。
我是先 split -l 1000000 file，1 千万行一个文件，大约 130 个文件。
然后批量转换成 mysql load 需要的 csv，（让 server 直接去 load 本地的文件应该是最快的，文档说比 insert 快 20 倍左右 https://dev.mysql.com/doc/refman/8.0/en/insert-optimization.html ，一开始尝试一行一行 insert，大约 20 行/s，实在太慢了，然后一次 insert 一万行，依然很慢）。
先写好这个脚本：
cat scripts/json2csv.py
import fileinput
import json
import ipaddress
import csv
import sys
writer = csv.writer(sys.stdout, quoting=csv.QUOTE_ALL)
for line in fileinput.input():
data = json.loads(line)
ip = int(ipaddress.IPv4Address(data.get('name')))
value = data.get('value')
t = data.get('type')
writer.writerow([ip, value, t])
然后开 130 个进程一起跑，毕竟是 64C 机器
for sf in $(ls source_file)
do
cat source_file/$sf | python3 scripts/json2csv.py > target_csv/$sf.csv &
done
最后在 mysql server 上执行 load data infile 语句，大约跑了 4 个小时。
然后我以为建索引毕竟也是顺序写，估计也有 4 个小时。就去 twitter 求助了... 结果半个小时竟然神器地跑完了...
laixintao
回复
mysql load infile 就是最快的了，再优化就是调参了 之前记录过 http://shopperplus.github.io/blog/2014/11/08/fastest-way-to-load-data-in-mysql.html
numbcoder
回复
查询网上好多基于 tpc-x 数据集的对比了 差别不大 会有差别的是 join 和聚合查询 这种 pg 一些场景是可以开并行的 多核机器上有优势
请教所以如何预测创建索引需要多少时间呢？
把线上一部分数据 dump 下来，然后在类似配置机器上把数据集的 1/8、1/4、1/2 的部分进行创建索引。然后根据增长的趋势预测真实创建完索引大概需要多少时间？
ceclinux-github
回复
一般都是在小数据上测，然后乘以系数，比如你可以测 100w 的，如果你真实数据是 1 亿，就乘以 100
最实际的办法还是 如果你有闲置服务器，模拟一下数据量，实际跑一遍
laixintao
回复
在 96 核、192G 内存的机器上，Ubuntu 18, mysql-server 5.7，没有调参数优化。13 亿行数据。
Load 到数据库花了大概 1 小时。
mysql> LOAD DATA INFILE '/data/mysql/ii.csv' INTO TABLE `ip` FIELDS TERMINATED BY ',' ;
Query OK, 1280000000 rows affected (1 hour 9 min 55.01 sec)
Records: 1280000000 Deleted: 0 Skipped: 0 Warnings: 0
加索引花了大概 40 分钟：
mysql> alter table `ip` add key `idx_ip` (`ip`);
Query OK, 0 rows affected (43 min 54.54 sec)
Records: 0 Duplicates: 0 Warnings: 0
观察下来，CPU 核心只用到了两个。主要瓶颈还在磁盘 IO 上。
hooopo
回复
是的。磁盘压力比较大。
70521 be/4 mysql 924.43 K/s 280.63 M/s 0.00 % 0.68 % mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid
测试环境的物理机，HDD。
同样配置，我又用 postgresql 试了一下。
- create table 没加 unlogged
- cerate index 的 workers 设置到 32 个。
导入数据用 copy 从 csv 复制，忘记开 \timing，不过大概也是 30-40 分钟。 加索引耗时大概 13 分钟。
postgres=# CREATE INDEX idx ON test (ip);
Time: 781496.452 ms (13:01.496)
试了。瓶颈在磁盘的情况下，用并行 copy 或者 直接 copy 时间差别不大。
at 30s, row rate 666546.92/sec (period), row rate 666546.92/sec (overall), 2.000000E+07 total rows
at 1m0s, row rate 653333.72/sec (period), row rate 659940.91/sec (overall), 3.960000E+07 total rows
at 1m30s, row rate 530000.43/sec (period), row rate 616630.03/sec (overall), 5.550000E+07 total rows
at 2m0s, row rate 546666.11/sec (period), row rate 599139.82/sec (overall), 7.190000E+07 total rows
at 2m30s, row rate 546659.98/sec (period), row rate 588644.12/sec (overall), 8.830000E+07 total rows
at 3m0s, row rate 530004.70/sec (period), row rate 578871.27/sec (overall), 1.042000E+08 total rows
at 3m30s, row rate 543335.38/sec (period), row rate 573794.86/sec (overall), 1.205000E+08 total rows
at 4m0s, row rate 523331.66/sec (period), row rate 567487.09/sec (overall), 1.362000E+08 total rows
4 分钟插入了一亿多条数据，13 亿差不多也得 40 分钟左右。
但是，关闭日志（建表时 unlogged), 速度提升很大：
at 30s, row rate 1826650.79/sec (period), row rate 1826650.79/sec (overall), 5.480000E+07 total rows
at 1m0s, row rate 2005766.79/sec (period), row rate 1916228.49/sec (overall), 1.150000E+08 total rows
at 1m30s, row rate 1990897.15/sec (period), row rate 1941106.77/sec (overall), 1.747000E+08 total rows
一亿多条数据，只需要 1 分钟左右。
综合看，如果用 HDD，瓶颈大概率在硬盘这里，关闭日志可以提升写性能。原推可能用了比较慢的硬盘。 回头有空，找一台 SSD 的物理服务器测试下。
