一篇文章讀懂什么是MySQL索引下推(ICP)

 更新時間:2021年09月09日 15:22:38   作者:牧碼人zhouz  
當MySQL使用一個索引來檢索表中的行時,可以使用ICP作為一種優化方案,下面這篇文章主要給大家介紹了如何通過一篇文章讀懂什么是MySQL索引下推(ICP)的相關資料,文中通過示例代碼介紹的非常詳細,需要的朋友可以參考下

一、簡介

ICP(Index Condition Pushdown)是在MySQL 5.6版本上推出的查詢優化策略,把本來由Server層做的索引條件檢查下推給存儲引擎層來做,以降低回表和訪問存儲引擎的次數,提高查詢效率。

二、原理

為了理解ICP是如何工作的,我們先了解下沒有使用ICP的情況下,MySQL是如何查詢的:

  • 存儲引擎讀取索引記錄;
  • 根據索引中的主鍵值,定位并讀取完整的行記錄;
  • 存儲引擎把記錄交給Server層去檢測該記錄是否滿足WHERE條件。

使用ICP的情況下,查詢過程如下:

  • 讀取索引記錄(不是完整的行記錄);
  • 判斷WHERE條件部分能否用索引中的列來做檢查,條件不滿足,則處理下一行索引記錄;
  • 條件滿足,使用索引中的主鍵去定位并讀取完整的行記錄(就是所謂的回表);
  • 存儲引擎把記錄交給Server層,Server層檢測該記錄是否滿足WHERE條件的其余部分。

三、實踐

先創建一張表,并插入記錄

CREATE TABLE user (
id int(11) NOT NULL AUTO_INCREMENT COMMENT "主鍵",
name varchar(32)  COMMENT "姓名",
city varchar(32)  COMMENT "城市",
age int(11)  COMMENT "年齡",
primary key(id),
key idx_name_city(name, city)
)engine=InnoDB default charset=utf8;

insert into user(name, city, age) values("ZhaoDa", "BeiJing", 20),("QianEr", "ShangHai", 21),("SunSan", "GuanZhou", 22), ("LiSi", "ShenZhen", 24), ("ZhouWu", "NingBo", 25),  ("WuLiu", "HangZhou", 26), ("ZhengQi", "NanNing", 27), ("WangBa", "YinChuan", 28), ("LiSi", "TianJin", 29), ("ZhangSan", "NanJing", 30), ("CuiShi", "ZhengZhou", 65),  ("LiSi", "KunMing", 29), ("LiSi", "ZhengZhou", 30);

查看一下表記錄

mysql> select * from user;
+----+----------+-----------+------+
| id | name     | city      | age  |
+----+----------+-----------+------+
|  1 | ZhaoDa   | BeiJing   |   20 |
|  2 | QianEr   | ShangHai  |   21 |
|  3 | SunSan   | GuanZhou  |   22 |
|  4 | LiSi     | ShenZhen  |   24 |
|  5 | ZhouWu   | NingBo    |   25 |
|  6 | WuLiu    | HangZhou  |   26 |
|  7 | ZhengQi  | NanNing   |   27 |
|  8 | WangBa   | YinChuan  |   28 |
|  9 | LiSi     | TianJin   |   29 |
| 10 | ZhangSan | NanJing   |   30 |
| 11 | CuiShi   | ZhengZhou |   65 |
| 12 | LiSi     | KunMing   |   29 |
| 13 | LiSi     | ZhengZhou |   30 |
+----+----------+-----------+------+
13 rows in set (0.00 sec)

注意,這張表里創建了聯合索引(name, city),假設我們想查詢如下語句:

select * from user where name="LiSi" and city like "%Z%" and age > 25;

3.1 不使用索引下推

在不使用索引下推的情況下,根據聯合索引“最左匹配”原則,只有name列能用到索引,city列由于是模糊匹配,是不能用到索引的,此時的執行過程是這樣的:

  1. 存儲引擎根據(name, city)聯合索引,找到name值為LiSi的記錄,共4條記錄;
  2. 然后根據這4條記錄中的id值,逐一進行回表掃描,去聚簇索引中取出完整的行記錄,并把這些記錄返回給Server層;
  3. Server層接收到這些記錄,并按條件name="LiSi" and city like "%Z%" and age > 25進行過濾,最終留下("LiSi", "ZhengZhou", 30)這條記錄。

畫張圖看一下:

未使用使用索引條件下推

3.2 使用索引下推

使用索引下推的情況下,執行過程是這樣的:

  • 存儲引擎根據(name, city)聯合索引,找到name='LiSi'的記錄,共4條;
  • 由于聯合索引中包含city列,存儲引擎直接在聯合索引中按city like "%Z%"進行過濾,過濾后剩下2條記錄;
  • 根據過濾后的記錄的id值,逐一進行回表掃描,去聚簇索引中取出完整的行記錄,并把這些記錄返回給Server層;
  • Server層根據WHERE語句的其它條件age > 25,再次對行記錄進行篩選,最終只留下("LiSi", "ZhengZhou", 30)這條記錄。

畫張圖看一下:


使用索引條件下推

另外,從執行計劃里也可以看到使用了索引下推(Extra里顯示Using index condition)

mysql> explain select * from user where name="LiSi" and city like "%Z%" and age > 25;
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra                              |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+------------------------------------+
|  1 | SIMPLE      | user  | NULL       | ref  | idx_name_city | idx_name_city | 99      | const |    4 |     7.69 | Using index condition; Using where |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)

四、使用條件

  • 只能用于range、 ref、 eq_ref、ref_or_null訪問方法;
  • 只能用于InnoDB和 MyISAM存儲引擎及其分區表;
  • 對InnoDB存儲引擎來說,索引下推只適用于二級索引(也叫輔助索引);

tip:索引下推的目的是為了減少回表次數,也就是要減少IO操作。對于InnoDB的聚簇索引來說,完整的行記錄已經加載到緩存區了,索引下推也就沒什么意義了。

  • 引用了子查詢的條件不能下推;
  • 引用了存儲函數的條件不能下推,因為存儲引擎無法調用存儲函數。

五、相關系統參數

索引條件下推默認是開啟的,可以使用系統參數optimizer_switch來控制器是否開啟。

查看默認狀態:

mysql> select @@optimizer_switch\G;
*************************** 1. row ***************************
@@optimizer_switch: 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
1 row in set (0.00 sec)

切換狀態:

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

總結

到此這篇關于什么是MySQL索引下推(ICP)的文章就介紹到這了,更多相關MySQL索引下推(ICP)內容請搜索腳本之家以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持腳本之家!

相關文章

  • MySQL數據庫優化技術之配置技巧總結

    MySQL數據庫優化技術之配置技巧總結

    這篇文章主要介紹了MySQL數據庫優化技術之配置技巧,較為詳細的總結分析了MySQL進行硬件級軟件優化的相關方法與注意事項,需要的朋友可以參考下
    2016-07-07
  • mysql 5.7以上版本安裝配置方法圖文教程(mysql 5.7.12\mysql 5.7.13\mysql 5.7.14)

    mysql 5.7以上版本安裝配置方法圖文教程(mysql 5.7.12\mysql 5.

    這篇文章主要為大家分享了MySQL 5.7以上縮版本安裝配置方法圖文教程,包括mysql5.7.12、mysql5.7.13、mysql5.7.14安裝教程,包括感興趣的朋友可以參考一下
    2016-08-08
  • mysql手動刪除BINLOG的方法

    mysql手動刪除BINLOG的方法

    用于刪除列于在指定的日志或日期之前的日志索引中的所有二進制日志。這些日志也會從記錄在日志索引文件
    2013-03-03
  • MySQL8.0.20壓縮版本安裝教程圖文詳解

    MySQL8.0.20壓縮版本安裝教程圖文詳解

    這篇文章主要介紹了MySQL8.0.20壓縮版本安裝教程,需本文通過圖文并茂的形式給大家介紹的非常詳細,對大家的學習或工作具有一定的參考借鑒價值,要的朋友可以參考下
    2020-08-08
  • 講解MySQL中<=>操作符的用法

    講解MySQL中<=>操作符的用法

    這篇文章主要介紹了講解MySQL中<=>操作符的用法,整理自stackoverflow的相關實際問題,需要的朋友可以參考下
    2015-04-04
  • mysql主鍵的缺少導致備庫hang住

    mysql主鍵的缺少導致備庫hang住

    最近線上頻繁的出現slave延時的情況,經排查發現為用戶在刪除數據的時候,由于表主鍵的主鍵的缺少,同時刪除條件沒有索引,或或者刪除的條件過濾性極差,導致slave出現hang住
    2016-05-05
  • mysql觸發器之創建多個觸發器操作實例分析

    mysql觸發器之創建多個觸發器操作實例分析

    這篇文章主要介紹了mysql觸發器之創建多個觸發器操作,結合實例形式分析了mysql創建及使用多個觸發器的相關操作技巧,需要的朋友可以參考下
    2019-12-12
  • MySQL 兩種恢復數據的方法

    MySQL 兩種恢復數據的方法

    這篇文章主要介紹了MySQL 兩種恢復數據的方法,幫助恢復線上數據,保證數據完整,感興趣的朋友可以了解下
    2020-10-10
  • 關于MySQL innodb_autoinc_lock_mode介紹

    關于MySQL innodb_autoinc_lock_mode介紹

    下面小編就為大家帶來一篇關于MySQL innodb_autoinc_lock_mode介紹。小編覺得挺不錯的,現在就分享給大家,也給大家做個參考。一起跟隨小編過來看看吧
    2017-03-03
  • mysql prompt的用法詳解

    mysql prompt的用法詳解

    本篇文章是對mysql中prompt的用法進行了詳細的分析介紹,需要的朋友參考下
    2013-06-06

最新評論

精品国内自产拍在线观看