@brader 我试了下，大致有以下结论：

1. 即使我用 text 类型存 "1,2,3,…"（ latin1 编码），无论是 700 / 7000 字节，find_in_set 都是 index

看来 MySQL 还不足够聪明优化 find_in_set

我还以为会生成个临时表，然后 FROM b JOIN a ON a.id IN tmp_table_of_a_ids 呢。。


2. JSON_TABLE 根据 "1,2,3,…" 生成表，再 join 表 a 是 eq_ref ，效率看来不错


3. 表 b 即使插入两行有 7000 字节的 ids 的行记录，也都在同一页（ 16KB ），不用担心查找溢出页导致的效率问题


4. 你说的方法二（新增 C 表），我试了下，插入一千万行(a_id, b_id)，磁盘占用 272.8 MB ，平均每行占用约 29 字节

看了下书，主要是每行数据额外记录了（记录头信息 5 字节 + 事务 ID 列 6 字节+ 回滚指针列 7 字节）= 20 字节，然后才是 (int, int) 的 8 字节，所以 C 表其实空间利用率很低

若是用 "1,2,3,…" 存储，即使每个 a_id 是 8 个数字+1 个逗号，一千万个记录也才 9 * (10 ^ 7) / (1 << 20) ≈ 85.8 MB

可即使是 85.8 MB ，楼主也说“计算量小，但是耗地方”

所以 C 表更不符合楼主要求


SET SESSION group_concat_max_len = 8192;
SET SESSION cte_max_recursion_depth = 2048;

CREATE TABLE `a` (
　`id` int NOT NULL AUTO_INCREMENT,
　`time` date NOT NULL,
　`data` int NOT NULL,
　`user` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
　 PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

CREATE TABLE `b` (
　`id` int NOT NULL AUTO_INCREMENT,
　`user` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
　`sum_data` int NOT NULL,
　`a_ids` text CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
　 PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

INSERT INTO a (time, data, user)
WITH RECURSIVE
　 generate_series(i) AS (
　　 SELECT 0
　　 UNION ALL
　　 SELECT i+1 FROM generate_series WHERE i < 1600
　)
SELECT DATE_ADD('2021-01-01', INTERVAL i DAY), i + 1, 'a'
　 FROM generate_series
WHERE i < 199 　-- ids 长度为 2*9(1~9,) + 3*90(10~99,) + 4*100(100~199,) -1(末尾逗号) = 687 B
UNION ALL
SELECT DATE_ADD('2021-01-01', INTERVAL i DAY), i + 1, 'b'
　 FROM generate_series
WHERE i <= 1760 - 200 　-- ids 长度为 4*800(200~999,) + 5*761(1000~1760,) -1 = 7004 B
UNION ALL
SELECT DATE_ADD('2021-01-01', INTERVAL i DAY), i + 1, 'c'
　 FROM generate_series
WHERE i <= 3240 - 1761;　-- ids 长度为 5*1480(1761~3240,) -1 = 7399 B

INSERT INTO b (user, sum_data, a_ids)
SELECT user, sum(data), GROUP_CONCAT(id)
　 FROM a
GROUP BY user;

EXPLAIN
SELECT a.id
　 FROM b
　 JOIN a ON FIND_IN_SET(a.id, b.a_ids)
WHERE b.user = 'c';

EXPLAIN
SELECT a.*
　 FROM b,
　　　 JSON_TABLE(
　　　　　 CONCAT('[', b.a_ids, ']'),
　　　　　 '$[*]' COLUMNS (id INT PATH '$')
　　　 ) AS ids
　 JOIN a USING(id)
WHERE b.user = 'c';


C 表测试：

SET SESSION cte_max_recursion_depth = 1 << 31;

CREATE TABLE `c` (
　`a_id` int NOT NULL,
　`b_id` int NOT NULL,
　 PRIMARY KEY (`a_id`, `b_id`)
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

INSERT INTO c
WITH RECURSIVE
　 generate_series(i) AS (
　　 SELECT 1
　　 UNION ALL
　　 SELECT i+1 FROM generate_series WHERE i <= 10000000
　)
SELECT i, i
　 FROM generate_series;

@brader

1. 我查了下，你是说方法一会扫描表 a 整个聚集索引才得出结果？（这么傻的么。。）

有没有将 a_indexes 变成一张表再去 join 的方法（ split 后成为一张表）？我试着用了一下 json_table ，应该可行？

SELECT a.*
FROM b, JSON_TABLE(
CONCAT('[', b.a_ids, ']'),
'$[*]' COLUMNS (id INT PATH '$')
) AS ids JOIN a USING (id)
WHERE b.id = 1;


2.1 不知道😳


2.2 确实要扩充 a_ids 的长度，但不知你说的“索引失效”何意？超过 767 字节时，只是行溢出到其他页上？

而且，我读了下《 MySQL 技术内幕：InnoDB 存储引擎（第二版）》 111-116 页，大意是：

Compact 格式下，只要保证一页至少有两行数据，VARCHAR 、TEXT 、BLOB 可以不溢出到其他页（一页 16KB 情况下，大概是一行不超过 8098 字节？）。若溢出了，前 768 字节存在数据页上，其余在溢出页

@brader 数据库新人求问，为何方法二高效？

方法一查表 B 得到 a_indexes （如"0,1,2"），然后去 join 表 A 得到各自数据，好像这个思路看起来也不慢？

SELECT a.*
FROM b JOIN a ON FIND_IN_SET(a.index, b.a_indexes)
WHERE b.index = 0

是这样实现吗？应该足够聪明，不是扫 a 全表吧？

@mylovesaber 不是大佬，只是被 bash 折磨过一阵

原文有些错误，后面也有人纠正了，注意甄别

1. 对于 7s 部分的代码，每测试一个含 : 的参数，你就要开启 2 + 614 * 6 = 3686 次子进程。。。

2. awk 的初始化部分，应该在外部传递参数。如 -v keywords="$(IFS=:; echo "${MySQLKeywords[*]}")"

不能 xxx.sh 是因为没有执行权限？好奇你 bash xxx.sh 会发生啥

@aloxaf 嗯，$() 会启动 sub shell ，但 | 只是保证各个命令会在独立进程中执行吧

> Each command in a pipeline is executed as a separate process (i.e., in a subshell)

1. 如 sleep 2 | sleep 3：

/init
└─ -bash
　 ├─ sleep 2
　 └─ sleep 3

2. 而 f() { sleep 2; }; f | sleep 3：

/init
└─ -bash
　 ├─ -bash
　 │　└─ sleep 2
　 └─ sleep 3

至于你说的“<<< 只是单纯的重定向”，是其 stdin 重定向至(临时)文件？还是管道（由某个 bash 喂进去）？

@haoliang 试了一下，<<< 和 <<EOF 都实现为 pipe ，< file 则不是

bash -c '[[ -p /dev/stdin ]] && echo piped' <a.txt # 没回应

echo | bash -c '[[ -p /dev/stdin ]] && echo piped' # piped
bash -c '[[ -p /dev/stdin ]] && echo piped' < <(echo 'abc') # piped
bash -c '[[ -p /dev/stdin ]] && echo piped' <<<'abc' # piped
bash -c '[[ -p /dev/stdin ]] && echo piped' <<EOF # piped
abc
EOF

所以应该不是管道导致的慢（大家都用的管道）

也试了下，在 $() 中确实没见到 echo 等 builtin 命令的进程，如：$(read -t 3 | sleep 3)

估计是由 $() 实现了 read ？没启动外部进程，但内部还是执行了相关功能？（如 echo -e 会转义参数里的 \ 内容）

所以 echo | xxx 比 <<< 慢？

@haoliang 请教一下，`echo xx | cmd` 的主要开销如果 pipeline 上的话，<<< 的实现是啥？不是管道传输作为标准输入吗？

为嘛 v 站的回复不能用 markdown 。。

主要是你启动的进程太多，又叠加循环大量关键字了（$() 会开启 sub shell ，你还有一堆 echo 、cut ）

对于 7s 部分的代码，每测试一个含 : 的参数，你就要开启 3 + 614 * 9 = 5529 次子进程。。。


●针对 7s 部分，以一个参数 'asd:f' 逐步进行测试：

1. 使用 <<<xxx 优化掉 echo xxx ，用时为原来的 74%（即 5.2 秒）

GROUP_NAME1=$(cut -d':' -f 1 <<<"$GROUP_NAME")
GROUP_NAME2=$(cut -d':' -f 2 <<<"$GROUP_NAME")
if [ -n "$(cut -d':' -f 3 <<<"$GROUP_NAME")" ]; then

2. 使用 bash 内置的替换功能，来代替 cut ，用时为原来的 30%（即 2.1 秒）

GROUP_NAME1=$(echo "${GROUP_NAME%%:*}")　# 删掉 :.*$
GROUP_NAME2=$(echo "${GROUP_NAME##*:}")　# 删掉 ^.*:
if ([[ $GROUP_NAME =~ ^.*:.*: ]]); then 　# () 用来开启一次 sub shell

3. 取消掉 $()，用时为原来的 1%（即 0.07 秒）

GROUP_NAME1=${GROUP_NAME%%:*}　# 删掉 :.*$
GROUP_NAME2=${GROUP_NAME##*:}　# 删掉 ^.*:
if [[ $GROUP_NAME =~ ^.*:.*: ]]; then
　　_error "非法字符: ${GROUP_NAME#*:*:}"; exit 1


●另外，bash 也内置了大小写转换功能：

declare -a EXTRAARG=("${@,,}")　# 将每个参数的每个字符转为小写
# GROUP_NAME=$(echo "${GROUP_NAME}"|tr "[:upper:]" "[:lower:]")　# 去掉这行


●还可考虑使用关联数组，用时为原来的 0.00071%（原 7 秒 可执行 140000 次）

declare -A dict="($(printf "['%s']= " "${MySQLKeywords[@]}"))"　# 要求关键字里没有 ' 字符，否则老老实实 for

for GROUP_NAME in "${EXTRAARG[@]}";do

　　# 每个参数循环多次测试
　　 for ((i = 0; i < 140000; i++)); do
　　　　# 删掉 :
　　　　 GROUP_NO_SEP=${GROUP_NAME//:}

　　　　# 计算 : 个数（原字符串长度 - 删掉 : 后长度）
　　　　 case $(( ${#GROUP_NAME} - ${#GROUP_NO_SEP} )) in
　　　　　　 0)
　　　　　　　　[[ -v dict[$GROUP_NAME] ]] || continue
　　　　　　　　;;
　　　　　　 1)
　　　　　　　　[[ -v dict[${GROUP_NAME%%:*}] ||
　　　　　　　　　 -v dict[${GROUP_NAME##*:}] ]] || continue
　　　　　　　　;;
　　　　　　*)
　　　　　　　　_error "非法字符: ':${GROUP_NAME#*:*:}'"
　　　　　　　　 exit 1
　　　　　　　　;;
　　　　 esac

　　　　# 出现了关键字
　　　　_error "参数不能为 MySQL 的关键字或保留字！退出中"
　　　　 exit 1
　　 done
done


●或者使用 awk 里的数组，用时为原来的 0.0004%（原 7 秒 可执行 250000 次）

for GROUP_NAME in "${EXTRAARG[@]}";do

　　# 每个参数循环多次测试
　　 for ((i = 0; i < 250000; i++)); do
　　　　 echo "$GROUP_NAME"
　　 done

done | awk -F: '
　　 BEGIN {
　　　　# 要求关键字里不出现 : " 等字符
　　　　 split("'"$(IFS=:; echo "${MySQLKeywords[*]}")"'", arr)
　　　　 for (i in arr) dict[arr[i]] = 1;
　　}

　　{
　　　　 if (NF > 2) {
　　　　　　 print "非法字符: " $3
　　　　　　 exit 1
　　　　}

　　　　 for (i = 1; i <= NF; i++) {
　　　　　　 if ($i in dict) {
　　　　　　　　 print "参数不能为 MySQL 的关键字或保留字！退出中"
　　　　　　　　 exit 1
　　　　　　}
　　　　}
　　}
'

上面写错了，一行索引存为 (uid: 4 Bytes, 主键 id: 4 Bytes)

@sadfQED2 会不会他 uid 没加索引。。

即使一行索引只是存为 (uid: 4 Bytes, 页号: 4 Bytes)，给 uid 加索引，至少也需要 (10 ^ 9) * 8 Bytes ≈ 7.5 GB 磁盘？

感觉 1 楼说的 bitmap 去重可行啊

如果 UID 为 4 字节，bitmap 需要 (2 ^ 32) bits / 8 (bits/Byte) = 512 MB 内存 /磁盘，

如果 UID 极差不超过 2 ^ 30 （约 10 亿 7300 万），bitmap 只需要 128 MB 内存 /磁盘

@wanchenyi 8 楼的应该还不是完整的答案，除非：

1. 不要最后一组数据了
2. 保证 name 和 id 里的值不会出现一些如 $ ` 的特殊字符，否则会出现任意执行代码漏洞

如 id: "恶意代码：$(sleep 300)" 可以让你的 shell 卡五分钟

靠，tree 还用了不是普通空格的空格。。

上面写漏了，应该是：

/
├── 归档
│   ├── A 项目 6 月份月报.docx
│   ├── A 项目 7 月份月报.docx
│   ├── B 项目 6 月份月报.docx
│   └── B 项目 7 月份月报.docx
├── 月份
│   ├── 6
│   │   ├── A 项目月报.docx.lnk
│   │   └── B 项目月报.docx.lnk
│   └── 7
│  　　 ├── A 项目月报.docx.lnk
│  　　 └── B 项目月报.docx.lnk
└── 项目
　　├── A
　　│   ├── 6 月份月报.docx.lnk
　　│   ├── 7 月份月报.docx.lnk
　　│   └── （如果在此新建“8 月份月报.docx”，自动移到“归档 /A 项目 8 月份月报.docx”，此处变成“8 月份月报.docx.lnk”）
　　└── B
　　　　├── 6 月份月报.docx.lnk
　　　　└── 7 月份月报.docx.lnk

感觉你说的第二点可行。写个小脚本，对现存的文件自动生成快捷方式 /软链接 /硬链接，咋样？

如现存文件：
A 项目 6 月份月报.docx
A 项目 7 月份月报.docx
B 项目 6 月份月报.docx
B 项目 7 月份月报.docx

自动生成（把两个半角空格转成一个全角了，不知道还会不会吞空格）：
根
├── 月份
│　 ├── 6
│　 │　 └── A 项目月报.docx.lnk
│　 └── 7
│　　　 └── B 项目月报.docx.lnk
└── 项目
　　├── A
　　│　 └── 6 月份月报.docx.lnk
　　└── B
　　　　└── 7 月份月报.docx.lnk

如果在某个生成的目录中增加 /重命名 /修改文件，也自动同步回归档目录中？

@ruidoBlanco 我是 v 站 新人，不太熟悉这儿的风气，不知道回答问题是否该考虑全一点，还是大致给个方向即可

我觉得，字符串里有 " 是合理且可预见的情况，还是该做处理的，仍属于帖子“提取对应值”主题范畴

（所以，只是“答得有没有更全面一点”的区别？）

“name 可以有重复”，这是上一层的工作了吧。另外，楼主也说了“每个 name 和 id 是不一样的”

@ruidoBlanco

按 " 分割的话，值包含 " 就不好办了（ name: "my name is \"xxx\""）

另外，最后一行不是------的话，最后一组值也没了

不懂 awk 如何限制最多分割的列数（比如，按“:”分割，最多两列，最后一列可以包含任意个“:”），只能用正则来匹配了

假设 name 或 id 的值符合 json 的字符串规范

awk -v FPAT='(\\w+)|:|"(\\\\?.)*"' 'function output_dict() {if (length(dict)) {printf "%s\0%s\0", dict["name"], dict["id"]; delete dict }} /^\w/{dict[$1]=$3} /^-/{output_dict()} END{output_dict()}' | xargs -0 -n 2 echo


[输入]
name: "name1"
id: "id1"
------
id: "\"id2\": "
name: ": \"name2\""


[输出（要由后面的命令去转义了）]
"name1" "id1"
": \"name2\"" "\"id2\": "


[实际执行]
echo '"name1"' '"id1"'
echo '": \"name2\""' '"\"id2\": "'

@kkkiio 树结构庞大，CTE 比不过原因是啥？查 parent_id 索引难命中缓存？

那加个 root_id 字段，索引 (root_id, parent_id)，是不是同一棵树的索引都尽量集中到一块儿去就好了

@n0trace 邻接表类型，移动节点，不是一条 update xxx set parent_id = ? 即可吗