天天看點
傳回

性能優化｜索引優化最佳實戰(二)

表初始化sql見 性能優化｜索引優化最佳實(一)

https://my-wechat.mdnice.com/fullstack-1.png); margin-bottom: -22px;">深入索引優化

https://my-wechat.mdnice.com/fullstack-2.png); background-position: center; background-size: 30px; margin: auto; opacity: 1; background-repeat: no-repeat; margin-bottom: -8px;">我們很好奇，mysql是如何選擇合适的索引的？

其實在mysql底層，使用了一個

trace

的工具，幫助你分析每條SQL語句，它會按照索引去查詢語句，預估消耗的時間，然後再按照全表掃描，預估消耗的時間，最後比較兩者消耗時間，選擇是否走索引還是全表掃描,執行如下sql,檢視結果2，分析trace字段的結果：

https://files.mdnice.com/point.png); height: 30px; width: 100%; background-size: 40px; background-repeat: no-repeat; background-color: #282c34; margin-bottom: -7px; border-radius: 5px; background-position: 10px 10px;">         SET SESSION optimizer_trace = "enabled=on",
end_markers_in_json = ON;

SELECT
* 
FROM
 student 
WHERE
 stu_age > 10 
ORDER BY
 stu_age;
SELECT
* 
FROM
 information_schema.OPTIMIZER_TRACE;

預估表的通路成本

性能優化｜索引優化最佳實戰(二)

使用索引預估的通路成本

性能優化｜索引優化最佳實戰(二)

https://img.alicdn.com/tfs/TB1Yycwyrj1gK0jSZFuXXcrHpXa-32-32.png); display: inline-block; width: 18px; height: 18px; background-size: 18px; background-repeat: no-repeat; background-position: center; margin-right: 5px; margin-bottom: -5px;">在這裡插入圖檔描述

比較成本大小，則決定使用索引查詢。

https://my-wechat.mdnice.com/fullstack-2.png); background-position: center; background-size: 30px; margin: auto; opacity: 1; background-repeat: no-repeat; margin-bottom: -8px;">order by優化

  • SQL1 
https://files.mdnice.com/point.png); height: 30px; width: 100%; background-size: 40px; background-repeat: no-repeat; background-color: #282c34; margin-bottom: -7px; border-radius: 5px; background-position: 10px 10px;">         explain 
SELECT
*
FROM
 student 
WHERE
 stu_name = '劉備' 
ORDER BY
 stu_addr;
性能優化｜索引優化最佳實戰(二)

執行計劃發現使用了檔案排序 using filesort，原因是由于where 後面的語句過濾字段和排序字段中間跳過了age字段，索引排序是在檔案中排序。

  • SQL2 
https://files.mdnice.com/point.png); height: 30px; width: 100%; background-size: 40px; background-repeat: no-repeat; background-color: #282c34; margin-bottom: -7px; border-radius: 5px; background-position: 10px 10px;">         explain 
SELECT
*
FROM
 student 
WHERE
 stu_name = '劉備' and stu_age =  16
ORDER BY
 stu_addr;
性能優化｜索引優化最佳實戰(二)

分析執行計劃，發現using filesort沒有了，因為滿足了最左字首原則了。加上了stu_age字段，直接可以在索引中完成排序。

  • SQL3 
https://files.mdnice.com/point.png); height: 30px; width: 100%; background-size: 40px; background-repeat: no-repeat; background-color: #282c34; margin-bottom: -7px; border-radius: 5px; background-position: 10px 10px;">         explain 
SELECT
*
FROM
 student 
WHERE
 stu_name = '劉備'
ORDER BY
 stu_addr,stu_age
性能優化｜索引優化最佳實戰(二)

執行計劃中出現了filesort,原因是排序字段與索引建立的順序不一緻，不滿足最左字首法則，mysql沒有使用索引進行排序。

  • SQL4 
https://files.mdnice.com/point.png); height: 30px; width: 100%; background-size: 40px; background-repeat: no-repeat; background-color: #282c34; margin-bottom: -7px; border-radius: 5px; background-position: 10px 10px;">         explain 
SELECT
*
FROM
 student 
WHERE
 stu_name = '劉備'
ORDER BY
 stu_age, stu_addr desc

分析執行計劃，發現又出現了using filesort，使用了檔案排序，按照排序确實按照最左字首了啊，不應該啊。仔細觀察發現。兩個字段的排序方向不一緻，這會導緻索引無法進行排序，在索引樹中：隻能使用排序方向一緻的索引，否則就需要使用檔案排序了。

  • SQL5 
https://files.mdnice.com/point.png); height: 30px; width: 100%; background-size: 40px; background-repeat: no-repeat; background-color: #282c34; margin-bottom: -7px; border-radius: 5px; background-position: 10px 10px;">         explain 
SELECT
*
FROM
 student 
WHERE
 stu_name in('劉備','張飛')
ORDER BY
 stu_age

[外鍊圖檔轉存失敗,源站可能有防盜鍊機制,建議将圖檔儲存下來直接上傳(img-P8CAbS5j-1603062000913)(media/16030236994724/16030326775297.jpg)]

執行計劃發現明明按照最左字首的順序查詢和排序的，為什麼又出現了檔案排序呢？

因為in查詢屬于範圍查詢，會導緻右側所有索引失效。

  • order by 總結：
    • 排序字段盡量是索引字段
    • 盡量使用索引覆寫
    • where字段和排序字段遵循最左字首
    • 出現filesort ，盡量優化成 using index，在索引中排序肯定比使用檔案排序要快得多。
    • group by 和order by 優化點是一樣的，預設會按照字段排序的，如果不需要排序 則可以在group by 之後加上order by null，省去排序的消耗。

https://my-wechat.mdnice.com/fullstack-2.png); background-position: center; background-size: 30px; margin: auto; opacity: 1; background-repeat: no-repeat; margin-bottom: -8px;">檔案排序 using filesort

在上面優化中，發現在mysql中排序分為檔案排序和索引排序，在無法使用索引排序的情況下，我們就得考慮如何優化檔案排序了。

檔案排序分為兩種：

  • 單路排序

    單路排序是一次性取出所有滿足條件的資料的所有字段，儲存在sort buffer中，然後在sort buffer排序完成之後，直接傳回結果。

  • 雙路排序

    雙路排序是取出排序的字段和能夠定位資料的行辨別，儲存在sort_buffer中，完成排序後，再按照行辨別按順序從檔案中取出需要查詢的字段，這個比單路排序多了一步操作。

https://my-wechat.mdnice.com/fullstack-3.png); display: inline-block; width: 16px; height: 16px; background-size: 100%; background-position: left bottom; background-repeat: no-repeat; width: 16px; height: 15px; line-height: 15px; margin-right: 6px; margin-bottom: -2px;">單路排序和雙路排序分别在什麼情況下使用呢？

  • 如果查詢的字段比sort buffer 空間要大(大小由系統變量max_length_for_sort_data來決定的，預設為1024位元組)，則使用雙路排序，因為一次性無法從檔案中取出所有字段放在sort buffer中。
  • 相反，如果查詢的字段總長度比sort buffer 空間小，則使用單路排序。

https://my-wechat.mdnice.com/fullstack-3.png); display: inline-block; width: 16px; height: 16px; background-size: 100%; background-position: left bottom; background-repeat: no-repeat; width: 16px; height: 15px; line-height: 15px; margin-right: 6px; margin-bottom: -2px;">如何區分執行器使用的是單路排序還是雙路排序呢？

我們可以使用上面提到的trace工具來分析：

https://files.mdnice.com/point.png); height: 30px; width: 100%; background-size: 40px; background-repeat: no-repeat; background-color: #282c34; margin-bottom: -7px; border-radius: 5px; background-position: 10px 10px;">         SET SESSION optimizer_trace = "enabled=on",
end_markers_in_json = ON;

SELECT
*
FROM
 student 
WHERE
 stu_name ='a'
ORDER BY
 stu_addr,stu_age;
SELECT
* 
FROM
 information_schema.OPTIMIZER_TRACE;
           
https://files.mdnice.com/point.png); height: 30px; width: 100%; background-size: 40px; background-repeat: no-repeat; background-color: #282c34; margin-bottom: -7px; border-radius: 5px; background-position: 10px 10px;">         {
      "join_execution": {
        "select#": 1,
        "steps": [
          {
            "filesort_information": [
              {
                "direction": "asc",
                "table": "         student                ",
                "field": "stu_addr"
              },
              {
                "direction": "asc",
                "table": "         student                ",
                "field": "stu_age"
              }
            ] / filesort_information /,
            "filesort_priority_queue_optimization": {
              "usable": false,
              "cause": "not applicable (no LIMIT)"
            } / filesort_priority_queue_optimization /,
            "filesort_execution": [
            ] / filesort_execution /,
            "filesort_summary": {
              "rows": 0,
              "examined_rows": 0,
              "number_of_tmp_files": 0,
              "sort_buffer_size": 204800 // sort buffer大小
              "sort_mode": "<sort_key, packed_additional_fields>" // 單路排序
            } / filesort_summary /
          }
        ] / steps /
      } / join_execution /
    }

分析trace結果中主要的字段：

sort_buffer_size：204800 mysql設定的預設值為1m

"sort_mode": "<sort_key, packed_additional_fields>" 單路排序

因為我們查詢的字段長度肯定是小于1m的，我們現在設定下sort_buffer_size大小，然後再trace下結果：

https://files.mdnice.com/point.png); height: 30px; width: 100%; background-size: 40px; background-repeat: no-repeat; background-color: #282c34; margin-bottom: -7px; border-radius: 5px; background-position: 10px 10px;">         // 修改預設值，記得測試完之後修改回預設值（1m）
 set max_length_for_sort_data = 10;

再執行上面的語句，檢視結果為：

https://files.mdnice.com/point.png); height: 30px; width: 100%; background-size: 40px; background-repeat: no-repeat; background-color: #282c34; margin-bottom: -7px; border-radius: 5px; background-position: 10px 10px;">         "filesort_summary": {
              "rows": 0,
              "examined_rows": 0,
              "number_of_tmp_files": 0,
              "sort_buffer_size": 57344,
              "sort_mode": "<sort_key, rowid>" // 雙路排序 因為包含了rowid,需要根據rowid再去提取查詢的字段。
            } / filesort_summary /

sort_mode 字段中包含了rowid，是以排序模式已經更改為雙路排序了。

// 設定回原來的值

set max_length_for_sort_data = 1024;

❝

微信搜一搜【樂哉開講】關注帥氣的我，回複【幹貨領取】，将會有大量面試資料和架構師必看書籍等你挑選，包括java基礎、java并發、微服務、中間件等更多資料等你來取哦。

❞

本文使用

mdnice

排版

sql 架構師 關系型資料庫 mysql java 中間件 索引 微服務 索引優化實戰 性能優化索引 資料結構計算java 資料結構java代碼 資料結構計算java代碼
上一篇: 心選建站---根據企業和個人阿裡雲建站的優點與缺點
下一篇: 直播帶貨系統源碼，根據日期計算年齡

繼續閱讀