一個MySQL建表需求的討論和引導
昨天收到一個業務同學的需求郵件，一般有些復雜的需求業務同學會發郵件告知我們，需要我們評估之后再做交付，我看了郵件之后，發現這個需求好像有點別扭，大體的意思是在中間件的環境中創建一張表，表結構如下：
- CREATE TABLE `app_loading_info` (
- `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
- `pid` bigint(20) NOT NULL DEFAULT '0' COMMENT ,
- `appid` int(11) NOT NULL DEFAULT '0' COMMENT 'APPID',
- `username` varchar(64) NOT NULL DEFAULT '' COMMENT '姓名',
- `card` varchar(20) NOT NULL DEFAULT '' ,
- `ai` varchar(40) NOT NULL DEFAULT '' ,
- `state` int(11) NOT NULL DEFAULT '0' ,
- `ctime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間',
- `mtime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
- PRIMARY KEY (`id`),
- KEY `idx_pid` (`pid`),
- KEY `idx_state` (`state`)
- ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
按照ID分片，基本邏輯如下：
每天會去篩選為完成處理的用戶數據，重新處理，處理完成后會去修改用戶的一個標志位，主要有幾個步驟：
1)根據state狀態提取state=0的數據(未完成處理數據)
2)程序中按照id為區間分批提取
3)提取完成后修改state為state=1,根據pid,state組合
看了這個初步的設計之后，我總是感覺哪里不對，于是找業務同學面對面溝通。
首先對于這個表的定義上，業務同學說是歸屬于狀態表，也就意味著表中的每一個用戶都有唯一的狀態值對應，這個表中存儲的數據量會越來越大。
其次，按照state狀態字段去提取未完成處理的數據，這個目標環境是一套集群環境，集群中是按照id進行分片，但是查詢條件按照state是有潛在問題的。
比如業務層對于自增id的使用，在分片環境中可能是不唯一的，如上圖所示，可能id=1最多會存在N條同樣的數據(N為分片數)，所以從業務需求上是不太能滿足的。
另外根據state=0去查詢數據，這個查詢的復雜度較高，也就意味著state=0需要遍歷所有的分片，每個分片中會通過state=0的索引條件過濾數據最后匯總起來，從使用上來說，這也是分庫分表的一個潛在影響，不是很建議這種使用方式。
還有字段id的設計，按照狀態表的使用方式，也是不合理的，在一些特殊的場景中我們會采用id+其他業務屬性字段組合主鍵， 在這里這種場景顯然不是。
如果去掉id字段采用主鍵的模式，好像就違背了業務初衷根據id進行區間提取的方式，細細品來這個需求是矛盾的。
如果按照最勉強的方式，建議是指定時間范圍內處理，比如8點到9點之間處理，這個之外的時間范圍就不要做類似心跳或者服務檢測的處理了，對于業務側來說，還是能夠基本接受的，但是無論如何這不是一種最優解，而且對于索引的使用實在有悖于中間件服務使用的初衷。
經過進一步的溝通，我們再次挖掘需求，對于里面的表數據是如何處理的，業務同學說其實表中的數據如果時間長了之后是需要考慮數據清理的，所以按照這種模式，這個需求的就基本清晰了，和初始需求有比較大的差異。
到了這里需求的方向其實就有了大的轉折，這個表按照目前的需求其實使用日志表的模式要更好一些，比如表中的數據是按照如下的列表情況存儲，以日期表為維度進行存儲。
如果需要按照T+1的模式去處理未完成的數據，整個復雜度只針對某一天的表執行索引掃描，不會對其他的表產生關聯影響，而如果按照日期為單表存儲，整個事情的自由度就更大了，按照state或者是pid的維度進行查詢，效果都是可以接受的。
所以最后經過討論和評估，其實沒有必要在中間件環境中進行該類業務的處理，相比而言，性價比也不高。而基于中間件的服務承接的是偏核心的業務，對于性能和負載的影響較為敏感，如果稀里糊涂就執行了，其實后面會帶來一些其他的隱患。
通過這樣一個看起來簡單的需求的溝通和挖掘，最后產生了不同的解決方案，對于業務側來說還是比較滿意的，至少能夠超出他們的基本需求期望實現，而且很多細節的工作也不需要更多的人工參與和后期討論，大大減少了溝通的邊際成本。
以上僅是一個需求的討論過程，不代表方案是最優的，僅供參考。
本文轉載自微信公眾號「楊建榮的學習筆記」，可以通過以下二維碼關注。轉載本文請聯系楊建榮的學習筆記公眾號。
