MySQL 實現 ROWNUM/行號的四種方案與性能對比

背景

MySQL 5.1/5.5/5.6 均無原生 ROWNUM 或 ROW_NUMBER()（8.0 才支持窗口函數）。常見替代方案：

測試數據準備

CREATE TABLE tbl (id INT PRIMARY KEY, col INT);
INSERT INTO tbl VALUES (1,26),(2,46),(3,35),(4,68),(5,93),(6,92);
-- 按 col 排序後期望行號：1→26, 2→35, 3→46, 4→68, 5→92, 6→93

方案對比

1. 應用層計數（推薦）

# PHP/Python/Java 等
row_num = 0
while row = fetch():
    row_num += 1
    print(row_num, row)

• 優點：零資料庫開銷、最靈活、支持複雜分頁邏輯
• 缺點：需修改應用代碼

2. 用戶變量（會話級）

SET @rownum := 0;
SELECT @rownum := @rownum + 1 AS rownum, id, col
FROM tbl ORDER BY col;

• 優點：純 SQL、無連接開銷
• 缺點：
  — 變量為連接級，連接池復用需顯式 SET @rownum=0
  — 優化器可能調整執行順序導致行號錯亂（5.7+ 較穩定）
  — 無法直接用於子查詢/派生表

3. 自連接/笛卡爾積（不推薦）

SELECT a.id, a.col, COUNT(*) AS rownum
FROM tbl a JOIN tbl b ON a.col >= b.col
GROUP BY a.id, a.col
ORDER BY a.col;

• 複雜度：O(N²)，百萬級數據不可用
• 唯一優勢：無變量依賴，純標準 SQL

4. 相關子查詢

SELECT a.*, 
       (SELECT COUNT(*) FROM tbl WHERE col <= a.col) AS rownum
FROM tbl a
ORDER BY a.col;

• 複雜度：O(N²)，依賴索引優化
• 適用場景：小結果集（< 1萬行）、臨時分析

性能測試參考（6 行數據）

MySQL 8.0+ 原生窗口函數（標準解法）

SELECT id, col,
       ROW_NUMBER() OVER (ORDER BY col) AS rownum,
       RANK() OVER (ORDER BY col) AS rnk,
       DENSE_RANK() OVER (ORDER BY col) AS drnk
FROM tbl;

• 符合 ANSI SQL:2003 標準
• 支持 PARTITION BY 分組行號
• 執行計劃可見，優化器友好

選型建議

1. MySQL 8.0+：直接用窗口函數
2. 5.7/5.6 應用可改：應用層計數
3. 5.7/5.6 僅 SQL：用戶變量 + ORDER BY 子查詢保證順序：
   SELECT @rn:=@rn+1 rownum, id, col FROM (SELECT id, col FROM tbl ORDER BY col) t, (SELECT @rn:=0) r;
4. 避免：自連接、子查詢計數（大數據場景）