sem包括网站建设吗,直接玩的网页游戏,网站404页面源码,学校网站用什么模板好前些天发现了一个巨牛的人工智能学习网站#xff0c;通俗易懂#xff0c;风趣幽默#xff0c;忍不住分享一下给大家。点击跳转到网站。 索引下推是之前面试的时候遇到的一个面试题#xff0c;当时没有答上来#xff0c;今天来学习一下。 介绍索引下推之前先看一下MySQL基… 前些天发现了一个巨牛的人工智能学习网站通俗易懂风趣幽默忍不住分享一下给大家。点击跳转到网站。 索引下推是之前面试的时候遇到的一个面试题当时没有答上来今天来学习一下。 介绍索引下推之前先看一下MySQL基础架构。
MySQL通常被分为两层架构即Server层和存储引擎层。Server层处理查询解析、分析、优化、缓存以及与客户端的交互等操作而存储引擎层负责数据的存储和提取。MySQL支持多种不同的存储引擎包括InnoDB、MyISAM、Memory等。通过使用不同的存储引擎可以实现不同的功能和性能特点。 什么是索引下推
索引下推Index Condition Pushdown简称ICP 是 MySQL 5.6 开始引入的一项优化技术可以在执行查询时将过滤条件 下推到存储引擎层 。索引下推技术允许存储引擎使用非键列索引来筛选不符合条件的行减少回表访问主键索引的次数从而提高查询性能。
什么是回表
二级索引(又称非聚簇索引)并不包含行记录的全部数据二级索引上除了当前列以外还包含一个主键通过这个主键来查询聚集索引上对应的数据。当查询除索引以外的其他数据时由于数据无法通过二级索引获取就需要通过主键来找到完整的行记录这就是回表。
索引下推的条件
索引下推的适用条件如下
ICP仅适用于 InnoDB 和 MyISAM 引擎包括它们的分区表。ICP适用于执行计划type是 range, ref, eq_ref和 ref_or_null 的查询语句ICP 只适用于二级索引存储函数不能使用索引下推因为存储引擎无法调用存储函数引用子查询条件不能使用索引下推如果索引列的数据类型是 BLOB 、TEXT 等大数据类型则索引下推无法使用。索引下推只适用于 联合索引
怎么判断一个查询语句是否使用了索引下推
可以通过 MySQL 数据库的慢查询日志或查询性能分析工具来判断一个查询语句是否使用了索引下推。以下是一些常用的方法
查看慢查询日志 。在 MySQL 数据库中可以开启慢查询日志功能来记录执行时间超过指定阈值的查询语句。在慢查询日志中可以查看查询语句的执行计划如果执行计划中包含 Using index condition 等信息则说明该查询语句使用了索引下推。使用 EXPLAIN 命令 。在 MySQL 数据库中可以使用 EXPLAIN 命令来查看查询语句的执行计划。如果执行计划 Extra 中包含 Using index condition 等信息则说明该查询语句使用了索引下推。使用查询性能分析工具 。可以使用一些查询性能分析工具如 pt-query-digest 等来分析 MySQL 数据库的查询性能。这些工具可以自动识别使用了索引下推的查询语句并给出相应的分析结果。
如何使用索引下推
索引下推是 默认开启 的可以通过 optimizer_switch 系统变量来控制。如果要关闭索引下推可以执行以下命令
set optimizer_switchindex_condition_pushdownoff;如果要开启索引下推可以执行以下命令
set optimizer_switchindex_condition_pushdownon;查看是否开启索引下推可以执行如下命令
SHOW VARIABLES LIKE optimizer_switch;索引下推的流程
查询语句中的 WHERE 子句包含一个或多个过滤条件。如果查询语句中使用了索引则 MySQL 数据库会将过滤条件下推到存储引擎层以便在存储引擎中进行过滤减少返回的记录数量。如果过滤条件中包含了非索引列的比较操作符则 MySQL 数据库会将这些条件下推到存储引擎层进行处理这种操作称为索引下推优化。
如果没有使用索引下推MySQL 数据库需要先扫描所有的数据行然后再根据 WHERE 子句中的条件进行筛选这会导致返回的数据量较大查询效率较低。而使用索引下推可以在存储引擎层级别上对数据进行过滤减少不必要的数据扫描提高查询效率。
下面举个例子说明一下
有下面这样一张表
CREATE TABLE users_copy (Id bigint(20) NOT NULL AUTO_INCREMENT COMMENT id,name varchar(32) DEFAULT NULL COMMENT 名称,age tinyint(3) DEFAULT NULL COMMENT 性别,create_date datetime DEFAULT NULL COMMENT 创建时间,is_deleted bit(1) NOT NULL DEFAULT b0,PRIMARY KEY (Id) USING BTREE,KEY idx_age_name (age,name) USING BTREE
) ENGINEInnoDB AUTO_INCREMENT5 DEFAULT CHARSETutf8 ROW_FORMATDYNAMIC COMMENT系统用户表其中age和name组成联合索引。
有下面这样一条SQL语句
SELECT * FROM users_copy WHERE age 18 and name LIKE %M%;在不使用索引下推情况下执行阶段的流程如下
利用索引从存储引擎中查询age 18的用户数据查询完成返回给 server层由于使用的是二级索引且没有索引覆盖所以 server层 会取第一次查询到的每条数据的主键值然后根据主键回表再去存储索引查询一次获取完整行数据。回表查完之后把完整行数据的返回到 server层 再判断 LIKE %M%的数据。最后返回给客户端。
下面看一下没有开启索引下推情况下的EXPLAIN执行计划 查询计划中使用了 Using where 表示使用了回表来查询数据。
使用索引下推情况下执行阶段的流程如下
MySQL把查询条件 age 18 and name LIKE %M% 下推到存储引擎然后查询name以M开头的用户数据查询完并不返回给 server层 而是会检查 name 列只返回 name LIKE %M%的数据其他不符合条件的数据不返回最后返回给 server层 经过 server层 处理之后再返回给客户端。
下面看一下开启索引下推情况下的EXPLAIN执行计划 查询计划中使用了 Using index condition 表示使用了索引下推查询数据。
以上的例子就好比你同事要在代码层(非SQL代码)根据name和 age查用户列表他先通过for循环根据age去一个一个数据库查询到完整的数据然后再判断age是否是符合条件符合条件就放到结果集中。 而你技术远在他之上一眼就看出这段代码要优化就让他把name和 age两个查询条件一起传到数据库层进行查询然后再返回用户列表这样减少了查询次数和IO提高了查询性能。 总结
需要注意的是使用索引下推优化时需要注意数据类型的兼容性问题以避免因为类型不兼容导致查询结果不准确。此外不同的存储引擎对索引下推的支持程度也不同需要针对具体的存储引擎进行优化和调整。
索引下推到此就结束了今天又学了一个知识点。关注我学习更多知识点。
