取前 N 筆資料 (Top-N Query)
| 資料庫 | 標準語法 | 備註 |
|---|
| Oracle | SELECT * FROM table_name WHERE ROWNUM <= N | Oracle 12c+ 可用 FETCH FIRST N ROWS ONLY |
| DB2 | SELECT * FROM table_name FETCH FIRST N ROWS ONLY | 或使用 ROW_NUMBER() OVER() 視窗函數 |
| SQL Server | SELECT TOP N * FROM table_name | 搭配 ORDER BY 確保確定性 |
| MySQL / MariaDB | SELECT * FROM table_name LIMIT N | LIMIT offset, N 支援分頁 |
| PostgreSQL | SELECT * FROM table_name LIMIT N | 標準 SQL:2008 FETCH FIRST N ROWS ONLY 亦支援 |
| SQLite | SELECT * FROM table_name LIMIT N | |
| ANSI SQL:2008 標準 | SELECT * FROM table_name FETCH FIRST N ROWS ONLY | Oracle 12c、PostgreSQL、DB2、SQL Server 2012+ 支援 |
帶排序的 Top-N (確定性結果)
-- Oracle 12c+
SELECT * FROM table_name ORDER BY col1 DESC FETCH FIRST 10 ROWS ONLY;
-- SQL Server
SELECT TOP 10 * FROM table_name ORDER BY col1 DESC;
-- MySQL / PostgreSQL
SELECT * FROM table_name ORDER BY col1 DESC LIMIT 10;
-- DB2
SELECT * FROM table_name ORDER BY col1 DESC FETCH FIRST 10 ROWS ONLY;
分頁查詢 (Pagination)
| 資料庫 | 語法 (第 2 頁，每頁 10 筆) |
|---|
| Oracle 12c+ | SELECT * FROM table_name ORDER BY id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY |
| SQL Server 2012+ | SELECT * FROM table_name ORDER BY id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY |
| PostgreSQL / MySQL | SELECT * FROM table_name ORDER BY id LIMIT 10 OFFSET 10 |
| Oracle 11g 及之前 | 需使用巢狀 ROWNUM 或視窗函數 ROW_NUMBER() |
隨機抽樣前 N 筆
| 資料庫 | 語法 | 效能備註 |
|---|
| DB2 | SELECT * FROM mytable ORDER BY RAND() DESC FETCH FIRST 10 ROWS ONLY | 全表掃描 + 排序，大表極慢 |
| SQL Server | SELECT TOP 10 * FROM userinf ORDER BY NEWID() | NEWID() 產生 GUID 排序，大表效能差 |
| MySQL | SELECT * FROM tablename ORDER BY RAND() LIMIT 10 | 臨時表 + 檔案排序，大表不可用 |
| PostgreSQL | SELECT * FROM tablename ORDER BY RANDOM() LIMIT 10 | 同全表排序 |
| Oracle | SELECT * FROM (SELECT * FROM table_name ORDER BY DBMS_RANDOM.VALUE) WHERE ROWNUM <= 10 | 子查詢排序 |
大表高效隨機抽樣替代方案
伯努利採樣 / 系統採樣 (TABLESAMPLE)
-- SQL Server (系統採樣，頁面層級)
SELECT * FROM large_table TABLESAMPLE (10 PERCENT) REPEATABLE (42);
-- PostgreSQL (伯努利/系統採樣)
SELECT * FROM large_table TABLESAMPLE BERNOULLI (10) REPEATABLE (42);
SELECT * FROM large_table TABLESAMPLE SYSTEM (10) REPEATABLE (42);
優點：極快，不掃描全表。缺點：非精確 N 筆、可能有叢集偏差、REPEATABLE 種子確保可重現。
主鍵範圍隨機抽樣 (適用連續整數主鍵)
-- 取得主鍵範圍
DECLARE @min_id INT, @max_id INT, @sample_size INT = 10;
SELECT @min_id = MIN(id), @max_id = MAX(id) FROM large_table;
-- 產生隨機 ID 清單 (應用層或 T-SQL 迴圈)
-- 然後查詢
SELECT * FROM large_table WHERE id IN (/* 隨機 ID 清單 */);
預先計算隨機排序欄位
-- 表中新增欄位
ALTER TABLE large_table ADD rand_order INT;
UPDATE large_table SET rand_order = CHECKSUM(NEWID()); -- 或 RAND()
-- 建立索引
CREATE INDEX IX_rand_order ON large_table(rand_order);
-- 快速取樣
SELECT TOP 10 * FROM large_table ORDER BY rand_order;
適合固定抽樣比例、定期重算的場景。
效能比較總結
| 方法 | 適用資料量 | 隨機品質 | 查詢速度 | 實作複雜度 |
|---|
| ORDER BY RAND/NEWID | < 10 萬筆 | 高 | 慢 (全表排序) | 低 |
| TABLESAMPLE | 任意 (百萬+) | 中 (頁面/列層級) | 極快 | 低 |
| 主鍵範圍 + IN | 任意 (需連續 PK) | 高 | 快 (索引搜尋) | 中 |
| 預計算 rand_order | 任意 | 高 | 極快 (索引排序) | 中 (需維護) |
最佳實踐建議
- 生產環境大表嚴禁 ORDER BY RAND()，會導致 CPU 飆升、磁碟 I/O 暴增、查詢超時
- 優先使用
TABLESAMPLE (SQL Server/PostgreSQL) 或主鍵範圍法
- 需精確 N 筆且高隨機性時，應用層產生隨機 ID 清單再
IN 查詢
- 分頁查詢大偏移量 (OFFSET 1000000) 同樣效能差，改用「游標分頁」：
WHERE id > @last_id ORDER BY id LIMIT 10
- 跨資料庫開發時，封裝成通用 Repository 介面，內部針對各 DB 實作最佳語法
