在某個專案中，訂單表每天會產生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查詢，查詢速度沒什么影響，資料準確
某一天，領導：“服務器怎么訪問不了了？”我查看日志，發現有人試圖匯出幾十萬資料，記憶體溢位，應用崩了：“服務器網路波動，”沒多久，領導：“服務商說網路沒問題，還給我提了個建議，”我很好奇：“什么建議？”領導：“建議我開除你，”
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/153014.html
標籤：其他