阿粉昨天讀者朋友們說(shuō)了關(guān)于MySQL的索引的類型還有索引的實(shí)現(xiàn)原理，今天，阿粉就來(lái)給大家說(shuō)說(shuō)這個(gè)索引分析和索引優(yōu)化是應(yīng)該怎么做的。

查看命中索引

MySQL 里面我們都知道一個(gè)指令， EXPLAIN，這個(gè)指令我們都知道，它可以對(duì) SELECT 語(yǔ)句進(jìn)行分析，并輸出 SELECT 執(zhí)行的詳細(xì)信息，供開(kāi)發(fā)人員有針對(duì)性的優(yōu)化。

比如我們的 SQL 是這樣的：

EXPLAIN SELECT * from user WHERE id < 10;

就會(huì)吧所有的信息都給我們列出來(lái)，我們來(lái)分析一下這個(gè)每個(gè)字段內(nèi)容的含義：

第一個(gè)參數(shù)：

select_type

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

• SIMPLE ： 表示查詢語(yǔ)句不包含子查詢或union
• PRIMARY：表示此查詢是最外層的查詢
• UNION：表示此查詢是UNION的第二個(gè)或后續(xù)的查詢
• DEPENDENT UNION：UNION中的第二個(gè)或后續(xù)的查詢語(yǔ)句，使用了外面查詢結(jié)果
• UNION RESULT：UNION的結(jié)果
• SUBQUERY：SELECT子查詢語(yǔ)句
• DEPENDENT SUBQUERY：SELECT子查詢語(yǔ)句依賴外層查詢的結(jié)果。

最常見(jiàn)的查詢類型是SIMPLE，表示我們的查詢沒(méi)有子查詢也沒(méi)用到UNION查詢。

type

表示存儲(chǔ)引擎查詢數(shù)據(jù)時(shí)采用的方式。比較重要的一個(gè)屬性，通過(guò)它可以判斷出查詢是全表掃描還 是基于索引的部分掃描。常用屬性值如下，從上至下效率依次增強(qiáng)。

type 參數(shù)也有很多的值，每個(gè)值的不同代表這不同的含義；

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

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í)候，我們就得看看他是怎么出來(lái)的這個(gè)數(shù)據(jù)

key_len的計(jì)算規(guī)則如下：

• 字符串類型

字符串長(zhǎng)度跟字符集有關(guān)：latin1=1、gbk=2、utf8=3、utf8mb4=4

char(n)：n*字符集長(zhǎng)度

varchar(n)：n * 字符集長(zhǎng)度 + 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，則沒(méi)有此項(xiàng)。

Extra

Extra表示很多額外的信息，各種操作會(huì)在Extra提示相關(guān)信息

比如你會(huì)在其中看到Using where,Using index,Using filesort,Using temprorary,

Using where表示查詢需要通過(guò)索引回表查詢數(shù)據(jù)。

Using index表示查詢需要通過(guò)索引，索引就可以滿足所需數(shù)據(jù)。

Using filesort表示查詢出來(lái)的結(jié)果需要額外排序，數(shù)據(jù)量小在內(nèi)存，大的話在磁盤(pán)，因此有Using filesort 建議優(yōu)化。

Using temprorary

查詢使用到了臨時(shí)表，一般出現(xiàn)于去重、分組等操作。

既然我們都知道了這個(gè) 關(guān)鍵字，那么肯定得想辦法處理我們的 SQL 然后達(dá)到我們想要優(yōu)化的目的。

那么查詢優(yōu)化需要我們做到哪些呢？

我們可以通過(guò)查看慢查詢?nèi)罩?來(lái)定位我們的慢業(yè)務(wù)問(wèn)題。

我們應(yīng)該怎么開(kāi)啟慢查詢?nèi)罩竞驮趺慈〔榭绰樵兊娜罩灸兀堪⒎墼谶@里給大家講述一波：

開(kāi)啟慢查詢?nèi)罩荆?/p>

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執(zhí)行時(shí)間超過(guò)閥值，就屬于慢查詢 記錄到日志文件中。
• log_queries_not_using_indexes

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

查看慢查詢?nèi)罩?

使用mysqldumpslow查看:

這個(gè)可是非常經(jīng)典的一個(gè)慢查詢?nèi)罩痉治龉ぞ撸梢酝ㄟ^(guò)該工具分析慢查詢?nèi)罩緝?nèi)容。

工具其實(shí)還有很多，并不限制只有這一種，還有pt-query-digest、mysqlsla等，這些都是可以定位慢查詢?nèi)罩镜男」ぞ摺?/p>

至于如何來(lái)進(jìn)項(xiàng)慢查詢的優(yōu)化呢？阿粉講在下個(gè)文章來(lái)科普一下，畢竟今天已經(jīng)我們吧分析索引的方法講給了大家，大家也需要消化一下，不是么？