Explain進行索引分析和優化

作者：鴨血粉絲Tang 2023-02-26 01:00:12

數據庫其他數據庫

至于如何來進項慢查詢的優化呢？阿粉講在下個文章來科普一下，畢竟今天已經我們吧分析索引的方法講給了大家，大家也需要消化一下，不是么？

阿粉昨天讀者朋友們說了關于MySQL的索引的類型還有索引的實現原理，今天，阿粉就來給大家說說這個索引分析和索引優化是應該怎么做的。

查看命中索引

MySQL 里面我們都知道一個指令， EXPLAIN，這個指令我們都知道，它可以對 SELECT 語句進行分析，并輸出 SELECT 執行的詳細信息，供開發人員有針對性的優化。

比如我們的 SQL 是這樣的：

EXPLAIN SELECT * from user WHERE id < 10;

就會吧所有的信息都給我們列出來，我們來分析一下這個每個字段內容的含義：

第一個參數：

select_type

表示查詢的類型。常用的值如下：

SIMPLE ：表示查詢語句不包含子查詢或union
PRIMARY：表示此查詢是最外層的查詢
UNION：表示此查詢是UNION的第二個或后續的查詢
DEPENDENT UNION：UNION中的第二個或后續的查詢語句，使用了外面查詢結果
UNION RESULT：UNION的結果
SUBQUERY：SELECT子查詢語句
DEPENDENT SUBQUERY：SELECT子查詢語句依賴外層查詢的結果。

最常見的查詢類型是SIMPLE，表示我們的查詢沒有子查詢也沒用到UNION查詢。

type

表示存儲引擎查詢數據時采用的方式。比較重要的一個屬性，通過它可以判斷出查詢是全表掃描還是基于索引的部分掃描。常用屬性值如下，從上至下效率依次增強。

type 參數也有很多的值，每個值的不同代表這不同的含義；

ALL：表示全表掃描，性能最差。
index：表示基于索引的全表掃描，先掃描索引再掃描全表數據。
range：表示使用索引范圍查詢。使用>、>=、<、<=、in等等。
ref：表示使用非唯一索引進行單值查詢。
eq_ref：一般情況下出現在多表join查詢，表示前面表的每一個記錄，都只能匹配后面表的一行結果。
const：表示使用主鍵或唯一索引做等值查詢，常量查詢。
NULL：表示不用訪問表，速度最快。

possible_keys

表示查詢時能夠使用到的索引。注意并不一定會真正使用，顯示的是索引名稱。

注意這個只是可能用到的索引，而不是實際上用到的索引。

key

表示查詢時真正使用到的索引，顯示的是索引名稱。

rows

MySQL查詢優化器會根據統計信息，估算SQL要查詢到結果需要掃描多少行記錄。原則上rows是越少效率越高，可以直觀的了解到SQL效率高低。

key_len

表示查詢使用了索引的字節數量。可以判斷是否全部使用了組合索引。

但是這個時候，我們知道了索引使用的字節數量的時候，我們就得看看他是怎么出來的這個數據

key_len的計算規則如下：

字符串類型

字符串長度跟字符集有關：latin1=1、gbk=2、utf8=3、utf8mb4=4

char(n)：n*字符集長度

varchar(n)：n * 字符集長度 + 2字節

數值類型

TINYINT：1個字節

SMALLINT：2個字節

MEDIUMINT：3個字節

INT、FLOAT：4個字節

BIGINT、DOUBLE：8個字節

時間類型

DATE：3個字節

TIMESTAMP：4個字節

DATETIME：8個字節

字段屬性

NULL屬性占用1個字節，如果一個字段設置了NOT NULL，則沒有此項。

Extra

Extra表示很多額外的信息，各種操作會在Extra提示相關信息

比如你會在其中看到Using where,Using index,Using filesort,Using temprorary,

Using where表示查詢需要通過索引回表查詢數據。

Using index表示查詢需要通過索引，索引就可以滿足所需數據。

Using filesort表示查詢出來的結果需要額外排序，數據量小在內存，大的話在磁盤，因此有Using filesort 建議優化。

Using temprorary

查詢使用到了臨時表，一般出現于去重、分組等操作。

既然我們都知道了這個關鍵字，那么肯定得想辦法處理我們的 SQL 然后達到我們想要優化的目的。

那么查詢優化需要我們做到哪些呢？

我們可以通過查看慢查詢日志來定位我們的慢業務問題。

我們應該怎么開啟慢查詢日志和怎么取查看慢查詢的日志呢？阿粉在這里給大家講述一波：

開啟慢查詢日志：

SET global slow_query_log = ON;
SET global slow_query_log_file = 'OAK-slow.log';
SET global log_queries_not_using_indexes = ON;
SET long_query_time = 10;

long_query_time 指定慢查詢的閥值，單位秒。如果SQL執行時間超過閥值，就屬于慢查詢記錄到日志文件中。
log_queries_not_using_indexes

表示會記錄沒有使用索引的查詢SQL。前提是slow_query_log的值為ON，否則不會奏效。

查看慢查詢日志:

使用mysqldumpslow查看:

這個可是非常經典的一個慢查詢日志分析工具，可以通過該工具分析慢查詢日志內容。

工具其實還有很多，并不限制只有這一種，還有pt-query-digest、mysqlsla等，這些都是可以定位慢查詢日志的小工具。

至于如何來進項慢查詢的優化呢？阿粉講在下個文章來科普一下，畢竟今天已經我們吧分析索引的方法講給了大家，大家也需要消化一下，不是么？

責任編輯：武曉燕來源： Java極客技術

索引優化慢查詢