SQL连续登录查询怎么建索引？SQL优化连续登录查询索引策略有哪些

连续登录查询通常用于统计用户连续多天登录的情况，是用户活跃度分析、异常登录检测等场景的核心需求，这类查询的性能很大程度上取决于索引设计的合理性。

连续登录查询的常见实现逻辑

常见的连续登录查询实现方式是先对用户的登录记录按用户ID和登录日期排序，再计算日期的差值来判断是否连续。比如统计连续登录3天的用户，核心逻辑是先去重用户的登录日期，再给每个用户的登录日期排序，用登录日期减去排序序号，若差值相同则说明是连续登录。

以下是一个基础的连续登录查询示例，用于查询连续登录3天的用户：

-- 查询连续登录3天的用户
WITH login_distinct AS (
    SELECT 
        user_id,
        DISTINCT login_date
    FROM user_login_log
),
login_rank AS (
    SELECT 
        user_id,
        login_date,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
    FROM login_distinct
),
login_group AS (
    SELECT 
        user_id,
        login_date,
        DATE_SUB(login_date, INTERVAL rn DAY) AS group_date
    FROM login_rank
)
SELECT 
    user_id,
    COUNT(DISTINCT login_date) AS continuous_days,
    MIN(login_date) AS start_date,
    MAX(login_date) AS end_date
FROM login_group
GROUP BY user_id, group_date
HAVING COUNT(DISTINCT login_date) >= 3;

连续登录查询的索引设计策略

1. 基础联合索引设计

连续登录查询的核心过滤和排序字段是user_id和login_date，因此优先创建(user_id, login_date)的联合索引。这个索引可以同时满足用户维度的过滤和登录日期的排序需求，避免查询时的全表扫描和额外排序操作。

创建该索引的SQL语句如下：

-- 创建用户ID和登录日期的联合索引
CREATE INDEX idx_user_login ON user_login_log(user_id, login_date);

2. 覆盖索引优化

如果查询只需要用到user_id和login_date两个字段，那么可以将这两个字段组成覆盖索引，这样查询过程中不需要回表查询完整记录，进一步提升查询效率。上面的idx_user_login索引本身就是覆盖索引，只要查询字段都包含在索引中即可生效。

3. 带时间范围的索引优化

如果连续登录查询通常限定时间范围，比如只查询最近30天的登录记录，那么可以在联合索引中加入时间条件的字段，或者在查询时明确限定login_date的范围，让索引的过滤效果更明显。比如查询最近30天连续登录的用户，索引仍然可以使用(user_id, login_date)，查询时加上时间过滤条件即可：

-- 查询最近30天连续登录3天的用户
WITH login_distinct AS (
    SELECT 
        user_id,
        DISTINCT login_date
    FROM user_login_log
    WHERE login_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
),
login_rank AS (
    SELECT 
        user_id,
        login_date,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
    FROM login_distinct
),
login_group AS (
    SELECT 
        user_id,
        login_date,
        DATE_SUB(login_date, INTERVAL rn DAY) AS group_date
    FROM login_rank
)
SELECT 
    user_id,
    COUNT(DISTINCT login_date) AS continuous_days,
    MIN(login_date) AS start_date,
    MAX(login_date) AS end_date
FROM login_group
GROUP BY user_id, group_date
HAVING COUNT(DISTINCT login_date) >= 3;

索引使用注意事项

• 登录日期字段建议存储为DATE类型，避免存储字符串格式的日期，否则索引的排序和比较效率会明显下降。
• 如果user_login_log表数据量非常大，且只需要保留最近一段时间的登录记录，可以定期清理历史数据，减少索引的维护成本和查询时的扫描范围。
• 避免创建过多的冗余索引，比如已经创建了(user_id, login_date)的联合索引，就不需要再单独创建user_id的单列索引，因为联合索引的前缀匹配规则可以覆盖单列索引的使用场景。
• 可以通过EXPLAIN命令分析连续登录查询的执行计划，确认索引是否被正确命中，若存在全表扫描或者临时表、文件排序等情况，需要及时调整索引策略。

不同场景的索引选择总结

可以通过以下表格快速选择适合的索引方案：

SQL连续登录查询索引优化SQL索引策略连续登录检测修改时间：2026-06-26 05:27:39