SingleStore 6 — Index & tối ưu truy vấn

Trong bốn bài trước ta đã thấy SingleStore chia dữ liệu thành partition trải trên các leaf (sharding & phân tán), lưu trên hai họ storage rowstore/columnstore (Universal Storage), và biên dịch truy vấn ra mã máy rồi chạy MPP song song (thực thi truy vấn). Nhưng tốc độ cuối cùng của một câu SQL còn phụ thuộc một tầng nữa: dữ liệu được đánh chỉ mục và sắp xếp thế nào, và query optimizer chọn đường đi ra sao.

Bài này dựng mô hình tinh thần về các loại index theo từng storage, rồi đi vào những kỹ thuật tối ưu mà một kỹ sư dữ liệu ngân hàng thực sự dùng: chọn SORT KEY để bật segment elimination, chọn SHARD KEY để tránh reshuffle, quyết định rowstore hay columnstore theo workload, và giữ statistics luôn tươi.

Mô hình tinh thần: index gắn chặt với storage

Điểm khác biệt lớn nhất so với một CSDL đơn nút như MySQL/PostgreSQL: ở SingleStore loại index bạn được dùng phụ thuộc vào bảng đang là rowstore hay columnstore. Không có một cây B-tree "vạn năng" áp cho mọi bảng. Thay vào đó:

Mỗi công cụ giải một bài toán khác nhau. Bảng bên dưới là "bản đồ" để không dùng nhầm:

Ba dòng lệnh DDL sau minh hoạ ba thế giới này — chú ý cú pháp tương thích MySQL:

-- 1) Rowstore OLTP: point lookup theo mã giao dịch + range theo thời gian
CREATE TABLE txn_hot (
  txn_id      BIGINT       NOT NULL,
  account_id  BIGINT       NOT NULL,
  amount      DECIMAL(18,2) NOT NULL,
  txn_ts      DATETIME(6)  NOT NULL,
  status      VARCHAR(16)  NOT NULL,
  SHARD KEY (account_id),                 -- collocate theo tài khoản
  PRIMARY KEY (txn_id, account_id),       -- rowstore cần PK chứa shard key
  KEY (txn_ts) USING CLUSTERED COLUMNSTORE  -- (ví dụ đối lập; xem chú thích)
);

-- 2) Columnstore phân tích: sắp segment theo thời gian để prune
CREATE TABLE txn_history (
  txn_id      BIGINT       NOT NULL,
  account_id  BIGINT       NOT NULL,
  amount      DECIMAL(18,2) NOT NULL,
  txn_ts      DATETIME(6)  NOT NULL,
  merchant    VARCHAR(64),
  SHARD KEY (account_id),                 -- tránh reshuffle khi join theo account
  SORT KEY (txn_ts)                       -- segment elimination theo khoảng ngày
) USING CLUSTERED COLUMNSTORE;

-- 3) Universal Storage: thêm secondary HASH index cho point lookup trên columnstore
ALTER TABLE txn_history
  ADD INDEX ix_txn_id (txn_id) USING HASH;   -- tra cứu 1 giao dịch không phải quét segment

Chú thích: một bảng vừa là rowstore vừa là clustered columnstore trong cùng lệnh chỉ để minh hoạ hai lựa chọn; thực tế bạn chọn một họ storage cho mỗi bảng. Với CLUSTERED COLUMNSTORE, chính bảng đã là columnstore nên không cần khai báo thêm.

Rowstore: HASH và skiplist

Rowstore sống trong RAM và tối ưu cho OLTP. Nó cho hai kiểu index:

• HASH index — băm giá trị khoá về bucket, cho point lookup cực nhanh: WHERE txn_id = ?, WHERE account_id IN (...). Không hỗ trợ range/thứ tự vì băm phá vỡ trật tự.
• Skiplist (mặc định cho khoá thứ tự) — một cấu trúc lock-free thay cho B-tree, giữ dữ liệu theo thứ tự nên phục vụ tốt BETWEEN, >, <, và ORDER BY trùng cột index (tránh bước sort). Lock-free giúp nhiều transaction đọc/ghi đồng thời mà không chặn nhau — hợp OLTP tải cao.

Quy tắc ngón tay cái: nếu truy vấn của bạn chỉ khớp bằng (=, IN) thì HASH; nếu cần khoảng hoặc sắp thứ tự thì skiplist.

Columnstore: SORT KEY và segment elimination

Columnstore tổ chức dữ liệu thành các row segment (mặc định cỡ tới ~1 triệu hàng mỗi segment), mỗi cột nén thành blob riêng. Với mỗi segment, engine lưu metadata min/max của từng cột. Đây là chìa khoá của tối ưu columnstore:

Segment elimination (segment pruning): khi câu truy vấn có bộ lọc trên một cột mà dữ liệu đã được sắp thứ tự theo SORT KEY, engine đọc min/max của mỗi segment và bỏ qua nguyên cả segment không thể chứa hàng thoả điều kiện — không cần giải nén, không cần quét.

Nếu SORT KEY (txn_ts) thì các segment gần như không chồng lấn khoảng thời gian. Một truy vấn WHERE txn_ts >= '2026-07-01' chỉ chạm vài segment cuối thay vì toàn bảng:

Nếu không có sort key phù hợp (hoặc sắp theo cột khác), khoảng thời gian sẽ rải đều khắp mọi segment → min/max của segment nào cũng "giao" với điều kiện → không prune được gì, phải quét toàn bảng. Đây là lý do chọn sort key theo cột lọc thường xuyên nhất quan trọng hơn mọi thứ khác trong tối ưu columnstore.

Một vài lưu ý thực chiến về SORT KEY:

• Chọn cột hay xuất hiện trong WHERE dạng range (thời gian, id tăng dần) làm sort key — đó là nơi elimination phát huy.
• Sort key có thể gồm nhiều cột; thứ tự cột quyết định ưu tiên sắp xếp (giống prefix).
• Dữ liệu mới nạp vào rồi được background merger hợp nhất và sắp lại; giai đoạn "chưa sắp" tạm thời làm prune kém hơn — theo dõi qua management view khi bảng vừa nạp lớn.
• SHARD KEY và SORT KEY độc lập: shard quyết định hàng nằm ở partition nào, sort quyết định trong partition thì segment sắp thế nào.

Universal Storage: HASH phụ để point lookup trên columnstore

Điểm yếu kinh điển của columnstore là tra một hàng đơn (WHERE txn_id = 123): nếu txn_id không phải sort key thì đành quét/prune kém. Universal Storage giải bằng secondary HASH index trên cột lọc, biến columnstore thành nơi vừa quét phân tích tốt vừa point lookup kiểu OLTP:

-- Bật point lookup theo mã giao dịch mà vẫn giữ columnstore để phân tích
ALTER TABLE txn_history
  ADD INDEX ix_merchant (merchant) USING HASH;   -- lọc chính xác theo merchant

-- Từ đây: WHERE merchant = 'ACME' dùng hash để nhảy tới subsegment,
-- không quét toàn cột merchant của mọi segment.

Nhờ đó bạn thường không cần giữ song song một bảng rowstore "nóng" và một bảng columnstore "lạnh" cho cùng dữ liệu; một bảng Universal Storage phục vụ được cả hai kiểu truy vấn. Chi tiết cơ chế seekable/subsegment xem Universal Storage.

Tránh reshuffle: shard key là quyết định tối ưu lớn nhất

Ở tầng phân tán, chi phí đắt nhất thường không phải quét đĩa mà là di chuyển dữ liệu giữa các leaf qua mạng. Khi join hai bảng:

• Collocated join — hai bảng cùng SHARD KEY trên cột join → mỗi partition tự join cục bộ, không truyền dữ liệu. Rẻ nhất.
• Reshuffle — cột join khác shard key → engine phải băm lại và bắn hàng qua mạng cho khớp partition. Đắt.
• Broadcast — nhân bản một bảng nhỏ tới mọi leaf; hợp với bảng chiều bé (hoặc dùng reference table nhân bản sẵn).

Vì thế: chọn shard key theo cột join/lọc chủ đạo để các bảng liên quan collocate. Trong ví dụ trên, cả txn_history và bảng accounts nếu cùng SHARD KEY (account_id) thì join theo tài khoản chạy cục bộ. Cách kiểm tra một truy vấn có reshuffle hay không là đọc EXPLAIN/PROFILE (xem thực thi truy vấn) tìm các toán tử Repartition/Broadcast.

Cảnh báo skew: nếu shard key có phân bố lệch (ví dụ một tài khoản gom phần lớn giao dịch), một partition sẽ "gánh" nhiều hơn hẳn → nút cổ chai. Chọn khoá có độ phân tán (cardinality) cao và đều.

Rowstore hay columnstore? Chọn theo workload

Thực tế ngân hàng thường kết hợp: bảng giao dịch "nóng" 30 ngày gần nhất để rowstore/Universal Storage cho tra cứu tức thời, dữ liệu lịch sử để columnstore nén sâu cho báo cáo. Universal Storage giảm nhu cầu tách đôi này.

Statistics và ANALYZE

Query optimizer của SingleStore chọn thứ tự join, kiểu join (collocated/reshuffle/broadcast) và index dựa trên thống kê về số hàng, cardinality cột. Sau khi nạp lượng lớn hoặc dữ liệu thay đổi đáng kể, thống kê cũ có thể khiến optimizer chọn sai kế hoạch:

-- Cập nhật thống kê để optimizer ước lượng đúng số hàng & cardinality
ANALYZE TABLE txn_history;

-- Kiểm tra kế hoạch sau khi cập nhật: tìm segments scanned vs eliminated,
-- và các toán tử Repartition/Broadcast báo hiệu reshuffle.
EXPLAIN SELECT account_id, SUM(amount)
FROM   txn_history
WHERE  txn_ts >= '2026-07-01'
GROUP  BY account_id;

Kết hợp ANALYZE định kỳ với việc đọc PROFILE sau đổi schema là vòng lặp tối ưu chuẩn: đo → chỉnh sort/shard key → đo lại.

Use case thực tế

Bối cảnh (số liệu minh hoạ): hệ thống lõi của một ngân hàng lưu txn_history khoảng 4 tỷ giao dịch/năm trên cluster 8 leaf. Hai loại truy vấn cùng tồn tại: (1) tra cứu chi tiết 1 giao dịch cho tổng đài, (2) báo cáo tổng chi tiêu theo tài khoản trong một khoảng ngày.

Trước tối ưu, bảng columnstore SORT KEY (txn_id) và không có index phụ. Kết quả (minh hoạ):

• Báo cáo WHERE txn_ts BETWEEN ... phải quét ~toàn bộ segment vì sort theo txn_id không giúp prune theo thời gian → mỗi báo cáo ~9 giây.
• Tra cứu 1 giao dịch theo txn_id may mắn nhanh nhờ sort trùng.

Sau khi đổi sang SORT KEY (txn_ts), giữ SHARD KEY (account_id) để collocate với bảng accounts, và thêm ADD INDEX (txn_id) USING HASH (Universal Storage):

• Báo cáo theo ngày prune phần lớn segment → còn ~0,7 giây (minh hoạ), CPU và I/O giảm mạnh.
• Tra cứu 1 giao dịch vẫn nhanh nhờ hash index phụ thay vì dựa vào sort.
• Join txn_history ⋈ accounts theo account_id là collocated, không reshuffle qua mạng.

Bài học: một quyết định sort/shard đúng đổi lại cải thiện bậc độ lớn, không cần thêm phần cứng.

Ghi nhớ

• Loại index phụ thuộc storage: rowstore có HASH (point) + skiplist (range/order); columnstore có SORT KEY + SHARD KEY; Universal Storage thêm secondary HASH cho point lookup trên columnstore.
• SORT KEY quyết định segment elimination: chọn theo cột lọc range hay dùng (thời gian, id tăng) để prune nguyên segment nhờ metadata min/max.
• SHARD KEY quyết định reshuffle: chọn theo cột join/lọc để các bảng collocate; tránh khoá lệch gây skew.
• HASH cho khớp bằng (=, IN); skiplist cho range và ORDER BY.
• Universal Storage + secondary HASH index thường thay được việc phải giữ song song bảng rowstore nóng và columnstore lạnh.
• Chọn rowstore hay columnstore theo workload: OLTP điểm vs quét/aggregate lớn.
• Chạy ANALYZE sau khi nạp lớn để optimizer ước lượng đúng; xác nhận bằng EXPLAIN/PROFILE (tìm segments eliminated và Repartition/Broadcast).
• Vòng lặp tối ưu: đo bằng PROFILE → chỉnh sort/shard key/index → đo lại.

Nguồn tham khảo

• SingleStore Documentation — "Columnstore" (row segments, sort key, segment elimination): https://docs.singlestore.com
• SingleStore Documentation — "Rowstore" (in-memory, lock-free skiplist, hash index)
• SingleStore Documentation — "Universal Storage" (secondary hash index, seekable columnstore)
• SingleStore Documentation — "Understanding Keys and Indexes in SingleStore" / "CREATE TABLE" (SHARD KEY, SORT KEY, KEY USING HASH)
• SingleStore Documentation — "Distributed SQL" / "Query Optimization" (collocated join, reshuffle, broadcast, reference tables)
• SingleStore Documentation — "ANALYZE TABLE" và "Optimizing Table Data Structures" (statistics)
• MySQL Documentation (dev.mysql.com) — cú pháp tương thích cho CREATE TABLE/ALTER TABLE/index