：數(shù)據(jù)模型、狀態(tài)機與派單調度實戰(zhàn))
簡介這份資源是面向Android初學者與移動應用開發(fā)練習者的同城物流派送系統(tǒng)項目源碼圍繞城市內快速配送場景完整呈現(xiàn)從訂單發(fā)起到司機搶單、再到貨物送達的業(yè)務閉環(huán)可用于學習SQLite數(shù)據(jù)存儲、賬號注冊登錄、自定義適配器列表展示、搶單與派送狀態(tài)流轉以及GPS定位與路線規(guī)劃等典型模塊的實現(xiàn)思路。壓縮包共275個文件約53.42MB以94個png界面素材、86個class編譯文件、39個xml布局與配置、31個java源碼為主另含9個jar依賴、5個so庫及apk安裝包等兼顧源碼閱讀與直接運行體驗。目前已有323人學習下載。讀者可從中獲得一套結構完整的項目參考對照數(shù)據(jù)庫增刪改查、用戶認證與適配器數(shù)據(jù)綁定等環(huán)節(jié)理解Android應用的組織方式適合作為課程設計或畢業(yè)設計的實踐藍本。1. 同城物流派送系統(tǒng)拆解一個 SQLite 單文件撐起整套調度邏輯同城物流派送這類項目最容易被做復雜訂單表、騎手表、路線表、狀態(tài)機、結算流水一上來就上 MySQL 加 Redis本地跑個 Demo 還得先裝服務、配賬號、開端口。我這次拆的這份資源走的是另一條路——整套派送調度邏輯壓在一個 SQLite 單文件里訂單落庫、騎手狀態(tài)流轉、派單匹配、軌跡點記錄全在一個.db文件內完成拷走就能跑換臺機器不用改一行連接配置。它解決的是中小規(guī)模同城配送場景下「數(shù)據(jù)量不大但事務一致性要求不低」的問題適合想快速驗證派單算法、做課程設計、或者給門店做輕量調度工具的從業(yè)者。SQLite 在這里不是妥協(xié)是選型。2. 數(shù)據(jù)模型與派單狀態(tài)機先把表設計對后面少改三次代碼拿到這類資源我一般不看界面先看表結構。同城派送的復雜度八成壓在數(shù)據(jù)模型上訂單從創(chuàng)建到簽收要經(jīng)過哪些狀態(tài)、騎手接單后訂單和騎手怎么綁定、派單失敗怎么回滾這些想不清楚后面每加一個功能都要動表。這份資源的表設計不算多但把關鍵關系收住了值得先講透再動手。2.1 四張核心表與它們的外鍵關系資源里的核心表大致是四張訂單表orders、騎手表couriers、派單記錄表assignments、軌跡點表track_points。訂單表存收貨地址經(jīng)緯度、期望時段、當前狀態(tài)騎手表存當前位置、載單上限、在線狀態(tài)派單記錄表是訂單和騎手的多對多橋接帶派單時間和結果軌跡點表按時間戳追加騎手位置。關鍵設計點在assignments這張橋接表它沒有把courier_id直接塞進orders而是單獨記錄每一次派單嘗試包括失敗的那次。這個選擇很關鍵——同城派送里「派了但騎手拒單」是高頻事件如果只在訂單表上掛一個騎手字段拒單后歷史就丟了事后排查「這單為什么繞了三圈才派出去」沒有依據(jù)。橋接表讓每次嘗試都留痕代價是多一次 join。-- 訂單表狀態(tài)用整數(shù)枚舉避免字符串比較 CREATE TABLE orders ( id INTEGER PRIMARY KEY AUTOINCREMENT, pickup_lat REAL NOT NULL, pickup_lng REAL NOT NULL, drop_lat REAL NOT NULL, drop_lng REAL NOT NULL, status INTEGER NOT NULL DEFAULT 0, -- 0待派 1已派 2取貨中 3配送中 4已簽收 5已取消 created_at INTEGER NOT NULL, -- Unix 秒統(tǒng)一時區(qū) expect_start INTEGER, -- 期望送達時段起點 expect_end INTEGER ); -- 派單記錄每次嘗試一行失敗也留痕 CREATE TABLE assignments ( id INTEGER PRIMARY KEY AUTOINCREMENT, order_id INTEGER NOT NULL REFERENCES orders(id), courier_id INTEGER NOT NULL REFERENCES couriers(id), result INTEGER NOT NULL, -- 0待響應 1接受 2拒單 3超時 assigned_at INTEGER NOT NULL, responded_at INTEGER ); CREATE INDEX idx_assign_order ON assignments(order_id); CREATE INDEX idx_assign_courier ON assignments(courier_id, result);上面這段建表語句里有兩個參數(shù)值得說。status用整數(shù)而不是文本是因為 SQLite 沒有原生枚舉文本狀態(tài)在批量更新時容易拼錯且索引效率低整數(shù)配合應用層常量最穩(wěn)。created_at和expect_start統(tǒng)一存 Unix 秒而不是文本時間是為了后面算「距離期望時段還有多久」時能直接做減法不用在 SQL 里調日期函數(shù)——SQLite 的日期函數(shù)跨時區(qū)處理是玄學能避開就避開。兩個索引分別服務「查某訂單的派單歷史」和「查某騎手當前有效單」這兩個查詢在派單循環(huán)里會被高頻調用。2.2 狀態(tài)流轉與事務邊界狀態(tài)機是這類系統(tǒng)最容易翻車的地方。訂單狀態(tài)從 0 到 4 是一條單向鏈但取消5可以從任意前置狀態(tài)進入騎手拒單則要把訂單從 1 退回 0。這份資源把狀態(tài)變更全部收在一個函數(shù)里不允許業(yè)務代碼直接UPDATE orders SET status這個約束我認可。import sqlite3 # 合法狀態(tài)遷移當前狀態(tài) - 允許的下一狀態(tài)集合 TRANSITIONS { 0: {1, 5}, # 待派 - 已派 / 取消 1: {2, 0, 5}, # 已派 - 取貨中 / 退回待派(拒單) / 取消 2: {3, 5}, # 取貨中 - 配送中 / 取消 3: {4, 5}, # 配送中 - 已簽收 / 取消 4: set(), # 終態(tài) 5: set(), } def change_status(conn, order_id, new_status): cur conn.cursor() cur.execute(SELECT status FROM orders WHERE id?, (order_id,)) row cur.fetchone() if row is None: raise ValueError(forder {order_id} not found) old row[0] if new_status not in TRANSITIONS.get(old, set()): raise ValueError(fillegal transition {old} - {new_status}) cur.execute( UPDATE orders SET status? WHERE id? AND status?, (new_status, order_id, old), # 帶舊狀態(tài)做樂觀鎖 ) if cur.rowcount ! 1: raise RuntimeError(concurrent status change detected) conn.commit()這段代碼的邏輯說明先查當前狀態(tài)用TRANSITIONS字典校驗遷移合法性非法遷移直接拋錯而不是靜默忽略——靜默忽略是血淚經(jīng)驗線上出問題時你根本不知道哪一步被吞了。UPDATE語句里帶AND status?是樂觀鎖防止兩個并發(fā)請求同時把訂單從 1 改到 2 和 0rowcount不等于 1 就說明有人搶先改了拋錯讓上層重試。參數(shù)上conn由調用方傳入而不是函數(shù)內新建是為了讓多個狀態(tài)變更能包在同一個事務里如果你要一次改多張表把commit提到外層。提示SQLite 默認的isolation_level是隱式事務Python 的sqlite3模塊在 DML 前會自動開事務。上面代碼里顯式commit是必要的但如果你在外層已經(jīng)開了事務這里的commit會提前提交需要改成由外層統(tǒng)一控制。2.3 派單匹配的 SQL 寫法派單的核心查詢是「找一批在線、未滿載、距離取貨點最近的騎手」。這份資源沒有上空間索引用的是經(jīng)緯度平方差近似排序在同城幾公里范圍內誤差可以接受。-- 候選騎手在線、載單未滿、按到取貨點距離平方升序 SELECT c.id, (c.lat - :pickup_lat) * (c.lat - :pickup_lat) (c.lng - :pickup_lng) * (c.lng - :pickup_lng) AS dist2 FROM couriers c WHERE c.online 1 AND c.load_count c.max_load AND c.id NOT IN ( SELECT courier_id FROM assignments WHERE order_id :order_id AND result IN (1, 0) -- 已接受或待響應 ) ORDER BY dist2 ASC LIMIT 5;邏輯說明dist2是距離平方省掉開方排序結果和真實距離一致但快一點。NOT IN子查詢排除掉已經(jīng)對這筆訂單接受或待響應的騎手避免重復派給同一個人。LIMIT 5是候選池大小實際派單時按順序逐個發(fā)請求前一個拒單再試下一個。參數(shù):pickup_lat這類命名占位符比?可讀性好Python 里用字典傳參即可。注意load_count是冗余字段每次接單要同步加一這個一致性靠應用層保證SQLite 沒有觸發(fā)器約束的話容易漏——這是后面避坑章要展開的點。3. 從零跑通派單閉環(huán)建庫、灌數(shù)據(jù)、模擬一輪調度表設計講完接下來是讓它動起來。這一章按「建庫 → 灌測試數(shù)據(jù) → 跑一輪派單 → 看結果」的順序走每一步都能直接抄。我一般會先造一批假數(shù)據(jù)把閉環(huán)跑通再談優(yōu)化不然連狀態(tài)流轉對不對都驗證不了。3.1 初始化數(shù)據(jù)庫與 PRAGMA 調優(yōu)SQLite 默認配置偏保守同城派送這種寫多讀也多的小系統(tǒng)開庫時設幾個 PRAGMA 能明顯減少鎖等待。import sqlite3 def open_db(pathdispatch.db): conn sqlite3.connect(path, timeout5.0) cur conn.cursor() cur.execute(PRAGMA journal_modeWAL;) # 讀寫不互斥 cur.execute(PRAGMA synchronousNORMAL;) # 兼顧安全與速度 cur.execute(PRAGMA foreign_keysON;) # 外鍵約束默認是關的 cur.execute(PRAGMA busy_timeout5000;) # 鎖等待 5 秒 conn.commit() return conn參數(shù)說明journal_modeWAL讓讀操作不被寫操作阻塞派單循環(huán)里一邊寫派單記錄一邊查騎手列表不開 WAL 會頻繁database is locked。synchronousNORMAL在 WAL 模式下已經(jīng)足夠安全比默認FULL快不少代價是極端斷電可能丟最后一個事務——同城派送場景可以接受。foreign_keysON必須顯式開SQLite 默認不校驗外鍵不開的話assignments里能插進不存在的order_id臟數(shù)據(jù)就是這么來的。busy_timeout是等鎖的超時設 5 秒比默認立即報錯友好。3.2 灌入模擬訂單與騎手測試數(shù)據(jù)要覆蓋邊界有騎手滿載、有騎手離線、有訂單在期望時段邊緣。下面這段造 20 個騎手、50 個訂單坐標在一個虛構城區(qū)的矩形范圍內隨機。import random, time def seed(conn, n_couriers20, n_orders50): cur conn.cursor() now int(time.time()) for i in range(n_couriers): cur.execute( INSERT INTO couriers(lat,lng,online,load_count,max_load) VALUES(?,?,?,?,?), (30.0 random.random(), 120.0 random.random(), 1 if random.random() 0.2 else 0, # 兩成離線 0, random.choice([2, 3, 4])), ) for i in range(n_orders): cur.execute( INSERT INTO orders(pickup_lat,pickup_lng,drop_lat,drop_lng,status,created_at,expect_start,expect_end) VALUES(?,?,?,?,0,?,?,?), (30.0 random.random(), 120.0 random.random(), 30.0 random.random(), 120.0 random.random(), now, now 1800, now 3600), ) conn.commit()邏輯說明online用random.random() 0.2讓約兩成騎手離線是為了驗證派單查詢的WHERE online1真的生效。max_load在 2 到 4 之間隨機制造滿載邊界。訂單的expect_start設在 30 分鐘后、expect_end在 1 小時后方便后面測「臨近超時優(yōu)先派」的邏輯。坐標范圍是虛構的實際用時換成你所在城區(qū)的經(jīng)緯度矩形即可。3.3 跑一輪派單并核對結果派單主循環(huán)的邏輯是取待派訂單按期望時段緊迫度排序對每單查候選騎手逐個嘗試成功則更新訂單和騎手。def dispatch_round(conn): cur conn.cursor() cur.execute(SELECT id,pickup_lat,pickup_lng FROM orders WHERE status0 ORDER BY expect_end ASC) orders cur.fetchall() for oid, plat, plng in orders: cur.execute( SELECT c.id FROM couriers c WHERE c.online1 AND c.load_count c.max_load AND c.id NOT IN (SELECT courier_id FROM assignments WHERE order_id? AND result IN (0,1)) ORDER BY (c.lat-?)*(c.lat-?)(c.lng-?)*(c.lng-?) ASC LIMIT 5 , (oid, plat, plat, plng, plng)) candidates [r[0] for r in cur.fetchall()] for cid in candidates: try: cur.execute(BEGIN) cur.execute(INSERT INTO assignments(order_id,courier_id,result,assigned_at) VALUES(?,?,0,?), (oid, cid, int(time.time()))) cur.execute(UPDATE orders SET status1 WHERE id? AND status0, (oid,)) if cur.rowcount ! 1: raise RuntimeError(order taken) cur.execute(UPDATE couriers SET load_countload_count1 WHERE id? AND load_countmax_load, (cid,)) if cur.rowcount ! 1: raise RuntimeError(courier full) conn.commit() break except Exception: conn.rollback() continue邏輯說明外層按expect_end升序越臨近超時的訂單越先派。內層對每個候選騎手開一個事務插入派單記錄、改訂單狀態(tài)、加騎手載單數(shù)三步要么全成要么全回滾。UPDATE orders ... AND status0和UPDATE couriers ... AND load_countmax_load都帶條件rowcount校驗是并發(fā)下的后悔藥——兩個派單線程同時處理同一單或同一騎手時第二個會失敗并回滾。參數(shù)上LIMIT 5控制候選池太大浪費查詢太小可能派不出去5 是個經(jīng)驗值。跑完一輪后查SELECT status, COUNT(*) FROM orders GROUP BY status就能看到派出去多少、還剩多少待派。4. 避坑與排查SQLite 派送系統(tǒng)最容易翻車的五個點這套東西跑通不難難的是跑穩(wěn)。下面五條是我在類似項目里真實踩過或見別人踩過的按「現(xiàn)象 → 原因 → 解決」寫遇到對應癥狀直接對號入座。4.1 現(xiàn)象派單循環(huán)跑幾百單后報database is locked原因沒開 WAL或者開了 WAL 但多個連接同時寫。SQLite 同一時刻只允許一個寫者派單循環(huán)里如果每個訂單都新開連接寫鎖競爭會非常激烈。另一個常見原因是事務沒及時提交長事務占著寫鎖。解決開庫時統(tǒng)一走open_db()設journal_modeWAL和busy_timeout整個派單循環(huán)復用同一個連接不要每單新建事務粒度控制在「一單一次」別把整輪派單包成一個大事務。如果確實要多線程寫操作串行化讀可以并發(fā)。4.2 現(xiàn)象騎手load_count和實際有效單數(shù)對不上原因load_count是冗余字段接單時加一、簽收或取消時減一漏了任何一處就會漂移。最常見的是訂單取消時忘了減或者拒單回滾時減了兩次。解決不要靠人工記增減點寫一個對賬函數(shù)定期重算UPDATE couriers SET load_count(SELECT COUNT(*) FROM assignments a JOIN orders o ON a.order_ido.id WHERE a.courier_idcouriers.id AND a.result1 AND o.status IN (1,2,3))。更穩(wěn)的做法是干脆不存load_count每次派單時用子查詢實時算數(shù)據(jù)量小的時候性能完全夠省掉一致性維護。4.3 現(xiàn)象同一訂單被派給兩個騎手兩人都來取貨原因派單時先查候選再插入查和插之間沒有鎖兩個調度線程同時處理同一單都查到空候選然后各自插入。這是典型的檢查后使用競態(tài)。解決插入派單記錄前先UPDATE orders SET status1 WHERE id? AND status0用rowcount判斷是否搶到搶到再插記錄或者給assignments加唯一約束UNIQUE(order_id, courier_id, result)配合事務。核心是讓「占單」這個動作原子化別把判斷和寫入分開。4.4 現(xiàn)象按距離排序結果和地圖上看到的順序不一致原因用經(jīng)緯度平方差排序在緯度跨度大或者靠近極地時誤差明顯另外如果經(jīng)緯度存的是文本或者混了不同坐標系比如有的點存了偏移后的坐標排序直接失效。解決同城幾公里范圍內平方差夠用但前提是所有坐標同一坐標系、同一單位。入庫前統(tǒng)一轉成 WGS84 的十進制度別混?？缍瘸^十幾公里時改用 Haversine 公式或者上 R-Tree 索引SQLite 自帶rtree模塊建虛擬表按矩形范圍先篩再精排。4.5 現(xiàn)象程序重啟后部分訂單狀態(tài)卡在「已派」但騎手說沒收到原因派單記錄插了、訂單狀態(tài)改了但通知騎手的動作在事務外通知失敗后事務已提交狀態(tài)和實際不一致。或者反過來通知發(fā)了但事務回滾了。解決把「通知」做成可重試的獨立步驟訂單狀態(tài)里加一個「已派待確認」騎手確認后才轉「取貨中」。通知失敗時靠定時任務掃「已派待確認」超時的單重新派。別指望一次通知必達同城派送的常態(tài)就是重試。5. 進階用觸發(fā)器和對賬任務把一致性焊死前面幾章把閉環(huán)跑通了但冗余字段和狀態(tài)一致性還是靠應用層自覺。真要在生產(chǎn)邊緣用我一般會加兩層保險一層是 SQLite 觸發(fā)器兜底一層是定時對賬任務。觸發(fā)器負責「該減的必須減」對賬負責「已經(jīng)漂的拉回來」兩層配合基本不用再擔心數(shù)據(jù)對不上。先看觸發(fā)器。訂單狀態(tài)從「已派/取貨中/配送中」進入終態(tài)簽收或取消時對應騎手的load_count必須減一。用AFTER UPDATE觸發(fā)器實現(xiàn)CREATE TRIGGER trg_order_release_load AFTER UPDATE OF status ON orders WHEN OLD.status IN (1,2,3) AND NEW.status IN (4,5) BEGIN UPDATE couriers SET load_count MAX(load_count - 1, 0) WHERE id ( SELECT courier_id FROM assignments WHERE order_id NEW.id AND result 1 ORDER BY assigned_at DESC LIMIT 1 ); END;邏輯說明AFTER UPDATE OF status只在狀態(tài)列被改時觸發(fā)避免無關更新誤觸。WHEN子句限定「從進行中進入終態(tài)」重復觸發(fā)不會重復減。MAX(load_count - 1, 0)防止減成負數(shù)這是兜底中的兜底。子查詢取最近一次接受的派單記錄因為一單可能被派過多次只有最后接受的那個騎手該減。參數(shù)上沒有可調的但要注意觸發(fā)器在事務內執(zhí)行如果外層事務回滾觸發(fā)器效果也回滾這點和普通語句一致。再看對賬任務。觸發(fā)器能覆蓋大部分路徑但歷史臟數(shù)據(jù)、手工改庫、觸發(fā)器創(chuàng)建前的數(shù)據(jù)都管不到所以需要一個定時跑的校正腳本def reconcile(conn): cur conn.cursor() cur.execute( UPDATE couriers SET load_count ( SELECT COUNT(*) FROM assignments a JOIN orders o ON a.order_id o.id WHERE a.courier_id couriers.id AND a.result 1 AND o.status IN (1,2,3) ) ) conn.commit() return cur.rowcount這個腳本把每個騎手的load_count重算成「當前有效單數(shù)」rowcount返回被修正的行數(shù)可以打到日志里監(jiān)控漂移頻率。我一般設成每 10 分鐘跑一次或者每次派單批次結束后跑一次。如果rowcount長期大于 0說明觸發(fā)器有漏網(wǎng)路徑得回去查。驗證方法也簡單造一批訂單隨機走完簽收和取消然后跑一次reconcile看rowcount是否為 0。不為 0 就說明有路徑?jīng)]被觸發(fā)器覆蓋。我習慣在每次改狀態(tài)機代碼后都跑一遍這個驗證比肉眼看代碼靠譜。注意觸發(fā)器會增加寫操作開銷派單高峰期如果發(fā)現(xiàn)寫入變慢先查是不是觸發(fā)器里的子查詢沒走索引。assignments(order_id)和assignments(courier_id, result)這兩個索引就是為它準備的別刪。從那以后我每次給這類單文件數(shù)據(jù)庫加冗余字段都強制先寫觸發(fā)器再寫業(yè)務代碼最后補一個對賬腳本兜底——順序反了后面補一致性就是無底洞。希望幫到你。本文還有配套的精品資源點擊獲取 SQLite單文件驅動同城物流派送系統(tǒng)：數(shù)據(jù)模型、狀態(tài)機與派單調度實戰(zhàn)
簡介這份資源是面向Android初學者與移動應用開發(fā)練習者的同城物流派送系統(tǒng)項目源碼圍繞城市內快速配送場景完整呈現(xiàn)從訂單發(fā)起到司機搶單、再到貨物送達的業(yè)務閉環(huán)可用于學習SQLite數(shù)據(jù)存儲、賬號注冊登錄、自定義適配器列表展示、搶單與派送狀態(tài)流轉以及GPS定位與路線規(guī)劃等典型模塊的實現(xiàn)思路。壓縮包共275個文件約53.42MB以94個png界面素材、86個class編譯文件、39個xml布局與配置、31個java源碼為主另含9個jar依賴、5個so庫及apk安裝包等兼顧源碼閱讀與直接運行體驗。目前已有323人學習下載。讀者可從中獲得一套結構完整的項目參考對照數(shù)據(jù)庫增刪改查、用戶認證與適配器數(shù)據(jù)綁定等環(huán)節(jié)理解Android應用的組織方式適合作為課程設計或畢業(yè)設計的實踐藍本。1. 同城物流派送系統(tǒng)拆解一個 SQLite 單文件撐起整套調度邏輯同城物流派送這類項目最容易被做復雜訂單表、騎手表、路線表、狀態(tài)機、結算流水一上來就上 MySQL 加 Redis本地跑個 Demo 還得先裝服務、配賬號、開端口。我這次拆的這份資源走的是另一條路——整套派送調度邏輯壓在一個 SQLite 單文件里訂單落庫、騎手狀態(tài)流轉、派單匹配、軌跡點記錄全在一個.db文件內完成拷走就能跑換臺機器不用改一行連接配置。它解決的是中小規(guī)模同城配送場景下「數(shù)據(jù)量不大但事務一致性要求不低」的問題適合想快速驗證派單算法、做課程設計、或者給門店做輕量調度工具的從業(yè)者。SQLite 在這里不是妥協(xié)是選型。2. 數(shù)據(jù)模型與派單狀態(tài)機先把表設計對后面少改三次代碼拿到這類資源我一般不看界面先看表結構。同城派送的復雜度八成壓在數(shù)據(jù)模型上訂單從創(chuàng)建到簽收要經(jīng)過哪些狀態(tài)、騎手接單后訂單和騎手怎么綁定、派單失敗怎么回滾這些想不清楚后面每加一個功能都要動表。這份資源的表設計不算多但把關鍵關系收住了值得先講透再動手。2.1 四張核心表與它們的外鍵關系資源里的核心表大致是四張訂單表orders、騎手表couriers、派單記錄表assignments、軌跡點表track_points。訂單表存收貨地址經(jīng)緯度、期望時段、當前狀態(tài)騎手表存當前位置、載單上限、在線狀態(tài)派單記錄表是訂單和騎手的多對多橋接帶派單時間和結果軌跡點表按時間戳追加騎手位置。關鍵設計點在assignments這張橋接表它沒有把courier_id直接塞進orders而是單獨記錄每一次派單嘗試包括失敗的那次。這個選擇很關鍵——同城派送里「派了但騎手拒單」是高頻事件如果只在訂單表上掛一個騎手字段拒單后歷史就丟了事后排查「這單為什么繞了三圈才派出去」沒有依據(jù)。橋接表讓每次嘗試都留痕代價是多一次 join。-- 訂單表狀態(tài)用整數(shù)枚舉避免字符串比較 CREATE TABLE orders ( id INTEGER PRIMARY KEY AUTOINCREMENT, pickup_lat REAL NOT NULL, pickup_lng REAL NOT NULL, drop_lat REAL NOT NULL, drop_lng REAL NOT NULL, status INTEGER NOT NULL DEFAULT 0, -- 0待派 1已派 2取貨中 3配送中 4已簽收 5已取消 created_at INTEGER NOT NULL, -- Unix 秒統(tǒng)一時區(qū) expect_start INTEGER, -- 期望送達時段起點 expect_end INTEGER ); -- 派單記錄每次嘗試一行失敗也留痕 CREATE TABLE assignments ( id INTEGER PRIMARY KEY AUTOINCREMENT, order_id INTEGER NOT NULL REFERENCES orders(id), courier_id INTEGER NOT NULL REFERENCES couriers(id), result INTEGER NOT NULL, -- 0待響應 1接受 2拒單 3超時 assigned_at INTEGER NOT NULL, responded_at INTEGER ); CREATE INDEX idx_assign_order ON assignments(order_id); CREATE INDEX idx_assign_courier ON assignments(courier_id, result);上面這段建表語句里有兩個參數(shù)值得說。status用整數(shù)而不是文本是因為 SQLite 沒有原生枚舉文本狀態(tài)在批量更新時容易拼錯且索引效率低整數(shù)配合應用層常量最穩(wěn)。created_at和expect_start統(tǒng)一存 Unix 秒而不是文本時間是為了后面算「距離期望時段還有多久」時能直接做減法不用在 SQL 里調日期函數(shù)——SQLite 的日期函數(shù)跨時區(qū)處理是玄學能避開就避開。兩個索引分別服務「查某訂單的派單歷史」和「查某騎手當前有效單」這兩個查詢在派單循環(huán)里會被高頻調用。2.2 狀態(tài)流轉與事務邊界狀態(tài)機是這類系統(tǒng)最容易翻車的地方。訂單狀態(tài)從 0 到 4 是一條單向鏈但取消5可以從任意前置狀態(tài)進入騎手拒單則要把訂單從 1 退回 0。這份資源把狀態(tài)變更全部收在一個函數(shù)里不允許業(yè)務代碼直接UPDATE orders SET status這個約束我認可。import sqlite3 # 合法狀態(tài)遷移當前狀態(tài) - 允許的下一狀態(tài)集合 TRANSITIONS { 0: {1, 5}, # 待派 - 已派 / 取消 1: {2, 0, 5}, # 已派 - 取貨中 / 退回待派(拒單) / 取消 2: {3, 5}, # 取貨中 - 配送中 / 取消 3: {4, 5}, # 配送中 - 已簽收 / 取消 4: set(), # 終態(tài) 5: set(), } def change_status(conn, order_id, new_status): cur conn.cursor() cur.execute(SELECT status FROM orders WHERE id?, (order_id,)) row cur.fetchone() if row is None: raise ValueError(forder {order_id} not found) old row[0] if new_status not in TRANSITIONS.get(old, set()): raise ValueError(fillegal transition {old} - {new_status}) cur.execute( UPDATE orders SET status? WHERE id? AND status?, (new_status, order_id, old), # 帶舊狀態(tài)做樂觀鎖 ) if cur.rowcount ! 1: raise RuntimeError(concurrent status change detected) conn.commit()這段代碼的邏輯說明先查當前狀態(tài)用TRANSITIONS字典校驗遷移合法性非法遷移直接拋錯而不是靜默忽略——靜默忽略是血淚經(jīng)驗線上出問題時你根本不知道哪一步被吞了。UPDATE語句里帶AND status?是樂觀鎖防止兩個并發(fā)請求同時把訂單從 1 改到 2 和 0rowcount不等于 1 就說明有人搶先改了拋錯讓上層重試。參數(shù)上conn由調用方傳入而不是函數(shù)內新建是為了讓多個狀態(tài)變更能包在同一個事務里如果你要一次改多張表把commit提到外層。提示SQLite 默認的isolation_level是隱式事務Python 的sqlite3模塊在 DML 前會自動開事務。上面代碼里顯式commit是必要的但如果你在外層已經(jīng)開了事務這里的commit會提前提交需要改成由外層統(tǒng)一控制。2.3 派單匹配的 SQL 寫法派單的核心查詢是「找一批在線、未滿載、距離取貨點最近的騎手」。這份資源沒有上空間索引用的是經(jīng)緯度平方差近似排序在同城幾公里范圍內誤差可以接受。-- 候選騎手在線、載單未滿、按到取貨點距離平方升序 SELECT c.id, (c.lat - :pickup_lat) * (c.lat - :pickup_lat) (c.lng - :pickup_lng) * (c.lng - :pickup_lng) AS dist2 FROM couriers c WHERE c.online 1 AND c.load_count c.max_load AND c.id NOT IN ( SELECT courier_id FROM assignments WHERE order_id :order_id AND result IN (1, 0) -- 已接受或待響應 ) ORDER BY dist2 ASC LIMIT 5;邏輯說明dist2是距離平方省掉開方排序結果和真實距離一致但快一點。NOT IN子查詢排除掉已經(jīng)對這筆訂單接受或待響應的騎手避免重復派給同一個人。LIMIT 5是候選池大小實際派單時按順序逐個發(fā)請求前一個拒單再試下一個。參數(shù):pickup_lat這類命名占位符比?可讀性好Python 里用字典傳參即可。注意load_count是冗余字段每次接單要同步加一這個一致性靠應用層保證SQLite 沒有觸發(fā)器約束的話容易漏——這是后面避坑章要展開的點。3. 從零跑通派單閉環(huán)建庫、灌數(shù)據(jù)、模擬一輪調度表設計講完接下來是讓它動起來。這一章按「建庫 → 灌測試數(shù)據(jù) → 跑一輪派單 → 看結果」的順序走每一步都能直接抄。我一般會先造一批假數(shù)據(jù)把閉環(huán)跑通再談優(yōu)化不然連狀態(tài)流轉對不對都驗證不了。3.1 初始化數(shù)據(jù)庫與 PRAGMA 調優(yōu)SQLite 默認配置偏保守同城派送這種寫多讀也多的小系統(tǒng)開庫時設幾個 PRAGMA 能明顯減少鎖等待。import sqlite3 def open_db(pathdispatch.db): conn sqlite3.connect(path, timeout5.0) cur conn.cursor() cur.execute(PRAGMA journal_modeWAL;) # 讀寫不互斥 cur.execute(PRAGMA synchronousNORMAL;) # 兼顧安全與速度 cur.execute(PRAGMA foreign_keysON;) # 外鍵約束默認是關的 cur.execute(PRAGMA busy_timeout5000;) # 鎖等待 5 秒 conn.commit() return conn參數(shù)說明journal_modeWAL讓讀操作不被寫操作阻塞派單循環(huán)里一邊寫派單記錄一邊查騎手列表不開 WAL 會頻繁database is locked。synchronousNORMAL在 WAL 模式下已經(jīng)足夠安全比默認FULL快不少代價是極端斷電可能丟最后一個事務——同城派送場景可以接受。foreign_keysON必須顯式開SQLite 默認不校驗外鍵不開的話assignments里能插進不存在的order_id臟數(shù)據(jù)就是這么來的。busy_timeout是等鎖的超時設 5 秒比默認立即報錯友好。3.2 灌入模擬訂單與騎手測試數(shù)據(jù)要覆蓋邊界有騎手滿載、有騎手離線、有訂單在期望時段邊緣。下面這段造 20 個騎手、50 個訂單坐標在一個虛構城區(qū)的矩形范圍內隨機。import random, time def seed(conn, n_couriers20, n_orders50): cur conn.cursor() now int(time.time()) for i in range(n_couriers): cur.execute( INSERT INTO couriers(lat,lng,online,load_count,max_load) VALUES(?,?,?,?,?), (30.0 random.random(), 120.0 random.random(), 1 if random.random() 0.2 else 0, # 兩成離線 0, random.choice([2, 3, 4])), ) for i in range(n_orders): cur.execute( INSERT INTO orders(pickup_lat,pickup_lng,drop_lat,drop_lng,status,created_at,expect_start,expect_end) VALUES(?,?,?,?,0,?,?,?), (30.0 random.random(), 120.0 random.random(), 30.0 random.random(), 120.0 random.random(), now, now 1800, now 3600), ) conn.commit()邏輯說明online用random.random() 0.2讓約兩成騎手離線是為了驗證派單查詢的WHERE online1真的生效。max_load在 2 到 4 之間隨機制造滿載邊界。訂單的expect_start設在 30 分鐘后、expect_end在 1 小時后方便后面測「臨近超時優(yōu)先派」的邏輯。坐標范圍是虛構的實際用時換成你所在城區(qū)的經(jīng)緯度矩形即可。3.3 跑一輪派單并核對結果派單主循環(huán)的邏輯是取待派訂單按期望時段緊迫度排序對每單查候選騎手逐個嘗試成功則更新訂單和騎手。def dispatch_round(conn): cur conn.cursor() cur.execute(SELECT id,pickup_lat,pickup_lng FROM orders WHERE status0 ORDER BY expect_end ASC) orders cur.fetchall() for oid, plat, plng in orders: cur.execute( SELECT c.id FROM couriers c WHERE c.online1 AND c.load_count c.max_load AND c.id NOT IN (SELECT courier_id FROM assignments WHERE order_id? AND result IN (0,1)) ORDER BY (c.lat-?)*(c.lat-?)(c.lng-?)*(c.lng-?) ASC LIMIT 5 , (oid, plat, plat, plng, plng)) candidates [r[0] for r in cur.fetchall()] for cid in candidates: try: cur.execute(BEGIN) cur.execute(INSERT INTO assignments(order_id,courier_id,result,assigned_at) VALUES(?,?,0,?), (oid, cid, int(time.time()))) cur.execute(UPDATE orders SET status1 WHERE id? AND status0, (oid,)) if cur.rowcount ! 1: raise RuntimeError(order taken) cur.execute(UPDATE couriers SET load_countload_count1 WHERE id? AND load_countmax_load, (cid,)) if cur.rowcount ! 1: raise RuntimeError(courier full) conn.commit() break except Exception: conn.rollback() continue邏輯說明外層按expect_end升序越臨近超時的訂單越先派。內層對每個候選騎手開一個事務插入派單記錄、改訂單狀態(tài)、加騎手載單數(shù)三步要么全成要么全回滾。UPDATE orders ... AND status0和UPDATE couriers ... AND load_countmax_load都帶條件rowcount校驗是并發(fā)下的后悔藥——兩個派單線程同時處理同一單或同一騎手時第二個會失敗并回滾。參數(shù)上LIMIT 5控制候選池太大浪費查詢太小可能派不出去5 是個經(jīng)驗值。跑完一輪后查SELECT status, COUNT(*) FROM orders GROUP BY status就能看到派出去多少、還剩多少待派。4. 避坑與排查SQLite 派送系統(tǒng)最容易翻車的五個點這套東西跑通不難難的是跑穩(wěn)。下面五條是我在類似項目里真實踩過或見別人踩過的按「現(xiàn)象 → 原因 → 解決」寫遇到對應癥狀直接對號入座。4.1 現(xiàn)象派單循環(huán)跑幾百單后報database is locked原因沒開 WAL或者開了 WAL 但多個連接同時寫。SQLite 同一時刻只允許一個寫者派單循環(huán)里如果每個訂單都新開連接寫鎖競爭會非常激烈。另一個常見原因是事務沒及時提交長事務占著寫鎖。解決開庫時統(tǒng)一走open_db()設journal_modeWAL和busy_timeout整個派單循環(huán)復用同一個連接不要每單新建事務粒度控制在「一單一次」別把整輪派單包成一個大事務。如果確實要多線程寫操作串行化讀可以并發(fā)。4.2 現(xiàn)象騎手load_count和實際有效單數(shù)對不上原因load_count是冗余字段接單時加一、簽收或取消時減一漏了任何一處就會漂移。最常見的是訂單取消時忘了減或者拒單回滾時減了兩次。解決不要靠人工記增減點寫一個對賬函數(shù)定期重算UPDATE couriers SET load_count(SELECT COUNT(*) FROM assignments a JOIN orders o ON a.order_ido.id WHERE a.courier_idcouriers.id AND a.result1 AND o.status IN (1,2,3))。更穩(wěn)的做法是干脆不存load_count每次派單時用子查詢實時算數(shù)據(jù)量小的時候性能完全夠省掉一致性維護。4.3 現(xiàn)象同一訂單被派給兩個騎手兩人都來取貨原因派單時先查候選再插入查和插之間沒有鎖兩個調度線程同時處理同一單都查到空候選然后各自插入。這是典型的檢查后使用競態(tài)。解決插入派單記錄前先UPDATE orders SET status1 WHERE id? AND status0用rowcount判斷是否搶到搶到再插記錄或者給assignments加唯一約束UNIQUE(order_id, courier_id, result)配合事務。核心是讓「占單」這個動作原子化別把判斷和寫入分開。4.4 現(xiàn)象按距離排序結果和地圖上看到的順序不一致原因用經(jīng)緯度平方差排序在緯度跨度大或者靠近極地時誤差明顯另外如果經(jīng)緯度存的是文本或者混了不同坐標系比如有的點存了偏移后的坐標排序直接失效。解決同城幾公里范圍內平方差夠用但前提是所有坐標同一坐標系、同一單位。入庫前統(tǒng)一轉成 WGS84 的十進制度別混?？缍瘸^十幾公里時改用 Haversine 公式或者上 R-Tree 索引SQLite 自帶rtree模塊建虛擬表按矩形范圍先篩再精排。4.5 現(xiàn)象程序重啟后部分訂單狀態(tài)卡在「已派」但騎手說沒收到原因派單記錄插了、訂單狀態(tài)改了但通知騎手的動作在事務外通知失敗后事務已提交狀態(tài)和實際不一致。或者反過來通知發(fā)了但事務回滾了。解決把「通知」做成可重試的獨立步驟訂單狀態(tài)里加一個「已派待確認」騎手確認后才轉「取貨中」。通知失敗時靠定時任務掃「已派待確認」超時的單重新派。別指望一次通知必達同城派送的常態(tài)就是重試。5. 進階用觸發(fā)器和對賬任務把一致性焊死前面幾章把閉環(huán)跑通了但冗余字段和狀態(tài)一致性還是靠應用層自覺。真要在生產(chǎn)邊緣用我一般會加兩層保險一層是 SQLite 觸發(fā)器兜底一層是定時對賬任務。觸發(fā)器負責「該減的必須減」對賬負責「已經(jīng)漂的拉回來」兩層配合基本不用再擔心數(shù)據(jù)對不上。先看觸發(fā)器。訂單狀態(tài)從「已派/取貨中/配送中」進入終態(tài)簽收或取消時對應騎手的load_count必須減一。用AFTER UPDATE觸發(fā)器實現(xiàn)CREATE TRIGGER trg_order_release_load AFTER UPDATE OF status ON orders WHEN OLD.status IN (1,2,3) AND NEW.status IN (4,5) BEGIN UPDATE couriers SET load_count MAX(load_count - 1, 0) WHERE id ( SELECT courier_id FROM assignments WHERE order_id NEW.id AND result 1 ORDER BY assigned_at DESC LIMIT 1 ); END;邏輯說明AFTER UPDATE OF status只在狀態(tài)列被改時觸發(fā)避免無關更新誤觸。WHEN子句限定「從進行中進入終態(tài)」重復觸發(fā)不會重復減。MAX(load_count - 1, 0)防止減成負數(shù)這是兜底中的兜底。子查詢取最近一次接受的派單記錄因為一單可能被派過多次只有最后接受的那個騎手該減。參數(shù)上沒有可調的但要注意觸發(fā)器在事務內執(zhí)行如果外層事務回滾觸發(fā)器效果也回滾這點和普通語句一致。再看對賬任務。觸發(fā)器能覆蓋大部分路徑但歷史臟數(shù)據(jù)、手工改庫、觸發(fā)器創(chuàng)建前的數(shù)據(jù)都管不到所以需要一個定時跑的校正腳本def reconcile(conn): cur conn.cursor() cur.execute( UPDATE couriers SET load_count ( SELECT COUNT(*) FROM assignments a JOIN orders o ON a.order_id o.id WHERE a.courier_id couriers.id AND a.result 1 AND o.status IN (1,2,3) ) ) conn.commit() return cur.rowcount這個腳本把每個騎手的load_count重算成「當前有效單數(shù)」rowcount返回被修正的行數(shù)可以打到日志里監(jiān)控漂移頻率。我一般設成每 10 分鐘跑一次或者每次派單批次結束后跑一次。如果rowcount長期大于 0說明觸發(fā)器有漏網(wǎng)路徑得回去查。驗證方法也簡單造一批訂單隨機走完簽收和取消然后跑一次reconcile看rowcount是否為 0。不為 0 就說明有路徑?jīng)]被觸發(fā)器覆蓋。我習慣在每次改狀態(tài)機代碼后都跑一遍這個驗證比肉眼看代碼靠譜。注意觸發(fā)器會增加寫操作開銷派單高峰期如果發(fā)現(xiàn)寫入變慢先查是不是觸發(fā)器里的子查詢沒走索引。assignments(order_id)和assignments(courier_id, result)這兩個索引就是為它準備的別刪。從那以后我每次給這類單文件數(shù)據(jù)庫加冗余字段都強制先寫觸發(fā)器再寫業(yè)務代碼最后補一個對賬腳本兜底——順序反了后面補一致性就是無底洞。希望幫到你。本文還有配套的精品資源點擊獲取