面试题：MariaDB用户统计功能之性能优化与分区

Question

假设MariaDB中有一张非常大的用户行为表`user_actions`，包含`action_id`（行为ID，主键），`user_id`（用户ID），`action_type`（行为类型，如 '登录'、'购买' 等），`action_time`（行为发生时间）。现在需要频繁统计最近一周内不同行为类型的用户数量，由于数据量巨大导致查询缓慢。请阐述如何通过分区表和索引优化来提升该统计功能的性能，并给出具体的实现步骤和SQL示例。

Answer 1

1. 分区表优化

• 分区依据选择：由于需要统计最近一周内的数据，按时间分区较为合适，以action_time作为分区依据。
• 实现步骤：
  • 创建分区表：

CREATE TABLE user_actions (
    action_id INT PRIMARY KEY,
    user_id INT,
    action_type VARCHAR(50),
    action_time DATETIME
)
PARTITION BY RANGE (YEARWEEK(action_time)) (
    PARTITION p0 VALUES LESS THAN (YEARWEEK('2023-01-01')),
    PARTITION p1 VALUES LESS THAN (YEARWEEK('2023-01-08')),
    PARTITION p2 VALUES LESS THAN (YEARWEEK('2023-01-15')),
    -- 根据数据量和时间跨度继续添加分区
    PARTITION pn VALUES LESS THAN (MAXVALUE)
);

• 数据导入：若已有数据，可通过INSERT INTO...SELECT语句将数据从原表导入新的分区表。

INSERT INTO user_actions SELECT * FROM original_user_actions;

2. 索引优化

• 索引选择：为了加速对action_type和action_time的查询，创建联合索引。
• 实现步骤：

CREATE INDEX idx_action_type_time ON user_actions (action_type, action_time);

3. 统计查询优化

• SQL示例：

SELECT 
    action_type, 
    COUNT(DISTINCT user_id) AS user_count
FROM 
    user_actions
WHERE 
    action_time >= CURDATE() - INTERVAL 7 DAY
GROUP BY 
    action_type;

此查询利用了分区表快速定位最近一周数据，同时通过索引加速了对action_type的分组统计，从而提升查询性能。