【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的功能設計理念，而不顧對底層基礎架構的影響。其實只需要向前多走一步，我們可以做的更好，只不過這一步，可能是 優秀的程序員的一小步，是某些人的一大步。
留給大家一個問題:如何看待和解決 開發快速迭代帶來的技術債？
