一次让我加班的慢SQL事故
去年双11大促后，运营扔给我一个需求：统计所有参与秒杀活动的用户，在活动结束后第1天、第3天、第7天的留存率。活动参与用户大概 2600 万，分布在 200 多个分表里。我第一个版本用 SQL 直接写：
-- 查询参与活动的用户次日是否登录
SELECT COUNT(DISTINCT a.user_id) AS retained_users
FROM activity_users a
INNER JOIN user_login_log l
ON a.user_id = l.user_id
AND l.login_date = DATE_ADD(a.activity_date, INTERVAL 1 DAY)
WHERE a.activity_id = 88231;
这条 SQL 在 MySQL 8.0.35 上跑了 5 分 47 秒，还没算第 3 天和第 7 天的。DBA 直接打电话过来：你这条 SQL 把从库 IO 打满了，赶紧停掉。我 kill 掉之后，运营又在钉钉催进度。那天晚上我加了 3 小时班，用 BitMap 重构了这个统计逻辑，最终单次留存计算耗时稳定在 80ms 以内。
这篇文章就把完整的方案写出来，包括核心代码、压测数据和踩过的坑。
留存统计的常规方案，慢在哪
方案一：SQL JOIN + COUNT DISTINCT
上面那条 SQL 就是典型写法。慢的原因有三层：
- 第一个慢在 COUNT(DISTINCT)，2600 万参与用户和 1.2 亿登录记录做 JOIN，MySQL 要维护一个巨大的哈希集合去重。
- 第二个慢在索引失效风险，user_login_log 表按 (user_id, login_date) 建了联合索引，但活动用户分布在 200 张分表里，需要 UNION ALL 合并后与总表关联，优化器经常选错驱动表。
- 第三个慢在 IO 放大，即使走了索引，每查一个用户要回表一次，2600 万用户就是 2600 万次随机 IO。
| 数据量 | SQL耗时（秒） | 扫描行数 | 临时表占用 |
|---|---|---|---|
| 100万用户 | 8.3 | 4200万 | 180MB |
| 2600万用户 | 347 | 11.2亿 | 4.7GB |
这只是第 1 天留存，第 3 天、第 7 天就是把 SQL 改个日期再跑一遍。等结果全出来，大促的复盘会已经开完了。
方案二：Redis SET 交集
同事给我提了个方案：把参与用户和当天登录用户都装进 Redis 的 SET 里，用 SINTERSTORE 算交集。
# 伪代码，实际用 Redis 命令
SADD activity:88231:users {2600万个user_id}
SADD login:20241112 {当天登录用户}
SINTERSTORE retain:day1 activity:88231:users login:20241112
SCARD retain:day1
实测结果：2600 万个 user_id 全部塞进 SET，每个 user_id 是 10 位数字字符串，占用约 1.4GB 内存。SINTERSTORE 操作耗时 4.2 秒。内存占用太高了，如果同时统计 3 个留存指标，Redis 直接撑爆。
| 方案 | 存储结构 | 2600万数据内存占用 | 单次交集耗时 |
|---|---|---|---|
| Redis SET | 哈希表 | 1.4GB | 4.2秒 |
| BitMap | 位数组 | 3.1MB | 80ms |
BitMap 的核心原理
BitMap 就是一个 bit 数组，每个 bit 只有 0 或 1 两种状态。把 user_id 映射到 bit 的位置：位置下标就是 user_id，bit 值表示「是否存在」。
这个映射关系可以用一个最简单的哈希函数：user_id 直接作为偏移量。假设我们把 user_id 数值范围映射到 [0, N) 区间，第 k 位为 1 就表示 user_id = k 的用户在集合里。
关键的内存计算：N 个用户只需要 N/8 字节。拿 2600 万用户举例：
26,000,000 / 8 / 1024 / 1024 = 3.1 MB
跟 Redis SET 的 1.4GB 相比，内存只有原来的 1/450。
交集运算在 BitMap 里就是按位与（AND）。Redis 的 BITOP 命令就是干这个的，CPU 一次能处理 64 个 bit（一个机器字长），2600 万个 bit 只需要约 40 万次位运算指令，毫秒级完成很正常。
完整方案：基于 BitMap 的留存统计
数据模型设计
核心思路：每天一个 BitMap，bit 位置代表 user_id，bit 值代表该用户当天是否活跃。留存率 = 活动参与用户的 BitMap 与第 N 天活跃用户的 BitMap 的交集大小 ÷ 活动参与用户总数。
为什么每天一个 BitMap 而不是一个大的？因为每天一个可以把 key 按日期分开，方便按天统计、按天过期。同时做跨天计算时只需要把两个 BitMap 做 AND，不用处理同一个 BitMap 里多天数据混杂的问题。
PHP 核心代码实现
环境说明：PHP 8.3.2，Redis 7.2.4，扩展 phpredis 6.0.2。
先把用户 ID 写入当日活跃 BitMap。这里直接用 Redis 的 SETBIT 命令，O(1) 复杂度：
<?php
/**
* 将某个用户标记为指定日期活跃
* @param Redis $redis
* @param string $date 日期 Y-m-d
* @param int $userId 用户ID
*/
function markActive(\Redis $redis, string $date, int $userId): void {
$key = "bitmap:active:{$date}";
// SETBIT key offset value，offset 就是 user_id
// 返回旧值，如果原来是 0 现在是 1，说明新增活跃用户
$redis->setbit($key, $userId, 1);
}
/**
* 批量标记活跃用户（用于离线导入历史数据）
* @param Redis $redis
* @param string $date 日期
* @param array $userIds 用户ID数组
* @param int $batchSize 每批处理数量
*/
function batchMarkActive(\Redis $redis, string $date, array $userIds, int $batchSize = 5000): void {
$key = "bitmap:active:{$date}";
// 使用 pipeline 减少 RTT
$pipe = $redis->pipeline();
$count = 0;
foreach ($userIds as $userId) {
$pipe->setbit($key, $userId, 1);
$count++;
if ($count % $batchSize === 0) {
$pipe->exec(); // 批量发送
$pipe = $redis->pipeline(); // 重新创建
}
}
// 最后一批
if ($count % $batchSize !== 0) {
$pipe->exec();
}
}
写入时有个关键细节：参与活动的用户和活跃用户必须用同一套 ID 映射。不能活动表用自增 ID，登录日志用用户 ID，映射不一致算出来的留存率就是错的。我们在导入数据前统一把 user_id 做了一次连续化处理（或者直接用数据库自增主键作为 bit 偏移量）。
接下来是留存计算的核心逻辑。假设活动参与用户在 activity_users 表里，先构建活动用户的 BitMap，再与第 N 天活跃 BitMap 做 AND：
<?php
/**
* 构建活动参与用户的 BitMap
* @param Redis $redis
* @param PDO $pdo
* @param int $activityId 活动ID
* @return string 返回 BitMap 的 Redis key，方便后续复用
*/
function buildActivityUserBitmap(\Redis $redis, \PDO $pdo, int $activityId): string {
$redisKey = "bitmap:activity:{$activityId}";
// 检查是否已经构建过，避免重复查询数据库
if ($redis->exists($redisKey)) {
return $redisKey;
}
// 分页读取活动用户，避免一次性加载内存溢出
$offset = 0;
$limit = 10000;
$pipe = $redis->pipeline();
do {
$stmt = $pdo->prepare(
"SELECT user_id FROM activity_users
WHERE activity_id = :aid AND user_id > :offset
ORDER BY user_id ASC LIMIT :limit"
);
$stmt->bindValue(':aid', $activityId, PDO::PARAM_INT);
$stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
$stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_COLUMN);
foreach ($rows as $userId) {
$pipe->setbit($redisKey, (int)$userId, 1);
}
$pipe->exec();
$pipe = $redis->pipeline();
$offset = $rows ? end($rows) : 0;
} while (count($rows) === $limit);
return $redisKey;
}
/**
* 计算留存率
* @param Redis $redis
* @param string $activityUserKey 活动用户 BitMap key
* @param string $day1Key 第1天活跃 BitMap key
* @param string $day3Key 第3天活跃 BitMap key
* @param string $day7Key 第7天活跃 BitMap key
* @param string $resultKey 结果临时 key
* @return array{day1: float, day3: float, day7: float}
*/
function calcRetention(
\Redis $redis,
string $activityUserKey,
string $day1Key,
string $day3Key,
string $day7Key,
string $resultKey
): array {
// 先用 BITCOUNT 拿到活动用户总数
$baseCount = $redis->bitcount($activityUserKey);
if ($baseCount === 0) {
return ['day1' => 0, 'day3' => 0, 'day7' => 0];
}
$retention = [];
$targets = ['day1' => $day1Key, 'day3' => $day3Key, 'day7' => $day7Key];
foreach ($targets as $label => $targetKey) {
// BITOP AND destkey srckey1 srckey2 ... 将交集结果存入临时 key
$redis->bitop('AND', $resultKey, $activityUserKey, $targetKey);
// BITCOUNT 统计交集 bit 为 1 的个数
$retainedCount = $redis->bitcount($resultKey);
// 留存率保留 4 位小数
$retention[$label] = round($retainedCount / $baseCount, 4);
}
return $retention;
}
完整调用链路：
<?php
require 'vendor/autoload.php';
use Predis\Client;
$redis = new \Redis();
$redis->connect('127.0.0.1', 6379);
$redis->auth('your_password');
$redis->select(0);
// 假设活动 88231 的参与用户 BitMap 已经构建过
$activityKey = buildActivityUserBitmap($redis, $pdo, 88231);
// 活动日期 2024-11-10，计算第 1、3、7 天留存
$activityDate = '2024-11-10';
$day1 = date('Y-m-d', strtotime($activityDate . ' +1 day'));
$day3 = date('Y-m-d', strtotime($activityDate . ' +3 day'));
$day7 = date('Y-m-d', strtotime($activityDate . ' +7 day'));
$result = calcRetention(
$redis,
$activityKey,
"bitmap:active:{$day1}",
"bitmap:active:{$day3}",
"bitmap:active:{$day7}",
"bitmap:tmp:retain:{$activityDate}"
);
// 打印结果
echo json_encode([
'activity_id' => 88231,
'activity_date' => $activityDate,
'base_users' => $redis->bitcount($activityKey),
'day1_retention' => $result['day1'],
'day3_retention' => $result['day3'],
'day7_retention' => $result['day7'],
], JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE);
跨天场景：日期筛选与批量计算
运营经常会问「过去 30 天每天的留存变化趋势」。如果每天的数据都存了 BitMap，计算 N 天前的留存就是循环调用 calcRetention：
<?php
/**
* 批量计算连续多天的留存率趋势
* @param Redis $redis
* @param string $activityKey 活动用户 BitMap key
* @param string $startDate 起始日期 Y-m-d
* @param int $days 天数
* @param int $retentionOffset 留存间隔天数（1表示次日留存）
*/
function batchRetentionTrend(
\Redis $redis,
string $activityKey,
string $startDate,
int $days,
int $retentionOffset = 1
): array {
$trend = [];
$baseCount = $redis->bitcount($activityKey);
for ($i = 0; $i < $days; $i++) {
$baseDate = date('Y-m-d', strtotime($startDate . " +{$i} day"));
$targetDate = date('Y-m-d', strtotime($baseDate . " +{$retentionOffset} day"));
$targetKey = "bitmap:active:{$targetDate}";
// 跳过未来日期
if ($targetDate > date('Y-m-d')) {
break;
}
$tmpKey = "bitmap:tmp:trend:{$baseDate}:{$retentionOffset}";
$redis->bitop('AND', $tmpKey, $activityKey, $targetKey);
$retained = $redis->bitcount($tmpKey);
$trend[] = [
'base_date' => $baseDate,
'target_date' => $targetDate,
'retained_users' => $retained,
'retention_rate' => $baseCount > 0 ? round($retained / $baseCount, 4) : 0,
];
// 用完即删临时 key，避免 Redis 内存泄漏
$redis->del($tmpKey);
}
return $trend;
}
漏斗分析：多步骤间的转化计算
留存只是 BitMap 的一个应用。漏斗分析（比如「浏览商品 → 加购物车 → 下单」的转化）原理一样：每一步一个 BitMap，相邻两步之间做 AND 算交集，再除以步骤一的用户数。
<?php
/**
* 漏斗转化分析
* @param Redis $redis
* @param array $stepKeys 有序 BitMap key 数组，依次代表漏斗每一步
* @return array 每步用户数和步骤间转化率
*/
function funnelAnalysis(\Redis $redis, array $stepKeys): array {
$result = [];
$prevCount = null;
$currentBitmap = null;
foreach ($stepKeys as $index => $key) {
if ($index === 0) {
$currentBitmap = $key;
} else {
// 当前步骤与上一步的交集 = 走到当前步骤的用户
$tmpKey = "bitmap:tmp:funnel:{$index}:{$key}";
$redis->bitop('AND', $tmpKey, $currentBitmap, $key);
$currentBitmap = $tmpKey;
// 上一轮的临时 key 可以删了
if ($index > 1) {
$redis->del($result[$index - 1]['tmp_key'] ?? '');
}
}
$count = $redis->bitcount($currentBitmap);
$conversionRate = ($prevCount !== null && $prevCount > 0)
? round($count / $prevCount, 4)
: 1.0;
$result[$index] = [
'step' => $index + 1,
'users' => $count,
'conversion_rate' => $conversionRate,
'tmp_key' => $currentBitmap,
];
$prevCount = $count;
}
// 清理临时 key
foreach ($result as $item) {
if ($item['tmp_key'] !== $stepKeys[0]) {
$redis->del($item['tmp_key']);
}
}
return $result;
}
压测数据：BitMap vs SQL vs Redis SET
测试环境：Redis 7.2.4 单机，分配 4GB 内存，禁用 swap。测试机 CPU 是 8 核 Intel Xeon Platinum 8259CL，内存 16GB。数据规模：2600 万用户 ID，模拟每天约 800 万活跃用户。
测试数据生成逻辑：为了保证压测真实，我用脚本模拟了 30 天活跃数据，每天约 800 万个 user_id 随机分布于 0~2600 万区间，活动用户为完整 2600 万，用于测试极端场景。
# 生成 30 天测试数据，每天 800 万活跃用户
# 提前灌入 30 个 BitMap，观察内存增长
php gen_test_data.php --users=26000000 --active-per-day=8000000 --days=30
| 指标 | MySQL 8.0.35（SQL方案） | Redis SET 方案 | Redis BitMap 方案 |
|---|---|---|---|
| 2600万用户存储占用 | 约 1.8GB（含索引） | 约 1.4GB | 3.1MB |
| 构建数据总耗时 | 8分42秒 | 6分15秒 | 2分23秒 |
| 单次留存计算（第1天） | 347秒 | 4.2秒 | 80ms |
| 连续 30 天留存趋势 | 无法在1小时内跑完 | 126秒 | 2.4秒 |
| Redis 内存峰值 | — | 2.8GB（含临时交集） | 39MB（含 30 天数据） |
BITOP AND 与 BITCOUNT 的时间复杂度都是 O(N)，N 是 BitMap 的 bit 长度（这里取决于最大的 user_id 偏移量，不是实际用户数）。2600 万 bit = 3.25MB，内存中连续 3.25MB 数据的按位与，加上 Redis 命令本身的网络往返，单次 80ms 的组成大概是：网络 RTT 0.1ms + BITOP 执行 62ms + BITCOUNT 执行 17ms。
如果把 BitMap 数据加载到 PHP 内存里用扩展直接算，还能更快，但单台 Redis 80ms 已经完全够用了。优化方向应该是批量管道，减少网络往返。
这里需要说明一个细节：3.1MB 存储是基于 user_id 连续且最大值接近 2600 万的情况。如果 user_id 是雪花 ID（比如 19 位数字），直接做偏移量会导致 BitMap 稀疏，内存爆炸。解决办法在避坑部分详说。
优化：多命令合并与 Pipeline
上面的 calcRetention 每次都发两条命令（BITOP + BITCOUNT）。如果运营要看 30 天趋势，30 次循环就是 60 条命令。用 Pipeline 把命令打包，一次网络往返完成所有计算：
<?php
/**
* Pipeline 批量计算留存率，减少网络 RTT
* @param Redis $redis
* @param string $activityKey 活动用户 BitMap
* @param array $targetDates 目标日期数组
* @return array 对应的留存率
*/
function batchRetentionPipeline(\Redis $redis, string $activityKey, array $targetDates): array {
$baseCount = $redis->bitcount($activityKey);
$rates = [];
$pipe = $redis->pipeline();
$keys = [];
foreach ($targetDates as $date) {
$tmpKey = "bitmap:tmp:pipeline:{$date}";
$targetKey = "bitmap:active:{$date}";
$pipe->bitop('AND', $tmpKey, $activityKey, $targetKey);
$keys[] = ['date' => $date, 'tmpKey' => $tmpKey];
}
$results = $pipe->exec(); // 一次网络往返
// 再发一次 pipeline 做 BITCOUNT
$pipe = $redis->pipeline();
foreach ($keys as $item) {
$pipe->bitcount($item['tmpKey']);
}
$counts = $pipe->exec();
foreach ($keys as $i => $item) {
$rates[$item['date']] = [
'retained' => $counts[$i],
'rate' => $baseCount > 0 ? round($counts[$i] / $baseCount, 4) : 0,
];
$redis->del($item['tmpKey']);
}
return $rates;
}
Pipeline 化之后，30 天的留存趋势计算耗时从 2.4 秒降到 1.1 秒。瓶颈变成了 BITOP 本身的 CPU 运算。
避坑指南
这套方案我从 2.0 版本用到 3.0 版本，踩过的坑比写代码的时间多。下面这几个坑，每一个都真实遇到过：
坑一：user_id 不连续，BitMap 内存爆炸
我们线上真实的 user_id 是分布式发号器生成的，最大值到 1.9 亿，但实际用户只有 2600 万。如果直接用 user_id 做 bit 偏移量，BitMap 长度就是 1.9 亿位 = 23.7MB，膨胀 7 倍。看着还能接受，但如果 userId 是雪花 ID（19 位数字），BitMap 直接炸到 TB 级。
解决办法：对 user_id 做一次连续化映射。拿一个自增字典表，给每个 user_id 分配一个递增序号，BitMap 用这个序号做偏移量。
-- 用户ID映射表，将稀疏ID映射为连续序号
CREATE TABLE user_id_map (
user_id BIGINT PRIMARY KEY, -- 原始用户ID
bitmap_index INT UNSIGNED NOT NULL AUTO_INCREMENT, -- 连续序号
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY uk_user_id (user_id)
) ENGINE=InnoDB;
<?php
/**
* 获取用户ID对应的 BitMap 偏移量（连续化映射）
* @param PDO $pdo
* @param int $userId 原始用户ID
* @return int 连续序号，不存在则分配
*/
function getBitmapIndex(\PDO $pdo, int $userId): int {
// INSERT IGNORE + LAST_INSERT_ID 方式，避免并发问题
$stmt = $pdo->prepare(
"INSERT IGNORE INTO user_id_map (user_id) VALUES (?)"
);
$stmt->execute([$userId]);
if ($stmt->rowCount() === 1) {
// 新插入，LAST_INSERT_ID 就是新分配的序号
return (int)$pdo->lastInsertId();
}
// 已存在，查出来
$stmt = $pdo->prepare("SELECT bitmap_index FROM user_id_map WHERE user_id = ?");
$stmt->execute([$userId]);
return (int)$stmt->fetchColumn();
}
注意：这个表只做一次全量映射，后续新增用户只追加不修改。bitmap_index 从 1 开始，0 位置空着不用，避免「用户 ID 为 0」的语义歧义。
坑二：BITCOUNT 的精度问题
Redis 的 BITCOUNT 有两种实现：遍历计数（key 小于 128KB 时）和 查表法（key 较大时）。查表法严格精确，不会出错。但如果你用 Redis 3.x 旧版本，BITCOUNT 在大 key 时可能返回近似值（旧版本有已知 bug）。我们线上 Redis 升到 7.2.4 后没再遇到这个问题。
另一个精度陷阱：如果 BitMap 的 offset 分配到了 2^32 以上（约 42 亿），Redis 的 SETBIT/BITCOUNT 内部处理会出现溢出问题。普通业务到不了这个量级，但如果做物联网设备 ID 映射，必须注意。
坑三：临时 key 不清理，Redis 内存悄悄涨满
BITOP 的结果必须放在目标 key 里，这个 key 用完不删就一直占内存。我们的漏斗分析代码里，每算一步就生成一个 tmp key。刚开始跑线上任务时没加清理逻辑，跑了一周 Redis 内存从 1.2GB 涨到 3.8GB，触发 maxmemory 策略开始淘汰其他业务的数据，差点造成线上事故。
处理方式：
- 所有 BITOP 结果都用固定的 tmp key 前缀，用完立即 DEL。
- 写一个定时任务，每天凌晨扫描 bitmap:tmp:* 的 key，TTL 超过 1 小时的直接删掉。
# 定时清理临时 BitMap key
# 配合 redis-cli 扫描，也可以用 PHP 脚本循环
redis-cli --scan --pattern 'bitmap:tmp:*' | xargs -r redis-cli del
坑四：偏移量从 0 开始，还是从 1 开始？
Redis 的 SETBIT 偏移量从 0 开始。但数据库的自增主键从 1 开始。如果映射表的 bitmap_index 直接取自增 ID，第一个用户的序号是 1，第二个是 2，后面的 bit 位全部没问题。但注意：bitmap_index = 0 的位置不可用。如果你用 AUTO_INCREMENT 从 1 开始，没问题；千万别把主键减 1 当偏移量，否则两个不同的用户 ID 可能映射到同一个 bit（id=1 映射到 0，id=0 映射到 -1 直接报错）。
我们就在这上面翻过车：设计映射表时把 AUTO_INCREMENT 默认从 0 开始，结果第一个用户序号是 0，第二个是 1，后来从 1 开始计数导致所有统计结果错位了一位。最终全表重建才修正。
坑五：负数和超大整数的偏移量
PHP 的 int 类型在 64 位系统上是 64 位有符号整数。如果 user_id 映射后的 bitmap_index 超过 2^63 - 1（约 9.2 × 10^18），Redis 的 SETBIT 直接报错。实际业务到不了这个值，但要注意：如果使用 PHP 的 int 类型接收 Redis 返回的偏移量，可能因为类型转换溢出变成负数，导致莫名其妙的数据错误。务必用 (int) 强转或者大整数类型处理。
总结
BitMap 就是把集合存储换成位数组，用空间换时间。做留存统计、漏斗分析这类「交集 + 计数」的场景，它天然合适。核心不是复杂的位运算技巧，而是把你手里的数据按「天」组织成多个 BitMap，然后灵活地用 BITOP 组合它们。
这套方案在我们线上跑了半年，支撑了 30+ 个活动分析看板，每日统计任务从原来的 3 小时缩短到 10 分钟。如果你的统计场景还没用上 BitMap，建议从今天的数据迁移开始。