震驚,小白看了都知道的!!Mysql6000w資料表的查詢優化到0.023S

語言: CN / TW / HK

前言

很抱歉現在才把這篇文章發出來,這幾天事情比較多,週四把任務完成才得空寫一寫,閒話不多說請看下↓↓↓

詳細需求

需求:

系統中有一個專門存車流量的庫(沒有主鍵),其中一個歷史表資料量太大,表空間佔據太大,每天有500w的資料寫入,然後老大給我安排了個任務,讓我寫個按天分表的定時任務,每次把一天的資料轉移到按天生成的表中,並刪除原表中的資料,主要目的是不想再增長表空間了,保持一個平衡,因為每天刪500w也會加500w

表空間和資料量:
在這裡插入圖片描述在這裡插入圖片描述

實現思路

我本人實現的做法流程,如圖
在這裡插入圖片描述
實現虛擬碼(刪減了部分程式碼):

     /**
     * 轉移資料 每天凌晨3點 每次只能轉移一天的資料
     */
    @Scheduled(cron = "0 0 3 * * ?")
    public void dataTransfer()throws Exception{
   
   
        System.out.println("定時器開始執行------------------------------------------");
        String tabaleName = "XXX";
        String isTable = getTableName(tabaleName);
         // 當返回為空時,代表該表不存在,則建立
         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("進入迴圈");
                // 轉移資料
                int rst =  dataTransfer(orderTime);
                // 刪除重複資料
                int delt = deleteDataTransfer(orderTime);
                // 當今天資料轉移完成時,退出本次迴圈
                if(rst<=0 && delt <=0){
   
   
                    break;
                }
                i++;
                System.out.println("轉移資料表為:"+tabaleName+" 轉移資料次數: "+i);
            }
        }
         System.out.println("定時器結束執行------------------------------------------");
    }

心路歷程

方法完成之後,上週五去伺服器正式實測,實測時方法用 @PostConstruct 修飾,會在伺服器載入Servlet的時候執行,並且只會被伺服器執行一次。
當時控制檯列印:

(“定時器開始執行”)卡住,去庫中看到表已成功建立

開始以為是某個地方異常了,後面逐一列印步驟發現是得到最遠時間段是卡住了,也就是被一條sql查詢卡住了(直接用這條sql去庫裡查詢300s+也沒查詢出來),然後維護這個庫的小夥跟我說:要不直接limit 1 吧,它的插入是根據時間順序插入的,當時也想到了會出問題,時間順序肯定不可能完全按照順序寫入,週末程式走了2天果然有問題,如圖:
在這裡插入圖片描述

在這裡插入圖片描述
limit 1是行不通了,那就只能來查詢優化了,講查詢優化之前,先說說為什麼我們使用order by為什麼會這麼慢?

深入分析

MySql有兩種方式可以實現 ORDER BY 這裡只做簡單介紹:

  1. 通過索引掃描生成有序的結果
    舉個例子,假設history表有id欄位上有主鍵索引,且id目前的範圍在1001-1006之間,則id的索引B+Tree如下:
    在這裡插入圖片描述
    現在當我們想按照id從小到大的順序中取出資料時,執行以下sql


select * from history order by id

Mysql會直接遍歷上圖id索引的葉子節點連結串列,不需要進行額外的排序操作。這就是用索引掃描來排序。
2. 使用檔案排序(filesort)
但如果id欄位沒有任何索引,上圖的B+Tree結構不存在,Mysql就只能先掃表篩選出符合條件的資料,再將篩選結果根據id排序。這個排序過程就是filesort
我們要讓ORDER BY字句使用索引來避免filesort(用“避免”可能有些欠妥,某些場景下全表掃描、filesort未必比走索引慢),以提高查詢效率。


-------------------------------------------------------------------------------------

進行優化之前我們還需要學會看sql的執行計劃(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
      1.range 只檢索給定範圍的行,使用一個索引來選擇行,一般是在where中出現between、<、>、in等查詢,範圍掃描好於全表掃描
      2.index Full Index Scan,Index與All區別為index型別只遍歷索引樹。通常比All快,因為索引檔案通常比資料檔案小。也就是說,雖然all和index都是讀全表,但是index是從索引中讀取的,而all是從硬碟讀取的
      3.ALL Full Table Scan,將遍歷全表以找到匹配的行


  • rows:根據表統計資訊及索引選用情況,大致估算出找到所需的記錄所需要讀取的行數,也就是說,用的越少越好

  • extra :包含不適合在其他列中顯式但十分重要的額外資訊
    1.Using Index:表示相應的select操作中使用了覆蓋索引(Covering Index),避免訪問了表的資料行,效率不錯。如果同時出現using where,表明索引被用來執行索引鍵值的查詢;如果沒有同時出現using where,表明索引用來讀取資料而非執行查詢動作。
    2.Using filesort:當Query中包含 order by 操作,而且無法利用索引完成的排序操作稱為“檔案排序”

下面我們通過一張真實資料圖來分析

由於正式庫的未優化之前的執行計劃圖片忘記儲存,這裡用本地環境代替一下,內容相同
執行SQL:
EXPLAIN SELECT updateTime FROM historydata ORDER BY updateTime LIMIT 1

在這裡插入圖片描述
可以看到type:ALL,則代表為全表掃描,rows為掃描行數,Extra的描述資訊為 using filesort,所以造成了查詢的緩慢,既然知道問題的源頭,下面我們就來解決問題!!

解決

我們線上的歷史表是沒有主鍵的,設計初有一個唯一索引,如圖:
在這裡插入圖片描述
通過我們那種查詢是沒有辦法命中索引的,遵循最左原則,為updateTime新建一個普通索引(index)NORMAL
在這裡插入圖片描述
新增索引的過程中再提一嘴,因為這個歷史庫時時刻刻都有資料寫入,所以我當時建索引時擔心鎖表,後面查詢相關資料就知道了,Mysql5.6之後的版本不影響讀寫,不會鎖表,前提儲存引擎為InnoDB,MyISAM加索引鎖表,讀寫會全部堵塞。
如果表資料量過多,可能建立索引的時間會過長,以我舉例6000w差不多建了4h,下面為索引效果圖:
在這裡插入圖片描述
在這裡插入圖片描述
在這裡插入圖片描述







結合執行計劃分析該資料,優化就到這了,優化過後這幾天定時程式異常的穩,每天定時500w資料的轉移和刪除,也算是解決了。

結尾

其實本文就是一些很基礎得東西,歡迎指出問題,可能大家都知道,但是沒有機會去實際接觸這麼多資料,實際去優化這樣的東西,我也是第一次接觸這些東西,寫本文單純就是想分享下,順便加深下自己的印象,寫的不好,請見諒!!
sql索引詳解(mysql)
Mysql - ORDER BY詳解

分享到: