【mysql】MySQL中的回表查询、索引覆盖、索引下推、谓词下推 ...

mysql版本：8.0.25
【一】回表查询

【1】索引的存储形式

在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：

聚集索引选取规则
（1）假如存在主键，主键索引就是聚集索引。
（2）假如不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
（3）假如表没有主键，或没有合适的唯一索引，则InnoDB会主动天生一个rowid作为隐蔽的聚集索引
聚集索引和二级索引的详细布局如下：

（1）聚集索引的叶子节点下挂的是这一行的数据 。
（2）二级索引的叶子节点下挂的是该字段值对应的主键值
【2】sql的执行过程

接下来，我们来分析一下，当我们执行如下的SQL语句时，详细的查找过程是什么样子的。

详细过程如下：
（1）由于是根据name字段进行查询，所以先根据name='Arm’到name字段的二级索引中进行匹配查找。但是在二级索引中只能查找到 Arm 对应的主键值 10。
（2）由于查询返回的数据是*，所以此时，还需要根据主键值10，到聚集索引中查找10对应的记录，终极找到10对应的行row。
（3）终极拿到这一行的数据，直接返回即可。
得到回表查询的概念：
回表查询： 这种先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据的方式，就称之为回表查询。
【3】案例分析

以下两条SQL语句，那个执行效率高? 为什么?

1. -- A语句
2. -- 备注: id为主键，name字段创建的有索引；
3. select * from user where id = 10 ;
5. -- B语句
6. select * from user where name = 'Arm' ;

复制代码

A 语句的执行性能要高于B 语句。由于A语句直接走聚集索引，直接返回数据。 而B语句需要先查询name字段的二级索引，然后再查询聚集索引，也就是需要进行回表查询。
【4】什么是mysql的回表

在MySQL数据库中，回表（Look Up）指的是在进行索引查询时，起首通过索引定位到对应页，然后再根据行的物理地址找到所需的数据行。换句话说，回表是指根据索引查询到的主键值再去访问主键索引，从而获取完整的数据记录。

【5】什么情况下会触发回表？

MySQL的回表操纵通常在以下情况下会发生：
（1）索引不Cover所有需要查询的字段

当查询语句中需要返回的列不在索引列上时，纵然通过索引定位了相干行，仍然需要回表获取其他列的值。
（2）使用了非聚簇索引

非聚簇索引（Secondary Index）只包罗了索引列的副本以及指向对应主键的引用，查询需要通过回表才气获取完整的行数据。

（3）使用了覆盖索引但高出了最大索引长度

在MySQL的InnoDB存储引擎中，每个索引项的最大长度是767字节，假如查询需要返回的字段长度高出了该限定，同样会触发回表操纵。
需要留意的是，回表操纵主要发生在读取操纵（SELECT）中，写入操纵（INSERT、UPDATE、DELETE）一般不会触发回表。
【6】哪些情况下不会触发回表？

在某些特别情况下，MySQL的回表操纵可以被制止：
（1）覆盖索引

假如查询的字段都在某个索引上，并且没有高出最大索引长度限定，MySQL可以直接从索引中获取所需数据，而无需回表。
（2）使用聚簇索引

InnoDB存储引擎的主键索引是聚簇索引，它包罗了整个行的数据。当查询条件使用了主键或者通过主键查询时，MySQL可以直接从主键索引中获取所有需要的数据，无需回表。

【7】回表操纵的题目和场景

回表操纵固然提供了更全面的数据信息，但也带来了一些题目和范围性。
（1）性能题目

回表操纵通常需要访问两次索引，增长了IO开销和CPU斲丧，对查询性能有肯定的影响。特别是在高并发、大数据量的情况下，回表可能成为性能瓶颈。
（2）数据一致性

由于回表操纵是基于物理地址来获取数据，假如在回表过程中发生了数据修改（如DELETE、UPDATE），则可能会读取到不一致或错误的数据。
（3）是否使用覆盖索引的判定

在选择是否使用覆盖索引时，需要综合考虑查询的字段以及字段长度，以及查询操纵的频率和数据量。假如查询需要返回的字段较多或字段长度较长，可能需要衡量回表带来的性能损耗和数据完整性的需求。
在现实应用中，我们可以根据详细的场景来决定是否使用回表操纵。下面列举了一些使用回表的典范场景：
   需要返回更全面的数据：有些查询场景下，返回的字段可能不仅仅是索引所包罗的列，此时回表可以提供更全面的数据信息。
使用非聚簇索引：当表中没有定义主键或者查询条件没有使用主键时，非聚簇索引成为主要的索引选择，但回表操纵则难以制止。
高出最大索引长度限定：假如需要返回的字段长度高出了最大索引长度限定，纵然使用了覆盖索引也无法制止回表，此时需要留意回表带来的性能损耗。
  【8】总结

综上所述，MySQL的回表操纵是在索引查询时，通过主键索引再次访问以获取完整数据记录的过程。
【二】索引覆盖

索引覆盖（Index Covering）是指通过在索引中包罗所有查询语句中所需的列，可以制止对表中的数据进行额外的访问，从而进步查询效率。(制止了回表操纵)
例如，对于一个查询语句：

1. SELECT col1, col2, col3 FROM table WHERE col1 = x AND col2 = y

复制代码

假如在table表中创建了一个索引，包罗col1、col2和col3三列，那么MySQL可以通过索引定位到符合条件的数据，并在索引中提取col1、col2和col3列的值，无需对表中的数据进行额外的访问。这种方式就叫做索引覆盖。
索引覆盖可以或许显著进步查询效率，因此在创建索引时应只管考虑包罗查询语句中所需的所有列。
我们现实性进行测试：

1. -- 创建数据库
2. CREATE DATABASE IndexCoveringDemo;
3. USE IndexCoveringDemo;
5. -- 创建表
6. CREATE TABLE employees (
7.     id INT PRIMARY KEY AUTO_INCREMENT,
8.     first_name VARCHAR(50),
9.     last_name VARCHAR(50),
10.     department VARCHAR(50),
11.     salary DECIMAL(10, 2),
12.     INDEX idx_name_department_salary (first_name, department, salary)
13. );
15. -- 插入随机数据
16. INSERT INTO employees (first_name, last_name, department, salary)
17. VALUES
18. ('John', 'Doe', 'Engineering', 75000.00),
19. ('Jane', 'Smith', 'Marketing', 55000.00),
20. ('Alice', 'Johnson', 'Sales', 60000.00),
21. ('Bob', 'Brown', 'Engineering', 80000.00),
22. ('Charlie', 'Davis', 'HR', 50000.00),
23. ('Emily', 'Wilson', 'Sales', 65000.00),
24. ('David', 'Clark', 'Engineering', 70000.00),
25. ('Frank', 'Moore', 'Marketing', 52000.00),
26. ('Grace', 'Taylor', 'HR', 48000.00),
27. ('Henry', 'Miller', 'Sales', 72000.00);

复制代码

假设我们要查询Engineering部分中first_name和salary，我们可以利用之前创建的复合索引来进行索引覆盖查询。

1. EXPLAIN SELECT first_name, salary FROM employees WHERE department = 'Engineering';

复制代码

在这个查询中，first_name和salary两个字段都包罗在索引idx_name_department_salary中，department字段是索引的一部分。这个查询将可以通过索引直接返回数据，而不需要访问现实的表数据。
假如我们查询的字段不完全包罗在索引中，则MySQL将无法进行索引覆盖，需要访问表数据。

1. EXPLAIN SELECT first_name, last_name FROM employees  WHERE department = 'Engineering';

复制代码

在这个查询中，last_name字段不在索引idx_name_department_salary中，因此MySQL不能使用索引覆盖查询。
索引覆盖可以或许显著提拔查询性能，尤其是在涉及大量数据时。通过公道筹划复合索引，可以使查询仅通过索引就能返回所有所需的数据，从而减少磁盘I/O并加速查询速度。在筹划索引时，需要衡量字段选择，确保常用查询尽可能通过索引覆盖来优化。
【三】索引下推

【1】先容

索引下推（INDEX CONDITION PUSHDOWN，简称 ICP）是在 MySQL 5.6 针对扫描二级索引的一项优化改进。总的来说是通过把索引过滤条件下推到存储引擎，来减少 MySQL 存储引擎访问基表的次数以及 MySQL 服务层访问存储引擎的次数。ICP 适用于 MYISAM 和 INNODB，本篇的内容只基于 INNODB。

（1）MySQL 服务层：也就是 SERVER 层，用来剖析 SQL 的语法、语义、天生查询筹划、接管从 MySQL 存储引擎层上推的数据进行二次过滤等等。
（2）MySQL 存储引擎层：按照 MySQL 服务层下发的哀求，通过索引或者全表扫描等方式把数据上传到 MySQL 服务层。
（3）MySQL 索引扫描：根据指定索引过滤条件，遍历索引找到索引键对应的主键值后回表过滤剩余过滤条件。
（4）MySQL 索引过滤：通过索引扫描并且基于索引进行二次条件过滤后再回表。

（5）使用索引下推实现

【2】案例

（1）索引下推的使用条件

（1）ICP目标是减少全行记录读取，从而减少IO 操纵，只能用于非聚簇索引。聚簇索引本身包罗的表数据，也就不存在下推一说。
（2）只能用于range、 ref、 eq_ref、ref_or_null访问方法；
（3）where 条件中是用 and 而非 or 的时候。
（4）ICP适用于分区表。
（5）ICP不支持基于假造列上创建的索引，比如说函数索引
（6）ICP不支持引用子查询作为条件。
（7）ICP不支持存储函数作为条件，由于存储引擎无法调用存储函数。
（2）索引下推相干语句

1. # 查看索引下推是否开启
2. select @@optimizer_switch
3. # 开启索引下推
4. set optimizer_switch="index_condition_pushdown=on";
5. # 关闭索引下推
6. set optimizer_switch="index_condition_pushdown=off";

复制代码

进行详细的测试：

1. CREATE DATABASE icp_demo;
2. USE icp_demo;
4. CREATE TABLE employees (
5.     emp_id INT AUTO_INCREMENT PRIMARY KEY,
6.     emp_name VARCHAR(255),
7.     dept_id INT,
8.     salary DECIMAL(10, 2),
9.     hire_date DATE,
10.     INDEX idx_dept_salary (dept_id, salary)
11. ) ENGINE=InnoDB;

复制代码

1. DELIMITER $$
2. CREATE PROCEDURE populate_employees()
3. BEGIN
4.     DECLARE i INT DEFAULT 1;
5.     WHILE i <= 100000 DO
6.         INSERT INTO employees (emp_name, dept_id, salary, hire_date)
7.         VALUES (
8.             CONCAT('Employee_', i),
9.             FLOOR(RAND() * 10),
10.             ROUND(RAND() * 100000, 2),
11.             CURDATE() - INTERVAL FLOOR(RAND() * 3650) DAY
12.         );
13.         SET i = i + 1;
14.     END WHILE;
15. END$$
16. DELIMITER ;

复制代码

1. CALL populate_employees();

复制代码

在执行查询之前，我们可以先查看索引下推是否开启。

1. SELECT @@optimizer_switch;

复制代码

1. SET optimizer_switch = 'index_condition_pushdown=on';

复制代码

1. EXPLAIN SELECT emp_id, emp_name FROM employees WHERE dept_id = 5 AND salary BETWEEN 50000 AND 80000;

复制代码

1. SET optimizer_switch = 'index_condition_pushdown=off';

复制代码

1. EXPLAIN SELECT emp_id, emp_name FROM employees WHERE dept_id = 5 AND salary BETWEEN 50000 AND 80000;

复制代码

记得再次打开：

1. SET optimizer_switch = 'index_condition_pushdown=on';

复制代码

【四】谓词下推

谓词下推，就是在将过滤条件下推到离数据源更近的地方，最好就是在table_scan时就能过滤掉不需要的数据，在关系代数中谓词是可以左右上下移动的，由于join查询的特别性，在优化join condition中的谓词时，对应不同的join 内型，有不同的战略
（1）inner join
inner join的结果集是左表和有表都要满意条件，所以inner join condtion中的条件都是可以下推的，比如下面的查询

1. select e.* from emp e inner join dept d on e.deptno = d.deptno and emp.deptno=5;

复制代码

优化后的查询

1. select e. from (select * from emp where emp.deptno =5) e inner join (select * from deptno where deptno =5 ) on on e.deptno = d.deptno;

复制代码

（2）left join
由于左表是保存表，所有join condition 中的左表条件会失效，右表的条件可以下推，对right join. 则相反；
（3）outter join
对于outter join 由于左表和右表都是保存表，则都不能下推；
（4）anti join
对于anti join,由于查询的结果集是不满意join 条件的集合，假如谓词下推，查询结果集将会为null, 当然有一种思绪可以在谓词时下推时将条件取非，获取正确结果；

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。