1前言

我們本節(jié)來看看我們常說的索引下推。

2什么是索引下推

索引下推(Index Condition Pushdown，簡(jiǎn)稱ICP)，是MySQL5.6版本的新特性，用于優(yōu)化數(shù)據(jù)查詢。

不使用索引條件下推優(yōu)化時(shí)存儲(chǔ)引擎通過索引檢索到數(shù)據(jù)，然后返回給MySQL服務(wù)器，服務(wù)器然后判斷數(shù)據(jù)是否符合條件。

當(dāng)使用索引條件下推優(yōu)化時(shí)，如果存在某些被索引的列的判斷條件時(shí)，MySQL服務(wù)器將這一部分判斷條件傳遞給存儲(chǔ)引擎，然后由存儲(chǔ)引擎通過判斷索引是否符合MySQL服務(wù)器傳遞的條件，只有當(dāng)索引符合條件時(shí)才會(huì)將數(shù)據(jù)檢索出來返回給MySQL服務(wù)器。

索引條件下推優(yōu)化可以減少存儲(chǔ)引擎查詢基礎(chǔ)表的次數(shù)，也可以減少M(fèi)ySQL服務(wù)器從存儲(chǔ)引擎接收數(shù)據(jù)的次數(shù)。

換句話說：索引下推能減少回表查詢次數(shù)，提高查詢效率。

3索引下推優(yōu)化的原理

我們先簡(jiǎn)單了解一下MySQL大概的架構(gòu)：

MySQL從上至下分為以下幾層：

• MySQL服務(wù)層：包括NoSQL和SQL接口、查詢解析器、優(yōu)化器、緩存和Buffer等組件。
• 存儲(chǔ)引擎層：各種插件式的表格存儲(chǔ)引擎，實(shí)現(xiàn)事務(wù)、索引等各種存儲(chǔ)引擎相關(guān)的特性。
• 文件系統(tǒng)層: 讀寫物理文件。

MySQL服務(wù)層負(fù)責(zé)SQL語法解析、生成執(zhí)行計(jì)劃等，并調(diào)用存儲(chǔ)引擎層去執(zhí)行數(shù)據(jù)的存儲(chǔ)和檢索。

索引下推的下推其實(shí)就是指將部分上層（服務(wù)層）負(fù)責(zé)的事情，交給了下層（引擎層）去處理。

我們來具體看一下，在沒有使用ICP的情況下，MySQL的查詢：

• 獲取下一行，首先讀取索引信息，然后根據(jù)索引將整行數(shù)據(jù)讀取出來。
• 然后通過where條件判斷當(dāng)前數(shù)據(jù)是否符合條件，符合返回?cái)?shù)據(jù)。

使用ICP的情況下，查詢過程：

• 獲取下一行的索引信息。
• 檢查索引中存儲(chǔ)的列信息是否符合索引條件，如果符合將整行數(shù)據(jù)讀取出來，如果不符合跳過讀取下一行。
• 用剩余的判斷條件，判斷此行數(shù)據(jù)是否符合要求，符合要求返回?cái)?shù)據(jù)。

4索引下推適用條件

• 需要整表掃描的情況。比如：range, ref, eq_ref, ref_or_null 。
• 適用于InnoDB 引擎和 MyISAM 引擎的查詢。（5.6版本不適用分區(qū)表查詢，5.7版本后可以用于分區(qū)表查詢）。
• 對(duì)于InnDB引擎只適用于二級(jí)索引，因?yàn)镮nnDB的聚簇索引會(huì)將整行數(shù)據(jù)讀到InnDB的緩沖區(qū)，這樣一來索引條件下推的主要目的減少IO次數(shù)就失去了意義。因?yàn)閿?shù)據(jù)已經(jīng)在內(nèi)存中了，不再需要去讀取了。
• 引用子查詢的條件不能下推。
• 調(diào)用存儲(chǔ)過程的條件不能下推，存儲(chǔ)引擎無法調(diào)用位于MySQL服務(wù)器中的存儲(chǔ)過程。
• 觸發(fā)條件不能下推。

5EXPLAN分析

當(dāng)使用explan進(jìn)行分析時(shí)，如果使用了索引條件下推，Extra會(huì)顯示Using index condition。并不是Using index。

因?yàn)椴⒉荒艽_定利用索引條件下推查詢出的數(shù)據(jù)就是符合要求的數(shù)據(jù)，還需要通過其他的查詢條件來判斷。

6索引下推的具體實(shí)踐

理論比較抽象，我們來上一個(gè)實(shí)踐。使用一張用戶表tuser，表里創(chuàng)建聯(lián)合索引（name, age）。

如果現(xiàn)在有一個(gè)需求：檢索出表中名字第一個(gè)字是張，而且年齡是10歲的所有用戶。那么，SQL語句是這么寫的：

select * from tuser where name like '張%' and age=10;

假如你了解索引最左匹配原則，那么就知道這個(gè)語句在搜索索引樹的時(shí)候，只能用 張，找到的第一個(gè)滿足條件的記錄id為1。

那接下來的步驟是什么呢？

沒有使用ICP

在MySQL 5.6之前，存儲(chǔ)引擎根據(jù)通過聯(lián)合索引找到name likelike '張%' 的主鍵id（1、4），逐一進(jìn)行回表掃描，去聚簇索引找到完整的行記錄，server層再對(duì)數(shù)據(jù)根據(jù)age=10進(jìn)行篩選。我們看一下示意圖：

可以看到需要回表兩次，把我們聯(lián)合索引的另一個(gè)字段age浪費(fèi)了。

使用ICP

而MySQL 5.6 以后， 存儲(chǔ)引擎根據(jù)（name，age）聯(lián)合索引，找到，由于聯(lián)合索引中包含列，所以存儲(chǔ)引擎直接再聯(lián)合索引里按照age=10過濾。按照過濾后的數(shù)據(jù)再一一進(jìn)行回表掃描。我們看一下示意圖：

可以看到只回表了一次。

除此之外我們還可以看一下執(zhí)行計(jì)劃，看到Extra一列里 Using index condition，這就是用到了索引下推。

+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra || 1 | SIMPLE | tuser | NULL | range | na_index | na_index | 102 | NULL | 2 | 25.00 | Using index condition |

7相關(guān)系統(tǒng)參數(shù)

索引條件下推默認(rèn)是開啟的，可以使用系統(tǒng)參數(shù)optimizer_switch來控制器是否開啟。

查看默認(rèn)狀態(tài)：

mysql> select @@optimizer_switch\G;
index_merge=on,
index_merge_union=on,
index_merge_sort_union=on,
index_merge_intersection=on,
engine_condition_pushdown=on,
index_condition_pushdown=on,
mrr=on,
mrr_cost_based=on,
block_nested_loop=on,
batched_key_access=off,
materialization=on,
semijoin=on,
loosescan=on,
firstmatch=on,
duplicateweedout=on,
subquery_materialization_cost_based=on,
use_index_extensions=on,
condition_fanout_filter=on,
derived_merge=on

切換狀態(tài)：

set ="index_condition_pushdown=off";
set ="index_condition_pushdown=on";

8小結(jié)

索引下推優(yōu)化技術(shù)其實(shí)就是充分利用了索引中的數(shù)據(jù)，盡量在查詢出整行數(shù)據(jù)之前過濾掉無效的數(shù)據(jù)。

由于需要存儲(chǔ)引擎將索引中的數(shù)據(jù)與條件進(jìn)行判斷，所以這個(gè)技術(shù)是基于存儲(chǔ)引擎的，只有特定引擎可以使用。并且判斷條件需要是在存儲(chǔ)引擎這個(gè)層面可以進(jìn)行的操作才可以，比如調(diào)用存儲(chǔ)過程的條件就不可以，因?yàn)榇鎯?chǔ)引擎沒有調(diào)用存儲(chǔ)過程的能力。

到此這篇關(guān)于MySQL中的count(*) 和 count(1) 區(qū)別性能對(duì)比的文章就介紹到這了,更多相關(guān)mysql  count(*) 和 count(1)區(qū)別內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論