Hello everyone , I'm the third , Share a little knowledge today —— Index push down .

If you're in an interview , hear MySQL5.6”、“ Index optimization ” Words like that , You're going to get To , The question is “ Index push down ”.

What is index push down

Index push down (Index Condition Pushdown, abbreviation ICP), yes MySQL5.6 New features of version , It can reduce the number of queries back to the table , Improve query efficiency .

The principle of index push down optimization

Let's take a brief look at MySQL General structure :

MySQL The service layer is responsible for SQL Syntax parsing 、 Generate execution plan, etc , And call the storage engine layer to perform data storage and retrieval .

Index push down Of Push down In fact, it means that part of the upper layer ( Service layer ) Responsible things , To the lower level ( Engine layer ) To deal with .

Let's take a specific look at , Before use ICP Under the circumstances ,MySQL Query for :

  • The storage engine reads index records ;
  • According to the primary key value in the index , Locate and read the complete row record ;
  • The storage engine gives the records to Server Layer to detect whether the record meets WHERE Conditions .

Use ICP Under the circumstances , The query process :

  • The storage engine reads index records ( Not a complete line record );
  • Judge WHERE Whether the condition part can be checked with the columns in the index , Condition not satisfied , Then process the next row of index records ;
  • Conditions met , Use the primary key in the index to locate and read the complete row record ( It's the so-called back table );
  • The storage engine gives the records to Server layer ,Server The layer detects whether the record meets WHERE The rest of the condition .

Specific practice of index push down

The theory is abstract , Let's take a practice .

Use a user table tuser, Create a union index in the table (name, age).

If there is a need now : Search out the The first word of the name is Zhang , And the age is 10 All users at the age of . that ,SQL This is how the statement is written :

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

If you understand the leftmost matching principle of index , Then you know that this statement is used when searching the index tree , Only use Zhang , The first record found that meets the condition id by 1.

What are the next steps ?

Not used ICP

stay MySQL 5.6 Before , The storage engine finds... Through the federated index name likelike ' Zhang %' Primary key of id(1、4), Scan the table one by one , Go to the cluster index to find the complete row record ,server The layer then analyzes the data according to age=10 Screening .

Let's take a look at the diagram :

You can see that you need to go back to the table twice , Put us in another field of the joint index age wasted .

Use ICP

and MySQL 5.6 in the future , The storage engine is based on (name,age) Joint index , find name likelike ' Zhang %', Because the union index contains age Column , So the storage engine directly federates the index according to age=10 Filter . Scan the table again according to the filtered data .

Let's take a look at the diagram :

You can see that you only went back to the table once .

In addition, we can also look at the implementation plan , notice Extra In a column Using index condition, This is the index push down .

+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| 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 |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+

Index push down usage conditions

  • It can only be used for rangerefeq_refref_or_null Access method ;
  • It can only be used for InnoDB and MyISAM Storage engine and its partition table ;
  • Yes InnoDB For storage engines , Index push down is only applicable to secondary indexes ( Also called secondary index );

The purpose of index push down is to reduce the number of table returns , That is to reduce IO operation . about InnoDB Of Cluster index Come on , Data and index are together , There is no such thing as going back to the table .

  • Conditions that reference subqueries cannot be pushed down ;
  • A condition that references a storage function cannot be pushed down , Because the storage engine cannot call the storage function .

Related system parameters

Push is enabled by default under index conditions , You can use system parameters optimizer_switch To check whether the controller is on .

View default status :

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)

Switch state :

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

Reference resources :

[1].《 MySQL Technology insider InnoDB Storage engine 》

[2]. 《MySQL actual combat 45 speak 》

[3]. MySQL Index push down (ICP) Simple understanding and examples

[4]. Read what is MySQL Index push down (ICP)

Five minutes to understand MySQL More related articles pushed down the index

  1. Five minutes to understand Vuex

    I've been using it all this time vue Write project ,vuex It will also be used in the project , But there is always a hazy feeling . So I decided to understand it completely . Read the official documents all afternoon , And information , Only then discovered vuex so easy! As a person in a circle ...

  2. What a programmer must know —— You understand mysql Is the indexing mechanism in place ?

    One . What is the index MySQL The official definition of index is : Indexes (Index) Help MySQL Efficient Get the data structure of the data , and MYSQL The data structure used is :B+ Trees I recommend you to read a book here ,< A book with an in-depth understanding of computer systems ...

  3. A complete understanding of MySQL Index optimization EXPLAIN encyclopedia

    1.MySQL Logical architecture Everyday life is CURD In the process of , You can't avoid dealing with databases , Most businesses cannot do without the design and implementation of database tables SQL Compiling , So how do you write SQL Statement performance is better ? Let's take a look at the whole MySQL Logical architecture : M ...

  4. Five minutes to find out MySQL Transaction isolation level

    I haven't touched the database for a long time , I just remembered that I was always stuck in the transaction isolation level when I was working on the database , What you don't understand . Now I want to sort this out , Try to describe it in the simplest language , For new people . First create a table account. The process of creating a table skips ( ...

  5. Five minutes to figure out what is B- Trees ( Whole process diagram )【 turn 】

    Foreplay We all know that dynamic search tree can improve search efficiency , such as : Binary search tree , Balanced binary search tree , Red and black trees . The time complexity of their search efficiency O(log2n), It has to do with the depth of the tree , So how can we improve efficiency ? Of course, the quickest way is to reduce ...

  6. Five minutes to understand Linux Key knowledge , A fool can learn !

    source : No obsession , You don't succeed www.cnblogs.com/justmine/p/9053419.html Write it at the front We all know Linux Is a multi-user support . A multitasking system , It's also one of its best features , That is, there may be very ...

  7. c# Code The interface One minute to understand why your blog is not read After watching python This crawler code ,java Tears c# There was silence Image binary conversion is related to database storage C#7.0-- References to return values and references to local variables JS Call directly C# Backstage method (ajax call ) Linq To Json SqlServer recursive query

    The procedure of weather forecast . The procedure is not difficult . Seeing this demand, the first idea is that as long as we find the right weather forecast interface, everything is small , Do as you say , Communicate the price to the student immediately . ​ ​ But in the process of negotiating an offer , Almost didn't make me squirt old blood on the keyboard , Say we're human beings ...

  8. Five minutes Go.js

    Five minutes Go.js  1. be based on html5~ because Go.js It's a place that depends on HTML5 Characteristic JavaScript library , So make sure your page says it's a HTML5 file , Of course, you need to load the library <!DOCTYPE ...

  9. zookeeper- Architecture design and role division -《 Big data in five minutes a day 》

    The reading time of this article 5 About minutes Click to see < Big data in five minutes a day > Complete Mind Map   zookeeper As a distributed coordination system , Many components depend on it , Then its availability is very important , So the same thing to ensure availability ...

  10. zookeeper The core -zab agreement -《 Big data in five minutes a day 》

    Last article <paxos And consistency > Speaking of zab Is in paxos Made important transformation on the basis of , Solved a series of problems , In this article, let's talk about this zab. zab The full name of the agreement is ZooKeeper Atomic Bro ...

Random recommendation

  1. Apache_proxy Load balancing and Session Copy

    I checked the information on the Internet today , Before using apache Of jk Load balancing module . Later I thought jk The load configuration is a little rigid , Requests can only be distributed according to the load weight value , Failed to achieve more intelligent load balancing , And use mod_jk It's really slow to visit the page ...

  2. Yes Oracle Understanding of bad database blocks

    1. Physical bad blocks and logical bad blocks There is a concept in databases called the consistency of data blocks ,Oracle There are two levels of data block consistency : Physical consistency and logical consistency , If a data block is inconsistent at these two levels , That corresponds to what we are going to say today ...

  3. no need ide compile java The program calls jar package

    Call... In a specific directory jar package javac -cp d:\javatest\dom4j.jar Dom4jDemo.java // This part must pay attention to ,d:\javatest yes Dom4jDemo.class Where ...

  4. OC in NSArray

    #import <Foundation/Foundation.h> int main(int argc, const char * argv[]) { @autoreleasepool { ...

  5. baidu Express inquiry API

    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/ ...

  6. Windows API Document processing

    CloseHandle Close a kernel object . It includes documents . File mapping . process . Threads . Security and synchronization objects, etc CompareFileTime Compare the time of the two files CopyFile Copy file CreateDirect ...

  7. JDBC Program common errors and debugging methods

    Detailed introduction :http://dev.mysql.com/doc/refman/5.5/en/error-handling.html http://dev.mysql.com/doc/refman/5.5/ ...

  8. shell Programming based (5)--- Cyclic instruction

    while A cycle of types while Type of loop is a kind of indefinite loop , Each cycle verifies the given cycle conditions , Determine whether to proceed to the next cycle .linux in while The way to write a loop is the same as c I really want to , But there are some differences in the way conditions are given . The first ...

  9. Luogu P4063 [JXOI2017] The sequence (dp)

    The question Topic link Sol This question is not difficult to think about , It's just that it's troublesome to write , Then I went to have a look loj The shortest code of can only Orz First of all, it is not difficult to find a property : The range that can be selected must be shrinking , And the new optional interval must be somewhere in the old interval ...

  10. flask Hook function

    explain : before_request function , It's a decorator , He can put the code that needs to be set as a hook function before the view function is executed Example : from flask import Flask,url_for,redire ...