震驚，小白看了都知道的！！Mysql6000w數據表的查詢優(yōu)化到0.023S
創(chuàng)譽代碼 sycoding
前言
很抱歉現(xiàn)在才把這篇文章發(fā)出來，這幾天事情比較多，周四把任務完成才得空寫一寫，閑話不多說請看下↓↓↓
詳細需求
系統(tǒng)中有一個專門存車流量的庫（沒有主鍵），其中一個歷史表數據量太大，表空間占據太大，每天有500w的數據寫入，然后老大給我安排了個任務，讓我寫個按天分表的定時任務，每次把一天的數據轉移到按天生成的表中，并刪除原表中的數據，主要目的是不想再增長表空間了，保持一個平衡，因為每天刪500w也會加500w
表空間和數據量：
[圖片上傳失敗...(image-4ae8c9-1614477989258)] [圖片上傳失敗...(image-7d6617-1614477989258)]
實現(xiàn)思路
實現(xiàn)做法流程，如圖： [圖片上傳失敗...(image-3eb068-1614477989258)] 實現(xiàn)偽代碼（刪減了部分代碼）：
/**
* 轉移數據 每天凌晨3點 每次只能轉移一天的數據
*/
@Scheduled(cron = "0 0 3 * * ?")
public void dataTransfer()throws Exception{
System.out.println("定時器開始運行------------------------------------------");
String tabaleName = "XXX";
String isTable = getTableName(tabaleName);
// 當返回為空時,代表該表不存在,則創(chuàng)建
if(ObjectUtils.isNull(isTable)){
createHistoryDate(names);
}
// 得到最遠的時間段
Map<String, Object> orderTime = orderByTime();
// 得到開始和結束時間
if(SysFun.isNotEmpty(orderTime) && orderTime.size() > 0){
orderTime.put("startTime",startTime);
orderTime.put("endTime",endTime);
orderTime.put("tableName",tabaleName);
int i=0;
for (;;) {
System.out.println("進入循環(huán)");
// 轉移數據
int rst = dataTransfer(orderTime);
// 刪除重復數據
int delt = deleteDataTransfer(orderTime);
// 當今天數據轉移完成時,退出本次循環(huán)
if(rst<=0 && delt <=0){
break;
}
i++;
System.out.println("轉移數據表為："+tabaleName+" 轉移數據次數: "+i);
}
}
System.out.println("定時器結束運行------------------------------------------");
}
復制代碼
心路歷程
方法完成之后，上周五去服務器正式實測，實測時方法用 @PostConstruct 修飾，會在服務器加載Servlet的時候運行，并且只會被服務器執(zhí)行一次。 當時控制臺打?。?/p>
（“定時器開始運行”）卡住，去庫中看到表已成功創(chuàng)建
開始以為是某個地方異常了，后面逐一打印步驟發(fā)現(xiàn)是得到最遠時間段是卡住了，也就是被一條sql查詢卡住了(直接用這條sql去庫里查詢300s+也沒查詢出來)，然后維護這個庫的小伙跟我說：要不直接limit 1 吧，它的插入是根據時間順序插入的，當時也想到了會出問題，時間順序肯定不可能完全按照順序寫入，周末程序走了2天果然有問題，如圖：
[圖片上傳失敗...(image-bacec3-1614477989258)] [圖片上傳失敗...(image-72297d-1614477989258)] limit 1是行不通了，那就只能來查詢優(yōu)化了，講查詢優(yōu)化之前，先說說為什么我們使用order by為什么會這么慢？
深入分析
MySql有兩種方式可以實現(xiàn) ORDER BY 這里只做簡單介紹：
- 通過索引掃描生成有序的結果
舉個例子，假設history表有id字段上有主鍵索引，且id目前的范圍在1001-1006之間，則id的索引B+Tree如下： [圖片上傳失敗...(image-c670e0-1614477989258)]
現(xiàn)在當我們想按照id從小到大的順序中取出數據時，執(zhí)行以下sql
select * from history order by id
Mysql會直接遍歷上圖id索引的葉子節(jié)點鏈表，不需要進行額外的排序操作。這就是用索引掃描來排序。
- 使用文件排序(filesort)
但如果id字段沒有任何索引，上圖的B+Tree結構不存在，Mysql就只能先掃表篩選出符合條件的數據，再將篩選結果根據id排序。這個排序過程就是filesort。 我們要讓ORDER BY字句使用索引來避免filesort(用“避免”可能有些欠妥，某些場景下全表掃描、filesort未必比走索引慢),以提高查詢效率。
進行優(yōu)化之前我們還需要學會看sql的執(zhí)行計劃(EXPLAIN)分別為（這里著重講解type、rows、Extra，其它的這里不做講解，可自己私下進行了解）：
id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra
- type:對表訪問方式，表示MySQL在表中找到所需行的方式，又稱“訪問類型”。
- Mysql找到數據行的方式，效率排名 NULL > system > const > eq_ref > ref > range > index > All
- range 只檢索給定范圍的行，使用一個索引來選擇行，一般是在where中出現(xiàn)between、<、>、in等查詢，范圍掃描好于全表掃描
- index Full Index Scan，Index與All區(qū)別為index類型只遍歷索引樹。通常比All快，因為索引文件通常比數據文件小。也就是說，雖然all和index都是讀全表，但是index是從索引中讀取的，而all是從硬盤讀取的
- ALL Full Table Scan,將遍歷全表以找到匹配的行
- rows：根據表統(tǒng)計信息及索引選用情況，大致估算出找到所需的記錄所需要讀取的行數，也就是說，用的越少越好
- extra ：包含不適合在其他列中顯式但十分重要的額外信息
- Using Index:表示相應的select操作中使用了覆蓋索引（Covering Index），避免訪問了表的數據行，效率不錯。如果同時出現(xiàn)using where，表明索引被用來執(zhí)行索引鍵值的查找；如果沒有同時出現(xiàn)using where，表明索引用來讀取數據而非執(zhí)行查找動作。
- Using filesort：當Query中包含 order by 操作，而且無法利用索引完成的排序操作稱為“文件排序”
下面我們通過一張真實數據圖來分析
由于正式庫的未優(yōu)化之前的執(zhí)行計劃圖片忘記保存，這里用本地環(huán)境代替一下，內容相同 執(zhí)行SQL： EXPLAIN SELECT updateTime FROM historydata ORDER BY updateTime LIMIT 1
[圖片上傳失敗...(image-e4df8e-1614477989258)]
通過我們那種查詢是沒有辦法命中索引的，遵循最左原則，為updateTime新建一個普通索引(index)NORMAL [圖片上傳失敗...(image-e425c9-1614477989258)]
添加索引的過程中再提一嘴，因為這個歷史庫時時刻刻都有數據寫入，所以我當時建索引時擔心鎖表，后面查詢相關資料就知道了，Mysql5.6之后的版本不影響讀寫，不會鎖表，前提存儲引擎為InnoDB，MyISAM加索引鎖表，讀寫會全部堵塞。
如果表數據量過多，可能建立索引的時間會過長，以我舉例6000w差不多建了4h，下面為索引效果圖： [圖片上傳失敗...(image-a3735b-1614477989258)] [圖片上傳失敗...(image-5fcda2-1614477989258)] [圖片上傳失敗...(image-39b0b3-1614477989258)]
結合執(zhí)行計劃分析該數據，優(yōu)化就到這了，優(yōu)化過后這幾天定時程序異常的穩(wěn)，每天定時500w數據的轉移和刪除，也算是解決了。
結尾
其實本文就是一些很基礎得東西，歡迎指出問題，可能大家都知道，但是沒有機會去實際接觸這么多數據，實際去優(yōu)化這樣的東西，我也是第一次接觸這些東西，寫本文單純就是想分享下，順便加深下自己的印象，寫的不好，請見諒！！
[圖片上傳失敗...(image-87cd71-1614477989258)]
