Explain進(jìn)行索引分析和優(yōu)化
阿粉昨天讀者朋友們說了關(guān)于MySQL的索引的類型還有索引的實(shí)現(xiàn)原理,今天,阿粉就來給大家說說這個(gè)索引分析和索引優(yōu)化是應(yīng)該怎么做的。
查看命中索引
MySQL 里面我們都知道一個(gè)指令, EXPLAIN,這個(gè)指令我們都知道,它可以對(duì) SELECT 語句進(jìn)行分析,并輸出 SELECT 執(zhí)行的詳細(xì)信息,供開發(fā)人員有針對(duì)性的優(yōu)化。
比如我們的 SQL 是這樣的:
EXPLAIN SELECT * from user WHERE id < 10;
就會(huì)吧所有的信息都給我們列出來,我們來分析一下這個(gè)每個(gè)字段內(nèi)容的含義:
第一個(gè)參數(shù):
select_type
表示查詢的類型。常用的值如下:
- SIMPLE : 表示查詢語句不包含子查詢或union
- PRIMARY:表示此查詢是最外層的查詢
- UNION:表示此查詢是UNION的第二個(gè)或后續(xù)的查詢
- DEPENDENT UNION:UNION中的第二個(gè)或后續(xù)的查詢語句,使用了外面查詢結(jié)果
- UNION RESULT:UNION的結(jié)果
- SUBQUERY:SELECT子查詢語句
- DEPENDENT SUBQUERY:SELECT子查詢語句依賴外層查詢的結(jié)果。
最常見的查詢類型是SIMPLE,表示我們的查詢沒有子查詢也沒用到UNION查詢。
type
表示存儲(chǔ)引擎查詢數(shù)據(jù)時(shí)采用的方式。比較重要的一個(gè)屬性,通過它可以判斷出查詢是全表掃描還 是基于索引的部分掃描。常用屬性值如下,從上至下效率依次增強(qiáng)。
type 參數(shù)也有很多的值,每個(gè)值的不同代表這不同的含義;
- ALL:表示全表掃描,性能最差。
- index:表示基于索引的全表掃描,先掃描索引再掃描全表數(shù)據(jù)。
- range:表示使用索引范圍查詢。使用>、>=、<、<=、in等等。
- ref:表示使用非唯一索引進(jìn)行單值查詢。
- eq_ref:一般情況下出現(xiàn)在多表join查詢,表示前面表的每一個(gè)記錄,都只能匹配后面表的一 行結(jié)果。
- const:表示使用主鍵或唯一索引做等值查詢,常量查詢。
- NULL:表示不用訪問表,速度最快。
possible_keys
表示查詢時(shí)能夠使用到的索引。注意并不一定會(huì)真正使用,顯示的是索引名稱。
注意這個(gè)只是可能用到的索引,而不是實(shí)際上用到的索引。
key
表示查詢時(shí)真正使用到的索引,顯示的是索引名稱。
rows
MySQL查詢優(yōu)化器會(huì)根據(jù)統(tǒng)計(jì)信息,估算SQL要查詢到結(jié)果需要掃描多少行記錄。原則上rows是 越少效率越高,可以直觀的了解到SQL效率高低。
key_len
表示查詢使用了索引的字節(jié)數(shù)量。可以判斷是否全部使用了組合索引。
但是這個(gè)時(shí)候,我們知道了索引使用的字節(jié)數(shù)量的時(shí)候,我們就得看看他是怎么出來的這個(gè)數(shù)據(jù)
key_len的計(jì)算規(guī)則如下:
- 字符串類型
字符串長度跟字符集有關(guān):latin1=1、gbk=2、utf8=3、utf8mb4=4
char(n):n*字符集長度
varchar(n):n * 字符集長度 + 2字節(jié)
- 數(shù)值類型
TINYINT:1個(gè)字節(jié)
SMALLINT:2個(gè)字節(jié)
MEDIUMINT:3個(gè)字節(jié)
INT、FLOAT:4個(gè)字節(jié)
BIGINT、DOUBLE:8個(gè)字節(jié)
- 時(shí)間類型
DATE:3個(gè)字節(jié)
TIMESTAMP:4個(gè)字節(jié)
DATETIME:8個(gè)字節(jié)
- 字段屬性
NULL屬性占用1個(gè)字節(jié),如果一個(gè)字段設(shè)置了NOT NULL,則沒有此項(xiàng)。
Extra
Extra表示很多額外的信息,各種操作會(huì)在Extra提示相關(guān)信息
比如你會(huì)在其中看到Using where,Using index,Using filesort,Using temprorary,
Using where表示查詢需要通過索引回表查詢數(shù)據(jù)。
Using index表示查詢需要通過索引,索引就可以滿足所需數(shù)據(jù)。
Using filesort表示查詢出來的結(jié)果需要額外排序,數(shù)據(jù)量小在內(nèi)存,大的話在磁盤,因此有Using filesort 建議優(yōu)化。
Using temprorary
查詢使用到了臨時(shí)表,一般出現(xiàn)于去重、分組等操作。
既然我們都知道了這個(gè) 關(guān)鍵字,那么肯定得想辦法處理我們的 SQL 然后達(dá)到我們想要優(yōu)化的目的。
那么查詢優(yōu)化需要我們做到哪些呢?
我們可以通過查看慢查詢?nèi)罩?來定位我們的慢業(yè)務(wù)問題。
我們應(yīng)該怎么開啟慢查詢?nèi)罩竞驮趺慈〔榭绰樵兊娜罩灸??阿粉在這里給大家講述一波:
開啟慢查詢?nèi)罩荆?/p>
- long_query_time 指定慢查詢的閥值,單位秒。如果SQL執(zhí)行時(shí)間超過閥值,就屬于慢查詢 記錄到日志文件中。
- log_queries_not_using_indexes
表示會(huì)記錄沒有使用索引的查詢SQL。前提是slow_query_log的值為ON,否則不會(huì)奏效。
查看慢查詢?nèi)罩?
使用mysqldumpslow查看:
這個(gè)可是非常經(jīng)典的一個(gè)慢查詢?nèi)罩痉治龉ぞ?,可以通過該工具分析慢查詢?nèi)罩緝?nèi)容。
工具其實(shí)還有很多,并不限制只有這一種,還有pt-query-digest、mysqlsla等,這些都是可以定位慢查詢?nèi)罩镜男」ぞ摺?/p>
至于如何來進(jìn)項(xiàng)慢查詢的優(yōu)化呢?阿粉講在下個(gè)文章來科普一下,畢竟今天已經(jīng)我們吧分析索引的方法講給了大家,大家也需要消化一下,不是么?