在某個專案中，訂單表每天會產生5-10萬條記錄。
先前對巨量資料查詢沒多少處理經驗，碰到難以查動的統計資料通常是建一張表，定時統計一次。或者寫儲存過程，優化查詢過程。
按照以往一貫思路，建表，加索引。
然而這次在表裡只有十多萬資料時，每日統計查詢竟然查不動了，查一次要四五秒。
如果是百萬級資料，我還可以甩鍋為伺服器效能問題，十萬資料查不動，要是彙報讓領導升級伺服器，估計領導直接讓我去跟人事結算工資了。
我是這麼設想的：
我：「查不動是伺服器效能問題。」領導：「現在伺服器一百一個月，十萬一百，一百萬一千。這樣吧，你跟人事結下工資，我用你的工資買個高配伺服器。」
於是我開始了百度尋找資料優化的方法。
表結構如下(欄位有刪減，欄位個數，資料量對查詢速度有影響)：
CREATE TABLE `myorder` (
`ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '資料id',
`CREATE_TIME` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
`UPDATE_TIME` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '修改時間',
`sn` varchar(50) NOT NULL COMMENT '訂單編號',
`client_id` int(11) NOT NULL COMMENT '供應商id',
`money` decimal(19,2) DEFAULT NULL COMMENT '訂單金額',
PRIMARY KEY (`ID`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
ALTER TABLE myorder ADD INDEX index_mo_sn ( `sn` ) ;
ALTER TABLE myorder ADD INDEX index_mo_client_id ( `client_id` ) ;SQL如下：
select client_id,sum(money)
from myorder
where CREATE_TIME>=CURRENT_DATE
group by client_id去掉分組條件，查詢很快。
經過嘗試，第一次優化的結果便是給 時間 欄位加上索引。
原以為問題解決了。某天晚上，一個電話把我嚇得睡不著。領導發現每次統計查詢要半分鐘。
我納悶了，不可能，昨天都沒問題，怎麼今天就萎了，四十萬和五十萬資料有很大區別？
於是我親自去檢閱了那個功能，確實查不動。然後我告訴領導：「伺服器網路波動，我修復一下。」
經過測試，發現這天的資料量有點大，按時間查詢都要十來秒，加上分組，時間更長。
過了十多分鐘，領導問我：「怎麼樣了？」我：「服務商說他們網路被攻擊了，正在修復。」我登入伺服器，防火牆把80埠關了。
檢視執行計劃，查詢沒有走 時間 索引。最後不斷寫SQL測試發現，用時間做查詢條件是行不通的。
靜音了手機後，我細細思考，一度萌生了建個統計表定時統計的想法。但是一想這統計每次要十多秒，萬一鎖了表就很尷尬了。
經過一番思考，我想到了用ID查詢，用前一天最後一條資料的ID做統計條件
SQL如下：
select client_id,sum(money)
from myorder
where id>(select max(id) from myorder where CREATE_TIME<CURRENT_DATE)
group by client_id經測試，百萬資料查詢時間控制在1秒內。
隨之還發現了一個小問題。因為分頁查詢分兩步，第一步查詢總條數，第二步查分頁資料。在查總條數時，不加任何條件，查詢速度有點慢，資料量越大速度越慢。
SQL如下：
select count(1)
from pay_order100w資料查了2秒
檢視執行計劃可以發現，查詢沒走索引，讓他走索引即可。
SQL如下：
select count(1)
from pay_order
where id is not null加上後只用了0.2秒
重新部署專案，給領導回撥了個電話。領導：「為什麼不接我電話？」我：「剛臨時有事出門，沒帶手機。」領導：「服務商告訴我他們網路沒被攻擊。」我詭辯道：「不可能，我剛什麼都沒做，要是沒被攻擊，它怎麼自己好了。」領導語塞。
有一天，領導突然叫我去聊天。領導：「我發現現在查詢很慢，特別是點最後一頁。」我解釋道：「資料量大了，查的就慢了。」領導：「我朋友公司那個開發月薪4K，百萬級資料查詢速度和你的差不多，你要對得起你8K的工資，快一倍要求不高吧？」我愕然，沒想到還能這樣比：「好像有那麼點道理……我覺得可以升級一下伺服器。」領導一臉不快：「要我給你配個天河一號？」我：「……」
首先看下最後一頁查詢速度慢的問題
按照百度到的方法，改寫查詢條件：
原SQL：
select *
from myorder
limit 1000000,100改寫後：
select *
from myorder
where id >= (SELECT id FROM myorder LIMIT 1000000, 1)
limit 100看著好像是沒什麼問題，遂部署上線。過了兩天，領導突然找我。領導：「為什麼倒序排序的資料不管哪一頁都是一樣的？」我一看，還真是，靈機一動：「服務商的資料庫預設設定有問題，我處理一下。」
在實際中，我們的where後面會帶很多查詢條件，還會有多個欄位組合升序降序的排序。有個好辦法，禁用排序（儘可能的減少order by），禁用搜尋。
經過多次測試發現，如果結果欄位只有id這一列，查詢速度會非常快。檢視執行計劃，結果列多加一個欄位，就不走索引了。where和order by用到的欄位都要測一遍，檢視執行計劃。
既然如此，那就把上面的SQL拆成兩個查詢
SELECT id FROM myorder where ... order by ... LIMIT 1000000, 100select * from myorder where id in (1,2,3,4...)先查出id，將id拼成字串，再用in查詢，查詢速度沒什麼影響，資料準確
某一天，領導：「伺服器怎麼存取不了了？」我檢視紀錄檔，發現有人試圖匯出幾十萬資料，記憶體溢位，應用崩了：「伺服器網路波動。」沒多久，領導：「服務商說網路沒問題，還給我提了個建議。」我很好奇：「什麼建議？」領導：「建議我開除你。」