SoFunction
Updated on 2025-04-08

Sample code for implementing index pushdown in MySQL

Index push down（Index Condition Pushdown, abbreviationICP) is a database optimization technology designed to reduce the amount of data transmission from the storage engine to the database engine during database querying, thereby improving query performance. By filtering as much unnecessary data as possible during the index scanning phase, index pushdown can reduce table back operations (i.e., searching from index to actual data rows) and improve query efficiency.

1. Basic concepts of index pushdown

1. What is index pushdown?

Index pushdown is an optimization strategy that pushes more query conditions to the index scanning stage for filtering, rather than relying solely on the index itself to satisfy the query conditions. By applying additional filtering conditions during the index scanning process, the database can exclude rows that do not meet the criteria at an earlier stage, reducing subsequent data processing.

2. Why do you need to push down the index?

Traditional index scanning usually only uses the index itself to meet query conditions, such as when using conditions.WHERE a = 1 AND b = 2When the index may only be based onaColumns are searched. If further filtering is neededb = 2, you may need to return to the table to get the complete data rows and then filter them. This approach may lead to a large number of back-table operations, especially when the selectivity of query conditions is low, which can significantly affect query performance.

Index pushdown can reduce or even avoid table back operations by applying more filtering conditions during the index scanning phase, thereby improving query efficiency.

2. The working principle of index pushdown

1. Traditional index scanning process

Include composite index(a, b, c)As an example, execute the following query:

SELECT c FROM table_name WHERE a = 1 AND b = 2 AND d = 3;

The traditional index scanning process is as follows:

  • Use index(a, b, c)Searcha = 1andb = 2index entry.
  • Return to the table to getdThe value of the column.
  • Applicationd = 3filter conditions.
  • Return to meet the criteriacColumn value.

In this process, evendThe filtering conditions of the column are very strict, and index scan still requires returning to the table to obtain all the compliants.aandbRecord the record, then proceeddFiltering of columns.

2. Enable the scan process after index pushdown

After enabling index pushdown, the scanning process is as follows:

  • Use index(a, b, c)Searcha = 1andb = 2index entry.
  • During the index scanning process, directly read thec Column and storage engine dColumn (ifdColumns are included in the index, so there is no need to return to the table).
  • Applicationd = 3filter conditions.
  • Return to meet the criteriacColumn value.

By applying it in the index scanning staged = 3The filtering conditions of the database can reduce the amount of data that needs to be returned to the table, thereby improving query efficiency.

3. Conditions for index pushdown

The effectiveness of index pushdown depends on the following conditions:

  • Covering Index: If the query only involves columns in the index, it can avoid table back operations and further improve performance.
  • Support index pushdown database: Not all databases support index pushdown, depending on the database implementation and the capabilities of the optimizer.
  • The complexity of query conditions: Suitable for simple or moderate complexity filtering conditions that can be applied during the index scanning phase.

3. Advantages of index pushdown

  • Reduce table return operations: By applying additional filtering conditions during the index scanning phase, the number of times you need to return to the table to get the complete data rows can be significantly reduced.
  • Reduce I/O overhead: Reduce unnecessary data reading, reduce disk I/O overhead, and improve query performance.
  • Improve query speed: Overall, it improves the response speed of queries, especially when processing large-scale data sets, which are effective.
  • Optimize resource utilization: Reduce CPU and memory usage and improve system resource utilization.

4. Index down in different databases

1. MySQL

  • Support: Starting from MySQL 5.6, the InnoDB storage engine supports index pushdown.

  • Implementation method: When InnoDB performs index scans, it pushes some filtering conditions to the storage engine level for processing, reducing the amount of data that needs to be returned to the database engine.

  • Override index optimization: When using overwrite indexes, InnoDB can make full use of index pushdown to avoid table back operations.

  • Example：

    -- Create tables and indexes
CREATE TABLE employees (
    id INT PRIMARY KEY,
    department INT,
    salary INT,
    age INT,
    INDEX idx_dept_salary_age (department, salary, age)
);

-- Query
SELECT salary FROM employees WHERE department = 5 AND age > 30;

    In the above query, indexidx_dept_salary_ageIncludeddepartmentandsalary, but the query also containsage > 30. After enabling index pushdown, InnoDB can be applied during the index scanning phase.age > 30filter conditions to reduce the amount of data that needs to be returned to the table.

2. PostgreSQL

  • Support: PostgreSQL 12 and above introduces index pushdown (calledIndex-Only Scan), you can use index to push down under specific conditions.
  • Implementation method:PostgreSQL viaBitmap Index ScanandIndex-Only ScanImplement index push-down to reduce unnecessary data access.
  • Override index optimization: If the query only involves columns in the index, PostgreSQL can satisfy the query entirely through the index, avoiding back to the table.

3. Oracle

  • Support: Oracle has always supported optimization technologies similar to index pushdown, such asIndex FilteringandIndex Fast Full Scans。
  • Implementation method:The Oracle optimizer applies as many filter conditions as possible during the index scanning phase to reduce table back operations.
  • Bitmap index: Oracle's bitmap index can efficiently utilize index pushdown when processing complex queries, especially queries involving multiple filter conditions.

4. SQL Server

  • Support: Starting from SQL Server 2012, supportColumnstore indexpush down the index.
  • Implementation method: SQL Server can apply filtering conditions when scanning indexes through column storage, reducing unnecessary data access.
  • Column storage optimization: Especially suitable for analytical queries and large-scale data processing.

5. The actual example of index pushdown

Sample Scenario

Suppose there is onestudentsThe table, structure is as follows:

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    grade INT,
    INDEX idx_age_grade (age, grade)
);

Query 1: Satisfies index pushdown

SELECT grade FROM students WHERE age = 20 AND grade > 85;

  • analyze：

    • Query the columns involved:ageandgrade。
    • Indexidx_age_gradeIncludeageandgrade。
    • The query only needs to returngradeList.

  • Index push down：

    • Database can use indexidx_age_gradePerform index scan.
    • During the scanning process, apply directlygrade > 85filter conditions.
    • Since the query only requiresgradecolumn, andgradeIt is already included in the index and can avoid back to the table.

  • Execution plan(Taking MySQL as an example):

    EXPLAIN SELECT grade FROM students WHERE age = 20 AND grade > 85;

    The output may be displayed.idx_age_gradeIndex, and forUse override index(Covering Index), no need to return the table.

Query 2: Index pushdown is not satisfied

SELECT name FROM students WHERE age = 20 AND grade > 85;

  • analyze：

    • Query the columns involved:name、ageandgrade。
    • Indexidx_age_gradeIncludeageandgrade, but does not includename。

  • Index pushdown limit：

    • becausenameThe column is not in the index, and it needs to be retrieved back to the table.namevalue.
    • At this time, index pushdown can be applied during the index scanning stage.age = 20andgrade > 85Conditions, but still need to return to the table to obtainname。

Query 3: Only some conditions apply index pushdown

SELECT grade FROM students WHERE age = 20 AND grade > 85 AND name LIKE 'A%';

  • analyze：

    • Query the columns involved:grade、age、name。
    • Indexidx_age_gradeIncludeageandgrade。

  • Index push down：

    • Can be applied during the index scanning stageage = 20andgrade > 85filter conditions.
    • Forname LIKE 'A%', need to return to the table to obtainnameColumns are further filtered.
    • Index pushdown reduces the amount of data that needs to be returned to the table, but still requires partial return to the table.

6. Limitations of index pushdown

  • Complex query conditions: For query conditions that contain complex expressions, subqueries, or non-simple comparisons, index pushdown may be difficult to apply.
  • Non-coverage index: If the columns required by the query are not completely included in the index, the table back operation is still required, which limits the effect of index pushdown.
  • Database support: Different databases support different levels of index pushdown, and some advanced features may only be available in specific versions or storage engines.
  • Index structure limitations: Some index types (such as hash indexes) may not support efficient index pushdown operations.

7. Suggestions for optimizing index pushdown

  • Design coverage index：

    • Try to keep all columns required for the query included in the index to avoid back-table requirements.
    • For example, for frequently queried columns, these columns can be included in the composite index.

  • Optimize query conditions：

    • Use simple equality and range conditions as much as possible to make index push-down easier to apply.
    • Avoid using complex functions or expressions in query conditions unless these functions are already applied to the index.

  • Select the right index type：

    • Select the appropriate index type according to the query mode, such as B-Tree index is suitable for most range and equivalent queries, bitmap index is suitable for low cardinality sequences, etc.

  • Maintain indexes and statistics：

    • Regularly rebuild or reorganize the index to maintain the efficiency of the index.
    • Ensure the accuracy of statistics and help the query optimizer make the right decisions.

  • Use query analysis tools：

    • Using the query analysis tools provided by the database (such as MySQL'sEXPLAIN, PostgreSQLEXPLAIN ANALYZE) to check the query execution plan and confirm the application status of the index pushdown.
    • Adjust the index design and query structure according to the analysis results.

  • Separate high cardinality and low cardinality sequences：

    • In composite indexes, high cardinality sequences are usually placed in front and low cardinality sequences are placed behind to improve index selectivity and filtering.

8. Conclusion

Index pushdown, as a powerful query optimization technology, can significantly improve database query performance, especially when dealing with complex query conditions and large-scale data. By applying as many filtering conditions as possible during the index scanning phase, reducing table back operations and I/O overhead, index push-down helps improve the efficiency of the overall database system. However, the effect of index pushdown depends on index design, query condition complexity, and the degree of support of the database system. Therefore, rational design of indexes, optimize query structures, and query analysis tools that utilize databases are the key to making full use of index push-down advantages.

This is the end of this article about the sample code for MySQL implementing index pushdown. For more related content on MySQL index pushdown, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!