深度解析 MySQL VARCHAR 長度語義與 LIMIT 分頁性能陷阱

深度解析 MySQL VARCHAR 長度語義與 LIMIT 分頁性能陷阱

兩個看似基礎、實則易誤用的知識點：VARCHAR(N) 中 N 的真實含義隨版本演進；LIMIT offset, rows 深分頁性能崩塌原理與優化方案。本文基於 MySQL 5.6 實測數據，還原真相。

一、VARCHAR(N)：N 代表字符數，而非字節數（≥ 4.1 版本）

實測環境

CREATE TABLE `test` (
  `name` VARCHAR(5) NOT NULL DEFAULT '',
  `info` CHAR(5) NOT NULL DEFAULT '',
  PRIMARY KEY (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

實測結果

• VARCHAR(5) 成功存入 5 個漢字「中文測試a」→ N = 字符數。
• 超長截斷：插入 6 字符只存前 5 個，報 Warning 而非 Error（非嚴格模式）。

官方文檔演進佐證

MySQL 4.1 前：「length is interpreted as number of bytes」 → VARCHAR(5) = 5 字節 = 2.5 個漢字（GBK）。

MySQL 4.1+：「maximum number of characters you want to store」 → VARCHAR(5) = 5 字符（字母/數字/漢字等同）。

結論：生產環境均為 5.6+，**直接按字符數設計長度**，無需換算字節。utf8mb4 下 1 字符最多 4 字節，VARCHAR(255) 最大佔 1020 字節 + 2 字節長度前綴，遠小於 65535 行長上限。

二、LIMIT 深分頁性能陷阱：LIMIT offset, rows vs LIMIT rows

實測場景

• 表：InnoDB，10,000 行，4 列（id、time、title、body），~170MB
• 索引：time 列有索引
• 關閉 Query Cache：SET GLOBAL query_cache_size=0; SET @@query_cache_type=OFF;
• 開啟 Profiling：SET profiling=1;

對比 SQL

-- A: 深分頁 offset=2000
SELECT id, time, title FROM cnblogs WHERE time >= 1315646940 ORDER BY time ASC LIMIT 2000, 10;

-- B: 淺分頁 offset=0
SELECT id, time, title FROM cnblogs WHERE time >= 1315646940 ORDER BY time ASC LIMIT 10;

-- C: 無 WHERE 深分頁
SELECT id, time, title FROM cnblogs ORDER BY time ASC LIMIT 3000, 10;

Profiling 結果

原理剖析

• LIMIT offset, rows：MySQL 必須掃描並丟棄前 offset 行，再取 rows 行。Offset 越大，掃描/丟棄成本線性增長。
• LIMIT rows（等價 LIMIT 0, rows）：直接取前 N 行，無丟棄開銷。

優化方案：游標式分頁（Seek Method）

記住上一頁最後一條記錄的排序鍵值，下頁查詢用 WHERE sort_key > last_val ORDER BY sort_key LIMIT page_size。

-- 首頁
SELECT id, time, title FROM cnblogs WHERE time >= 1315646940 ORDER BY time ASC LIMIT 10;
-- 假設最後一條 time = 1315647000

-- 次頁
SELECT id, time, title FROM cnblogs 
WHERE time >= 1315646940 AND time > 1315647000 
ORDER BY time ASC LIMIT 10;

• 利用索引範圍掃描，O(log N + page_size)，與頁碼無關。
• 適用：時間線、ID 遞增、唯一排序鍵場景。
• 不適用：需跳頁、排序鍵非唯一（需加 tie-breaker：WHERE (time, id) > (last_time, last_id)）。