Five minutes to understand MySQL index push down

Three evil 2021-09-15 07:45:59
minutes understand mysql index push

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 About the architecture

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).

 User table

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.

B+ Tree union index

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 :

 not used ICP

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


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 :

 Use ICP Schematic 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)

本文为[Three evil]所创,转载请带上原文链接,感谢

  1. 小白也能看懂的dubbo3应用级服务发现详解
  2. SpringBoot异步使用@Async原理及线程池配置
  3. Questions d'entrevue de test avancé de Dachang, liste des compétences de base de l'entrevue Java,
  4. SpringBoot异步使用@Async原理及線程池配置
  5. Springboot utilise asynchrone le principe @ async et la configuration du pool de threads
  6. Détails de la découverte du Service d'application Dubbo 3 que Xiaobai peut également comprendre
  7. Springboot utilise asynchrone le principe @ async et la configuration du pool de threads
  8. 如何强大且优雅的搞定Linux文件系统,算法题 JVM,
  9. 太牛了,阿里P7架构师带你看透maven的来龙去脉,
  10. Oracle central et Oracle décentralisé
  11. java JavaBean
  12. Java wrapper type
  13. Java super keyword
  14. Java static keyword
  15. Java this keyword
  16. Java interface
  17. 太牛了,阿裏P7架構師帶你看透maven的來龍去脈,
  18. C'est génial, l'architecte Ali p7 vous montre à travers Maven.
  19. Comment traiter le système de fichiers Linux avec puissance et élégance, algorithme JVM,
  20. Java + SSM Social Insurance Pension System for Computer Graduation Design
  21. Usage of Java scanner
  22. Java inheritance
  23. Java method review
  24. java JVM
  25. Java Basics
  26. Java file operation object IO stream
  27. Java console reads multi character input and output
  28. Java simple array sorting
  29. In addition to MySQL master-slave, you have another choice, Galera
  30. Configuration standard dockerfile et docker-composer.yml
  31. 字节大神强推千页PDF学习笔记,2021Java开发学习路线,
  32. 字节大牛耗时八个月又一力作,靠这份Java知识点PDF成功跳槽,
  33. 字节大牛教你手撕Java学习,最新大厂程序员进阶宝典,
  34. Comment l'automne est - il beau?Ces 24 ensembles de modèles d'automne et d'hiver sont grands, minces et vieillissants
  35. 字節大牛教你手撕Java學習,最新大廠程序員進階寶典,
  36. 字節大牛耗時八個月又一力作,靠這份Java知識點PDF成功跳槽,
  37. Byte Bull vous apprend à déchiqueter Java à la main, le dernier dictionnaire avancé des programmeurs de grandes usines,
  38. Byte Bull a pris huit mois à travailler dur et a réussi à changer d'emploi avec ce PDF Java Knowledge point.
  39. Byte God Push 1000 pages PDF Learning notes, 2021 Java Development Learning route,
  40. Five minutes to understand MySQL index push down
  41. Spring中@within与@target的一些区别
  42. 力荐:提高千倍效率的一些 Java 代码小技巧
  43. Redis技术专题系列之帮你从底层彻底吃透RDB技术原理(基础篇)
  44. Juan Benet et vitalik buterin discutent des réflexions sur les médias sociaux décentralisés
  45. Ipfs Weekly Report 152 | pinata launched "submarining"
  46. Performance optimization issue 03 - HTTP request optimization
  47. JavaScript genrator generator
  48. 字节跳动Java面试全套真题解析在互联网火了,面试大厂应该注意哪些问题?
  49. 字节跳动Java社招,2021年阿里 腾讯 快手offer都已拿到!
  50. 用Java实现红黑树
  51. 使用Redis Stream来做消息队列和在Asp.Net Core中的实现
  52. 海量列式非关系数据库HBase 架构,shell与API
  53. Redis Technology Topic Series vous aide à comprendre les principes de la technologie rdb du Bas (Basic)
  54. Conseils: quelques conseils pour améliorer l'efficacité du Code Java
  55. Quelques différences entre @ within et @ Target au printemps
  56. 海量列式非關系數據庫HBase 架構,shell與API
  57. Architecture, Shell et API de base de données non relationnelle à grande échelle
  58. Mise en œuvre de l'arbre Rouge et noir en Java
  59. Byte Hopping Java Service Call, 2021 Alibaba Tencent Express offer a été obtenu!
  60. Byte Jump Java interview Full Set of true Problems Analysis in Internet fire, interview Factory should pay attention to what Problems?