Shulou(Shulou.com)06/03 Report--

This article mainly explains "what is MySQL index push down", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let Xiaobian take you to learn "What is MySQL index push down"!

directory

1. The leftmost prefix principle

2. Back to the table

3. Index push down

Foreword:

Index Push Down (ICP) is an excellent solution for cases where MySQL uses indexes to retrieve rows of data from tables.

Without index pushdown, MySQL locates rows in the table by traversing the index through the storage engine and returns them to the MySQl server, which then determines WHERE conditions to add rows to the result set.

Index pushdown is enabled, and the WHERE condition part can be evaluated using only columns in the index, in which case MySQL server pushes this part of the WHERE condition down to the storage engine, which then evaluates the pushed index condition using index entries, and reads from the table only if the condition is met

Index push-down reduces the number of times the storage engine accesses the data table and the number of times the MySQL server accesses the storage engine.

There is no doubt that the above paragraph is quite difficult to understand, but please do not lose heart, I will use the most understandable language to take you to understand the index push down.

To sum up:

leftmost prefix principle

return statement

1. The leftmost prefix principle

  MySQL follows the leftmost prefix principle when establishing a union index. For example, the User table now establishes a union index (id, name, age). According to the leftmost prefix principle, this union index can only be used when the conditional part of SQL hits (id),(id, name) or (id, name, age).

This index can be used in the following cases:

SELECT * FROM USER WHERE id = 1SELECT * FROM USER WHERE id = 1 and name = 'zhangsan'SELECT * FROM USER WHERE id = 1 and name = 'zhangsan' and age = 18

This index cannot be used in the following cases:

SELECT * FROM USER WHERE name = 'zhangsan'SELECT * FROM USER WHERE age = 18SELECT * FROM USER WHERE name = 'zhangsan' and age = 18

For union indexes mysql matches right until it encounters range queries (>,,

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.