Grundlegendes zum MySQL-Index-Pushdown in einem Artikel

Dieser Artikel bringt Ihnen relevantes Wissen über MySQL, das hauptsächlich den relevanten Inhalt zum Index-Pushdown vorstellt, der auch Index-Pushdown genannt wird, oder kurz ICP Optimierung der Datenabfrage. Ich hoffe, dass es für alle hilfreich sein wird.

Empfohlenes Lernen: MySQL-Video-Tutorial

SELECT-Anweisungsausführungsprozess

MySQL-Datenbank besteht aus Server-Ebene und Engine Schichtzusammensetzung: MySQL 数据库由 Server 层和 Engine 层组成：

• Server 层： 有 SQL 分析器、SQL 优化器、SQL 执行器，用于负责 SQL 语句的具体执行过程。
• Engine 层： 负责存储具体的数据，如最常使用的 InnoDB 存储引擎，还有用于在内存中存储临时结果集的 TempTable 引擎。

• 通过客户端/服务器通信协议与 MySQL 建立连接。

• 查询缓存：

  • 如果开启了 Query Cache 且在查询缓存过程中查询到完全相同的 SQL 语句，则将查询结果直接返回给客户端；
  • 如果没有开启 Query Cache 或者没有查询到完全相同的 SQL 语句则会由解析器进行语法语义解析，并生成解析树。

• 分析器生成新的解析树。

• 查询优化器生成执行计划。

• 查询执行引擎执行 SQL 语句，此时查询执行引擎会根据 SQL 语句中表的存储引擎类型，以及对应的 API 接口与底层存储引擎缓存或者物理文件的交互情况，得到查询结果，由 MySQL Server 过滤后将查询结果缓存并返回给客户端。

  若开启了 Query Cache，这时也会将 SQL 语句和结果完整地保存到 Query Cache 中，以后若有相同的 SQL 语句执行则直接返回结果。

Tips：MySQL 8.0 已去掉 query cache（查询缓存模块）。

因为查询缓存的命中率会非常低。 查询缓存的失效非常频繁：只要有对一个表的更新，这个表上所有的查询缓存都会被清空。

什么是索引下推？

索引下推(Index Condition Pushdown)： 简称 ICP，通过把索引过滤条件下推到存储引擎，来减少 MySQL 存储引擎访问基表的次数 和 MySQL 服务层访问存储引擎的次数。

索引下推 VS 覆盖索引： 其实都是 减少回表的次数，只不过方式不同

• 覆盖索引： 当索引中包含所需要的字段（SELECT XXX），则不再回表去查询字段。

• 索引下推： 对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表的行数。

要了解 ICP 是如何工作的，先从一个查询 SQL 开始：

举个栗子：查询名字 la 开头、年龄为 18 的记录

SELECT * FROM user WHERE name LIKE 'la%' AND age = 18;

有这些记录：

不开启 ICP 时索引扫描是如何进行的：

• 通过索引元组，定位读取对应数据行。（实际上：就是回表）
• 对 WHERE 中字段做判断，过滤掉不满足条件的行。

使用 ICP，索引扫描如下进行：

• 获取索引元组。
• 对 WHERE 中字段做判断，在索引列中进行过滤。
• 对满足条件的索引，进行回表查询整行。
• 对 WHERE
  Server Schicht: Hat SQL-Analysator, SQL-Optimierung Executor, SQL Executor, ist für den spezifischen Ausführungsprozess von SQL-Anweisungen verantwortlich.

• Engine-Schicht: Verantwortlich für die Speicherung spezifischer Daten, wie z. B. der am häufigsten verwendeten InnoDB-Speicher-Engine, und für die Speicherung temporärer Ergebnisse in Speichersets TempTable-Engine.

  🎜🎜
  • 🎜Vom Kunden Stellen Sie mithilfe des Client/Server-Kommunikationsprotokolls eine Verbindung mit MySQL her. 🎜🎜
  • 🎜🎜Abfrage-Cache:🎜
    • Wenn Abfrage-Cache aktiviert ist und genau das gleiche SQL während der Abfrage abgefragt wird Cache-Prozesscode>-Anweisung werden die Abfrageergebnisse direkt an den Client zurückgegeben. 🎜
    • Wenn Query Cache nicht aktiviert ist oder genau dieselbe SQL-Anweisung nicht aktiviert ist Wenn es abgefragt wird, wird es analysiert. Der Prozessor führt eine syntaktische und semantische Analyse durch und generiert einen Analysebaum. 🎜🎜🎜
    • 🎜Der Parser generiert einen neuen Parse-Baum. 🎜🎜
    • 🎜Der Abfrageoptimierer generiert einen Ausführungsplan. 🎜🎜
    • 🎜Die Abfrageausführungs-Engine führt die SQL-Anweisung aus. Zu diesem Zeitpunkt basiert die Abfrageausführungs-Engine auf dem Speicher-Engine-Typ der Tabelle im SQL Anweisung und die entsprechende API Die Schnittstelle interagiert mit dem zugrunde liegenden Speicher-Engine-Cache oder physischen Dateien, um Abfrageergebnisse zu erhalten. Nach der Filterung durch MySQL Server werden die Abfrageergebnisse zwischengespeichert und zurückgegeben der Kunde. 🎜

      🎜Wenn Query Cache aktiviert ist, werden die SQL-Anweisung und die Ergebnisse zu diesem Zeitpunkt vollständig im Query Cache gespeichert Wird dieselbe SQL-Anweisung ausgeführt, wird das Ergebnis direkt zurückgegeben. 🎜

      🎜🎜🎜🎜Tipps: MySQL 8.0 hat den Abfrage-Cache (Abfrage-Cache-Modul) entfernt. 🎜

      🎜Weil die Trefferquote des Abfragecaches sehr niedrig sein wird. Ungültigmachungen des Abfragecaches kommen sehr häufig vor: Bei jeder Aktualisierung einer Tabelle werden alle Abfragecaches für diese Tabelle geleert. 🎜

      🎜Was ist Index-Pushdown? 🎜🎜🎜Index Condition Pushdown (Index Condition Pushdown): wird als ICP bezeichnet und reduziert MySQL, indem die Indexfilterbedingungen nach unten verschoben werden Speicher-Engine. Die Häufigkeit, mit der die Speicher-Engine auf die Basistabelle zugreift, und die Häufigkeit, mit der die MySQL-Dienstschicht auf die Speicher-Engine zugreift. 🎜🎜🎜Index-Pushdown vs. Covering-Index: Tatsächlich 🎜reduzieren beide die Anzahl der Tabellenrückgaben, aber auf unterschiedliche Weise 🎜
      • 🎜🎜Covered-Index: Wenn die Indizierung die erforderlichen Felder enthält (SELECT XXX), ist es nicht erforderlich, zur Tabelle zurückzukehren, um die Felder abzufragen. 🎜🎜
      • 🎜🎜Index-Pushdown: Treffen Sie zunächst eine Beurteilung der im Index enthaltenen Felder, 🎜filtern Sie direkt die Datensätze heraus, die die Bedingungen nicht erfüllen und reduzieren Sie die Anzahl der zurückgegebenen Zeilen der Tisch. 🎜🎜🎜🎜🎜Um zu verstehen, wie ICP funktioniert, beginnen Sie mit einer Abfrage SQL:🎜🎜Zum Beispiel: Fragen Sie den Namen la Records ab, der mit beginnt und Alter 18 🎜

        -- 表创建
        CREATE TABLE IF NOT EXISTS `user` (
        `id` VARCHAR(64) NOT NULL COMMENT '主键 id',
        `name` VARCHAR(50) NOT NULL COMMENT '名字',
        `age` TINYINT NOT NULL COMMENT '年龄',
        `address` VARCHAR(100) NOT NULL COMMENT '地址',
        PRIMARY KEY (id)
        ) ENGINE=InnoDB DEFAULT CHARSET utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT '用户表';
        
        -- 创建索引
        CREATE INDEX idx_name_age ON user (name, age);
        
        -- 新增数据
        INSERT INTO user (id, name, age, address) VALUES (1, 'tt', 14, 'linhai');
        INSERT INTO user (id, name, age, address) VALUES (2, 'lala', 18, 'linhai');
        INSERT INTO user (id, name, age, address) VALUES (3, 'laxi', 30, 'linhai');
        INSERT INTO user (id, name, age, address) VALUES (4, 'lawa', 40, 'linhai');
        
        -- 查询语句
        SELECT * FROM user WHERE name LIKE 'la%' AND age = 18;

        🎜🎜 haben diese Datensätze: 🎜🎜🎜🎜🎜So funktioniert das Indexscannen, wenn ICP nicht aktiviert ist:🎜
        • Suchen und lesen die entsprechende Datenzeile durch das Indextupel. (Eigentlich: Geben Sie einfach die Tabelle zurück) 🎜
        • Beurteilen Sie die Felder in WHERE und filtern Sie Zeilen heraus, die die Bedingungen nicht erfüllen. 🎜🎜🎜🎜🎜🎜Verwenden Sie ICP</ code>, der Indexscan läuft wie folgt ab:</strong>🎜<ul><li>Holen Sie sich das Indextupel. 🎜<li>Beurteilen Sie die Felder in <code>WHERE und filtern Sie in der Indexspalte. 🎜
        • Für Indizes, die die Bedingungen erfüllen, fragen Sie die gesamte Zeile zurück in die Tabelle ab. 🎜
        • Beurteilen Sie die Felder in WHERE und filtern Sie Zeilen heraus, die die Bedingungen nicht erfüllen. 🎜🎜🎜🎜🎜

          动手实验：

          实验：使用 MySQL 版本 8.0.16

          -- 表创建
          CREATE TABLE IF NOT EXISTS `user` (
          `id` VARCHAR(64) NOT NULL COMMENT '主键 id',
          `name` VARCHAR(50) NOT NULL COMMENT '名字',
          `age` TINYINT NOT NULL COMMENT '年龄',
          `address` VARCHAR(100) NOT NULL COMMENT '地址',
          PRIMARY KEY (id)
          ) ENGINE=InnoDB DEFAULT CHARSET utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT '用户表';
          
          -- 创建索引
          CREATE INDEX idx_name_age ON user (name, age);
          
          -- 新增数据
          INSERT INTO user (id, name, age, address) VALUES (1, 'tt', 14, 'linhai');
          INSERT INTO user (id, name, age, address) VALUES (2, 'lala', 18, 'linhai');
          INSERT INTO user (id, name, age, address) VALUES (3, 'laxi', 30, 'linhai');
          INSERT INTO user (id, name, age, address) VALUES (4, 'lawa', 40, 'linhai');
          
          -- 查询语句
          SELECT * FROM user WHERE name LIKE 'la%' AND age = 18;

          新增数据如下：

          

          • 关闭 ICP，再调用 EXPLAIN 查看语句：

          -- 将 ICP 关闭
          SET optimizer_switch = 'index_condition_pushdown=off';
          -- 查看确认
          show variables like 'optimizer_switch';
          
          -- 用 EXPLAIN 查看
          EXPLAIN SELECT * FROM user WHERE name LIKE 'la%' AND age = 18;

          

          • 开启 ICP，再调用 EXPLAIN 查看语句：

          -- 将 ICP 打开
          SET optimizer_switch = 'index_condition_pushdown=on';
          -- 查看确认
          show variables like 'optimizer_switch';
          
          -- 用 EXPLAIN 查看
          EXPLAIN SELECT * FROM user WHERE name LIKE 'la%' AND age = 18;

          

          由上实验可知，区别是否开启 ICP： Exira 字段中的 Using index condition

          

          更进一步，来看下 ICP 带来的性能提升：

          通过访问数据文件的次数

          -- 1. 清空 status 状态
          flush status;
          -- 2. 查询
          SELECT * FROM user WHERE name LIKE 'la%' AND age = 18;
          -- 3. 查看 handler 状态
          show status like '%handler%';

          对比开启 ICP 和 关闭 ICP： 关注 Handler_read_next 的值

          -- 开启 ICP
          flush status;
          SELECT * FROM user WHERE name LIKE 'la%' AND age = 18;
          show status like '%handler%';
          +----------------------------|-------+
          | Variable_name              | Value |
          +----------------------------|-------+
          | Handler_commit             | 1     |
          | Handler_delete             | 0     |
          | Handler_discover           | 0     |
          | Handler_external_lock      | 2     |
          | Handler_mrr_init           | 0     |
          | Handler_prepare            | 0     |
          | Handler_read_first         | 0     |
          | Handler_read_key           | 1     |  
          | Handler_read_last          | 0     |
          | Handler_read_next          | 1     |  <---重点
          | Handler_read_prev          | 0     |
          | Handler_read_rnd           | 0     |
          | Handler_read_rnd_next      | 0     |
          | Handler_rollback           | 0     |
          | Handler_savepoint          | 0     |
          | Handler_savepoint_rollback | 0     |
          | Handler_update             | 0     |
          | Handler_write              | 0     |
          +----------------------------|-------+
          18 rows in set (0.00 sec)
          
          
          -- 关闭 ICP
          flush status;
          SELECT * FROM user WHERE name LIKE &#39;la%&#39; AND age = 18;
          show status like '%handler%';
          +----------------------------|-------+
          | Variable_name              | Value |
          +----------------------------|-------+
          | Handler_commit             | 1     |
          | Handler_delete             | 0     |
          | Handler_discover           | 0     |
          | Handler_external_lock      | 2     |
          | Handler_mrr_init           | 0     |
          | Handler_prepare            | 0     |
          | Handler_read_first         | 0     |
          | Handler_read_key           | 1     |
          | Handler_read_last          | 0     |
          | Handler_read_next          | 3     |  <---重点
          | Handler_read_prev          | 0     |
          | Handler_read_rnd           | 0     |
          | Handler_read_rnd_next      | 0     |
          | Handler_rollback           | 0     |
          | Handler_savepoint          | 0     |
          | Handler_savepoint_rollback | 0     |
          | Handler_update             | 0     |
          | Handler_write              | 0     |
          +----------------------------|-------+
          18 rows in set (0.00 sec)

          由上实验可知：

          • 开启 ICP：Handler_read_next 等于 1，回表查 1 次。
          • 关闭 ICP：Handler_read_next 等于 3，回表查 3 次。

          这实验跟上面的栗子就对应上了。

          索引下推限制

          根据官网可知，索引下推 受以下条件限制：

          • 当需要访问整个表行时，ICP 用于 range、 ref、 eq_ref 和 ref_or_null

          • ICP可以用于 InnoDB 和 MyISAM 表，包括分区表 InnoDB 和 MyISAM 表。

          • 对于 InnoDB 表，ICP 仅用于二级索引。ICP 的目标是减少全行读取次数，从而减少 I/O 操作。对于 InnoDB 聚集索引，完整的记录已经读入 InnoDB 缓冲区。在这种情况下使用 ICP 不会减少 I/O。

          • 在虚拟生成列上创建的二级索引不支持 ICP。InnoDB 支持虚拟生成列的二级索引。

          • 引用子查询的条件不能下推。

          • 引用存储功能的条件不能被按下。存储引擎不能调用存储的函数。

          • 触发条件不能下推。

          • 不能将条件下推到包含对系统变量的引用的派生表。（MySQL 8.0.30 及更高版本)。

          小结下：

          • ICP 仅适用于 二级索引。
          • ICP 目标是 减少回表查询。
          • ICP 对联合索引的部分列模糊查询非常有效。

          拓展：虚拟列

          CREATE TABLE UserLogin (
          userId BIGINT,
          loginInfo JSON,
          cellphone VARCHAR(255) AS (loginInfo->>"$.cellphone"),
          PRIMARY KEY(userId),
          UNIQUE KEY idx_cellphone(cellphone)
          );

          列 cellphone ：就是一个虚拟列，它是由后面的函数表达式计算而成，本身这个列不占用任何的存储空间，而索引 idx_cellphone 实质是一个函数索引。

          好处： 在写 SQL 时可以直接使用这个虚拟列，而不用写冗长的函数。

          举个栗子： 查询手机号

          -- 不用虚拟列
          SELECT * FROM UserLogin WHERE loginInfo->>"$.cellphone" = '13988888888'
          
          -- 使用虚拟列
          SELECT * FROM UserLogin WHERE cellphone = '13988888888'

          推荐学习：mysql视频教程

          Das obige ist der detaillierte Inhalt vonGrundlegendes zum MySQL-Index-Pushdown in einem Artikel. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!