首頁 > 軟體

SQL優化老出錯,那是你沒弄明白MySQL解釋計劃用法

2021-11-25 19:02:14

1、準備工作

準備三張表,一張角色表,一張裝備表,一張基礎資料表,這裡只展示一些教學中需要的欄位,在遊戲開發的過程中肯定不止這麼幾個欄位,我想大家都懂的。

角色表:

CREATE TABLE `role` (
  `n_role_id` int DEFAULT NULL,
  `s_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

裝備表:

CREATE TABLE `equip` (
  `n_equip_id` int DEFAULT NULL,
  `s_equip_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `n_config_id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

裝備設定表

CREATE TABLE `dict_equip` (
  `n_equip_id` int DEFAULT NULL,
  `s_desc` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

2、初識解釋計劃

有兩種方式可以檢視解釋計劃:

1、命令的方式:explain sql,或者 desc sql ,兩個命令都可以,我覺得記住explain比較好,單詞很直接。

2、藉助工具 Navicat(其他的不熟,估計也有),點選查詢視窗的解釋,可以不用加關鍵字explain

可以看到結果裡面包含了很多列,有的是null 有的有值,只要我們看懂了解釋計劃是不是就可以有的放矢的優化sql。

3、欄位詳解

解釋計劃的欄位還是蠻多的,Navicat顯示了12個欄位,有些欄位我們需要重點關注,有些知道怎麼回事就好了。

官方的檔案解釋:https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

1、id 執行的順序

id 是select的執行順序,id越大優先順序越高,越先被執行,id 相同時 下面的先執行.

原因是因為執行子查詢時,先查內層的,再查外層

SELECT
    de.*
FROM
    dict_equip de
WHERE
    de.n_equip_id = (
        SELECT n_equip_id FROM equip e WHERE
            e.n_role_id = (
                SELECT n_role_id FROM role r WHERE r.s_name = '香菜' )
    )

從上面的執行計劃可以看到先執行了查詢role表,後執行了equip ,最後執行了 dict_equip

2、select_type select 的型別

3、table 查詢涉及的表或衍生表

當前輸出的正在使用的表,可以有下面幾種:

<unionM,N> : 行資料是聯合之後的資料id 處於 m和 n

<derived*N*>: 衍生表

<subqueryN>: 子查詢

4、partitions 查詢涉及到的分割區

在使用分割區表的時候才能用到,暫時沒用到過這種高階功能。

5、type 查詢的型別

表示MySQL在表中找到所需行的方式,又稱「存取型別」,常見型別如下:

效能:all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const

由左至右,由最差到最好

在進行優化的時候如果查詢出的資料量大的話可以使用全表掃描,避免使用索引。

如果只是查詢很少的資料儘量使用索引。

6、possible_keys:預計可能使用的索引

在不和其他表進行關聯的時候,查詢表的是可能使用的索引

7、key:實際查詢的過程中使用的索引

顯示MySQL在查詢中實際使用的索引,若沒有使用索引,顯示為NULL

8、key_len

表示索引中使用的位元組數,可通過該列計算查詢中使用的索引的長度

9、ref 顯示該表的索引欄位關聯了哪張表的哪個欄位

注: 我在equip 和 dict_equip 兩張表都分別新增了索引,索引列是n_equip_id

通過上面的執行計劃可以看出,首先使用了索引

10、rows:根據表統計資訊及選用情況,大致估算出找到所需的記錄或所需讀取的行數,數值越小越好

比如 一個列上 雖然沒做索引,但是都是唯一的,這個時候查詢的時候如果是全表讀取,就是表裡有多少資料這個值就是多少,這個時候你需要優化的就是儘可能的讀取少的表,可以增加索引,減少讀取行數

11、filtered:返回結果的行數佔讀取行數的百分比,值越大越好

比如全表有100條資料,可能讀取了全表資料,但是隻有一條匹配上,這個時候百分比就是1,所以你需要讓這個比例越大越好,也就是讀到的資料儘量都是有用的,避免讀取不用的資料,因為IO是很費時的。

12、extra

常見的有下面幾種

use filesort:MySQL需要額外的一次傳遞,以找出如何按排序順序檢索行,如果是這個值,應該優化索引。

use temporary:為了解決查詢,MySQL需要建立一個臨時表來容納結果。典型情況如查詢包含可以按不同情況列出列的GROUP BY和ORDER BY子句時。

use index:從只使用索引樹中的資訊而不需要進一步搜尋讀取實際的行來檢索表中的列資訊。當查詢只使用作為單一索引一部分的列時,可以使用該策略

use where:where子句用於限制哪一行

總結

sql 優化的原則就是在保證正確的情況下縮短時間,目標是確定的,通過目標進行回推可以知道想要執行的快就要儘可能的少讀資料,減少讀取資料的方式大的只有兩種過濾和使用索引,在這樣的規則範圍內進行優化,但是注意索引會佔用額外的空間,要平衡好這兩者的關係。

到此這篇關於SQL優化老出錯,那是你沒弄明白MySQL解釋計劃用法的文章就介紹到這了,更多相關SQL優化 MySQL解釋計劃內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com!


IT145.com E-mail:sddin#qq.com