Java OpenResty Spring Spring Boot MySQL Redis MongoDB PostgreSQL Linux Android Nginx 面试 小程序 Arthas JVM AQS juc Kubernetes Docker DevOps


MySQL 索引条件下推

MySQL 索引 面试 大约 1817 字

索引条件下推

Index Condition Pushdown简称ICP,也称索引条件下推

MySQL中默认开启了索引下推。

开启

set optimizer_switch='index_condition_pushdown=on';

关闭

set optimizer_switch='index_condition_pushdown=off';

ICP 使用条件

  1. 如果表访问的类型为rangerefeq_refref_or_null可以使用ICP
  2. ICP可以用于InnoDBMyISAM表,包括分区表InnoDBMyISAM表。
  3. 对于InnoDB表,ICP仅用于二级索引ICP的目标是减少全行读取次数,从而减少I/O操作。
  4. SQL使用覆盖索引时,不支持ICP。因为这种情况使用ICP不会减少I/O
  5. 相关子查询的条件不能使用ICP

概念

如果部分where条件可以仅使用索引中的列进行筛选,则MySQL服务器会把这部分where条件放到存储引擎中筛选。然后存储引擎通过使用索引条目来筛选数据,并且只有在满足这一条件时才从表中读取行。

好处

ICP可以减少存储引擎必须访问表的次数和MySQL服务器必须访问存储引擎的次数。

坏处

ICP的加速效果取决于在存储引擎内通过ICP筛选的数据的比例。

解释

更多针对联合索引。where条件中有这个字段,但这个字段不走索引。

create table t_user(id int primary key, name varchar(25), age int, no int);
create index idx_age_name on t_user(age, name);
insert into t_user values(1, 'aaa', 10, 1), (2,'bbb', 20, 2), (3, 'ccc', 30, 3);

Using index condition:表示使用了索引条件下推。

本来是要带着age=10回表查询,再带着name like '%abc'回表查询。

使用了索引下推后:

  1. 先将age=10回表查询得到数据。
  2. 然后name like '%abc'再在age=10查询得到的数据中进行筛选,过滤掉不符合的行数据。
  3. 再回表查询得到最终数据。

(因为索引中包含了这两个字段的值,所以第二部不用回表)

mysql> explain select * from t_user where age = 10 and name like '%abc';
+----+-------------+--------+------------+------+---------------+--------------+---------+-------+------+----------+-----------------------+
| id | select_type | table  | partitions | type | possible_keys | key          | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+--------+------------+------+---------------+--------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | t_user | NULL       | ref  | idx_age_name  | idx_age_name | 5       | const |    1 |    33.33 | Using index condition |
+----+-------------+--------+------------+------+---------------+--------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
阅读 437 · 发布于 2022-05-13

————        END        ————

Give me a Star, Thanks:)

https://github.com/fendoudebb

扫描下方二维码关注公众号和小程序↓↓↓

扫描二维码关注我
昵称:
随便看看 换一批