MySQL 加入大数据最佳实践

Question

table1_shard1(每个分片 1,000,000 行 x 120 个分片)

 id_user   hash

表 2(100,000 行)

 value    hash

期望的输出:

 id_user  hash    value

我正在尝试从上表中找到将 id_user 与 value 相关联的最快方法。

我当前的查询运行了 30 小时没有结果。

SELECT 
    table1_shard1.id_user, table1_shard1.hash, table2.value 
FROM table1_shard1 
LEFT JOIN table2 ON table1_shard1.hash=table2.hash 
GROUP BY id_user
UNION
SELECT 
    table1_shard2.id_user, table1_shard2.hash, table2.value 
FROM table1_shard1 
LEFT JOIN table2 ON table1_shard2.hash=table2.hash 
GROUP BY id_user
UNION 
( ... )
UNION 
SELECT 
    table1_shard120.id_user, table1_shard120.hash, table2.value 
FROM table1_shard1 
LEFT JOIN table2 ON table1_shard120.hash=table2.hash 
GROUP BY id_user

Answer 1

首先，hash 字段是否有索引

我认为你应该在查询之前将你的表合并为一个(至少暂时)

CREATE TEMPORARY TABLE IF NOT EXISTS tmp_shards
SELECT * FROM table1_shard1;

CREATE TEMPORARY TABLE IF NOT EXISTS tmp_shards
SELECT * FROM table1_shard2;

# ...

然后做主查询

SELECT
  table1_shard120.id_user
, table1_shard120.hash
, table2.value
FROM tmp_shards AS shd
LEFT JOIN table2 AS tb2 ON (shd.hash = tb2.hash)
GROUP BY id_user
;

不确定性能提升，但它至少更易于维护。