mysql分表策略?

01-13

非常感謝各位了到來，遇到點問題，小弟不才。
功能收集各方渠道匯總訂單
要求添加判斷渠道訂單號遊戲內部訂單號是否重複
查詢 (一) 訂單明細
時間段內(必選) 可根據渠道ID(可選) 遊戲ID(可選) 渠道支付狀態(可選) 遊戲通知返回狀態(可選) cp_serial(可選) 組合條件查詢

(二) 匯總統計
時間段內(必選) 渠道ID(可選) 遊戲ID(可選)
獲取按天統計訂單數量支付成功訂單數量(pay_stause == 1) 通知成功訂單數量(notice_status != 0 ) 支付成功總金額(pay_stause==1 count(order_money))
表結構
| 欄位 | 說明
| id | 自增ID |
| order_serial | 渠道訂單號（不確定長度類型） |
| cp_serial | 遊戲內部訂單號（不確定長度類型） |
| order_money | 訂單金額 |

| channel_id | 渠道ID |
| game_id | 遊戲ID |
| pay_stause | 渠道支付狀態 0為支付失敗 1為支付成功 |
| pay_msg | 渠道支付信息 |
| notice_status |遊戲通知返回狀態 0為未通知遊戲方 1:成功 2:金額不符 3:訂單號不存在 4:重複訂單 5:用戶不存在 6:數據驗證失敗 |
| notice_msg | 遊戲通知返回信息|
| start_date_time | 接收訂單時間 |
| day_time |當天時間時間戳 2015-2-3 00:00:00 |
查詢匯總統計碰到的問題

一開始的時候沒有day_time這個欄位，只是給start_date_time加上索引按天分組對from_unixtime(start_date_time, "%Y%m%d")分組
發現不走索引全表掃描，於是就添加day_time 欄位數據類型為date 存2015-2-3 接著發現between區間查詢了時候不走索引，於是換成存當天時間戳
一切很美好，我自己手動添加一年1000W條數據，匯總查詢了時候發現光查區間內容未分組讀取數據18W條，應用段讀數據就讀了7秒多，explain後是全走了索引
網上問了下表數據太大，讓分表，我不懂一開始我想按時間月分表唄，
問題
表數據太大，讓分表，我不懂一開始我想按時間月分表唄，可是要是跨月不是要兩隻表都查嗎，寫兩條sql 還是合併查？
插入了時候怎麼確定是否重複
讓你們做你們會怎麼做？給點建議唄

你這種情況不需要分表

分表是沒辦法的辦法，而且分表只適用隨機訪問類的信息比如用戶，博客。

訂單流水是死數據一旦創建好，訂單狀態流轉完成，像金額，渠道，日期這些屬性都是不可更改的。

你需要圍繞這個訂單流水表創建一系列統計表，查詢的時候查這些統計表，不查流水。

比如

日期渠道成功/失敗筆數/金額支付成功/失敗筆數金額

首先得明確一點，不管如何分表，都無法完全避免同時訪問多個表的情況。具體還是要根據業務需求來決定。

針對題主的問題，查詢時間過長的主要原因還是要訪問的數據量太大（ 18w ），所以降低單次訪問的數據總量才是關鍵。

試試 LIMIT, OFFSET 以後多次查詢吧。。。一次讀取 18w 數據的場景本身就很奇怪。

你是指縱向劃分還是水平劃分？

1&>縱向分表

將本來可以在同一個表的內容，人為劃分為多個表。（所謂的本來，是指按照關係型資料庫的第三範式要求，是應該在同一個表的。）

分表理由：根據數據的活躍度進行分離，（因為不同活躍的數據，處理方式是不同的）

案例：

對於一個博客系統，文章標題，作者，分類，創建時間等，是變化頻率慢，查詢次數多，而且最好有很好的實時性的數據，我們把它叫做冷數據。而博客的瀏覽量，回複數等，類似的統計信息，或者別的變化頻率比較高的數據，我們把它叫做活躍數據。所以，在進行資料庫結構設計的時候，就應該考慮分表，首先是縱向分表的處理。

這樣縱向分表後：

首先存儲引擎的使用不同，冷數據使用MyIsam 可以有更好的查詢數據。活躍數據，可以使用Innodb ,可以有更好的更新速度。

其次，對冷數據進行更多的從庫配置，因為更多的操作時查詢，這樣來加快查詢速度。對熱數據，可以相對有更多的主庫的橫向分表處理。

其實，對於一些特殊的活躍數據，也可以考慮使用memcache ,redis

之類的緩存，等累計到一定量再去更新資料庫。或者mongodb 一類的nosql 資料庫，這裡只是舉例，就先不說這個。

2&>橫向分表

字面意思，就可以看出來，是把大的表結構，橫向切割為同樣結構的不同表，如，用戶信息表，user_1,user_2 等。表結構是完全一樣，但是，根據某些特定的規則來劃分的表，如根據用戶ID來取模劃分。

分表理由：根據數據量的規模來劃分，保證單表的容量不會太大，從而來保證單表的查詢等處理能力。

案例：同上面的例子，博客系統。當博客的量達到很大時候，就應該採取橫向分割來降低每個單表的壓力，來提升性能。例如博客的冷數據表，假如分為100個表，當同時有100萬個用戶在瀏覽時，如果是單表的話，會進行100萬次請求，而現在分表後，就可能是每個表進行1萬個數據的請求（因為，不可能絕對的平均，只是假設），這樣壓力就降低了很多很多。

查詢訂單明細：
首先，按運營人員實際操作來看，明確按時間段查詢的頻率。一個月內，工作人員可能A按月查詢2次，B按星期查詢10次，C按天(包含小時)查詢200次。則可以將表拆分成10個表，今天數據存table_5，明天數據存table_6。
這樣1000w數據，每張表只有100w，AB查詢由於頻率低，可以允許時間長點，按C查詢速度會快許多，還不行就再多分點。
匯總統計：
由於獲取結果是一天的某些統計數據，可以新建訂單統計表。欄位分別是：日期（只有年月日）、訂單量、成功訂單量等等。此表數據靠腳本按時生成，一天統計一次放入此表，如每天半夜3點統計昨天訂單量等需要數據放入此表。
具體操作按篩選情況，酌情來統計。
訂單號不重複，可以再建一張訂單號表，只有渠道自增、訂單號、遊戲內部訂單號三個欄位，這樣這張表雖然長，但體積小，應該可以滿足題主要求。

總的來說，就是將大表瘦身，以滿足業務需求。

這種統計數據，你直接走隊列就行了，主表只負責邏輯處理、統計從log表中查詢

建議是做歸檔處理