面對單機十億級資料量，傳統「全量索引、單表查詢」模式難以支撐毫秒級響應。本文分享實戰驗證的核心設計思想：只查必要表、只建必要索引、動態拼接 SQL，配合資訊庫元數據過濾無效表，在普通筆電環境 (i7-4700, 8G RAM, USB3.0 2TB HDD) 實測 20 億資料查詢 1~3 秒完成。

一、核心優化原則

1. 資料隔離分表：依業務特徵將資料拆分至不同表，每表 < 1 億行。
2. 選擇性建立索引：僅對「查詢條件列」且「該表確實包含目標資料」的欄位建立索引，避免無效索引佔用空間、拖慢寫入。
3. 動態 SQL 路由：查詢前先檢查 information_schema.statistics 判斷哪些表擁有目標欄位索引，僅對這些表發起查詢並 UNION ALL 合併結果。

二、實戰場景：社工庫郵箱查詢

假設兩表 A、B 結構相同，但資料互補：

• A 表：username 非空、email 為空
• B 表：email 非空、username 為空
• 兩表 password 皆非空

索引策略：

• 兩表皆建 password 索引
• A 表建 username 索引，不建 email 索引
• B 表建 email 索引，不建 username 索引

三、動態過濾無效表 SQL

SELECT TABLE_NAME
FROM information_schema.statistics
WHERE INDEX_NAME != 'PRIMARY'
  AND table_schema = 'info'
  AND COLUMN_NAME = 'email'
GROUP BY TABLE_NAME;

應用層獲取表名列表後，動態生成：

SELECT * FROM `B` WHERE `email` = 'root@cnseay.com';
-- 僅查擁有 email 索引的表，自動跳過 A 表

四、關鍵配置與硬體建議

• 分表分區：單表 < 1 億行，大表拆分 2-3 表
• 索引精簡：僅索引高選擇性、高頻查詢欄位
• MySQL 參數：innodb_buffer_pool_size 設物理記憶體 60-70%，innodb_flush_log_at_trx_commit=2 (非核心業務)
• 硬體：SSD RAID10、大記憶體、獨立 temp 目錄

五、避坑指南

• 不要盲目給所有查詢欄位加索引——索引會佔用大量磁碟與記憶體，拖慢 INSERT/UPDATE。
• 避免 SELECT *，只取必要欄位減少 I/O。
• 大表 COUNT(*) 極慢，改用近似值或單獨統計表。
• 定期 ANALYZE TABLE 更新統計資訊，確保優化器選擇正確執行計畫。

此架構在「查詢為主、寫入可控、資料可按業務維度自然分片」的場景效果顯著。若寫入極高或需強一致跨表事務，需引入分散式中間件或 NewSQL 方案。