1、準備工作

準備三張表，一張角色表，一張裝備表，一張基礎(chǔ)數(shù)據(jù)表，這里只展示一些教程中需要的字段，在游戲開發(fā)的過程中肯定不止這么幾個字段，我想大家都懂的。

角色表：

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（其他的不熟，估計也有），點擊查詢窗口的解釋，可以不用加關(guān)鍵字explain

可以看到結(jié)果里面包含了很多列，有的是null 有的有值，只要我們看懂了解釋計劃是不是就可以有的放矢的優(yōu)化sql。

3、字段詳解

解釋計劃的字段還是蠻多的，Navicat顯示了12個字段，有些字段我們需要重點關(guān)注，有些知道怎么回事就好了。

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

1、id 執(zhí)行的順序

id 是select的執(zhí)行順序，id越大優(yōu)先級越高，越先被執(zhí)行，id 相同時 下面的先執(zhí)行.

原因是因為執(zhí)行子查詢時，先查內(nèi)層的，再查外層

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 = '香菜' )
    )

從上面的執(zhí)行計劃可以看到先執(zhí)行了查詢role表，后執(zhí)行了equip ，最后執(zhí)行了 dict_equip

2、select_type select 的類型

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

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

<unionM,N> ： 行數(shù)據(jù)是聯(lián)合之后的數(shù)據(jù)id 處于 m和 n

<derived*N*>: 衍生表

<subqueryN>： 子查詢

4、partitions 查詢涉及到的分區(qū)

在使用分區(qū)表的時候才能用到，暫時沒用到過這種高級功能。

5、type 查詢的類型

表示MySQL在表中找到所需行的方式，又稱“訪問類型”，常見類型如下：

性能：all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const

由左至右，由最差到最好

在進行優(yōu)化的時候如果查詢出的數(shù)據(jù)量大的話可以使用全表掃描，避免使用索引。

如果只是查詢很少的數(shù)據(jù)盡量使用索引。

6、possible_keys：預(yù)計可能使用的索引

在不和其他表進行關(guān)聯(lián)的時候，查詢表的是可能使用的索引

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

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

8、key_len

表示索引中使用的字節(jié)數(shù)，可通過該列計算查詢中使用的索引的長度

9、ref 顯示該表的索引字段關(guān)聯(lián)了哪張表的哪個字段

注： 我在equip 和 dict_equip 兩張表都分別添加了索引，索引列是n_equip_id

通過上面的執(zhí)行計劃可以看出，首先使用了索引

10、rows：根據(jù)表統(tǒng)計信息及選用情況，大致估算出找到所需的記錄或所需讀取的行數(shù)，數(shù)值越小越好

比如 一個列上 雖然沒做索引，但是都是唯一的，這個時候查找的時候如果是全表讀取，就是表里有多少數(shù)據(jù)這個值就是多少，這個時候你需要優(yōu)化的就是盡可能的讀取少的表，可以增加索引，減少讀取行數(shù)

11、filtered：返回結(jié)果的行數(shù)占讀取行數(shù)的百分比，值越大越好

比如全表有100條數(shù)據(jù)，可能讀取了全表數(shù)據(jù)，但是只有一條匹配上，這個時候百分比就是1，所以你需要讓這個比例越大越好，也就是讀到的數(shù)據(jù)盡量都是有用的，避免讀取不用的數(shù)據(jù)，因為IO是很費時的。

12、extra

常見的有下面幾種

use filesort：MySQL需要額外的一次傳遞，以找出如何按排序順序檢索行，如果是這個值，應(yīng)該優(yōu)化索引。

use temporary：為了解決查詢，MySQL需要創(chuàng)建一個臨時表來容納結(jié)果。典型情況如查詢包含可以按不同情況列出列的GROUP BY和ORDER BY子句時。

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

use where：where子句用于限制哪一行

總結(jié)

sql 優(yōu)化的原則就是在保證正確的情況下縮短時間，目標是確定的，通過目標進行回推可以知道想要執(zhí)行的快就要盡可能的少讀數(shù)據(jù)，減少讀取數(shù)據(jù)的方式大的只有兩種過濾和使用索引，在這樣的規(guī)則范圍內(nèi)進行優(yōu)化，但是注意索引會占用額外的空間，要平衡好這兩者的關(guān)系。

到此這篇關(guān)于SQL優(yōu)化老出錯,那是你沒弄明白MySQL解釋計劃用法的文章就介紹到這了,更多相關(guān)SQL優(yōu)化 MySQL解釋計劃內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論