【MySQL】最容易忽略的常識

起因
  開發反饋一個表的資料大小已經130G，對物理儲存空間有影響，且不容易做資料庫ddl變更。諮詢了開發相關業務邏輯，在電商業務系統中,每筆訂單成交之後會有一條對應的訂單物流資訊,因此需要設計一個物流相關的表用來儲存該訂單的物流節點資訊,該表使用text欄位儲存物流資訊。

大致的表結構：
CREATE TABLE `goods_order_express` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `express_id` int(10) unsigned NOT NULL,
  `message` varchar(200) NOT NULL,
  `status` varchar(20) NOT NULL,
  `state` tinyint(3) unsigned NOT NULL,
  `data` text NOT NULL,
  `created_time` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_expid` (`express_id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4;

業務分析
當快遞每到達一箇中轉站或者發生攬件，接收等事件，快遞公司的api都會生成如下格式的資訊(去掉業務相關敏感資料) 
[{"time":"2016-03-16 11:16:20","ftime":"2016-03-16 11:16:20","context":"四川省成都市TD客戶一公司 已發出,下一站成都轉運中心","areaCode":"","areaName":"","status":"在途"},{"time":"2016-03-16 11:11:03","ftime":"2016-03-16 11:11:03","context":"四川省成都市TD客戶一公司 已打包","areaCode":"","areaName":"","status":"在途"},{"time":"2016-03-16 11:08:09","ftime":"2016-03-16 11:08:09","context":"四川省成都市TD客戶一公司 已攬收","areaCode":"","areaName":"","status":"收件"}]
該json 串 411個字元，開發業務程式去定期輪訓呼叫相關api資訊，並把上面的json串資料 insert 或者update 到goods_order_express的data欄位。而且該表從開始到現在從未刪除，積累了初始到現在的所有資料。隨著公司業務爆發式增長，該表未來會更大，而且增長速度會更快。資料庫伺服器的磁碟空間面臨不足，表結構變更難以操作。
如何優化？
1 能否減小資料量寫入？
   和業務分析，我們不能丟棄新增的資料。但是每一筆物流資訊實際上是有生命週期的，從發貨到收件完成即可完成其生命週期，也就是該資料可以不再展示了，我們基本不會檢視一個已經收到貨的物流資訊。因此可以針對歷史資料進行歸檔，比如將90天之前的資料備份到hbase中並且從MySQL 資料庫中刪除，從而維持該表的大小在一個合理的範圍。
2 減少data 欄位資料大小
a 縮小json串資料，保留有效資料
time 和ftime 是一樣的，和開發確認ftime無功能使用，在我們的物流展示系統中 areaCode areaName也沒有邏輯意義。
故對json資料做如下精簡 
[{"time":"2016-03-16 11:16:20","context":"四川省成都市TD客戶一公司 已發出,下一站 成都轉運中心","status":"在途"},{"time":"2016-03-16 11:11:03","context":"四川省成都市TD客戶一公司 已打包","status":"在途"},{"time":"2016-03-16 11:08:09","context":"四川省成都市TD客戶一公司 已攬收","status":"收件"}]
精簡之後佔用的字元數由411個減小為237個，減少47%的資料。
b 評估物流節點數
相信大家都有網購的經驗 ，一般情況下快遞大約含有15-20個節點資訊
{"time":"2016-03-16 11:16:20","context":"四川省成都市TD客戶一公司 已發出,下一站 成都轉運中心","status":"在途"} 佔用85個，我們按照100個字元來評估，物流資訊最大20*100=2000個字元，使用varchar(2048) 應該可以滿足正常需求。
c 可能有人會說凡事總有例外，那我們從這個例外分析一下 如果一個物流有30或者40個節點資訊 怎麼辦？
從深圳到黑龍江漠河 或者新疆烏魯木齊到杭州，上海的節點資訊估計會比較多。對於20個以上 的節點資訊 我們不會去關注其中第10個 11個 14個 15個節點的資訊。大家對快遞的關注點是什麼？ 商家是否發貨？快遞公司是否攬件? 快遞是否到達目的地的最後1公里。分析到這裡，我們可以針對超過25個/30個以上的節點進行收縮處理，去掉中間非核心節點資訊，在不影響使用者體驗的情況下，滿足我們的varchar(2048)的設計。
3 分庫分表
  這點是迫不得已而為之的方案。現在雖然各種中介軟體都比較成熟，cobar，oneproxy ，mycat等靠譜的軟體，但是對於一個創業公司目前我們還缺少相對應的分散式資料庫的管理工具，1024個表如何做變更？這個其實也是一個相對比較困難的問題。

小結 
   經過一系列的分析和優化,我們最終將text欄位轉化為varchar(2048)，釋出到線上目前執行良好。回顧上面的優化過程是建立在對業務邏輯和物流相關知識有深入理解，對使用者行為多加分析的基礎之上的，該過程不需要高深的資料庫知識。但是實際上開發往往簡單粗暴的接受pd的功能設計理念，而不顧對底層基礎架構的影響。其實只需要向前多走一步，我們可以做的更好，只不過這一步，可能是 優秀的程式設計師的一小步，是某些人的一大步。
留給大家一個問題:如何看待和解決 開發快速迭代帶來的技術債？

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22664653/viewspace-2079576/，如需轉載，請註明出處，否則將追究法律責任。