首页 数据库 mysql教程 关于Index Condition Pushdown特性_MySQL

关于Index Condition Pushdown特性_MySQL

May 30, 2016 pm 05:10 PM
特性

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(&#39;M&#39;,&#39;F&#39;) 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 &#39;%index_condition_pushdown%&#39; \G
*************************** 1. row ***************************
@@optimizer_switch like &#39;%index_condition_pushdown%&#39;: 1
1 row in set (0.00 sec)

mysql> select @@optimizer_switch like &#39;%index_condition_pushdown%&#39; \G
*************************** 1. row ***************************
@@optimizer_switch like &#39;%index_condition_pushdown%&#39;: 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 &#39;1955-01-01&#39; and &#39;1955-12-31&#39; and datediff(hire_date,birth_date)>12300 and first_name like &#39;S%b%&#39;;
+----+-------------+-----------+-------+---------------+----------+---------+------+-------+-------------+
| 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=&#39;index_condition_pushdown=on&#39;;
Query OK, 0 rows affected (0.00 sec)

mysql> explain select *   from employees where birth_date between &#39;1955-01-01&#39; and &#39;1955-12-31&#39; and datediff(hire_date,birth_date)>12300 and first_name like &#39;S%b%&#39;;
+----+-------------+-----------+-------+---------------+----------+---------+------+-------+------------------------------------+
| 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 &#39;1955-01-01&#39; and &#39;1955-12-31&#39; and datediff(hire_date,birth_date)>12300 and first_name like &#39;S%b%&#39; |
|        4 | 0.00027500 | SET optimizer_switch=&#39;index_condition_pushdown=off&#39;                                                                                                  |
|        5 | 0.12347025 | select *   from employees where birth_date between &#39;1955-01-01&#39; and &#39;1955-12-31&#39; and datediff(hire_date,birth_date)>12300 and first_name like &#39;S%b%&#39; |
+----------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------+
登录后复制

 

 

从结果可以看出来开启ICP之后确实快不少

 

启用ICP之后,可以用索引来筛选 datediff(hire_date,birth_date)>12300 记录,不需要读出整条记录

 

ICP原理

 

如下图所示(图来自MariaDB)

 

1、优化器没有使用ICP时

 

在存储引擎层,首先读取索引元组(index tuple),然后使用(index tuple)在基表中(base table)定位和读取整行数据

 

到服务器层,匹配where条件,如果该行数据满足where条件则使用,否则丢弃
 

指针向下一行移动,重复以上过程

关于Index Condition Pushdown特性_MySQL

2、使用ICP的时候

 

如果where条件的一部分能够通过使用索引中的字段进行过滤,那么服务器层将把这部分where条件Pushdown到存储引擎层

 

到存储引擎层,从索引中读取索引元组(index tuple),使用索引元组进行判断,如果没有满足where条件,则处理下一条索引元组(index tuple),只有当索引元组满足条件的时候,才会去基表中读取数据

关于Index Condition Pushdown特性_MySQL

ICP的使用条件

 

1、只能用于二级索引(secondary index)

 

2、explain显示的执行计划中type值(join 类型)为range、 ref、 eq_ref或者ref_or_null。且查询需要访问表的整行数据,即不能直接通过二级索引的元组数据获得查询结果(索引覆盖)

 

3、ICP可以用于MyISAM和InnnoDB存储引擎,不支持分区表(5.7将会解决这个问题)

 

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

本站声明
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn

热AI工具

Undresser.AI Undress

Undresser.AI Undress

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

AI Clothes Remover

AI Clothes Remover

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

Undress AI Tool

Undress AI Tool

免费脱衣服图片

Clothoff.io

Clothoff.io

AI脱衣机

AI Hentai Generator

AI Hentai Generator

免费生成ai无尽的。

热门文章

R.E.P.O.能量晶体解释及其做什么(黄色晶体)
2 周前 By 尊渡假赌尊渡假赌尊渡假赌
仓库:如何复兴队友
1 个月前 By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island冒险:如何获得巨型种子
4 周前 By 尊渡假赌尊渡假赌尊渡假赌

热工具

记事本++7.3.1

记事本++7.3.1

好用且免费的代码编辑器

SublimeText3汉化版

SublimeText3汉化版

中文版,非常好用

禅工作室 13.0.1

禅工作室 13.0.1

功能强大的PHP集成开发环境

Dreamweaver CS6

Dreamweaver CS6

视觉化网页开发工具

SublimeText3 Mac版

SublimeText3 Mac版

神级代码编辑软件(SublimeText3)

win7家庭版与win7旗舰版的区别介绍 win7家庭版与win7旗舰版的区别介绍 Jul 12, 2023 pm 08:41 PM

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

掌握Spring MVC的关键概念:了解这些重要特性 掌握Spring MVC的关键概念:了解这些重要特性 Dec 29, 2023 am 09:14 AM

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

5g的三个特性是什么 5g的三个特性是什么 Dec 09, 2020 am 10:55 AM

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

Golang中有类似类的面向对象特性吗? Golang中有类似类的面向对象特性吗? Mar 19, 2024 pm 02:51 PM

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

选择适用的Go版本,根据需求和特性 选择适用的Go版本,根据需求和特性 Jan 20, 2024 am 09:28 AM

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

C++ 函数的类型和特性 C++ 函数的类型和特性 Apr 11, 2024 pm 03:30 PM

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

掌握Golang中间件的关键特性与应用场景 掌握Golang中间件的关键特性与应用场景 Mar 20, 2024 pm 06:33 PM

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

java的特性是什么 java的特性是什么 Aug 09, 2023 pm 03:05 PM

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

See all articles