Memahami tekan bawah indeks MySQL dalam satu artikel

Artikel ini membawakan anda pengetahuan yang berkaitan tentang mysql, yang terutamanya memperkenalkan kandungan yang berkaitan tentang tekan turun indeks juga dipanggil tekan ke bawah Indeks Nama penuh Bahasa Inggeris ialah Tekan Turun Indeks ICP, digunakan untuk mengoptimumkan pertanyaan data Mari kita lihat. Saya harap ia akan membantu semua orang.

Pembelajaran yang disyorkan: tutorial video mysql

PILIH proses pelaksanaan pernyataan

MySQL Pangkalan Data Terdiri daripada lapisan Server dan lapisan Engine:

• Server lapisan: mempunyai SQL penganalisis, SQL pengoptimum, SQL Pelaksana , bertanggungjawab untuk proses pelaksanaan khusus pernyataan SQL.
• Engine Lapisan: bertanggungjawab untuk menyimpan data tertentu, seperti enjin storan InnoDB yang paling biasa digunakan dan enjin TempTable yang digunakan untuk menyimpan set hasil sementara dalam ingatan .

• mewujudkan sambungan ke MySQL melalui protokol komunikasi klien/pelayan.

• Cache pertanyaan:

  • Jika Query Cache didayakan dan SQL yang sama dipersoalkan semasa proses cache pertanyaan penyataan, hasil pertanyaan akan dikembalikan terus kepada klien;
  • Jika Query Cache tidak didayakan atau pernyataan SQL yang sama persis tidak ditanya, penghurai akan melakukan analisis sintaks dan semantik dan menjana parse pokok.

• Penghurai menjana pepohon parse baharu.

• Pengoptimum pertanyaan menjana pelan pelaksanaan.

• Enjin pelaksanaan pertanyaan melaksanakan pernyataan SQL Pada masa ini, enjin pelaksanaan pertanyaan akan menentukan jenis enjin storan jadual dalam pernyataan SQL dan antara muka dan enjin storan asas Berdasarkan interaksi antara cache atau fail fizikal, hasil pertanyaan diperolehi Selepas menapis oleh API, hasil pertanyaan dicache dan dikembalikan kepada klien. MySQL Server

  Jika

  dihidupkan, pernyataan Query Cache dan hasil akan disimpan sepenuhnya dalam SQL Jika pernyataan Query Cache yang sama dilaksanakan pada masa hadapan, hasilnya akan menjadi kembali secara langsung. SQL

: Tips telah mengalih keluar MySQL 8.0 (modul cache pertanyaan). query cache

Kerana kadar hit bagi cache pertanyaan akan menjadi sangat rendah. Ketidaksahihan cache pertanyaan adalah sangat kerap: setiap kali terdapat kemas kini pada jadual, semua cache pertanyaan pada jadual itu dikosongkan.

Apakah itu index pushdown?

Tekan turun indeks (): Index Condition Pushdown dirujuk sebagai , dengan menolak syarat penapisan indeks ke enjin storan ke bawah, ia mengurangkan bilangan kali storan ICP enjin mengakses jadual asas dan MySQL Bilangan kali lapisan perkhidmatan mengakses enjin storan. MySQL

Indeks tekan turun VS meliputi indeks: Malah, kedua-duanya mengurangkan bilangan pulangan jadual, tetapi dengan cara yang berbeza

• Indeks tertutup: Apabila indeks mengandungi medan yang diperlukan (), tidak perlu kembali ke jadual untuk menanyakan medan. SELECT XXX

• Tekan turun indeks: Buat pertimbangan pada medan yang disertakan dalam indeks dahulu, tapis terus rekod yang tidak memenuhi syarat untuk kurangkan jadual pulangan bilangan baris.

Untuk memahami cara berfungsi, mulakan dengan pertanyaan ICP: SQL

Contohnya: Nama pertanyaan

Rekod bermula dengan la dengan umur 18

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

mempunyai rekod ini:

tidak dibuka Bagaimana pengimbasan indeks dilakukan: ICP

Cari dan baca baris data yang sepadan melalui tupel indeks. (Sebenarnya: hanya pulangkan jadual)
membuat pertimbangan pada medan dalam
• dan menapis keluar baris yang tidak memenuhi syarat. WHERE

Menggunakan , imbasan indeks diteruskan seperti berikut: ICP

Dapatkan tuple indeks.
Buat pertimbangan pada medan dalam
• dan tapis dalam lajur indeks. WHERE
Kembali ke jadual untuk menanyakan keseluruhan baris untuk indeks yang memenuhi syarat.
Buat pertimbangan pada medan dalam
• dan tapis baris yang tidak memenuhi syarat. WHERE

动手实验：

实验：使用 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视频教程

Atas ialah kandungan terperinci Memahami tekan bawah indeks MySQL dalam satu artikel. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!