MySQL語(yǔ)句執(zhí)行的神器Optimizer Trace怎么用

MySQL語(yǔ)句執(zhí)行的神器Optimizer Trace怎么用，針對(duì)這個(gè)問(wèn)題，這篇文章詳細(xì)介紹了相對(duì)應(yīng)的分析和解答，希望可以幫助更多想解決這個(gè)問(wèn)題的小伙伴找到更簡(jiǎn)單易行的方法。

明明有索引，但是為什么查詢時(shí)未使用索引等。為此，MySQL 提供了 Optimizer Trace 功能，讓我們能更加詳細(xì)的了解 SQL 語(yǔ)句執(zhí)行的所有分析，優(yōu)化和選擇過(guò)程。

如果您想更深入地了解為什么選擇某個(gè)查詢計(jì)劃，那么優(yōu)化器跟蹤非常有用。雖然 EXPLAIN 顯示選定的計(jì)劃，但Optimizer Trace 能顯示為什么選擇計(jì)劃：您將能夠看到替代計(jì)劃，估計(jì)成本以及做出的決策。本篇文章會(huì)詳細(xì)講解 Optimizer Trace 展示的所有相關(guān)信息，并且會(huì)輔之一些具體使用案例。

基于成本的執(zhí)行計(jì)劃

在了解 Optimizer Trace 的之前，我們先來(lái)學(xué)習(xí)一下 MySQL 是如何選擇眾多執(zhí)行計(jì)劃的。

MySQL 會(huì)使用一個(gè)基于成本(cost)的優(yōu)化器對(duì)執(zhí)行計(jì)劃進(jìn)行選擇。每個(gè)執(zhí)行計(jì)劃的成本大致反應(yīng)了該計(jì)劃查詢所需要的資源，主要因素是計(jì)算查詢時(shí)將要訪問(wèn)的行數(shù)。優(yōu)化器主要根據(jù)從存儲(chǔ)引擎獲取數(shù)據(jù)的統(tǒng)計(jì)數(shù)據(jù)和數(shù)據(jù)字典中元數(shù)據(jù)信息來(lái)做出判斷。它會(huì)決定是使用全表掃描或者使用某一個(gè)索引進(jìn)行掃描，也會(huì)決定表 join的順序。優(yōu)化器的作用如下圖所示。

優(yōu)化器會(huì)為每個(gè)操作標(biāo)上成本，這些成本的基準(zhǔn)單位或最小值是從磁盤讀取隨機(jī)數(shù)據(jù)頁(yè)的成本，其他操作的成本都是它的倍數(shù)。所以優(yōu)化器可以根據(jù)每個(gè)執(zhí)行計(jì)劃的所有操作為其計(jì)算出總的成本，然后從眾多執(zhí)行計(jì)劃中，選取成本最小的來(lái)最終執(zhí)行。

既然是基于統(tǒng)計(jì)數(shù)據(jù)來(lái)進(jìn)行標(biāo)記成本，就總會(huì)有樣本無(wú)法正確反映整體的情況，這也是 MySQL 優(yōu)化器有時(shí)做出錯(cuò)誤優(yōu)化的重要原因之一。

Optimizer Trace 的基本使用

首先，我們來(lái)看一下具體如何使用 Optimizer Trace。默認(rèn)情況下，該功能是關(guān)閉的，大家可以使用如下方式打開該功能，然后執(zhí)行自己需要分析的 SQL 語(yǔ)句，然后再?gòu)?INFORMATION_SCHEMA 的 OPTIMIZER_TRACE中查找到該 SQL 語(yǔ)句執(zhí)行優(yōu)化的相關(guān)信息。

# 1. 打開optimizer trace功能 (默認(rèn)情況下它是關(guān)閉的):
SET optimizer_trace="enabled=on";
SELECT ...; # 這里輸入你自己的查詢語(yǔ)句
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
# 當(dāng)你停止查看語(yǔ)句的優(yōu)化過(guò)程時(shí)，把optimizer trace功能關(guān)閉
SET optimizer_trace="enabled=off";

這個(gè) OPTIMIZER_TRACE 表有4個(gè)列，如下所示：

• QUERY：表示我們的查詢語(yǔ)句。

• TRACE：表示優(yōu)化過(guò)程的JSON格式文本。

• MISSING_BYTES_BEYOND_MAX_MEM_SIZE：由于優(yōu)化過(guò)程可能會(huì)輸出很多，如果超過(guò)某個(gè)限制時(shí)，多余的文本將不會(huì)被顯示，這個(gè)字段展示了被忽略的文本字節(jié)數(shù)。

• INSUFFICIENT_PRIVILEGES：表示是否沒有權(quán)限查看優(yōu)化過(guò)程，默認(rèn)值是0，只有某些特殊情況下才會(huì)是1，我們暫時(shí)不關(guān)心這個(gè)字段的值。

其中，信息最多也最為重要的就是第二列 TRACE，它也是我們后續(xù)分析的重點(diǎn)。

TRACE 列的基本格式

TRACE 列的內(nèi)容是一個(gè)超級(jí)大的 JSON 數(shù)據(jù)，直接展開然后一條一條解析估計(jì)能看到大伙腦殼疼。

所以，我們先來(lái)看一下這坨大 JSON 的骨架。它有三大塊內(nèi)容，也代表著 SQL 語(yǔ)句處理的三個(gè)階段，分別為準(zhǔn)備階段，優(yōu)化階段和執(zhí)行階段。

接下來(lái)，我們?cè)敿?xì)介紹一個(gè)案例，在案例中介紹涉及到的具體字段和含義。

為什么查詢未走索引而是全表掃描

首先，SQL 語(yǔ)句查詢不使用索引的情況有很多，我們這里只討論因?yàn)榛诔杀镜膬?yōu)化器認(rèn)為全表查詢執(zhí)行計(jì)劃的成本低于走索引執(zhí)行計(jì)劃的情況。

如下圖這個(gè)場(chǎng)景，明明 val 列上有索引，并且 val 現(xiàn)存值也有一定差異性，為什么沒有使用索引進(jìn)行查詢呢？

我們按照上文使用 Optimizer Trace 找到其 join_optimization 中 range_analysis 相關(guān)數(shù)據(jù)，它會(huì)展示 where 從句范圍查詢過(guò)程中索引的選擇情況

由上圖可以看出，MySQL 對(duì)比了全表掃描和使用 val 作為索引兩個(gè)方案的成本，最后發(fā)現(xiàn)雖然全表掃描需要掃描更多的行，但是成本更低。所以選擇了全表掃描的執(zhí)行方案。

這是為什么呢？明明使用 val 索引可以少掃描 4 行。這其實(shí)涉及 InnoDB 中使用索引查詢數(shù)據(jù)行的原理。

Innodb引擎查詢記錄時(shí)在無(wú)法使用索引覆蓋(也就是需要查詢的數(shù)據(jù)多與索引值，比如該例子中，我要查name，而索引列是 val)的場(chǎng)景下，需要做回表操作獲取記錄的所需字段，也就是說(shuō)，通過(guò)索引查出主鍵，再去查數(shù)據(jù)行，取出對(duì)應(yīng)的列，這樣勢(shì)必是會(huì)多花費(fèi)成本的。

所以在回表數(shù)據(jù)量比較大時(shí)，經(jīng)常會(huì)出現(xiàn) Mysql 對(duì)回表操作查詢代價(jià)預(yù)估代價(jià)過(guò)大而導(dǎo)致不使用索引的情況。

一般來(lái)說(shuō)，當(dāng)SQL 語(yǔ)句查詢超過(guò)表中超過(guò)大概五分之一的記錄且不能使用覆蓋索引時(shí)，會(huì)出現(xiàn)索引的回表代價(jià)太大而選擇全表掃描的現(xiàn)象。且這個(gè)比例隨著單行記錄的字節(jié)大小的增加而略微增大。

通過(guò) range_analysis 中的相關(guān)數(shù)據(jù)也可以對(duì) where 從句使用多個(gè)索引列，如何選擇執(zhí)行時(shí)使用的索引的情況進(jìn)行分析。

關(guān)于MySQL語(yǔ)句執(zhí)行的神器Optimizer Trace怎么用問(wèn)題的解答就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，如果你還有很多疑惑沒有解開，可以關(guān)注億速云行業(yè)資訊頻道了解更多相關(guān)知識(shí)。