MySQL執(zhí)行計(jì)劃

我們知道，當(dāng)一條sql查詢語(yǔ)句執(zhí)行時(shí)，會(huì)通過(guò)服務(wù)層中的優(yōu)化器生成“查詢執(zhí)行計(jì)劃”。而使用explain關(guān)鍵字可以查詢到執(zhí)行的SQL查詢語(yǔ)句，從而知道MySQL是如何處理SQL的，即SQL的執(zhí)行計(jì)劃。因此根據(jù)執(zhí)行計(jì)劃我們可以選擇更好的索引和寫出更優(yōu)化的查詢語(yǔ)句，分析我們的查詢語(yǔ)句或是表結(jié)構(gòu)的性能瓶頸。

公司主營(yíng)業(yè)務(wù)：網(wǎng)站建設(shè)、成都做網(wǎng)站、移動(dòng)網(wǎng)站開(kāi)發(fā)等業(yè)務(wù)。幫助企業(yè)客戶真正實(shí)現(xiàn)互聯(lián)網(wǎng)宣傳，提高企業(yè)的競(jìng)爭(zhēng)能力。創(chuàng)新互聯(lián)是一支青春激揚(yáng)、勤奮敬業(yè)、活力青春激揚(yáng)、勤奮敬業(yè)、活力澎湃、和諧高效的團(tuán)隊(duì)。公司秉承以“開(kāi)放、自由、嚴(yán)謹(jǐn)、自律”為核心的企業(yè)文化，感謝他們對(duì)我們的高要求，感謝他們從不同領(lǐng)域給我們帶來(lái)的挑戰(zhàn)，讓我們激情的團(tuán)隊(duì)有機(jī)會(huì)用頭腦與智慧不斷的給客戶帶來(lái)驚喜。創(chuàng)新互聯(lián)推出西鄉(xiāng)塘免費(fèi)做網(wǎng)站回饋大家。

首先先解釋一下以上執(zhí)行計(jì)劃中各列的含義：

2. PRIMARY： 如果查詢語(yǔ)句中包含子查詢或者UNION操作，指最外層的SELECT；

3. UNION： UNION中的第二個(gè)或后面的SELECT語(yǔ)句；

4. UNION RESULT： UNION 的結(jié)果；

5. SUBQUERY： 子查詢中的第一個(gè)SELECT；

6. DERIVED： 導(dǎo)出表的SELECT(FROM子句的子查詢)。

下面介紹在實(shí)際開(kāi)發(fā)過(guò)程中，常見(jiàn)的幾種類型：

1. const： 表示通過(guò)索引一次就找到數(shù)據(jù)，用于比較primary key或者unique索引，很快就能找到對(duì)應(yīng)的數(shù)據(jù)；

2. eq_ref： 唯一性索引掃描，對(duì)于每個(gè)索引鍵，表中只有一條記錄與之匹配，常用于主鍵或唯一索引掃描；

3. ref： 非唯一索引掃描，返回匹配的所有行；

4. index_merge： 經(jīng)常出現(xiàn)在使用一張表中的多個(gè)索引時(shí)，mysql會(huì)將多個(gè)索引合并在一起；

5. range： 使用一個(gè)索引檢索指定范圍的行，一般在where語(yǔ)句中會(huì)出現(xiàn)between、、、in等范圍查詢；

6. index： index連接類型與ALL相同，只是遍歷索引樹(shù)；

7. ALL： 全表掃描，找到匹配行。與index比較，ALL需要掃描磁盤數(shù)據(jù)，index值需要遍歷索引樹(shù)。

誤區(qū)：

上述圖片可以看到，key_len的值為9（即hotelID（4）+dateTime（5）），沒(méi)有使用到全部聯(lián)合索引，以下是改良后的sql語(yǔ)句：

此時(shí)key_len的值為14（即hotelID（4）+dateTime（5）+dateTime（5）），使用到了key中所有索引。

優(yōu)化前：

很顯然，從explain執(zhí)行計(jì)劃中可以看到，該sql語(yǔ)句使用了兩個(gè)索引，但是從我們自己的優(yōu)化目標(biāo)中，只需要使用IDX_DataChange_CreateTime這一個(gè)索引就夠了，以下是我們通過(guò)一些小手段影響優(yōu)化器得到的優(yōu)化方案：

Day42 Mysql 執(zhí)行計(jì)劃

官方文檔 對(duì)于5.7 版本 explain的描述

const ref range index all

const: 主鍵索引

ref: 二級(jí)索引

range： 索引的范圍掃描

index: 比如用到了聯(lián)合索引 比如 a,b,c，走的是聯(lián)合索引

all: 全表掃描

rows: 需要逐行去掃描的記錄數(shù)

filted: 起作用的比例

!!!上線前sql一定要進(jìn)行預(yù)執(zhí)行

explain變種

const: 表中只有一條元素匹配時(shí)為system.

最好達(dá)到range級(jí)別，最差要到ref級(jí)別。

utf-8: 數(shù)字或者字母= 1字節(jié) ，漢子占三個(gè)字節(jié)。

type 表示 關(guān)聯(lián)類型

ref 代表查找值所 用到的列或者常量 ，常見(jiàn)的有const,字段名

Mysql學(xué)會(huì)查看sql的執(zhí)行計(jì)劃

首先在Mysql的服務(wù)中有 連接器、查詢緩存(Mysql8 已經(jīng)刪除)、分析器、優(yōu)化器、執(zhí)行器等，所有跨存儲(chǔ)引擎的功能都在這一層實(shí)現(xiàn)

而一條sql怎么執(zhí)行是由優(yōu)化器決定的,?優(yōu)化器是在表里面有多個(gè)索引的時(shí)候，決定使用哪個(gè)索引；或者在一個(gè)語(yǔ)句有多表關(guān)聯(lián)（join）的時(shí)候，決定各個(gè)表的連接順序。

而執(zhí)行計(jì)劃就是優(yōu)化器優(yōu)化后的sql的執(zhí)行的詳細(xì)方案

Mysql中查看執(zhí)行計(jì)劃的方式有兩種 : 1. 使用desc? ? 2.使用 explain? 使用它倆的效果是一樣的

接下來(lái)要通過(guò)執(zhí)行計(jì)劃知道sql是怎么執(zhí)行的

執(zhí)行計(jì)劃中有幾個(gè)重要的字段, 分別是?

id,? table,? type,? possible_keys,? key,? key_len, Extra

id :? 可以通過(guò)ID來(lái)查看在多表聯(lián)查中sql是先查詢哪張表的 id相同的從上往下依次執(zhí)行,id不同的id大的先執(zhí)行

table :? ?table當(dāng)然就是查詢的表名

type :? 查詢的類型? ?查詢類型分為??ALL,??index,??range,??ref ,?eq_ref,?const(system),??null

? ? ALL: 指的全盤掃描,沒(méi)有走任何索引? ?查詢結(jié)果集大于25% 優(yōu)化器可能會(huì)走全盤掃描? ?字符串查詢的時(shí)候一定要加"" 不然可能會(huì)全索引掃描(隱式轉(zhuǎn)換)? ?統(tǒng)計(jì)信息 失效 或者 過(guò)舊 也可能走全盤掃描? 因?yàn)閮?yōu)化器會(huì)參考統(tǒng)計(jì)信息來(lái)制定執(zhí)行計(jì)劃

???index:?全索引掃描? 就是掃描整顆索引樹(shù)

? ? ? ?range: 索引范圍? 查詢索引樹(shù)的一部分范圍? ?范圍索引中 ? ? =? =? like? 的效率會(huì)比? or? ?in? 的效率高, 使用like %再前面的不走索引

ref:? ?輔助索引的等值查詢? ??????????

? ? ? ? ? ? ? ? 當(dāng)查詢的數(shù)據(jù)量小,優(yōu)化器也有可能會(huì)走索引的全盤掃描? 這里我就不貼圖了;

???eq_ref : 多表連接查詢中,被連接的表的連接條件列是主鍵或者唯一鍵

???const(system): 主鍵 或者 唯一鍵 的等值查詢

? ? ? ? ? ?null: 沒(méi)有數(shù)據(jù)

他們的性能是依次遞增的 全盤掃描性能最差,? const性能最高

possible_keys:? 查詢過(guò)程中可能用到的索引

key: 真正使用到的索引

key_len:? 走索引的長(zhǎng)度

? ? 這個(gè)是怎么計(jì)算的呢???

???key_len 的計(jì)算方法 :

int 類型最長(zhǎng)存儲(chǔ)4個(gè)字節(jié)長(zhǎng)度的數(shù)字? 有not null? 是4字節(jié)? 沒(méi)有的話會(huì)花1字節(jié)存儲(chǔ)是不是null

tinyint 最大存儲(chǔ)一個(gè)字節(jié)? ? 也會(huì)花1字節(jié)來(lái)判斷是不是null

字符串類型 : 字符集 utf8mb4? 1-4字節(jié)

varchar超過(guò)255會(huì)預(yù)留2個(gè)字節(jié)存儲(chǔ)長(zhǎng)度 沒(méi)超預(yù)留1個(gè)字節(jié)

key_len 永遠(yuǎn)是你設(shè)置的長(zhǎng)度的最大的??

聯(lián)合索引可以通過(guò)key_len 來(lái)判斷走了幾個(gè)索引

? ? 使用desc format=json select * from table 可以查看詳細(xì)情況

filtered:? 索引掃描過(guò)濾掉數(shù)據(jù)的占比

Extra: 額外的信息?

??? Using filesort ：MySQL?對(duì)數(shù)據(jù)在sql層進(jìn)行了排序，而不是按照表內(nèi)的索引進(jìn)行排序讀 取。 效率比較低

??? Using temporary ：使用臨時(shí)表保存中間結(jié)果，也就是說(shuō) MySQL 在對(duì)查詢結(jié)果排序時(shí)使用了臨時(shí)表，常見(jiàn)于order by 或 group by。

??? Using index ：表示 SQL 操作中使用了覆蓋索引（Covering Index），避免了訪問(wèn)表的數(shù)據(jù)行，效率高。

??? Using index condition ：表示 SQL 操作命中了索引，但不是所有的列數(shù)據(jù)都在索引樹(shù)上，還需要訪問(wèn)實(shí)際的行記錄。

??? Using where ：表示 SQL 操作使用了 where 過(guò)濾條件。

??? Select tables optimized away ：基于索引優(yōu)化 MIN/MAX 操作或者 MyISAM 存儲(chǔ)引擎優(yōu)化 COUNT(*) 操作，不必等到執(zhí)行階段再進(jìn)行計(jì)算，查詢執(zhí)行計(jì)劃生成的階段即可完成優(yōu)化。

Using join buffer (Block Nested Loop) ：表示 SQL 操作使用了關(guān)聯(lián)查詢或者子查詢，且需要進(jìn)行嵌套循環(huán)計(jì)算

Mysql執(zhí)行計(jì)劃功能詳解

在Mysql調(diào)優(yōu)過(guò)程中其中最關(guān)鍵的一點(diǎn)，就是正確使用執(zhí)行計(jì)劃，從而查看SQL語(yǔ)句的具體執(zhí)行過(guò)程和參數(shù)指標(biāo)，來(lái)具體場(chǎng)景具體分析，來(lái)達(dá)到優(yōu)化SQL語(yǔ)句的執(zhí)行效率的效果

id

select查詢的序列號(hào)，包含一組數(shù)字，表示查詢中執(zhí)行select子句或者操作表的順序

1、如果id相同，那么執(zhí)行順序從上到下

2、如果id不同，如果是子查詢，id的序號(hào)會(huì)遞增，id值越大優(yōu)先級(jí)越高，越先被執(zhí)行

3、id相同和不同的，同時(shí)存在：相同的可以認(rèn)為是一組，從上往下順序執(zhí)行，在所有組中，id值越大，優(yōu)先級(jí)越高，越先執(zhí)行

select_type

主要用來(lái)分辨查詢的類型，是普通查詢還是聯(lián)合查詢還是子查詢

table

對(duì)應(yīng)行正在訪問(wèn)哪張表，表名或者別名，可能是臨時(shí)表或者union合并結(jié)果集

1、如果是具體的表名，則表明從實(shí)際的物理表中獲取數(shù)據(jù)，當(dāng)然也可以是表的別名

2、表名是derivedN的形式，表示使用了id為N的查詢產(chǎn)生的衍生表

3、當(dāng)有union result的時(shí)候，表名是union n1,n2等的形式，n1,n2表示參與union的id

type

type顯示的是訪問(wèn)類型，訪問(wèn)類型表示我是以何種方式去訪問(wèn)我們的數(shù)據(jù)，最容易想的是全表掃描，直接暴力的遍歷一張表去尋找需要的數(shù)據(jù)，效率非常低下，訪問(wèn)的類型有很多，效率從最好到最壞依次是：

system const eq_ref ref fulltext ref_or_null index_merge

unique_subquery index_subquery range index ALL

possible_keys

顯示可能應(yīng)用在這張表中的索引，一個(gè)或多個(gè)，查詢涉及到的字段上若存在索引，則該索引將被列出，但不一定被查詢實(shí)際使用

key

實(shí)際使用的索引，如果為null，則沒(méi)有使用索引，查詢中若使用了覆蓋索引，則該索引和查詢的select字段重疊。

key_len

表示索引中使用的字節(jié)數(shù)，可以通過(guò)key_len計(jì)算查詢中使用的索引長(zhǎng)度，在不損失精度的情況下長(zhǎng)度越短越好。

ref

顯示索引的哪一列被使用了，如果可能的話，是一個(gè)常數(shù)

rows

根據(jù)表的統(tǒng)計(jì)信息及索引使用情況，大致估算出找出所需記錄需要讀取的行數(shù)，此參數(shù)很重要，直接反應(yīng)的sql找了多少數(shù)據(jù)，在完成目的的情況下越少越好

extra

SQL包含額外的信息

網(wǎng)頁(yè)題目：mysql執(zhí)行計(jì)劃怎么做,mysql執(zhí)行計(jì)劃命令
文章來(lái)源：http://chinadenli.net/article25/dsipdci.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供自適應(yīng)網(wǎng)站、面包屑導(dǎo)航、網(wǎng)站導(dǎo)航、品牌網(wǎng)站制作、標(biāo)簽優(yōu)化、網(wǎng)站內(nèi)鏈

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)