震驚刻诊，小白看了都知道的１破！Mysql6000w數(shù)據(jù)表的查詢優(yōu)化到0.023S
創(chuàng)譽代碼 sycoding
前言
很抱歉現(xiàn)在才把這篇文章發(fā)出來倒戏，這幾天事情比較多，周四把任務完成才得空寫一寫恐似，閑話不多說請看下↓↓↓
詳細需求
系統(tǒng)中有一個專門存車流量的庫（沒有主鍵）杜跷，其中一個歷史表數(shù)據(jù)量太大，表空間占據(jù)太大矫夷，每天有500w的數(shù)據(jù)寫入葛闷，然后老大給我安排了個任務，讓我寫個按天分表的定時任務双藕，每次把一天的數(shù)據(jù)轉移到按天生成的表中淑趾，并刪除原表中的數(shù)據(jù)，主要目的是不想再增長表空間了忧陪，保持一個平衡扣泊，因為每天刪500w也會加500w
表空間和數(shù)據(jù)量：
[圖片上傳失敗...(image-4ae8c9-1614477989258)] [圖片上傳失敗...(image-7d6617-1614477989258)]
實現(xiàn)思路
實現(xiàn)做法流程，如圖： [圖片上傳失敗...(image-3eb068-1614477989258)] 實現(xiàn)偽代碼（刪減了部分代碼）：
/**
* 轉移數(shù)據(jù) 每天凌晨3點 每次只能轉移一天的數(shù)據(jù)
*/
@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)");
// 轉移數(shù)據(jù)
int rst = dataTransfer(orderTime);
// 刪除重復數(shù)據(jù)
int delt = deleteDataTransfer(orderTime);
// 當今天數(shù)據(jù)轉移完成時,退出本次循環(huán)
if(rst<=0 && delt <=0){
break;
}
i++;
System.out.println("轉移數(shù)據(jù)表為："+tabaleName+" 轉移數(shù)據(jù)次數(shù): "+i);
}
}
System.out.println("定時器結束運行------------------------------------------");
}
復制代碼
心路歷程
方法完成之后嘶摊，上周五去服務器正式實測延蟹，實測時方法用 @PostConstruct 修飾，會在服務器加載Servlet的時候運行叶堆，并且只會被服務器執(zhí)行一次阱飘。 當時控制臺打印：
（“定時器開始運行”）卡住虱颗，去庫中看到表已成功創(chuàng)建
開始以為是某個地方異常了沥匈，后面逐一打印步驟發(fā)現(xiàn)是得到最遠時間段是卡住了，也就是被一條sql查詢卡住了(直接用這條sql去庫里查詢300s+也沒查詢出來)忘渔，然后維護這個庫的小伙跟我說：要不直接limit 1 吧咐熙，它的插入是根據(jù)時間順序插入的，當時也想到了會出問題辨萍，時間順序肯定不可能完全按照順序寫入，周末程序走了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從小到大的順序中取出數(shù)據(jù)時犁罩，執(zhí)行以下sql
select * from history order by id
Mysql會直接遍歷上圖id索引的葉子節(jié)點鏈表，不需要進行額外的排序操作两疚。這就是用索引掃描來排序床估。
- 使用文件排序(filesort)
但如果id字段沒有任何索引，上圖的B+Tree結構不存在诱渤，Mysql就只能先掃表篩選出符合條件的數(shù)據(jù)丐巫，再將篩選結果根據(jù)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找到數(shù)據(jù)行的方式，效率排名 NULL > system > const > eq_ref > ref > range > index > All
- range 只檢索給定范圍的行委可，使用一個索引來選擇行渊跋，一般是在where中出現(xiàn)between、<着倾、>拾酝、in等查詢，范圍掃描好于全表掃描
- index Full Index Scan卡者，Index與All區(qū)別為index類型只遍歷索引樹蒿囤。通常比All快，因為索引文件通常比數(shù)據(jù)文件小崇决。也就是說材诽，雖然all和index都是讀全表底挫，但是index是從索引中讀取的，而all是從硬盤讀取的
- ALL Full Table Scan,將遍歷全表以找到匹配的行
- rows：根據(jù)表統(tǒng)計信息及索引選用情況脸侥，大致估算出找到所需的記錄所需要讀取的行數(shù)建邓，也就是說，用的越少越好
- extra ：包含不適合在其他列中顯式但十分重要的額外信息
- Using Index:表示相應的select操作中使用了覆蓋索引（Covering Index）湿痢，避免訪問了表的數(shù)據(jù)行涝缝，效率不錯。如果同時出現(xiàn)using where譬重，表明索引被用來執(zhí)行索引鍵值的查找拒逮；如果沒有同時出現(xiàn)using where，表明索引用來讀取數(shù)據(jù)而非執(zhí)行查找動作臀规。
- Using filesort：當Query中包含 order by 操作滩援，而且無法利用索引完成的排序操作稱為“文件排序”
下面我們通過一張真實數(shù)據(jù)圖來分析
由于正式庫的未優(yōu)化之前的執(zhí)行計劃圖片忘記保存，這里用本地環(huán)境代替一下塔嬉，內(nèi)容相同 執(zhí)行SQL： EXPLAIN SELECT updateTime FROM historydata ORDER BY updateTime LIMIT 1
[圖片上傳失敗...(image-e4df8e-1614477989258)]
通過我們那種查詢是沒有辦法命中索引的玩徊，遵循最左原則，為updateTime新建一個普通索引(index)NORMAL [圖片上傳失敗...(image-e425c9-1614477989258)]
添加索引的過程中再提一嘴谨究，因為這個歷史庫時時刻刻都有數(shù)據(jù)寫入恩袱，所以我當時建索引時擔心鎖表，后面查詢相關資料就知道了胶哲，Mysql5.6之后的版本不影響讀寫畔塔，不會鎖表，前提存儲引擎為InnoDB鸯屿，MyISAM加索引鎖表澈吨，讀寫會全部堵塞。
如果表數(shù)據(jù)量過多寄摆，可能建立索引的時間會過長谅辣，以我舉例6000w差不多建了4h，下面為索引效果圖： [圖片上傳失敗...(image-a3735b-1614477989258)] [圖片上傳失敗...(image-5fcda2-1614477989258)] [圖片上傳失敗...(image-39b0b3-1614477989258)]
結合執(zhí)行計劃分析該數(shù)據(jù)婶恼，優(yōu)化就到這了桑阶，優(yōu)化過后這幾天定時程序異常的穩(wěn)，每天定時500w數(shù)據(jù)的轉移和刪除勾邦，也算是解決了联逻。
結尾
其實本文就是一些很基礎得東西，歡迎指出問題检痰，可能大家都知道，但是沒有機會去實際接觸這么多數(shù)據(jù)锨推，實際去優(yōu)化這樣的東西铅歼，我也是第一次接觸這些東西公壤，寫本文單純就是想分享下，順便加深下自己的印象椎椰，寫的不好厦幅，請見諒！慨飘！
[圖片上傳失敗...(image-87cd71-1614477989258)]
