MyCat 連續分片實戰：按日期（天）分片配置與路由分析

三、建表與寫入驗證

CREATE TABLE ORDER0402(
  ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  PROVINCE VARCHAR(16),
  SN VARCHAR(64),
  CREATE_TIME DATETIME
);

測試數據

-- 2016-01-03 屬於第 0 個 10 天區間 → dn21
INSERT INTO ORDER0402(ID,PROVINCE,SN,CREATE_TIME)
VALUES(10001,'shanghai','shanghai10006_2016-01-03','2016-01-03');

-- 2016-01-13 屬於第 1 個 10 天區間 → dn22
INSERT INTO ORDER0402(ID,PROVINCE,SN,CREATE_TIME)
VALUES(5000001,'shanghai','shanghai10006_2016-01-13','2016-01-13');

EXPLAIN 驗證

mysql> EXPLAIN INSERT ... VALUES(10001,...,'2016-01-03');
+-----------+--------------------------------------------------+
| DATA_NODE | SQL                                              |
+-----------+--------------------------------------------------+
| dn21      | INSERT ... VALUES(10001,...,'2016-01-03')        |
+-----------+--------------------------------------------------+

mysql> EXPLAIN INSERT ... VALUES(5000001,...,'2016-01-13');
+-----------+--------------------------------------------------+
| DATA_NODE | SQL                                              |
+-----------+--------------------------------------------------+
| dn22      | INSERT ... VALUES(5000001,...,'2016-01-13')      |
+-----------+--------------------------------------------------+

四、寫入路由日誌分析

關鍵節點（以 2016-01-13 寫入為例）：

1. ServerConnection INSERT ... CREATE_TIME='2016-01-13'
2. route={ 1 -> dn22{INSERT ...} } 直接命中 dn22
3. NonBlockingSession release connection MySQLConnection[id=17, host=13.23.1.11, port=3327, attachment=dn22{...}]
4. PhysicalDatasource release channel ... modifiedSQLExecuted=false

日期分片同樣屬於單點直連路由，無緩存構建開銷。

五、查詢路由行為

1. 精確日期查詢 → 單節點

mysql> EXPLAIN SELECT * FROM ORDER0402 WHERE CREATE_TIME='2016-01-03';
+-----------+--------------------------------------------------+
| DATA_NODE | SQL                                              |
+-----------+--------------------------------------------------+
| dn21      | SELECT * FROM ORDER0402 WHERE CREATE_TIME='2016-01-03' LIMIT 100 |
+-----------+--------------------------------------------------+

日誌：SQLRouteCache add cache ... route={ 1 -> dn21{...} }

2. 日期範圍查詢 → 全節點

mysql> EXPLAIN SELECT * FROM ORDER0402 WHERE CREATE_TIME<'2016-01-04';
+-----------+--------------------------------------------------+
| DATA_NODE | SQL                                              |
+-----------+--------------------------------------------------+
| dn21      | SELECT * FROM ORDER0402 WHERE CREATE_TIME<'2016-01-04' LIMIT 100 |
| dn22      | SELECT * FROM ORDER0402 WHERE CREATE_TIME<'2016-01-04' LIMIT 100 |
+-----------+--------------------------------------------------+

即使數據僅在 dn21，範圍條件導致廣播查詢。

3. 非分片字段查詢 → 全節點

mysql> EXPLAIN SELECT * FROM ORDER0402 WHERE SN='shanghai10006_2016-01-03';
+-----------+--------------------------------------------------+
| DATA_NODE | SQL                                              |
+-----------+--------------------------------------------------+
| dn21      | SELECT * FROM ORDER0402 WHERE SN='shanghai10006_2016-01-03' LIMIT 100 |
| dn22      | SELECT * FROM ORDER0402 WHERE SN='shanghai10006_2016-01-03' LIMIT 100 |
+-----------+--------------------------------------------------+

六、運維建議

• 預分片規劃：根據業務增長預估未來 1-2 年所需分片數，一次性在 schema.xml 定義足夠的 dataNode。
• 歷史數據歸檔：舊分片可遷移至廉價存儲或只讀實例，減少主庫壓力。
• 跨分片查詢優化：業務層盡量按日期精確查詢；必須跨分片時，考慮彙總表或異構索引（ES/ClickHouse）。
• 時區一致性：應用層、MyCat、後端 MySQL 統一使用 UTC 或固定時區，避免日期邊界計算偏差。