关于Index Condition Pushdown特性_MySQL
ICP简介
Index Condition Pushdown (ICP) is an optimization for the case where MySQL retrieves rows from a table using an index. Without ICP, the storage engine traverses the index to locate rows in the base table and returns them to the MySQL server which evaluates the WHEREcondition for the rows. With ICP enabled, and if parts of the WHERE condition can be evaluated by using only fields from the index, the MySQL server pushes this part of the WHERE condition down to the storage engine. The storage engine then evaluates the pushed index condition by using the index entry and only if this is satisfied is the row read from the table. ICP can reduce the number of times the storage engine must access the base table and the number of times the MySQL server must access the storage engine.
也就说:利用索引(二级索引)来过滤一部分where条件
测试
导入数据库
wget https://launchpad.net/test-db/employees-db-1/1.0.6/+download/employees_db-full-1.0.6.tar.bz2 tar jxf employees_db-full-1.0.6.tar.bz2 cd employees_db mysql -uroot -p < employees.sql
表结构
mysql> show create table employees \G *************************** 1. row *************************** Table: employees Create Table: CREATE TABLE `employees` ( `emp_no` int(11) NOT NULL, `birth_date` date NOT NULL, `first_name` varchar(14) NOT NULL, `last_name` varchar(16) NOT NULL, `gender` enum('M','F') NOT NULL, `hire_date` date NOT NULL, PRIMARY KEY (`emp_no`), KEY `index_bh` (`birth_date`,`hire_date`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
一些表数据
mysql> select @@optimizer_switch like '%index_condition_pushdown%' \G *************************** 1. row *************************** @@optimizer_switch like '%index_condition_pushdown%': 1 1 row in set (0.00 sec) mysql> select @@optimizer_switch like '%index_condition_pushdown%' \G *************************** 1. row *************************** @@optimizer_switch like '%index_condition_pushdown%': 1 1 row in set (0.00 sec) mysql> select @@query_cache_type; +--------------------+ | @@query_cache_type | +--------------------+ | OFF | +--------------------+ 1 row in set (0.01 sec) mysql> select count(*) from employees; +----------+ | count(*) | +----------+ | 300024 | +----------+ 1 row in set (0.17 sec) mysql> set profiling=1; Query OK, 0 rows affected, 1 warning (0.00 sec)
建立索引
alter table employees add index index_bh (`birth_date`,`hire_date`);
查询分析
mysql> explain select * from employees where birth_date between '1955-01-01' and '1955-12-31' and datediff(hire_date,birth_date)>12300 and first_name like 'S%b%'; +----+-------------+-----------+-------+---------------+----------+---------+------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+-------+---------------+----------+---------+------+-------+-------------+ | 1 | SIMPLE | employees | range | index_bh | index_bh | 3 | NULL | 46318 | Using where | +----+-------------+-----------+-------+---------------+----------+---------+------+-------+-------------+ 1 row in set (0.00 sec) mysql> SET optimizer_switch='index_condition_pushdown=on'; Query OK, 0 rows affected (0.00 sec) mysql> explain select * from employees where birth_date between '1955-01-01' and '1955-12-31' and datediff(hire_date,birth_date)>12300 and first_name like 'S%b%'; +----+-------------+-----------+-------+---------------+----------+---------+------+-------+------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+-------+---------------+----------+---------+------+-------+------------------------------------+ | 1 | SIMPLE | employees | range | index_bh | index_bh | 3 | NULL | 46318 | Using index condition; Using where | +----+-------------+-----------+-------+---------------+----------+---------+------+-------+------------------------------------+ 1 row in set (0.01 sec)
执行查询
mysql> show profiles; +----------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query_ID | Duration | Query | +----------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------+ | 1 | 0.00278025 | desc employees | | 2 | 0.00049775 | show create table employees | | 3 | 0.07444550 | select * from employees where birth_date between '1955-01-01' and '1955-12-31' and datediff(hire_date,birth_date)>12300 and first_name like 'S%b%' | | 4 | 0.00027500 | SET optimizer_switch='index_condition_pushdown=off' | | 5 | 0.12347025 | select * from employees where birth_date between '1955-01-01' and '1955-12-31' and datediff(hire_date,birth_date)>12300 and first_name like 'S%b%' | +----------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------+
从结果可以看出来开启ICP之后确实快不少
启用ICP之后,可以用索引来筛选 datediff(hire_date,birth_date)>12300 记录,不需要读出整条记录
ICP原理
如下图所示(图来自MariaDB)
1、优化器没有使用ICP时
在存储引擎层,首先读取索引元组(index tuple),然后使用(index tuple)在基表中(base table)定位和读取整行数据
到服务器层,匹配where条件,如果该行数据满足where条件则使用,否则丢弃
指针向下一行移动,重复以上过程
2、使用ICP的时候
如果where条件的一部分能够通过使用索引中的字段进行过滤,那么服务器层将把这部分where条件Pushdown到存储引擎层
到存储引擎层,从索引中读取索引元组(index tuple),使用索引元组进行判断,如果没有满足where条件,则处理下一条索引元组(index tuple),只有当索引元组满足条件的时候,才会去基表中读取数据
ICP的使用条件
1、只能用于二级索引(secondary index)
2、explain显示的执行计划中type值(join 类型)为range、 ref、 eq_ref或者ref_or_null。且查询需要访问表的整行数据,即不能直接通过二级索引的元组数据获得查询结果(索引覆盖)
3、ICP可以用于MyISAM和InnnoDB存储引擎,不支持分区表(5.7将会解决这个问题)
4、ICP的加速效果取决于在存储引擎内通过ICP筛选掉的数据的比例

热AI工具

Undresser.AI Undress
人工智能驱动的应用程序,用于创建逼真的裸体照片

AI Clothes Remover
用于从照片中去除衣服的在线人工智能工具。

Undress AI Tool
免费脱衣服图片

Clothoff.io
AI脱衣机

AI Hentai Generator
免费生成ai无尽的。

热门文章

热工具

记事本++7.3.1
好用且免费的代码编辑器

SublimeText3汉化版
中文版,非常好用

禅工作室 13.0.1
功能强大的PHP集成开发环境

Dreamweaver CS6
视觉化网页开发工具

SublimeText3 Mac版
神级代码编辑软件(SublimeText3)

大家都知道win7系统有很多种版本,比如win7旗舰版、win7专业版、win7家庭版等,有不少用户在家庭版和旗舰版之间纠结,不知道选择哪个版本比较好,所以今天小编来跟大家说说win7家庭餐与win7旗舰版的区别介绍,大家一起来看看吧。1、体验不同家庭普通版使您的日常操作变得更快、更简单,可以更快、更方便地访问使用最频繁的程序和文档。家庭高级版让您享有最佳的娱乐体验,可以轻松地欣赏和共享您喜爱的电视节目、照片、视频和音乐。旗舰版集各版本功能之大全,具备Windows7家庭高级版的所有娱乐功能和专

了解SpringMVC的关键特性:掌握这些重要的概念,需要具体代码示例SpringMVC是一种基于Java的Web应用开发框架,它通过模型-视图-控制器(MVC)的架构模式来帮助开发人员构建灵活可扩展的Web应用程序。了解和掌握SpringMVC的关键特性将使我们能够更加有效地开发和管理我们的Web应用程序。本文将介绍一些SpringMVC的重要概念

5g的三个特性是:1、高速率;在实际应用中,5G网络的速率是4G网络10倍以上。2、低时延;5G网络的时延大约几十毫秒,比人的反应速度还要快。3、广连接;5G网络出现,配合其他技术,将会打造一个全新的万物互联景象。

在Golang(Go语言)中并没有传统意义上的类的概念,但它提供了一种称为结构体的数据类型,通过结构体可以实现类似类的面向对象特性。在本文中,我们将介绍如何使用结构体实现面向对象的特性,并提供具体的代码示例。结构体的定义和使用首先,让我们看一下结构体的定义和使用方式。在Golang中,结构体可以通过type关键字定义,然后在需要的地方使用。结构体中可以包含属

随着互联网的快速发展,编程语言也在不断演化和更新。其中,Go语言作为一种开源的编程语言,在近年来备受关注。Go语言的设计目标是简单、高效、安全且易于开发和部署。它具有高并发、快速编译和内存安全等特性,让它在Web开发、云计算和大数据等领域中有着广泛的运用。然而,目前Go语言也有不同的版本可供选择。在选择合适的Go语言版本时,我们需要考虑需求和特性两个方面。首

C++函数有以下类型:简单函数、const函数、静态函数、虚函数;特性包括:inline函数、默认参数、引用返回、重载函数。例如,calculateArea函数使用π计算给定半径圆的面积,并将其作为输出返回。

Golang作为一门快速、高效的编程语言,在Web开发领域也有着广泛的应用。其中,中间件作为一种重要的设计模式,能够帮助开发者更好地组织、管理代码,提高代码的可重用性和可维护性。本文将介绍Golang中中间件的关键特性和应用场景,并通过具体的代码示例来说明其用法。一、中间件的概念及作用中间件作为一种插入式的组件,位于应用程序的请求-响应处理链中,用

java的特性是:1、简单易学;2、面向对象,使得代码更加可重用和可维护;3、平台无关性,能在不同的操作系统上运行;4、内存管理,通过自动垃圾回收机制来管理内存;5、强类型检查,变量在使用之前必须先声明类型;6、安全性,可以防止未经授权的访问和恶意代码的执行;7、多线程支持,能提高程序的性能和响应能力;8、异常处理,可以避免程序崩溃;9、大量的开发库和框架;10、开源生态系统。
