# Hướng dẫn scale bảng `zalo_messages`

Tài liệu này dùng cho bài toán launch thực tế:

- khoảng **100.000 tài khoản Zalo**;
- vài triệu tin nhắn mỗi ngày;
- nếu lưu toàn bộ thì khoảng **100 triệu bản ghi/tháng**;
- chiến lược vận hành mong muốn: **mỗi hội thoại chỉ giữ tối đa 500 tin mới nhất**;
- hot table dự kiến khoảng **50 triệu bản ghi**.

Kết luận thiết kế: **không partition trực tiếp bảng live `zalo_messages` ở giai đoạn launch**.
Với giới hạn 500 tin/hội thoại, bài toán chính không phải partition mà là:

1. Giữ bảng live bị chặn trần bằng prune theo từng hội thoại.
2. Dùng đúng index cho timeline và unread count.
3. Giảm write amplification: không giữ index thừa.
4. Đảm bảo đường ingest không mất tin khi Laravel/MySQL chậm hoặc restart.
5. Chỉ dùng archive partition nếu cần giữ lịch sử cũ ngoài 500 tin.

MySQL đang chạy trong container lúc kiểm tra là **MySQL 8.4.11**. Mẫu dữ liệu hiện tại còn nhỏ
(`zalo_messages` có 141 dòng), nên các kết luận về tải nặng được đưa ra dựa trên schema, query
shape, `EXPLAIN`, và quy mô dữ liệu mục tiêu, không phải benchmark production.

## 1. Nhận định theo quy mô 50 triệu dòng

50 triệu dòng trong một bảng InnoDB **không phải là mức đáng sợ** nếu:

- mọi query UI đều bám vào index có selectivity cao;
- không có query scan toàn bảng;
- không `COUNT(*)` trên hội thoại không giới hạn;
- không xóa hàng triệu dòng bằng một lệnh lớn;
- số index được giữ ở mức tối thiểu cần thiết;
- buffer pool và disk I/O đủ cho working set nóng.

Với 100 triệu tin/tháng, tốc độ trung bình là:

```text
100.000.000 / 30 ngày / 24 giờ / 3600 giây ≈ 39 tin/giây
```

Peak thực tế có thể gấp 5-20 lần trung bình. Thiết kế nên chịu được **300-800 tin/giây** ở giờ
cao điểm. MySQL một primary trên NVMe xử lý được mức này nếu ghi theo batch, index gọn và không
có job xóa nặng chạy cùng giờ.

Điểm rủi ro lớn hơn 50 triệu dòng là:

- bảng tăng không giới hạn vì chưa có prune 500 tin/hội thoại;
- worker đang giữ queue trong RAM, container chết là mất tin chưa đẩy;
- thiếu index có `direction`, khiến ingest phải đếm unread bằng scan nhiều dòng;
- index thừa làm chậm mọi insert/upsert.

## 2. Index production nên dùng

Hiện bảng đang có:

```sql
PRIMARY KEY (id)
UNIQUE KEY zalo_messages_zalo_account_id_msg_id_unique (zalo_account_id, msg_id)
KEY zalo_messages_zalo_conversation_id_sent_at_index (zalo_conversation_id, sent_at)
KEY zalo_messages_sent_at_index (sent_at)
```

Cho launch, nên chuyển sang bộ index này:

```sql
PRIMARY KEY (id)
UNIQUE KEY zalo_messages_zalo_account_id_msg_id_unique (zalo_account_id, msg_id)
KEY zalo_msg_conv_timeline_idx (zalo_conversation_id, sent_at, id)
KEY zalo_msg_conv_dir_sent_msg_idx (zalo_conversation_id, direction, sent_at, msg_id)
```

Giải thích:

| Index | Lý do giữ |
|---|---|
| `PRIMARY (id)` | khóa chính Eloquent, delete theo chunk bằng id |
| `UNIQUE (zalo_account_id, msg_id)` | chống trùng khi worker gửi lại lô, khi `selfListen` vọng lại tin tự gửi |
| `(zalo_conversation_id, sent_at, id)` | đọc 50/100/500 tin mới nhất, prune phần vượt quá 500 |
| `(zalo_conversation_id, direction, sent_at, msg_id)` | đếm unread, lấy tin đến mới nhất cho auto-reply, lấy outgoing mới nhất |

Index cũ `(zalo_conversation_id, sent_at)` bị thay bởi `(zalo_conversation_id, sent_at, id)`.
Không nên giữ cả hai trên production vì mỗi tin mới sẽ phải cập nhật thêm một B-tree vô ích.

Index đơn `(sent_at)` chỉ nên giữ nếu có job purge/archive theo thời gian trên bảng live. Nếu
đã quyết định giữ tối đa 500 tin/hội thoại và không purge theo ngày, nên bỏ index này để giảm
write amplification.

Nếu production chưa migrate, sửa migration gốc để tạo đúng index ngay từ đầu. Nếu DB đã có dữ
liệu, thêm index mới trước, đo lại, rồi mới drop index cũ.

Migration thêm index:

```php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    public function up(): void
    {
        Schema::table('zalo_messages', function (Blueprint $table) {
            $table->index(
                ['zalo_conversation_id', 'sent_at', 'id'],
                'zalo_msg_conv_timeline_idx',
            );

            $table->index(
                ['zalo_conversation_id', 'direction', 'sent_at', 'msg_id'],
                'zalo_msg_conv_dir_sent_msg_idx',
            );
        });

        DB::statement('ANALYZE TABLE zalo_messages');
    }

    public function down(): void
    {
        Schema::table('zalo_messages', function (Blueprint $table) {
            $table->dropIndex('zalo_msg_conv_timeline_idx');
            $table->dropIndex('zalo_msg_conv_dir_sent_msg_idx');
        });
    }
};
```

Sau khi đã xác nhận query dùng index mới:

```php
Schema::table('zalo_messages', function (Blueprint $table) {
    $table->dropIndex('zalo_messages_zalo_conversation_id_sent_at_index');
    $table->dropIndex('zalo_messages_sent_at_index');
});
```

Nếu vẫn cần purge/archive theo thời gian, giữ hoặc đổi index đơn thành:

```sql
KEY zalo_msg_sent_id_idx (sent_at, id)
```

## 3. Query nóng phải được tối ưu

### 3.1 Đọc tin mới nhất của một hội thoại

API trang đầu:

```text
GET /api/zalo-accounts/{id}/messages?conversation_key={thread_id}&limit=50
```

API trang cũ hơn:

```text
GET /api/zalo-accounts/{id}/messages?conversation_key={thread_id}&limit=50&before_sent_at={cursor_time}&before_id={cursor_id}
```

Response giữ `data` như cũ và thêm metadata phân trang:

```json
{
  "success": true,
  "data": [],
  "pagination": {
    "limit": 50,
    "has_more": true,
    "next_before_sent_at": "2026-08-02T10:00:00+00:00",
    "next_before_id": 123456
  }
}
```

`next_before_sent_at` và `next_before_id` lấy từ tin cũ nhất của page hiện tại. Frontend gửi
lại đúng hai giá trị này để lấy page cũ hơn. Không dùng `OFFSET` trong API người dùng.

Query trang đầu:

```sql
SELECT *
FROM zalo_messages
WHERE zalo_conversation_id = ?
ORDER BY sent_at DESC, id DESC
LIMIT 51;
```

Query trang cũ hơn:

```sql
SELECT *
FROM zalo_messages
WHERE zalo_conversation_id = ?
  AND (
    sent_at < ?
    OR (sent_at = ? AND id < ?)
  )
ORDER BY sent_at DESC, id DESC
LIMIT 51;
```

Index kỳ vọng:

```text
zalo_msg_conv_timeline_idx (zalo_conversation_id, sent_at, id)
```

Kỳ vọng `EXPLAIN`:

- `key = zalo_msg_conv_timeline_idx`
- không có `Using filesort`
- số `rows` nhỏ, gần với `LIMIT`

### 3.2 Đếm unread

Query logic hiện tại:

```sql
SELECT COUNT(*)
FROM zalo_messages
WHERE zalo_conversation_id = ?
  AND direction = 'in'
  AND sent_at > ?;
```

Index kỳ vọng:

```text
zalo_msg_conv_dir_sent_msg_idx (zalo_conversation_id, direction, sent_at, msg_id)
```

Với giới hạn 500 tin/hội thoại, query này chỉ đếm tối đa vài trăm dòng. Không cần denormalize
phức tạp ở launch. Nếu sau này unread vẫn vào slow log, mới đổi sang cập nhật tăng/giảm bằng
counter có kiểm soát trùng.

### 3.3 Lấy tin đến mới nhất cho auto-reply

Query logic:

```sql
SELECT msg_id
FROM zalo_messages
WHERE zalo_conversation_id = ?
  AND direction = 'in'
ORDER BY sent_at DESC
LIMIT 1;
```

Index kỳ vọng vẫn là:

```text
zalo_msg_conv_dir_sent_msg_idx
```

### 3.4 Tra quote payload

Query logic:

```sql
SELECT quote_payload
FROM zalo_messages
WHERE zalo_account_id = ?
  AND msg_id = ?;
```

Index kỳ vọng:

```text
zalo_messages_zalo_account_id_msg_id_unique
```

Không thêm index riêng cho `quote_msg_id` ở launch. UI chỉ tra quote của các tin đang hiển thị,
và đã có `msg_id` của tin gốc.

## 4. Cơ chế giữ tối đa 500 tin/hội thoại

Đây là phần bắt buộc trước launch. Nếu không có cơ chế này, bảng sẽ chạy thẳng về 100 triệu
dòng/tháng và sau vài tháng mọi thao tác schema/backup sẽ nặng.

### 4.1 Nguyên tắc

- Prune theo `zalo_conversation_id`, không prune toàn bảng.
- Mỗi hội thoại giữ 500 tin mới nhất theo `(sent_at DESC, id DESC)`.
- Không chạy `COUNT(*)` để quyết định prune; chỉ tìm phần overflow bằng `OFFSET 500`.
- Xóa theo chunk nhỏ, ví dụ 500-2.000 dòng/lần.
- Không prune đồng bộ trong request ingest nếu có thể tránh; đưa sang queue job.
- Job prune phải unique theo conversation để tránh 10 lô tin cùng đẩy 10 job xóa cho một hội thoại.

### 4.2 Query prune khuyến nghị

Lấy danh sách id vượt quá 500:

```sql
SELECT id
FROM zalo_messages
WHERE zalo_conversation_id = ?
ORDER BY sent_at DESC, id DESC
LIMIT 1000 OFFSET 500;
```

Sau đó xóa theo primary key:

```sql
DELETE FROM zalo_messages
WHERE id IN (...);
```

Lặp lại cho đến khi query overflow trả rỗng.

Không dùng một lệnh kiểu `DELETE WHERE sent_at < cutoff` nếu chưa khóa kỹ logic, vì tin Zalo có
thể có timestamp trùng nhau hoặc lô backfill đến muộn. Xóa theo `id` lấy từ danh sách đã sort
rõ ràng sẽ dễ kiểm soát hơn.

### 4.3 Job Laravel mẫu

```php
final class PruneZaloConversationMessages implements ShouldQueue
{
    private const KEEP = 500;
    private const CHUNK = 1000;

    public function __construct(private readonly int $conversationId) {}

    public function handle(): void
    {
        do {
            $ids = DB::table('zalo_messages')
                ->where('zalo_conversation_id', $this->conversationId)
                ->orderByDesc('sent_at')
                ->orderByDesc('id')
                ->offset(self::KEEP)
                ->limit(self::CHUNK)
                ->pluck('id');

            if ($ids->isEmpty()) {
                return;
            }

            DB::table('zalo_messages')
                ->whereIn('id', $ids->all())
                ->delete();
        } while ($ids->count() === self::CHUNK);
    }
}
```

Nên bọc job bằng lock theo conversation:

```php
$lock = Cache::lock("zalo:prune-conversation:{$conversationId}", 60);

if (! $lock->get()) {
    return;
}

try {
    // prune
} finally {
    $lock->release();
}
```

Sau `ZaloMessageIngestService::ingestThread()` nên dispatch job này sau khi ghi xong batch. Nếu
Laravel queue đang dùng database queue, nên chuyển riêng queue prune sang Redis hoặc một worker
riêng để prune không tranh tài nguyên với auto-reply.

### 4.4 Cleanup bảng phụ

Khi tin cũ bị prune khỏi `zalo_messages`, cần dọn bảng phụ để không tăng vô hạn:

- `zalo_agent_sent_messages`: xóa bản ghi quá cũ hoặc không còn tồn tại trong `zalo_messages`;
- queue/job thất bại liên quan tin đã bị prune;
- archive nếu có.

Nếu chỉ dùng `zalo_agent_sent_messages` để loại trừ lúc đếm unread, có thể giữ theo thời gian
ngắn hơn, ví dụ 30-90 ngày, vì tin live đã bị giới hạn 500/hội thoại.

## 5. Có cần partition bảng live không?

Với chiến lược giữ tối đa 500 tin/hội thoại, **không nên partition bảng live lúc launch**.

Lý do:

- Hot table đã bị chặn trần khoảng 50 triệu dòng.
- Query chính theo `zalo_conversation_id`, không phải query theo tháng.
- Partition theo tháng không giúp query `WHERE zalo_conversation_id = ? LIMIT 50` nhanh hơn nếu
query không có điều kiện thời gian để prune partition.
- Partition theo hash cũng không đáng: vẫn vướng FK, làm schema phức tạp, và không giúp xóa
phần vượt quá 500 tin/hội thoại.
- MySQL 8.4.11 không cho foreign key trên bảng partition. Đã kiểm chứng lỗi:

```text
Foreign keys are not yet supported in conjunction with partitioning
```

Ngoài ra, mọi `PRIMARY KEY`/`UNIQUE KEY` trên bảng partition phải chứa cột partition. Nếu
partition theo ngày, unique hiện tại:

```sql
UNIQUE KEY (zalo_account_id, msg_id)
```

sẽ không còn hợp lệ. Phải đổi thành dạng có `sent_date`, làm yếu đảm bảo chống trùng toàn cục.

Vì vậy lựa chọn launch hợp lý là:

```text
zalo_messages          = hot table, không partition, giữ 500 tin/hội thoại
zalo_messages_archive  = optional cold table, partition theo tháng nếu cần lưu lịch sử cũ
```

## 6. Nếu cần giữ lịch sử ngoài 500 tin

Nếu nghiệp vụ chỉ cần 500 tin mới nhất thì không cần archive. Prune là hard delete.

Nếu cần giữ lịch sử cũ để audit, thống kê hoặc tra cứu sau này, dùng bảng archive riêng:

- `zalo_messages` giữ 500 tin mới nhất/hội thoại;
- `zalo_messages_archive` giữ tin bị prune;
- archive table không có FK;
- archive table partition theo tháng bằng `sent_date`;
- UI mặc định không đọc archive, chỉ đọc archive khi người dùng chủ động mở lịch sử cũ.

Schema archive mẫu:

```sql
CREATE TABLE zalo_messages_archive (
  id BIGINT UNSIGNED NOT NULL,
  zalo_account_id BIGINT UNSIGNED NOT NULL,
  zalo_conversation_id BIGINT UNSIGNED NOT NULL,
  msg_id VARCHAR(64) NOT NULL,
  cli_msg_id VARCHAR(64) NULL,
  thread_id VARCHAR(64) NOT NULL,
  thread_type ENUM('user','group') NOT NULL DEFAULT 'user',
  direction ENUM('in','out') NOT NULL,
  sender_id VARCHAR(64) NULL,
  sender_name VARCHAR(255) NULL,
  content LONGTEXT NULL,
  msg_type VARCHAR(64) NULL,
  attachments JSON NULL,
  mentions JSON NULL,
  quote_msg_id VARCHAR(64) NULL,
  quote_payload JSON NULL,
  quote_preview JSON NULL,
  sent_at DATETIME NOT NULL,
  sent_date DATE GENERATED ALWAYS AS (DATE(sent_at)) STORED,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,

  PRIMARY KEY (id, sent_date),
  KEY zalo_msg_archive_conv_timeline_idx (zalo_conversation_id, sent_at, id),
  KEY zalo_msg_archive_account_msg_idx (zalo_account_id, msg_id),
  KEY zalo_msg_archive_sent_date_idx (sent_date)
) ENGINE=InnoDB
PARTITION BY RANGE COLUMNS(sent_date) (
  PARTITION p202608 VALUES LESS THAN ('2026-09-01'),
  PARTITION p202609 VALUES LESS THAN ('2026-10-01'),
  PARTITION pmax VALUES LESS THAN (MAXVALUE)
);
```

Thêm partition tháng mới trước khi sang tháng:

```sql
ALTER TABLE zalo_messages_archive
REORGANIZE PARTITION pmax INTO (
  PARTITION p202610 VALUES LESS THAN ('2026-11-01'),
  PARTITION pmax VALUES LESS THAN (MAXVALUE)
);
```

Xóa archive cũ:

```sql
ALTER TABLE zalo_messages_archive DROP PARTITION p202608;
```

Cần có cảnh báo nếu còn ghi vào `pmax`, vì nghĩa là quên tạo partition mới.

## 7. Ingest queue trước launch

Ở quy mô vài triệu tin/ngày, queue trong RAM của worker là điểm rủi ro dữ liệu. Container chết
đột ngột thì các tin chưa POST về Laravel sẽ mất.

Khuyến nghị trước launch:

- chuyển `messageForwarder` từ queue RAM sang Redis Stream/List có ack/retry;
- hoặc ghi tin vào một durable outbox trước khi POST Laravel;
- batch size nên bắt đầu ở 100-200 tin/lô thay vì 50 nếu MySQL và payload chịu được;
- `MESSAGE_QUEUE_MAX` phải tính theo peak, không dùng mặc định 10.000 cho production lớn.

Công thức sizing queue:

```text
MESSAGE_QUEUE_MAX >= peak_messages_per_second * outage_seconds
```

Ví dụ muốn chịu Laravel chậm 5 phút ở peak 500 tin/giây:

```text
500 * 300 = 150.000 tin
```

Nếu dùng queue RAM, 150.000 tin có thể ăn nhiều RAM khi tin có JSON/attachment metadata. Durable
queue vẫn là hướng an toàn hơn.

## 8. Ước lượng dung lượng

Không đo bằng số dòng đơn thuần. Đo bằng dữ liệu thật:

```sql
SHOW TABLE STATUS LIKE 'zalo_messages';
```

Tính:

```text
avg_bytes_per_row = (Data_length + Index_length) / Rows
expected_hot_size = avg_bytes_per_row * 50.000.000
```

Với tin text ngắn, hot table 50 triệu dòng có thể ở mức vài chục GB. Nếu `attachments`,
`quote_payload`, `quote_preview` lớn, con số có thể lên 100-200GB. Production nên dùng NVMe và
buffer pool đủ lớn cho working set nóng.

Khuyến nghị vận hành:

- MySQL riêng, không chạy chung với worker/browser/container nặng;
- `innodb_buffer_pool_size` khoảng 60-70% RAM nếu máy dành riêng cho MySQL;
- disk NVMe, không dùng ổ mạng chậm;
- backup/restore phải test bằng dữ liệu cỡ production, không chỉ test migration;
- bật slow query log trước launch.

## 9. Checklist bắt buộc trước launch

- Sửa schema production để dùng bộ index cuối cùng ở mục 2.
- API chi tiết tin nhắn phải dùng cursor `before_sent_at` + `before_id`, không dùng kiểu tăng
  limit từ 50 lên 100.
- Không giữ đồng thời index cũ `(zalo_conversation_id, sent_at)` và index mới
  `(zalo_conversation_id, sent_at, id)` lâu dài.
- Drop index đơn `(sent_at)` nếu không purge/archive theo thời gian trên bảng live.
- Thêm job prune giữ tối đa 500 tin/hội thoại.
- Prune chạy async, chunk nhỏ, có lock theo conversation.
- Dọn `zalo_agent_sent_messages` để bảng phụ không tăng vô hạn.
- Chuyển queue tin nhắn worker sang durable queue hoặc tăng `MESSAGE_QUEUE_MAX` theo peak.
- Chạy seed/load test tối thiểu 50 triệu dòng hoặc gần mức đó.
- Chạy `EXPLAIN` cho timeline, unread, latest incoming, quote payload.
- Bật slow query log với ngưỡng ban đầu 300-500ms.
- Đo `Data_length`, `Index_length`, tốc độ tăng mỗi ngày.
- Test restore backup trên dữ liệu lớn.
- Nếu cần lịch sử ngoài 500 tin, đưa vào `zalo_messages_archive` partition theo tháng, không
  partition bảng live.

## 10. Kết luận launch

Phương án ít rủi ro nhất cho launch:

```text
Live table:      zalo_messages không partition
Retention:       tối đa 500 tin/hội thoại
Hot rows:        khoảng 50 triệu
Index chính:     unique account+msg, timeline conv+sent_at+id, direction conv+direction+sent_at+msg
Prune:           async queue, chunk nhỏ, lock theo conversation
Archive:         chỉ dùng nếu cần lịch sử cũ, partition ở bảng archive riêng
Ingest queue:    durable Redis/outbox, không dựa vào RAM queue nếu launch lớn
```

Với thiết kế này, 50 triệu dòng là mức có thể vận hành ổn trên MySQL nếu hạ tầng đúng và không
có query scan toàn bảng. Việc không nên làm là partition bảng live ngay từ đầu: nó làm mất FK,
làm phức tạp unique key, nhưng không giải quyết query nóng hiện tại.